Database » ⚠️ User already has more than ‘max_user_connections’ active connections [✅ SOLVED]

⚠️ User already has more than ‘max_user_connections’ active connections [✅ SOLVED]

Noticed a lot of Crawler errors on the Google Adsense console:

image 1 1024x522 - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

So I enabled the WP_DEBUG and run a stress test with vegeta on the website:

echo "GET" | vegeta attack -duration=30s -rate=250 | tee results.bin | vegeta report

the website became unavailable and the following error was shown: User already has more than ‘max_user_connections’ active connections

image - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

This explains the errors in Google SC because the crawler most likely also encountered this error.

To resolve this issue, it is important to check which queries are problematic. For this, you can check the following guide: How to properly DEBUG in WordPress

On cPanel you can see the active connections from WHM > Show MySQL® Processes

image 2 1024x513 - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

or if you don’t have access to the WHM *(e.g. you are on shared hosting) then from the terminal run:

mysqladmin -u USERNAME_HERE -p processlist
image 3 1024x229 - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

This will display all active MySQL connections for that user.

The next step is to determine what is causing the problem, in WordPress the most common culprit is:

  • A: Plugin that was recently installed or updated
  • B: Corrupt tables in the Database

In this post I will go over 3 examples of problems that are causing the “User already has more than ‘max_user_connections’ active connections” error on a WordPress website:

  • Plugin is storing data inside WP core tables (wp_options)
  • The plugin is using custom tables but the queries and tables are not optimized
  • Multiple WP websites use the same MySQL user

Another example of a plugin that is causing this problem is All In One WP Security which uses the wp_options table to store data and leaves it after being uninstalled:

ShareX d9RKOqUIgJ - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

In this case, the plugin leftover 900k of rows in the wp_options table, and queries to that table had to go through all records which became a problem.

mspaint YNlPwcHxri 1024x572 - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

To delete residual data after uninstalling a plugin read this guide: How to safely remove database tables after plugin deactivation

First take a look into the query that causes the problem, in my case the query ALTER_TABLE wpoe_snax_collections … is the last query that is active, and all new queries are waiting for this one to finish.

Since this plugin is a must for the website and has to be used, let’s look into ways to optimize the database and MySQL service.

This query is used by a plugin named Snax – Viral Content Builder and I can confirm that it is the culprit by renaming the plugin folder and by doing so temporarily disabling it.

See also  Dumping specific tables using mysqldump or WP-CLI

To learn exactly what this query does and why it needs a lot of time to finish, we can use the EXPLAIN:

EXPLAIN ALTER_TABLE wpoe_snax_collections ...

This will show you how the query is executed: if there are indexes, autoload is enabled, does the whole table need to be scanned, etc.

In this example, the MySQL user is used for multiple WordPress websites and often causes the “User already has more than ‘max_user_connections’ active connections” error.

image 2 - ⚠️ User already has more than 'max_user_connections' active connections [✅ SOLVED]

Using the same MySQL user for many databases is seen as a poor security practice. For instance, if the server has a 20 connection limit per user, the limit would frequently be hit, resulting in an error message being displayed on all websites using that user.

Developers of plugins and themes can speed up slow queries inside their code, however for end users, there are several things you can do to speed up queries on your website:

When running a query on a standard table, the entire table needs to be scanned, which might be problematic for large tables. Indexes arrange the information in tables in a way that speeds up each query.

To add index to wp_postmeta table run:

CREATE INDEX wp_postmeta_wpxss ON wp_postmeta (meta_key)

On existing tables, We can add high-performance keys that match the most common WordPress queries, for wp_postmeta:

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

This will create new high-performance keys:

TableStandard WP keysNew keys
wp_postmetameta_id: primary key
post_id, meta_key, meta_id: primary key
meta_id: unique key
meta_key, post_id

For more examples and code snippets for other WordPress tables check this post: Adding high-performance keys to MySQL tables to speed up WordPress

WordPress transients allow us to save the results of the query so that they can be displayed more quickly the next time.

But because transients are stored inside the wp_options table, you should only use them for global and necessary data.

Here is an example:

if ( false === ( $results = get_transient( 'transient_key_name' ) ) ) {
  $results = ...;

  set_transient( 'transient_key_name', $results, 60 * 60 ); 

For more useful examples check Common APIs Handbook and Guide to Transients in WordPress

Was this post helpful?

Leave a Comment

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.