Скорость отклика сайта зависит от того, насколько быстро он сможет обработать запросы к базам данных (БД). Медленная работа БД приводит к появлению очереди из операций. Чем она длиннее, тем серверу сложнее с ней справиться. Когда нагрузка превысит допустимый предел, сайт потеряет свою доступность. Решить проблему может помочь переезд веб-проекта на более производительный сервер, но это будет связано с дополнительными расходами. Кроме того, в некоторых случаях это только отсрочит исчерпание серверных ресурсов. Более подходящим методом является оптимизация баз данных. В статье даем несколько советов по улучшению производительности веб-ресурса.
Оптимизация запросов баз данных
Эффективная оптимизации СУБД 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 секунд.
Корректность работы веб-ресурса зависит от количества сложных запросов. Если их немного, то сервер легко выдерживает нагрузку. Когда нужно сократить численность ресурсоемких операций, оптимизируются таблицы или код. Проработка кода — сложный вариант, так как требует исключения всех сложных запросов. Лучше сконцентрироваться на первом варианте.
Таблицы анализируются с помощью MySQLTuner, который находит проблемные места и дает рекомендации по их исправлению. Находим текущие проблемы, открыв файлы через wget:
# wget http://mysqltuner.pl/ -O mysqltuner.pl
Проводим тест БД с помощью скрипта # perl ./mysqltuner.pl. Зоны, нуждающиеся, по мнению утилиты, в оптимизации, будут выделены красным восклицательным знаком.
Использование индексов для ускорения работы
Индексация позволяет сократить время на выполнение запроса. Индекс указывает путь для быстрого поиска данных на основе значений в столбцах. Первичному ключу присваивается индекс. Затем выполняется поиск строк с данными, содержащими данный ключ. SQL Server находит индекс, а затем по его значению — всю строку с данными. Когда индекс отсутствует, сканируются все строки таблицы, что сказывается на производительности.
Кластеризация данных
Когда один сервер не справляется с нагрузкой, вызванной ресурсоемкими запросами к базе данных, организуют кластер. Это значит, что систему управления базами данных запускают сразу на нескольких серверах. Один из них является главным — мастером, остальные репликами. Мастер записывает и распространяет изменения между остальными машинами. Пользователи обращаются за данными к репликам, тем самым распределяя нагрузку между несколькими компьютерами. Вслед за снижением нагрузки уменьшается время отклика сайта.
Оптимизация физической структуры базы данных
Когда в таблице слишком много полей, отдельные записи имеют большую длину. Это замедляет сканирование таблицы. Кроме того, отдельные запросы, например select, в этом случае приводят к выдаче полей, которые пользователю вовсе не нужны. Чтобы сократить время на обработку данных, необходимо разделить большую таблицу на несколько маленьких.
Резервирование и восстановление базы данных
Резервное копирование поможет сохранить и быстро восстановить БД в случае каких-либо сбоев, повреждений или разрушений. Реплики не позволят восстановить БД после логических ошибок, например, при удалении какой-то группы данных.
Заключение и рекомендации
Чем лучше оптимизирована база данных, тем меньше времени и других вычислительных ресурсов требуется серверу для поиска информации, которую запрашивает пользователь. Контент загружается быстро и пользователи остаются довольны.
RuWeb — недорогой хостинг для сайтов! Автоматическая установка CMS. Без скрытых платежей. 💻
