Оптимизация скорости работы сайта, поиск слабых мест (от CPU до MySQL)

Автор: Роман Чернышов    Опубликовано: 7 декабря 2022

Доброго времени друзья! Ко мне часто обращаются за помощью, по части ускорения работы сайта, и практически во всех случаях, прежде чем понять, что нужно оптимизировать, нужно произвести исследование — а именно найти слабые места на сайте. Если проблема в базе данных, нужно изучить её, если проблема в запросах к базе данных, нужно также изучить их и сделать ряд экспериментов, на определения их скорости выполнения, а также на возможность их оптимизации. Также дело может быть и в сложном PHP коде, и в медленном CPU сервера, или даже в большой нагрузке на сайт со стороны пользователей(когда запросов к сайту слишком много). То есть, нужно понять общую картину и принять решения, что именно оптимизировать и как. Также от принятого решения, зависит и стоимость такой работы.

Задача

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

Проведем изучение и анализ всех параметров и характеристик как хостинга(сервера) так и самого сайта, проанализируем скорость работы всех задействованных компонентов, как скорость чтения данных с диска, скорость работы интерпретатора PHP, базы данных, так и скорость ответа веб-сервера и загрузки страниц сайта. То есть осуществим общий обзор состояния всей системы.

Хостинг

Изучаем параметры. От параметров хостинга, виртуального(VDS) или выделенного сервера, зависит вообще общая скорость работы всей системы. На скорость влияют следующие характеристики:

• Размещение (страна, локация, ДЦ);
• Тип (хостинг, VDS, выделенный сервер);
• Операционная система;
• Перечень установленного программного обеспечения;
• Сколько сайтов и БД размещено на сервере (их общая посещаемость);
• Процессор;
• Объем оперативной памяти;
• Тип жесткого диска SSD или HDD.

Наш хостинг имеет следующие конфигурацию:

• Hetzler — Германия
• Виртуальный сервер
• Операционная система Debian 10 на ядре Linux 5.10
• Основное задействованное ПО, которое использует 95% ресурсов сервера MySQL сервер 8.0.31(MariaDB), PHP 7.4.33, nGinx в связке с Apache
• Сайтов 3, баз данных 3;
• vCPU(виртуальные) AMD Epyc-Rome, 6 ядер
• RAM 16 GB
• SSD 80 GB

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

Общая нагрузка

Изучаем общую нагрузку на хостинг. Начинаем с мониторинга процессов и их нагрузки на CPU, использование объема памяти и количества потоков.

В режиме отсутствия посетителей на сайте, а следовательно и отсутствие запросов к серверу, отсутствие вызова интерпретатора PHP, запросов к базе данных, общая нагрузка на CPU не превышает 10%, использование памяти менее 50%.

В режиме активности пользователей, одновременное количество запросов к страницам сайта до 10. Общая нагрузка на CPU составляет 100%.

Вывод: CPU, не смотря на его мощность(6 ядер), очень плохо справляется с задачей обработки процесса MySQL (это может быть также связано, как с плохим распределением мощности виртуальной машиной, так и с построением самих запросов к БД). Значит проблема критическая.

Скорость доступа к диску

Читаем случайные блоки с диска, 1000 случайных блоков 4k из первых 16 ГБ диска:

time for i in `seq 1 1000`; do
    dd bs=4k if=/dev/sda count=1 skip=$(( $RANDOM * 128 )) >/dev/null 2>&1;
done

Достаточно хороший показатель для SSD, показатель реального времени чтения данных менее одной секунды.

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

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

В этот же момент, при одновременно открытии от 5 до 10 страниц, смотрим на индикатор скорости загрузки страницы сайта (включая время TTFB). В большинстве случаев, за TTFB отвечает совокупность работы CPU/SSD/RAM/LAN, а также на стороне программного обеспечения: отклик от Apache, то как написан код на PHP, то какие запросы уходят в MySQL, а также сама структура, индексы и объем базы данных. То есть, на этот параметр влияет общая производительность системы.

Как видим, время ожидание ответа от сервера составляет 7.72 секунды(в некоторых случаях доходит до 15 секунд), что очень долго, так как хорошим показателем на посещаемом сайте(10 запросов в секунду), должен составлять менее 1.5 секунды.

Вывод: Очень плохой показатель, скорости работы системы.

Сайт

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

• OkayCMS © 2022 v.4.3.1, использует шаблонизатор Samrty(есть кеш), структура похожа не Laravel, работа с БД через сырой SQL построителя запросов
• Таблиц в БД — 97
• Самые крупные таблицы имеют количество записей от 400 тысяч(таблица с товарами), до 6.2 миллиона строк(таблица с информацией о параметрах товаров).
• Структура таблиц имеет индексы, проставленные разработчиком CMS. Индексы дополнительно проверены. Не все поля таблиц, используемые для построение выборки, имеют индексы — это может снижать скорость таких выборок.

