Как ускорить работу MySQL и снять нагрузку с дисковой подсистемы

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

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

В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:

$ sudo apt-get install sysstat

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

Как ускорить чтение

Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.

Движок InnoDB имеет встроенный кэш для данных и индексов - так называемый Buffer Pool. Его размер регулируется переменной innodb_buffer_pool_size. В идеале размер Buffer Pool должен быть как минимум такого объёма, чтобы в нём полностью можно было разместить все данные и индексы плюс 30%-60% от их размера. Дополнительная память используется для служебных нужд и Insert Buffer, а также для обеспечения запаса памяти на будущее. Переменная innodb_buffer_pool_size - не динамическая, поэтому после её изменения в конфигурационном файле потребуется перезапуск MySQL.

Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:

$ free -m
             total       used       free     shared    buffers     cached
Mem:        257934     255969       1964          0       4354     157772
-/+ buffers/cache:      93841     164092
Swap:            0          0          0
$

Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.

По умолчанию под pagecache выделяется почти вся незанятая процессами память, поэтому увеличить его объём можно лишь установкой дополнительных планок RAM. Однако память - недорогой по сравнению с ЦПУ и дисками ресурс, при этом эффект от увеличения кэша может привести к значительному увеличению производительности. Ниже представлен график %iowait - доли времени, в течение которого ЦПУ ожидает ввода/вывода. График снят с рабочего нагруженного сервера. Думаю, комментарии здесь излишни.

Как ускорить запись

Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.

По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.

Однако если datadir MySQL расположен на аппаратном RAID-массиве, то есть возможность задействовать для такой буферизации NVRAM-кэш RAID-контроллера, что намного эффективнее. Следует только убедиться, что контроллер оснащён BBU (Battery Backup Unit) - отдельным источником питания для кэша. При внезапном отключении электропитания у контроллера должно быть время, чтобы сбросить содержимое кэша на диски, иначе данные в массиве останутся в неконсистентном состоянии.

При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.

Значение по умолчанию (1) предполагает, что буфер redo-логов, расположенный в памяти InnoDB, записывается на диск после каждого коммита транзакции. Это наиболее безопасный режим работы, обеспечивающий сохранность каждой транзакции даже в случае “падения” сервера. Можно выставить innodb_flush_log_at_trx_commit в значение 2, тогда логи будут записываться также после каждого коммита, но fsync() - сброс данных на диск - будет выполняться лишь раз в секунду (начиная с версии MySQL 5.6.6 этот интервал определяется переменной innodb_flush_log_at_timeout). Аварийное завершение работы СУБД не приведёт к потере транзакций, однако отключение самого сервера может привести к потере последней секунды транзакций. Значение 0 подразумевает ещё более быстрый режим записи - данные и записываются, и синхронизируются раз в секунду, безотносительно коммитов транзакций. Однако innodb_flush_log_at_trx_commit=0 может привести к потере транзакций даже при падении процесса. Администратору базы данных нужно сделать выбор исходя из текущей нагрузки и бизнес-требований.

Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:

mysql> show global status like "Innodb_os_log_written"; select sleep(60); show global status like "Innodb_os_log_written";
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 337936892416 |
+-----------------------+--------------+
1 row in set (0.00 sec)
​
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Innodb_os_log_written | 337939448320 |
+-----------------------+--------------+
1 row in set (0.00 sec)

mysql> select (337939448320 - 337936892416) / 1024 / 1024 as innodb_log_written_per_min;
+----------------------------+
| innodb_log_written_per_min |
+----------------------------+
|                 2.43750000 |
+----------------------------+
1 row in set (0.00 sec)

mysql>

Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).

Вывод

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