MySQL tmp_table_size для небольшой базы данных
У меня MySQL 5.7.23 на Ubuntu 18.04. Это VPS с 2 vCores и 8 ГБ ОЗУ. На сервере размещены два сайта WordPress. Каждый сайт имеет базу данных размером 7,5 МБ. Таким образом, общая база данных составляет 15 МБ.
Я пытался оптимизировать базу данных с помощью mysqltuner. Он рекомендует мне каждый раз одно и то же:
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
Я начал с 16M, затем мне рекомендовали увеличить значение до 32M, и теперь он рекомендует мне увеличить значение до 64M или более. Но почему? Общий размер всех моих баз данных ~15M.
У меня достаточно памяти, свободной памяти около 6,5 ГБ, поэтому я могу использовать гораздо больше памяти для MySQL.
Полный журнал от mysqltuner
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)
2 ответа
Вы должны принять рекомендации mysqltuner с небольшим количеством соли. Его предложения не делаются с какими-либо знаниями о вашей базе данных или вашими запросами, а иногда они просто предположения, основанные на неполной информации.
Предложение увеличить tmp_table_size может основываться на чтении значений состояния, которые указывают, что некоторые временные таблицы были созданы на диске.
mysql> show global status like 'created%tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 35 |
| Created_tmp_tables | 274 |
+-------------------------+-------+
"Ага!" говорит mysqltuner, "13% ваших временных таблиц были на диске. Они могли бы остаться в оперативной памяти, если бы мы позволили большим временным таблицам оставаться в оперативной памяти. Удвойте tmp_table_size!"
Но некоторые временные таблицы всегда создаются на диске.
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html говорит:
Некоторые условия запроса не позволяют использовать временную таблицу в памяти, и в этом случае сервер использует вместо нее таблицу на диске:
Наличие столбца BLOB или TEXT в таблице. Сюда входят пользовательские переменные, имеющие строковое значение, поскольку они обрабатываются как столбцы BLOB или TEXT, в зависимости от того, является ли их значение двоичной или недвоичной строкой, соответственно.
Наличие любого строкового столбца с максимальной длиной более 512 (байты для двоичных строк, символы для недвоичных строк) в списке SELECT, если используется UNION или UNION ALL.
Операторы SHOW COLUMNS и DESCRIBE используют BLOB в качестве типа для некоторых столбцов, поэтому временная таблица, используемая для результатов, представляет собой таблицу на диске.
Как mysqltuner может определить разницу между временными таблицами, которые пришли на диск, потому что они были слишком большими, и временными таблицами, которые пошли на диск из-за одного из условий выше?
Это не может отличить. Он должен будет прочитать ваш журнал запросов и проанализировать каждый запрос, но он этого не делает. Он знает только количество временных таблиц на диске, а не почему.
Mysqltuner только предполагает, что временная таблица была сохранена на диске, потому что она была больше, чем tmp_table_size, и предполагает, что увеличение tmp_table_size будет иметь некоторое преимущество.
Полезное правило: они должны занимать менее 1% оперативной памяти:
tmp_table_size
max_heap_table_size
Если вы сделаете их слишком большими, вы рискуете поменяться местами, что очень плохо скажется на производительности.
(Я с Биллом отрицательно отношусь к некоторым советам mysqltuner.)