Зависает MySQL

MySQL/PostgreSQL/SQLite/Oracle/M$SQL/....

Модератор: terminus

Правила форума
Убедительная просьба юзать теги [code] при оформлении листингов.
Сообщения не оформленные должным образом имеют все шансы быть незамеченными.
pascal
проходил мимо
Сообщения: 4
Зарегистрирован: 2011-10-26 9:59:57

Зависает MySQL

Непрочитанное сообщение pascal » 2011-10-26 11:07:40

Всех приветствую, давно посещаю лиссяру, но сегодня таки зарегистрировался на этом прекрасном сайте :)

По теме. Имеется веб-хостинг со ста клиентами и до недавнего времени всё было хорошо. Но в последний месяц стал обнаруживать, что mysql-сервер начал зависать при большом количестве запросов в базу. В обычном режиме мускуль съедает примерно 100-300 мегабайт памяти, но при зависании наблюдается следующая картина:
- Объём памяти занятый mysql-сервером 1-1.5гб
- Потребляемое процессорное время колеблется от 10 до 40%
- SHOW PROCESSLIST показывает, что все доступные подключения (max_connections=80) заняты разными клиентами и sql-запросами. Причём видно, что завис один самый первый и долгий sql-запрос на вставку (INSERT), за ним идут 9 запросов на очистку этой-же таблицы (TRUNCATE) в состоянии Locked. После них стоит очередь самых разных запросов других клиентов в состоянии Waiting for table.
Ограничение на число подключений одного клиента (max_user_connections) 10.
Такое чувство, что завис один самый первый запрос, а все остальные стоят в очереди и ждут-недождутся когда он отработает.
Ждать завершения зависшего sql-запроса бесполезно, некоторые запросы висят по нескольку часов.
Просить клиентов использовать качественный код тоже бесполезно - быдлокод был, есть и будет в любом случае.

На сервере 4гб памяти и два процессора по 2ггц, но mysql виснет не использовав и двух гб памяти. Подкрутил настройки my.cnf, но это лишь немного облегчило жизнь.
Пока что решил проблему следующим образом: php-скрипт работает в бекграунде и каждые 10 секунд смотрит SHOW PROCESSLIST на наличие долгих (больше 10 секунд) sql-запросов. При нахождении долгого запроса он убивается командой KILL. Мне кажется это некрасиво, неправильно и нужно курить настройки сервера, либо выставлять какие-то ограничения, вот только какие.... ведь справляются как-то хостеры с этой проблемой.

Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2460 рублей (8 CPU, 8Gb RAM, 2x500Gb HDD, RAID 3ware 9750):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

Аватара пользователя
vadim64
майор
Сообщения: 2098
Зарегистрирован: 2009-09-17 15:15:26
Откуда: Засратовец

Re: Зависает MySQL

Непрочитанное сообщение vadim64 » 2011-10-29 18:49:18

подписка
Люди не делятся на национальности, партии, фракции и религии. Люди делятся на умных и дураков, а вот дураки делятся на национальности, партии, фракции и религии.

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35056
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Зависает MySQL

Непрочитанное сообщение Alex Keda » 2011-10-30 18:32:57

2 vadim64
для подписки, достаточно ткнуть линк внизу
так, к сведению.
===
2 тредстартер
вы задачу опишите.

либо у вас шеред хостинг - тогда непонятна забота о клиентах - гоните их на ВПС или пусть код спрямляют, либо у вас внутренний проект - тогда непонятно зачем ограничения =))
Убей их всех! Бог потом рассортирует...

Аватара пользователя
vadim64
майор
Сообщения: 2098
Зарегистрирован: 2009-09-17 15:15:26
Откуда: Засратовец

Re: Зависает MySQL

Непрочитанное сообщение vadim64 » 2011-10-30 19:32:26

