Determine the cause of too many connections in MySQL

Databases

Read this post if you want to determine the cause of too many connections in MySQL. Too many database connections will cause an “error establishing a connection to database” response.

What is the maximum number of connections your database will handle?

The maximum number of database connections permitted is calculated based on the amount of  RAM, disk space, vCPUs and average database connection size.

You can use the ‘SHOW variables‘ SQL command to determine the max connections on your database.

SHOW VARIABLES LIKE 'max_connections';

Result:

Where are these database connections coming from?

You can use the โ€˜SHOW processlistโ€™  SQL command to show you which threads are currently running.

It will for instance provide the following details:

  • User – The MySQL user who issued the statement.
  • Host – The hostname of the client issuing the statement.
  • Command โ€“ The SQL command that is executed.

The ‘show processlist SQL command:

SHOW processlist

Result

In conclusion,

Investigate the ‘SHOW processlist’ results to determine the cause of too many connections in MySQL.

Additionally,

Do you have too many many connections in MySQL due to a PHP code executed via CRON? Read the post about preventing CRON from running PHP code more than once if you have a similar problem.

For those using AWS RDS MySQL instances. The maximum number of connections is set to:

  • t2.micro – max connections: 66
  • t2.small – max connections: 150
  • m3.medium – max connections: 296
  • t2.medium – max connections: 312
  • m3.large – max connections: 609
  • t2.large – max connections: 648
  • m4.large – max connections: 648
  • m3.xlarge – max connections: 1237
  • r3.large – max connections: 1258
  • m4.xlarge – max connections: 1320
  • m2.xlarge – max connections: 1412
  • m3.2xlarge – max connections: 2492
  • r3.xlarge – max connections: 2540

Visit this post for more information on calculating the max connections for your AWS RDS instance type.



About the Authors

Each member of Anto's editorial team is a Cloud expert in their own right. Anto Online takes great pride in helping fellow Cloud enthusiasts. Let us know if you have an excellent idea for the next topic!

Support the Cause

Support Anto Online and buy us a coffee. Anything is possible with coffee and code.

Buy me a coffee



One Comment on “Determine the cause of too many connections in MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *