Author Topic: Cleaning up the CiviMail event queue (Read 6870 times)

Dennis Gray · July 27, 2009, 06:37:09 pm

We have nearly 100,000 contacts with many people doing bulk mailings.

civicrm_activity_history 6,405,659
civicrm_mailing_event_delivered 5,896,612
civicrm_mailing_event_opened 1,913,200
civicrm_mailing_event_queue 5,933,206

I have culled the activity_history but have not started on the others yet. I am going to write script to do this.

Denver Dave · July 28, 2009, 01:35:42 pm

Yikes - I guess I'm glad to have company.

By culled, do you mean deleted some records from the table or was there more involved? i.e. related records in other tables?

Dennis Gray · July 28, 2009, 02:50:59 pm

I used the steps above to delete records from the activity history. If relationships were broken, so be it. My goal is to be able to create a development environment that I can carry around on my laptop, which is different than reducing the size of the production data.

I am not too concerned with referential integrity but will still give that consideration when I finish up the scripts. I have to take a closer look at the data model.

bcobin · January 26, 2011, 04:04:45 am

Has there been any additional activity on this issue? I, too, am running into this with a client that frequently uses CiviMail - the table bloat makes backups and updates more arduous than it should be.

P.S. - Love the new forum look, BTW... cool.

Donald Lobo · January 26, 2011, 08:21:50 am

No.

I suspect this needs to be done sometime in the near future. Might make a good Make It Happen

lobo

Denver Dave · January 26, 2011, 08:50:17 am

We are on an older version 1.9 and I know the newer versions are better, but we've pretty much given up on keeping the civimail event queue clear - we now have over a 1000 in the queue. We seem to be on the strategy of running the civicrm system until it drops. For 9,000 contacts, we are now over 3 million records.

In order to convert or reload to the current version, we'll likely have to trim, let alone reload from backups if necessary.

Dave

bcobin · January 26, 2011, 03:13:26 pm

Quote

We seem to be on the strategy of running the civicrm system until it drops.

Oh yeah. It only became clear to me somewhat recently, when a client site started crashing.

In addition to the server disk filling up with iNodes from the CiviMail processing, the civicrm_mailing_event_bounce table had ballooned to over 5 GB! With the help of Piotr (thank you, Piotr!), we came up with several cron scripts to keep things down to a somewhat manageable size; it was when I took a look today at at the mailing_event_queue that I saw that there's more to be done.

For convenience's sake, I'm going to post the cron scripts here in the hopes they may be helpful. I'm reticent to put myself out as an "expert" here; somebody more qualified than me should give them the once-over before adding them to the documentation.

But make no mistake about it: If somebody uses CiviMail "out of the box" to send frequent emails to a large mailing list, they'll almost definitely run into major problems over time unless they deal with the "bloat" issue.

These are all using cPanel - most with self-delete:

First, the hourly cron:

Code: [Select]

wget --delete-after 'http://[site]/cron.php' >/dev/null 2>&1
Then, the Civi mailing cron - every five minutes:

Code: [Select]

wget --delete-after -O - -q -t 1 --post-data='name=[username]&pass=[password]&key=[site_key]' http://[site]/sites/all/modules/civicrm/bin/civimail.cronjob.php >/dev/null 2>&1

Now, the CiviMailProcessor - 11 and 41 minutes after each hour:

Code: [Select]

wget --delete-after 'http://[site]/sites/all/modules/civicrm/bin/EmailProcessor.php?name=[username]&pass=[password]&key=[site_key]' >/dev/null 2>&1

That's all standard. Now here are the cleanup scripts:

First, clean up bounce messages older than three days old - I run this once a day every day at 3 AM:

Code: [Select]

find /home/[site_account]/public_html/sites/default/files/civicrm/custom/CiviMail.processed -mtime +3 -exec rm -f {} \;
And now, truncation of the civicrm_mailing_event_bounce table; again, anything over three days old. I run this once a day at 4 AM:

Code: [Select]

mysql -u [site_account]_trunk -p[password - from host, not your password] [civicrm_database] -e "DELETE FROM civicrm_mailing_event_bounce WHERE time_stamp < '`date --date='3 days ago' --rfc-3339=date`'"

Note there's no space between -p and the password; in any event, this password will come from the host - it's system-level stuff. In fact, you'll most likely need to get your host to set this up for you anyway, so let them help you out here.

Again, if you're using CiviMail a lot and don't deal with this, you're asking for trouble in the long run.