Alex Keda писал(а):2 vadim64
для подписки, достаточно ткнуть линк внизу
так, к сведению.
:-D
http://forum.lissyara.su/viewtopic.php? ... B0#p286053
http://forum.lissyara.su/viewtopic.php? ... B0#p288037
http://forum.lissyara.su/viewtopic.php? ... B0#p301072
я как бы уже в курсе, просто так удобнее, я на форум захажу через страницу http://forum.lissyara.su/search.php?search_id=egosearch
Люди не делятся на национальности, партии, фракции и религии. Люди делятся на умных и дураков, а вот дураки делятся на национальности, партии, фракции и религии.

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35056
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Зависает MySQL

Непрочитанное сообщение Alex Keda » 2011-10-30 23:49:50

захажу
захожу
Убей их всех! Бог потом рассортирует...

Аватара пользователя
vadim64
майор
Сообщения: 2098
Зарегистрирован: 2009-09-17 15:15:26
Откуда: Засратовец

Re: Зависает MySQL

Непрочитанное сообщение vadim64 » 2011-10-31 6:54:04

тьфу
Люди не делятся на национальности, партии, фракции и религии. Люди делятся на умных и дураков, а вот дураки делятся на национальности, партии, фракции и религии.

pascal
проходил мимо
Сообщения: 4
Зарегистрирован: 2011-10-26 9:59:57

Re: Зависает MySQL

Непрочитанное сообщение pascal » 2011-10-31 12:25:17

Alex Keda писал(а):2 тредстартер
вы задачу опишите.

либо у вас шеред хостинг - тогда непонятна забота о клиентах - гоните их на ВПС или пусть код спрямляют, либо у вас внутренний проект - тогда непонятно зачем ограничения =))
Да, у меня именно шаред-хостинг. Дело в том, что клиентов пока что чуть более сотни а чудеса уже началсиь. Когда клиентов будет, скажем так 800 (на одном сервере), логично предположить что такие и инциденты будут случаться минимум в 4 раза чаще. Если последовать вашему совету то в скором времени гонения начнутся не хуже чем при Сталине, а это не есть гуд. Задача - поставить ограничение на использование ресурсов, в частности на время выполнения sql-запроса. В общем хочется сделать так, чтобы тяжёлые sql-запросы одних клиентов не мешали жить другим клиентам.
Вообще, скриптик убивающий долгие запросы хорошо себя показал за несколько дней. Я думал может таблицы будут ломаться от частых KILL'ов запросов, но пока что всё норм :)
Но всёже интересно как другие хостеры борятся с перегрузкой сервера mysql.

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35056
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Зависает MySQL

Непрочитанное сообщение Alex Keda » 2011-10-31 12:45:56

никак =)

мы считаем процент использования процессора - на всех тарфиах есть лимиты
если долго и упорно превышешь - просто лочится аккаунт - с 10 до 19 часов, по МСК.
по запросу в техподдержку разблокируем, нет запроса - сам разлочится вечером.

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

в первую неделю запуска системы потеряли ~2% клиентов - в основном на микроскопических тарифах с 10-500 кратным превышением допустимой нагрузки
нагрузка на сервера упала в разы, в итоге выиграли - т.к. нормальные клиенты перестали жаловаться что тормозит и уходить к другим хостерам.
Убей их всех! Бог потом рассортирует...

pascal
проходил мимо
Сообщения: 4
Зарегистрирован: 2011-10-26 9:59:57

Re: Зависает MySQL

Непрочитанное сообщение pascal » 2011-10-31 13:01:09

Спасибо за совет, учту.
Кому нужен скриптик, могу скинуть по просьбе в личку.

Аватара пользователя
Alex Keda
стреляли...
Сообщения: 35056
Зарегистрирован: 2004-10-18 14:25:19
Откуда: Made in USSR
Контактная информация:

Re: Зависает MySQL

Непрочитанное сообщение Alex Keda » 2011-10-31 14:52:02

а не в личку - не можете?
Убей их всех! Бог потом рассортирует...