What is causing these insane database connection spikes?

[Markshall]

This was the most appropriate section I could find for this post, feel free to move if not.

My issue is that I maintain a few websites that are all built on WordPress (I know...), which for the most part is good at what it does. The servers are EC2 instances on AWS and the database is an RDS instance, also on AWS. Every now and again, I notice a huge increase in connections to the database coming from one of the websites.

What I want to know is, what counts as a connection? How is a connection actually established? At what point is a connection made? Hopefully finding this out will possibly help me understand how this issue is happening. I’ve been programming for over 15 years now so you’d think I should know this, well, I think I should know this.

The only users who should see dynamic content are logged in users—thus should be connected to the database, as they are editing content and viewing the pages as an admin. Any unauthorised users see static HTML pages generated by a plugin (W3TC), so I’m under the assumption that no connection is being made to the database there.

Due to this, I’ve ruled out the possibility of there being too many people viewing the website at 1 time. So I’m stuck thinking if it’s a plug-in being used that is faulty? Maybe WordPress itself is running jobs in the background that is causing it?

These spikes in connections are huge, I can often see 200-300+ connections just spawn out of nowhere. I have an alarm set up in RDS to email me when the connections are rising, that’s when I go to check and see that it’s coming from just one website.

Any help from you lovely bunch of nerds would be great. Thanks.

Edit: paging the expertise of the likes of @TesoMayn, @Ecko, @RastaLulz, @Sledmore and @Macemore here

 

[RastaLulz]

If I had to guess, it's a WP-Cron that is being triggered. You might even be able to look in the database, and see previous WP cron's that have been registered (not sure if WP deletes them), and try and correlate the spike.

 

[TesoMayn]

Weird, I didn't get a notification for the tag.

It could be wp-cron, try disabling it and using crontab instead
But it could also be connections not closing

You must be registered for see links

this is a useful plugin

Which method are you using for caching?

 

[Ecko]

Enable general and slow query logging, after it occurs, turn it off

Logs from that should hopefully indicate what the excess connections/queries are

 

[Markshall]

If I had to guess, it's a WP-Cron that is being triggered. You might even be able to look in the database, and see previous WP cron's that have been registered (not sure if WP deletes them), and try and correlate the spike.

Yep that was my initial thought as well. Is there a way I can see what queries have been previously run on the database? I’m using MySQL Workbench. Not sure if logs like that are kept on the server? Thanks

Weird, I didn't get a notification for the tag.

It could be wp-cron, try disabling it and using crontab instead
But it could also be connections not closing

You must be registered for see links

this is a useful plugin

Which method are you using for caching?

I’ll look into disabling wp-cron and see if that solves anything. Thanks for the plug-in recommendation. Have you previously used it yourself? For caching, I’m using W3TC but can’t remember what setting it’s on. It may just be the recommended setting by the plug-in. Thanks for the help

Enable general and slow query logging, after it occurs, turn it off

Logs from that should hopefully indicate what the excess connections/queries are

Is there a specific way to do this? Thanks!

 

[Ecko]

There should be a my.ini or my.cnf file you can use to set the option:

You must be registered for see links

Just make sure the log file name path is writeable by MySQL

 

[Sledmore]

Like the others, I think it could be the cron.

By connections do you mean threads? For MySQL active connections come up under 'Threads connected' rather than 'Connections', it might be worth monitoring the active threads, you can view these by running the processlist query.

SHOW PROCESSLIST;

It'd be worth running this when/if you experience this again. We recently had an issue w/ an e-learning site where for some unknown reason WooCommerce had started to run an alter query, but due to how long it took and timeouts it tried again, and again, and again and so on, thus bringing the site down - which I believe was trigged by the cron.

 

[Markshall]

Like the others, I think it could be the cron.

By connections do you mean threads? For MySQL active connections come up under 'Threads connected' rather than 'Connections', it might be worth monitoring the active threads, you can view these by running the processlist query.

SHOW PROCESSLIST;

It'd be worth running this when/if you experience this again. We recently had an issue w/ an e-learning site where for some unknown reason WooCommerce had started to run an alter query, but due to how long it took and timeouts it tried again, and again, and again and so on, thus bringing the site down - which I believe was trigged by the cron.

Yeah threads is what I meant. When I go to Server > Client Connections in MySQL Workbench, it shows me something like “threads connected: 270” etc etc, then lists all the currently active threads/connections. I think when I go to Client Connections, the query it executes is basically the one you’ve provided in your reply

What I’d like to know is at what point the site decides to spin up a new thread/connection.

But your situation sounds similar to mine. Something on the server side seems to take a while due to most likely the cron thing, then the DB receives too many connections thus bringing the site down

 

