The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

eximstats slow queries

Discussion in 'Workarounds and Optimization' started by never2far, Oct 4, 2012.

  1. never2far

    never2far Active Member

    Joined:
    May 6, 2008
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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
    # User@Host: eximstats[eximstats] @ localhost []
    # Query_time: 28.082486 Lock_time: 0.000070 Rows_sent: 0 Rows_examined: 2357022
    use eximstats;
    SET timestamp=1349337790;

    Thank you,
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  3. never2far

    never2far Active Member

    Joined:
    May 6, 2008
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    675
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  5. srpurdy

    srpurdy Well-Known Member

    Joined:
    Jun 1, 2011
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
Loading...

Share This Page