Heavy Database Load

Hi! What kind of requests do you get? Are they the same?

Hi there,

Here’s the stuff the tech support guy at the host sent me:

260390 [db_username] localhost [db_name] Query 5457 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “69.147.248.208” AND activity IN (7) AND stamp > 1534935 0.000
260295 [db_username] localhost [db_name] Query 5528 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
260292 [db_username] localhost [db_name] Query 5528 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “185.217.63.42” AND activity IN (7) AND stamp > 15349356 0.000
260199 [db_username] localhost [db_name] Query 5609 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
259971 [db_username] localhost [db_name] Query 6196 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “202.80.219.10” AND activity IN (7) AND stamp > 15349350 0.000
259951 [db_username] localhost [db_name] Query 6235 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “202.80.219.10” AND activity IN (7) AND stamp > 15349349 0.000
259923 [db_username] localhost [db_name] Query 6316 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “202.80.219.10” AND activity IN (7) AND stamp > 15349349 0.000
259897 [db_username] localhost [db_name] Query 6354 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “202.80.219.10” AND activity IN (7) AND stamp > 15349348 0.000
259724 [db_username] localhost [db_name] Query 6723 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
259577 [db_username] localhost [db_name] Query 6843 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “104.237.218.99” AND activity IN (7) AND stamp > 1534934 0.000
259476 [db_username] localhost [db_name] Query 7031 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
259458 [db_username] localhost [db_name] Query 7076 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “172.245.105.45” AND activity IN (7) AND stamp > 1534934 0.000
259097 [db_username] localhost [db_name] Query 7427 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “23.89.197.2” AND activity IN (7) AND stamp > 1534933791 0.000
258861 [db_username] localhost [db_name] Query 7886 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “83.188.111.67” AND activity IN (7) AND stamp > 15349333 0.000
258843 [db_username] localhost [db_name] Query 7924 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “83.188.111.67” AND activity IN (7) AND stamp > 15349332 0.000
258822 [db_username] localhost [db_name] Query 7962 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “83.188.111.67” AND activity IN (7) AND stamp > 15349332 0.000
258803 [db_username] localhost [db_name] Query 7998 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “83.188.111.67” AND activity IN (7) AND stamp > 15349332 0.000
258723 [db_username] localhost [db_name] Query 8176 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258655 [db_username] localhost [db_name] Query 8339 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258544 [db_username] localhost [db_name] Query 8583 Waiting for table metadata lock DELETE FROM cerber_log WHERE stamp < 1532344235 0.000
258389 [db_username] localhost [db_name] Query 9082 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
258328 [db_username] localhost [db_name] Query 9170 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258316 [db_username] localhost [db_name] Query 9188 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
258253 [db_username] localhost [db_name] Query 9339 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258176 [db_username] localhost [db_name] Query 9522 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258155 [db_username] localhost [db_name] Query 9573 Waiting for table metadata lock SELECT * FROM cerber_log LIMIT 0, 350 0.000
258118 [db_username] localhost [db_name] Query 9658 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
258047 [db_username] localhost [db_name] Query 9879 Waiting for table metadata lock SELECT * FROM cerber_log LIMIT 0, 350 0.000
258044 [db_username] localhost [db_name] Query 9880 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
257948 [db_username] localhost [db_name] Query 10217 Waiting for table metadata lock SELECT * FROM cerber_log LIMIT 0, 350 0.000
257858 [db_username] localhost [db_name] Query 10528 Waiting for table metadata lock SELECT * FROM cerber_log LIMIT 0, 350 0.000
257714 [db_username] localhost [db_name] Query 10923 Waiting for table metadata lock SELECT * FROM cerber_log LIMIT 0, 350 0.000
257354 [db_username] localhost [db_name] Query 11854 Waiting for table metadata lock LOCK TABLES cerber_log READ /*!32311 LOCAL */ 0.000
255785 [db_username] localhost [db_name] Query 15822 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
255781 [db_username] localhost [db_name] Query 15832 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
255735 [db_username] localhost [db_name] Query 15985 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
255613 [db_username] localhost [db_name] Query 16365 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
255298 [db_username] localhost [db_name] Query 17475 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
254720 [db_username] localhost [db_name] Query 19188 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
254628 [db_username] localhost [db_name] Query 19286 Waiting for table metadata lock DELETE FROM cerber_log WHERE stamp < 1532333531 0.000
254189 [db_username] localhost [db_name] Query 19824 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
254067 [db_username] localhost [db_name] Query 19932 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
253948 [db_username] localhost [db_name] Query 20010 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
253865 [db_username] localhost [db_name] Query 20067 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
253699 [db_username] localhost [db_name] Query 20188 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
252073 [db_username] localhost [db_name] Query 23012 Waiting for table metadata lock DELETE FROM cerber_log WHERE stamp < 1532329806 0.000
251801 [db_username] localhost [db_name] Query 23575 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
251782 [db_username] localhost [db_name] Query 23616 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
249835 [db_username] localhost [db_name] Query 26645 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
249634 [db_username] localhost [db_name] Query 26775 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
249509 [db_username] localhost [db_name] Query 26870 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
246892 [db_username] localhost [db_name] Query 30184 Waiting for table metadata lock DELETE FROM cerber_log WHERE stamp < 1532322634 0.000
246587 [db_username] localhost [db_name] Query 30755 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
246555 [db_username] localhost [db_name] Query 30807 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
244185 [db_username] localhost [db_name] Query 34054 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
243680 [db_username] localhost [db_name] Query 34383 Waiting for table metadata lock ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL 0.000
243021 [db_username] localhost [db_name] Query 34805 Waiting for table metadata lock INSERT INTO cerber_log (ip, ip_long, user_login, user_id, stamp, activity, session_id, country, deta 0.000
242259 [db_username] localhost [db_name] Query 35254 Waiting for table metadata lock SELECT count(ip) FROM cerber_log WHERE ip = “185.86.164.103” AND activity IN (7) AND stamp > 1534905 0.000
240994 [db_username] localhost [db_name] Query 36029 Waiting for table level lock OPTIMIZE TABLE cerber_log 0.000
240993 [db_username] localhost [db_name] Query 36029 Waiting for table metadata lock OPTIMIZE TABLE cerber_log 0.000
262958 [user] localhost NULL Sleep 20 NULL 0.000
262957 [user] localhost [db_name] Query 19 Waiting for table metadata lock SELECT COUNT(*) FROM [db_name].cerber_log 0.000
261950 [user] localhost [db_name] Query 2040 Waiting for table metadata lock SELECT COUNT(*) FROM [db_name].cerber_log 0.000
261741 [user] localhost [db_name] Query 2242 Waiting for table metadata lock SELECT COUNT(*) FROM [db_name].cerber_log 0.000
261639 [user] localhost [db_name] Query 2540 Waiting for table metadata lock SELECT COUNT(*) FROM [db_name].cerber_log 0.000

Seems to be a lot of waiting on table locks. You should probably force InnoDB engine when the plugin is installed. Some of those tables are MyISAM in the DB and that is really not a DB engine optimized for modern web applications anymore as it has table level locking when InnoDB has row level locking which is a lot more efficient.

Yup, I’m seeing this as well w/o any obvious pattern. Seems to be happening at random, but that can’t be the case.
I suggest to gather more environment info. Are you using MySQL or MariaDB?

From the looks of it, at some point the plugin exceeds the # of simultaneous connections to the DB (always set to 35 in all my cases). Perhaps there’s a problem at some point with persistent connections not being closed properly or something like that? Just a wild guess because I don’t have a lot more info at this point.

On a separate note, in all my cases all tables are InnoDB. It’s not a good idea to force engine in the plugin (even in 2018 some web hosts don’t support InnoDB), it’s up to DB server to pick a default/preferred engine.

You’re right about not forcing InnoDB.

And yes, for some reason, unclosed long running connections is something the host mentioned as well.

For the time being the plugin doesn’t force any DB engine at all. It was doing that some years ago and was using MyISAM. Now the tables are created with the default engine configured by the server admin. I think converting all plugins tables to InnoDB eliminates topic issue.

Are you running the website on a shared hosting? What version of PHP?

@bugnumber9

I have PHP 7.2 everywhere, default DB engine set to InnoDB everywhere as well (MariaDB 10.1)
Most websites are on shared hosting with very generous resource limits: 2-4GB of memory, 200-400% CPU, 10-20Mbps for I/O (SSD storage), web server is LiteSpeed.

I don’t think the issue has anything to do with the above resource limits. There’s a limit of 35 simultaneous DB connections per client, and looks like this is what exceeds under certain conditions.
Looking at https://gist.github.com/NazarH/e3d1f9ba6db0066d0cd18a821f19aa49 I suspect that the plugin somehow doesn’t close connections on massive operations, e.g. removing old log entries, etc.

Also, you can see “OPTIMIZE TABLE cerber_log” in my log. I think (again, just a wild guess) this could be a part of the problem. When InnoDB is being used, the server will do “recreate + analyze”, which is a resource-intensive operation. It surely makes sense to add an extra check for table engine and avoid running OPTIMIZE TABLE in case of InnoDB.

Besides that, do you have any idea why “ALTER TABLE cerber_log CHANGE stamp stamp DECIMAL(14,4) NOT NULL” is seen in the log multiple times? This also looks somewhat suspicious, doesn’t it?

It is shared hosting but quite powerful (on paper). Site is running PHP 7.1.

The plugin doesn’t close connections because it uses a WordPress DB connection that was initiated by the WordPress itself, not the plugin.

OPTIMIZE TABLE cerber_log occurs once a day and on a normal website should not be a resource-intensive operation because an average plugin log takes up about 10Mb.

ALTER TABLE cerber_log is part of a “not ideal watchdog feature” and occurs once an hour.

I’ve never come across issues with DB like you’ve got. So I see only one cause of the issues and it’s a misconfigured DB server.

@bugnumber9

Hi there,

Thanks for the reply and looking into this. Misconfigured DB server would not surprise me at all with these guys. Their default position is always to blame plugins, WordPress or me and I’ve been having DB usage resources issues with them on multiple sites recently. We use other hosts and do not run into the same issues and I pretty much use the same tool set on every site with minor differences depending on site needs.

From their replies to tickets, they have very little WordPress expertise and it’s starting to be troublesome for us. No new client sites are going there and I’ll be moving existing ones away as they come close to renewal.

Thanks again for your help! I love your plugin and will keep using it on better hosting ??

Disclaimer: I work for a web host ??

@gioni any idea what exactly can be misconfigured on server side? Our usual stack is CloudLinux, cPanel, LiteSpeed. We don’t use MySQL Governor.

On a separate note, I don’t tend to blame plugins or themes as the first resort, I realize certain things may run goofy because of server-side specific configuration, human error, faulty hardware and what not.

@pixelyzed Who is your web host? Do you have any details about environment? There’s definitely something in common as we’re seeing the same issues, would be really good to find out what it is.

@bugnumber9 The host I’m talking about is called PlanetHoster and that’s on their “World Platform” plans. Our clients are hosted in their Montreal facilities (they also have facilities in France).

FWIW, they are using LiteSpeed and cPanel too but so is MDDHosting which is a host we’ve been using for a lot longer and we’ve never had thoses kinds of issues there (not with the servers and MDDHosting’s support has always been stellar).

What kind of info would you need that I can get from cPanel. These are all shared plans in their “World” infratructure. Here’s what they advertise about them if that gives you a clue.

In comparison, on paper, ressources are more limited on SiteGround and, one of the sites we’ve had issues with at PlanetHoster was on SiteGround’s GoGeek before and they only reason we left was hard drive space which was getting maxed. It was not cost effective to my client to move to the next tier at SiteGround which would have been 3 to 4 times the price of a GoGeek account. But we never got near max CPU and other ressources usage on SiteGround for the same site that has given us a lot of issues on PlanetHoster recently. That was before the issues with the site that prompted me to start this thread. I was using WP Cerber Security on both FWIW but it was not “flagged” as clearly as now on the other site.

So whatever specific info you think I may get for you as a regular shared client, just ask and I’ll try to get it for you. I don’t see PlanetHoster ever doing what you are doing now as they’re way too busy deflecting… And ressources issues are far from thet the only thing prompting me to say that. But whatever can help mitigate these issues I’ll try to help with.

• This reply was modified 6 years, 3 months ago by pixelyzed.
• This reply was modified 6 years, 3 months ago by pixelyzed.

Forgot to give you their World plan link: https://www.planethoster.com/en/World-Hosting

@bugnumber9 Do you have any other input on this issue as a host? Did you know the host I mentioned? I’m running into the same issues again with the same site and WP Cerber is not installed anymore (but I saw another plugin also has its tables as MyISAM and I was about to change that when the connexion errors started again. Waiting on the host to restart the MariaDB server or whatever they did last time to fix it.

Adding this so others searching for similar issues may know that WP Cerber might and probably is not the cause of the problem. My issues with it here were probably symptoms of a larger problem.

Thanks!

This may be related

I am interested in this topic. I have WP Cerber on about 10 websites, shared hosting CPanel, PHP 7.2. One of them started having PHP Warning: mysqli_real_connect(): (HY000/1040): Too many connections. It is a site that has much more traffic than the others. It has WP Rocket caching installed, and not many plugins, so I was perplexed about the error until I thought of WP Cerber, which does lots of logging. I have just updated to 7.9.7, and will see if this error happens again.