Percona Toolkit

Компания Percona имеет в своем арсенале кучу утилит для работы с базой под названием percona-toolkit.

[urlspan]pt-archiver[/urlspan] - Архивирование строк из таблицы MySQL в другую таблицу или файл.

[urlspan]pt-variable-advisor[/urlspan] — Вызов: pt-variable-advisor localhost.[urlspan]pt-mysql-summary[/urlspan] — Удобный и краткий обзор состояния и конфигурации сервера базы данных MySQL. Это не инструмент настройки или диагностики. Это отчет, который легко может быть вставлен в сообщения электронной почты без потери форматирования. Должен хорошо работать на любых современных системах UNIX.[urlspan]pt-summary[/urlspan] — Удобный и краткий обзор состояния и конфигурации сервера базы данных MySQL. Это не инструмент настройки или диагностики. Это отчет, который легко может быть вставлен в сообщения электронной почты без потери форматирования. Должен хорошо работать на любых современных системах UNIX.[urlspan]pt-diskstats[/urlspan]    печатает диск I / O для статистики GNU / Linux. Это несколько похоже на IOSTAT, но это является интерактивным и более подробно. Она может анализировать образцы, собранные с другой машины.

pt-query-advisor — Анализ запросов и консультаций по возможным проблемам.
Вызов: pt-query-advisor /var/log/mysql/mysql-slow.log — u username -p password.
pt-ioprofile —
pt-deadlock-logger — Информация о deadlock'ах
pt-variable-advisor. С помощью этой утилиты вы можете проанализировать вашу текущую конфигурацию, и получить рекомендации по настройке параметров. pt-variable-advisor проверит вашу конфигурацию на основе ряда правил, заданных в Percona. Для того, чтобы посмотреть полный список правил, можно глянуть в [urlspan]их справку[/urlspan].

Давайте запустим утилиту на стандартной конфигурации MySQL, и посмотрим на ее рекомендации:

# ПРЕДУПРЕЖДЕНИЕ delay_key_write: Блоки индексов MyISAM очищаются только при необходимости.
# ПРЕДУПРЕЖДЕНИЕ innodb_log_file_size: Размер файла лога InnoDB установлен размером по умолчанию, что не подходит для продакшен-серверов.
# ЗАМЕЧАНИЕ log_warnings-2: Значение должно быть выставлено больше 1, чтобы в лог попадали только особые события, напримр, обрыв соединения.
# ЗАМЕЧАНИЕ max_binlog_size: Параметр max_binlog_size меньше, чем значение по умолчанию в 1Гб.
# ЗАМЕЧАНИЕ max_connect_errors: Параметр стоило бы выставить в максимальное значение, которое может позволить ваша платформа.
# ПРЕДУПРЕЖДЕНИЕ slave_net_timeout: Параметр выставлен в слишком большое значение.
# ЗАМЕЧАНИЕ sort_buffer_size-1: Переменную лучше оставить в том значени, с каким она идет по умолчанию. Необходимость изменения может подсказать только специалист.
# ЗАМЕЧАНИЕ innodb_data_file_path: Автоматическое увеличение размеров файлов InnoDB может привести к увеличению используемого дискового пространства, что довольно тяжело исправить позже.
# ЗАМЕЧАНИЕ innodb_flush_method: Для большинства продакшен-серверов, использующих InnoDB, переменная innodb_flush_method должна быть выставлена в O_DIRECT, чтобы избежать двойной буферизации, кроме случаем, когда производительность системы ввода-вывода страдает.
# ПРЕДУПРЕЖДЕНИЕ log_bin: Двоичное логирование отключено, так что функции восстановления и репликации отключены.

На основе данных замечаний можно начинать улучшение конфигурации. Для более детализированного описания каждой переменной можно обратиться к документации MySQL.

MySQLTuner

[urlspan]MySQLTuner[/urlspan] — еще одна утилита из набора Perkona Toolkit. Эта утилита проанализирует быстродействие вашего MySQL сервера, и предложит варианты его улучшения. По-хорошему, запускать данную утилиту стоит тогда, когда ваш MySQL сервер проработал несколько дней. После изменения конфигурации надо подождать еще несколько дней, и запустить утилиту снова.

Давайте запустим ./mysqltuner.pl и посмотрим, как себя показывает текущая конфигурация MySQL.

>> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.26-74.0-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 12M (Tables: 214)
[--] Data in InnoDB tables: 48M (Tables: 79)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 45

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12d 12h 55m 38s (2M q [2.677 qps], 225K conn, TX: 20B, RX: 899M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 288.0M global + 238.0K per thread (22 max threads)
[OK] Maximum possible memory usage: 293.1M (58% of installed RAM)
[OK] Slow queries: 2% (72K/2M)
[!!] Highest connection usage: 95% (21/22)
[OK] Key buffer size / total MyISAM indexes: 24.0M/3.1M
[OK] Key buffer hit rate: 99.2% (4K cached / 34 reads)
[OK] Query cache efficiency: 86.4% (1M cached / 1M selects)
[!!] Query cache prunes per day: 393
[OK] Sorts requiring temporary tables: 0% (61 temp sorts / 7K sorts)
[OK] Temporary tables created on disk: 19% (6K on disk / 33K total)
[OK] Thread cache hit rate: 99% (21 created / 225K connections)
[OK] Table cache hit rate: 96% (403 open / 416 opened)
[OK] Open file limit used: 0% (478/245K)
[OK] Table locks acquired immediately: 100% (168K immediate / 168K locks)
[OK] InnoDB buffer pool / data size: 56.0M/48.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
 Run OPTIMIZE TABLE to defragment tables for better performance
 Reduce or eliminate persistent connections to reduce connection usage
Variables to adjust:
 max_connections (> 22)
 wait_timeout (< 90)
 interactive_timeout (< 90)
 query_cache_size (> 96M)

Сначала мы видим результаты теста MySQLTuner. На основе этих результатов MySQLTuner дает некоторые рекомендации по изменению конфигурации, что приведет к улучшению производительности базы данных. Прошу принять во внимание, что конфигурация бузы данных — это всегда метод проб и ошибок, и проверка, что все работает как надо. Также не забудьте вернуться к проверке базы данных этим скриптом через несколько дней после изменения конфигурационных параметров, чтобы проверить, улучшилась ли ситуация, и не нужны ли дополнительные настройки.

 tuning primer

Качаем ее и запускаем

На все запросы отвечаем «Y»  и нажимаем Enter и вводим логин и пароль администратора MySQL

в папке пользователя будет создан файл .my.cnf  с указанными логином и паролем.

Смотрим рекомендации и выполняем.

Сравнение вашей конфигурации на нескольких серверах

Если вы работаете с несколькими серверами MySQL, вы, возможно, хотите, чтобы конфигурации на всех серверах были одинаковы. Ручное сравнение конфигурации может быть довольно утомительным. К нашему счастью, у Percona есть утилита

[urlspan]pt-config-diff[/urlspan].

В базовом варианте утилита принимает два файла, и сравнивает их. Возьмем за основу два следующих примера:

[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1
[mysqld]
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 0

Если мы запустим команду pt-config-diff /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf, то мы получим примерно такой ответ:

1 разница в конфиге
Переменная                 /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf
log_queries_not_using_... 1           0

Использованный пример, разумеется, очень прост. Но если вам необходимо оптимизировать множество серверов, то данная утилита может прийти вам на помощь, когда необходимо настраивать большое число переменных.