Как уменьшить количество запросов к MySQL?

[Алексей]

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

Версия phpBBex 1.5.3, из модов установлен чат mchat by AllCity. Хостинг siteko.net.

Сначала проверяли на самом простом тарифе Старт - сразу выходили ошибки вроде:
SQL ERROR [ mysqli ]
User 'aigulya_mysql' has exceeded the 'max_connections_per_hour' resource (current value: 2000) [1226]
An sql error occurred while fetching this page. Please contact an administrator if this problem persists.
Затем, увеличили тариф до максимального, сайт поработает около 5 минут и тоже выходят ошибки:
SQL ERROR [ mysqli ]
User 'aigulya_mysql' has exceeded the 'max_questions' resource (current value: 70000) [1226]
An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

У максимального тарифа следующие ограничения:
Запросов к MySQL 70000 в час
Обновлений MySQL 25000 в час
Соединений к MySQL 12000 в час

На нашем сайте такая статистика:
Пользователей в день (в админке): 199.86
Больше всего посетителей (151) здесь было 13.11.2012, 21:46 (на странице)
Часто заходят бота от Google [Bot] (очень часто), Bing [Bot], Yandex [Bot].
Недавно переименовал файл robot.txt на robots.txt.

Можно ли урезать как-то кол-во запросов или для такого кол-ва пользователей это нормально и надо переходить на выделенный сервер?

Вот настройки нагрузки на сервер:

[VEG]

Алексей, впервые вижу хостинг, который ограничивает количество запросов к базе. Скорее всего чат съедает лимит. Зависит от того, как часто он опрашивает сервер. На каждое обновление чата движок делает несколько запросов к базе.

Пользователей в день (в админке): 199.86

Это количество зареганных, а не реальная посещаемость. Для оценки посещаемости установите счётчик (например, liveinternet.ru или Яндекс.Метрика).
У меня на обычном хостинге более 1000 уников держит без проблем.

[Алексей]

Спасибо.
У меня тоже подозрения были на чат. Он находится на главной странице. Надо будет переместить его на отдельную страницу и увеличить время обновления.

Сейчас пишу функцию по сбору статистики sql-запросов(общее кол-во и какие таблицы чаще запрашиваются), которую буду вызывать из функции dbal_mysqli->sql_query(). И проверю статистику запросов до и после изменений на сайте.

Или может есть уже готовые модификации по сбору информации о sql-запросах? Или инструменту? (В phpMyAdmin выводится только информация с момента запуска сайта).

[Алексей]

Оставил на локальном компьютере работать сайт со страничкой с чатом, подсчитал с помощью скрипта кол-во запросов к MySQL , получилось около 1000 запросов час на одного человек. В основном используются таблицы SESSION_TABLE, CONFIG_TABLE, USERS_TABLE.

На сайте у хостера отключил на 2 часа чат и запретил индексировать ботам все файлы, в результате получилось:

1. До отключения чата на сайте было 90 человек и запросов 162114 за час, использование таблиц в порядке убывания кол-ва обращения к ним:
phpbb_users = 46188
phpbb_sessions = 33926
phpbb_warnings = 16693
phpbb_topics = 11858
phpbb_config = 10645
phpbb_mchat = 8379

2. После отключения чата кол-во людей уменьшилось до 60 человек и запросов за первый час стало 103752, за второй час - 101999, использование таблиц:
phpbb_users = 27264(27527)
phpbb_sessions = 19100(14876)
phpbb_warnings = 14446(17658)
phpbb_topics = 8937(10694)
phpbb_config = 6430(4878)
phpbb_mchat = 0(0)

3. После включения чата картина та же, что и до выключения чата.

Таблица phpbb_warnings у меня вообще пустая, но в запросах используется часто, запросы вроде:

Код: Выделить всё

SELECT * FROM phpbb_warnings
      WHERE warning_active = 1 AND warning_days > 0
      AND (warning_time + warning_days * 86400) < 1353665909

