CiviCRM Community Forums (archive)

On a site that makes very heavy use of CiviMail, I've discovered that the the table civicrm_mailing_event_queue can become very, very large, so much so that it is affecting our ability to use backups.  Currently, the table has *millions* of records.  This means that it's hard for a developer to load the DB on a lap top (and even on a fast server, a load is now taking close to an hour and a half).

I suspect that many of the records are for mailings done months ago, and that we no longer really need them around.  Does anyone have advice as to how best to clean this up.  I haven't worked out the dependencies of the different mailing tables, although I'd expect that events would be pretty far "downstream".

Any reason I can't just "work backwards" from the event types (bounce, etc.) once I can identify the event_queue_id?

I don't think that I could get through that many records via any of the PHP calls in reasonable (or even "reasonably finite") time, and I suspect I can go from job_id ==> event_queue_id ==> records.

This "looks" like it would work, at least judging by the way the keys are set up.

I posted a similar issue earlier about how time consuming it is to even delete individual mailings.  We have many people doing mailings and the queue of old mailings keeps building and someone (me), has to go in and delete one at a time.   Currently we have 217 mailings in the queue, which is not as many as it sounds like, because each real mailing typically has 2 or 3 test runs before the final run.
http://forum.civicrm.org/index.php/topic,5060.0.html

I've done a fair bit of research on this, and have a reasonable (and reasonably fast) solution.

The key thing to do, assuming that you may need the information in future, is to create archive tables.  MySQL has an engine type Archive, which are tables that you can use for long term data storage.  The tables are very easy to write to, but do not have indexes, so they are not useful for anything you need to get at fast.  But for archival purposes, they work well.

I'll give the example of 1.9 activity history first, since the table has very simple structure.  The details will differ for different tables and for different versions of CiviCRM, but once you understand the trick, it's easy to extend this.

The steps are:

First, reate a new database to hold your archive tables.  This will let you backup more efficiently.

Then, create a framework for the archive table from the existing table:
create new_db.old_activity_history like old_db.civicrm_activity_history;
Since the Archive engine does not know about indexes, you need to delete all the indexes from the new table format.  For 1.9 activity history, this looks like this:
use new_db;
-- the primary key is also auto_increment, so you need to remove
-- this before you can proceed
alter table  old_activity_history modify id int(10) unsigned;

-- remove the primary key itself
alter table old_activity_history drop PRIMARY KEY;

-- remove the regular indexes
alter table old_activity_history drop index index_entity;
alter table old_activity_history drop index index_activity;

-- and the foreign key indexes as well
alter table old_activity_history drop index FK_civicrm_activity_history_relationship_id;
alter table old_activity_history drop index FK_civicrm_activity_history_group_id;

Now you have an InnoDB table with no indexes.  You can convert it to an Archive table like this:
alter table old_activity_history engine = archive;
Now fill the new table with the old data.  Get some coffee, this will take a while:
insert into old_activity_history select * from old_db.civicrm_activity_history;
Now a minor trick.  InnoDB will forget what's the next ID for the old table if you delete everything.  And you want to delete everything -- truncation is at least hundreds of times faster than any kind of DELETE statement.  So save away the maximum ID in the old table.  I did it this way:
select max(id) from old_db.civicrm_activity_history into @ah_max_id;
Now you can finally purge the old data:
truncate old_db.civicrm_activity_history;
Finally, restore the max ID record so that auto_increment will work right going forward:
insert into old_db.civicrm_activity_history select * from old_activity_history where id = @ah_max_id;

As long and as nasty is this is, it's simpler than doing CiviMail's event_queue, since there are dependencies with a variety of tables.  But the basic procedure works the same way.

some more thoughts on this (after reading this blog post: http://github.com/blog/328-database-troubles). At some point soon, i think civimail will need a batch archive and delete facility (i.e. ability to handle this for multiple mailings at a time). So here is the rough idea:

The archive feature will install mysql tables of type archive in the database. Basically this will include most of the CiviMail tables which hold mailing / event information. To reduce the number of tables, we can potentially collapse all the civicrm_mailing_event_queue_* table into one archive table (all these tables have a common set of 4 fields). The archive code will then move all the entries to the relevant tables on a periodic basis based on some frequency parameter. I suspect most sites will not need archival features for quite some time.

Another easier option might be to wait for an official mysql 5.1 release and use the partitioning feature: http://dev.mysql.com/tech-resources/articles/performance-partitioning.html

If you are interested in working on this or sponsoring this feature, let us know

lobo

The approach used for activity history does work for the CiviMail event tables.  The main issue I've found isn't speed (the archive table approach is actually reasonably fast, even for millions of records), but making sure you save the right "stuff".

Part of what the CiviMail event tables do is "explain" when and why people opt out of email or unsubscribe from lists.  For some installations, this is valuable information, since you can audit with it.  But that's a very small fraction of the events -- for the install I'm looking at, about 0.1% of the records.  So if you can lop off the "opened" and the "delivered" tables, and any event_queue record that isn't needed for the remaining event records, you can reduce the size of those tables by a factor of about 1000.

Mostly, this needs to be a SQL only solution, because you never want to load most of the records in the system, and CiviCRM's system wants to load everything so it can use its object wrappers.  But a PHP wrapper for the script would make it a lot easier to "rewrite" the SQL, since MySQL's dialect doesn't let you parameterize things like table names and database names:  @my_value will substitute for a value, but @my_table or @my_database will cause a syntax error.

Has there been any progress on this since Rob's last post?

Thanks - glad someone else recognizes the issue.  I don't think it will take very long to empty the tables, but with related tables it might stop me or maybe have unintended consequences, so thought I'd ask around.

We don't have that big a database, only 6,400 contacts.  However, with many people doing mailings, I'll bet we have more items in the CiviMail event queue.

Thanks - Dave