Высокая нагрузка от phpBB 3.3

[digitalfarseer]

digitalfarseer,
Вам, как и мне, наверное показалось, потому что

phpBB не при делах

У меня нет высокой нагрузки на процессор, нет медленных запросов (очевидно, они просто не попадают в slow.log), но при этом форум еле ворочается. Даже панель администрирования открывается ощутимо дольше, чем на 3.2.8.

[digitalfarseer]

Похоже, что это совпадение и проблема была где-то вне сервера с phpbb. Видимо, доступность сервера для cloudflare была нарушена или btrfs на NAS индексировал новые файлы, из-за чего всё тупило. Ничего не трогал пару часов и всё пришло в норму.

[pico]

Пагинация с limit offset для больших таблиц известная и насущная проблема, начиная ещё с phpBB 2.x, решений кучу можно найти, той или иной степени костыльности.
Самый простой вариант, отказаться от offset, добавить поле c позицией поста в топике, и делать выборки оператором сравнения, минусы - нужно перестраивать позиции при изменении/удалении постов в топике начиная с последнего изменённого, что в принципе не так уж страшно, и большого оверхеда не вызывает, т.к. изменяются посты в основном на последних страницах топика, плюсы - увеличение производительности на порядок.

https://www.eversql.com/faster-paginati ... t-is-slow/
https://habr.com/ru/post/44608/
https://dba.stackexchange.com/questions ... iting-them

SET timestamp=1581073377;
SELECT p.post_id
FROM phpbb_posts p
WHERE p.topic_id = 638997
AND ((p.post_visibility = 1))
ORDER BY p.post_time ASC, p.post_id ASC
LIMIT 28650, 30;

Там выборка идет так: начиная с 28620 показать 30 записей.

Подобные запросы не должны вешать базу данных. Они лёгкие

[rxu]

добавить поле c позицией поста в топике

Это нерабочий вариант (часть причин сами же и указали). Позиция поста в топике - величина непостоянная. Топик может быть разделен, объединен с другим, туда могут быть перенесены посты из другой темы, удалены (причем софтово или безвозвратно), да и вообще позиция поста в топике может поменяться в зависимости от типа сортировки.
Не говоря уже о том, что цитируемый "тяжелый" запрос как раз и представляет из себя выборку по primary index, которым и является post_id, аналогичная которой как раз и приводится в качестве подзапроса по ссылкам на "решение" данной "проблемы".
Другие предложения есть?

[pico]

Не говоря уже о том, что цитируемый "тяжелый" запрос как раз и представляет из себя выборку по primary index, которым и является post_id, аналогичная которой как раз и приводится в качестве подзапроса по ссылкам на "решение" данной "проблемы".

Информация по ссылкам не только проблему индексов затрагивает, но и большие offset также.

SELECT **id** FROM **table** LIMIT 100 OFFSET 2500000
Если вы используете LIMIT и OFFSET следующим образом, MySQL обрабатывает запрос так, как если бы вы сказали (в данном случае) LIMIT 250100, а затем просто отбрасывает (то есть не возвращает Вам) первые 2500000 строк. Это не эффективный способ.

Чудес не бывает, software architecture это всегда trade-off, нужно чем-то жертвовать, в данном случае размениваем performance на flexibility.

Вообще пересчёт позиции можно прозрачно для веб-приложения реализовывать, той-же триггерной процедурой при изменении данных в СУБД.
https://postgrespro.ru/docs/postgresql/ ... ql-trigger

Хорошего варианта имплементации классической пагинации без offset для больших таблиц с дырами с возможностью запроса любой страницы я увы предложить нем могу, есть алгоритмы без механизма offset, но это всё костыли ещё большие

[pico]

Без изменения структуры БД можно самые простые оптимизации делать
Как это имплеметировано у «конкурентов» например https://github.com/SimpleMachines/SMF2. ... y.php#L866

1. Определение наиболее выгодного смещения, сначала или с хвоста
2. Использование сессий для хранения стейта пагинации юзера
3. Снижение больших оффсетов кешированием и использование индекса

https://ruhighload.com/%d0%9f%d0%be%d1% ... 0%b2+mysql