Golang error 1040 too many connections

Привет Хабр, меня зовут Богданов Илья, я работаю ведущим инженером в команде веб-разработки. Сегодня я вам расскажу как настроить стандартную библиотеку Golang так, чтобы избежать неожиданных ошибок в production.

Я программирую на Go более двух лет и за 2019 год выпустил в production пять микросервисов: от простых с одной задачей до сложных со своей доменной моделью и несколькими внешними зависимостями. До этого я четыре года программировал на C++, продолжаю увлекаться трехмерной графикой и микроконтроллерами. За это время было набито немало шишек и получено много опыта, которым я хочу с вами поделиться.

Моя статья ориентирована в основном на начинающих разработчиков, но ветераны Go тоже, вероятно, смогут узнать что-то новое.

Коротко, о чем будет статья:

• о том, как не ловить ошибки соединения с базой данных на production;

• http.Client и что не так с клиентом по умолчанию;

• http.Server и его подводные камни;

• и, наконец, рассмотрим пару занятных проблем, не связанных напрямую с настройкой стандартной библиотеки.

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

sql.DB

sql.DB — это пул соединений, которые могут открываться при отправке запросов и автоматически закрываться специальной горутиной. Пользоваться очень просто: открываем соединение, отправляем запросы, по завершении программы закрываем. 

Мы проверили на тестовом окружении, все работало как надо, никаких проблем и решили поехать в production.

db, err := sql.Open("mysql", "root:localhost/test")
if err != nil {
  return err
}
defer db.Close()
_, err := db.Exec("...")

Скоро после релизов по утрам мы стали иногда получать ошибки соединения с базой или вовсе непонятный EOF.

failed to begin a transaction: invalid connection
unexpected EOF

Пришлось обратиться к документации. Тип sql.DB имеет три метода для конфигурации. 

SetMaxOpenConns

Этот метод ограничивает максимальное количество соединений с базой. По-умолчанию там стоит ноль, то есть лимит отсутствует и сервис может устанавливать столько соединений с базой данных, сколько ему вздумается, в то время как ресурсы базы данных и микросервиса ограничены, поэтому ограничение определенно не помешает. Для выбора оптимального значения мы стали профилировать. 

Воспользуемся встроенным в язык бенчмарком. В нем мы открываем соединение с базой, выставляем разные значения максимального числа соединений и начинаем в неё параллельно записывать строки по 255 символов. Результаты бенчмарка в таблице.

В левом столбце указано максимальное количество соединений, затем идет время, затраченное на каждую операцию вставки в наносекундах (соответственно, чем меньше, тем лучше), затем идет число байт оперативной памяти, выделенной для выполнения операции и число аллокаций на операцию. Последние два столбца нам не особо интересны, т. к. значения почти не меняются в зависимости от числа соединений.

Как мы видим, при одном соединении с базой данных производительность примерно в пять раз меньше, чем при неограниченном числе соединений. В данном случае оптимальным будет примерно 10 соединений, т. к. дальнейшее увеличение числа соединений не дает прироста производительности, выходящего за рамки погрешности. 

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

Но что же это получается, чем больше соединений, тем лучше? Зачем нам ограничивать количество соединений и потенциально терять производительность? А вот зачем. 

[mysqld]
max_connections=10

Benchmark_MaxOpenConns20
    Benchmark_MaxOpenConns20: main_test.go:69: Error 1040: Too many connections
    Benchmark_MaxOpenConns20: main_test.go:69: Error 1040: Too many connections
    Benchmark_MaxOpenConns20: main_test.go:69: Error 1040: Too many connections
...
-- FAIL: Benchmark_MaxOpenConns20 
FAIL

Для симуляции проблемы я ограничил количество соединений со стороны базы данных (в моем случае это MySQL). В реальной системе такой лимит тоже стоит, просто он повыше, но учитывая, что время запроса будет гораздо выше, чем на локальной машине, а микросервисов, подключенных к одной базе, может быть несколько… упереться в лимит вполне реально. 

Вернемся к нашим бенчмаркам: как только число соединений со стороны микросервиса превысило лимит базы данных, мы тут же упали с ошибкой. Это не повод бежать в настройки MySQL и бесконтрольно увеличивать количество соединений базы данных, ведь каждое соединение потребляет ресурсы не только на стороне микросервиса, но и на стороне базы данных. Если хотите в этом убедиться, можно поиграться с калькулятором потребления памяти MySQL с разным количеством соединений https://mysqlcalculator.com

