Как исправить ошибку: Too many connections в MySQL?
На проекте в MySQL выскакивает ошибка иногда: Too many connections
Иногда я имею ввиду, думаю это происходит когда идет наплыв онлайна на сайте и кроны у меня которые каждую минуту выполняются, может в совокупности когда еще начинает выполнятся крон резкий скачок запросов и появляется ошибка.
Может пару раз за день такое, было max_connections 100, а потом поменял на 150 перезагрузил сервер и снова повалило куча ошибок, но когда уже дальше работать стал вроде одна вылезла и все, ну типа реже стало.
использую mysqli
создается всегда объект бд для работы, а так закрываю соединение.
а при запросе соединения показывает практически постоянно 17-18
А вообще диапазон небольшой, 17 -25.
показывает эти 17 подключений, иногда больше и но большая часть из них спит.
Бд у меня около 500мб, на серваке 10гб озу, 8 ядер. Я думаю 150 коннектов для бд это много.
Хотя может и все ок.
Вообщем прошу направить, подсказать.
В чем может быть проблема ?
Может я вообще не так замеряю ? А если неправильно то как ?
Чаще всего причиной является либо говнокод, либо пконнект.
При переходе с mysql api на mysqli многие не учитывают, что каждое новое обращение к mysqli connect, в отличие от mysql connect, создает новое подключение. И таким обраом из одного и того же скрипта создается множество подключений.
Ну и обязательный карго культ — как только похапист узнает про pconnect, он тут же тащит его к себе в норку, и огребает те самые too many connections.
И это при том, что при 150 одновременных коннектах можно держать 50000 онлайн пользователей спокойно. Всего-то надо выкинуть пконнект, создавать соединение строго 1 раз и оптимизировать запросы.
FanatPHP
Скорее всего гавнокод.
Раньше было так, что при каждом запросе, внутри
public function query
в конце было
mysqli_close($this->link);
Я подумал что это полный бред, ну типа какой смысл ?
Если будет цикл и в нем будут запросы, цикл будет 100 итераций, я получу 100 коннектов и 100 разъединений, ну по крайне мере я так думал, и мне так показалось не логичным переместил в __destruct
Скрипт завершает работу, мы вызываем __destruct, а потом закрываем соединение.
Также у меня стоит nodejs может быть в нем проблема ? Там просто тоже есть коннект к бд, хотя нода работает всегда, я думаю там просто один коннект.
Онлайн порядка 500-1000 чел.
pconnect такого нет.
У меня просто mysqli_connect в классе.
А потом проверка через mysqli_ping
Куда смотреть на что смотреть ? Что почитать ?
Куда смотреть на что смотреть ?
на класс в котором есть public function query.
Сколько объектов этого класса создается за время работы скрипта? Есть ли в нему функция connect() и если есть, то сколько раз она вызывается?
Как объект этого класса попадает в другие объекты? Наследуются ли они от него? создается ли соединение в конструкторе?
Мда, в коде конечно много неясного.
И ты не ответил на вопрос, если в query было close, то значит был и connect? А куда он делся? И когда?
Запросы медленные есть? Сколько работает в среднем 1 скрипт?
Раньше при каждом запросе, внутри
public function query
в конце было
mysqli_close($this->link);
FanatPHP, Думаешь тогда это решит мою проблему если перепишу ?
У меня есть еще один класс, я так понимаю это синглтон https://pastebin.com/3mickJuZ
@ThunderCat
1. Правильно ли я понимаю что не нужно нигде делать
mysqli_close($this->link); это происходит автоматически после завершения скрипта. И данный код в принципе можно убрать.
2. На самом деле онлайна гораздо меньше, 300-500 постоянно, а иногда и более, но редко.
Думаю что max_connections 150 будет вполне достаточно.
3. У меня есть файл get-message.php на который каждый в аккаунте каждые 10 секунд идут запросы через таймер.
И этот файл выскакивает в числе тех где происходит данная ошибка и причем больше всех. Ну и кроны есть пару штук которые 1 раз в минуты запускаются.
Мне кажется надо убрать тогда может get-message.php и сделать это на ноде, у меня есть некоторые вещи на ноде, например онлайн.
Приложение думаю активно все таки работает с бэком. Там все на ajax и приложение не маленькое достаточно.
Ну и эти таймеры на js запускаются, не думаю что это хорошо.
это происходит автоматически после завершения скрипта. И данный код в принципе можно убрать.
У меня есть файл get-message.php на который каждый в аккаунте каждые 10 секунд идут запросы через таймер.
ThunderCat,
То есть решение такое, либо увеличить max_connections, но хз до скольки, минимум в 2 раза, я так понимаю.
Я посмотрел еще в гугл аналитикс, что в среднем где-то 100 просмотренных страниц пользователями при 250 онлайна, это если они одновременно сделают какое-то одно действие, будет 250 коннектов к бд и это уже далеко за лимиты, верно ?
Или допустим просто сделают какое-то действие 100 человек и накладутся на запросы через таймер который дергает сообщения, то тоже есть вероятность ошибки + всякие кроны могут быть, как минимум 3 крона запускаются каждую минуту. Верно ?
Я заметил что это происходи когда онлайн даже 250-300 уже может происходить ошибка, но когда людей больше ошибка присходит чаще, примерно раз в пол часа или час. Происходит это чаще под вечер, когда наплыв происходит.
Что есть я допустим сделаю чтобы сообщения чтобы дергались вообще раз в 30 секунд, итого 500*2 = 1000 в минуту. 1000 / 60 = 16 rps.
Скрипт делает один запрос, но не понимаю какая разница сколько он делает, все равно это же один коннект, так ведь ?
Ну или вообще все на ноже сделать и посылать нотификейшены о сообщении тем людям которым они пришли.
Еще у меня постоянно висит 8 запросов в режими sleep, они всегда спят и ничего не делают, это тоже мне непонятно.
Скрипт делает один запрос, но не понимаю какая разница сколько он делает, все равно это же один коннект, так ведь ?
В теории — да, как на самом деле хз, при условии наличия кода закрытия соединения не известно где «стреляет палка». Скорее всего соединение одно, это несложно проверить тупо вставив дебаги при создании соединения, при запросе и при закрытии.
Что есть я допустим сделаю чтобы сообщения чтобы дергались вообще раз в 30 секунд, итого 500*2 = 1000 в минуту. 1000 / 60 = 16 rps.
Че думать, трясти надо (с), поставь и проверь )
Подключение MySQL после ошибки 1040: слишком много соединений
И снова ERROR 1040…
Техподдержка получает много жалоб на эту печально известную ошибку: ERROR 1040: Too many connections — слишком много соединений. Проблема очевидна: приложение или пользователи создают больше соединений, чем допускает сервер, то есть текущее число соединений превышает значение переменной max_connections .
Ситуация уже сама по себе проблема для конечных пользователей, но если еще при этом у вас нет доступа к серверу для диагностики и исправления причины, все становится совсем плохо. Обычно приходится завершать экземпляр и перезапускать его, чтобы восстановить.
Root-пользователь тоже не может подключиться! Почему?!
В правильно настроенной среде пользователь с привилегией SUPER сможет получить доступ к экземпляру и диагностировать причину ошибки 1040, из-за которой не хватает соединений. Это описано в руководстве:
mysqld разрешает max_connections + 1 клиентских соединений. Дополнительное соединение зарезервировано для аккаунтов с привилегиями SUPER . Когда эти привилегии предоставляются администраторам, а не обычным пользователям (которым они и не нужны), администратор, у которого есть еще и привилегия PROCESS , может подключиться к серверу и использовать SHOW PROCESSLIST , чтобы диагностировать проблемы, даже если подключено максимальное число клиентов без привилегий.
Но куча людей дают привилегии SUPER своим пользователям приложения или скрипта — из-за требований приложения (опасно!) или незнания последствий, а потом зарезервированное соединение занимает обычный пользователь, а административный пользователь (обычно root ) не может подключиться.
Как гарантировать доступ к экземпляру
Можно использовать хорошо известный хак с GDB, который советовал Ауримас лет 100 назад для ошибки 1040, но теперь есть решения получше. Правда сначала их надо включить.
С Percona Server 5.5.29 и выше и MySQL 8.0.14 и выше можно настроить еще один порт с дополнительным числом соединений. Приложение не будет использовать эти интерфейсы. Они только для администраторов баз данных и агентов мониторинга и проверки работоспособности (см. примечание ниже).
Настройка Percona Server
Начиная с Percona Server 5.5.29 можно просто добавить extra_port в my.cnf , и при следующем перезапуске порт будет доступен и будет ожидать данные по тому же bind_address , что и обычные соединения. Если не настроить переменную extra_port , дополнительного порта по умолчанию не будет.
Еще можно определить extra_max_connections , чтобы задать количество подключений, которое будет обрабатывать этот порт. Количество по умолчанию — 1.
Для примера я занял все подключения к порту обычных пользователей у экземпляра, где уже настроил extra_port и extra_max_connections в my.cnf :
Кстати, extra_port удален в Percona Server 8.0.14 и выше, поскольку в MySQL Community реализован admin_port с теми же функциями. Так что отредактируйте my.cnf при апгрейде до Percona Server 8.0.14 или выше, если вы уже определили extra_port.
Настройка в MySQL Community
Как я уже сказал, для этого нужен MySQL 8.0.14, где применен WorkLog 12138.
Чтобы включить админский интерфейс, нужно определить admin_addres, который должен быть единственным и уникальным (без подстановочных символов) IPv4, IPv6, IPv4-сопоставленным адресом или именем хоста, по которому админский интерфейс будет ожидать передачи данных. Если эта переменная не определена, интерфейс не включен.
Еще можно определить порт, но это не обязательно. По умолчанию это порт 33062 . Если этот порт свободен, это значение не нужно настраивать. Если настраиваете, то поместите обе переменные в раздел [mysqld] в my.cnf .
Наконец, можно настроить create_admin_listener_thread (отключено по умолчанию), который создает отдельный поток для обработки входящих соединений. Это может пригодиться в некоторых ситуациях.
Еще одно различие — в документации Oracle сказано, что:
Число административных соединений не ограничено.
(А у нас значение по умолчанию — 1). Не уверен, что это значит, но я бы был осторожен, чтобы случайно не установить 1 млн соединений. Они, конечно, не ограничены, но ресурсы-то все равно потребляют.
Использование для мониторинга и проверок работоспособности
Удобно, что не только люди могут использовать дополнительный интерфейс или порт в экстренной ситуации, когда мы достигли max_connections . К нему может подключиться система мониторинга и проверки работоспособности прокси/балансировщика нагрузки/обнаружения сервисов.
Скрипты мониторинга смогут извлекать данные для диаграмм, чтобы потом вы разобрались, откуда столько соединений. А скрипты проверки работоспособности будут докладывать об ухудшившемся состоянии сервера, и определенный код может указывать, что соединений много, но сервер справляется (то есть может разобраться сам и лучше чуть дольше подождать до отработки отказа).
Обязательно устанавливайте только по одному соединению за раз для мониторинга и проверки работоспособности, чтобы не забивать extra_max_connections в Percona Server и не создать миллион потоков в MySQL. То есть скрипты не должны подключаться снова, если предыдущий запрос или подключение к базе данных еще активны.
Для Percona Server 8.0.14 и выше процесс будет тем же, что и для MySQL Community.
Помогите! Мне нужно войти, но все порты заняты!
Если это та самая причина, по которой вы читаете этот пост, используйте безумный хак с GDB (без обид, Ауримас, просто выглядит рисково :-D) или завершите экземпляр. К счастью, экземпляр почти всегда можно аккуратно завершить с помощью SIGTERM (-15) вместо SIGKILL (-9). Так сервер выполнит чистую остановку, и у потоков будет шанс нормально завершить работу. Просто следуйте инструкциям:
2) Отправьте SIGTERM в этот PID:
3) Следите в журнале ошибок, как выполняется завершение работы. Это будет выглядеть примерно так:
Это означает начало процесса завершения работы. Экземпляр будет завершен, когда вы увидите подобную строку:
Перевод not connected too many connections
not connected with — index immaterial Burton s Legal Thesaurus. William C. Burton. 2006 … Law dictionary
Connected space — For other uses, see Connection (disambiguation). Connected and disconnected subspaces of R² The green space A at top is simply connected whereas the blue space B below is not connected … Wikipedia
Connected space/Proofs — Every path connected space is connectedLet S be path connected and suppose, for contradiction, that S is not connected. Then S = A cup B for nonempty disjoint open sets A and B . Let x in A, y in B. Since S is path connected, there exists a… … Wikipedia
Connected Mathematics — is a comprehensive, problem centered curriculum designed for all students in grades 6 8 based on the NCTM standards. The curriculum was developed by the Connected Mathematics Project (CMP) at Michigan State University and funded by the National… … Wikipedia
Connected-component labeling — (alternatively connected component analysis, blob extraction, region labeling, blob discovery, or region extraction) is an algorithmic application of graph theory, where subsets of connected components are uniquely labeled based on a given… … Wikipedia
Connected Component Labeling — (alternatively connected component analysis) is an algorithmic application of graph theory, where subsets of connected components are uniquely labeled based on a given heuristic. Connected component labeling is not to be confused with… … Wikipedia
Connected Health — is a term used to describe a model for healthcare delivery that uses technology to provide healthcare remotely. Connected health aims to maximize healthcare resources and provide increased, flexible opportunities for consumers to engage with… … Wikipedia
Connected farm — in Windham, Maine. The barn dates from the late 18th century. The house was built in three stages during the 19th century. The unconnected garage was a 20th century addition. All doors of the structure are visible in this view from the south side … Wikipedia
Not Quite Connected — Studio album by Josh Woodward Released 2007 Genre … Wikipedia
Not eXactly C — Not eXactly C, or NXC, is a high level programming language for the Lego Mindstorms NXT. NXC, which is short for Not eXactly C, is based on Next Byte Codes, an assembly language. NXC has a syntax like C. The IDE for NXC is the Bricx Command… … Wikipedia
Connected (The Foreign Exchange album) — Connected Studio album by The Foreign Exchange Released August 24, 2004 Recorded 2003–2004 Genre Hip hop … Wikipedia