Missing Indexes on cerber_traffic and cerber_log

Bug Reports
1

Bug

cerber_traffic only has KEY stamp (stamp). No index on ip.
cerber_log only has KEY ip (ip). No index on stamp.

Any query filtering by ip + stamp (geo updates, bot checks, lockout counts) does a full table scan. On active sites these tables grow to 3 GB+, causing queries to take 30–50 seconds each. They queue up, exhaust all MySQL connections, and crash the site with “Error establishing database connection”.

Observed on production: 108 concurrent PHP processes stuck on UPDATE cerber_traffic SET country = '...' WHERE ip = '...' AND country = '' — all doing full table scans.

Fix

Add these indexes in cerber_upgrade_db():

-- cerber_traffic: covers ip + time range queries (geo, bot checks)
ALTER TABLE cerber_traffic ADD INDEX idx_ip_stamp (ip, stamp);

-- cerber_traffic: covers cleanup DELETE by user_id + stamp
ALTER TABLE cerber_traffic ADD INDEX idx_user_stamp (user_id, stamp);

-- cerber_log: replace single KEY ip with composite (covers ip + time queries)
ALTER TABLE cerber_log DROP INDEX ip;
ALTER TABLE cerber_log ADD INDEX idx_ip_stamp (ip, stamp);

-- cerber_log: for dashboard/report queries filtering by time range
ALTER TABLE cerber_log ADD INDEX idx_stamp (stamp);

-- cerber_log: for activity + time range queries (lockout/login counts)
ALTER TABLE cerber_log ADD INDEX idx_activity_stamp (activity, stamp);

Safe for auto-cleanup - MySQL maintains indexes automatically on INSERT/DELETE.

2

Good catch on the query pattern, but the missing composite indexes are not an oversight. The current schema is intentionally biased toward fast inserts because cerber_traffic and cerber_log are hot write tables. On many hosts extra indexes become the real bottleneck during attacks or high traffic periods.

What you are seeing is a valid edge case: very large tables plus concurrent updates can force full scans and exhaust MySQL connections. That is a scaling limit of the current single table design, not simply a missing index bug.

Adding (ip, stamp) can help on large installations like yours, but shipping multiple composite indexes by default would slow down logging for a much larger group of WordPress users. So this is something that should stay site specific for now, not part of cerber_upgrade_db() globally.

Short term, you can safely add a minimal index set on your server if reads are your bottleneck:

cerber_traffic (ip, stamp)
cerber_log (ip, stamp)

Avoid adding many extra indexes unless you confirm with EXPLAIN that they are actually used.

A point to challenge in your proposal

Safe for auto cleanup because MySQL maintains indexes automatically is true but incomplete. The cost is paid on every insert, every delete, and every page split. On log tables, that cost is continuous and often more damaging than occasional slow reads. That is the reason we do not ship heavy indexing by default.

Tiered Storage is coming to WP Cerber

We are already working on a new log storage scheme for exactly this reason: one table design cannot serve both write at line rate under attack and fast analytics on months of data without painful compromises.

The upcoming architecture splits the problem into tiers: a hot write path (minimal contention), an operational archive optimized for reads (triage, etc.), and deep storage for long retention. The migration is designed to be safe and resumable, so we will not ask people to run manual conversions or accept downtime.

If you are interested in testing this feature, please RSVP and we will share a beta build with tiered storage enabled.