Чрезмерная нагрузка на БД. Что делать?

[Lotos]

Всем привет!

Мой провайдер говорит, что я гружу базу данных больше, чем надо и высылает список загрузных запросов. Мне не совсем понятно, какие практические шаги я могу совершить далее. Менять скрипт форумов phpbb? Это как-то нелепо.

Может быть здесь можно сделать что-то еще, меняя настройки форума, или используя какие-то моды? Подскажите, кто понял, в чем именно проблема и как ее решить? Что именно грузит БД?

Форум довольно посещаемый - порядка 4000-5000 посещений в сутки.

На форуме уже стоит eXtreme Styles mod.

Вот письмо провайдера:

Ваши запросы к базе данных u27255 создают повышенную нагрузку на MySQL-сервер. Примеры таких запросов приведены ниже.

Оптимизируйте, пожалуйста, Ваши скрипты и дайте нам знать о результатах.

Привожу список тех запросов к БД, работа которых негативно сказывается на производительности серверов баз данных:

| 270049 | u27255 | 10.10.10.227:20272 | u27255 | Query | 581 | Locked |
UPDATE phpbb_users
| 271535 | u27255 | 10.10.10.227:21633 | u27255 | Query | 510 | Locked |
UPDATE phpbb_users
| 272601 | u27255 | 10.10.10.227:22652 | u27255 | Query | 447 | Locked |
UPDATE phpbb_users
| 275722 | u27255 | 10.10.10.227:25685 | u27255 | Query | 327 | Locked |
UPDATE phpbb_users
| 277008 | u27255 | 10.10.10.227:27026 | u27255 | Query | 285 | Locked |
UPDATE phpbb_users
| 277065 | u27255 | 10.10.10.227:27093 | u27255 | Query | 282 | Locked |
UPDATE phpbb_users
| 279341 | u27255 | 10.10.10.227:29054 | u27255 | Query | 158 | Locked |
UPDATE phpbb_users
| 280050 | u27255 | 10.10.10.227:29642 | u27255 | Query | 150 | Locked |
UPDATE phpbb_users
| 280116 | u27255 | 10.10.10.227:29707 | u27255 | Query | 158 | Locked |
UPDATE phpbb_users
| 280996 | u27255 | 10.10.10.227:10386 | u27255 | Query | 112 | Locked |
UPDATE phpbb_users
| 282501 | u27255 | 10.10.10.227:11601 | u27255 | Query | 31 | Locked |
SELECT a.topic_id,a.topic_title, a.topic_views, a.topic_replies, a.topic_last_post_id, c.post_time |
| 282619 | u27255 | 10.10.10.227:11687 | u27255 | Query | 29 | Locked |
SELECT a.topic_id,a.topic_title, a.topic_views, a.topic_replies, a.topic_last_post_id, c.post_time |
| 282998 | u27255 | 10.10.10.227:12015 | u27255 | Query | 30 | Locked |
SELECT a.topic_id,a.topic_title, a.topic_replies, a.topic_last_post_id, c.post_time
| 283125 | u27255 | 10.10.10.227:12119 | u27255 | Query | 30 | Locked |
SELECT a.topic_id,a.topic_title, a.topic_views, a.topic_replies, a.topic_last_post_id, c.post_time |
| 283283 | u27255 | 10.10.10.227:12250 | u27255 | Query | 29 | Locked |
SELECT a.topic_id,a.topic_title, a.topic_views, a.topic_replies, a.topic_last_post_id, c.post_time |
| 283414 | u27255 | 10.10.10.227:12346 | u27255 | Query | 30 | Locked |
UPDATE phpbb_topics
| 283424 | u27255 | 10.10.10.227:12359 | u27255 | Query | 33 | Locked |
UPDATE phpbb_users
| 283444 | u27255 | 10.10.10.227:12381 | u27255 | Query | 32 | Locked |
UPDATE phpbb_users
| 283621 | u27255 | 10.10.10.227:12507 | u27255 | Query | 26 | Locked |
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 283742 | u27255 | 10.10.10.227:12593 | u27255 | Query | 22 | Locked |
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 283821 | u27255 | 10.10.10.227:12662 | u27255 | Query | 19 | Locked |
UPDATE phpbb_users
| 283824 | u27255 | 10.10.10.227:12666 | u27255 | Query | 19 | Locked |
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 283877 | u27255 | 10.10.10.227:12707 | u27255 | Query | 17 | Locked |
SELECT u.*, s.*
| 283938 | u27255 | 10.10.10.227:12747 | u27255 | Query | 15 | Locked |
SELECT u.*, s.*
| 284014 | u27255 | 10.10.10.227:12809 | u27255 | Query | 13 | Locked |
SELECT u.*, s.*
| 284138 | u27255 | 10.10.10.227:12900 | u27255 | Query | 8 | Locked |
UPDATE phpbb_users
| 284326 | u27255 | 10.10.10.227:13026 | u27255 | Query | 3 | Locked |
UPDATE phpbb_users
| 284371 | u27255 | 10.10.10.227:13062 | u27255 | Query | 2 | Locked |
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |

Пути решения сложившейся ситуации могут быть следующими:

- оптимизация структуры таблиц. Возможно, Вам помогут следующие ссылки:

http://www.mysql.com/doc/en/Indexes.html
http://www.mysql.com/doc/en/CREATE_INDEX.html

Рекомендую к использованию:

команду ANALYZE TABLE

http://dev.mysql.com/doc/mysql/ru/ANALYZE_TABLE.html

оператор EXPLAIN (получение информации о SELECT)

http://dev.mysql.com/doc/mysql/ru/EXPLAIN.html

Также рекомендую к прочтению статью по оптимизации запросов и увеличению их скорости выполнения:

http://dev.mysql.com/doc/mysql/ru/Query_Speed.html

[Xpert]

Хорошо бы увидеть полный текст запросов. Смена настроек не поможет, а вот оптимизация некоторых скриптов - вполне.

[Lotos]

Хм, провайдер сказал, что не может дать полный текст запросов.

Мы привели Вам часть списка запросов. К сожалению, полный список запросов мы привести не можем, Вам следует ориентироваться на полученную информацию.

Как я понимаю, повышенную нагрузку могут создавать моды форума? Например, отслеживание кто когда приходил и сколько времени был на форуме и т.п.

Xpert, и еще вопрос: в последней версии форума, на твой взгляд, оптимально ли выстроены запросы к БД, или их можно значительно оптимизировать?

[Lotos]

Поднимаю вновь сию тему, ибо удовлетворительное решение не найдено.
Кто может ответить на поставленные вопросы?

Кто ставил себе мод Cached Generation (SQL Cache)?
Каковы результаты?

Вот подобные темы, которые обсуждались ранее:
Загрузка сервера
Оптимизация SQL-запросов

[Xpert]

Оптимизация движка
Оптимизация index.php
Как я оптимизировал скорость мода topics_anywhere

Вообще желательно узнать, какие стоят моды, посмотреть форум. Конкретно эти запросы такого ступора вызывать не должны...

[Lotos]

Итак, у меня все завершилось
Провайдер сказал "да, так нормально".

Адрес форумов пока не даю.

Что я сделал?

Снес старую переполненную модами версию форума.
Поставил 0.19 с нуля.
Поставил моды:
- быстрый ответ
- eXtreme Styles mod
- rss от Наклоняева
- seo-линки
- topics_anywhere_1110 (оптимизированный не поставился)
- свою тему без иконок и с оптимизированным html-кодом
- разную мелочевку вроде Page Generation Time

Заработало по ощущениям процентов на 20-25% быстрее.

Главная:
Page generation time: 0.2094s (PHP: 30% - SQL: 70%) - SQL queries: 14 - GZIP enabled - Debug on

Страница с темами:
Page generation time: 0.1773s (PHP: 46% - SQL: 54%) - SQL queries: 17 - GZIP enabled - Debug on

Страница с постами:
Page generation time: 0.1406s (PHP: 61% - SQL: 39%) - SQL queries: 15 - GZIP enabled - Debug on

Примерно такие значения сохраняются постоянно.

Перегруз БД был вызван, скорее всего, неожиданным одновременным налетом поисковых пауков и зверской индексацией моего уже немаленького форума (БД 100Mb+) - провайдер увидел перегрузки и выслал предупреждение, которое, впрочем, мог бы позже и снять.

Я предполагаю, что наибольшую нагрузку на БД вызывают следующие события:
- поиск по форуму (особенно с простыми словами)
- вызов "версии для печати" (чем длиннее тема, тем больше нагрузка)
- одновременное нахождение на форуме большого количества народу (сколько, интересно???)
- наплыв поисковых пауков (по-сути это быстрое открывание одним юзером десятков и даже сотен страниц форума)

Что думаете по этому поводу?

[Xpert]

topics_anywhere_1110 (оптимизированный не поставился)

Замените на Recent Topics, лежит на этом сайте и на форуме. Как раз из-за этого мода и создается приличная нагрузка. Кстати, если он у вас RSS выводил, то более чем вероятно что именно он стал причиной чрезмерной нагрузки.