eximstats SQL delayed insert

[vmicovic]

I don't want to open a ticket yet, so I'm trying to solve the problem on my own.

I did some tests and came up with a temporary solution:

1) Log into WHM
2) Go to Main >> Service Configuration >> Exim Configuration Manager
3) Go to the Reset tab
4) Select the option "Reset the entire Exim configuration to the default settings provided with cPanel & WHM"
5) Check the button for "Disable third-party and custom ACLs"
6) Hit the Reset button at the bottom

That solved my problems. It appears like a bug in cPanel's exim configuration, but I don't know what exactly causes it, it could be an ACL or a main exim.conf option. I need to do some testing and see what brings back the problem.

If anyone tries the above procedure, please let me know.

Thank you, this resolve problem.
But main problem in my case is hard drive, i contact DC for replacement.

[sehh]

I'm glad it worked for you.

This issue is indeed not related to the SQL delayed inserts, but once people see their emails getting dropped the first thing they notice is those delayed inserts by "eximstats", so we all thought these two are related. Apparently not, the issue is with the broken ACL scripts once we all got upgraded via "upcp".

[kalexanakis]

Hello.

I ve got exactly the same problem with eximstats (delayed inserts are delayed... for ever!)

I tried to remedy the situation with the following actions in the order given:
- restaring exim
- Optimizing MySQL using mysqltuner suggestions
- optimizing eximstats database tables (repair, optimize)
- reducing eximstats log to from 90 to 30 days
- emptying sends and smtp tables
- clearing email message queue
- reseting exim configuration with default CPanel/WHM configuration
- disabling eximstats from tailwatchd

Only the final action worked, but ofcourse I don't have mail stats now.
I am on a VPS with WHM 11.32.2 (build 25) and CENTOS 5.8 x86_64 virtuozzo

Any help would be really appreciated...

[hostproof]

The stats are killing one of our servers. The Eximstats tables are growing fast - current size of the 4 tables is 16 GB containing a sum of 12 million entries. I have changed the days it keeps stats from the default of 90 days to 3 days, will share if this helps.

[cpanelnick]

The stats are killing one of our servers. The Eximstats tables are growing fast - current size of the 4 tables is 16 GB containing a sum of 12 million entries. I have changed the days it keeps stats from the default of 90 days to 3 days, will share if this helps.

I'd love to take a look at this system as this is the largest database size I've personally seen. If you could open a ticket and PM me the #, I would be happy to make some recommendations after looking at the system.

[cpanelnick]

Hello.

I ve got exactly the same problem with eximstats (delayed inserts are delayed... for ever!)

I tried to remedy the situation with the following actions in the order given:
- restaring exim
- Optimizing MySQL using mysqltuner suggestions
- optimizing eximstats database tables (repair, optimize)
- reducing eximstats log to from 90 to 30 days
- emptying sends and smtp tables
- clearing email message queue
- reseting exim configuration with default CPanel/WHM configuration
- disabling eximstats from tailwatchd

Only the final action worked, but ofcourse I don't have mail stats now.
I am on a VPS with WHM 11.32.2 (build 25) and CENTOS 5.8 x86_64 virtuozzo

Any help would be really appreciated...

Please open a ticket using the link in my signature.

[sehh]

The delayed inserts in MySQL have the potential problem to NEVER complete. If the table is busy with other queries then the delayed inserts sit in memory INDEFINITELY. Which also means that if MySQL is shutdown, you loose those queries completely!!!

Just a warning to our cPanel developers

[cpanelnick]

Which also means that if MySQL is shutdown, you loose those queries completely!!!

Thats simply not true. As long as mysql is shutdown properly the delayed inserts will be processed. If you really don't want delayed inserts you could add 'max_delayed_threads=0' to /etc/my.cnf and restart mysql.

[sehh]

cpanelnick, I should have been more specific: "...if MySQL is shutdown uncleanly..."

you do loose all the queries.

[ruzbehraja]

Sorry to sound foolish, but are "slow queries" = "resource intensive queries"?

I think the answer to this whole thread would lie there.

[cpanelnick]

Sorry to sound foolish, but are "slow queries" = "resource intensive queries"?

I think the answer to this whole thread would lie there.

The real question is, "what made the query slow?"

A query can be slow because of the query itself or the available resources. If you are seeing slow queries, you can be sure that there is either the server is overloaded, the query itself is slow, or both.

INSERTS from eximstats are not likely to be very resource intensive as they are all very small chunks of data being inserted. The SELECTS (when you are looking at the data in the UI) can be very intensive if you are trying to query a large volume of information. The catch is that if you have lots of mail traffic and you have a loaded server, the inserts are more likely to show up in the slow query log because its more likely they will happen during a period where the server is loaded due to the sheer volume.

The short version is: If you are seeing lots of slow queries, you should open a ticket if you can't figure out the cause.

[panayot]

Just upgraded to 11.32 on one server. Stable and there is BIG PROBLEM WITH EXIMSTATS.

Up to now we have on all servers eximstats service disabled in Service Manager. But this setting is no longer doing anything even after cpanel restart.

I managed to stop eximstats by killing this process:

/usr/local/cpanel/scripts/import_exim_data

To be sure it really dies, i also did:

mv /usr/sbin/eximstats /root/eximstats-disabled; touch /usr/sbin/eximstats; chattr +i /usr/sbin/eximstats

Now load is back to normal.

I attach Munin grapht showing mysql inserts going crasy the very moment cpanel upgrade finished.

[cpanelnick]

Just upgraded to 11.32 on one server. Stable and there is BIG PROBLEM WITH EXIMSTATS.

Up to now we have on all servers eximstats service disabled in Service Manager. But this setting is no longer doing anything even after cpanel restart.

I managed to stop eximstats by killing this process:

/usr/local/cpanel/scripts/import_exim_data

Now load is back to normal.

import_exim_data is run upon upgrade to 11.32 to bring all the data in your eximstats database up to date to the 11.32 format. This process is going to be very intensive if you have a lot of data to import for a short while during and after the update. Once it completes load should go back to normal levels. Since you had eximstats disabled, it likely had a lot more work to do then on a normal system.

panayot: I is very likely just killing off the import brought things back to normal for you.

If its not working how I am describing above, please do open a ticket using the link in my signature so we can investigate.

[LeetWebH]

I was experiencing this issue... upon doing pretty much everything (disabling eximstats, reset the exim config), I figured out that even with eximstats disabled, exim still inserts into its eximstats table every message. On my server, the smtp table and the messages table was huge. I just emptied those tables.. and baaam load when down, so the delayed inserts and everything... i was getting about 2 spikes per hours

[cpanelnick]

I was experiencing this issue... upon doing pretty much everything (disabling eximstats, reset the exim config), I figured out that even with eximstats disabled, exim still inserts into its eximstats table every message. On my server, the smtp table and the messages table was huge. I just emptied those tables.. and baaam load when down, so the delayed inserts and everything... i was getting about 2 spikes per hours

If you don't mind, could you post the following :

* How many days do you have the data retention set for
* Specs of the server
* Number of emails processed per minute (average)