Comments on: Preventing MySQL Error 1040: Too Many Connections

By: Nikunj Bhatt

Nikunj Bhatt — Tue, 20 Oct 2020 23:52:10 +0000

I have suggestion to overcome this problem and so I have requested feature of max_readonly_user_connection: https://bugs.mysql.com/bug.php?id=101254

By: strokeDB

strokeDB — Fri, 31 Jul 2020 19:57:47 +0000

Hi !

thanks for the tips (global max connexion + user max connexion + timeout)

you can also use middleware to prevent to much queries by one user 🙂

By: Ruud H.G. van Tol

Ruud H.G. van Tol — Sat, 04 Jul 2020 08:20:06 +0000

A common bad pattern is to have many worker processes each make a connection to a master, to do (non-urgent) database changes. Then put a queue in between. (Or switch to Cassandra.)

By: Tate McDaniel

Tate McDaniel — Thu, 02 Jul 2020 15:03:13 +0000

In reply to danielgblack. This is a really great point Daniel, so thank you for sharing it. There are also a lot of other examples of things that can cause a performance impact that results in a "pile up" that consumes all the connections. I was a little shy of going down that rabbit hole since there are so many, but this point you bring is excellent and worth keeping in mind.

By: danielgblack

danielgblack — Thu, 02 Jul 2020 00:35:41 +0000

The other cause is slow queries that stack up and because of their lack of speed consume and their frequency saturate the max_connections. This is compounded more if it is an update query that causes other queries to wait on locks before they are executed.

1000 requests/second evenly distributed over a second that each execute a 10ms query could with exceptional timing use a maximum of 10 connections however if the query took 100ms the maximum connections would be 100. A 50% (not mathematically derived) margin is needed on this for normal distributions and because of connection overhead.

Tuning for safety is good, but don’t overlook the overall requirement can be reduced with the frequent queries being faster.