Dear WP Cerber Support Team,
I am experiencing performance issues related to the traffic log cleanup mechanism in the WP Cerber security plugin. During a system performance analysis, I observed that the DELETE query for cleaning up old traffic logs is causing system resource strain.
Specific Observations:
The DELETE query (DELETE FROM cerber_traffic WHERE stamp < 1729705628) is causing high CPU usage
The query appears to block database operations
PHP-FPM processes show high resource consumption during this cleanup process
Current MySQL Queries
Opening tables DELETE FROM cerber_traffic WHERE stamp < 1729705628
Claude.ai gave the following advice on how to improve query:
Proposed Optimized Cleanup Query:
-- Create a temporary table for safe deletion
CREATE TEMPORARY TABLE temp_delete_logs AS
SELECT id FROM cerber_traffic
WHERE stamp < (UNIX_TIMESTAMP(NOW()) - (7 * 24 * 60 * 60))
LIMIT 5000;
-- Delete in batches with transaction support
START TRANSACTION;
DELETE ct FROM cerber_traffic ct
INNER JOIN temp_delete_logs tdl ON ct.id = tdl.id;
COMMIT;
-- Clean up temporary table
DROP TEMPORARY TABLE temp_delete_logs;
(This is just a simple sample, and you can further improve the code.)
Key Optimization Benefits:
Prevents full table blocking
Limits deletion to 5000 records per batch
Uses transaction for data consistency
Dynamically calculates log retention period
Reduces server resource consumption
Provides a more controlled cleanup process
Could you please review and provide your expert advice on this issue?
Best regards,
Lisette