Продолжаем серию публикацию статей по настройке веб-сервера для Drupal сайт и теперь переходим от настройки веб-сервера nginx, которую мы рассмотрели в предудущей статье, к настройке сервера баз данных MySQL в операционной системе Debian.

Установка

Установка MySQL 5.5 или 5.6

Сначала обновляем индекс пакетов:

apt-get update

Для установки MySQL 5.5:

apt-get install mysql-server-5.5

Для установки MySQL 5.6:

apt-get install mysql-server-5.6

Установка MySQL 5.7

Для установки  MySQL 5.7, необходимо добавить новый репозиторий APT с сайта MySQL https://dev.mysql.com/downloads/repo/apt/. Для этого нажмите Download, скопируйте ссылку в поле No thanks, just start my download и загрузите пакет .deb:

wget https://repo.mysql.com//mysql-apt-config_0.8.7-1_all.deb
dpkg -i mysql-apt-config_0.8.7-1_all.deb

Выбрав версию mysql-5.7, выберите в меню Apply и нажмите ENTER. Обновите индекс пакетов:

apt-get update

Установите пакет mysql-server, который содержит версию MySQL 5.7.

apt-get install mysql-server

Настройка MySQL

Изменение настроек в MySQL осуществляются в файле /etc/mysql/my.cnf

Настройка логирования

В MySQL четыре основных лога :

1. Error Log —  лог ошибок, которые собираются во время работы сервера Mysql;
2. Binary Log — лог всех команд изменения БД, необходим для репликации и восстановления после сбоя;
3. General Query Log — лог запросов;
4. Slow Query Log — лог медленных запросов.

Лог ошибок

Этот лог содержит ошибки и предупреждения, которые произошли во время работы сервера. Его анализируют в случае сбоя системы. В Debian по умолчанию все ошибки записываются в syslog или можно писать в отдельный лог-файл:

log_error=/var/log/mysql/mysql_error.log

Бинарный лог

В этот лог записываются все команды изменения базы данных, необходимо использовать для репликации и восстановления после сбоя.

Включается таким образом:

log_bin                 = /var/log/mysql/mysql-bin.log # Указывает расположение
expire_logs_days        = 5 # срок устаревания логов в днях
max_binlog_size         = 500M # максимальный размер файла

Лог запросов

Этот журнал содержит все SQL-запросы, информацию о подключениях клиентов:

general_log_file        = /var/log/mysql/mysql.log # расположение файла
general_log             = 1 # включение лога

Лог медленных запросов

До версии 5.7

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 1

Начиная с версии 5.7

slow_query_log          = /var/log/mysql/mysql-slow.log
long_query_time         = 1

• log_slow_queries (slow_query_log) указывает путь к файлу настроек
• long_query_time указывает минимальное время выполнения запроса, после которого он считается медленным в секундах,

Параметры MySQL

• bind-address — ip адрес, на котором слушать порт сервер MySQL. В целях безопасности рекомендуется установить здесь 127.0.0.1, если не используете внешние соединения с сервером.
   
• max_allowed_packet — максимальный размер данных, которые могут быть переданы за один запрос. По умолчанию значение 4MB. Рекомендуем для  работы Drupal увеличить до 128 MB или менять если столкнетесь с ошибкой «Packet too large».
   
• max_connections — максимальное количество параллельных соединений к серверу. Если возникает ошибка «Too many connections», то этот параметр необходимо увеличить.  По умолчанию значение 151.
   
• max_join_size — запрещает запросы, которые будут анализировать более max_join_size строк или больше указанного числа поисков по диску. С помощью этой переменной можно найти запросы, которые пытаются считать миллионы строк. 
   
• open_files_limit - количество файлов, которое операционная система позволяет использовать mysqld. Если возникает ошибка "Too many open files" необходимо увеличить значение этой переменной. В Unix значение не может быть установлено больше, чем ulimit -n .
   

• thread_cache_size — указывает число кэшируемых потоков. После обработки запроса сервер не будет завершать поток, а разместит его в кэше, если число потоков, находящих в кэше меньше, чем указанное значение. Значение по умолчанию 0, увеличьте его до 8 или сразу до 16. Если наблюдается рост значения переменной состояния Threads_Created, то следует еще увеличить thread_cache_size.

Кэш запросов

• query_cache_limit — максимальный размер запроса, который может быть закэширован. Для работы Drupal это значение можно ставить в 16Мб.

• query_cache_size — размер кэша запросов. Значение 0 отключает использование кэша. Для настройки этого параметра необходимо следить за переменной Qcache_lowmem_prunes и добиться, чтобы ее значение увеличивалось незначительно. Также необходимо иметь ввиду, что излишне большой кэш будет создавать ненужную нагрузку. Для работы Drupal это значение можно установить в 128Мб.

• query_cache_type — (OFF, DEMAND, ON). OFF отключает кэширование, DEMAND – кэширование будет производиться только при наличии директивы SQL_CACHE в запросе, ON включает кэширование.

• query_cache_wlock_invalidate — устанавливает будет ли результат запроса браться из кеша, если таблица, к которым он относится, заблокирована на чтение.

Медленные запросы

• log_slow_queries — указывает серверу путь к файлу для логирования медленных запросов. В качестве значения передается полное имя файла (например /var/log/slow_queries).

• long_query_time — медленный означает, что запрос выполняется дольше чем значение long_query_time. Значение устанавливается в секундах.

Таймауты

• interactive_timeout — время в течение которого сервер MySQL ожидает активности со стороны интерактивного соединения (использующего флаг CLIENT_INTERACTIVE), прежде чем закрыть его. Значение устанавливается в секундах. Например, этот параметр используется при работе через консольный клиент MySQL и если долго не было обращений к серверу, то он закрывает соединение.

• net_read_timeout — время в течение которого сервер MySQL будет ожидать получения данных, прежде чем соединение будет прервано. Значение устанавливается в секундах.

• net_write_timeout — время в течение которого сервер MySQL будет ожидать получения данных, прежде чем соединение будет прервано. Значение устанавливается в секундах.

• wait_timeout — время в течение которого сервер ожидает активности соединения, прежде чем прервет его. Значение устанавливается в секундах.

Буферы

• key_buffer_size — размер буфера, выделяемого под индексы и доступного всем потокам. Эта настройка влияет на производительность. Значение по умолчанию 8 МБ, рекомендуется 15-30% от общего объема ОЗУ. Устанавливать целесообразно не более чем общий размер всех .MYI файлов. 

• table_open_cache — количество кэшированных открытых таблиц. Следует учесть, что каждая запись в этом кэше использует системный дескриптор, поэтому иногда увеличивать ограничения на количество дескрипторов (ulimit). Переменная состояния Opened_tables позволяет отслеживать число таблиц, открытых в обход кэша.

• tmp_table_size — максимальный объём памяти, выделяемый для временных таблиц MySQL.

Все значение этих переменных задаются в байтах.

Параметры InnoDB

• innodb_buffer_pool_size — размер памяти, выделяемый InnoDB для хранения и индексов и данных. Можно рекомендуется увеличивать вплоть до общего размера всех InnoDB таблиц или до 80% ОЗУ, в зависимости от того, что меньше.

• innodb_flush_log_at_trx_commit — имеет допустимые значения: 0, 1, 2. При значении равном 0, лог сбрасывается на диск один раз в секунду, вне зависимости от происходящих транзакций. При значении равном 1, лог сбрасывается на диск при каждой транзакции. При значении равном 2, лог пишется при каждой транзакции, но не сбрасывается на диск никогда, оставляя это на совести ОС. По умолчанию используется 1, что является самой надежной настройкой, но самой медленной. Рекомендуется использовать значение 2, данные могут быть утеряны лишь в случае краха ОС и лишь за несколько секунд (зависит от настроек ОС). 0 — самый быстрый режим, но и самый ненадежный, данные могут быть утеряны как при крахе ОС, так и при крахе самого сервера MySQL.

• innodb_log_file_size — максимальный размер одного лог-файла. При достижении этого размера InnoDB будет создавать новый лог-файл. Для Drupal мы обычно используем значение 64Мб.

• innodb_file_per_table - При включении этой опции, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). Прироста в производительности не будет, однако есть ряд преимуществ:

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

• innodb_buffer_pool_instances - По умолчанию InnoDB использует один инстанс для Buffer Pool. Но MySQL позволяет выделить несколько экземпляров пула и работает с ними InnoDB в ряде случаев гораздо эффективнее. Имеет смысл выделять несколько экземпляров если innodb_buffer_pool_size более 2Гб. Размер каждого экземпляра стоит делать 1 Гб и более. При этом важно понимать, что innodb_buffer_pool_size — это общий размер пула. 

Инструменты оптимизации MySQL

• Percona Toolkit
• MySQLTuner
• Drupal модуль Performance Monitor