MySQL created tmp disk tables performance issues

Hi All!

this is my first post here. It's not about direct support with plugins or themes, but i would like to ask expert people using wordpress mu.

I've a blog network (40 blogs) with wordpress mu and more/less 100k visits a day, 150k pageviews/day, webserver and msyql on the same server with varnish as reverse proxy (same server).

The problem is that our server (2xQuad, 4G Ram, SAS HD) is having performance issues on some peaks and mysql use almost all the CPU and some queries takes many seconds so pages load slowly.

Running some mysql tuning tools always get the same report: problems with temporary tables created on disk.

For example, running mysql tuner from http://mysqltuner.com/ i get:

Temporary tables created on disk: 42% (343K on disk / 803K total)

My mysql configuration is:

tmp_table_size = 96M
max_heap_table_size = 96M
table_cache = 2048

Please, based on your experience, what should be a common ratio for temporary tables on disk?

Wordpress with no plugins or themes has this problem or is a plugin/theme cause?

i'm looking for a point to start checking

thanks!

  • drmike

    Greets:

    Are you running any sort of page or data caching with your mu install? What version of mu are you running?

    To be honest, I've not seen this issue before but my boxes are managed. Also a quick google doesn't pull that specific error phrase up although I see a lot of general discussion on the topic. (especially with drupal)

    I wish the tuner site gave some details about result codes. (Thanks for the link by the way.)

    edit: Also how often do you optimize your dbs?

  • Luke

    Forgive me for being broad on this, but there are a ton of factors for tmp tables on disk.

    The biggest cause, usually, for tmp tables on disk is for queries which cannot be executed using proper indexes or on occasion queries with rather "wild" grouping and/or order by clauses.

    The WordPress, and inherently MU, core(s) are fairly well groomed in terms of most of the queries. I will note that there are on rare occasion a few which could be better. However, they paid MySQL to groom their queries in a recent version (was it 2.5?), so as a whole the queries are as efficient as they can be.

    First thing to do would be to turn on the slow-queries log file for MySQL. This will let you know which queries are performing poorly, and give you a starting place.

    The first place to look in terms of code would be plugins. Double check their queries. There are a lot of plugin editors out there who can make some functions do something, but MySQL isn't exactly their forte.

    I can think of one plugin off the top of my head that is absolutely horrible with queries, the "folding category" plugin. It is absolutely awful.

    Also look at the number of queries per page. I've always found it funny that something like SMF (the forum) can get about as much data as WordPress, but do it in far fewer queries. An average WordPress page may have 100 (or more) queries executed, where SMF would have 30 or less (more like 20 or less, usually).

    Both programs are pulling similar data, and out of the box WP will pull about 30-40 queries, with SMF at about 10.

    There is one little thing to watch. The more queries you run, the better the odds that something is going to cause a tmp table to be created.

    Also, one big thing with MySQL is memory. You said you have 4G on your box. That's for the OS, programs, PHP, web server, and MySQL. How much total are you passing to MySQL for use? After everything else takes a chunk, how much is left? I would think that with 4G you could probably allow 2G for MySQL.

    Back to the tmp tables on disk though, I'd guess (without more amplifying information) that it's non-indexable queries causing the problem. MySQL runs a query, can't effectively use indexes, so it has to put information to a tmp table. If there isn't enough RAM available, it ends up on disk. No matter what you do, usually, you'll have a few tmp tables. The 42% is pretty high, so that would lead me to think about queries.

    One valuable asset in MySQL is the EXPLAIN statement. Add that to the beginning of any SELECT query, and it will describe to you the "thought process" that MySQL goes through to get the specific results for a query.

    Example: "EXPLAIN SELECT post_title, guid FROM wp_1_posts WHERE post_type = 'post' AND post_status = 'publish';"

    Example 2: "EXPLAIN SELECT post_title, guid FROM wp_1_posts WHERE ping_status = 'open' AND comment_status = 'open';"

    Run those two queries in PhpMyAdmin, or from the command line, and compare the resulting data. Note the difference in the WHERE clauses of the two examples above. The WHERE clause is crucial to effective queries, as the result of the two examples will show.

    For reference: MySQL Manual: EXPLAIN

    Also, what are your loads looking like, through top?
    Is it time to perhaps look at scaling your solution?

    Do you have caching in place? Of all those page views, how many are from users who are not logged in? Super Cache may help you out a bit, as would memcached. While those won't solve the underlying problem, it will still need addressed, it should at least help your load times in the interim.

  • Blogestudio

    wow, thanks all for the help

    Drmike, I will explain a little bit more about the configuration:

    I'm using 256 Mb for memcache with object-cache and queries per second decreased form 600 qps to 200 qps.

    I'm testing now nginx instead of apache and load average decreased a little.

    I'm using wp-supercache in half-mode (I wasn't able to configure the redirects for full-mode in nginx, yet

    I'm using wpmu 2.7 since 2 or 3 days ago, but the problem was the same with 2.6.5

    I'm optimizing the db's every 2 or 3 days.

    Ovidiu, thanks, yes i know mysqlreport and use it sometimes. Also mysql activity report and munin for monitoring

    Luke, load average is from 1 to 10 (during peaks), the problem is the time you have to wait until the webpage start loading because mysql is very busy (sometimes until 10 seconds). And disk i/o reports time waiting, so that's why i think mysql creating disk tables maybe the main problem.

    I'm using 1G for mysql, and key_buffer_size is 256M (i've 220M indexes, so it's enough and some space for grow)

    Do you think a 150k pageviews a day installation need scaling?

    I will have a look with explain and querys with the plugins I'm using.

    And i also read somewhere that mysql folks helped wordpress with performancing and queries.

    Thanks again!

  • Luke

    It sounds like disk i/o is one of the issues, which could be from a combination of things.
    MySQL and the tmp tables, and potentially object-cache if you're using that from disk with that much traffic.

    You did mention SAS drives, which to me means 10k+ RPM, which is better than a 7200 any day. Still you have a lot of i/o, when there shouldn't be "that" much. Not enough to bring it down.

    A good starter would be the slow query log, setting slow queries to be something like 2 seconds or more. Any queries which pop up, and are selects, run the explain on them and see if they can be better optimized.

    Not sure what all you have running, or if it's feasible, but if you can isolate a couple of the slowest blogs and then turn off plugins, that might help track something down too.

    Also, how much stuff is in mu-plugins? Files in mu-plugins should be kept to as bare of a minimum as possible. I even tend to go so far as to break everything into directories, admin and public. Then have a single file in mu-plugins that checks for the admin side or public side, and then include the files from whichever it is. This approach can be tedious if you run a big plugin out of mu-plugins, but as an example, if you can save a couple hundred k by not loading admin code (that doesn't get used) on the public side, that adds up to a lot over a lot of requests. With MU, everything helps.

    One area I hadn't thought of is files. Are you serving a lot of /files/ requests on pages?

    While anything other than the tmp tables is (most likely) minor, with MU everything adds up. Everything that you can make static (or cache to memcached, etc) is going to help. That's more of an icing on the cake, to make it super over good, but helps nonetheless.

  • Blogestudio

    thanks luke!

    the SAS disks are 15 RPM (2x146Gb in mirror, raid 1)

    I'm using object cache but the memcache version, not the disk one.

    I'm using wp-supercache (half mode) and i didn't see more than 5.000/9.000 files in disk in the 7.200 seconds period (expire time margin) for the 40 blogs in wpmu. I would like to use batcache with memcache but we're having some issues with nginx.

    MU plugins, i've 11 plugins there:

    these 7:

    * Add meta tags
    * A modified version of check behind proxy. As we are using varnish all requests come from 127.0.0.1. With this plugin we have all 'real IP' logged (comments, etc.)
    * Plugin commander
    * A modified verion of Domain mapping
    * Unfiltered MU
    * subdir-plugin
    * wp-supercache

    and 4 done by us:

    * One to deactivate flash uploader in wp-admin with this code:
    function be_deactivate_flash_browser__flash_uploader() {
    return false;
    }
    add_filter('flash_uploader', 'be_deactivate_flash_browser__flash_uploader');

    * Replace í with &iacute. Kses.php has some problems with the spanish letter í
    $textsToReplace[] = array(
    "replacethis" => chr(195).chr(173),
    "with" => "í",

    * Remove update notifications:
    remove_action( 'admin_notices', 'update_nag', 3 );

    * an other plugin to add two new fields in addition to title (pretitle and subtitle) in the new post form in wp-admin.

    should i move some plugins to /plugins/ folder instead of the /mu-plugins/ one?

    is better to have plugins in /plugins/ instead of mu-plugins and activate them to everyblog with plugin commander?

    I have been monitoring the mysql server in different moments (with normal load and peak load)

    In normal load for a 60 seconds sample i've load average 1, 240 Created_tmp_disk_tables and 300 Created_tmp_tables

    During a 60 seconds sample during peaks load i've 1986 Created_tmp_disk_tables and 2019 Created_tmp_tables.

    I've been monitoring and analyzing the mysql-slow log and i thing i've 2 plugins that are causing this. I'm going to deactivate them and monitor again a sample period to compare.

    About the /files/ requests, i've a reverse proxy with varnish caching the static, it's reducing from 200 requests/second to only 20.

    thanks again!

  • Luke

    OK, so varnish is supplying the static files, that's good. I've never personally been a big fan of the blogs.php translation file for serving static files like that.

    It looks like you're at least headed in the right direction though.

    Did you run an EXPLAIN on the ones in the slow query log? Are they using indexes?
    One thing that appears to be happening is that some of the tmp tables are created in memory, and then some to disk. RAM is always going to be faster than disk, but the less tmp tables you have the better MySQL will behave.

    Some of those little plugins you have, could you combine them into a single file? Not that it's your issue, but it's one of those little things that will help over the long run. I say that as it appears that you have the "remove" type things in separate files. For a few lines like that, it's generally more efficient to combine them into one file and only read in that one file instead of several smaller ones.

    For example purposes, here's my tmp tables section from MySQL Report on one of my servers:

    __ Created Temp ________________________________________________________
    Disk table 2.37k 0.2/s
    Table 2.71k 0.3/s Size: 64.0M
    File 8 0.0/s

    As a note, that MySQL instance has been running for almost 3 months since the last restart so it has a bit of data to work with.

    This particular server has 3 MU instances running on it, one with about 75k page views a day, plus a few other sites (some WordPress, some run a CMS) on it.

  • Blogestudio

    my mysql report from the last restart (saturday morning, 57 hours)

    __ Created Temp ________________
    Disk table 576.00k 2.8/s
    Table 787.65k 3.9/s Size: 96.0M
    File 4 0.0/s

    it's a little bit different from yours

    this mysql instance it's only serving the wpmu (on the same server than varnish, memcache, nginx, etc.)

    i'm going to use EXPLAIN with the queries, i'll let you know.

    and yes, i'm going to combine those plugins in only one file, thanks.

    are you running the webserver and wpmu in the same server than that instance?

    thank you very much!

  • Luke

    That particular server is running everything together, yes.

    That explain option on the queries which are slow should help. Typically they're slow for one of a couple reasons.

    a) Amount of content being pulled.

    b) Format of the query (as in it can't use indexes, and has to create tmp tables)

    c) It executes but has to wait on table locks (MyISAM vs InnoDB kinda thing)

    Granted I'm generalizing this, but it's a bit difficult to get into specifics without explicit details, etc.