О переделке запросов в MaxSite CMS 0.41

Те, кто следит за развитием MaxSite CMS, знают, что в предыдущей версии (0.40) мы уже немного переделывали структуру базы в плане оптимизации индексов. Это позволило получить примерно 5-кратное увеличение скорости некоторых запросов на сайтах с большой посещаемостью. Для средних сайтов эффект малоощутим, разве что немного уменьшился общий размер базы.

На сей раз изменения были более существенные.

В качестве «испытуемого», как и в прошлый раз, выступил сайт darievna.ru. Сайт на тот период имел посещаемость примерно 10 тыс. посетителей в сутки, что уже является хорошим показателем. Причем работал на обычном виртуальном хостинге (правда на дорогом тарифе). Через некоторое время сайт переехал на отдельный выделенный сервер, поскольку посещаемость постоянно увеличивалась (и в планах имеет дальнейший существенный рост), а обычный хостинг для таких крупных проектов уже мало подходит.

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

Первым делом мне пришлось включить бд-кэширование в MaxSite CMS. Это практически полностью сняло проблему при отдаче страниц посетителям, поскольку все запросы автоматом попадали в файловый кэш. Для большинства страниц вообще не было обращений к базе и тем самым сайт мог продолжать работать, пока админы настраивали сервер и MySQL.

Тут необходимо сделать небольшое отступление, рассказывающее о кэшировании MaxSite CMS.

В системе есть три варианта кэшей.

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

Теоретически, основного кэша должно быть более чем достаточно для самых посещаемых сайтов, поскольку «завалить» сервер 5-15-ю запросов на страницу довольно проблематично.

Другой вопрос - где та граница, за которой основного кэша системы будет уже недостаточно? С darievna.ru на практике мы поняли, что граница лежит где-то в районе 10-15 тыс. хостов и для меня это было довольно неприятное открытие. И это значит, что нужно было включать второй кэш - базы данных (CodeIgniter-кэширование запросов).

БД-кэширование выполняется автоматически средствами CodeIgniter. Для его включения нужно в файле application/config/database.php выставить

$db['default']['cache_on'] = TRUE;

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

Третий вариант - т.н. глобальное кэширование. «Круче» него наверное уже ничего нет, поскольку в кэш попадают сразу готовые страницы. По сути он превращает сайт в набор готовых html-страничек и, естественно, при такой работе системе не требуется использовать ни запросы к базе данных, ни множество php-функций (конечно, когда страницы в кэше). Соответственно, при глобальном кэшировании многократно увеличивается скорость работы, а потребление php-памяти падает в 2-3 раза (впрочем, для MaxSite CMS этот показатель не критичен). К минусу этого кэша следует отнести тот факт, что сайт перестает «динамически» работать, то есть посетители видят только старые страницы и если какая-то часть изменилась, то этого не будет видно, пока кэш не будет обновлён. Именно по этой причине глобальное кэширование практически никто не использует: основного и бд-кэширования вполне достаточно.

У всех видов кэшей есть одна общая черта - когда на сайте произошли какие-то изменения, например появилась новая запись, то кэш автоматом сбрасывается и после этого начинает создаваться заново. Но, чтобы данные попали в кэш, их необходимо создать. То есть после сброса кэша идет лавинообразная нагрузка и прежде всего на MySQL.

Например на нашем испытуемом изначально в пиках приходилось около 100 одновременных mysql-соединений. Сам по себе показатель не очень большой (возможности MySQL несколько больше), тут главное, чтобы sql-запросы были достаточно простыми. И тут как раз и выяснилось, что MySQL не справляется с таким потоком - возникала нагрузка, которая приводила к сбоям на сервере.

Постепенно мы пришли к выводу, что дальнейшие работы необходимо выполнять в двух направлениях: настройка MySQL на максимальную производительность и оптимизация SQL-запросов (и структуры базы) в MaxSite CMS.

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

С оптимизацией запросов дело обстояло несколько сложней. Изначально мы решили, что дело в недостаточно проработанных индексах таблиц. Я провёл кучу испытаний и тестов и пришёл к выводу, что для MySQL индексы не дают какого-то существенного прироста скорости. То есть те изменения, которые мы внесли в 0.40, оказалось достаточно - все дальнейшие испытания не давали практически никакого результата. Значит дело было в самих запросах.

Я определил самый затратный запрос к базе и мы приступили к его оптимизации. Идея была в том, чтобы выяснить в чём причина «тормознутости» и дальше уже по аналогии изменить все остальные.

Первым под подозрение попал JOIN. По сути это вложенный запрос, который MySQL выполняет в «цикле». Естественно на него тратится много времени и не зря авторы крупных проектов, так часто советуют избавляться от JOIN'ов. Однако в нашем случае выяснилось, что хоть JOIN'ы и «тормозят», но не настолько, чтобы говорить о них, как о глобальной проблеме. Более того, в некоторых тестах запросы с JOIN'ами работали на том же уровне, что и простые.