SetMaxIdleConns

Второй метод позволяет ограничивать количество соединений, которые могут остаться открытыми, для повторного использования, после того как все запросы в очереди будут выполнены. Если установить этот параметр в 0, то соединения перестанут переиспользоваться и на каждый запрос будет создаваться новое. Пока не особо понятно, как это повлияет на производительность и стабильность. Значит, время побенчмаркать  

Точно такая же таблица, как и с прошлым методом, но вместо максимального числа соединений мы меняем число соединений для переиспользования (первый столбец). Как мы видим, если совсем уж сильно не ограничивать переиспользование соединений, то производительность не упадет (но обратите внимание: число и размер аллокаций при запрете переиспользования сильно возрастает — вот цена установления соединений). На самом деле эта настройка не оказывает такого сильного влияния, как остальные две, поэтому, если у вас не высоконагруженный сервис, её можно оставить в значении по умолчанию, то есть 2. Как мы видим, 2 — это оптимальное значение. А вот если сервис нагруженный, то спасет только собственный бенчмарк для выяснения подходящего значение. 

SetConnMaxLifetime

Последняя настройка позволяет нам ограничивать время, в течение которого соединение можно переиспользовать. По её названию можно подумать, что она обрывает соединения, после того как время жизни соединения превысило лимит, но все хорошо: пока запрос выполняется, соединение не разорвется, оно просто не будет переиспользоваться для новых запросов. Вновь по умолчанию ограничение отсутствует, что позволяет соединениям висеть часами, днями, неделями… Естественно, мы хотим это время ограничить, ведь долгоживущие соединения опасны, т. к. они могут внезапно разорваться или висеть бесполезным грузом, сжирая ресурсы. А чтобы выбрать оптимальное значение, мы, конечно же, побенчмаркаем. 

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

Что же имеем в итоге?

• В стандартной конфигурации отсутствует лимит на количество соединений. Это значит, что микросервис может установить огромное количество соединений с базой данных, которые будут расходовать ресурсы и приводить к ошибкам, если база не способна поддерживать такое количество

• Соединения могут переиспользоваться после долгого простоя. Соединение, которое несколько часов или дней висело без запросов, может быть выбрано для очередного запроса, что может привести к нестабильной работе.

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

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

Проблему мы решили, но как подобрать оптимальные настройки для вашего сервиса?

В первую очередь оцените нагруженность сервиса. Если сервис должен постоянно обрабатывать тысячи запросов в секунду, то стоит увеличить максимальное количество соединений с БД, количество idle соединений и время жизни соединения. Также можно задуматься о выделенном инстансе базы данных для этого микросервиса. Если же микросервис не сильно нагружен, можно сэкономить ресурсы сервиса и базы и сократить число используемых соединений.

Во-вторых, проанализируйте конфигурацию базы данных: сколько она позволяет создать соединений (стоит оставить небольшой запас свободных соединений — около 5 шт.) на случай, если вы захотите подключиться из консоли + база данных не может мгновенно создать новое соединение взамен старого, будет небольшой период времени, когда существуют оба соединения, и тут вам запас пригодится).

Также можно воспользоваться методом Stats() у sql.DB, который вернет не только информацию о текущих соединениях, но и статистику по ожиданию соединений: сколько раз мы уткнулись в необходимость ожидания соединения и сколько времени потратили впустую, ожидая соединение. Можно вывести эту статистику во внутренний API и изредка мониторить

Ну и, наконец, читайте документацию к языку! В Go прекрасная документация как на сайте golang.org, так и в самом коде, не поленитесь почитать её для типов, которые вы используете. 

Вот пример конфигурации для низконагруженного сервиса. 

db, err := sql.Open("mysql", "root:localhost/test")
if err != nil {
   return err
}
defer db.Close()
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(time.Minute)
_, err := db.Exec("...")

25 соединений более чем достаточно, соединения будем пересоздавать каждую минуту, а idle соединений оставим стандартное значение — 2. Мы дописали всего три строчки к тому коду, который был вначале, и улучшили надежность микросервиса!

http.Client

Этот тип используется для отправки HTTP, что позволяет как общаться с другими вашими сервисами через REST API, так и использовать внешние API, скачивать или заливать файлы и т. д.

