Стратегия оптимизации веб-проекта с использованием MySQL

Введение

В жизни любого крупного веб-проекта, особенно на PHP, но, в целом, это касается любого серверного ЯП, пригодного для веб-разработки, обычно наступает понимание, что «так дальше жить нельзя», и что настал момент, когда нужно провести оптимизацию работы сайта, чтобы он перестал тормозить (хотя бы на production).

Интересно, что, как правило, даже тяжелые фреймворки (вроде Symfony или RoR) на «медленных» языках, в production-окружении работают достаточно сносно по скорости, а основные «тормоза» вызываются SQL-запросами и неграмотным кешированием (к примеру, инициализация достаточно сложной и большой конфигурации проекта на Symfony занимает около 80 мс, а времена исполнения страницы, при этом, иногда достигают секунды и более).

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

Выявление узких мест

Для начала, хотелось бы сказать, что реальная польза от оптимизации работы с MySQL будет ощущаться далеко не всегда, поэтому перед этим желательно убедиться, что проблемы действительно вызваны работой с MySQL. Здесь общие советы дать довольно сложно, но, в любом случае, стоит сначала каким-либо образом измерить, к примеру, общий процент времени исполнения запросов по отношению к времени исполнения страницы (в production окружении) в вашем драйвере работы с СУБД. Если этот процент — порядка 50 и выше, то оптимизация работы с MySQL более, чем обоснована.

Что и как измерять

Как правило, даже в самых «плохих» (в плане производительности) фреймворках и/или CMS есть некоторые средства для отслеживания проблем производительности СУБД — обычно всегда подсчитывается, хотя бы, количество запросов на странице. На всякий случай скажу, что количества запросов на страницу больше 100 — это плохо :), и начать стоит с этого.

Давайте предположим, что самих запросов в СУБД не так много, и оно всё равно чудовищно тормозит, причём не очень понятно, что именно оказывает наибольшее влияние.

Измерение времен исполнения запросов

Конечно же, самый простой и в меру эффективный способ отслеживания «медленных» запросов — это подсчет времени исполнения каждого запроса и вывод времен исполнения запросов на экран (на основании чего делать вывод, что какие-то запросы «тормозят»). Проблема заключается в том, что этот способ более-менее хорошо годится только для production окружения, когда СУБД хорошо настроена и у MySQL-сервера достаточное количество памяти, чтобы I/O был минимальным и т.д., то есть, когда время исполнения запроса будет зависеть действительно только от его сложности для исполнения и времени CPU, которое на это требуется, и будет минимум сторонних факторов, влияющих на производительность. А если включен query cache, то простое повторение одного и того же запроса будет давать совершенно различное время исполнения (если только не используются ф-ции из стоп-листа).

Если же использовать этот подход в dev-окружении, то, зачастую, запросы «тормозят» не из-за того, что они сложные, а просто из-за случайной посторонней нагрузки или банального I/O. Можно, конечно, просто сразу после запроса обновлять страницу и выполнять те же самые запросы заново, но даже это не гарантирует отсутствия побочных эффектов (например, некоторые включают query cache и на dev-сервере MySQL — если так, то его нужно, конечно же, отключить немедленно).

Поскольку, зачастую, времена исполнения запросов на dev-базе сильно «скачут», порог для slow-запросов ставят в очень большие величины (в Propel с Symfony это, по умолчанию, 100 ms) или вообще не обращают внимания на запросы, которые медленно исполняются, списывая всё на I/O.

Несмотря на все недостатки, конечно же, время исполнения запроса — это базовый критерий для оценки производительности, и нужно просто уметь правильно эти самые времена интерпретировать.

Автоматический EXPLAIN для запросов

Вы можете поддаться соблазну делать для всех SELECT-запросов ещё один запрос с префиксом «EXPLAIN » и считать произведение поля «rows» для всех уникальных id запросов в EXPLAIN для оценки сложности запроса. Идея хорошая, но, зачастую, трудно реализуемая из-за сложностей с выполнением того же prepared-запроса, к примеру. Но это не самая большая проблема (проблему с prepared statements ещё, в принципе, можно как-то решить). Самая большая проблема заключается в том, что EXPLAIN в MySQL зачастую нагло врёт.