[Weasel]

What I’d like to know is at what point the site decides to spin up a new thread/connection.

Assuming WordPress is using PDO (but it also works similar to mysqli_*), when an instance of the PDO object is created in the script, a connection is made. Until the object is destroyed, or when the script finishes running, the connection is closed. So basically every time a user visits your non-static pages, it creates a connection and closes is once the server is done executing (/your page finishes loading). So most of the connections are short-lived.

If you really have that many connections all at once, it could also be someone is DDoSing your site (layer 7), bots are indexing your site or something like that. It would be weird for a cronjob to spawn so many connections, as the script probably just would use one connection for the processing.

 

[Markshall]

Assuming WordPress is using PDO (but it also works similar to mysqli_*), when an instance of the PDO object is created in the script, a connection is made. Until the object is destroyed, or when the script finishes running, the connection is closed. So basically every time a user visits your non-static pages, it creates a connection and closes is once the server is done executing (/your page finishes loading). So most of the connections are short-lived.

If you really have that many connections all at once, it could also be someone is DDoSing your site (layer 7), bots are indexing your site or something like that. It would be weird for a cronjob to spawn so many connections, as the script probably just would use one connection for the processing.

Thanks man. That’s the kind of answer I was looking for. I’m not sure if WP does use PDO but I’d certainly assume so. I’ll look into it.

What is meant by layer 7? Is there a way I can make connections automatically kill themselves/close after X seconds of inactivity? I do often notice in MySQL Workbench that the connections are in SLEEP state for a few hundred seconds, so a few minutes.

 

[Weasel]

Thanks man. That’s the kind of answer I was looking for. I’m not sure if WP does use PDO but I’d certainly assume so. I’ll look into it.

What is meant by layer 7? Is there a way I can make connections automatically kill themselves/close after X seconds of inactivity? I do often notice in MySQL Workbench that the connections are in SLEEP state for a few hundred seconds, so a few minutes.

The same principle applies to other MySQL libraries such as mysqli. Layer 7 basically means the interaction part, see this overview:

Connections should not enter into a sleep state unless somewhere in the config it specifically opens up persistent connections. Can you see from which source the sleeping connections are from?

 

[Markshall]

The same principle applies to other MySQL libraries such as mysqli. Layer 7 basically means the interaction part, see this overview:

Connections should not enter into a sleep state unless somewhere in the config it specifically opens up persistent connections. Can you see from which source the sleeping connections are from?

Thanks man. Funnily enough I saw a really similar image to that yesterday, may have been on AWS’ documentation.

By source do you mean the file in which the DB connections are made? If so, I’m not entirely sure. It’ll be some bastard WordPress file somewhere.

Also, is there a difference between a thread and a connection?

And one last thing (for now lol). I have a few sites hosted on one database, but when one of the websites goes down due to too many connections, it doesn’t bring the others with it. How is that happening?

 

[Weasel]

Thanks man. Funnily enough I saw a really similar image to that yesterday, may have been on AWS’ documentation.

By source do you mean the file in which the DB connections are made? If so, I’m not entirely sure. It’ll be some bastard WordPress file somewhere.

No, I mean actually from your MySQL server. If you run SELECT * FROM PROCESSLIST on your database, it should give you an overview of those active connections. Under the column "host" you can see from where the connections are made (the source).

Also, is there a difference between a thread and a connection?

They're basically the same. One connection usually spins up 1 thread (the actual process), but that's more DBA stuff.

And one last thing (for now lol). I have a few sites hosted on one database, but when one of the websites goes down due to too many connections, it doesn’t bring the others with it. How is that happening?

Are you using different users for this, or does every application connect through the same user with the database? If you use different users, it might be hitting the max_user_connections instead of the max_connections, which would explain why your other sites stay up. Another reason might be caching, if your other sites don't perform any MySQL connections, it won't go down.

 

[Markshall]

No, I mean actually from your MySQL server. If you run SELECT * FROM PROCESSLIST on your database, it should give you an overview of those active connections. Under the column "host" you can see from where the connections are made (the source).

They're basically the same. One connection usually spins up 1 thread (the actual process), but that's more DBA stuff.

Are you using different users for this, or does every application connect through the same user with the database? If you use different users, it might be hitting the max_user_connections instead of the max_connections, which would explain why your other sites stay up. Another reason might be caching, if your other sites don't perform any MySQL connections, it won't go down.

Oh, I see! Yeah the host field shows the private IP of the EC2 instance the website runs on.

Thanks for the explanation

And ahh that makes sense then. Thanks for the insight! You clever boy, you!