Answers.Body: <p>I've never used Civi with a dataset that large, but there are some general scaling principles that apply to Civi as with any other system.</p>
  
  <p>The most important one I learned is that work = size * complexity. Okay, that might be a bit of an exaggeration, and in some ways, size doesn't matter very much at all (e.g. you can simply scale your servers to some extent to handle the extra records), but in any real project, there will be exceptions and workarounds and custom bits, and those little bits will be magnified, sometimes exponentially, with the size of your dataset.</p>
  
  <p>For example - suppose you're importing some data that isn't perfect. On a small contact dataset, you might load it into excel and do a search and replace so that USA is replaced by United States. And after doing an import you might have to clean up a few extra records. But that kind of process isn't feasible if you multiply your dataset by 1000.</p>
  
  <p>In other words, scaling is not simply linear. There are some aspects that will scale almost magically, and overall Civi is well-designed to scale and there are certainly Civi installs out there with that kind of size. But with any specific install, especially one that has some kind of migration that includes custom data and idiosyncratic requirements, you're going to get various kinds of unexpected points in your scaling that will require new things to learn, new processes, and quite likely, custom code.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T14:26:41.347Z
Answers.LastActivityDate: 2016-09-21T14:26:41.347Z
Answers.UserDisplayName: Alan Dixon
Answers.Body: <p>Skvare has worked with a number of CiviCRM implementations with data sets of significant size. As has been mentioned, do expect to have a bit of a "learning" curve - especially with the LAMP stack setup. It will require a well-tuned VPS.</p>
  
  <p>Data handling will be another major challenge. First, small variations in addresses, prefix or suffix fields, etc. Standardization will be very important and will need to have pre-defined rules akin to a style guide for designers. That may include corrections of character sets, capitalization, and more. </p>
  
  <p>Next, the built-in GUI import tools are not suitable for large and complex imports. We now have an internal library of methods that use Drupal's batch API with the ability to source the data, manipulate it according to established standards, and then insert it into appropriate fields within CiviCRM including the necessary conditions/dependencies/relationships. </p>
  
  <p>Lastly, the number of contacts in a database is not a good indicator of the above requirements. 250K contact records with sparse related info will be a very different setup (both in ongoing server requirements and during data import) from 250K contact records with 30 years worth of contributions, memberships, event registrations, activities, relationships, groups/tags, etc.</p>
  
  <p>As an option, we've also set up CiviCRM to interface with a 3rd party CRM from which the client wished to migrate off. Using the API we were able to dynamically pull data into CiviCRM upon specific user action (ie login, membership renewal, event registration), have the user validate/correct it, and then store it and mark it in the "old" CRM system as migrated. Depending on the level of user engagement that may be a longer timeline but the approach successfully crowd sources the "elbow grease" responsibility for cleaning up data.</p>
  
  <p>Bottom line - CiviCRM is up to the task; the variable aspects for scaling it to accommodate large record sets is mostly a factor of organizational approach and methodology for data management.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T19:02:20.347Z