SELECT w.*, u.username AS issuer_name, u.user_colour AS issuer_colour
            FROM phpbb_warnings w
            LEFT JOIN phpbb_users u ON w.issuer_id = u.user_id
            WHERE w.user_id = 957 AND w.warning_active = 1
            ORDER BY w.warning_id

SELECT w.*, u.username AS issuer_name, u.user_colour AS issuer_colour
            FROM phpbb_warnings w
            LEFT JOIN phpbb_users u ON w.issuer_id = u.user_id
            WHERE w.user_id = 971 AND w.warning_active = 1
            ORDER BY w.warning_id

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

Добавлено спустя 2 часа 11 минут 51 секунду:
Скрипт для подсчёта кол-ва запросов к базе данных mysqli.

1. Копируем скрипт calc_queries.php в папку ./work. В эту же папку будет сбрасываться статистика:
f_counter.txt - файл-счётчик
f_stat.txt - файл статистики общего кол-ва запросов за каждый час
f_queries.txt - запросы к определенной таблице, которые вы хотите видеть (нужно раскомментировать строчки вида if ($table == 'phpbb_warnings' ) file_put_contents($f_queries, $query . "\n\n",FILE_APPEND);). На хостинге с большим кол-вом посетителей лучше не исользовать сброс запросов в этот файл.

2. Модифицируем файл ./includes/db/mysqli.php (этот тип базы - MySQLi у меня был выбран при установке форума):

Код: Выделить всё

//--Находим строчку--:
include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
//--Добавляем после этой строчки--:
include_once($phpbb_root_path . 'work/calc_queries.' . $phpEx);

//--Находим строчку--:
if (($this->query_result = @mysqli_query($this->db_connect_id, $query)) === false)
//--Добавляем перед этой строчкой--:
calc_queries($query);

[VEG]

Количество запросов к phpbb_warnings в принципе можно сократить кэшированием и объединением нескольких запросов в один. Поработаю над этим к следующей версии. Но судя по вашей статистике сильно ситуацию это вряд ли изменит.
Приведите нормальную статистику по посещаемости вашего сайта. То есть сколько пользователей онлайн, сколько страниц за час они открыли и т.д. В идеале — ссылка на статистику liveinternet.ru или аналогичную.
Дело в том, что 60-90 человек онлайн (как вы пишете) — это на самом деле немало. Или вы имели в виду 90 человек зашло на форум за час?
В общем посмотрите по статистике, сколько уникальных посетителей у вас в сутки и сколько просмотров они генерируют. Из этого можно будет сделать какие-то более интересные выводы.

Далее, хостинг вообще странный. Берём самый дорогой тариф «Эксперт» за 300 рублей в месяц. Предлагают 30 гигабайт пространства и до 200 сайтов на одной площадке :) Читаем конец таблицы и видим в чём подвох. 10 писем в час — вообще смех. 70000 запросов / 12000 соединений в час ≈ 5 запросов к базе на открытие одной страницы форума. Форумы классически не вписываются в эти ограничения. В эти рамки могут вписаться только простейшие сайты, которые просто отображают какие-то страницы с текстом или каталоги.

[Алексей]

Или вы имели в виду 90 человек зашло на форум за час?

Нет, не за час, а онлайн.

Я ориентируюсь, сколько онлайн, по информации КТО СЕЙЧАС НА САЙТЕ:

Попробую собрать статистику с liveinternet.ru

[VEG]

Алексей, ну раз у вас онлайн столько активных пользователей, то совершенно неудивительно, что все лимиты израсходовались. У вас посещаемый форум :) Поставьте сбор статистики Я.Метрика (навороченный, красивые графики, без рекламы в статистике) или liveinternet.ru (классика, полегче для юзеров, но графики спартанские и много рекламы при просмотре статистики) — уверяю, вы увидите много интересной для себя информации. И поделитесь с нами статистикой, очень интересно :)
В ближайшие день-два постараюсь уменьшить количество запросов к phpbb_warnings. Как я понимаю, вы берёте версию из репозитория, поэтому сможете сразу протестировать. Я уведомлю об этом отдельно.

