Очень высокая нагрузка на mysql на хостинге, как оптимизировать?

Question

[Cristalius]

Ребята, привет, большая проблема с высокой нагрузкой на базу mysql, хостинг начинает блочит (смена тарифа не вариант).

БД InnoDb выглядит как простая таблица excel, 18 столбов, но 2 ляма строк, весит 600 Мб.

Вывод базы данных реализован с помощью wordpress плагином wpDataTables. это большая база книг, в которой работают 7 человек, поиск, редактирование.
Основной вопрос - можно что-либо сделать, чтобы не было такой больной нагрузки? как то оптимизировать, может вывод с помощью другого плагина?

Сам сервер Apache


Буду благодарен любой помощи!

Comments

[Ипатьев]

Всю эту художественную самодеятельность надо из поста убрать,а привести ровно две картинки:

1. Код создания таблицы с книгами
2. список настроек mysql. Искать часы надо не под фонарем, а там где потерял. Если тормозит БД, то привдить надо настройки базы данных, а не веб-сервера

[Юрий]

Для начала стоит посмотреть структуру этой таблички, в частности, индексы, и какие sql-запросы на нее идут от плагина.
У WP много плагинов, которые в принципе не могут работать на больших объемах данных.

[Руслан Федосеев]

Ипатьев, Это shared хостинг, какие настройки мускула? кто туда пустит? )

Оптимизировать нагрузку - это отказаться от использования плагина и сделать по человечески. С проектированием запросов, форм, структуры БД...

[Stalker_RED]

На вашем хостинге можно посмотреть slow query log? Если он ведется то в нем будут все запросы которые выполняются дольше заданного значения. Вот с него-бы и начать.

[Ипатьев]

Руслан Федосеев, я писал не про хостинг, а про текст вопроса.

[Cristalius]

YBB,
Структура



Вывод в плагине

[Cristalius]

Руслан Федосеев, если бы я был гуд прогером, то сделал бы ручками ) А так , пока что умеем )

[Cristalius]

Stalker_RED, не могу сказать, наверное нет

[Ипатьев]

Cristalius, а вы правда думаете, что отвечая двум разным людям, надо два раза добавлять один и тот же набор картинок?

[Юрий]

Стоит проиндексировать поля (или комбинации полей), используемые в запросах в where и в order by. А для этого нужно видеть эти запросы. Я в аналогичной ситуации пользовался плагином Query Monitor: сидел, тыкал, смотрел, копировал в блокнотик... Потом в PhpMyAdmin, добавляя индексы, можно более-менее наглядно увидеть увеличение скорости обработка (или отсутствие увеличения, если не угадал). Можно попробовать посмотреть медленные запросы в mysql Slow log, но там не все однозначно, особенно на серверах с жесткими лимитами.

Вообще-то, проектирование БД - задача для специалиста в этой области (себя к ним не отношу ни в коей мере; так, погулять вышел).

[AlXan]

Для начала нужно смотреть mysqladmin processlist status, если есть доступ в консоль.

А в плагине mysql-query (что у тебя на последнем скрине) есть список запросов и их хар-ки.

[Анатолий Куликов]

Можно ещё начать с того, что у вас есть несколько полей, по которым ведется поиск === столбцам таблицы.

Для этих столбцов нужно добавить индексы - это уже может несколько ускорить процесс.

Затем, не совсем понятно, каким именно образом идет запрос в базу. Если через плагин с «магией плагина», то скорее всего этот запрос неоптимален - типа, ищет через like по всем столбцам. Поэтому дальнейшее ускорение - это уже написание запроса самостоятельно, мимо плагина, тем более, что таблица есть и у WP все нужные инструменты для этого имеются.

[Cristalius]

Анатолий Куликов,
вверху таблицы есть фильтры, по ним просто ищут, чаще всего используют Ozonid,


например ... на склад приехали книги, в накладной у них есть свой Ozonid, оператор вводит этот числовой id в поисковый фильтр, база ищет строку, если есть уже эта книга в базе - гуд, если нет, то оператор вводит новую строку прям во фронтэнде (плагин это позволяет) и забивает новые данные по столбцам.
По сути тут всегда тупо поиск в фильтре (есть книжка - нет книжки)

