Как исправить ошибку: Too many connections в MySQL?

Question

[Дмитрий Тарасов]

На проекте в MySQL выскакивает ошибка иногда: Too many connections

Иногда я имею ввиду, думаю это происходит когда идет наплыв онлайна на сайте и кроны у меня которые каждую минуту выполняются, может в совокупности когда еще начинает выполнятся крон резкий скачок запросов и появляется ошибка.
Может пару раз за день такое, было max_connections 100, а потом поменял на 150 перезагрузил сервер и снова повалило куча ошибок, но когда уже дальше работать стал вроде одна вылезла и все, ну типа реже стало.

использую mysqli
создается всегда объект бд для работы, а так закрываю соединение.

public function __destruct()
{
    mysqli_close($this->link);
}

а при запросе соединения показывает практически постоянно 17-18
А вообще диапазон небольшой, 17 -25.



Делаю еще так

Show processlist;

показывает эти 17 подключений, иногда больше и но большая часть из них спит.

Бд у меня около 500мб, на серваке 10гб озу, 8 ядер. Я думаю 150 коннектов для бд это много.
Хотя может и все ок.

Вообщем прошу направить, подсказать.

В чем может быть проблема ?
Может я вообще не так замеряю ? А если неправильно то как ?

Answer 1

[FanatPHP]

Чаще всего причиной является либо говнокод, либо пконнект.

При переходе с mysql api на mysqli многие не учитывают, что каждое новое обращение к mysqli connect, в отличие от mysql connect, создает новое подключение. И таким обраом из одного и того же скрипта создается множество подключений.

Ну и обязательный карго культ - как только похапист узнает про pconnect, он тут же тащит его к себе в норку, и огребает те самые too many connections.

И это при том, что при 150 одновременных коннектах можно держать 50000 онлайн пользователей спокойно. Всего-то надо выкинуть пконнект, создавать соединение строго 1 раз и оптимизировать запросы.

Comments

[Дмитрий Тарасов]

FanatPHP
Скорее всего гавнокод.

Раньше было так, что при каждом запросе, внутри
public function query

в конце было
mysqli_close($this->link);

Я подумал что это полный бред, ну типа какой смысл ?
Если будет цикл и в нем будут запросы, цикл будет 100 итераций, я получу 100 коннектов и 100 разъединений, ну по крайне мере я так думал, и мне так показалось не логичным переместил в __destruct
Скрипт завершает работу, мы вызываем __destruct, а потом закрываем соединение.

Также у меня стоит nodejs может быть в нем проблема ? Там просто тоже есть коннект к бд, хотя нода работает всегда, я думаю там просто один коннект.

Онлайн порядка 500-1000 чел.

pconnect такого нет.

У меня просто mysqli_connect в классе.
А потом проверка через mysqli_ping

$this->link = mysqli_connect($host, $user, $pass, $db_name);

if (!mysqli_ping($this->link)) {
      mysqli_connect($host, $user, $pass, $db_name);
}

Куда смотреть на что смотреть ? Что почитать ?

[FanatPHP]

если в конце было mysqli_close($this->link);, то в начале должно быть mysqli_connect. а оно куда делось?

Куда смотреть на что смотреть ?

на класс в котором есть public function query.

Сколько объектов этого класса создается за время работы скрипта? Есть ли в нему функция connect() и если есть, то сколько раз она вызывается?
Как объект этого класса попадает в другие объекты? Наследуются ли они от него? создается ли соединение в конструкторе?

[Дмитрий Тарасов]

FanatPHP, от класса ничего не наследуется https://pastebin.com/jyLNYzcW
1. Сколько объектов этого класса создается за время работы скрипта?
Один объект в единой точке входа index.php
2. создается ли соединение в конструкторе?
Да, создается.

[FanatPHP]

Мда, в коде конечно много неясного.

И ты не ответил на вопрос, если в query было close, то значит был и connect? А куда он делся? И когда?

