У нас есть веб-сайт на трех серверах. Два из них являются веб-серверами с балансировкой нагрузки, а последний - выделенным сервером mysql. Сервер mysql работает под управлением 64-разрядной версии RHEL5 с использованием ядра SMP 2.6.18-92.1.6.el5 # 1 и MySQL 5.0.45. Это тоже довольно мощный сервер с Xeon L5420 и 8 гигабайтами оперативной памяти. Страницы php нашего веб-сервера настроены на использование mysqli.
Обычно мы не используем все ресурсы, которые нам дают, мы делаем 20-25 запросов в секунду в дневное время. Однако время от времени мы сталкиваемся с максимальным количеством подключений к базе данных, и с этим наш сайт хрипит. Более того, похоже, что это происходит ночью, когда посещаемость сайта должна быть минимальной.
Мы начали с 100 максимальных подключений, мы повысили их до 300, но это все еще происходит. Если это имеет значение, мы замечаем, что иногда существует множество спящих процессов MySQL, но ничто из того, что подключается к базе данных, не использует постоянные подключения. Это не происходит каждую ночь, у нас были некоторые проблемы, из-за которых он квакал каждую ночь, а потом полторы недели до сегодняшнего дня было нормально.
У нас нет запросов-монстров, которые бы занимали базу данных на несколько минут. Мы попытались просмотреть журнал SLOW_QUERY. У нас есть несколько запросов, которые появляются там, но, как правило, они длятся не более 1-2 секунд и довольно редко.
Это похоже на что-то конкретное? Как мы будем поступать отсюда с точки зрения диагностики проблемы?
Я предполагаю, что в вашем приложении есть несколько длительных запросов. Когда они выполняются, они заставляют соединение оставаться вне пула в течение длительного времени (по сравнению с обычным шаблоном использования), это приводит к тому, что ваш пул исчерпывается, растет и продолжает расти до своего максимума, после чего любые оставшиеся рабочие блокируют ожидание освобождения соединений.
Первым делом нужно будет найти когда это случается, то есть циклическое событие или случайное. Если первое, вам повезло, так как вы можете быть готовы, когда это произойдет. Если вы не можете определить закономерность, вам придется проявить бдительность.
Вы можете понять это, просмотрев журналы мониторинга вашего веб-сайта или sar из вашей базы данных, чтобы увидеть, есть ли коррелирующие всплески.
Если вы можете поймать свою базу данных, когда она находится под нагрузкой, вы должны выполнить следующие команды на сервере mysql
show innodb status;
show processlist;
Первый распечатает диагностическую информацию о движке innodb (вы ведь используете innodb, верно?), Второй распечатает первые несколько сотен символов выполняемого запроса. Ищите запросы, которые выполняются в течение длительного времени, запросы, генерирующие временные таблицы на диске, и запросы, которые заблокированы для ресурса.
После этого начинается тяжелая работа. Использовать EXPLAIN для оценки стоимости запроса и используемых ресурсов. Избегайте запросов, требующих сортировки на диске с помощью таблицы tmp. Ищите длительные задания по отчетности или другие запланированные задачи обслуживания, которые периодически блокируют или насыщают вашу базу данных. Это может быть что-то столь же простое, как задача резервного копирования или задание, которое объединяет старые данные заказа на покупку.
Я рекомендую иметь эти три настройки в вашем /etc/my.cnf
log_slow_queries
log-queries-not-using-indexes
set-variable = long_query_time=1
Для веб-приложения, выполняющего 20-30 запросов в секунду, вы не можете позволить, чтобы что-либо отображалось в этих журналах.
кстати, ИМХО бессмысленно увеличивать размер пула соединений сверх вашего исходного размера, поскольку это только задержит начало исчерпания пула в лучшем случае на несколько секунд и только окажет большее давление на ваш db, когда он не нужен.
Я видел это раньше.
У нас был cron, выполняющий mysqldump базы данных с таблицами MyISM. Из-за MyISM дамп mysql блокировал целые таблицы. заставляя запросы (и, следовательно, соединения) стоять в очереди.
AlexMax, где можно решить этот вопрос? Хотя есть небольшие различия, я в настоящее время наблюдаю аналогичные проблемы, описанные здесь, с нагрузкой 14 веб-серверов, сбалансированной для 8 серверов mysql (каждый веб-сайт кодируется на одном из веб-серверов). Количество подключений будет увеличиваться, и более 90% подключений в списке процессов mysql будут указаны как спящие с NULL в запросе. Это заставит mysql перестать разрешать соединения и длится ок. 2-3 минуты. Mysql 5.0.70 и PHP 5.28 на Quad Xeon (хотя для нас 32 бит).