Аналогичные тесты проводились и с группировками, сортировками и везде если и получали, то совсем небольшое ускорение - 5-10%. Этого было явно недостаточно чтобы решить проблемы.

Мы долго топтались на одном месте, и наверное до сих пор топтались, если бы я не сообразил обновить phpMyAdmin до последней версии. А дело в том, что в ней добавили опцию профилирования запроса. Строго говоря, это стандартная возможность MySQL, но она редко восстребована потому что выдаёт чисто техническую информацию о том как был выполнен запрос. Например: сколько времени ушло на открытие таблицы, оптимизацию запроса, сортировку и т.п.

Выяснилось, что самая затратная часть это «Copying to tmp table» - в процентном отношении выходило примерно 95% времени всего запроса! То есть не на получение данных, сортировку и т.п. операции, а именно на копирование данных во временную таблицу!

Естественно, направление поиска немного сузилось и мы погрузились в доки MySQL, чтобы найти какую-нибудь зацепку по этому поводу. В результате выяснили, что если использовать запросы SELECT с параметром SQL_BUFFER_RESULT, то MySQL будет использовать специальную временную таблицу для результата и тем самым раньше освобождает таблицу для других операций. Мы проверили этот вариант и сразу получили примерно 30%-ускорение запросов! Это уже был хоть какой-то результат.

Следующим шагом я стал разбирать исходный запрос и, постепенно упрощая его, заметил, что в некоторых случаях, если запрос обновить дважды, то в первом случае он выполняется довольно медлено, а во втором практически мгновенно. Профилирование показало, что второй запрос вообще не выполнятся (как первый), а сразу получается готовый результат. Так работает кэш MySQL и всё было бы замечательно, если бы не тот факт, что для одних запросов, MySQL использует кэширование, а для других нет.

Дальше осталось только найти, что именно мешает MySQL использовать кэш. Путем исключений выяснился виновник - функция NOW(), которая возвращает текущее время. Порывшись в документации, я нашёл подтверждение своей находке.

Немного подумав, я понял почему NOW() не должна кэшироваться. Ведь эта функция возвращает текущее время, как будто бы мы его явно указали в запросе. Даже если такой запрос и попадет в кэш, то уже через секунду он устареет. То есть смысла его кэшировать нет.

Однако в запросах MaxSite CMS функция NOW() играет не последнюю роль. При получении страниц система проверяет даты записей, чтобы исключить из выборки те, которые должны быть опубликованы в будущем. Данный механизм используется в блогах как отложенная публикация - блогер выставляет будущую дату и при её наступлении запись автоматом появится на сайте.

Исключив NOW(), мы получаем большой прирост скорости, но теряем возможность отложенной публикации. Тупик...

Однако, через какое-то время я понял, что можно изменить алгоритм запросов так, чтобы убрать из него NOW(). По сути NOW() нам нужна только для того, чтобы исключить некоторые страницы. Но что нам мешает явно указать их номера с помощью page_id NOT IN()? Ну а сами страницы для исключения мы определим перед основным запросом в виде той же NOW() - этот запрос будет простой, причем с использванием индекса page_date_publish, что позволит выполнить его очень быстро.

В итоге мы удалили из основного запроса NOW() - это дало его ускорение на несколько порядков (примерно в 1000 раз) за счет кэша запросов. Добавив SQL_BUFFER_RESULT, мы указали MySQL  «работать» быстрей и оптимальней (в среднем на 20-30% для некэшируемых/первых запросов). Из «потерь» - только дополнительный запрос для дат.

Причем изменения оказались настолько эффективными, что даже на моём довольно слабом VDS скорость отдачи страниц увеличилась примерно в 1,5-2 раза.

На darievna.ru мы смогли опять вернуться в основному кэшированию, выключив бд-кэш. Сейчас посещаемость сайта приблизилась к 30 тыс. посетителей в сутки (при около 100 тыс. просмотров страниц), а количество одновременных соединений к базе в пиках достигает 250. Расчет показывает, что при текущей нагрузке на базу можно расчитывать на посещаемость от 100 тыс. в сутки (по «оптимистичному сценарию» - до 200 тыс.).

Для всех пользователей MaxSite CMS приятная новость заключается в том, что система стала менее требовательна к ресурсам сервера и сайты на ней будут работать несколько быстрей. При этом, думаю, что мы и дальше будем работать над вопросами ресурсопотребления: есть некоторые идеи, которые, возможно, ещё улучшат работу MaxSite CMS.

В заключении хотелось бы поблагодарить тех, кто участвовал в этой работе: Дарью, Михаила и Алексея. :)