Оптимизация производительности MySQL

Ссылочный блог

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

В сегодняшней статье мы обсудим, как оптимизировать производительность MySQL. Какое программное обеспечение используется оптимально и как оно работает.

Скорость работы MySQL

Оптимизация без анализа бессмысленна. Прежде чем приступать к оптимизации, посмотрите на производительность вашей базы данных, если у вас есть вопросы, которые выполняются очень медленно. Все настройки службы MySQL находятся в файле /etc/my.cnf. Чтобы активировать появление медленных запросов, добавьте следующую строку в файл my.cnf в раздел [mysqld].

Оптимизация производительности MySQL

Первая строка здесь содержит журнал медленных вопросов, а вторая определяет, что минимальное время для включения запроса в этот журнал составляет 2 секунды. Можно также включить вопросы, не подлежащие индексации.

Однако это больше не нужно для управления скоростью, а используется больше для разрешения кода и создания соответствующих таблиц. Затем перезапустите сервер базы данных и проверьте файлы журнала.

Systemctl Restart Mariadb

Tail -f /var/log/mariadb/slow-queries.log

Оптимизация производительности MySQL

Вы увидите, что некоторые вопросы идут более 10 секунд. Например, вот такие вопросы

Выберите имя_опции и значение_опции из wp_options с autoload = 'yes'.

Вы также можете сделать это отдельно в консоли MySQL.

Оптимизация производительности MySQL

И здесь снова измеряется время, и результат — 3 секунды. Это излишество. И даже если такие запросы поступают редко, если сайт не находится постоянно под нагрузкой, количество невыполненных запросов не будет превышать 3 секунд, а скорость ответа будет расти в течение нескольких минут. Можно пойти двумя путями — оптимизировать код, чтобы убрать сложные вопросы, или оптимизировать MySQL на сервере.

ЧИТАТЬ ЕЩЁ:  10 плагинов для резервного копирования WordPress

Оптимизация MySQL

Настройка MySQL очень сложна, но, к счастью, нет необходимости углубляться во многие из них. Существует специальный сценарий под названием MySQLTunner, который анализирует MySQL и дает подсказки о том, какие параметры нужно изменить и какие значения необходимо установить. Скрипт поддерживает большинство версий MariadB, MySQL и Percona Xtradb. Для загрузки с помощью WGET требуется три файла.

wget http://mysqltuner.pl/ -o mysqltuner.pl wget https://raw.githubusercontent.com/major/mysqltner-perl/master/basic_passwords.txt-o basic_passwordsxt: //raw.githuser.

Оптимизация производительности MySQL

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

Оптимизация производительности MySQL

В течение нескольких минут сценарий предоставляет подробную статистику MySQL. Вопросы о буфере, использование памяти, показатели эффективности. Вы можете просмотреть их все, чтобы лучше понять, что может быть причиной проблемы. Проблемные зоны отмечены красным восклицательным знаком. Например, вы можете видеть, что размер машины Innodb (буферный пул Innodb) значительно меньше для оптимальной производительности.

Оптимизация производительности MySQL

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

Оптимизация производительности MySQL

Все параметры должны быть добавлены в файл /etc/my.cnf. Еще раз хотим отметить, что мы не копируем статьи, а смотрим на то, что дает инструмент. Давайте начнем с кэша запросов.

query_cache_size=0 query_cache_type=0 query_cache_limit=1M

Рекомендуется отключать кэш запросов в сценариях. Кэш запросов — это кэш вызовов SELECT. Когда запрос отправляется в базу данных, база данных выполняет его и сохраняет запрос и результаты в этом кэше. Это не проблема, но при использовании с InnoDB кэш перестраивается при изменении совпадающих данных, что снижает производительность. Кроме того, чем больше объем кэша, тем больше потери. Кроме того, при обновлении кэша может возникнуть блокировка запроса. Поэтому его безопаснее отключить, если данные часто записываются в базу данных.

ЧИТАТЬ ЕЩЁ:  Фолловеры в Instagram как накрутить и удержать

Оба параметра устанавливают объем памяти, используемой для внутренних временных таблиц MySQL. Утилита рекомендует размер более 16 мегабайт. Если доступно достаточно памяти, можно выделить 32 или 64, просто установив это значение в обе переменные. Однако важно, чтобы оба значения были одинаковыми.

Этот параметр отвечает за количество потоков, которые кэшируются. База данных не будет разрывать соединение после завершения, но будет кэшировать, если количество кэшируемых потоков не превышает лимит. Рекомендуется минимум четыре услуги. 16.

Указывает, что доменные имена не нужно настраивать для внешних подключений. Ускоряет работу, так как DNS-запросы не тратят время.

Этот параметр определяет размер буфера InnoDB в оперативной памяти. Это существенно влияет на скорость выполнения запросов. Цена зависит от размера таблицы и количества данных в ней. Если памяти недостаточно, запрос будет обрабатываться дольше. Используется стандартный размер 128, но требуется 652 или более.

Оптимизация производительности MySQL

Размер файла журнала innodb должен составлять 25% от размера буфера. Для 800 мегабайт это будет 200M. Однако здесь есть проблема. Чтобы изменить размер файла журнала, необходимо выполнить несколько действий. После изменения всех необходимых параметров можно приступать к перезагрузке сервера. В журнале необходимо остановить службу.

systemctl stop mariadb

Затем переместите журналы в /tmp.

mv /var/lib/mysql/ib_logfile[01] /tmp

Оптимизация производительности MySQL

Затем запустите службу.

systemctl start mariadb

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

systemctl status mariadb

Оптимизация производительности MySQL

Тестирование результата

База данных mysql была оптимизирована; попробуйте выполнить тот же запрос через клиент mysql.

> USE база_данных; > SELECT option_name, option_value FROM wpfc_options WHERE autoload = 'yes',

Оптимизация производительности MySQL

Вначале это занимает некоторое время и может занять больше времени, чем обычно, но после этого все происходит буквально мгновенно. Показатели от более чем 3 секунд до 0,15. Кроме того, если вы получаете статистику из более чем десятка медленных логов, их также стоит внедрить, если вывод утилиты предполагает другие оптимизации.

ЧИТАТЬ ЕЩЁ:  С чего начать учить PHP особенности синтаксис и идеи для первых проектов

Выводы

Как видите, оптимизация mysql очень проста благодаря скриптам, но в то же время такие функции очень полезны, особенно в высоконагруженных проектах. Только оптимизация запросов mysql может быть еще быстрее. Не забудьте проверить параметры здесь и там, чтобы убедиться, что все в порядке.

Оцените статью