Why I am getting max_user_connections SQL Error?

Question

Why I am getting this error on my website http://elancemarket.com/ again and again ?

Error establishing a database connection

SQL ERROR [ mysqli ]
User elancema_user already has more than 'max_user_connections' active connections [1203]

Warning: mysqli::mysqli(): (HY000/1203): User elancema_user already has more than 'max_user_connections' active connections in /home/elancemarket/public_html/ask/qa-include/qa-db.php on line 66

I am on very expensive VPS !

Answer 1

The server throws you this error:

SQL ERROR [ mysqli ] User database_user already has more than 'max_user_connections' active connections [1203]

That means that the specific database user has already used up all the concurrent connections at that moment and more cannot be processed.

The only option to fix without doing any programming changes is to change the max_user_connections value in MySQL configuration. The configuration file is usually /etc/my.cnf on Linux.

Other solutions might be:

• Doing less queries in the website itself
• Creating separate database users for different projects if they are using the same database user.

You can check the current value for the user by running the command SHOW VARIABLES LIKE 'max_user_connections';.

Answer 2

Your best bet is to increase max_user_connections. For a MySQL instance serving three different web apps (raw php, WordPress, phpBB), you probably want a value of at least 60 for this.

Issue this command and you'll find out how many global connections you have available:

show global variables like '%connections%'

You can find out how many connections are in use at any given moment like this:

show status like '%connected%'

You can find out what each connection is doing like this:

show full processlist

I would try for a global value of at least 100 connections if I were you. Your service provider ought to be able to help you if you don't have access to do this. It needs to be done in the my.cnf file configuration for MySQL. Don't set it too high or you run the risk of your MySQL server process gobbling up all your RAM.

A second approach allows you to allocate those overall connections to your different MySQL users. If you have different MySQL usernames for each of your web apps, this approach will work for you. This approach is written up here. https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/

The final approach to controlling this problem is more subtle. You're probably using the Apache web server as underlying tech. You can reduce the number of Apache tasks running at the same time to, paradoxically, increase throughput. That's because Apache queues up requests. If it has a few tasks efficiently banging through the queue, that is often faster than lots of tasks because there's less contention. It also requires fewer MySQL connections, which will solve your immediate problem. That's explained here: Restart Mysql automatically when ubuntu on EC2 micro instance kills it when running out of memory

By the way, web apps like WordPress use a persistent connection pool. That is, they establish connections to the MySQL data base, hold them open, and reuse them. If your apps are busy, each connection's lifetime ought to be several minutes. (Based on the oversimplified statement db connections are created and deleted in fraction of seconds, your hosting provider's support tech doesn't understand the subtlety of this part of web app operation.)