I am not familiar with your problem though admittedly we are currently not running either of these plugins at the moment.
However, to see if MySQL is under a heavy load, if you have root access run the following commend to get what is going on with MySQL:
mysqladmin processlist
This will give you a list of ongoing MySQL Processes and you can see exactly what is happening with MySQL. You will probably find these plugs are making database calls every display of their content.
Next regardless of the processes are running, I would run the following Data Base Caching Plugin to see if I gain the performance back :-) and should also minimize the database calls.
"show full processlist" within mysql displays alot (100+) ongoing queries to the wp_site_posts table.
I backed down the "recent posts plugin" to the earlier version and it stopped :)
we are getting to the bottom of what the problem is :-) and a another Dev can jump in at anytime :-)
Here on WPMU Dev we are all developers including yourself sir or we would not be here :-)
Obviously the performance issue is the multiple queries problem from several sites to perform the operation of this particular plugin.
From your results and regarding performance it should make all the difference in the world to cache the MySQL Database calls.
I would suspect that from a WPMU Dev perspective that everything is working correctly just that you are using the instance too many times for your particular hosting to handle efficiently causing the performance problems. Everything has its limits ..... lol
By caching the database queries it should stop or at minimum slow down the MySQL calls and return a measured amount of performance :-)
Hi Joe, yes I understand :) but I ment the developer who has written the recent posts plugin.
Obviously there are some differences in the newest version causing lots of queries to the table.
By reverting to the former version it seems to be all good.
We have caching on in MySQL and run a varnish in front of the webservers.
thank you sir for your reply and it is an AHA! moment with the new information that the newer version is causing significantly more queries to the DB then the old :-)
and of course you are absolutely correct in this event that the programmer whom wrote the plugin needs to take a look at this issue :-)
Sorry I could not be of further help with this issue :-S
As a quick aside - can you let me know what indexes are on wp_site_posts on your database? That table gets very very big - and I'm sure the plugin should create a few on there (can't get to the code at the moment, but when I can I'll double check - but in the meantime we can get some on your table to improve performance a lot)
Try running these two bits of SQL on your DB to add two new indexes and see if that makes a difference - depending on the size of the table, it may take a short while for MySQL to build the indexes so run them, then leave it for a little bit before doing some proper testing again.
ALTER TABLE wp_site_posts ADD INDEX (blog_public);
ALTER TABLE wp_site_posts ADD INDEX (post_id, blog_id);
There is another index that we can add, but see how these work first.
Many thanks Barry!
The indexes has been added and I'll monitor the performance today. If all good I'll try to upgrade to the latest Recent Posts plugin. I'll be back with info
Barry, sorry to say but I still get *alot* of slow queries logged on that table.
Can I somehow truncate it a bit? We really only need the 10 latest posts at all time. Now it's around 222,000 rows in it
They may have still been building :/ though it shouldn't take that long.
Let me know the queries that are really slow for you when you can and I'll see what I can do either in the plugin or index wise to get them running quicker.
Responses (23)
Sales & Support Pro — 9th November 2011 #
Hiya!
MySQL response times is never something I've needed to look into in great detail so I'm not sure what a good benchmark would be.
Let me ask some of the other guys for their thoughts.
Phil
Member — 9th November 2011 #
Hey Tomas,
Are you using a plugin for the most recent posts or just running a loop?
Member — 9th November 2011 #
Hi, we use the wpmudev plugin "recent posts" and use this code in the theme files
<?php display_recent_posts(10,40,100,'<br />','<strong>','</strong>','<ul>','</ul>','<li>','</li>','yes','yes',48, 'post', true); ?>Thanks
Sales & Support Lead — 14th November 2011 #
Hiya,
Not something I'm familiar with either, let me ask Aaron and Barry about this one. Thanks.
Member — 25th January 2012 #
Hi again, I still get *alot* of these:
I'm really worried we have performance issues. Anyone with similar issues with the recent posts plugin?
Post-indexer and recent posts plugins are at latest level.
Thanks
Support Kangaroo — 25th January 2012 #
Greetings Tomas :-)
I am not familiar with your problem though admittedly we are currently not running either of these plugins at the moment.
However, to see if MySQL is under a heavy load, if you have root access run the following commend to get what is going on with MySQL:
mysqladmin processlistThis will give you a list of ongoing MySQL Processes and you can see exactly what is happening with MySQL. You will probably find these plugs are making database calls every display of their content.
Next regardless of the processes are running, I would run the following Data Base Caching Plugin to see if I gain the performance back :-) and should also minimize the database calls.
http://wordpress.org/extend/plugins/db-cache-reloaded-fix/
Hope this helps :-)
Joe :-)
Member — 25th January 2012 #
Thanks Joe,
"show full processlist" within mysql displays alot (100+) ongoing queries to the wp_site_posts table.
I backed down the "recent posts plugin" to the earlier version and it stopped :)
Maybe DEV can chip in?
Thanks
Tomas
Support Kangaroo — 25th January 2012 #
Greetings Tomas :-)
we are getting to the bottom of what the problem is :-) and a another Dev can jump in at anytime :-)
Here on WPMU Dev we are all developers including yourself sir or we would not be here :-)
Obviously the performance issue is the multiple queries problem from several sites to perform the operation of this particular plugin.
From your results and regarding performance it should make all the difference in the world to cache the MySQL Database calls.
I would suspect that from a WPMU Dev perspective that everything is working correctly just that you are using the instance too many times for your particular hosting to handle efficiently causing the performance problems. Everything has its limits ..... lol
By caching the database queries it should stop or at minimum slow down the MySQL calls and return a measured amount of performance :-)
Joe :-)
Member — 25th January 2012 #
Hi Joe, yes I understand :) but I ment the developer who has written the recent posts plugin.
Obviously there are some differences in the newest version causing lots of queries to the table.
By reverting to the former version it seems to be all good.
We have caching on in MySQL and run a varnish in front of the webservers.
Regards
Tomas
Support Kangaroo — 25th January 2012 #
Greetings Tomas :-)
thank you sir for your reply and it is an AHA! moment with the new information that the newer version is causing significantly more queries to the DB then the old :-)
and of course you are absolutely correct in this event that the programmer whom wrote the plugin needs to take a look at this issue :-)
Sorry I could not be of further help with this issue :-S
Joe :-)
Member — 25th January 2012 #
Joe, I really appreciate your assistance :)
Thanks
Tomas
Sales & Support Lead — 25th January 2012 #
Hiya guys,
We'll look into the extra db calls in Recent Posts here shortly. The developer has been notified. Thanks again!
Developer — 25th January 2012 #
As a quick aside - can you let me know what indexes are on wp_site_posts on your database? That table gets very very big - and I'm sure the plugin should create a few on there (can't get to the code at the moment, but when I can I'll double check - but in the meantime we can get some on your table to improve performance a lot)
Member — 26th January 2012 #
Hi Barry, this is the current indexes on that table:
Developer — 26th January 2012 #
Ok, I'll pop you some sql in my next reply to create a couple more indexes to see if that will help any with performance.
Member — 26th January 2012 #
Sounds great Barry!
Developer — 26th January 2012 #
Try running these two bits of SQL on your DB to add two new indexes and see if that makes a difference - depending on the size of the table, it may take a short while for MySQL to build the indexes so run them, then leave it for a little bit before doing some proper testing again.
ALTER TABLE wp_site_posts ADD INDEX (blog_public);
ALTER TABLE wp_site_posts ADD INDEX (post_id, blog_id);
There is another index that we can add, but see how these work first.
Member — 27th January 2012 #
Many thanks Barry!
The indexes has been added and I'll monitor the performance today. If all good I'll try to upgrade to the latest Recent Posts plugin. I'll be back with info
Member — 27th January 2012 #
Barry, sorry to say but I still get *alot* of slow queries logged on that table.
Can I somehow truncate it a bit? We really only need the 10 latest posts at all time. Now it's around 222,000 rows in it
Thanks
Member — 27th January 2012 #
I truncated the table wp_site_posts
The new indexes made performance worse somehow?!
So, now I run the latest 2.1 version and all seems ok this far. The wp_site_posts table is populating again
Developer — 27th January 2012 #
They may have still been building :/ though it shouldn't take that long.
Let me know the queries that are really slow for you when you can and I'll see what I can do either in the plugin or index wise to get them running quicker.
Member — 27th January 2012 #
Hi Barry, since I truncated wp_site_posts there are no more slow queries logged so far. Will have a look tomorrow
Member — 31st January 2012 #
Barry, since I truncated the wp_site_posts table performance is still good from the recent posts plugin.
Think we can close this issue for now
Thanks
Tomas
Become a member