CiviCRM and Voter Files

Recently went through an import of 2.4 million voter file records into CiviCRM. Went through a lot of testing, sharing some preliminary information here on things we discovered. The 'we' used in this document refers to me and other resources within my company, Trellon. Working with recent releases of CiviCRM 1.4.

First, default CiviCRM distros are not suitable for working with massive numbers of records. After importing the 2.4 million records, and before adding any custom fields, the main and advanced search interfaces became unusable. Trying to save information into a contact record became nearly impossible. The reasons for this were myriad and included database design, data access queries, as well as the hardware we were working with initially.

So, addressing these salient needs, we took the following steps -

1) Optimize the data model - We removed all foreign key restraints and changed all unique indexes to regular indexes to begin with. This lead to a speed increase using the default InnoDB tables and cleared the way to change the table types to memory resident or ndb clustered. More on the eventual deployment follows...

We also added numerous indexes throughout the database. For individuals, we indexed first name, last name, address information, and a variety of other fields specific to data access objects within the system.

2) Customize the data access interface - The default queries for accessing individual contact records in CiviCRM work well enough, but the default search interfaces blow up under scale. In some cases, we ended up with queries that would kill the server without load. To address this, we wrote custom search interfaces specific to the needs of the client, using name, address, action fields, contributions, and other fields. We tied these search interfaces into specific indexes we created in the database.

Writing custom search interfaces required us to learn smarty, which has advantages and disadvantages like all other development frameworks. DLobo was a huge help in getting us up to speed.

3) Throw Hardware at it - We put the database itself on a dual xeon 3.0 ghz machine with 4 GB of RAM. This lead to measurable performance increases over a single processor box with 512 MB of RAM. In terms of inserting records, we used a custom script hitting the CiviCRM API. On the smaller box, it took about 30 seconds per record to import new contact records after about 50k records were imported. On the bigger box, that number went down to about 3 seconds. For the majority of the custom searches we had written, there was about an 80% performance increase. This was with minimal changes to my.cnf.

One thing the hardware upgrades made possble was the consideration of moving the CiviCRM installation into memory resident tables by changing the table types. We did not take this step for the production deployment of the database, given the fact we had a known stable installation. I did put several tables into memory on a development server, however, and found it was not as productive as I would have hoped. While there were some performance gains, temp tables were still being written to disk on the larger queries. Perhaps there is a way to force mysql to do all temp tables in memory, but I did not find it. Another drawback was simply the size of the database when it is in memory - it took about 2.8 GB on the server to run all CiviCRM database tables in memory. Under load (like with 1000s of people hitting it) performance would degrade as mysql shifts to using disk and swap space when available memory runs out.

More specific details to follow as I process all the upsizing insanity. This model, operating on a large cluster, could result in a highly scalable system.

M