От скорости работы баз данных (БД) зависит быстрота отклика сайта. Ведь замедленная обработка запросов влияет на PHP, следовательно — накапливается огромное количество операций, с которыми сервер может не справиться.
Управлять данным процессом позволяет использование систем управления базами данных или СУБД. Одной из самых широко применяемых СУБД является MySQL — ПО с открытым исходным кодом, созданное компанией MySQL AB (Oracle) ещё в 1995 году. Оптимизация MySQL позволяет избежать проблем с производительностью сервера и значительно ускорить интернет-ресурс.
В статье представлены варианты повышения производительности баз данных MySQL с помощью специального скрипта, а также указаны параметры настройки, на которые необходимо обратить внимание.
Зачем оптимизировать работу MySQL
- Увеличение скорости обработки и выполнения запросов. Скорость работы сайта прямо пропорционально зависит от времени обработки и выполнения SQL-запроса к базе данных, которое должно быть минимальным.
- Предотвращение перегрузки сервера. При перегрузке сервера работа web-ресурса или приложения будет нестабильной. Хостер может заблокировать ресурс, чтобы последний не нарушал работу всего сервера, на котором также работают и другие сайты.
- Уменьшение времени ожидания загрузки web-страницы. Когда идет огромное количество SQL-запросов к базе данных, происходит существенное замедление работы сайта, что недопустимо для коммерческого или представительского интернет-ресурса.
- Экономия ресурсов хостинга. Если MySQL не оптимизирована, то происходит значительный перерасход использования ресурсов сервера (процессорного времени, оперативной памяти). На этом основании хостер имеет право заблокировать работу ресурса.
- Возможность масштабировать ресурс. При расширении сайта будет невозможно обеспечить хорошее качество его работы. Например, арендатор VPS загрузил на сервер интернет-магазин, в котором 100 видов товаров. Через некоторое время бизнес расширяется и появляется возможность предложить потребителю 10000 разновидностей. После загрузки и доработки интернет-магазин начинает работать медленно, постоянно происходят ошибки при помещении товаров в корзину и т. д.
Какие ресурсы желательно оптимизировать
- Средне и высоконагруженные сайты с посещаемостью от 10 000 человек в сутки.
- Сервера разработчиков веб-пиложений и сервисов.
- Интернет-магазины с базой товаров, превышающей 100 единиц.
- Высоконагруженные прокси/VPN сервера на основе VPS.
- Системы работы с финансами и бухгалтерские приложения (продукты «1 C», ПланФакт, «Моё дело», Livesklad).
- Приложения для мониторинга сервисов компьютерных сетей и серверов (Zabbix, Prometheus, Grafana).
- Сайты с высокой посещаемостью и регистрациями, превышаемыми 100 пользователей в сутки.
- Контент-биржи (Text.ru, Advego, Copylancer).
- Видео- и фотохостинговые ресурсы (YouTube, Vimeo, Imgur).
- Рекламно-баннерные площадки (myTarget, Adfox).
- Финансово-экономические игры с возможностью вывода денег (Your Real Town, Surfer Money).
Скорость работы баз данных
Чтобы оптимизация СУБД MySQL дала результат, нужно начать с анализа работы баз данных. Настройки сервиса содержатся в файле /etc/my.cnf.
С помощью настроек можно проверить, какие запросы выполняются медленно и что можно ускорить. Для этого в раздел [mysqld] добавляется следующий запрос:
log-slow-queries=/var/log/mariadb/slow_queries.log long_query_time=5
Информация указана в строчках:
log-slow-queries=/var/log/mariadb//slow_queries.log long_query_time=2
Во второй обозначено минимальное время внесения запроса в лог — 2 секунды.
Чтобы увидеть актуальные данные, сервер перезапускается. Сведения находятся в логе:
# systemctl restart mariadb # tail -f /var/log/mariadb/slow-queries.log
В полученном списке будут представлены все запросы, время выполнения которых превышает указанный показатель. К примеру, больше 10 секунд может выполняться запрос:
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
Если при его выполнении в MySQL операция происходит более 3 секунд, запрос может считаться медленным.
Сайт будет работать корректно при условии, что таких запросов немного. Но если у него постоянная нагрузка, количество необработанных запросов будет постепенно расти. Пропорционально им скорость ответа возрастет до нескольких минут.
Решить эту проблему может оптимизация таблиц или кода. В последнем случае из него убираются все сложные запросы. Это длительный процесс, описать который в рамках одной статьи крайне затруднительно. Поэтому здесь мы подробно рассмотрим именно первый вариант.
Первоначальная настройка
У MySQL достаточно сложная конфигурация, но оптимизация запросов не требует делать все вручную. Для устранения проблем со скоростью выполнения существует специальный скрипт — MySQLTuner. Он анализирует работу базы данных и выводит рекомендации, какие параметры с какими значениями требуется изменить.
Чтобы скрипт работал и показывал текущие проблемы, необходимо загрузить три файла через wget:
# wget http://mysqltuner.pl/ -O mysqltuner.pl # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt # wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Первый файл — это скрипт написан на Perl. Два остальных — сведения о простых паролях и уязвимостях, которые позволяют найти проблемы с безопасностью.
Далее необходимо провести тест базы данных. Оптимизация производительности будет основана на выявленных проблемах. Тест запускается скриптом # perl ./mysqltuner.pl. Он выдает полную статистику работы базы. Все проблемные места обозначаются красным восклицательным знаком [!].
Следует помнить, что параметры изменяются согласно рекомендациям, которые выдает утилита. Нельзя бездумно копировать параметры из статьи и применять их к собственной базе данных. В ином случаем можно столкнуться с рядом практических проблем. Например, недостаточным размером буфера движка таблиц (InnoDB buffer pool).
Дополнительная настройка производительности
Чтобы оптимизация базы данных MySQL дала результат, понадобится следовать рекомендациям, представленным в сообщениях утилиты.
Рабочие параметры
Параметры, указанные в MySQL по умолчанию, рассчитаны на очень маленькие базы данных. Это значит, что они не предусматривают высокую нагрузку, что в том числе касается и самой техники.
Для MariaDB
Для настройки производительности вручную необходимо выполнить изменения указанных ниже параметров в файле конфигурации наиболее популярной версии MySQL MariaDB — /etc/mysql/conf.d/app.cnf.
- tmp_table_size — максимальная величина ОЗУ (оперативной памяти), выделяемая для хранения временных таблиц. Последние создаются при формировании SQL-запросов, которые осуществляют выбор данных из таблицы для дальнейших операций над ними. Значение необходимо выбирать экспериментальным путем, руководствуясь соотношением tmp_table_size = объем_ОЗУ * 0,75 (tmp_table_size = 64 М).
- max_heap_table_size — максимальный размер таблицы, которая хранится в ОЗУ. Рекомендуемое значение параметра max_heap_table_size должно быть равным величине tmp_table_size.
- query_cache_size — объем ОЗУ, выделяемого под кеш SQL-запросы. Его рекомендуется отключить для проектов с большим количеством записей, т. е. query_cache_size=0.
- query_cache_type —параметр, активирующий или деактивирующий службу управления кешем в MySQL. Его рекомендуется отключить, установив query_cache_type в 0, т. е. query_cache_type = 0.
- join_buffer_size — значение количества объема ОЗУ, которое выделяется для объединения таблиц баз данных. Рекомендуемое значение 8М.
- sort_buffer_size — объем ОЗУ, выделяемый для выполнения операций сортировки данных. Рекомендуется установить значение, равное 10М.
- max_connections — опция, определяющее максимальное число соединений с БД, приобретает значение, когда возникает сообщение об ошибке «Too many connections». Повышать это значение следует постепенно, вплоть до исчезновения ошибки.
Для InnoDB
Дополнительной оптимизации MySQL способствует настройка параметров наиболее популярной подсистемы MySQL для работы с таблицами InnoDB.
- innodb_buffer_pool_size — если используются только таблицы InnoDB, необходимо установить максимально возможное значение с учетом технических характеристик системы. Оно должно быть 70-80% от доступной оперативной памяти. Например, при ОЗУ в 32 Гб: innodb_buffer_pool_size = 24G.
- innodb_log_file_size — чем выше показатель, тем быстрее работают записи, то есть большее их количество помещается в файл лога. Поскольку файла всегда два, параметр задает значение только для одного. Например, innodb_log_file_size = 512M в сумме даст 1 G.
- innodb_log_buffer_size — определяет размер буфера транзакций и изменяется только в том случае, если используются большие поля (TEXT, BLOB). 1М по умолчанию хватает в большинстве случаев.
- innodb_flush_log_at_trx_commit — повышает пропускную способность записи данных в базу. Решает, сбрасывает ли MySQL каждую операцию в файл лога. В большинстве случаев подойдет вариант innodb_flush_log_at_trx_commit = 2. Следует учитывать два типа значения, где 1 — сохранность данных играет первую роль, 2 — небольшая потеря данных не критична благодаря дублированию.
Объединение таблиц
Часто проблема при работе с объемными базами данных возникает при выборке — попытке объединения (JOIN) столбцов из разных таблиц.
Чтобы ускорить JOIN больших таблиц MySQL, необходимо убедиться в том, что все столбцы проиндексированы. Скорость операции будет выше, если объединяются столбцы одного типа. При JOIN типов DECIMAL и INT, сервер не сможет использовать как минимум один индекс.
После завершения оптимизации тестирование проводится с помощью клиента MySQL:
# mysql > USE база_данных; > SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
Первая операция может занять много времени, но последующие проверки происходят почти мгновенно. При оптимизации необходимо учитывать все советы утилиты.
Типы объединений
MySQL позволяет выполнять 3 типа объединений записей в двух таблицах (table_A и table_B):
- внутреннее;
- левостороннее внешнее;
- правостороннее внешнее.
Пример № 1 — внутреннее
При внутреннем объединении двух таблиц происходит выборка общих записей. Для этой цели используется ключевое слово INNER.
Чтобы выполнить SQL-запрос, нужно запустить терминал, а затем активировать консоль MySQL командой:
sudo mysql -u имя_пользователя -p
Далее необходимо ввести пароль и SQL-запрос: SELECT * FROM tableA INNER JOIN tableB ON tableA.name = tableB.name, где «name» — имя поля для таблицы А.
Пример № 2 — левостороннее
Суть объединения заключается в миграции данных из таблицы «table_B», которых нет в таблице «table_A», в последнюю. Используется комбинация зарезервированных слов LEFT-OUTER.
SQL-запрос будет иметь такой вид: SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.name = tableB.name. Если в «tableA» больше полей, чем в «tableB», то в первую запишутся значения NULL.
Пример № 3 — правостороннее
Главной является таблица «tableB». Для объединения необходимо применить ключевые слова RIGHT-OUTER.
В консоли СУБД нужно набрать SQL-запрос: SELECT * FROM tableB RIGHT OUTER JOIN tableA ON tableA.name = tableB.name. Недостающие поля заполняются константой NULL.
Заключение
Грамотная настройка и оптимизация MySQL позволяет достичь оптимально высоких показателей работы сервера и приложений, развернутых на его основе. Этому процессу способствует запуск скриптов, которые могут быстро обнаружить проблемы, влияющие на производительность базы данных.
Автоматическую оптимизацию с помощью скрипта следует обязательно дополнять настройкой производительности в ручном режиме с помощью регулировки основных параметров СУБД. Для повышения эффективности MySQL не менее важна оптимизация работы с выборкой из нескольких объединенных таблиц.
Нужен производительный хостинг для высоконагруженных проектов на MySQL? Выбирайте VPS от Eternalhost — сервер на быстрых SDD, гарантированные ресурсы, запуск за 5 минут.
