Eximstats db growing huge [case 59361]

Lehman

Registered
Feb 22, 2012
3
0
51
cPanel Access Level
Root Administrator
Hi there!

I've tried to search the forum to understand this, but the only information I found is from back to 2004.

My eximstats db is quite huge now (315 MB), but I'm quite noob and I don't know what it stores. Could I empty the tables? I keep a lot of emails in my accounts and I don't want to lose them, but the MySQL process for eximstats is very cpu consuming now.
 

katmai

Well-Known Member
Mar 13, 2006
564
3
168
Brno, Czech Republic
you can select tables and click empty in phpmyadmin logged in as root. it will work just fine. sorry for the late reply - just had this problem myself.
 

sneader

Well-Known Member
Aug 21, 2003
1,191
60
178
La Crosse, WI
cPanel Access Level
Root Administrator
Having a similar problem. This is on an older server where /var is 10GB (used to be plenty big in the 'old days'). Space on /var is precious now, and eximstats takes up nearly 3GB of it. Any strategies out there for minimizing the amount of space that eximstats takes up?

- Scott
 

katmai

Well-Known Member
Mar 13, 2006
564
3
168
Brno, Czech Republic
i am running a weekly maintenance where i just empty the eximstats db. not drop it, just empty all tables contents. this does the trick.
 

mtindor

Well-Known Member
Sep 14, 2004
1,363
65
178
inside a catfish
cPanel Access Level
Root Administrator
My strategy for keeping eximstats small is to simply choose a reasonable period to retain the stats. Other than out of boredom, I've never [knowingly] made use of eximstats data. I don't view email sending summarries, relayers, or any of that. Everything I ever want is in the exim logfiles.

With 11.32, the default is to retain stats for 90 days, which has really wreaked havoc for some with the initial high load during the importing / migration of stats and the default 90 day retention period. I just set my retention period to something much smaller, like 7 days, and the database decreased in size expontentially over the course of the day when I made the change.

Mike
 

sneader

Well-Known Member
Aug 21, 2003
1,191
60
178
La Crosse, WI
cPanel Access Level
Root Administrator
Great idea, Mike! I found the setting in Tweak Settings > Stats and Logs, for anyone else looking for it.

- Scott
 

mtindor

Well-Known Member
Sep 14, 2004
1,363
65
178
inside a catfish
cPanel Access Level
Root Administrator
Scott,

I apologize that I failed to say where that setting was found. Of course, you found it with no problem. On one server, my eximstats grew to about 3+ GB in size shortly after i did the 11.32 update. I set retention to 7 days, and then a day later my eximstats database was down to about 460 MB.

Mike
 

rmackay

Well-Known Member
Nov 26, 2002
75
0
156
great info here.. my eximstats DB went to 6GB in no time and caused the server to go nuts. 90 days? seems a bit too high.
 

cPanelNick

Administrator
Staff member
Mar 9, 2015
3,488
35
158
cPanel Access Level
DataCenter Provider
11.32.3.15 changed the default back to 30 days from 90 for new installs. However, it won't change it 30 if you are already running with 90 days in order to avoid data loss.


Fixed case 59361: Reduce load from mail delivery reports data collection and retention
 

rmackay

Well-Known Member
Nov 26, 2002
75
0
156
Nick, was this, combined with some sort of table change, possibly causing mysql high server loads on some cron job?
 

cPanelNick

Administrator
Staff member
Mar 9, 2015
3,488
35
158
cPanel Access Level
DataCenter Provider
The indices and log processing functionality were further optimized. You probably saw it having to rebuild one or two of the indices during the update. We use mysqldiff to only make the needed changes so it shouldn't need to rebuild everything.

On a side note, if you don't need more then 7 days worth of data, I'd highly recommend setting the retention period to just that ( I would not go any lower to avoid loss of bandwidth data ) .
 

sneader

Well-Known Member
Aug 21, 2003
1,191
60
178
La Crosse, WI
cPanel Access Level
Root Administrator
Well, I lowered it from 90 days to 14 days. A few hours later my server went high load. top showed mysql was the culprit, then viewing mysql queries, it was eximstats doing all the deletes to purge out the old stats. Eventually had to just restart MySQL to tame the box. If I were to do it again, I might gracefully lower the days... maybe from 90 to 80 to 70, etc.?

- Scott
 

Solokron

Well-Known Member
Aug 8, 2003
851
1
168
Seattle
cPanel Access Level
DataCenter Provider
Is there a way to run this configuration change through the command line?