Performance Issue with WP Cerber Traffic Log Cleanup Query

General Support
1

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

2

Your situation seems to point to insufficient resources on your database server. This typically occurs when hosting providers allocate limited resources to the database server, which might not be sufficient for operations like traffic log cleanup in WP Cerber.

I understand this might sound like I’m just passing the buck, but the cleanup SQL query we’re discussing is actually quite simple and optimized. It uses the indexed stamp column, so under normal circumstances, it should run smoothly without putting too much strain on the server. In fact, there’s really no room for further optimization here.

Could you share your hosting provider and plan details? I think the limitations of your plan is the root cause.

As for the Claude.ai suggestion about using a temporary DB table, it’s not something that will work here. The cerber_traffic table is updated in real time, and using a temporary table will lead to problems. The temporary table will skip any new log records that get added while it’s being created or processed.

On top of that, creating and working with a temporary table adds extra load to the server. There’s additional I/O work involved, such as creating the table and pulling data into it.

You got this unusable advice from Claude.ai because it didn’t fully understand the real-time nature of the table.