CiviCRM Community Forums (archive)

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

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

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.

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