Оптимизация работы MySQL под нагрузкой

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

Настройка сервера баз данных MySQL my.cnf, оптимизация работы сервера баз данных

В рамках статьи будут рассмотрены основы оптимизации работы MySQL при повышенной нагрузке

 

Оптимизация работы сервера баз данных MySQL при высокой нагрузке и MySQL my.cnf

Основной конфигурационный файл

mcedit /etc/mysql/my.cnf

Анализ логов сервера баз данных и использование инструментов диагностики производительности

 

Цели оптимизации:

  1. Достижение стабильной работы сервера
  2. Экономия на модернизации аппаратной части под растущие потребности проекта.

 

Когда веб проекты разрастаются и текущая конфигурация сервера перестает выдерживать нагрузки — нужно увеличивать объем RAM и выделать проекту больше ресурсов процессора CPU.

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

 

По умолчанию конфигурация mysql подразумевает следующие значения параметров

[mysqld]
set-variable = max_connections=500
safe-show-databases

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

 

Значение max_connections, как правило, ниже 500 устанавливать не стоит. Это объясняется тем, что стандартное количество child процессов (child process) для Apache — 512. Если все они будут использоваться, а в /etc/mysql/my.cnf будет задано значение менее 500 — часть пользователей будут видеть при обращении к ресурсу ошибки.

 

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

 

Дефолтные настройки во всех случаях нужно менять, без задаваемых непосредственно настроек сервер баз данных не сможет узнать как много оперативной памяти и ресурсов процессора используется. Работа серовера с 256Мб и 16Гб RAM сильно отличаются, для того чтобы обеспечить максимальную производительность MySQL нужна тонкая настройка параметров.

 

Переменные

MySQL CLI:

SHOW VARIABLES;

SSH (root)

mysqladmin variables или mysqladmin var

mysqladmin variables | grep name-of-variable

Параметры кэширования запросов, которые могут задаваться в my.cnf


thread_cache_size (default 0)

количество потоков, которое сервер должен кэшировать для повторного использования, хорошее значение для начала — 4


table_cache/tables_open_cache(default 64)

количество открытых таблиц для всех потоков


query_cache_limit (default 1M)

максимальный размер запроса который может быть помещен к кеш


query_cache_size (default 0)

поскольку здесь 0, следующий параметр работать не будет
4-8М хорошие значения, количество памяти для кэширования запросов


query_cache_type (default 1=on)

0=off, 2=on if needed

 

 

Буферы MySQL

key_buffer_size (default 8M)

буфер для индексов таблиц MYISAM, 24-48М нормальные значения

read_buffer_size (default 128K)

последовательный буфер потоков


join_buffer_size (default 128K)

используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size

 

 

Другие переменные

connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10)
количество секунд по просшествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать

max_connect_errors
(default 10)
максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:

FLUSH HOSTS;

В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать

max_allowed_packet (default 1M)

максимальный размер пакета, при подключении

tmp_table_size (system-specific default)

16М — довольно много
максимальный размер памяти выделяемой под хранение временных данных

 

 

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

thread_concurrency
используется только на Solaris, на linux mysql 5.5+ при использовании переменной MySQL не сможет запуститься

innodb_thread_concurrency

похожая на предыдущую переменная, является, тем не менее, совсем другой и относится к innodb

skip-locking
сейчас параметр называется skip-external-locking (по умолчанию используется начиная с MySQL 4.0 — непосредственное указание в конфиге не требуется)

 

 

Логирование MySQL

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

Без логирования единственным способом отслеживать состояние MySQL был бы мониторинг.

 

 

Есть три вида логов: логи ошибок, общие логи и логи медленных запросов.

Error-логи

По умолчанию включены

В общие логи пишется информация о любой активности процессов, обращающихся к таблицам баз данных — по умолчанию выключены.

Slow MySQL queries

Логи медленных запросов по умолчаню выключены, при раскомментировании соответствующей строки в конфигурационном файле в лог будут писаться запросы, выполняющиеся дольше 10 секунд (параметр можно варьировать в ту или иную сторону)

При снижении производительности сервера (или росте нагрузки, что также является типичным проявлением наличия медленных запросов) включается ведение логов медленных запросов, затем эти логи анализируются.

Логирование включается путем раскомментирования соответствующих директив к my.cnf

 

Включение ведения логов в MySQL 5.0:

Общие логи:

log=/var/lib/mysql/general.log

 

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

log-slow-queries=/var/lib/mysql

В большинстве случаев на файлы логов устанавливаются права 600 и владелец mysql:mysql

 

Включение ведения логов в MySQL 5.1.29+:

Общие логи:

general_log

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

slow_query_log

Файлы логов при этом будут созданы автоматически

Мониторинг

top

отображает только один процесс, индивидуально каждый процесс отслеживать возможности нет

atop -r

просмотр логов atop позволит определить причины неполадок на сервере в принципе, если нагрзку создает MySQL — вывод утилиты укажет на это

mtop

innotop

 

mysqladmin processlist

или

mysqladmin proc

предоставляет полную информацию о процессах MySQL

 

Образцы конфигурационных файлов MySQL

Найти их можно в каталоге /usr/share/mysql

my-huge.cnf
my-large.cnf
my.small.cnf
my-innodb-heavy-4G.cnf
my-medium.cnf

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

 

Также в шаблонах по умолчанию применяются thread_concurrency, что означает невозможность запуска MySQL с шаблонных конфигом в принципе

 

 

Инструменты диагностики MySQL

Общая оптимизация

mysqltuner.pl

wget mysqltuner.pl

chmod 755 mysqltuner.pl

Использование

./mysqltuner.pl

или

perl mysqltuner.pl

Сервер баз данных должен работать минимум 24 часа до запуска данной утилиты

 

mysqlidxchk

Анализирует общие логи или логи медленных запросов и ищет неиспользуемые индексы, их потребуется удалить — это позволит увеличить скорость работы базы данных

wget http://hackmysql.com/scripts/mysqlidxchk-1.1

chmod 755 mysqlidxchk* mysqlidxchk

Использование

./mysqlidxchk —general /var/lib/mysql/ general.log

 

 

Парсинг лог-файлов


mysqlsla

Определяет на основании анализа логов медленных запросов и общих логов пользователей которые используют базу больше всего (в процентном отношении)

wget http://hackmysql.com/scripts/mysqlsla

chmod 755 mysqlsla*

mv mysqlsla* mysqlsla

Использование (логирование должно быть включено)

./mysqlsla -lt general /var/lib/mysql/general.log

./mysqlsla -lt slow /var/lib/mysql/slow.log

Формирование отчетов о статусе

 

mysqlreport

Утилита интерпретирует вывод SHOW STATUS и составляет на основании собранной статистики отчет о работе MySQL

wget http://hackmysql.com/scripts/mysqlreport

chmod 755 mysqlreport

Исопльзование
./mysqlreport

Инструмент довольно сложен в использовании и предполагает хорошие знания MySQL

 

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