[Алексей]

Установил на сайт в overall_footer.html и simple_footer.html оба счётчика:

Я.Метрика
http://metrika.yandex.ru/stat/?counter_id=18423091

liveinternet.ru.
http://www.liveinternet.ru/?supermamki-nk.ru

[VEG]

Алексей, для этого в настройках отображения есть соответствующие поля :)



А в этой теме можно взять асинхронный код для liveinternet.ru.

[Алексей]

Спасибо, переделал.

Добавлено спустя 1 час 3 минуты 49 секунд:
Ещё вопрос - можно ли как-то отделить чат от индексации на главной странице?
Если я скрипт(mchat.php), который генерирует страницу чата, во фрейм засуну, запрещу для индексации mchat.php и этот фрейм будет находится на главной странице - содержимое фрейма всё равно будет индексироваться?

[VEG]

Если я скрипт(mchat.php), который генерирует страницу чата, во фрейм засуну, запрещу для индексации mchat.php и этот фрейм будет находится на главной странице - содержимое фрейма всё равно будет индексироваться?

Не должно.

Добавлено спустя 1 час 8 минут 6 секунд:
Добавил оптимизацию при получении предупреждений в теме. Теперь на один запрос к viewtopic.php делается один запрос в эту таблицу. Попробуйте последнюю ревизию в репозитории.

[Алексей]

Оптимизация хорошая.
Если считать относительно таблицы users, то было соотношение примерно 1 к 2.5 (~20000 запросов к warnings на ~55000 запросов к users).
После оптимизации стало соотношение примерно 1 к 23.5 (~700 запросов к warnings на ~17000 запросов к users).
Кол-во запросов к warnings существенно уменьшилась. Таблица sessions по кол-ву запросов передвинулась выше таблицы users.

Завтра днём, когда народ соберётся, посмотрю как оптимизация повлияла на общее кол-во запросов к базе, хотя оценка будет субъективной, так как у меня в статистике нет связи кол-во посетителей с кол-вом запросов за последний час. Как-то надо подключать к моей статистике яндекс.метрику.


Статистика запросов до оптимизации:
Общее количество запросов за последний час = 192646
Текущее время = 20-11-2012 13:19:52
phpbb_users = 55595
phpbb_sessions = 40283
phpbb_warnings = 19987
Общее количество запросов за последний час = 120907
Текущее время = 23-11-2012 23:54:27
phpbb_users = 35330
phpbb_sessions = 25252
phpbb_warnings = 13382

Статистика запросов после оптимизации:
Общее количество запросов за последний час = 76507
Текущее время = 24-11-2012 00:54:33
phpbb_sessions = 19076
phpbb_users = 17251
phpbb_topics = 7552
phpbb_config = 6273
phpbb_user_browser_ids = 5001
phpbb_forums = 4581
phpbb_mchat = 3876
phpbb_topics_track = 2241
phpbb_posts = 2168
phpbb_forums_track = 1406
phpbb_topics_watch = 1392
phpbb_privmsgs = 972
phpbb_zebra = 949
phpbb_forums_watch = 928
phpbb_bbcodes = 751
phpbb_warnings = 734
Общее количество запросов за последний час = 28289
Текущее время = 24-11-2012 01:54:40
phpbb_sessions = 7480
phpbb_users = 6704
phpbb_topics = 2419
phpbb_config = 2372
phpbb_user_browser_ids = 1731
phpbb_forums = 1565
phpbb_mchat = 1428
phpbb_posts = 1063
phpbb_topics_track = 638
phpbb_forums_track = 419
phpbb_topics_watch = 405
phpbb_zebra = 386
phpbb_banlist = 347
phpbb_post_rates = 337
phpbb_warnings = 337

[Алексей]

Вот статистика на воскресенье 25.11.12:

