Есть приложение работающее с базой данных MySQL. В нем параллельно работают несколько сотен пользователей. Используются InnoDB таблицы. Есть таблицы которые содержат несколько десятков миллионов записей. Запросы содержат много JOIN-ов, встречаются вложенные SELECT-ы. Часто WHERE условия строятся динамически (например когда пользователь настраивает фильтры).
Все это обычно работает нормально и довольно быстро. Но время от времени (несколько раз в месяц) внезапно резко начинает расти время исполнения почти ВСЕХ тяжелых запросов, даже тех, которые раньше исполнялись мгновенно. Растет с долей секунд до нескольких минут. При этом резко возрастает нагрузка на CPU со стороны MySQL, load average может многократно превысить количество ядер. В списке процессов MySQL начинают лавинообразно плодиться процессы с временем исполнения до нескольких минут (часто в состоянии сортировки или копирования временных таблиц, которые требуются для не очень оптимальных запросов). Все приложение начинает кошмарно тормозить и очень медленно отвечает. Проблему можно решить перезагрузкой сервера, но не всегда, если пользователи продолжают активно пытаться продолжать работать с приложением.
Бывает что БД просто начинает поддтупливать, но можно продолжать работать. А бывает что сервер уходит "в разнос", и единственное решение - перезагрузка MySQL или всего сервера.
При этом, во время таких ситуаций, не наблюдается особое увеличение активности пользователей. Вроде как обычная активность юзеров, ничего необычного. Полагаю что проблема связана с тем, что какие то пользователи строят какую то неудачную комбинацию SQL запросов, которая и пускает сервер "в разнос".
Смотреть slow_query_log бесполезно. В подобном состоянии системы, почти все запросы начинают валиться в этот лог, а в нормальном состоянии туда ничего не попадает.
Вопрос, как поймать, что именно провоцирует такую ситуацию? Куда копать?
Запросы к SQL-серверу могут идти не только от прямых действий пользователей, но и от вспомогательных процессов, например процесса резервного копирования, или иного процесса, запускаемого по крону.
В момент, когда сервер встает, вы изучали список процессов, для каких таблицы блокировались, а для каких нет, какого характера была недоступность сервера для остальных пользователей? Именно, ища в списке процессов активность не пользовательского приложения, а что-то еще.
alexalexes, Нет, в моменты торможения "чужих" запросов в списке процессов MySQL я не наблюдаю. Все запросы от самого приложения.
Характер недоступности для всех пользователей примерно одинаков - Тормозит все. Любые запросы тормозят, ну это естественно при многократном перегрузе CPU.
1 настройте мониторинг сервера: цпу, память, работа с хдд,
2 настройте мониторинг MySQL - чем больше чем лучше
3 потом уже изучайте графики - что в моменты перегрузки не соответствует обычной нагрузке - например создание огромных временных таблиц и тд.
А разве дедлоки приводят к резкой перегрузке CPU? Я полагал что задедлоченные процессы просто упираются друг в друга рогами, и стоят, ждут друг друга. Вроде CPU потреблять при этом не должны, просто должны стоять в режиме ожидания.
Но когда такая перегрузка опять случиться, я попробую SHOW ENGINE INNODB STATUS
Обратите внимание на кеш в приложении, ситуация похожа по описанию, на то, что в какой-то момент кеш протухает и начинает генериться заново и постоянно т.к запросов от клиентов очень много, в этот момент может нагнуться ваш сервер
Простой
