db performance on recent posts

Hi, we display 10 recent posts from across the blogs in every blogs sidebar.
I can see that MySQL logs these slow queries repetedly:

# Time: 111109 15:26:28
# User@Host: dbuser[dbuser] @  [217.25.39.14]
# Query_time: 17.410294  Lock_time: 1.244156 Rows_sent: 0  Rows_examined: 196877
SET timestamp=1320848788;
SELECT site_post_id FROM wp_site_posts WHERE post_id = '186436' AND blog_id = '2317';
# User@Host: dbuser[dbuser] @  [217.25.39.16]
# Query_time: 11.586513  Lock_time: 0.000072 Rows_sent: 10  Rows_examined: 196887
SET timestamp=1320848788;
SELECT * FROM wp_site_posts WHERE blog_public = '1' ORDER BY post_published_stamp DESC LIMIT 10;
# User@Host: dbuser[dbuser] @  [217.25.39.14]
# Query_time: 16.620471  Lock_time: 0.000100 Rows_sent: 0  Rows_examined: 196877

I'm no expert in this are but would you say we have performance issues?

Thanks
Tomas

  • Heydar
    • Flash Drive

    Hi again, I still get *alot* of these:

    SET timestamp=1327477003;
    SELECT * FROM wp_site_posts WHERE blog_public = '1' AND post_type = 'post' ORDER BY post_published_stamp DESC LIMIT 10;
    # User@Host: dbuser[dbuser] @  [ip adddress]
    # Query_time: 708.229000  Lock_time: 360.431317 Rows_sent: 10  Rows_examined: 238423
    SET timestamp=1327477003;
    SELECT * FROM wp_site_posts WHERE blog_public = '1' AND post_type = 'post' ORDER BY post_published_stamp DESC LIMIT 10;
    # User@Host: dbuser[dbuser] @  [ip adddress]
    # Query_time: 535.052889  Lock_time: 187.200948 Rows_sent: 10  Rows_examined: 238423

    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

  • aecnu
    • WP Unicorn

    Greetings Tomas :slight_smile:

    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 :slight_smile: and should also minimize the database calls.

    http://wordpress.org/extend/plugins/db-cache-reloaded-fix/

    Hope this helps :slight_smile:

    Joe :slight_smile:

  • Heydar
    • Flash Drive

    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 :slight_smile:

    Maybe DEV can chip in?

    Thanks
    Tomas

  • aecnu
    • WP Unicorn

    Greetings Tomas :slight_smile:

    we are getting to the bottom of what the problem is :slight_smile: and a another Dev can jump in at anytime :slight_smile:

    Here on WPMU Dev we are all developers including yourself sir or we would not be here :slight_smile:

    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 :slight_smile:

    Joe :slight_smile:

  • Heydar
    • Flash Drive

    Hi Joe, yes I understand :slight_smile: 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

  • aecnu
    • WP Unicorn

    Greetings Tomas :slight_smile:

    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 :slight_smile:

    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 :slight_smile:

    Sorry I could not be of further help with this issue :-S

    Joe :slight_smile:

  • Barry
    • DEV MAN’s Mascot

    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)

  • Heydar
    • Flash Drive

    Hi Barry, this is the current indexes on that table:

    mysql> SHOW INDEX FROM wp_site_posts FROM blogs_global;
    +---------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table         | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +---------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    | wp_site_posts |          0 | PRIMARY   |            1 | site_post_id | A         |      239079 |     NULL | NULL   |      | BTREE      |         |
    | wp_site_posts |          1 | post_type |            1 | post_type    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
    +---------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
    2 rows in set (0.00 sec)
  • Barry
    • DEV MAN’s Mascot

    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.

  • Barry
    • DEV MAN’s Mascot

    The new indexes made performance worse somehow?!

    They may have still been building :disappointed: 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.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.