Запросы медленные есть? Сколько работает в среднем 1 скрипт?

[Дмитрий Тарасов]

FanatPHP,
1. Коннект всегда был в __construct
2. Загрузка главной страницы из личного кабинета

Время загрузки: 0.0056 сек.
Кол-во запросов: 7
Затрачено памяти: 78 Kb

[FanatPHP]

Не понял, если Коннект всегда был в __construct, то каким образом у тебя сайт работал, когда

Раньше при каждом запросе, внутри
public function query
в конце было
mysqli_close($this->link);

Ну вот закрыл ты соединение, а что делает следующий запрос?

[Дмитрий Тарасов]

FanatPHP, Честно я сам не понимаю как это вообще работало, но после каждого запроса реально стояло mysqli_close внутри public function query в конце.
То есть по сути закрылось и вообще потом ничего не должно было выполнятся, но оно работало. Я подумал что это какая-то дичь и убрал оттуда.

[FanatPHP]

Дмитрий Тарасов это могло работать только по одной причине - новый инстанс класса MySQL создается каждый раз, когда выполняется запрос.
Я бы, если честно, избавился от этого класса и переписал на чистый mysqli. А лучше PDO.

[Дмитрий Тарасов]

Дмитрий Тарасов, Кажись оно вот так переподключался к бд если не было соединения.

if (!mysqli_ping($this->link)) {
       $this->link = mysqli_connect($host, $user, $pass, $db_name);
}

[Дмитрий Тарасов]

FanatPHP, Думаешь тогда это решит мою проблему если перепишу ?

У меня есть еще один класс, я так понимаю это синглтон https://pastebin.com/3mickJuZ

[FanatPHP]

Дмитрий Тарасов, ну так это же в конктрукторе переподключается, а не в query.

[Дмитрий Тарасов]

FanatPHP, ну да в констукторе, а что на этот код скажешь ? https://pastebin.com/3mickJuZ

[FanatPHP]

Нет, никакой эт не синглтон.
Он конечно получше первого, но тоже ужас летящий на крыльях ночи.
Но должен хотя бы решить проблему с подключениями, если его применять правильно

[Дмитрий Тарасов]

FanatPHP, почему это не синглтон ?

[FanatPHP]

Потому что сможно насоздавать сколько угодно инстансов.

[Дмитрий Тарасов]

FanatPHP, я тут немного его поменял https://pastebin.com/3rujSkZR
Теперь по лучше и какие есть минусы у него ?

[Дмитрий Тарасов]

FanatPHP, И как лучше всего передавать параметры для подключения ?
Сейчас я просто туда константы сунул. Мне кажется это немного неверное решение.

Answer 2

[ThunderCat]

1) Нет смысла руками рвать соединение, при завершении скрипта коннект закроется автоматически. В вашем же случае весьма вероятна ситуация что коннект порвали зря и скрипт еще не отработал до конца.
2) Онлайн порядка 500-1000 чел.Для запаса я бы поднял количество соединений до 1/4 онлайна. Зависит конечно от специфики сайта, но иногда их реально не хватает если сервер с активным взаимодействием с бэкендом.
3) Для драйвера бд пишется враппер, не по тому что это модно или прям необходимо для работы, а как раз на такие случаи, в логику врапера пишется опциональное логирование запросов и сразу видно "кто где срал", сколько реально запросов бегает и что вообще происходит. Тогда можно что-то решать на уровне много/мало и куда что крутить. В частности, может оказаться что коннект происходит чаще чем вы думаете и вовсе не там где задумывалось...

Comments

[Дмитрий Тарасов]

@ThunderCat
1. Правильно ли я понимаю что не нужно нигде делать
mysqli_close($this->link); это происходит автоматически после завершения скрипта. И данный код в принципе можно убрать.

public function __destruct()
{
    mysqli_close($this->link);
}