При открытии главной страницы сайта, выполняется 44 запроса к БД, 32 запроса до вывода контента в браузер, и 12 в момент вывода контента(после фиксации итогового времени TTFB), общее время выполнения 7.3 секунды, что как раз сходится с параметром TTFB и теста времени ответа сервера. Для составления статистики по запросам, был написан и внедрен специальный скрипт на в PHP, с класс CMS отвечающий за работы с БД.

Самый медленный запрос с временем выполнения 4.808 секунды, это запрос к таблице товаров.

SELECT DISTINCT
    `p`.`id`,
    `p`.`url`,
    `p`.`brand_id`,
    `p`.`visible`,
    `p`.`position`,
    `p`.`created`,
    `p`.`featured`,
    `p`.`external_id`,
    `p`.`rating`,
    `p`.`votes`,
    `p`.`last_modify`,
    `p`.`main_category_id`,
    `p`.`main_image_id`,
    `r`.`slug_url`,
    `l`.`name`,
    `l`.`annotation`,
    `l`.`special`
FROM
    `ok_products` AS `p`
LEFT JOIN `ok_router_cache` AS `r` ON `r`.`url`=`p`.`url` AND `r`.`type`="product"
LEFT JOIN `ok_lang_products` AS `l` ON `l`.`product_id` = `p`.`id` AND `l`.`lang_id` = 1
WHERE
    `p`.`visible` = '1'
ORDER BY
    ((SELECT count(`pv`.`id`) FROM ok_variants pv WHERE (`pv`.`stock` IS NULL OR `pv`.`stock`>0) AND `p`.`id` = `pv`.`product_id`)>0) DESC,
    `p`.`created` DESC
LIMIT 5

При этом ответ на данный запрос всего 6 строк с данными, слишком затратно для такого количества данных.

Второй медленный запрос имеет время выполнения 1.403 секунды, он также осуществляется к таблице товаров, но имеет сопряжение с таблицей брендов.

Третий медленный запрос имеет время выполнения 0.468 секунды, он относится к таблице записей в блоге.

Вывод: Запросы к таблице товаров, выполняются долго, это связано либо со слабым CPU(что не в нашем случае), либо с большим количеством данным в таблице, либо с неоптимизированным запросом к БД. Так как время выполнения медленных запросов соответствует времени TTFB, можно предположить что проблема именно в них, так как на все другие процессы работы сайта, уходит оставшиеся — 0.5 секунд.

Оптимизация

Так как слабое место найдено, попробуем оптимизировать работу базы данных, для чего у нас есть ряд методик, но какие именно из них окажутся наиболее эффективны в конкретно нашем случае, покажет только практика. Также не стоит забывать о стоимости тех или иных решений, например оптимизация SQL запросов непосредственно во всей CMS может оказаться очень дорогим решением, хоть и эффективным. Какие действия по оптимизации мы можем выполнить:

1. Оптимизация MySQL запросов во всей CMS (путем их изменения);
2. Оптимизация PHP кода(например разделения большого запроса, на два и более маленьких), этот пункт делается с первым;
3. Поиск ошибок и исправление, в расстановке индексов в таблицах (если разработчик CMS допустил ошибку, что-то пропустил и т.д.);
4. Реализация кеширования запросов к БД в файл, по ключу на уровне PHP (можно реализовать автоматическое кеширование медленных запросов);
5. Установка на сервере сервиса кэширования данных в оперативной памяти на основе хеш-таблицы, типа Memcached и настройка работы CMS с его использованием(аналог пункта 4);
6. Конфигурирование сервера MySQL, с целью достижения максимальной производительности;
7. Увеличение параметров мощности хостинга (сервера, VDS), а именно CPU(переход на SSD, но не в нашем случае, т.к. уже стоит);

Оптимизация MySQL запросов

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

И так, приступаем к модификации запроса. Первым делом убираем из него DISTINCT, эта команда в запросе, позволяет выбирать только уникальные значения из таблицы(то есть отсеивает дубли). Дело в том, что при работе с таблицей на 400 тысяч строк, вероятность того, что в ответе на запрос будут дубли, крайне мала, так, что дополнительный фильтр(на уровне MySQL) возможно нам и не нужен.
Новый запрос, уже имеет время выполнения в 3.441 секунды. Что на 1.4 секунды быстрее.

