Блог Ромки

Достался на поруки большой форум, построенный на базе PHPBB2. Я в курсе, что двигло старое и надо бы заменить, но предыдущий админ предупредил, что они уже пытались переехать на phpBB3 и это привело к диким тормозам и им пришлось съехать обратно. В довесок на самом форуме навешано много всяких МОДов, часть функционала которых отсутствует в 3 версии. Поэтому пока сидим на старом движке. И поскольку он больше не поддерживается и обновлений не предвидится, то я ручками ковыряюсь в исходниках.

Итак, форум, посещаемость около 30000 хостов в день и около 200К просмотров в день. В базе данных около 5 миллионов записей. БД весит около 3.5 Гбайт. Самая большая таблица с записями 3 Гбайта (не в последнюю очередь из-за индекса). В он-лайне постоянно от 300 до 700 человек. На форуме уже был установлен МОД Extreme Styles (и насколько я понял он серьезно снижает нагрузку), который кэширует шаблоны. Дополнительно на сервере были установлены ускорители eAccelerator и ZEND для PHP. Все эти действия привели к тому, что сам PHP и Apache не съедают много ресурсов. В данный момент главное узкое место – жесткие диски (обычные SATA), часть операций из-за размера БД неизбежно выполняется с использованием винчестера. В данный момент рассматриваем возможность переезда на другой сервер с SAS дисками. Текущий выделенный сервер, в общем, справляется с нагрузкой, но иногда в моменты пиковых нагрузок таблицы переходят в состояние «locked» и другие пользователи вынуждены подолгу ждать своей очереди, а то и вовсе получают сообщение об ошибке. Форум постоянно растет (за пол года +10К хостов в день) и вопрос переезда становится все актуальнее. А пока было время, я занимался оптимизацией.

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

Первым делом я приступил к починке именно поиска, потомучто Яндекс, которым обходились пользователи, искал явно не всё. Начал с попытки заново построить таблицы phpbb_search_wordlist и phpbb_search_wordmatch для этого пользовался МОДом rebuild search. Но мало того, что он серьезно грузил базу данных и обещал работать пару недель до полного выполнения задачи. Финальные таблицы ожидались в районе 10 Гбайт. Поэтому я оставил попытки вернуть к жизни стандартный поиск и начал смотреть в район FullText Search средствами самого MySQL (как это обсуждалось на уже закрытом официальном форуме). Решение оказалось вполне рабочим. Минус решения: на таблицу пришлось повесить FullText индекс. Который увеличил размер таблицы в два раза и слегка увеличил нагрузку во время работы с этой таблицей. Также подросло время оптимизации таблицы до 20 минут. Пришлось переписать хинт по поиску, но новые возможности пришлись пользователям по душе. Далее возникла новая проблема, не смотря на индекс, некоторые запросы в поиске выполнялись долгое время (иногда вплоть до минуты) по времени. На это время таблица с постами «лочилась» и пользователи, которые хотели что-то посмотреть или написать видели экран ожидания. Таких запросов за сутки едва ли набегало более 5-15, но с этим надо было что-то делать. Решение нашлось быстро: таблица с постами phpbb_posts_text дублировалась в phpbb_posts_text_search. С первой таблицы снимаются FullText индексы и оставляются только на второй таблице. Запросы из поиска идут к таблице phpbb_posts_text_search. Недостающие посты копируются из первой таблицы во вторую по CRON ночами, когда нагрузка на сервере минимальна. Минусы решения: 1) дополнительное место под копию таблицы 2) в поиск не попадают свежие посты (задержка около суток). 3) дополнительная возня со скриптом для крона

Далее я приступил к оптимизации непосредственно работы самого форума. Сначала посмотрел, какие запросы и сколько времени выполняются на форуме. Для этого в классе базы данных (db/mysql4.php) в функции sql_close() добавил строчку echo implode(», $this->info); Внизу страницы стали выводиться все запросы к базе данных и время их выполнения. В настройках mysql поставил log_slow_queries. Проанализировав результаты, стало примерно ясно что именно тормозит.

1) Если на сервере прилично памяти, то не стоит экономить на настройках MySQL. Большинство параметров можно значительно увеличить. Понять, что именно может помочь просмотр состояния MySQL в phpMyAdmin после длительной работы. Красненьким будут подсвечены наиболее критические места. Особо важные параметры:
tmp_table_size
key_buffer_size
sort_buffer_size
table_cache
myisam_sort_buffer_size
query_cache_size
max_heap_table_size и tmp_table_size желательно установить в очень большие значения если позволяет память

2) Для начала избавился от очень больших тем на форуме (более 200 страниц или более 5000 постов). Их не надо удалять – просто закрыть. И открыть продолжение. Такие большие темы часто являются популярными и их регулярно смотрят, а обращение к ним серьезно нагружает MySQL.

3) Как известно наиболее затратные операции для MyISAM это UPDATE и INSERT. Как можно сократить количество апдейтов? Например при каждом просмотре топика происходит операция UPDATE счетчика. Количество таких операция можно сократить используя следующую конструкцию if (rand()%10) . То есть счетчик будет обновляться каждый десятый просмотр сразу на 10 значений. Для топиков с большим числом просмотров погрешность значения будет невелика. Для остальных некритичных UPDATE можно поставить LOW_PRIORITY.

4) Ну и наконец один из самых главных апдейтов. В файле viewtopic.php есть один из главных запросов форума, который выводит собственно сообщения в теме. Запрос очень тяжелый и выполняется ежесекундно. Выглядит он как то так:

Особенно тяжелым с точки зрения памяти этот запрос становится на больших темах с 1000 и более ответов. MySQL получает и сохраняет в памяти все 1000 постов и выбирает из них последние 15 (если запрашивается последняя страница). Этот запрос можно разбить на 2 запроса следующего вида:

Сначала получаем список необходимых ID:

Затем получаем все данные которые требуются только для нужных ID:

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

Если нужны ещё идеи по оптимизации PHPBB2, то будет полезно почитать вот этот топик (к сожалению тоже уже закрытый). Если будут вопросы по деталям, задавайте в комментах. А также с удовольствием выслушаю ваши комментарии или замечания по этой теме.