[Анатолий Куликов]

Cristalius, я их видел, да. Тут штука в том, что не понятно, как конкретно работает запрос. Я предположил, что т.к. тут пользователь может ввести что-угодно, поэтому и поиск будет проходить по запросу типа `field LIKE '%searchstring%'`. У вас тот же Ozonid не индексирован, а это может увеличить скорость поиска.

[Cristalius]

Анатолий Куликов, вчера поставил в индекс OzonId, посмотрю как будет, через терминал SSH еще сегодня понаблюдаю в рабочее время что будет происходит.

Вот пару запросов вчера делал:

# Time: 2023-04-27T00:01:16
# User@Host: a0788774_baza[a0788774_baza] @ localhost []
# Query_time: 3.512829 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use a0788774_baza;
SELECT SQL_CALC_FOUND_ROWS bookinistika.`id`, bookinistika.`OzonId`, bookinistika.`Postuplenie`, bookinistika.`Zakupcena`, bookinistika.`kolvo_up`, bookinistika.`ISBN`, bookinistika.`Name`, bookinistika.`Author`, bookinistika.`Year`, bookinistika.`Publishing`, bookinistika.`Price`, bookinistika.`Shk`, bookinistika.`IMT`, bookinistika.`kolvo_st`, bookinistika.`format`, bookinistika.`opis`, bookinistika.`zhanr`, bookinistika.`obl`, bookinistika.`Link` FROM bookinistika WHERE `bookinistika`.`OzonId` LIKE '147%' LIMIT 10;

# Time: 2023-04-27T00:01:54
# User@Host: a0788774_baza[a0788774_baza] @ localhost []
# Query_time: 3.412027 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use a0788774_baza;
SELECT SQL_CALC_FOUND_ROWS bookinistika.`id`, bookinistika.`OzonId`, bookinistika.`Postuplenie`, bookinistika.`Zakupcena`, bookinistika.`kolvo_up`, bookinistika.`ISBN`, bookinistika.`Name`, bookinistika.`Author`, bookinistika.`Year`, bookinistika.`Publishing`, bookinistika.`Price`, bookinistika.`Shk`, bookinistika.`IMT`, bookinistika.`kolvo_st`, bookinistika.`format`, bookinistika.`opis`, bookinistika.`zhanr`, bookinistika.`obl`, bookinistika.`Link` FROM bookinistika WHERE `bookinistika`.`OzonId` LIKE '14755%' LIMIT 10;
# Time: 2023-04-27T00:01:57
# User@Host: a0788774_baza[a0788774_baza] @ localhost []
# Query_time: 0.403229 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
use a0788774_baza;
SELECT COUNT(*) as cnt_total FROM bookinistika;

[Анатолий Куликов]

Cristalius, попробуйте в базе этот запрос выполнить

SELECT * FROM bookinistika WHERE OzonId = 14755 LIMIT 10

и тот, что показал анализатор

[Cristalius]

Анатолий Куликов,
выводит нулевой запрос и в терминале он не отображается.
А вообще существует такое - выгрузить всю базу sql в файл на комп и с помощью какой либо программы для ПК открыть эту базу и так же работать в ней? Поискал в поиске, но пока не нашел результатов

[Анатолий Куликов]

Cristalius, да, есть конечно.

[Анатолий Куликов]

Можно выгрузить дамп, поднять в докере локально мускуль, взять что-то типа HeidiSQL и через него туда дамп залить и тем же Хейди играть с запросами.

[Анатолий Куликов]

Cristalius, ну, возможно он ничего и не нашел. Можно попробовать сюда вставить OzonId, который точно в базе есть.

И вот если он его не найдет, то либо запрос неправильный, либо в неправильном месте вызывается.

Answer 1

[Алексец Долженко]

Для начала разобраться нагрузка при записи или чтении, так как стратегии разные для оптимизации.