Далее, видим в запросе, в качестве сортировки используется подзапрос, на проверку товара «в наличии», т.е. при выборке, первыми будут выбираться именно товары магазина «из наличия». Вообще в идеале, было бы хорошо, вынести флаг наличия товара в основную таблицу `ok_products` с настройкой индекса по нему, и таким образом избежать подзапроса к таблице параметров товаров `ok_variants`(которая на минуточку, содержит более 6 млн. записей). Но, если логически подумать, то как мы видим в запросе, при сортировке(`p`.`created` DESC ), первыми также идут товары которые были недавно добавлены на сайте(т.е. новые товары выводятся с большим приоритетом), а это значит, что наиболее вероятно что новые товары(последние добавленные) есть в наличии! Т.е. проверка дополнительного параметра(в наличии товар или нет) может быть и не нужна, и так понятно, раз товары новые. Но, это конечно грубое допущение. Но всё-же, что-то решать надо, и пока, мы попробуем избавиться от этой части запроса.
Новый запрос, уже имеет время выполнения в 2.116 секунды. Что еще на 1.33 секунды быстрее.

Но на этом мы не собираемся останавливаться, и продолжаем изучать запрос. Как видим в нем, есть приращение таблиц(команда LEFT JOIN), таким образом приращиваются две таблицы `ok_router_cache` и `ok_lang_products`, изучим их подробнее. И при первом же изучении, сюрпризы: 1) Обе таблицы не имеют индексов по тем параметрам, на основе которых происходит приращение, 2) Первая таблица `ok_router_cache`, вообще пустая! То есть, из неё нечего брать.

Это значит, у нас появляется два хода: настроить индексы для приращиваемых таблиц, а также убрать из запроса вообще обращение к пустой таблице `ok_router_cache`. Более того, убрать из запроса обращение к ней, мы можем и хитрым способом, например в PHP, прописать дополнительный быстрый запрос(со скоростью выполнения 0.001 сек), на оценку количества строк в таблице `ok_router_cache`, и если там — пусто, то убираем обращение к ней из нашего текущего «тяжелого» запроса. И так, убрав подзапрос к `ok_router_cache`, мы получаем время выполнения в 0.56 секунды. Что еще на 1.6 секунды быстрее.

Вот это да!

Получаем запрос:

SELECT `p`.`id`, `p`.`url`, `p`.`brand_id`, `p`.`visible`, 
`p`.`position`, `p`.`created`, `p`.`featured`, `p`.`external_id`, `p`.`rating`, `p`.`votes`, `p`.`last_modify`, 
`p`.`main_category_id`, `p`.`main_image_id`,  `l`.`name`, `l`.`annotation`, `l`.`special`
 
FROM `ok_products` AS `p` 
 
LEFT JOIN `ok_lang_products` AS `l` ON `l`.`product_id` = `p`.`id` AND `l`.`lang_id` = 1 
 
WHERE `p`.`visible` = '1' 
 
ORDER BY 
 
`p`.`created` DESC 
 
LIMIT 5

Также мы можем настроить индексы для таблицы `ok_lang_products`, а именно по ключу `product_id`, и так вносим настройки в таблицу, и делаем тест запроса. Получаем время выполнения 0.55 секунды. Как ни странно(а это и не странно, потому что при выборке используется индекс только одного поля — первого в условиях, а не как у нас обоих, поэтому играем очередностью условий), настройка индексов, существенно на картину не повлияла, хоть мы и получили небольшой прирост, он мал, всего от 0.01 секунды до 0.2).

Итог: Время запроса, было 4.808 секунды и стало 0.56 секунды. Разница на мой взгляд впечатляющая.

Вывод: Хорошим результатом оптимизации будет вынос информации о наличии товара, поля `stock` в основную таблицу товаров(это конечно немного всё равно замедлит скорость выборки в сравнении с нашим экспериментом, но не так как подзапрос на другую таблицу), а также доработать код PHP, включив в него проверку наличия записей в таблице `ok_router_cache`, чтобы принимать решение «на лету», нужно ли делать к ней подзапрос.

Заключение

Как вы можем видеть из данного исследования, оптимизировать скорость работы сайта можно по разному и из предложенных мною семи вариантов, осуществив лишь один, мы получаем прирост в производительности почти в 10 раз! А если реализовать на сайте, весь список моих решений, то можно добиться очень впечатляющих результатов, в скорости его работы. И это только речь об оптимизации бэкенда, а у нас еще есть и фронтенд, о чем я писал в статье про оптимизацию сайта под Google PageSpeed. Отдельно стоило бы порекомендовать разработчикам CMS, в том числе OkayCMS, тщательнее прорабатывать как структуру баз данных и таблиц, так и сами запросы работающие с ними.

Друзья, если вам нужно помощь в оптимизации сайта, пожалуйста обращайтесь, буду ряд помочь вам!

CMS, mySQL, PHP     оптимизация mysql, оптимизация запросов к бд, оптимизация сайта