Support » Plugin: Cerber Security, Antispam & Malware Scan » 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

    (@gioni)

    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.

    Plugin Author Gioni

    (@gioni)

    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.

    Plugin Author Gioni

    (@gioni)

    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 1 year, 9 months ago by pixelyzed.
    • This reply was modified 1 year, 9 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!

    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.