pruning eximstats sqlite

[Philip R.]

Hi all,
I'm seeing our eximstats_db growing quite large for our disk space. 2.8GB when we noticed it last Thursday. We've taken steps to reduce this such as reducing the number of days the tweak settings for "Stats and Logs" -> "Exim stats in the database" are set for logging from 30 to 7. We've also changed the tweak settings for "Mail"-> "Initial default/catch-all forwarder destination" from system account to Fail. Except, after this was done the log increased to 3.5GB by monday. It has held steady since Monday at 3.5GB.

I've been seeing links online, such as this one, which recommend the first step above which we have done. This thread is quite old though, and at that time it appears the database used MySQL. Currently according to the Cpanel change logs for version 63.9999.74, see "Implemented case CPANEL-10867: Converted Eximstats DB to SQLite.", the eximstats_db has moved to sqlite3.

So I've tried accessing the Database using sqlite instead of MySQL to manually attempt dropping some old data. Unfortunately, when attempting to access the file using sqlite3 the database I receive the following issue. Does anyone know how to properly access this db file to drop old tables and slim down the db size? Listed below are also the current system version Specs.

bash#cd /var/cpanel
bash[/var/cpanel]# ls exim*
exim.conf.deps  exim_ipv4_sort_bias  exim_service_auth_enable  eximstats_db.sqlite3  eximstats_db.sqlite3-shm  eximstats_db.sqlite3-wal  eximstatspass
bash# sqlite3 eximstats_db.sqlite3
sqlite> .tables
Error: file is encrypted or is not a database

Thanks,
Phil

My Access Level - Root (system admin)
CentOS release 6.7 (Final)
cPanel version:11.64.0.17
envtype:standard
CPANEL=release
Server version: Apache/2.2.31 (Unix)
Server built:   Jul  1 2016 22:46:39
Cpanel::Easy::Apache v3.34.1 rev9999
PHP 5.6.23 (cli) (built: Jul  1 2016 22:49:30)
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
mysql  Ver 14.14 Distrib 5.5.54, for Linux (x86_64) using readline 5.1

 

[cPanelNick]

/usr/local/cpanel/3rdparty/bin/perl -MCpanel::EximStats::DB::Sqlite -e 'Cpanel::EximStats::DB::Sqlite->dbconnect()->do("VACUUM;");'

This should do what you need.

 

[Philip R.]

Hi cPanelNick, and thank you for your reply! I'm trying this presently. I will update again once it is done. It has been running for about 15 minutes now.

 

[Philip R.]

Update - I couldn't Vacuum the database because of the lack of space on the disk. After many hours now of trying to find an appropriate way via sqlite3 to change the temporary file path See SQLite3 Vacuum Documentation that vacuum uses during it's operation, and a few grey hairs later, I just moved the database file, restarted exim, and restarted tailwatchd. A new small sqlite db was generated, and my precious space has been freed once again. No changes to pragma in SQlite3 seemed to make any difference where vacuum was eating up space. So much for playing by the book I guess.