Как ускорить работу MySQL и снять нагрузку с дисковой подсистемы
Любой успешный проект рано или поздно сталкивается с проблемой роста. Число посетителей веб-сайта увеличивается, веб-сервер обрабатывает бóльшее количество соединений, растёт поток запросов к базе данных. В определённый момент времени отзывчивость сайта снижается: страницы загружаются медленнее, что, согласно многочисленным исследованиям, влияет на конверсию.
Причины увеличения времени загрузки страниц могут быть самыми разными. В этой статье мы рассмотрим одну из наиболее типичных ситуаций, а именно запросы к базе данных MySQL выполняются долго, и присутствует высокая нагрузка на дисковую подсистему.
В первую очередь следует выяснить характер нагрузки на диски. В этом поможет утилита iostat. В Ubuntu она устанавливается с пакетом sysstat:
Как ускорить чтение
Допустим, диски загружены запросами на чтение. Что можно сделать, чтобы ускорить отдачу данных? Закэшировать данные в памяти. MySQL предоставляет возможность использования разных хранилищ, или движков (storage engines), для доступа к данным, поэтому подход к кэшированию разный. Рассмотрим два наиболее популярных движка: MyISAM и InnoDB.
Движок MyISAM не имеет кэша для данных. Но мы по-прежнему можем ускорить чтения из таблиц MyISAM. Дело в том, что ядро Linux кэширует все прочитанные файлы в области оперативной памяти, которая называется pagecache. Разумеется, файлы с таблицами MyISAM также попадают в этот кэш. Объём pagecache можно узнать из вывода команды free:
Максимальной производительности чтения можно добиться, если объём pagecache равен объёму данных MyISAM.
Как ускорить запись
Увеличить производительность MySQL при большом объёме записи можно с помощью тонкой настройки параметров сервера.
По умолчанию InnoDB сбрасывает изменённые данные на диск с помощью системного вызова fsync(). При этом операционная система не гарантирует, что данные попадут в хранилища сию секунду, т.к. данные сперва проходят через буфер, поддерживаемый ядром. Буферизация необходима для ускорения ввода/вывода.
При задействовании кэша RAID-контроллера повысить производительность операций записи в БД можно, отключив ненужную буферизацию на уровне операционной системы. Для этого требуется выставить переменную MySQL innodb_flush_method в значение O_DIRECT, после чего перезагрузить систему управления базы данных. Снизить нагрузку на диски также может изменение переменной innodb_flush_log_at_trx_commit. Для соответствия требованиям ACID движок InnoDB хранит логи транзакций, или redo-логи, в которые записываются все запросы на изменение данных. Эти логи используются в процессе восстановления после аварийного останова системы управления базами данных.
Оптимизировать дисковые операции записи помогает правильный выбор размера redo-логов. Для этого есть несложное правило. Достаточно замерить объём данных, который записан в лог за одну минуту. Эту операцию нужно выполнять в момент дневной пиковой нагрузки:
Из примера видно, что за минуту в лог InnoDB записывается 2,44 Мб данных. Объём лога следует подбирать таким образом, чтобы в него умещался объём данных за час. В таком случае у InnoDB будет достаточно времени, чтобы изменить порядок запросов на ввод/вывод для достижения последовательной записи. В нашем примере за один час через redo-логи проходит 150 Мб данных, поэтому переменную innodb_log_file_size следует выставить в значение не менее 75M. Если объём лога выбрать слишком большим, то увеличится время InnoDB Crash Recovery, что увеличит даунтайм при аварийном перезапуске (стоит отметить, что в MySQL 5.5 время Crash Recovery зависит от размера InnoDB-лога в меньшей степени).
Вывод
Разумеется, все эти советы не являются исчерпывающими. Ключом к быстрой работе БД является понимание ваших данных, грамотно спроектированная схема и удачно составленные запросы. Тем не менее ряд эффективных оптимизаций можно произвести на уровне сервера.
Ускоряем MySQL insert/update в 5-10 раз
Немного теории. В операционных системах UNIX существует раздел файловой системы, который физически находится в оперативной памяти, но позволяет работать с ним как с обычным дисковым накопителем. Скорость доступа к блоку жесткого диска приблизительно равна 1 мс. Скорость доступа к памяти — 0.001 мс. Попробуем применить это к БД MySQL, чтобы выжать максимум из операций insert/update.
Сперва проверим скорость случайной записи на жесткий диск:
Теперь то же самое для shared memory (/run/shm или /dev/shm):
Сравним результаты и увидим, что время создания 1000 файлов уменьшилось в 574 раза. Хорошо. Значит, следует ожидать прирост скорости записи в БД.
1) Проверяем размер и свободное место для /run/shm
2) Проверяем сколько места занимает БД
Значит, что базу мы можем перенести в /run/shm
3) Останавливаем MySQL:
4) Создаем директории и копируем данные:
* Если сервис не стартует — смотрим /var/log/mysql/error.log
Теперь самое интересное. Проверяем, что получилось.
Тест на жестком диске я провел заранее, поэтому сразу привожу результаты.
Update выполнялся по случайному интервалу [1 000 000 — 9 000 000] для первичного ключа (id). Крайние значения отброшены, чтобы движок «копался» внутри таблицы.
Существенный прирост скорости на INSERT и еще больший на UPDATE.
Меньше для вставки, т.к. MySQL производит пересчет индексов и организацию данных.
В настройках MySQL выставлено:
innodb_buffer_pool_size = 1024M
Если ставить меньше, то скорость UPDATE для HDD естественно падает.
innodb_flush_log_at_trx_commit = 2
Как таковых транзакций здесь у нас нет и на скорость это не влияет. Тем не менее, оставляем это значение равным 2.
При такой схеме критически важно писать бинарный лог и регулярно делать бэкап. Максимально снизить издержки записи мы можем только указав для бинарника отдельный жесткий диск. Последовательная скорость записи на винчестер намного выше случайной. Поэтому ставим в систему дополнительный жесткий диск, монтируем его, например, в /mnt/hddbin/, и указываем в my.ini путь для бинарного лога: log_bin = /mnt/hddbin/mysql-bin.log
Не забываем добавить скрипты для перезагрузки и останова системы. Смотрим папку /etc/rc*. Обычно это 0 (отключение системы) и 6 (перезагрузка). Мануал, как добавить скрипты легко найти в гугле. Скрипт перед перезагрузкой или выключением системы останавливает MySQL, затем копирует папку /run/shm/mysql-lib на жесткий диск. При включении системы скрипт восстанавливает данные с жесткого диска в папку /run/shm/mysql-lib и после запускает MySQL.
Так же добавляем простенький bash или perl скрипт для мониторинга свободной памяти в /run/shm. Можно подключить Zabbix.
Как оптимизировать производительность MySQL при помощи MySQLTuner
Использование MySQL на оптимальных настройках для отдельных ресурсов помогает справляться с высокими серверными нагрузками и предотвращает замедление работы сервера.
Perl-скрипт MySQLTuner анализирует статистику работы, оценивает конфигурацию MySQL и выводит пользователю предложения о внесении изменений параметров для повышения производительности и стабильности работы сервера.
Установка
Для удобства можно воспользоваться штатными репозиториями и установить MySQLTuner.
Разрешите выполнение скрипта:
Запустите скрипт mysqltuner.pl. Вам будет предложено ввести имя пользователя и пароль администратора MySQL:
Если производилось скачивание скрипта:
Если производилась установка:
Если возникла ошибка:
Скрипт вернет результаты анализа, аналогичные представленным ниже:
MySQLTuner предлагает способы повышения производительности базы данных. Если самостоятельно обновить конфигурацию базы данных затруднительно, то выполнение рекомендаций MySQLTuner является одним из безопасных способов повышения производительности.
Настройка MySQL
При изменении конфигурации MySQL следует внимательно относиться к изменениям и их влиянию на базу данных. Даже при выполнении инструкций таких программ, как MySQLTuner, лучше иметь некоторое понимание процесса.
С порядком анализа можно ознакомиться здесь.
Конфигурационный файл MySQL хранится в директории:
В этот файл могут быть внесены изменения, основанные на рекомендациях MySQLTuner в пункте Variables to adjust секции Recommendations. Если какого-либо параметра нет в файле my.cnf, допишите его.
После внесения изменений в my.cnf перезагрузите MySQL-сервер:
Debian/Ubuntu и CentOS 6:
Обратите внимание! Прежде чем проводить обновление конфигурации MySQL, желательно создать бэкап.
Для наиболее эффективного использования возможностей MySQLTuner желательно производить небольшие изменения за раз, а затем проводить повторный анализ. Таким итеративным способом можно будет добиться наилучших результатов при настройке MySQL.
При этом, для того чтобы данные были корректны, необходимо, чтобы сервер MySQL проработал не менее 24 часов без перезагрузок и смены параметров конфигурации перед следующим анализом.
Параметры MySQL
key_buffer
Изменение key_buffer выделяет больше памяти MySQL, что существенно ускоряет работу базы данных при условии наличия свободной памяти. Размер key_buffer обычно должен занимать не более 25% системной памяти при использовании MyISAM и до 70% для InnoDB. Если значение установлено слишком высоко, ресурсы расходуются впустую.
Согласно документации MySQL, для серверов с 256MB (или более) ОЗУ с множеством таблиц рекомендуется настройка 64M. Серверы с 128MB ОЗУ и меньшим количеством таблиц могут быть настроены на 16M — это значение по умолчанию. Веб-сайты с еще меньшим числом ресурсов и таблиц могут быть ограничены даже меньшим объемом.
max_allowed_packet
Этот параметр позволяет задать максимальный размер отправляемого пакета. Если известно, что MySQL-сервер будет обрабатывать большие пакеты, лучше увеличить их до размера самого большого пакета. Если это значение будет слишком маленьким, в журнале ошибок появится ошибка.
thread_stack
thread_cache_size
Этот параметр указывает количество тредов, которые уходят в кеш при отключении клиента. При новом подключении тред используется из кеша, что позволяет экономить ресурсы при значительных нагрузках.
max_connections
table_cache
С полным перечнем анализируемых параметров можно ознакомиться здесь.
Оптимизация MySQL – основы правильной реализации
Дата публикации: 2016-06-13
От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.
Когда оптимизировать и зачем?
Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.
Но такого писательского терпения у меня явно нет, да и у вас читательского тоже. Мы поступим проще, и постараемся лишь слегка углубиться в чащи оптимизации MySQL сервера и его составляющих. С помощью оптимальной установки всех параметров СУБД можно достигнуть нескольких целей:
Увеличить скорость выполнения запросов.
Повысить общую производительность сервера.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Уменьшить время ожидания загрузки страниц ресурса.
Снизить потребление серверных мощностей хостинга.
Снизить объем занимаемого дискового пространства.
Зачем настраивать сервер
В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.
Если на вашем экземпляре сервера включено кэширование, то система MySQL автоматически «запоминает» введенный пользователем запрос. И в следующий раз при его повторении данный результат запроса (на выборку) будет не обработан, а взят из памяти системы. Получается, что таким образом сервер «экономит» время на выдачу ответа, и вследствие чего скорость реагирования сайта повышается. В том числе это касается и общей скорости загрузки.
В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.
Благодаря включенному кэшированию в MySQL выполнение запросов к серверу СУБД происходит намного быстрее. За счет чего и повышается скорость загрузки всего ресурса в целом. А это положительно отражается и на пользовательском опыте, и на позиции сайта в выдаче.
Включаем и настраиваем кэширование
Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:
PHP Profi
Квест → Как хакнуть форму
Оптимизация MySQL: индексы, медленные запросы, конфигурация Перевод
MySQL по-прежнему является самой популярной в мире реляционной базой данных, но в то же время и наиболее не оптимизированной. Многие люди остаются с настройками по умолчанию, не «копая» глубже. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL в сочетании с некоторыми новинками, которые вышли относительно недавно.
Оптимизация конфигурации
Ручной тюнинг
Следующие настройки должны быть сделаны «из коробки». Согласно этим советам, добавьте в файл конфига в раздел [mysqld] :
Есть инструмент от Percona, который поможет нам найти оставшиеся проблемы автоматически. Обратите внимание, что если мы бы запустили его без этой ручной настройки, то только 1 из 4 настроек была бы определена, т. к. другие 3 зависят от предпочтений пользователя и окружающей среды приложения.
Variable Inspector
Установка variable inspector на Ubuntu:
Для других систем, следуйте этим инструкциям.
Затем запустите toolkit:
Вы увидите такой результат:
Прим. переводчика:
На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:
О том, что параметр innodb_flush_method нужно установить в O_DIRECT и почему говорилось выше. И если вы придерживались последовательности тюнинга как в статье, то вы не увидите это предупреждение.
Ни одно из этих (прим.пер.: указанных автором) предупреждений не критично, их необязательно исправлять. Единственное, что можно поправить – это настройка бинарного лога для репликации и снапшотов.
Примечание: в новых версиях размер binlog-а по умолчанию 1G и этого ворнинга не будет.
Как вы видите, новый MySQL имеет значения по умолчанию, которые практически готовы к применению в продакшене. Конечно, каждое приложение отличается и имеет дополнительные применяемые им хитрости и тюнинги.
MySQL Tuner
Tuner предназначен для мониторинга базы в более длительных интервалах (запускайте его раз в неделю или около того на живом приложении). Он будет рекомендовать изменения, основанные на том, что он увидел в логах.
Установить его просто. Нужно просто скачать его:
Опять же, важно отметить, что этот инструмент должен запускаться примерно раз в неделю, так как сервер это время работал и накапливал информацию. Когда вы поняли значение в конфигах и перезапустили сервер, он должен сначала проработать неделю от этого времени. Хорошая идея создать cron-задачу, которая будет делать это за вас и периодически отправлять вам результаты.
Убедитесь, что вы перезапустили MySQL после любого изменения конфигурации:
Индексы
Далее, обратим внимание на индексы – главная болевая точка многих админов БД любителей! Особенно тех, кто сразу стал использовать ORM и никогда не нюхал чистого SQL.
Примечание: термины ключи и индексы могут быть использованы как взаимозаменяемые.
Вы можете сравнить индексы MySQL с оглавлением в книге, которое позволяет вам легко найти нужную страницу, которая содержит информацию, которую вы ищете. Если нет никаких индексов, вам придется пролистывать всю книгу в поиске нужной страницы.
Как вы можете себе представить, найти по оглавлению намного быстрее, чем пролистывать каждую страницу. Таким образом, добавление индексов в базу в целом ускоряет select-запросы. Однако, этот индекс должен быть создан и сохранен. А значит, запросы update и insert будут медленнее и это займёт немного больше места на диске. В целом вы не заметите разницы при обновлениях и вставках, если вы проиндексировали вашу таблицу правильно, а поэтому важно добавлять индексы в нужные места.
Так как же мы узнаем, какие индексы нужно добавить, и какие виды индексов существуют?
Уникальные/первичные индексы
Первичные индексы являются основными индексами, которые используются по умолчанию при поиске по данным. Для учетной записи пользователя это могут быть идентификатор пользователя, или логин, или даже основной email. Первичные индексы являются уникальными. Уникальные индексы – это индексы, которые не могут иметь повторов на всём наборе данных.
Например, если пользователь выбрал конкретный username, никто больше не может выбрать его. Добавление уникального индекса на столбец username решает эту проблему. MySQL будет «ругаться», если кто-то повторно попытается вставить строку с именем пользователя (username), которое уже существует.
Первичные ключи/индексы, как правило, задаются при создании таблицы, а уникальные индексы позднее через изменение ( ALTER ) таблицы.
И первичные ключи, и уникальные ключи могут быть созданы на один столбец или на несколько столбцов сразу. Например, если вы хотите убедиться, что только одно имя пользователя используется в рамках одной страны, то можно создать уникальный индекс на оба эти столбца, например, так:
Уникальные индексы ставятся на столбцы, к которым вы будете часто обращаться. Так что если учетная запись пользователя часто запрашивается, а у вас много учетных записей пользователей в базе данных, это хороший признак для использования индекса.
Регулярные индексы
Регулярные индексы облегчают поиск. Они очень полезны, когда вам необходимо быстро найти данные по определенному столбцу или комбинации столбцов, но эти данные не обязательно должны быть уникальными.
Приведённый выше пример ускоряет поиск по имени пользователя в стране.
Индексы также помогают увеличить скорость сортировок и группировок.
Полнотекстовые индексы
Эти индексы очень полезны, если вам нужен поиск по всему тексту в колонке. Он специализируется на поиске слов внутри текста. Используйте его на постах, комментариях, описаниях, отзывах и др., если ваше приложение позволяет искать в них.
Обратные индексы (по убыванию)
Начиная с версии 8+, MySQL поддерживает обратные индексы, что означает, что он может хранить индексы в порядке убывания. Это может пригодиться, когда у вас есть огромные таблицы, из которых чаще всего нужны последние добавленные данные. Конечно, всегда можно отсортировать по убыванию, но это будет немного медленнее. А вот это еще больше ускорит.
Применяйте обратные индексы когда, например, пишете логи в базу или, например, для постов и комментариев, у которых в первую очередь подтягиваются последние записи из БД и т. п..
Вспомогательные инструменты: Explain
Вы можете расширить отчёт с помощью EXTENDED :
Подробнее вы можете узнать в документации.
Вспомогательные инструменты: Percona Toolkit для выявления дублирующих индексов
Ранее установленный нами Percona Toolkit также имеет инструмент для обнаружения дублирующих индексов, который может пригодиться при использовании сторонних CMS или просто проверить себя – вдруг вы случайно добавили больше индексов, чем нужно. Например, установка WordPress по умолчанию имеет дублирующие индексы в таблице wp_posts :
Как видно из последней строки, этот инструмент также дает вам советы о том, как избавиться от повторяющихся индексов.
Вспомогательные инструменты: Percona Toolkit для неиспользуемых индексов
Percona Toolkit может также обнаружить неиспользуемые индексы. Если вы логируете медленные запросы (см. раздел «узкие места» ниже), вы можете запустить утилиту и она будет проверять, используют ли эти запросы индексы в таблицах и как именно.
Подробную информацию об использовании этой утилиты см. здесь.
Узкие места
В этом разделе описывается, как обнаруживать и отслеживать узкие места в базе данных.
Для начала, давайте включим логирование медленных запросов:
Строки выше должны быть добавлены в конфигурацию mysql. БД будет отслеживать запросы, которые выполнялись больше чем 1 секунду, и те, которые не используют индексы.
Если вы предпочитаете анализировать эти логи вручную, вы можете сделать то же самое, но сначала вам нужно экспортировать лог в более анализируемый формат. Это можно сделать так:
С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:
Остальные параметры см. в документации.
Заключение
В этом всеобъемлющем посте по оптимизации MySQL мы рассмотрели различные методы и техники, с помощью которых мы можем добиться, чтобы наш MySQL летал.
Мы разобрались с оптимизацией конфигурации, мы прокачались по индексам, и мы избавились от некоторых узких мест. Все это было в основном теорией, однако, всё это применимо на реальных приложениях.
