Оптимизация конфигурации MySQL

2014-04-23 0 ∞

Данная статья является 2 из 3 частей цикла «Оптимизация MySQL»:

Вступление
Заключение

Вступление

MySQL в сочетании с РНР является одним из наиболее часто используемых движков баз данных. Направить усилия на то, чтобы ваши базы данных MySQL работали наилучшим образом, это то, что вы должны сделать в первую очередь, когда веб-приложение начинает расти.

В этой части цикла статей мы рассмотрим, как мы можем оптимизировать конфигурацию MySQL. Я расскажу вам, что мы можем оптимизировать в конфигурации MySQL для увеличения производительности нашей базы, а также как мы можем найти потенциальные проблемы, когда MySQL работает не идеально.

Мы будем использовать в основном инструменты из Percona Toolkit. В этой статье я расскажу об оптимизации конфигурации.

Как изменить конфигурацию MySQL

Настройки конфигурации MySQL хранятся в файле my.cnf . В общем случае, вы можете найти конфигурационный файл по адресу /etc/mysql/my.cnf. При изменении файла конфигурации, чтобы запустить изменения, вам нужно будет перезагрузить сервер MySQL.

Однако если вы хотите внести изменения во время выполнения, можно использовать запросы SET GLOBAL и SET SESSION. Обратите внимание, что не все переменные конфигурации доступны для изменения во время выполнения, и данные изменения не будут постоянными.

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

Находим возможности для улучшения конфигурации

Percona Toolkit выпустили инструмент под названием pt-variable-advisor. С его помощью вы можете проанализировать текущую конфигурацию MySQL и получить обратные данные. pt-variable-advisor проверит конфигурацию на основе заданного набора правил, определяемого Percona Toolkit.

Чтобы увидеть полный список правил, вы можете зайти в раздел справки. Давайте запустим инструмент на чистом, только что установленном MySQL и посмотрим, какие обратные данные мы получим:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
(# ПРЕДУЖПРЕДЕНИЕ delay_key_write: Блок индекса MyISAM никогда не отключается без необходимости.)

# WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems.
# ПРЕДУПРЕЖДЕНИЕ innodb_log_file_size: Размера лог-файла InnoDB по умолчанию задан так, что это отражается на производительности системы.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.
# ОБРАТИТЕ ВНИМАНИЕ log_warnings-2: Значение предупреждений логов должно быть задано числом больше одного, чтобы система могла записывать логи необычных событий, таких как оборванные соединения.

# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.
# ОБРАТИТЕ ВНИМАНИЕ max_binlog_size: Значение max_binlog_size меньше, чем 1 ГБайт по умолчанию.

# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.
# ОБРАТИТЕ ВНИМАНИЕ max_connect_errors: значение max_connect_errors может быть установлено настолько большим, насколько позволяет ваша платформа.

# WARN slave_net_timeout: This variable is set too high.
# ПРЕДУПРЕЖДЕНИЕ slave_net_timeout: Задано слишком высокое значение переменной.


# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
# ОБРАТИТЕ ВНИМАНИЕ sort_buffer_size-1: Значение переменной sort_buffer_size, как правило, следует оставлять установленным по умолчанию, кроме тех случаев, когда эксперты считают, что им необходимо его изменить.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
# ОБРАТИТЕ ВНИМАНИЕ innodb_data_file_path: Автоматически расширяемые файлы InnoDB используют слишком много дискового пространства, и это позже очень трудно исправить.

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.
# ОБРАТИТЕ ВНИМАНИЕ innodb_flush_method: Для большинства серверов баз данных, использующих InnoDB следует задавать для innodb_flush_method значение O_DIRECT, чтобы избежать двойной буферизации, кроме I/O систем, которые имеют очень низкую производительность.


# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.
# ПРЕДУПРЕЖДЕНИЕ log_bin: Сейчас двоичное ведение журнала отключено, поэтому восстановление данных на определенный момент времени и репликация невозможны.

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

MySQLTuner

Еще один инструмент, который мы будем использовать, он не является частью Percona Toolkit, это MySQLTuner. Этот инструмент проанализирует производительность вашего сервера MySQL и предложит изменения.

Как правило, вам достаточно просто запустить этот инструмент, после того как ваш сервер MySQL уже проработал несколько дней. Спустя пару дней после внесения изменений в конфигурацию вы должны еще раз запустить его.

Давайте запустим ./mysqltuner.pl и посмотрим, насколько производительна наша текущая конфигурация MySQL:

>>  MySQLTuner 1.2.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.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 941M (Tables: 399)
[--] Data in InnoDB tables: 2G (Tables: 891)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 913

-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 1h 41m 1s (64K q [0.183 qps], 791 conn, TX: 119M, RX: 71M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 192.0M global + 2.8M per thread (151 max threads)
[OK] Maximum possible memory usage: 607.2M (10% of installed RAM)
[OK] Slow queries: 4% (3K/64K)
[OK] Highest usage of available connections: 3% (6/151)
[!!] Key buffer size / total MyISAM indexes: 16.0M/309.5M
[!!] Key buffer hit rate: 86.9% (14M cached / 1M reads)
[OK] Query cache efficiency: 58.9% (31K cached / 53K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 808 sorts)
[OK] Temporary tables created on disk: 6% (99 on disk / 1K total)
[OK] Thread cache hit rate: 99% (6 created / 791 connections)
[!!] Table cache hit rate: 6% (400 open / 5K opened)
[OK] Open file limit used: 3% (695/20K)
[OK] Table locks acquired immediately: 99% (47K immediate / 47K locks)
[!!] InnoDB data size / buffer pool: 2.0G/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 309.5M)
    table_cache (> 400)
    innodb_buffer_pool_size (>= 2G)

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

После чего вы можете начать оптимизацию, но, чтобы исправить определенные проблемы, практически всегда нужно пройти путь проб и ошибок. Кроме того, не забудьте повторно запустить этот скрипт через несколько дней.

И посмотреть, улучшилось ли состояние системы, или нужно дополнительное вмешательство.

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

Если вы работаете с несколькими серверами MySQL, вы возможно захотите сконфигурировать их все одинаково. Проверка вручную может потребовать много труда и времени. К счастью, в Percona существует инструмент под названием pt-config-diff.

С помощью него можно взять два конфигурационных файла и сравнить их. Рассмотрите следующие два примера:

[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 config difference
Variable                  /server1/etc/mysql/my.cnf /server2/etc/mysql/my.cnf
========================= =========== ====
log_queries_not_using_... 1           0

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

Заключение

Теперь вы понимаете, что важно не только следить за правильной работой базы данных важно также правильно настроить MySQL. Изучение каждой переменной и того, что она делает, может занять много времени. К счастью, у нас есть ряд инструментов, которые помогут справиться с этой колоссальной задачей.

Используете ли вы какие-либо инструменты для оптимизации конфигурации MySQL? Если да, то какие именно? Мне очень хотелось бы услышать ваши мнения в комментариях.

РедакцияПеревод статьи «Optimizing MySQL Configuration»