И вновь он требует настройки для корректного функционирования. Что еще более странно, пакет http включает DefaultClient, содержащий значения по умолчанию и функции Get, Post и т. д., его использующие. Соответственно, с этими функциями мы ничего не сможем сконфигурировать, и они будут работать без таймаутов, и в production я бы не рекомендовал их использовать.

http.Client.Timeout — самый простой таймаут, распространяющийся на все время выполнения запроса: от установления соединения до завершения чтения тела ответа. Идеально подходит для вызова API. Если же нам нужно скачать файл или стримить данные, он нам не подходит, и для этих случаев нам пригодятся следующие таймауты.

net.Dialer.Timeout — ограничивает время нахождения хоста и установления HTTP-соединения. Пригодится, чтобы не висеть долго на запросе, если удаленный сервер не отвечает.

http.Transport.TLSHandshakeTimeout — если вы используете HTTPS (а стоит, если ваш сервис доступен снаружи), то этот таймаут позволит ограничить время на установление защищенного соединения. Если же вы не используете HTTPS (например, если ваш сервис доступен только внутри кластера), то этот параметр не используется.

http.Transport.ResponseHeaderTimeout — ограничивает время чтения заголовков ответа. Может быть полезен, чтобы не зависнуть при общении с сервисами, которые долго думают после получения запроса.

http.Transport.IdleConnTimeout может ограничивать длительность keep-alive соединений, но об этом я расскажу дальше.

Как вы можете заметить на картинке, непокрытыми остались отправка запроса и получение тела ответа. Что же делать, если нам нужно передать или получить большой объем данных? Оставить передачу данных без таймаута опасно, ведь в случае, если удаленный сервис просто пропадет из сети, мы так и останемся висеть в процессе копирования. Чтобы этого избежать, нам нужно что-то более гибкое… например, динамический таймаут.

c := &http.Client{}
resp, _ := c.Get("https://host.com")
defer resp.Body.Close()

timer := time.AfterFunc(5*time.Second, func() {
   resp.Body.Close()
})

bodyBytes := make([]byte, 0)
for {
   timer.Reset(5 * time.Second)

   _, err = io.CopyN(bytes.NewBuffer(bodyBytes), resp.Body, 256)
   if err == io.EOF {
       break // данные закончились, выходим
   } else if err != nil {
       panic(err)
   }
}

Что тут происходит? Сначала мы устанавливаем соединение и подготовимся к скачиванию файла. Далее настроим таймер на небольшой промежуток времени. Если таймер сработает, значит мы не успели вовремя получить свою порцию данных и нужно завершить операцию с ошибкой. Затем запустим бесконечный цикл, в котором будем считывать следующий кусок данных и сбрасывать таймер. Таким образом, мы ставим условие, что за каждые пять секунд мы должны скопировать по меньшей мере 256 байт или получить ошибку. Как только мы дочитали до конца, мы получим ошибку io.EOF и выйдем из бесконечного цикла

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

Повторное использование соединений (Keep-Alive)

Также я хотел затронуть тему повторного использования соединений (Keep-Alive). Этот механизм позволяет открыть соединение с сервером один раз и посылать по нему несколько запросов, вместо того чтобы открывать новое соединение на каждый запрос. Особенно это полезно в случае использования HTTP-соединения, т. к. позволяет пропустить процесс handshake для всех запросов кроме первого. Что же нам предлагает Go по части переиспользования соединений? 

За него отвечает тип http.Transport, который по умолчанию оставляет открытыми до 100 соединений с таймаутом 90 секунд. Т. е. после того как мы сделали запрос на какой-то сервер, соединение с ним останется в пуле http.Transport для повторного использования в течение 90 секунд. Это звучит разумно, и в общем случае менять не стоит.

При этом присутствует дополнительное ограничение на два соединения на хост (MaxIdleConnsPerHos), т. е. если вы общаетесь только с одним сервером или проводите нагрузочное тестирование микросервиса (при котором будет много запросов к одному серверу), то имеет смысл увеличить этот лимит, изменив свойство MaxIdleConnsPerHost. Но в общем случае так делать не стоит, чтобы не исчерпать весь пул keep-alive соединений на один хост

