Managing and Cleaning up the Eximstats SQLite Database in cPanel

by

Managing and Cleaning up the Eximstats SQLite Database in cPanel

What Is the Eximstats Database?

The eximstats database is used by cPanel servers to store statistics about email traffic. It captures data such as messages sent, failed deliveries, deferrals, and more. This data is useful for mail reporting in WHM or for scripts parsing mail usage.

Over time, however, this database can grow significantly, especially on busy mail servers. Since it uses the sqlite3 format, you can access and manage it using the sqlite3 command-line utility. This allows you to inspect, trim, and vacuum (compact) the database as part of regular server maintenance.

Checking Table Sizes Before Cleanup

Before cleaning up, it’s a good idea to count the number of rows in each table to understand where most of the data is stored. Here’s a command that outputs the row count for each table in eximstats:

sqlite3 /var/cpanel/eximstats_db.sqlite3 "
SELECT 'defers', COUNT(*) FROM defers
UNION ALL
SELECT 'failures', COUNT(*) FROM failures
UNION ALL
SELECT 'metadata', COUNT(*) FROM metadata
UNION ALL
SELECT 'sends', COUNT(*) FROM sends
UNION ALL
SELECT 'smtp', COUNT(*) FROM smtp;"

Example output (before cleanup):

defers|104832
failures|498820
metadata|1
sends|1082493
smtp|937210

Cleanup Script for Old Records

You can safely trim the tables by deleting records older than 30 days. The following script removes old entries from the sends, failures, and defers tables using the sendunixtime field. It then vacuums the database to reclaim space.

#!/bin/bash

DB="/var/cpanel/eximstats_db.sqlite3"
BACKUP="$DB.bak.$(date +%F)"

# Backup before deleting
cp "$DB" "$BACKUP"

# Trim entries older than 30 days
sqlite3 "$DB" <<EOF
DELETE FROM sends WHERE sendunixtime < strftime('%s', 'now', '-30 days');
DELETE FROM failures WHERE sendunixtime < strftime('%s', 'now', '-30 days');
DELETE FROM defers WHERE sendunixtime < strftime('%s', 'now', '-30 days');
VACUUM;
EOF

If you prefer not to create a Bash script, you can run the entire cleanup process in a single line directly from the command line. This one-liner deletes all records older than 30 days from the sends, failures, and defers tables and then vacuums the database to reclaim disk space.

sqlite3 /var/cpanel/eximstats_db.sqlite3 "DELETE FROM sends WHERE sendunixtime < strftime('%s', 'now', '-30 days'); DELETE FROM failures WHERE sendunixtime < strftime('%s', 'now', '-30 days'); DELETE FROM defers WHERE sendunixtime < strftime('%s', 'now', '-30 days'); VACUUM;"

Save this script as cleanup_eximstats.sh, make it executable (chmod +x cleanup_eximstats.sh), and run it manually or via a cron job.

Checking Row Counts After Cleanup

After the cleanup and vacuum, rerun the row count check to verify the reduction in size:

sqlite3 /var/cpanel/eximstats_db.sqlite3 "
SELECT 'defers', COUNT(*) FROM defers
UNION ALL
SELECT 'failures', COUNT(*) FROM failures
UNION ALL
SELECT 'metadata', COUNT(*) FROM metadata
UNION ALL
SELECT 'sends', COUNT(*) FROM sends
UNION ALL
SELECT 'smtp', COUNT(*) FROM smtp;"

Example output (after cleanup):

defers|123
failures|9281
metadata|1
sends|17324
smtp|937210

Conclusion

Maintaining the eximstats database by trimming old entries is essential for ensuring your mail server remains performant and doesn’t consume unnecessary disk space. With simple sqlite3 commands, you can automate and schedule this cleanup as part of your regular server maintenance tasks.