MySQL является самой распространенной в настоящее время системой управления базами данных. MySQL — свободно распространяемое программное обеспечение, использование программного пакета бесплатно, что при наличии большого количества документации делает пакет популярным как при разработке небольших проектов, так и при создании нагруженных систем. Оптимизация работы сервера баз данных становится необходимой когда замечена нагрузка на систему, создаваемая MySQL
В рамках статьи будут рассмотрены основы оптимизации работы MySQL при повышенной нагрузке
Оптимизация работы сервера баз данных MySQL при высокой нагрузке и MySQL my.cnf
Основной конфигурационный файл
mcedit /etc/mysql/my.cnf
Анализ логов сервера баз данных и использование инструментов диагностики производительности
Цели оптимизации:
- Достижение стабильной работы сервера
- Экономия на модернизации аппаратной части под растущие потребности проекта.
Когда веб проекты разрастаются и текущая конфигурация сервера перестает выдерживать нагрузки — нужно увеличивать объем 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 и сделать более оптимальными добавив индексы.