I have MySQL 5.7.23 on Ubuntu 18.04. It's a VPS with 2 vCores and 8 GiB of RAM. The server is hosting two WordPress websites. Each website has a database with the size of 7,5 MB. So there is 15 MB database overall.
I was trying to optimize the database using mysqltuner. It recommends me everytime the same thing:
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
I started with 16M, then I was recommended to increase the value to 32M and now it recommends me to increase the value to 64M or more. But why? Total size of all my databases is ~15M.
I have enough memory, free memory is around 6,5 GiB so I can use much more memory for MySQL.
The complete log from mysqltuner is
root@web:~# perl mysqltuner.pl
>> MySQLTuner 1.7.10 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.23-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ---------------------------------------------- --------------------
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log
-------- Storage Engine Statistics --------------------------------------------- --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My ISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 14.2M (Tables: 45)
[OK] Total fragmented tables: 0
-------- Security Recommendations ---------------------------------------------- --------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is acti vated
-------- CVE Security Recommendations ------------------------------------------ --------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics --------------------------------------------------- --------------------
[--] Up for: 1d 2h 43m 42s (84K q [0.879 qps], 1K conn, TX: 441M, RX: 12M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 384.4M
[--] Other process memory: 252.8M
[--] Total buffers: 224.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 228.3M (2.86% of installed RAM)
[OK] Maximum possible memory usage: 384.4M (4.82% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/84K)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Aborted connections: 0.00% (0/1561)
[OK] Query cache is disabled by default due to mutex contention on multiprocesso r machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 44% (3K on disk / 7K total)
[OK] Thread cache hit rate: 99% (4 created / 1K connections)
[OK] Table cache hit rate: 77% (1K open / 1K opened)
[OK] Open file limit used: 1% (56/5K)
[OK] Table locks acquired immediately: 100% (202 immediate / 202 locks)
-------- Performance schema ---------------------------------------------------- --------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ---------------------------------------------------- --------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics -------------------------------------------------------- --------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[OK] Read Key buffer hit rate: 96.3% (190 cached / 7 reads)
-------- InnoDB Metrics -------------------------------------------------------- --------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/14.2M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 16.0M * 2/128.0M shou ld be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb _buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.96% (2124360 hits/ 2125302 total)
[!!] InnoDB Write Log efficiency: 83.91% (9286 hits/ 11067 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1781 writes)
-------- AriaDB Metrics -------------------------------------------------------- --------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics -------------------------------------------------------- --------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics -------------------------------------------------------- --------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ------------------------------------------------------- --------------------
[--] RocksDB is disabled.
-------- Spider Metrics -------------------------------------------------------- --------------------
[--] Spider is disabled.
-------- Connect Metrics ------------------------------------------------------- --------------------
[--] Connect is disabled.
-------- Galera Metrics -------------------------------------------------------- --------------------
[--] Galera is disabled.
-------- Replication Metrics --------------------------------------------------- --------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ------------------------------------------------------- --------------------
General recommendations:
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
You should take Mysqltuner recommendations with a grain of salt. Its suggestions are not made with any knowledge of your database or your queries, and sometimes they are just guesses based on incomplete information.
Edit: Having seen more cases of erroneous advice given by Mysqltuner, I caution more strongly against using it. Just delete Mysqltuner from your computer and forget it ever existed.
The suggestion to increase the tmp_table_size might be based on reading the status values that indicate some temp tables were created on disk.
mysql> show global status like 'created%tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 35 |
| Created_tmp_tables | 274 |
+-------------------------+-------+
"Aha!" says mysqltuner, "13% of your temp tables were on disk. Those might have stayed in RAM if we allowed larger temp tables to stay in RAM. Double the tmp_table_size!"
But some temp tables are always created on disk.
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html says:
Some query conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:
Presence of a BLOB or TEXT column in the table. This includes user-defined variables having a string value because they are treated as BLOB or TEXT columns, depending on whether their value is a binary or nonbinary string, respectively.
Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used.
The SHOW COLUMNS and DESCRIBE statements use BLOB as the type for some columns, thus the temporary table used for the results is an on-disk table.
How could mysqltuner tell the difference between temp tables that went to disk because they were too large, versus temp tables that went to disk because of one of the conditions above?
It can't tell the difference. It would have to read your query log and analyze each query, but it doesn't do this. It only knows the count of how many temp tables were on disk, not why.
Mysqltuner only assumes that a temp table was stored on disk because it was larger than tmp_table_size, and it assumes that there will be some benefit to increasing tmp_table_size.