Editing max connections in mysql

badboybackagain

Hi,

Just found that all my websites are not able to connect to the db and the reason is mysqli_real_connect(): (08004/1040): Too many connections. Can someone please help if this can be resolved by changing max connections mysql configuration. Also, can someone please point me how can I do that.

AdamM

badboybackagain https://dev.mysql.com/doc/refman/8.0/en/too-many-connections.html

In server database role settings - my.cnf

Adam

The default max_connections limit is 151 which should be enough for most servers. If you're hitting it there is usually an underlying cause - either the server is CPU or disk i/o limited or maybe the disk is full. Increasing the limit might exacerbate the problem.

Another possibility is that a site is holding on to connections for too long - it's opening a connection then doing something slow like connecting to an external API whilst holding on to that connection.

Instead you might want to look at setting max_user_connections.

jeffkee

Ensuring $mysqli->close() is at the end of every script helps, and logging slow SQLs and zapping those will help too (we run custom PHP, not just Wordpress, hence we look at these a lot)

i wonder, @Adam , what the protocol is when we scale up the server capacity itself (more RAM and more CPU)

For example, we are going from an medium class EC2 instance to xlarge, and maybe even 2xlarge as we are moving more websites into our system, and we ran into the MySQL max connections error as well as the PHP max request worker exhaustions.

Are we to double the defaults when we double the server capacity? Based on my understanding of estimating the MBs used per PHP/Apache process, then extrapolating that based on about 75% of RAM (leave 25% for OS/foundations), I should be able to do this, right?

Adam

You can set the limit as high as you like but I would suggest doing this in conjunction with setting max_user_connections.

MySQL/MariaDB are often more efficient with fewer databases especially if you're using the query cache under MariaDB. Because Enhance doesn't charge a licence fee per server, it might be better to add another server instance rather than resizing your existing one.

jeffkee

Multiple servers definitely makes sense. We serve clients across Canada, and this lets us run some on the East Coast (ca-central-1) data centres too, reducing latency of load ever so slightly. Amazing idea Adam.