2. На самом деле онлайна гораздо меньше, 300-500 постоянно, а иногда и более, но редко.
Думаю что max_connections 150 будет вполне достаточно.
3. У меня есть файл get-message.php на который каждый в аккаунте каждые 10 секунд идут запросы через таймер.
И этот файл выскакивает в числе тех где происходит данная ошибка и причем больше всех. Ну и кроны есть пару штук которые 1 раз в минуты запускаются.
Мне кажется надо убрать тогда может get-message.php и сделать это на ноде, у меня есть некоторые вещи на ноде, например онлайн.
Приложение думаю активно все таки работает с бэком. Там все на ajax и приложение не маленькое достаточно.
Ну и эти таймеры на js запускаются, не думаю что это хорошо.

[ThunderCat]

Дмитрий Тарасов,

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

Именно так.

У меня есть файл get-message.php на который каждый в аккаунте каждые 10 секунд идут запросы через таймер.

ну давайте считать. 500 пользователей, кроме работы со страницами (которые сами по себе создают некоторый объем запросов, типично 10-20 на страницу), у вас каждый юзер создает 6 соединений в минуту, то есть 500*6 = 3000 в минуту. 3000/60 = 50 рпс (это если скрипт только 1 запрос дергает). Плюс они накладываются друг на друга местами, 3 наложения - как раз ваши 150 рпс.

[Дмитрий Тарасов]

ThunderCat,
То есть решение такое, либо увеличить max_connections, но хз до скольки, минимум в 2 раза, я так понимаю.
Я посмотрел еще в гугл аналитикс, что в среднем где-то 100 просмотренных страниц пользователями при 250 онлайна, это если они одновременно сделают какое-то одно действие, будет 250 коннектов к бд и это уже далеко за лимиты, верно ?

Или допустим просто сделают какое-то действие 100 человек и накладутся на запросы через таймер который дергает сообщения, то тоже есть вероятность ошибки + всякие кроны могут быть, как минимум 3 крона запускаются каждую минуту. Верно ?

Я заметил что это происходи когда онлайн даже 250-300 уже может происходить ошибка, но когда людей больше ошибка присходит чаще, примерно раз в пол часа или час. Происходит это чаще под вечер, когда наплыв происходит.

Что есть я допустим сделаю чтобы сообщения чтобы дергались вообще раз в 30 секунд, итого 500*2 = 1000 в минуту. 1000 / 60 = 16 rps.
Скрипт делает один запрос, но не понимаю какая разница сколько он делает, все равно это же один коннект, так ведь ?

Ну или вообще все на ноже сделать и посылать нотификейшены о сообщении тем людям которым они пришли.

Еще у меня постоянно висит 8 запросов в режими sleep, они всегда спят и ничего не делают, это тоже мне непонятно.

[ThunderCat]

Дмитрий Тарасов,

Скрипт делает один запрос, но не понимаю какая разница сколько он делает, все равно это же один коннект, так ведь ?

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

Что есть я допустим сделаю чтобы сообщения чтобы дергались вообще раз в 30 секунд, итого 500*2 = 1000 в минуту. 1000 / 60 = 16 rps.

Че думать, трясти надо (с), поставь и проверь )

алсо по теме: https://dba.stackexchange.com/questions/1229/how-d...
https://bluemedora.com/mysql-performance-max-conne...

Answer 3

[Vitaly Karasik]

По-моему, вы начали не с того.
connections - вещь довольно "дешевая" и не проблема увеличить лимит connections.
Смотреть надо на другие метрики - slow queries и IO, плюс активизировать/оптимизировать cache.

Comments

[Katrychenko]

Сам понял что написал? 50 тыс пользователей онлайн подразумевают 50 тыс одновременных конектов. На 150 конектов можно получить ровно 150 одновременных обращений. Другое дело просто 50 тыс пользовался которые переодически обращаются к сайту и то я не уверен что работа будет стабильной