На максимальное кол-во посетителей онлайн = 176 приходится 247 294 запросов к базе данных за час. И это ещё заниженная цифра, так как скрипт по подсчёту кол-ва запросов делает блокировку к файлу-счётчику во время чтения-записи.

В ограничения максимального тарифа siteko.net мы точно не укладываемся.
В jino.ru мне техподдержка написала, что у них максимальное кол-во запросов к базе данных за час = 300 000 - в перспективе тоже мало.
Похоже надо искать другие хостинги или переходить на выделенный сервер

[VEG]

Алексей, вам повезло с активностью пользователей: на 1200 посетителей приходится 31000 просмотров, то есть 25 просмотров на посетителя. У меня, например, на fb2k.ru на 900 посетителей приходится порядка 4000-5000 просмотров, то есть около 5 просмотров на пользователя. То есть основной показатель, характеризующий нагрузку — количество просмотров.

Можете раскомментировать в config.php эти две строки:

Код: Выделить всё

// @define('DEBUG', true);
// @define('DEBUG_EXTRA', true);

И походить в таком виде по форуму. Внизу будет выводиться статистика по запросам и возможность посмотреть их. Только не оставляйте в таком виде надолго! Это увеличивает нагрузку. Какие у вас в среднем числа по запросам на страницу (главная, список тем, просмотр темы)?

Что касается чата. На каждое обновление чат должен делать около десятка запросов в базу (большая часть запросов уходит на стандартный механизм сессий). У вас чат на всех страницах внизу, обновление — каждые 45 секунд. А теперь представьте, что будет, если пользователи будут открывать по несколько окон форума. Я сам иногда по 20-30 вкладок открываю :) Из каждого окна каждые 45 секунд — запрос, который умножается на количество запросов, которые необходимы чату для обновления. Даже удивительно, что у вас отключение чата не дало ожидаемого эффекта :)

Немного математики. У вас за сутки 31000 просмотр, это в среднем 1290 просмотров в час. По статистике в пике у вас до 3000 просмотров в час. Умножим это на 20 запросов на страницу (у меня на fb2k.ru в среднем даже чуть меньше). Получаем ≈60000 запросов — это ожидаемое количество запросов от чистого phpBBex при таком количестве просмотров. У вас же в это время 250000 запросов — согласитесь, это совсем другая цифра. Попробуйте собрать такую же статистику за сутки, но без чата :)

Возможно, ещё какая-то другая модификация шалит. Вы устанавливали какие-нибудь моды, которые выполняют свои SQL запросы? Дело в том, что у вас (из предыдущих сообщений) слишком много обращений к phpbb_users и phpbb_sessions. По-хорошему должно быть не более 2-3 запросов в эти таблицы за один просмотр.

[Алексей]

Модификаций, кроме чата нет. Сам редактировал только, вроде, шаблоны.

Попробую ещё раз отключить чат. Хотя мне кажется, что у нас специфика сайта такая, что каждый пользователь чуть ли не весь форум Клуб СП просматривает за один заход и кол-во запросов при просмотре форума в разы покрывает кол-во запросов, который добавляет чат каждые 45 сек.
Кстати, приблизительно Количество просмотров за час = Визиты за час * Глубину просмотра, т.е. мне можно чат на весь день не отключать и посчитать просмотры за час, а то женщины с ума сойдут

На локальном компьютере, включил DEBUG, почистил кэш, цифры такие:
Включил чат:
index.php = 25 (без кеша), 14, 13, 13, 14 ....
viewforum.php = 13, 12, 11, 12 ...
viewtopic.php = 25 (без кеша), 19,18,17,19,16
Отключил чат и почистил кеш:
index.php = 21 (без кеша), 13, 12, 13, 11 ....
viewforum.php = 12, 10, 10 ...
viewtopic.php = 20 (без кеша), 17,15,17,15 ..
При первом просмотре главной страницы(без кеша) чат добавляет 4-5 запроса для index.php, при втором просмотре - 1-2 запроса.При просмотре сообщений примерно тоже самое.

