Появились медленные запросы mysql.

Вам необходимо грамотно настроить ваш сервер, как минимум mysql точно.

Стандартные настройки хорошо, но они не подходят для реальных задач.

mysqltuner вам в помощь, но делайте всё с умом, не надо ставить огромные значения,

также как и не всегда стоит слепо следовать рекомендациям тюннера.

Далее ваш SELECT затронул 1.4млн строк, это не есть хорошо.

Проверьте его через EXPLAIN, убедитесь чтобы запрос использовал индексы.

Если индексов в базе нет, то обязательно добавьте их где не хватает.

Точно так-же и с остальными медленными запросами.

Индекс тут бессмысленный, т.к. approve и allow_main скорее всего флаги, кардиналити у них будет очень низкий.

Ваш вариант - кешировать данные, или если точность не кретична - ходить в информационные таблицы.

А еще лучше выкинуть этот count вовсе... наверняка никому не нужный счетчик непоймичего.

У меня тоже есть такой тяжелый каунт на главной, я кеширую этот запрос через мем кеш, и проблема пропала.

Еще как вариант - изменитить логику вывода - выводить список постов с сортировкой не по дате, а по ID, на ID по идее должен быть первичный ключ и будет в разы быстрее.

он и так использует PK для подсчета, проблема в том, что у него WHERE, а без table scan на низком кардиналити у индекса будет перформанс ещё хуже, вот прямо сходил в доку и взял оттуда:

Этот запрос вряд ли отвечает за сам список постов, скорее за пагинатор, который определяет лимиты для последней страницы. Его только кешировать можно. Можно ещё повесить колонку с рандомным uuid, и на неё индекс, где WHERE условия будут совпадать. И потом по ней делать COUNT, но это костыль и так делать не надо. Лучше кешировать, да хоть в файл, и то будет быстрее 6 секунд, не говоря уже о memcached/redis.

Вобщем был у меня сервер уже с настроенным my.cnf -

тупо скопировал и все залетало)

Залетало у вас из-за

query-cache-size = 16M
query-cache-limit = 512K

которое на MySQL 8.0.3 уже не прокатит. да и в целом, конфиг такой себе.

обычно query_cache включают, и если на реальном workload он помогает, тогда его оставляют. но помогать он может только при убогом дизайне приложения. когда у вас есть запись, query_cache сбрасывается, а т.к. его фронтенд работает в 1 поток, то легко наткнуться на блокировку мьютексов. Другими словами, если у вас на 1000 чтений 1 запись - вам это поможет. Если у вас 1 запись на 50-100 чтений - может только усугубить.

И да, у вас сейчас конфиг заточен скорее под myisam. Если у вас innodb - можно по лучше потюнить.

И наверное с постраничной навигацией?

Также у вас таблицы в innodb или myisam? Если первое то он туго COUNT кушает, а вот с myisam не должно быть проблем.

Но как указали выше если у вас innodb то у вас вообще нет настроек под него.

Также у вас как идёт наполнение сайта? Автоматически т.е. парсер работает?

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

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