Support » Plugin: Wordfence Security - Firewall & Malware Scan » Thousands of Database Queries

  • We are having excessive memory usage on a site and it coincides with thousands of database queries, so we turned on query logging and found they were by the WordFence plugin. Turning on the option for low-resource servers helped a little, at least now it’s not using hundreds of megabytes of memory, and about half the number of database queries.

    Still, is it normal to have thousands of “SELECT name, val, autoload FROM wp_wfConfig WHERE name = ‘wfKillRequested'” queries?

Viewing 13 replies - 1 through 13 (of 13 total)
  • Plugin Support wfasa

    (@wfasa)

    Hi Chris, no I don’t think that sounds normal. It sounds like your wfConfig table might be corrupt. If you inspect it via phpMyAdmin you should be able to see if the table is corrupt and if it can be repaired you may be able to do so there.

    If you are unable to repair the table I would advise you delete all tables related to Wordfence and try a fresh install of the plugin. Normally I would have suggested to not do this manually but instead do it by activating the Wordfence setting “Delete Wordfence tables and data on deactivation“. However, if your wfConfig table is corrupt you will likely not be able to save any settings in the plugin.

    Please note that if you have optimized the Wordfence Firewall and then remove Wordfence manually there will be an auto_prepend_file directive in .htaccess and/or .user.ini in the root of your WordPress installation that points to a file called wordfence-waf.php. Do not delete the wordfence-waf.php during the manual deactivateion as this may cause a server error on your site when the auto_prepend_file directive is still in effect.

    Let me know how it goes!

    Ok thanks, I’ll try that.

    I can’t repair the table, it was an InnoDB table. Check, Analyze, and Optimize all say it was OK.

    Settings could still be changed, so I disabled the Firewall, deactivated the plugin (didn’t think about the “delete tables and data” setting), deleted all wf tables, deleted the wflogs folder in the wp-content follder, and reinstalled the plugin.

    I’ll continue monitoring things and see if there are still problems, should know by tomorrow.

    Sorry for the delay, just busy and it’s a pain to get the logs. That didn’t work though – I was really hoping a reset would do it too.

    So here’s what I gathered from one of the occurences…

    …about 8000 queries exactly like this…

    2315 Query SELECT name, val, autoload FROM ep_wp_wfConfig WHERE name = 'wfKillRequested'

    …followed by about 13000 queries similar to…

    2315 Query	INSERT INTO ep_wp_wfConfig (name, val, autoload) values ('scanFileProcessing', 'a:2:{i:0;s:104:\"wp-content/plugins/[...]\";i:1;i:1479507968;}', 'yes') ON DUPLICATE KEY UPDATE val = 'a:2:{i:0;s:104:\"wp-content/plugins/[...]\";i:1;i:1479507968;}', autoload = 'yes'
    
    2315 Query	insert into ep_wp_wfFileMods (filename, filenameMD5, knownFile, oldMD5, newMD5) values ('wp-content/plugins/[...]', unhex(md5('wp-content/plugins/[...]')), 1, '', unhex('FB4F60D443ED475226B2B5950A0E0273')) ON DUPLICATE KEY UPDATE newMD5=unhex('FB4F60D443ED475226B2B5950A0E0273'), knownFile=1
    
    2315 Query	INSERT INTO ep_wp_wfConfig (name, val, autoload) values ('scanFileProcessing', '', 'yes') ON DUPLICATE KEY UPDATE val = '', autoload = 'yes'
    
    2315 Query	SELECT name, val, autoload FROM ep_wp_wfConfig WHERE name = 'wfKillRequested'

    …all plugin files, plus a few at the beginning for wordpress core files…

    …and ending with one really large query…

    2315 Execute UPDATE ep_wp_wfConfig SET '...[BINARY?]

    Thoughts? Ideas?

    Plugin Support wfasa

    (@wfasa)

    Hi again Chris!

    Strange one. Would you be able to test one time to just delete all the Wordfence tables and let them be recreated? If you can, please let me know if the issue persists after that.

    Well that doesn’t really work. I deleted the tables, but they weren’t recreated. All of the settings were empty, nothing checked, and it wouldn’t Save Options without filling out some settings. So I deleted the wflogs folder too and deactivated and reactivated the plugin, so it’s back to defaults again, then I disabled Live Traffic View and a couple of alerts – no other changes.

    But that didn’t fix it either.

    Also, previously I was waiting for the regular scans, but I tried a manual scan just now and it triggered whatever is happening. So it’s definitely happening when the scans run.

    Plugin Support wfasa

    (@wfasa)

    Two things to check on the Wordfence diagnostics page that may help:

    1. Does the database user have all necessary privileges?
    2. Is wp cron functioning normally? (If so all cron jobs listen on the Wordfence Diagnostics page will be dated in the future)

    1. Filesystem, MySQL, PHP and Connectivity all have green check marks next to everything.
    2. All of the cron job have a future date.

    I enabled debugging mode and ran another scan. Near the end, I got the first email notification and looked at the log and saw Scanning contents… and two more emails and the end of the scan within about 30 seconds (I imagine there’s a slight delay on email delivery though but I don’t know how much).

    Email 1 – 10:33:43 -0800 (PST)
    WordPress peak memory usage: 548.83 MB
    Number of database queries: 13944

    Email 2 – 10:33:52 -0800 (PST)
    WordPress peak memory usage: 420.74 MB
    Number of database queries: 10400

    Email 3 – 10:34:06 -0800 (PST)
    WordPress peak memory usage: 359.51 MB
    Number of database queries: 9393

    I emailed the activity log to wftest@wordfence.com. The domain is DramaInPanama.com.

    Any thoughts?

    Plugin Support wfasa

    (@wfasa)

    Hi Chris,
    can’t say I have a solution. Can you send a diagnostics report from Wordfence and I’ll have a look at that? I’ll look at the activity log at the same time. You can send the diagnostics to asa@wordfence.com. Since you seem certain it’s the scan that does it you should be able to have Wordfence active as long as you turn of automatic scans on the Wordfence Options page.

    Hi,
    I’d like to add I have the exact same issue, with all 3 of my wordfence installs.
    If it might help, it started in the middle of week 45.
    I’m very interested in a solution, as this is massively spamming my mysql binlogs.
    Rgds
    V

    Ok, I just sent the diagnostics report.

    I disabled the scans and will see if there is still a problem or if it goes away.

    I think the 1000s of queries might be normal and just logging which files have already been scanned during the current scan. It’s a little excessive and could probably be optimized some way, but if it’s running a query for every file and you have a bunch of plugins (1000s of files) then it seems like this would not be unexpected. (The unexpected part would just be the high memory usage.)

    So turning off the automatic scans fixed the problem, no excessive memory usage now. I was pretty sure that was the case, just wanted to give it enough time in case something popped up (even though it was daily).

    It’s not the solution I was looking for though. I’d like to keep automatic scan going. Any ideas from the logs I sent in?

    Also, any confirmation on how many queries to expect? I checked another site and saw 1000’s of queries but only about 40MB of memory usage. Otherwise I’m going to assume that 1000’s of database queries is normal. Now if we can just figure out why it’s using so much memory on certain sites.

    Plugin Support wfasa

    (@wfasa)

    Hi Chris, sorry for the late reply. Just catching up on stuff right now. I checked your diagnostics and it looks like you have Query Monitor active. This plugin adds overhead to every database query that is made on the site and it will cause a lot of database overhead if enabled while scanning with Wordfence.

    If you had Query Monitor active while scanning, that would be the explanation. I can’t tell you how many queries would be normal because it depends a lot on how many plugins and themes you have installed on your site.

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘Thousands of Database Queries’ is closed to new replies.