I hope this can be incorporated in the documentation somewhere, and possibly, it might create a direction to solve the event_queue table issue, which seems to be the last major contributor to DB bloat, as least in my case. Better yet, it sure would be nice if some "bloat management" was built into the system somewhere; it's definitely needed.

Anyway, hope this helps - back to CiviMail adventures!

torenware · January 26, 2011, 05:51:15 pm

In any case, I do have scripts that work well to delete both the civicrm_mailing_event_* related fields, and to delete activity history records that the bulk mailing system generates. They are designed to work under CiviCRM 1.9, and will likely *not* work on any later system.

You'll need to edit these scripts to add in the names of your databases (MySQL doesn't let you put these into parameters unfortunately), and for Lir's Sake, you'll need to run this a couple times on a test system to get a sense of what the scripts do to your data. But I've been using versions of these scripts for a year and a half, and find they work well.

Once you have things set up to run on the test system, these scripts run on production reasonably fast.

The scripts are:

archive-mailing-tabs.sql.txt -- slims down the CiviMail tables
archive-mailing-tabs.sql.txt -- removes 1.9 activity history; read carefully to make sure it does not delete more types of activities than you want.
kill-archives.sql.txt -- removes tables that the scripts create, if you don't want to delete the whole archiving database the scripts use

Cheers,
Rob

Rob Thorne
Torenware Networks

ken · January 26, 2011, 06:03:07 pm

@bcobin,

Thanks for the post.

I understand you're deleting bounce emails from a Maildir format directory tree. Some possible issues come to mind...

The CiviMail processing currently DELETEs but does not EXPUNGE messages. Perhaps when that is fixed in 3.4 the problem you are facing will be ameliorated - see CRM-7356 [this won't affect the messages in the 'processed' sub-folder, but will clean up the 'deleted' messages left loitering in the Inbox]
You could add '-type f' to the 'find' command to avoid errors when trying to delete aged directories
You could add '-name "pattern"' to the 'find' command to avoid deleting IMAP indexes
Is there a risk of corrupting the IMAP index by deleting files and not updating the index?

I use Webmin to manage my server, and Usermin to manage email accounts, and that allows me to schedule jobs that regularly clean out bounce messages. Perhaps cPanel offers something similar?

Postfix allows you to set up jobs that automatically flush directories of aged messages having certain names (eg, Trash). Perhaps your MTA does the same?

These approaches seem safer to me than simply deleting files,

Ken

pdx_kid · January 28, 2011, 09:45:21 am

Great that this issue is being tackled.

This was a major problem when I moved CiviCRM/Drupal to a new server: millions of records were in the dB. First try took almost 16 hours to download, upload and restore. Dumping records, clearing cache tables and using server-to-server transfers cut this down to abt 30 minutes.

A function to archive these records would have been very handy.

bcobin · January 29, 2011, 12:28:21 pm

Great to see contributions to this thread from people who really know what they're talking about - I suggest that formulation of a "best practices" policy with either proper cron commands or (better yet) Civi scripts that manage necessary cleanup should be high-priority.

Without dealing with the "bloat" issue, CiviMail is potentially toxic in the long term - having a site crash on account of this is no fun at all, believe me.

Thanks to all you "pros" out here - looking forward to a proper solution!

Donald Lobo · January 29, 2011, 04:47:22 pm

a few thoughts, comments and my 2 cents worth

1. IMO, using words like "toxic" does not really help anyone involved

2. As any open source project, the sustainability and the strength of the ecosystem depends on the community and their contributions. As such its great to see relevant comments and code from various users

3. High priority items are best implemented with patches and/or someone taking the lead and helping harness and directing the energy of the involved participants. This is the best course of action to get a "proper" solution in place. It does not happen magically

4. The wiki / docs are community creatable/editable. Please do start a "best practices" document for managing a CiviMail install. Other folks will chime in with tips etc over a period of time.

5. IMO, CiviCRM is still a few releases/years away from being a complete self-managed solution. For larger installs, I do feel that having a good sysadmin is quite important to help manage, optimize and maintain the system.

lobo

bcobin · January 29, 2011, 05:29:06 pm

Quote

1. IMO, using words like "toxic" does not really help anyone involved.

Terribly sorry; I did not mean to offend. I only used the word to indicate the severity of the issue. It came to as a complete surprise when I high-visibility site I did started going down repeatedly. It was completely unexpected.

Quote

3. High priority items are best implemented with patches and/or someone taking the lead and helping harness and directing the energy of the involved participants. This is the best course of action to get a "proper" solution in place. It does not happen magically.

Indeed. It was others who have a greater skill set than I do who came up with these scripts, which, as I can see from the comments of other on this thread may not be the best way to handle the "bloat" issue.

Quote

4. The wiki / docs are community creatable/editable. Please do start a "best practices" document for managing a CiviMail install. Other folks will chime in with tips etc over a period of time.

I hardly think I'm qualified here; I was only suggesting that one of the other participants on the thread take the lead in starting a "best practices" document when it comes to CiviMail - I do believe it's very important. Hence my response to what was a fairly old thread in an effort to perhaps help others who may have run into the issue - it took many days of work to get this far in keeping CiviMail sites running smoothly and anybody is welcome to what I've discovered - with a lot of help.

Heavens - I certainly didn't want to upset anybody! (You're all too important to me.) Oh, dear.

