MySQL: оптимизация с помощью mysqltuner

Mysqltuner представляет собой perl-скрипт, которы анализирует работу сервера MySQL и выдаёт соответствующие рекомендации.

Для запуска – его можно скачать:

# wget http://mysqltuner.com/mysqltuner.pl

Если возникает ошибка вида:

ERROR: cannot verify raw.github.com’s certificate, issued by ‘/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert High Assurance CA-3’:
Unable to locally verify the issuer’s authority.

Зпаускаем с ключём –no-check-certificate:

# wget http://mysqltuner.com/mysqltuner.pl –no-check-certificate

Во FreeBSD его можно установить из портов:

# cd /usr/ports/databases/mysqltuner && mske BATCH=yes install clean

В Debian/Ubuntu можно установить из репозиториев:

# aptitude -y install mysqltuner

Так же он устанавливается в CentOS/RHEL/Fedora:

# yum -y install mysqltuner

Для запуска достаточно выполнить:

# mysqltuner

Для удобства – можно сразу записать в лог:

# mysqltuner | tee /home/setevoy/musqltuner.txt

После чего утилита запросит логин/пароль пользователя root:

Please enter your MySQL administrative login:

и пароль:

Please enter your MySQL administrative password:

При первом запуске можно обойтись без ключей, а при повторных – добавить ключ –nogood, что бы убрать соощения в статусе ОК и оставить только проблемные:

В результате – получаем страницу с такими сообщениями:

# mysqltuner –nogood

>> MySQLTuner 1.0.1 – Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: debian-sys-maint
Please enter your MySQL administrative password:

——– General Statistics ————————————————–
[–] Skipped version check for MySQLTuner script

——– Storage Engine Statistics ——————————————-
[–] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 156B (Tables: 11)
[–] Data in InnoDB tables: 32K (Tables: 2)
[!!] Total fragmented tables: 2

——– Performance Metrics ————————————————-
[–] Up for: 9d 21h 39m 7s (1K q [0.002 qps], 105 conn, TX: 145K, RX: 123K)
[–] Reads / Writes: 14% / 86%
[–] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[!!] Query cache efficiency: 2.3% (3 cached / 133 selects)
[!!] Table cache hit rate: 8% (36 open / 421 opened)

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
table_cache (> 64)

Нас интересует блок Recommendations.

В частности – советуют запустить OPTIMIZE TABLE, включить логгирование медленных запросов (описание есть в статье MySQL: включаем лог медленных запросов) и увеличить параметр table_cache gradually.

Ниже ещё несоклько советов по настройке MySQL, которые можно добавить в файл конфигурации сервера MySQL – /etc/my.cnf или /etc/mysql/my.cnf:

установить параметр query_cache_limit более 1m (это тестовая плоащадка, потому значеине такое маленькое);

установить параметр table_cache более 64 таблиц.

На нагруженном сервере рекомендаций будет больше.

Например:

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)
innodb_buffer_pool_size (>= 80M)