Оптимизация настроек MySQL с помощью MySQLTuner

Не беда, если вы как и я не являетесь большим специалистом в области понимания тонкостей настроек MySQL. Специально для таких людей существует маленький, но очень полезный скрипт на языке Perl — MySQLTuner. Он хитрым образом анализирует статистику работы MySQL и выдает свои рекомендации по оптимизации настроек сервера.

Установка под Debian крайне проста.

apt-get install mysqltuner

Чтобы данные анализа были более корректными, сервер MySQL должен проработать некоторое время в боевом режиме, по рекомендации самого MySQLTuner не менее 24 часов. Желательно запускать скрипт с пользователем root.

mysqltuner --user root --pass xxxxxx

После секундного раздумья программа выдает примерно следующую информацию. Нас интересуют строки, помеченные символами [!!] и секция Recommendations.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-0+squeeze1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 989)
[--] Data in InnoDB tables: 98M (Tables: 275)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 374

-------- Performance Metrics -------------------------------------------------
[--] Up for: 79d 1h 41m 0s (874M q [128.015 qps], 38M conn, TX: 14013B, RX: 163B)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 90.0M global + 3.1M per thread (200 max threads)
[OK] Maximum possible memory usage: 702.5M (2% of installed RAM)
[OK] Slow queries: 0% (187/874M)
[OK] Highest usage of available connections: 85% (171/200)
[OK] Key buffer size / total MyISAM indexes: 16.0M/808.4M
[OK] Key buffer hit rate: 98.0% (23B cached / 472M reads)
[OK] Query cache efficiency: 59.5% (269M cached / 453M selects)
[!!] Query cache prunes per day: 189628
[OK] Sorts requiring temporary tables: 0% (8K temp sorts / 17M sorts)
[!!] Joins performed without indexes: 612253
[!!] Temporary tables created on disk: 29% (2M on disk / 9M total)
[OK] Thread cache hit rate: 99% (36K created / 38M connections)
[!!] Table cache hit rate: 0% (256 open / 5M opened)
[OK] Open file limit used: 34% (356/1K)
[OK] Table locks acquired immediately: 99% (433M immediate / 434M locks)
[!!] InnoDB data size / buffer pool: 98.8M/8.0M

-------- 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
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 512.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_cache (> 256)
    innodb_buffer_pool_size (>= 98M)

Общие рекомендации в принципе можно пропустить, смотрим рекомендации по изменению переменных (Variables to adjust). Открываем файл /etc/mysql/my.cnf и изменяем указанные переменные в соответствие с рекомендациями MySQLTuner. При отсутствии особых познаний в тонкостях настройки, я просто увеличиваю указанные переменные в два раза, жду сутки и смотрю что скажет программа при следующем запуске. Для того, чтобы изменения вступили в силу нужно перезапустить MySQL.

/etc/init.d/mysql restart

При последующих запусках особое внимание обращаем на строку с максимально допустимым потреблением памяти.

[OK] Maximum possible memory usage: 702.5M (2% of installed RAM)

Необходимо чтобы процентное соотношение не было слишком высоким. При опасном превышении MySQLTuner выделит строчку красным и выдаст предупреждение.

Компактный Хендай Солярис в Барнауле пользуется спросом. Аккумулятор для ноутбука купить подробности на сайте.