tmp_table_size with mostly InnoDB tables

Question

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two small Wordpress blogs. I wasn't trying to get too deep into optimizing this but I encountered what I consider to be strange behavior.

When I run MySQLTuner 1.5.0, I get the following output:

[OK] Currently running supported MySQL version 5.6.25-0ubuntu0.15.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics-------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 7M (Tables: 30)
[!!] Total fragmented tables: 3

-------- Performance Metrics -----------------------------------------
[--] Up for: 10d 13h 0m 49s (86K q [0.095 qps], 3K conn, TX: 218M, RX: 26M)
[--] Reads / Writes: 73% / 27%
[--] Binary logging is disabled
[--] Total buffers: 296.0M global + 1.1M per thread (75 max threads)
[OK] Maximum reached memory usage: 302.8M (15.20% of installed RAM)
[OK] Maximum possible memory usage: 380.4M (19.10% of installed RAM)
[OK] Slow queries: 0% (0/86K)
[OK] Highest usage of available connections: 8% (6/75)
[OK] Aborted connections: 0.34%  (11/3243)
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/109.0K
[OK] Read Key buffer hit rate: 96.7% (30 cached / 1 reads)
[OK] Query cache efficiency: 45.3% (27K cached / 60K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 81% (2K on disk / 2K total)
[OK] Thread cache hit rate: 99% (6 created / 3K connections)
[OK] Table cache hit rate: 76% (115 open / 150 opened)
[OK] Open file limit used: 0% (48/16K)
[OK] Table locks acquired immediately: 100% (41K immediate / 41K locks)

-------- InnoDB Metrics ----------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/7.4M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 15.19% (1244 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.93% (1295836 hits/ 1296742 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12926 writes)

-------- Recommendations ---------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

My problem is the tmp_table_size (and max_heap_table_size). Every week I've been increasing it and MySQLTuner keeps suggesting larger. (As you can see, it now proposes >128M.) I can see from the above output that it's writing tables to disk, which I assume is the reason for the suggestion. However, in my understanding, this only relates to the MyISAM engine, while all of my Wordpress tables are using the InnoDB engine. I assume there are some internal MySQL tables that may be using MyISAM, but I still don't know why MySQLTuner thinks I need such a massive table size just for that minimal stuff.

So, the question: Why does MySQLTuner think I need such large temp tables, when I use only InnoDB?

Answer 1

Because your mysql is still creating disk tables: [!!] Temporary tables created on disk: 81% (2K on disk / 2K total)

Looking at the source of tuner-script

$mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100);

*snip*

if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
    push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
    push(@generalrec,"When making adjustments, make tmp_table_size/max_heap_table_size equal");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
} elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@generalrec,"Temporary table size is already large - reduce result set size");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");

So there's tuner's logic, if the (Created_tmp_disk_tables/Created_tmp_tables)*100 is more than 25(%) then increase your tmp-table size (upto max 256M).

Again, mysqltuner is for reference, if you don't see performance issues you don't need to blindly follow the suggestions.

Also note that tmp table has nothing to do with InnoDB vs MyISAM (if you mean that in your last line). You might want to read about internal temporary tables.

Answer 2

Do not use any suggestions from mysqltuner unless you understand what they mean - mostly they are unfortunately nonsense.

There is a page in manual covering internal usage of tmp tables. I will try to give a short overview:

• automatically created on-disk tables use MyISAM engine, you can not change that to InnoDB afaik, those in memory use MEMORY engine
• your queries probably really use mostly on-disk temp tables, but values of tmp_table_size and max_heap_table_size are not the only limit for deciding if table can be created in memory - MEMORY engine does not support some features which may be needed in the temporary table (BLOB/TEXT columns, too long varchars in SELECT or GROUP BY..) so for some queries it is just not possible to execute them without creating a temporary on-disk table.

Probably Wordpress uses such queries and tables that MySQL has no chance to actually use the MEMORY engine in most cases.