База данных грузит CPU в 100%. В чем может быть проблема?

Question

[Alexs47]

База данных грузит CPU в 100%. В чем может быть дело?
Салют всем, уже месяц мучаюсь с БД (MariaDb). Сразу скажу, слабо разбираюсь в этом вопросе, но честно пытался решить сам, единственный раз тут уже задавал такой вопрос давно и ответа всё равно не нашел. В общем, есть активный проект, уже (усилили машину недавно) стоит на VDS Intel Xeon E5 (cores 4 vCPU), 8 гб ОЗУ.
Версия сервера: 5.5.56-MariaDB. И вся начинка ставилась хостом, мы туда не лазили, просто поставили свой проект на хост.

Поначалу, всё было в порядке, однако сейчас проект разросся и база данных пользователей увеличилась.

На данный момент ситуация такая, что если 30 пользователей к примеру пытаются активировать промокод, то CPU взлетает до 100%, сайт просто не открывается. Если смотреть в монитор PMA, то при заходе 1 человека на сайт(обычом заходе, без действий) CPU поднимается на 2-3%.. А в графе вопросы появляется "пика" и количество вопросов становится 400+, а если никакой активности на сайте нет, то вопросы все равно в графике имеются, в количестве ~100-150 и CPU меньше 10% не падает никогда.

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

Скриншоты:1) Скриншот нынешнего состояния переменных БД (со старта mysql на скриншоте прошло 2 суток): https://c.radikal.ru/c06/1809/95/ecfa64208b00.png
2) Скриншот монитора в момент, когда нагрузки на сайт практически нет (максимум 2 человека зашло в этот период): https://c.radikal.ru/c35/1809/61/c9fe8d40c7bb.png
3) Скриншот ресурсов машины, слева - вчерашний вечер: https://b.radikal.ru/b04/1809/9d/f8b670efe8b3.png
4) И наконец скрин моего конфига бд, который я абы как с американских ресурсов собирал и пытался что-то менять ещё сам, смотря на результат: https://d.radikal.ru/d34/1809/0f/e6fb88b9e000.png и вот https://b.radikal.ru/b11/1809/70/757a3244f2d5.png остальное стоит по умолчанию.

Запросы уже оптимизировал насколько умею, перечитал про индексы и прочее, я вижу, что индексы есть и вижу, что БД оооочень много сканирует таблицы, но тут либо я не понимаю где и как правильно ставить индексы, либо иная причина, тяжелые таблицы оптимизировал с помощью Optimise и всё равно происходит та же картина.

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

На машине также стоит проксирование (защита от ддос), но оно не действует естественно на локальные запросы. Заранее всем огромное спасибо! Я надеюсь, что меня простят за моё незнание того, как всё работает, но я столкнулся с такой проблемой и нужно срочно её решать, поэтому я вынужден учиться и спрашивать. Спасибо!

Comments

[Сергей]

Срочно на фриланс.

[Alexs47]

Я пытался там найти в РФ специалистов, выбрал троих по отзывам и кто занят, кто не отвечает просто.. Такая ситуация длится долго, я не могу просто найти специалиста, который возьмется и чтобы адекватный был

Answer 1

[3vi1_0n3]

Не получится, наверное, объяснить много, но хотя бы начну.
1) Включить логгирование медленных запросов в БД
2) Есть такая команда - EXPLAIN - https://dev.mysql.com/doc/refman/8.0/en/using-expl...
Помогает понять как SQL-запрос должен работать
3) Индексами злоупотреблять тоже не надо. Обычно имеет смысл ставить индекс на поле в двух случаях: либо по этому полю связываются таблицы в JOIN'е, либо по нему происходит поиск. Больше индексов - больше потребление памяти.
4) Обычно настройка базы данных по моему опыту в итоге приходит к эмпирическому подбору размеров буферов и так далее, поскольку часто зависит от того, какие данные хранятся в базе и как обрабатываются
5) Все запросы надо посмотреть на предмет JOIN'ов и проанализировать при помощи EXPLAIN. Если две таблицы связаны по каким-то полям, не факт, что связывание будет в том направлении, в котором вы думаете. Тут на самом деле очень помогает построить схему SQL-запроса с таблицами и связями.
6) Иногда для того, чтобы индекс заработал, может потребоваться немного поменять SQL-запрос, зависит от БД и от планировщика запросов. Иногда помогает переписать запрос в обратном направлении или просто изменив порядок таблиц при связывании.
И т.д.
Я в данный момент решаю проблемы похожие вашим, но на PostgreSQL, принципы все те же. Иногда время выполнения запросов удается уменьшить до 15000 раз (на больших базах)

Comments

[Alexs47]

Спасибо, может я могу обратиться к Вам и решить эти вопросы непосредственно лично?

[3vi1_0n3]

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

[Alexs47]

3vi1_0n3, как связаться?

[3vi1_0n3]

Alexs47, напишите мне на почту [e-mail], у меня есть скайп или телеграм, в чем-нибудь словимся. Только у меня часовой пояс МСК+9, поэтому прямо сейчас не отвечу

Answer 2

[Алексей]

Ну во первых смотрите, сколько у вас неудачных соединений с БД - аж 66 штук. На нормальном проекте такого в принципе не должно быть. Наводит на мысль, что внутри проекта есть неоптимальные запросы или некорректно составленные, которые подвисают в момент нагрузки и не дают нормально подключаться другим. Это и объясняет подвисание PMA скорее всего - он банально не может подключиться к СУБД. И видимо это настолько печально, что даже ваших 2 тыс разрешенных подключений не хватает. Эти запросы можно отследить в самом MySQL, в консоли лучше всего командой SHOW FULL PROCESSLIST; - впрочем если будет нагрузка, скорее всего даже в консоль не сможете подключиться. Но если при нормальной работе сайта у вас там будет десяток подвисших запросов - явно дело пахнет не очень хорошо.
Смотрите еще Low Query - цифра тоже большая. Вам надо просмотреть эти запросы и определиться, что именно можно там изменить. В некоторых случаях достаточно сделать индекс на поле в таблице, чтобы в разы ускорить обработку запроса. А в некоторых нужно оптимизировать код приложения уже, чтобы заменить длинный запрос на более короткий и быстрый. Включите хотя бы временно логирование этих запросов в отдельный лог и разберите каждый из них.
Еще остается вопрос - что больше грузит CPU - именно MySQL или все же код приложения? На чем оно написано, фреймворк, CMS какие - все это нужно учитывать.

Comments

[Alexs47]

Спасибо за советы! Грузит CPU именно сервис MySQL. Как я понимаю - нативный php, собственная админ панель и от машины обычный isp, более ничего нет. Также вопрос, как и человеку выше, если Вы разбираетесь, могу ли я с Вами связаться и обсудить конкретно?

[Алексей]

Alexs47, Я всерьез эти не занимался, лишь делал примерный анализ вот примерно в таком виде, как вам и написал. Проблемы обычно в таком случае решали разработчики, я не лез туда.

[Alexs47]

Алексей, хорошо, спасибо большое в любом случае за информацию, думаю пригодится!

Answer 3

[vyrkmod]

Optimize помогает не везде и не от всего, читаем внимательно. Лог медленных запросов очень полезная штука. Если там оказываются частые запросы, все поля из условий в них - в индексы (какие конкретно - отдельное кунг-фу, вариант по-быстрому - каждое поле в отдельный индекс). Ну и ещё сильно помогает включить логирование всех запросов на тестовой машине (НЕ НА ПРОДЕ). Большое количество одинаковых запросов при прогоне одного кейса явно говорит о рукожопости разраба.

Comments

[Alexs47]

Спасибо за информацию!