Ещё я делал подсчёты на лок.компе - чат каждые 45 сек. делает 12 запросов.

Допустим, в течении 45 сек, пользователь листает разные странички с сообщениями и добавляется около 19 запросов (из них 2 запроса от чата) для каждой страницы. Если он листает за 45 сек в среднем 2 страницы (реальную цифру не знаю конечно), то получим 2 * (17+2) = 38 запросов за 45 сек.

Случайно, среднее кол-во просмотренных страниц на одного пользователя за час не равняется Глубине просмотра в Яндекс.Метрике? Тогда дальше можно не читать, так как получим 45*19,5(max)/3600 = 0,24 страниц на одного пользователя за 45 сек .

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

Если эти 12 запросов не учитывать, то чат, при просмотре тем и сообщений, добавляет 1-2 запроса к запросам от самого движка форума. Из 250 000 запросов:
- при просмотре сообщений 250 000 *(1 или 2) / 17 = 14700-29400 запросов от чата.
- при просмотре тем из 250 000 запросов - 20800 - 41600 запросов от чата.
- при просмотре главной страницы - 250 000 *(1 или 2) / 14 = 18000 - 36000 запросов от чата.
Вот из-за этого я наверное и не заметил существенного уменьшения запросов при отключении чата, например, хотелось бы увидеть уменьшение в 2-3 раза.

Ладно, ещё раз проверю эту теорию на практике, потом отчитаюсь.

[factotum]

Из своего опыта работы с другими движками вставлю свои пять копеек. Оптимизация запросов имеет две стороны. С одной стороны все лучше. С другой последующая модификация кода становится более сложной.
было бы здорово подружить PHPBB и php apc. Снова таки по опыту работы с другими движками, реально решает большинство вопросов с запросами/скоростью. Думаю, возможно прикрутить и к PHPBB

[Алексей]

Статистика на 27.11.12. Чат был отключен с 13:00 до 16:00

Вообщем, после отключения чата кол-во запросов уменьшилось в 1-й час примерно на 40000, во 2-й час ещё примерно на 40000. Но и в тоже время сайт показал, что кол-во посетителей онлайн уменьшилось примерно со 110 человек (перед откл. чата) до 60-65 человек (наблюдал в последнем часу). Но почему-то Яндекс это не зафиксировал - у него визиты уменьшились примерно только на 25.

Вот что скрипт выдаёт на макс.запросах:
Общее количество запросов за последний час = 235154
Текущее время = 27-11-2012 20:58:24
phpbb_sessions = 64652
phpbb_users = 58753
phpbb_config = 19885
phpbb_topics = 18520
phpbb_user_browser_ids = 18381
phpbb_mchat = 16905
phpbb_forums = 11349
phpbb_topics_track = 5629
phpbb_posts = 4741
phpbb_forums_track = 3643
phpbb_topics_watch = 3275
phpbb_forums_watch = 2274
phpbb_attachments = 1933
phpbb_zebra = 1895
phpbb_bbcodes = 1677
phpbb_warnings = 1592
...
Запросы к phpbb_sessions:

f_queries.txt

(15.38 КБ) Скачиваний: 2099

[factotum]

apc на моем ресурсе :

Код: Выделить всё

Database Caching using apc
Object Caching 2270/2489 objects using apc


APC работает - база отдыхает

[Алексей]

factotum, в phpBB какое-то кэширование запросов делается, но не все.
Чтение из кеша:

Код: Выделить всё

$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;

Запись в кеш:

Код: Выделить всё

$cache->sql_save($query, $this->query_result, $cache_ttl);

APC следит за актуальностью данных в базе и сбрасывает их в кэш?
Или сбрасывает в кэш повторяющиеся запросы?
Или как-то по-другому работает?

[VEG]

Алексей, можете отключить подсчёт количества гостей в «Кто сейчас на сайте». Как минимум это сократит количество запрсоов к базе на один на каждый запрос страницы.