netvigator · August 18, 2011, 07:52:54 pm

Lobo wrote:

Quote

there will be millions of event_queue_id's and just thousands of job id's, so a more efficient approach might be to do a set of mass deletions using the job_id, which you can retrieve from the civicrm_mailing_job table

We have millions of old email records, and it is hard to imagine we would ever want or need to access them, so doing the archive thing is more complicated than what I think we need to do -- just delete the old records. For now, as a test, we are trying deleting email records from 2008 and earlier (on a copy of our live system).

Sorry, I do not know php. The needs of our organization would be served by a sql shell script that is run periodically, say yearly.

We are on CiviCRM 1.9. Here is what I came up with:

Code: [Select]


use <database_name>;

create temporary table temp_mail_job_ids 
select id as job_id, mailing_id
from civicrm_mailing_job
where year( start_date ) < 2009 ;

create index temp_job_id on temp_mail_job_ids ( job_id ) ;
create index temp_mailing_id on temp_mail_job_ids ( mailing_id ) ;

delete from civicrm_mailing_trackable_url 
where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_group 
where mailing_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_event_unsubscribe
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing_event_trackable_url_open
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing_event_reply
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing_event_queue
where job_id in ( select job_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_event_opened
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing_event_delivered
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing_event_bounce
where year( time_stamp ) < 2009 ;

delete from civicrm_mailing
where id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_activity_history 
where activity_type = 'Email sent' and
      activity_id in ( select mailing_id from temp_mail_job_ids ) ;

delete from civicrm_mailing_job
where id in ( select job_id from temp_mail_job_ids ) ;

optimize table civicrm_activity_history ;
optimize table civicrm_mailing_event_delivered ;
optimize table civicrm_mailing_event_bounce ;
optimize table civicrm_mailing ;
optimize table civicrm_mailing_event_opened ;
optimize table civicrm_mailing_event_queue ;
optimize table civicrm_mailing_event_reply ;
optimize table civicrm_mailing_event_trackable_url_open ;
optimize table civicrm_mailing_job civicrm_mailing_trackable_url ;

I did not bother to delete records from tables with zero or just a few rows.

Denver Dave could adapt this to delete records older than a certain date, or older than a number of days old.

Does anyone see a problem with this approach?

Denver Dave · August 18, 2011, 09:59:03 pm

Thanks for the post. We're going to have to do something, hopefully as we convert to the new version.

Dave

CiviCRM Community Forums (archive)

News:

Author Topic: Cleaning up the CiviMail event queue (Read 6870 times)

Dennis Gray

Re: Cleaning up the CiviMail event queue

Denver Dave

Re: Cleaning up the CiviMail event queue

Dennis Gray

Re: Cleaning up the CiviMail event queue

bcobin

Re: Cleaning up the CiviMail event queue

Donald Lobo

Re: Cleaning up the CiviMail event queue

Denver Dave

Re: Cleaning up the CiviMail event queue

bcobin

Re: Cleaning up the CiviMail event queue

torenware

Re: Cleaning up the CiviMail event queue

ken

Re: Cleaning up the CiviMail event queue

pdx_kid

Re: Cleaning up the CiviMail event queue

bcobin

Re: Cleaning up the CiviMail event queue

Donald Lobo

Re: Cleaning up the CiviMail event queue

bcobin

Re: Cleaning up the CiviMail event queue

netvigator

Re: Cleaning up the CiviMail event queue

Denver Dave

Re: Cleaning up the CiviMail event queue