Answers.Text: Thanks Peter. It would be very interesting to learn more about the interface to the third party CRM using the API. What is the third party CRM? You used the Civi API or the third-party CRM API?
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T18:52:53.263Z
Answers.LastActivityDate: 2016-09-21T18:52:53.263Z
Answers.UserDisplayName: Peter Petrik
Answers.Body: <p>CiviCRM handles large datasets fine. There are 100+ sites with more than 250k contacts (<a href="https://stats.civicrm.org/?tab=sites" rel="nofollow">https://stats.civicrm.org/?tab=sites</a>). </p>
  
  <p>You will need to ensure that the server used is powerful enough. To save some money, you can improve performance on a smaller server by turning off some things, in particular Administer > Customize Data and Screens > Search Preferences, Include Order By Clause.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T15:57:24.187Z
Answers.Text: what do you recommend as specs for a server running Civi with 250k + contacts?
Answers.UserDisplayName: Joe McLaughlin
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T20:35:05.987Z
Answers.Text: In the Australian greens situation, I can;t remember all the deets off the top of my head, but primary we have 2 separate servers on different hardware one being the database and one being the web. Our database server runs off SSDs
Answers.UserDisplayName: Seamus Lee
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-27T16:54:58.063Z
Answers.Text: The main variables for CiviCRM besides db size are number of concurrent site visitors and number of concurrent staff users who might be doing things like searching, running reports, and especially doing dupe elimination. CiviCRM is single threaded so a request can't really take advantage of multiple CPUs very effectively. MySQL now takes advantage of available multiple cores by default. I think we're running 650k contacts without that many activity records on a 24GB RAM VPS on a  server using SSDs that doesn't restrict this VPS's use of 12 core/24 threads running at a relatively slow 2.4GHz.
Answers.UserDisplayName: Joe Murray
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-09-21T15:31:20.030Z
Answers.LastActivityDate: 2016-09-21T15:31:20.030Z
Answers.UserDisplayName: Joe Murray
Body: <p>I love civi! But mainly i use it for small datasets of sub 10,000</p>
  
  <p>I have the opportunity to suggest its use in an organisation with a voter pool of 1 million+ I know this is an open ended question but does anyone out there have experience of using civi with large datasets. Is there anything BAD I shoudl know!Hopefully I won't get told off for asking the question!</p>
  
  <p>Any thoughts welcome</p>
  
  <p>Thanks</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-09-21T13:42:34.197Z
Comments.Text: Hi Caroline. Jon G, Peter Petrik and a couple other folks did a great workshop at CiviCon in Denver last year about this - see https://www.youtube.com/watch?v=8UaAU71JFTo&list=PLjSU7YfE0E01HW7JPVAI80XDI0sgOSPDs&index=13 . Peter is here at http://civicrm.stackexchange.com/users/32/peter-petrik and Jon is here at http://civicrm.stackexchange.com/users/12/jon-g
Comments.UserDisplayName: Joe McLaughlin
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-09-21T14:20:12.257Z
Comments.Text: Brilliant thanks I will watch the YouTube first 
Comments.UserDisplayName: Caroline B
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-09-21T14:32:02.897Z
Comments.Text: @CarolineB Consider talking to the folks who use it for very large installs - Seamus Lee of the Australian Greens, Eileen for Wikimedia Foundation, Brian Shaughnessy for the New York State Senate.  1M+ instances exist but I wouldn't expect it to just work out of the box!
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-09-21T20:35:38.593Z
Comments.Text: @CarolineB feel free to get in touch my work email is seamus@nsw.greens.org.au.
Comments.UserDisplayName: Seamus Lee
Tags: <database><issues><performance>
Title: Using civi for big data sets (1 million plus)
ThreadText: # Using civi for big data sets (1 million plus)
  
  (Asked by: username_0 on 2016-09-21)
  
  I love civi! But mainly i use it for small datasets of sub 10,000
  
  I have the opportunity to suggest its use in an organisation with a voter pool of 1 million+ I know this is an open ended question but does anyone out there have experience of using civi with large datasets. Is there anything BAD I shoudl know!Hopefully I won't get told off for asking the question!
  
  Any thoughts welcome
  
  Thanks
  
  **Tags:** database, issues, performance
  
  > **Comments:**
  > **username_1**: Hi username_0. Jon G, username_5 username_5 and a couple other folks did a great workshop at CiviCon in Denver last year about this - see https://www.youtube.com/watch?v=8UaAU71JFTo&list=PLjSU7YfE0E01HW7JPVAI80XDI0sgOSPDs&index=13 . username_5 is here at http://civicrm.stackexchange.com/users/32/username_5-username_5 and Jon is here at http://civicrm.stackexchange.com/users/12/jon-g  
  > **username_0**: Brilliant thanks I will watch the YouTube first   
  > **username_2**: @username_0 Consider talking to the folks who use it for very large installs - username_3 of the Australian Greens, Eileen for Wikimedia Foundation, Brian Shaughnessy for the New York State Senate.  1M+ instances exist but I wouldn't expect it to just work out of the box!  
  > **username_3**: @username_0 feel free to get in touch my work email is username_3@nsw.greens.org.au.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_4 on 2016-09-21. Score: 2
  
  I've never used Civi with a dataset that large, but there are some general scaling principles that apply to Civi as with any other system.
  
  The most important one I learned is that work = size \* complexity. Okay, that might be a bit of an exaggeration, and in some ways, size doesn't matter very much at all (e.g. you can simply scale your servers to some extent to handle the extra records), but in any real project, there will be exceptions and workarounds and custom bits, and those little bits will be magnified, sometimes exponentially, with the size of your dataset.
  
  For example - suppose you're importing some data that isn't perfect. On a small contact dataset, you might load it into excel and do a search and replace so that USA is replaced by United States. And after doing an import you might have to clean up a few extra records. But that kind of process isn't feasible if you multiply your dataset by 1000.
  
  In other words, scaling is not simply linear. There are some aspects that will scale almost magically, and overall Civi is well-designed to scale and there are certainly Civi installs out there with that kind of size. But with any specific install, especially one that has some kind of migration that includes custom data and idiosyncratic requirements, you're going to get various kinds of unexpected points in your scaling that will require new things to learn, new processes, and quite likely, custom code.
  
  
  ---
  
  ### Answer by username_5 on 2016-09-21. Score: 2
  
  Skvare has worked with a number of CiviCRM implementations with data sets of significant size. As has been mentioned, do expect to have a bit of a "learning" curve - especially with the LAMP stack setup. It will require a well-tuned VPS.
  
  Data handling will be another major challenge. First, small variations in addresses, prefix or suffix fields, etc. Standardization will be very important and will need to have pre-defined rules akin to a style guide for designers. That may include corrections of character sets, capitalization, and more.
  
  Next, the built-in GUI import tools are not suitable for large and complex imports. We now have an internal library of methods that use Drupal's batch API with the ability to source the data, manipulate it according to established standards, and then insert it into appropriate fields within CiviCRM including the necessary conditions/dependencies/relationships.
  
  Lastly, the number of contacts in a database is not a good indicator of the above requirements. 250K contact records with sparse related info will be a very different setup (both in ongoing server requirements and during data import) from 250K contact records with 30 years worth of contributions, memberships, event registrations, activities, relationships, groups/tags, etc.
  
  As an option, we've also set up CiviCRM to interface with a 3rd party CRM from which the client wished to migrate off. Using the API we were able to dynamically pull data into CiviCRM upon specific user action (ie login, membership renewal, event registration), have the user validate/correct it, and then store it and mark it in the "old" CRM system as migrated. Depending on the level of user engagement that may be a longer timeline but the approach successfully crowd sources the "elbow grease" responsibility for cleaning up data.
  
  Bottom line - CiviCRM is up to the task; the variable aspects for scaling it to accommodate large record sets is mostly a factor of organizational approach and methodology for data management.
  
  > **Comments:**
  > **username_1**: Thanks username_5. It would be very interesting to learn more about the interface to the third party CRM using the API. What is the third party CRM? You used the Civi API or the third-party CRM API?  
  
  ---
  
  ### Answer by username_6 on 2016-09-21. Score: 2
  
  CiviCRM handles large datasets fine. There are 100+ sites with more than 250k contacts (<https://stats.civicrm.org/?tab=sites>).
  
  You will need to ensure that the server used is powerful enough. To save some money, you can improve performance on a smaller server by turning off some things, in particular Administer > Customize Data and Screens > Search Preferences, Include Order By Clause.
  
  > **Comments:**
  > **username_1**: what do you recommend as specs for a server running Civi with 250k + contacts?  
  > **username_3**: In the Australian greens situation, I can;t remember all the deets off the top of my head, but primary we have 2 separate servers on different hardware one being the database and one being the web. Our database server runs off SSDs  
  > **username_6**: The main variables for CiviCRM besides db size are number of concurrent site visitors and number of concurrent staff users who might be doing things like searching, running reports, and especially doing dupe elimination. CiviCRM is single threaded so a request can't really take advantage of multiple CPUs very effectively. MySQL now takes advantage of available multiple cores by default. I think we're running 650k contacts without that many activity records on a 24GB RAM VPS on a  server using SSDs that doesn't restrict this VPS's use of 12 core/24 threads running at a relatively slow 2.4GHz.  
  
  ---
  
  