Title: Heavy Database Load Last modified: August 22, 2018 --- # Heavy Database Load * [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/) * Hi there, * I had a client site down this morning with a database connection error. I openend a ticket with the host and they brought it back but they are compaining of too many requests to the database. The guy gave me a long list of requests, all coming from WP Cerber. * Any way I can set it up to lessen the database load and, is this something you ar looking into? Love the plugin and would hate to have to scrap it. * Thanks! Viewing 15 replies - 1 through 15 (of 15 total) * Plugin Author [gioni](https://wordpress.org/support/users/gioni/) * (@gioni) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10614646) * Hi! What kind of requests do you get? Are they the same? * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10615318) * 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. * [Nazar Hotsa](https://wordpress.org/support/users/bugnumber9/) * (@bugnumber9) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10615493) * 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. * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10615513) * You’re right about not forcing InnoDB. * And yes, for some reason, unclosed long running connections is something the host mentioned as well. * Plugin Author [gioni](https://wordpress.org/support/users/gioni/) * (@gioni) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10616526) * 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](https://wordpress.org/support/users/bugnumber9/) * [Nazar Hotsa](https://wordpress.org/support/users/bugnumber9/) * (@bugnumber9) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10616801) * 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](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? * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10621173) * It is shared hosting but quite powerful (on paper). Site is running PHP 7.1. * Plugin Author [gioni](https://wordpress.org/support/users/gioni/) * (@gioni) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10621256) * 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](https://wordpress.org/support/users/bugnumber9/) * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10622342) * 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 🙂 * [Nazar Hotsa](https://wordpress.org/support/users/bugnumber9/) * (@bugnumber9) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10627625) * Disclaimer: I work for a web host 🙂 * [@gioni](https://wordpress.org/support/users/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](https://wordpress.org/support/users/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. * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10628524) * [@bugnumber9](https://wordpress.org/support/users/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 7 years, 9 months ago by [pixelyzed](https://wordpress.org/support/users/pixelyzed/). - This reply was modified 7 years, 9 months ago by [pixelyzed](https://wordpress.org/support/users/pixelyzed/). * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 9 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10628528) * Forgot to give you their World plan link: [https://www.planethoster.com/en/World-Hosting](https://www.planethoster.com/en/World-Hosting) * Thread Starter [pixelyzed](https://wordpress.org/support/users/pixelyzed/) * (@pixelyzed) * [7 years, 8 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-10670048) * [@bugnumber9](https://wordpress.org/support/users/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! * [demon_ru](https://wordpress.org/support/users/demon_ru/) * (@demon_ru) * [7 years, 5 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-11034753) * [This may be related](https://wordpress.org/support/topic/table-locks-when-using-multisite-and-simultaneous-cron/?output_format=md) * [melissakfreeman](https://wordpress.org/support/users/melissakfreeman/) * (@melissakfreeman) * [7 years, 3 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-11182705) * 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. Viewing 15 replies - 1 through 15 (of 15 total) The topic ‘Heavy Database Load’ is closed to new replies. * ![](https://s.w.org/plugins/geopattern-icon/wp-cerber_77a9bf.svg) * [WP Cerber Security, Anti-spam & Malware Scan](https://wordpress.org/plugins/wp-cerber/) * [Frequently Asked Questions](https://wordpress.org/plugins/wp-cerber/#faq) * [Support Threads](https://wordpress.org/support/plugin/wp-cerber/) * [Active Topics](https://wordpress.org/support/plugin/wp-cerber/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/wp-cerber/unresolved/) * [Reviews](https://wordpress.org/support/plugin/wp-cerber/reviews/) * 15 replies * 5 participants * Last reply from: [melissakfreeman](https://wordpress.org/support/users/melissakfreeman/) * Last activity: [7 years, 3 months ago](https://wordpress.org/support/topic/heavy-database-load/#post-11182705) * Status: not resolved