never2far

Active Member
May 6, 2008
34
0
56
Hello,

I noticed on one server that there are some slow queries on eximstats database. Had anyone else this problem ? And if yes ...how did you optimized this database?
Searching on the net i saw that many people prefer to empty eximstats tables. But is there other way so that i can keep statistics ?

Here is an example from the slow_queries.log:

# Time: 121004 11:03:10
# [email protected]: eximstats[eximstats] @ localhost []
# Query_time: 28.082486 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 2357022
use eximstats;
SET timestamp=1349337790;

Thank you,
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,226
463
Hello :)

You may want to check the disk space usage of the eximstats database:

# du -sh /var/lib/mysql/eximstats

You can reduce it's size by adjusting the following option in "WHM >> Server Configuration >> Tweak Settings" under "Stats and Logs":

"The interval, in days, to retain Exim stats in the database"

Thank you.
 

never2far

Active Member
May 6, 2008
34
0
56
Hello cPanelMichael and thank you for your answer. The database has 4.7 GB and The interval was 90 days. I have reduced to 60 days.

But is there any way i can fix those slow queries somehow ?

Is it save to run mysqlcheck --optimize eximstats durring the day since it will take some time and i think it wil cause load. Also I'm not sure that mysqlcheck --optimize is the solution here...so please tell me more info.

Thank you
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,226
463
The following option in "Tweak Settings" under "Stats and Logs" is enabled by default:

"Use delayed sql inserts in the eximstats process to reduce the number of disk writes."

Per it's description, this option will queue the inserts in memory until they can be written in a single block. This is generally much faster then performing multiple seperate inserts, however this does use additional memory while the rows are queued.

I don't know of any additional optimization available for this database.

Thank you.
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Yeah it's just that they're is so much data this is why the query takes so long. Not a lot you can do about it except what was mentioned by Michael.

I only keep stats for 7 days myself. I don't see much use in keeping it longer.