Hosting requirements for Civi with 700,000 contacts

[davej]

Hi,

Hope this is the appropriate forum for this. Had an enquiry from a potential Civi user having 350,000 contacts and 1M activities currently and aiming to double this. Some crude back-of-envelope calculations suggested...

An existing Civi with 12,000 contacts / 30,000 activities has an InnoDB table size of 700MB.
This new Civi would have 30x as many contacts initially, growing to 60x .
700MB x 30 ~= 20GB
700MB x 60 ~= 40GB

If I were to follow this advice...

"You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces)"
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

...then I'd be setting innodb_buffer_pool_size to over 40GB to accommodate the projected data growth, so I'd be looking at a database server with at least 48GB of RAM! This seems excessive.

However this post describes running Civi with 500,000 contacts on a 1-2GB server:
http://wiki.civicrm.org/confluence/display/CRMDOC33/Large+Canadian+Political+Database+implementation

I guess it's a case of either will work but keeping the whole table space in RAM will yield much better performance. There would be about 80 staff using the full Civi UI.

I'd value people's experiences of hosting Civi dbs of this sort of size.

Thanks,

Dave J

[xcf33]

Very interesting topic,

We have clients with around 250,000 contacts and probably 1 million activities, etc. We currently have a cloud based database solution, it works OK but we have issues with the dreaded MySQL has gone away error from time to time.

We are looking to build some kind of database cluster that can handle the increased demand for database storage and performance as well as take control of the configurations. Our clients are also are looking for Civi database that can handle 500 k to 1 million contacts.


Cheers!

[stantale]

The back of the envelope calculation is about right, and we've followed similar procedures for scaling RAM as you outline for 50+ instances of 200,000 contacts each,  running off the same database server. Our footprint fits within 32GB of RAM + a little bit of swapping.

Of course "more is better", and swapping I/O is "bad", but it is definitely doable with a lot less RAM. We found that things ran quite well on 4GB for record lookup/add/edit and searching was smooth unless unreasonable criteria were given. However certain tasks (merging records, exporting data) would cause big cpu/IO spikes as well as locking which impacted everyone else's usage so the perception was that 4GB is too little. In actuality, only a few culprit scenarios end up killing the joy for everyone.

Mostly tmp table settings helped alleviate a lot of problems  (credit to Brian Shaughnessy spotting this). Tuning the myqsl/innodb parameters to get things running smoothly is key.

One note is that every implementation is different:

- number/size of records (notes, activities, tags, custom fields etc.)
- Usage patterns (case tracking vs. accepting donations vs. slicing/dicing groups and using civiMail)
- interface issues (ajax calls, too many items in dropdowns) aren't database problems per se, but browser choking amounts to the same thing for the end users.

Regarding deduping, there has been a lot of awesome work in the latest release that makes for much more efficient processing and it scales really well, but it does depend on your rules/weights and of course we've found one set of rules that still causes our db some grief ;-) - we're investigating this right now.

One non-mysql recommendation: Lobo did some great memcache work a few releases back - all the semi-static data like option values etc get cached and drive your queries/sec down tremendously. We've had much snappier front end response with this. Opening civicrm.settings.php file and changing the MEMCACHE value from 0 to 1 is all it takes! (not quite: you do also need a memcache server lying around but it's an easy setup.)

We've also written a few custom tools like import/export to bypass one-by-one processing. Of course you also loose civicrm's integrity checks and business rules so it requires special care.

hope this helps!

-s

[davej]

The back of the envelope calculation is about right, and we've followed similar procedures for scaling RAM as you outline for 50+ instances of 200,000 contacts each,  running off the same database server. Our footprint fits within 32GB of RAM + a little bit of swapping.

Interesting, so that's:
(1) 50 x 200,000 = 10M contacts with 32GB RAM = 3.2GB/million contacts

Whereas for the existing Civi that I mentioned:
(2) 12,000 contacts, uses 700M table space = 58GB/million contacts

Of course this isn't comparing like with like, as (1) is physical RAM and (2) is table space, and as you say, the amount of data per contact can vary greatly. D'you know what your total InnoDB table space size is?

Mostly tmp table settings helped alleviate a lot of problems

Care to share your findings on this?

Thanks for your helpful post,

Dave

[JoeMurray]

It's not valid to do this computation:
(1) 50 x 200,000 = 10M contacts with 32GB RAM = 3.2GB/million contacts

The issue is that in searches there may be NxN or even NxNxNxN kinds of lookups. So demands for temp tables and CPU are not linear with size of db.

[JoeMurray]

One non-mysql recommendation: Lobo did some great memcache work a few releases back - all the semi-static data like option values etc get cached and drive your queries/sec down tremendously. We've had much snappier front end response with this. Opening civicrm.settings.php file and changing the MEMCACHE value from 0 to 1 is all it takes! (not quite: you do also need a memcache server lying around but it's an easy setup.)

stantale: how are you configuring your memcache server(s)? I understand that it can be good to use RAM on webheads, or alternatively to put in servers just for memcache.

I'm trying to size a deployment with about 100 - 200 concurrent users managing a database of 60k donors and about 4M donations (750k donations/year). I'm thinking of using a 4GB Apache server with 500k of memcache RAM, and a 48GB or 64GB MySQL backend, dual quad-core. I'm worried we may need to put in a cluster of 3 or more Apache Servers, and that I may have over-spec'd the db server. How many concurrent users do you have, and how many webheads?

Mostly tmp table settings helped alleviate a lot of problems  (credit to Brian Shaughnessy spotting this). Tuning the myqsl/innodb parameters to get things running smoothly is key.

Like davej, it would be interesting to see which ones you tuned, and how you figured out how to set the parameters.

[fen]

Just discovered this topic.  I think the general goal of 6GB RAM per 100K contacts sounds about right, and I know that there are many factors (complexity of queries, etc.) but finding a good set of baseline hosting allocation guidelines would be very useful.

Other considerations: for a moderate sized site (say, 100K contacts) would it be wise to keep the CiviCRM DB in a separate VPS or server from the Drupal database and Apache?  Or can the Drupal and CiviCRM DBs play nicely together?

Joe - did you turn up - or develop - additional guidelines?

Thanks,
=Fen