Также, исследуя эту тему при подготовке статьи, я наткнулся на утверждение, что Keep-Alive ломается, если тело ответа не было считано либо было не закрыто. Я сам не проверял, но в целом будет хорошей практикой всегда считывать тело ответа, даже если оно вам не нужно. Вот пример кода, как это можно сделать, — отправить в ioutil.Discard. А чтобы не писать так каждый раз, можно сделать обертку над клиентом, которая всегда будет читать тело ответа и корректно закрывать его.

res, err := client.Do(req)
io.Copy(ioutil.Discard, res.Body)
res.Body.Close()

Итак, мы разобрали настройки http.Client и готовы настроить его для использования в сервисе. Если ваш http.Client используется исключительно для вызовов API, которые не должны занимать много времени, можно поставить маленький таймаут на всю обработку запроса, к примеру пять секунд, и не заморачиваться тонкими настройками. Если же вы передаете большие объемы данных, либо обработка запроса может занять продолжительное время, или вы вообще стримите данные, то я рекомендую использовать динамический таймаут, а также установить Dialer.Timeout и ResponseHeaderTimeout

http.Server

Где клиент, там должен быть и сервер. Значит, посмотрим на http.Server. Он позволяет вашему сервису отвечать на HTTP-запросы: реализовывать API, отдавать и принимать файлы, отдавать статические страницы и много всего прочего. И как вы уже могли догадаться, в нем опять нет таймаутов. В этот раз стандартная библиотека нам предоставляет два варианта для ограничения запросов и один вариант для управления Keep-Alive.

• ReadTimeout начинает отсчет с начала чтения заголовков запроса и заканчивает после завершения считывания тела запроса.

• WriteTimeout же отсчитывает с чтения тела запроса и заканчивает после возвращения ответа.

• IdleTimeout, как вы могли догадаться, отвечает за Keep-Alive.

Вам может показаться странным, что ReadTimeout и WriteTimeout имеют пунктирное продолжение, вплоть до самого начала соединения. Так вот, таково поведение при использовании TLS, т. е. если вы используете безопасное соединение, то оба таймаута стартанут в момент инициации соединения. Стоит это учитывать при подборе значений таймаутов

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

1. http.TimeoutHandler Например, вы можете обернуть свой обработчик http.Handler в http.TimeoutHandler и он автоматически оборвет запрос с кодом 504, если ваш обработчик работал слишком долго. Это может быть полезно, если вы хотите чтобы часть ваших сервисов работала с маленьким таймаутов, а часть — с большим ручным таймаутом.

2. ReadHeaderTimeout, который отслеживает время до окончания чтения заголовков запроса (в отличие от ReadTimeout, который включает в себя и чтение тела запроса). Он может быть полезен, если вам нужно прочитать большое тело запроса и вы не знаете, сколько это займет времени, и для тела собираетесь применять наш следующий вариант.

3. Динамический таймаут. Так же, как мы использовали копирование по частям при работе с клиентом, мы можем отдавать данные кусками и на стороне сервера. Если клиент не успевает принять часть данных, можно считать это ошибкой.

4. context.Context. Контекст с ограничением по времени, но это не особо удобно: в ответе на запрос, завершившийся по таймауту вы получите ошибку “context deadline exceeded” вместо ожидаемого таймаута, и вам придется руками преобразовывать эту ошибку для вашего API.

Итак, что мы узнали про http.Server: для простых кейсов, где мы вызываем только API, нас вполне устроит установка стандартных таймаутов. В более сложных случаях нам пригодится ReadHeaderTimeout и динамический таймаут для тела запроса и тела ответа.

Пример конфигурации для API:

srv := &http.Server{
    ReadTimeout:  5 * time.Second,
    WriteTimeout: 10 * time.Second,
    IdleTimeout:  120 * time.Second,
}

Пример конфигурации для передачи файлов и стриминга:

srv := &http.Server{
    ReadHeaderTimeout: 5 * time.Second,
    IdleTimeout:      120 * time.Second,
}

А также стоит использовать динамический таймаут, чтобы не зависнуть в процессе передачи данных

В качестве бонуса я бы хотел рассказать о паре проблем, с которыми мы сталкиваемся при работе с микросервисами. Они не относятся напрямую к стандартной библиотеке Go, но, думаю, вам будет интересно.

Пользовательские блокировки MySQL

Первая проблема всплыла недавно, когда мы решили использовать пользовательские блокировки MySQL в одном из микросервисов. Это команды GET_LOCK и RELEASE_LOCK

