Title: Database Performance Last modified: January 18, 2021 --- # Database Performance * Resolved [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/) * Hi there, in general I find this plugin to be excellent, however I have noticed that the MySQL queries are very slow. * ![](https://i0.wp.com/sharing.s3.wasabisys.com/FtWxJoaRpocAfmFe.png?ssl=1) * In particular the `wp_defender_lockout` select takes 210ms and the `wp_defender_lockout_log` select takes 12ms on average. That might not sound like a lot but for comparison the slowest native WP query takes 2.5ms. Overall Defender queries are responsible for 90% of all my MySQL activity, put another way, installing this plugin increases the database workload 10 fold! * ![](https://i0.wp.com/sharing.s3.wasabisys.com/iTar3YZRHdzjBooD.png?ssl=1) * I have tried disabling Login Protection and 404 Detection but it makes no difference. Is there a particular feature I can disable to improve performance? Is there any other workaround for this issue? * Thanks in advance šŸ™‚ Viewing 14 replies - 1 through 14 (of 14 total) * Plugin Support [Laura ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudev-support8/) * (@wpmudev-support8) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13922542) * Hi [@andrewspear](https://wordpress.org/support/users/andrewspear/) * I hope youā€™re well today and thank you for reporting this. * I agree that a bit over 0.2s doesnā€™t seem like a lot but overall it is a long query and adds up to general query execution time. This is related to checking locked-out IPs in the database so could you take a look into the plugin, please, and let me know how many current lockouts are reported there in ā€œFirewall -> IP Banningā€? * Also, does this query time/load change on your end if you delete firewall logs(ā€œ Defender -> Firewall -> settingsā€) ā€“ if this is acceptable thing to test for you ā€“ and if you change storage period to significantly less than default 30 days (on the same settings page)? * Best regards, Adam * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13924794) * Under ā€œIP Banningā€ it says there are 98 active lockouts. * The firewall log was already set to store for 3 **days** only, however the Logs page appears to go back about three **weeks** to 26 December. Perhaps this is a bug? * Regardless of that, I have emptied the logs completely now, and monitored the server for 30 minutes, the query time seems about the same: * ![](https://i0.wp.com/sharing.s3.wasabisys.com/o7pjJcs1p4nJOkGS.png?ssl=1) * Plugin Support [Imran ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudev-support9/) * (@wpmudev-support9) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13937604) * Hi [@andrewspear](https://wordpress.org/support/users/andrewspear/), * Thanks for providing that information. * Could you confirm if your server has any type of object caching that could be cleared? On the Firewall Storage settings, by default it is set to 30 days when plugin is installed. How long ago was the change made to 3 days? This change you made should help reduce the amount of the query takes going forward. * Best, Jonathan S * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13956743) * The site is hosted by WPEngine and I can confirm that they use the standard [ wp_object_cache]([https://developer.wordpress.org/reference/classes/wp_object_cache/](https://developer.wordpress.org/reference/classes/wp_object_cache/)) feature. * I switched from 30 days to 7 days as soon as I installed it (over a month ago), but recently reduced it to 3 days but it has made no change. In fact the average query time has got a lot longer now: * ![](https://i0.wp.com/sharing.s3.wasabisys.com/v4ypWSv9Cs2Wwomt.png?ssl=1) - This reply was modified 5 years, 4 months ago by [Andrew](https://wordpress.org/support/users/andrewspear/). * Plugin Support [Imran ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudev-support9/) * (@wpmudev-support9) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13965098) * Hi [@andrewspear](https://wordpress.org/support/users/andrewspear/), * Thank you for confirming and sorry this is causing you trouble. * Were you able to also clear WP Engineā€™s object cache? I have forwarded this to our Plugin Team for further investigation. The screenshot of queries is being pulled from a specific app provided by your host or is this a WordPress plugin? * Perhaps we can try a basic approach which would be to deactivate the plugin temporarily and reactivate it. Also, is there a high amount of IPs being locked out in Defender > Firewall > IP Banning > Active lockouts? * Let us know how query time behaves. * Best, Jonathan S * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13968181) * I have tried turning off the object cache completely but it makes no difference. The screenshots are from New Relic which is monitoring the database on the server- side (not a WP plugin). * Queries are still getting gradually slower, itā€™s averaging 410ms now. If I disable the entire Defender plugin then the slow queries do go away as weā€™d expect, you can see the massive drop in DB usage here: * ![](https://i0.wp.com/sharing.s3.wasabisys.com/GKTGX2UwgIvOsVJu.png?ssl=1) * Active lockouts: 107 Blocklist: 15 Allowlist: 3 * Oddly I still see items in the logs going back 10 days to 18 Jan, even though the limit is set to 7 days. Iā€™m unsure if this is related. * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13968237) * I did some more digging for you. * Iā€™ve just looked in the database and your `wp_defender_lockout` table has almost a million rows, Iā€™m not sure if this is normal or not but we do get millions of visitors. I donā€™t see a million entries in the UI, under logs or lockouts, but perhaps you use these entries for something else. * Thereā€™s also no indexing on the data columns šŸ˜§, so if this table is being queried on every page load it could explain why it slows the site down so much. * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13968351) * Yeah, the issue is definitely related to database/query optimisation. I added simple indexes to a few columns in the `wp_defender_lockout` table and average query speed went from over 600ms to 8ms, thatā€™s a 75x performance improvement! * ![](https://i0.wp.com/sharing.s3.wasabisys.com/AWMwcJMtpbTjL2mi.png?ssl=1) * I donā€™t know exactly which columns youā€™re querying so it was a stab in the dark, Iā€™m sure your developers can improve this much more. * ![](https://i0.wp.com/sharing.s3.wasabisys.com/je3TgJrzhpbwYDfU.png?ssl=1) * Plugin Support [Dmytro ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudevsupport16/) * (@wpmudevsupport16) * [5 years, 4 months ago](https://wordpress.org/support/topic/database-performance/#post-13971965) * Hi there [@andrewspear](https://wordpress.org/support/users/andrewspear/) * Thanks very for the additional digging effort! * And it does appear that adding indexes to tables has indeed improved the performance on your site. * Iā€™m going to pass this info on to our developers to let them dig in. šŸ™‚ * Cheers! Patrick * [Muser](https://wordpress.org/support/users/fvcmedia/) * (@fvcmedia) * [5 years, 3 months ago](https://wordpress.org/support/topic/database-performance/#post-14079755) * This has caused one of my customerā€™s server to crash twice in the past two days. * Such a beginnerā€™s mistake! How can you release something like this? * And I do not think it has even been addressed in the past update either. * [@andrewspear](https://wordpress.org/support/users/andrewspear/) ā€“ I wish Iā€™ve discovered your post sooner! Thank you for sharing this. One question also ā€“ what monitoring service are you using in the screenshot youā€™ve provided? Thanks. * [Nastia Kolomoytseva](https://wordpress.org/support/users/nastaia/) * (@nastaia) * [5 years, 3 months ago](https://wordpress.org/support/topic/database-performance/#post-14092382) * Hello [@fvcmedia](https://wordpress.org/support/users/fvcmedia/) * I trust youā€™re doing well! * I apologize for the inconvenience that this issue has caused. In the next Defender release, in version 2.4.7 this issue will be fixed. * Kind regards, Nastia * Thread Starter [Andrew](https://wordpress.org/support/users/andrewspear/) * (@andrewspear) * [5 years, 3 months ago](https://wordpress.org/support/topic/database-performance/#post-14092391) * [@fvcmedia](https://wordpress.org/support/users/fvcmedia/) Those screenshots are from NewRelic, itā€™s a great tool for tracking errors and performance bottlenecks. * Plugin Support [Patrick ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudevsupport12/) * (@wpmudevsupport12) * [5 years, 2 months ago](https://wordpress.org/support/topic/database-performance/#post-14201778) * Hi [@andrewspear](https://wordpress.org/support/users/andrewspear/) * I hope you are doing well. * We already released the 2.4.7, can you please confirm if the issue is gone? * Best Regards Patrick Freitas * Plugin Support [Patrick ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudevsupport12/) * (@wpmudevsupport12) * [5 years, 2 months ago](https://wordpress.org/support/topic/database-performance/#post-14233763) * Hi [@andrewspear](https://wordpress.org/support/users/andrewspear/) * I hope you are doing well and safe! * We havenā€™t heard from you in a while, Iā€™ll mark this thread as resolved. * Feel free to let us know if you have any additional question or problem. * Best Regards Patrick Freitas Viewing 14 replies - 1 through 14 (of 14 total) The topic ā€˜Database Performanceā€™ is closed to new replies. * ![](https://ps.w.org/defender-security/assets/icon-256x256.gif?rev=3435930) * [Defender Security - Malware Scanner, Login Security & Firewall](https://wordpress.org/plugins/defender-security/) * [Frequently Asked Questions](https://wordpress.org/plugins/defender-security/#faq) * [Support Threads](https://wordpress.org/support/plugin/defender-security/) * [Active Topics](https://wordpress.org/support/plugin/defender-security/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/defender-security/unresolved/) * [Reviews](https://wordpress.org/support/plugin/defender-security/reviews/) ## Tags * [Lockout](https://wordpress.org/support/topic-tag/lockout/) * [MySQL](https://wordpress.org/support/topic-tag/mysql/) * [performance](https://wordpress.org/support/topic-tag/performance/) * 14 replies * 7 participants * Last reply from: [Patrick ā€“ WPMU DEV Support](https://wordpress.org/support/users/wpmudevsupport12/) * Last activity: [5 years, 2 months ago](https://wordpress.org/support/topic/database-performance/#post-14233763) * Status: resolved