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 Anto

Hi, my name is Anto! I am a cloud computing hobbyist! Give me anything to do with the cloud, and I am interested. I work for a Cloud computing company by day and as a Cloud computing hobbyist by night! My projects use PHP, NodeJs, Ubuntu, MySQL and of course Amazon Web Services. Hopefully, my blog aids your cloud journey! Feel free to post a comment and share your thoughts.

View all posts by Anto →

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 *