В end2end тестах микросервиса никаких проблем первоначально не вылезло, и в тестовом окружении тоже все работало как надо, но затем запросы стали иногда валиться с ошибкой, что невозможно освободить лок, который мы не занимали. Это показалось нам очень странным (ведь в большинстве случаев этот же код работал без ошибок), и мы полезли читать документацию MySQL к этим функциям.

Оказалось, что эти локи привязаны к соединениям базы данных и если соединение разрывается, то и лок автоматически освобождается при разрыве соединения. И тут до нас дошло, что sql.DB — это пул соединений и в итоге захват и освобождение лока могут происходить в совершенно разных соединениях. Решением для нас оказалось перенести блокировку внутрь транзакции. В этом случае стандартная библиотека гарантирует, что все операции будут произведены на одном соединении, которое не будет разорвано между командами. Альтернативным решением было бы использовать выделенное соединение с помощью метода Conn(), но раз уж у нас уже использовались транзакции, мы воспользовались ими. Рассмотрим подробнее на примере кода:

var result int
err := db.Get(&result, “SELECT GET_LOCK(`lockname`, 60)”)
// execute some sql transaction
err := db.Get(&result, “SELECT RELEASE_LOCK(`lockname`)”)
if result != 1 {
	panic(“cannot release a lock that is not acquired”)
}

А после исправления стало так:
tx, err := db.Begin()
var result int
err := tx.Get(&result, “SELECT GET_LOCK(`lockname`, 60)”)
// execute some sql commands
err := tx.Get(&result, “SELECT RELEASE_LOCK(`lockname`)”)
if result != 1 {
	panic(“cannot release a lock that is not acquired”)
}
err := tx.Commit();

Раньше у нас сначала происходил захват лока при использовании соединения из пула, потом выполнение транзакции и освобождение лока. После исправления у нас сначала создается транзакция, затем в ней происходит захват лока и уже потом выполнение команд. А освобождение лока происходит перед закрытием транзакции. В итоге ошибки прекратились и блокировки работают как надо.

Реконнект к AMQP

Вторая проблема произошла, когда нам понадобилось присоединиться к брокеру сообщений (в нашем случае RabbitMQ) из микросервиса. Мы использовали рекомендуемый официальным сайтом клиент, сделали все по туториалам и все работало, пока в один прекрасный день брокер на production не упал. Кластер, конечно, его поднял, но оказалось, что официальный клиент не производит автоматический реконнект и попытки микросервиса отправить сообщение в брокер приводили к ошибке. Мы перезапустили микросервис, чтобы он присоединился к свежесозданному брокеру, и начали исследовать, как решить эту проблему.

Оказалось, все просто: этот же официальный клиент предоставляет функцию NotifyClose, которая позволяет узнать, когда соединение с брокером разорвано и переподключится. Важно при этом пересоздать все каналы, очереди и т. д. на случай, если брокер потерял свои данные (хотя я все же советую настраивать брокер так, чтобы он не терял данные в случае падения). Также обратите внимание, что реконнект выполняется в цикле на случай, если брокер долго не встает, иначе ваш сервис попробует один раз присоединиться и сдастся, а вы получите неработающий микросервис.

func connect() err {
  conn, err := amqp.Dial(“url”);
  connErrorChan := conn.NotifyClose(make(chan *amqp.Error))
  go processConnectErrors(connErrorChan)
  return err
}

func processConnectErrors(ch chan *amqp.Error) {
  err := <- ch
  for {
    err := connect()
    if err != nil {
      break
    }
  }
}

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

Выводы

• Не полагайтесь на стандартные конфигурации. Они созданы, чтобы вы могли быстро создать прототип, но для production их нужно хорошенько подготовить, особенно если ваш сервис доступен снаружи

• Читайте документацию и умные статьи. В Gо прекрасная документация к стандартной библиотеке, доступная как на сайте golang.org, так и в самом коде. Не ограничивайтесь простыми туториалами, ищите нюансы в документации и блогах крупных компаний

• Тестируйте и профилируйте микросервисы. Большинство проблем можно и нужно отловить до релиза с помощью end2end тестов, бенчмарков и нагрузочного тестирования.

One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:

What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections.  Until connections are closed, no new connection will be accepted by the server.

I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.

Accurately Tune the max_connections Parameter

This setting defines the maximum number of connections that a MySQL instance will accept.  Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns.  Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further connections.  Max_connections is a value designed to protect your server, not fix problems related to whatever is hijacking the connections.

Each connection to the server will consume both a fixed amount of overhead for things like the “thread” managing the connection and the memory used to manage it, as well as variable resources (for instance memory used to create an in-memory table.  It is important to measure the application’s resource patterns and find the point at which exceeding that number of connections will become dangerous.

Percona Monitoring and Management (PMM) can help you find these values. Look at the memory usage patterns, threads running, and correlate these with the number of connections.  PMM can also show you spikes in connection activity, letting you know how close to the threshold you’re coming.  Tune accordingly, keeping in mind the resource constraints of the server.

Seen below is a server with a very steady connection pattern and there is a lot of room between Max Used and Max Connections.

Avoiding Common Scenarios Resulting in Overuse of Connections

Having worked in the Percona Managed Services team for years, I’ve had the first-hand opportunity to see where many businesses get into “trouble” from opening too many connections.  Conventional wisdom says that it will usually be a bad code push where an application will behave badly by not closing its open connections or by opening too many quickly for frivolous reasons.

There are other scenarios that I’ve seen that will cause this too even if the application is performing “as expected”.  Consider an application stack that utilizes a cache.  Over time the application has scaled up and grown.  Now consider the behavior under load if the cache is completely cleared.  The workers in the application might try to repopulate the cache in mass generating a spike that will overwhelm a server.

It is important to consider the systems that use the MySQL server and prevent these sorts of edge case behaviors or it might lead to problems.  If possible, it is a good idea to trap errors in the application and if you run into “Too many connections” have the application back off and slip for a bit before a retry to reduce the pressure on the connection pool.

Safeguard Yourself From Being Locked Out

MySQL actually gives you “breathing” room from being locked out.  In versions 5.xx the SUPER user has a +1 always available connection and in versions 8.xx there is a +1 for users with CONNECTION_ADMIN privileges.  However, many times a system has lax privilege assignments and maybe an application user is granted these permissions and consumes this extra emergency connection.  It is a good idea to audit users and be sure that only true administrators have access to these privileges so that if a server does consume all its available connections, an administrator can step in and take action.  There are other benefits to being strict on permissions.  Remember that the minimum privilege policy is often a best practice for good reason!  And not always just “security”.

MySQL 8.0.14+ also allows us to specify admin_address and admin_port to provide for a completely different endpoint, bypassing the primary endpoint and establishing a dedicated admin connection.  If you’re running a lower version but are using Percona Server for MySQL, you’ll have the option of using extra_port and extra_max_connections to achieve another way of connecting.

If you are able to log in as an admin account, you may be able to kill connections, use pt-kill to kill open connections, adjust timeouts, ban offending accounts, or raise the max_connections to free up the server.

If you are unable to log in, you may try to adjust the max_connection value on the fly as a last resort.  Please see Too many connections? No problem!

Use a Proxy

Another way to alleviate connection issues (or move the issue to a different layer in the stack), is to adopt the user of a proxy server, such as ProxySQL to handle multiplexing.  See Multiplexing (Mux) in ProxySQL: Use Case.

Limits Per User

Another variable that MySQL can use to determine if a connection should be allowed is max_user_connections.  By setting this value, it puts a limit on the number of connections for any given user.  If you have a smaller number of application users that can stand some limit on their connection usage, you can set this value appropriately to prevent total server connection maximum.

For instance, if we know we have 3 application users and we expect those 3 users to never individually exceed 300 connections, we could set max_user_connections to 300.  Between the 3 application users, only a total of 900 connections would be allowed.  If max_connections was set to 1000, we’d still have 100 open slots.

Another approach in this same vein that is even more granular is to limit connections PER USER account.  To achieve this you can create an account like this:

It is a good idea to limit connections to tools/applications/monitoring that are newly being introduced in your environment and make sure they do not “accidentally” consume too many connections.

Close Unused Connections

MySQL provides the wait_timeout variable.  If you observe connections climbing progressively over time and not in a spike (and your application can handle it), you may want to reduce this variable from its default of 28800 seconds to something more reasonable.  This will essentially ask the server to close sleeping connections.

These are just a few considerations when dealing with “Too many connections”.  I hope they help you.  You may also consider further reading on the topic in this previous Percona blog post, MySQL Error: Too many connections.

How to interpret “MySQL error 1040 – Too many connections ! ” ?

When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are “too many connections“. This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about connections to a server can be found using the SHOW STATUS statement:

SHOW STATUS LIKE 'max_used_connections';

Prerequisite – Few points to remember before working or troubleshooting MySQL ” Too many connections ! ” error

1. MySQL does not have it’s own thread handling mechanism / implementation and it completely relies on the thread handling implementation of the underlying operating system.
2. MySQL system variable max_connections control the maximum number of clients the server permits to connect simultaneously,   You may have to increase max_connections if more clients attempt to connect simultaneously then the server is configured to handle (Explained more in detail –  “Too many connections”).
3. How MySQL connection handling (both connects and disconnects) works  ?
   1. MySQL Clients connects to MySQL Server via a simple and regular TCP-IP connect message though port 3306 on the Server instance
   2. The incoming connection requests are queued and then processed by the receiver thread one by one, All that receiver thread does is create user thread.  It’s actually user thread which handles the client-server protocol for connection management, Which involves allocate and initialize the corresponding THD for user authentication based on credentials stored on THD’s security policies / directories and finally if successfully authorized in the connection phase, the user thread will enter command phase
   3. The  receiver  thread will either create a new OS thread or reuse and existing “free” OS thread if available in the  thread cache. So we strongly recommend increasing  thread cache in  cases where number of  connections fluctuates  between ver few connections  and having  many connections. But there are three things which a thread might need to wait for:  A mutex, a database lock, or IO.
   4. THD basically is a large data structure used for several purposes like connection management, authorization and even unto to query execution, So how  much THD consumes memory is directly proportionate to the query execution complexities and connection traffic.

MySQL error – Too many connections, How to fix ?

Recently one of customers ( among the top 5 largest e-commerce companies in the world ) called us to check how graceful their connection handling works during peak hours of business, They had issues in  the past with ” ERROR 1040: Too many connections “ and that clearly explains the maximum number of clients that may be connected to the server has been reached so either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed. so wanted us to do a detailed health-check on MySQL connection management and address “Too many connections” error proactively, We have explained below on how we could successfully reproduce this issue and recommended the fix:

Goal: Manage  50,000 connections on MySQL 8.0 (Ubuntu)

The default setting for system variable max_connections is “151”and we are benchmarking 50K connections so the first step before benchmarking  is to increase max_connections to 50000. we increased max_connections to 50000 dynamically and what happened after that was not expected, We have copied the results below:

root@MDB1:~# mysql -uroot -pMDB@PassWd2020 -se "select @@max_connections"
@@max_connections
697

We got only 697 connections, Let’s interpret MySQL error log before proceeding to next steps.. We have copied the same below:

2020-01-30T19:52:35.136192Z0 [Warning] Changed limits: max_open_files: 5129 (requested 10000)
2020-01-30T19:54:13.241937Z0 [Warning] Changed limits: max_connections: 4152 (requested 10000)
2020-01-30T19:57:47.51617Z0 [Warning] Changed limits: table_open_cache: 533 (requested 15000)

This is due to open files limitations for MySQL so let’s increase now the number of allowed open files for MySQL, The following steps we did to fix this resource limit issue:

• Option  1 – Locate the systemd configuration folder for MySQL and create file /etc/systemd/system/mysqld.service.d/override.conf (file can be called anything ending with .conf).
  • Add LimitNOFILE=55000 in the file override.conf
  • Add TasksMax=55000 in the file override.conf
  • Add LimitNPROC=55000 in the file override.conf
• Option 2 – We can also create/modify the override file by using native systemctl command like: systemctl edit mysql

root@MDB1:~# cat /etc/systemd/system/mysql.service.d/override.conf
[Service]
LimitNOFILE=55000
TasksMax=55000
LimitNPROC=55000

** MySQL uses some files for additional work and we need to set LimitNOFILE, TasksMax and LimitMPROC higher to get 50000 connections, lets set it to 55000 and reload the systemd daemon and restart the MySQL service.

Reload the systmed daemon and restart the MySQL service:

root@MDB1:~# systemctl daemon-reload
root@MDB1:~# systemctl restart mysql

Now let’s check max_connections to confirm the change applied:

root@MDB1:~# mysql -uroot -pMDB@PassWd2020 -se "select @@max_connections"
mysql: [Warning] Using a password on the command line interface can be insecure.
@@max_connections
50000

Conclusion

We have no fixed value recommendations for system variable max_connections, It completely depends on your application load and how your application does connection handling. We advice our customers to avoid too many connections opened concurrently because each thread connected needs memory and there is also resource intensive context switching causing overall performance degradation, Thanks for reading and comments are welcome !

References

• https://mysqlserverteam.com/mysql-connection-handling-and-scaling/ 
• http://mysql-nordic.blogspot.com/2019/04/mysql-error-too-many-connections.html 
• https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html 

Book your appointment for 30 minutes ( absolutely free ) MinervaDB consulting 

Я думаю, что у меня серьезная проблема управления пулом подключений к базе данных в Голанге. Я создал RESTful API, используя веб-инструментарий Gorilla, который отлично работает, когда на сервер отправляется только несколько запросов. Но теперь я начал выполнять нагрузочное тестирование с использованием сайта loader.io. Прошу прощения за длинный пост, но я хотел дать вам полную картину.

Прежде чем идти дальше, вот некоторые сведения о сервере, на котором запущены API и MySQL:
Выделенный хостинг Linux
ОЗУ 8 ГБ
Версия 1.1.1
Подключение к базе данных с помощью go-sql-драйвера
MySQL 5.1

Используя loader.io, я могу отправить 1000 запросов GET/15 секунд без проблем. Но когда я отправляю 1000 запросов POST/15 секунд, я получаю много ошибок, все из которых связаны с ERROR 1040 слишком большим количеством подключений к базе данных. Многие люди сообщали о подобных проблемах в Интернете. Обратите внимание, что я тестирую только один конкретный запрос POST. Для этого почтового запроса я обеспечил следующее (что также было предложено многими другими в Интернете)

• Я старался использовать не Open и Close * sql.DB для коротких функций. Поэтому я создал только глобальную переменную для пула соединений, как вы видите в приведенном ниже коде, хотя я открыт для предложения здесь, потому что мне не нравятся глобальные переменные.

• Я старался использовать db.Exec, когда это возможно, и использовать db.Query и db.QueryRow, когда ожидаются результаты.

Так как вышеупомянутое не решило мою проблему, я попытался установить db.SetMaxIdleConns(1000), который решил проблему для 1000 запросов POST/15 секунд. Значение не более 1040 ошибок. Затем я увеличил нагрузку до 2000 запросов POST/15 секунд, и я снова начал получать ERROR 1040. Я попытался увеличить значение в db.SetMaxIdleConns(), но это не помогло.

Вот некоторые статистики соединений, которые я получаю из базы данных MySQL по количеству подключений, запустив SHOW STATUS WHERE variable_name= ‘Threads_connected’;

За 1000 запросов POST/15 секунд: наблюдается #threads_connected ~ = 100
Для 2000 запросов POST/15 секунд: наблюдается #threads_connected ~ = 600

Я также увеличил максимальные соединения для MySQL в my.cnf, но это не изменило ситуацию. Что ты предлагаешь? Отличается ли код? Если да, то, вероятно, соединения просто ограничены.

Вы найдете упрощенную версию кода ниже.

var db *sql.DB

func main() {
    db = DbConnect()
    db.SetMaxIdleConns(1000)

    http.Handle("/", r)
    err := http.ListenAndServe(fmt.Sprintf("%s:%s", API_HOST, API_PORT), nil)

    if err != nil {
       fmt.Println(err)
    }
}

func DbConnect() *sql.DB {
    db, err := sql.Open("mysql", connectionString)
    if err != nil {
        fmt.Printf("Connection error: %sn", err.Error())
        return nil
    }
    return db
}

func PostBounce(w http.ResponseWriter, r *http.Request) {
    userId, err := AuthRequest(r)

    //error checking
    //ready requesy body and use json.Unmarshal

    bounceId, err := CreateBounce(userId, b)

    //return HTTP status code here
}

func AuthRequest(r *http.Request) (id int, err error) {
    //parse header and get username and password

    query := "SELECT Id FROM Users WHERE Username=? AND Password=PASSWORD(?)"
    err = db.QueryRow(query, username, password).Scan(&id)

    //error checking and return
}

func CreateBounce(userId int, bounce NewBounce) (bounceId int64, err error) {
    //initialize some variables
    query := "INSERT INTO Bounces (.....) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    result, err := db.Exec(query, ......)

    //error checking

    bounceId,_ = result.LastInsertId()

    //return 
}