Приведу простой пример (допустим, в таблице 100 000 записей со случайными значениями some_field):

Табличка

CREATE TABLE some_table(id INT PRIMARY KEY AUTO_INCREMENT, some_field INT)

Исполняем простой запрос, который просмотрит 100 000 строк и не найдет ничего

SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100

Смотрим EXPLAIN на MySQL 5.1.49

EXPLAIN SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100

EXPLAIN говорит: насяльнике, 100 строк мне просмотреть нада, буду индекс праймаре использовать, насяльнике, всё хорошо будит, мамой клянусь!

Исполняем простой запрос, который просмотрит 100 строк (или немногим больше) и сразу же их вернет

SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100

Смотрим EXPLAIN на MySQL 5.0.51

EXPLAIN SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100

EXPLAIN говорит: насяльнике, 100 000 строк мне просмотреть нада, буду индекс праймаре использовать, осинь долго будит, насяльнике! (да, именно так и говорит, даю слово :))

Это были очень простые примеры, чтобы можно было легко понять, что к количеству строк в EXPLAIN стоит относиться с осторожностью — ведь EXPLAIN не исполняет запрос, а значит, он не может знать, сколько на самом деле ему нужно будет прочитать строк, он лишь делает оценку (причём часто ошибается раз в 10 :))

Считаем количества прочитанных строк

Если вы думали, что в MySQL для измерения производительности самый продвинутый способ — это EXPLAIN, то вы ошибаетесь (не так давно, я тоже так думал :)).

На самом деле, выполнение следующих запросов:

SET query_cache_type = OFF; -- если query cache включен, выключаем его
FLUSH STATUS; -- скоро поймете :)

SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100; -- наш запрос

SHOW SESSION STATUS LIKE 'handler_%' -- магия ;)!

Выдаст что-то похожее на следующую картинку:



Чтобы лучше понять, что такое Handler в MySQL, можете почитать, например, описание команд HANDLER в MySQL. Одним словом, Handler — это общий интерфейс для чтения/записи строк (и не только) в MySQL для разных типов таблиц. Название HandlerSocket для соответствующего интерфейса в MySQL тоже должно вам намекнуть на то, что это за зверь такой.

Таким образом, выполнив в самом начале страницы первые 2 запроса (отключение query cache и сброс статистики), а в самом конце страницы — последнего запроса, можно получить общее количество прочитанных/записанных строк у MySQL. Для проекта, для которого я занимался подобной оптимизацией, я добавил alert() на странице в dev-версии, если в результате выполнения страницы прочитано/записано больше 10 000 строк.

Конечно, это не панацея, но может вам серьезно помочь найти и «обезвредить» медленные страницы на сайте и принять соответствующие меры. Этому методу абсолютно наплевать на то, что это за запрос (оно работает даже для INSERT INTO… SELECT ...) и он всегда выдает точную статистику по совершенным действиям в результате запроса, даже если EXPLAIN ничего путного сказать не может.

Количество присланной информации сервером

В SESSION STATUS, на самом деле, содержится гораздо больше информации, например информация по трафику между клиентом и SQL-сервером (Bytes_%) — если у вас есть «широкие» таблицы, то это может быть тоже актуальным (особенно при работе с ORM, которые любят выбирать все столбцы из таблиц, даже если они там не нужны)

Собственно, оптимизация запросов

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

Надеюсь, информация из этого топика будет для вас полезной :). С помощью этой методики мне удалось выявить узкие места и снизить времена исполнения большинства страниц на сайте на Symfony с ~1000 ms до где-то 200-300 ms и добавить в дев-версию инструмент для автоматического предупреждения подобных проблем в будущем. Всё это заняло у меня где-то 3 дня для сайта с ~20 Мб активного «своего» (всего кода около 100 Мб) кода на PHP :). Много это или мало — думайте сами :)