Answers.Body: <p>Seems slower than I'd expect, you'll want to figure out where your bottleneck(s) is(are).</p>
  <p>Most of machine resource use is likely in Disk I/O from mysql (to create the new records, especially with the custom fields). You could use a standard resource monitoring program to confirm that. If that's the case, getting yourself SSD disks is likely your best way to speed things up. You might be getting short on memory for mysql as well (though importing contacts isn't a hugely memory intensive process and caches are almost counter productive), depending on what else is using up memory on your machine.</p>
  <p>I do see that you've got a couple of extensions that might be generating unnecessary work for your machine (profile and member sync) - those extensions might be kicking in for each contact created in order to see if they need to do something. You could try disabling them (and any other ones you can) and see if that makes a difference.</p>
  <blockquote>
  <p>&quot;Huge civicrm-tmp files get created, but nothing resolves unless it's tiny&quot;</p>
  </blockquote>
  <p>Not sure how to interpret this, that does suggest something is not optimal.</p>
  <p>On a different note, and not what you're asking, but in your position, I'd be tempted to try it on a different machine, perhaps some kind of large-instance db that you could use on a temporary basis to do the import, which is a specialty task that uses different resources from actually running a civicrm db in production. The 40 custom fields is a bit of a red flag for both architecture and performance. 2.7 million records with 40 custom fields is a bit of a mismatch with a home machine.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-06T13:09:45.823Z
Answers.Text: Thanks, Alan Dixon, for your help. Since small (5,000rows selected from 4.7million) SELECT statements go through with little delay, yet larger (20,000) ones lock up, I think that there's a memory limit being exceeded. Does that make sense? On the huge civicrm-tmp-... tables created - these have the same number of rows as what I hope to import. This suggests to me that a great part of the work has been done - getting these 20,000 rows from the 4.7 million - and that the hang-up is in writing these into the civicrm fields somehow. Does that make sense?
Answers.UserDisplayName: Brian Cady
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-06T15:31:28.503Z
Answers.Text: Alan Dixon, I disabled all wordpress plugins but CiviCRM. This did not change behavior.
Answers.UserDisplayName: Brian Cady
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-07T00:48:33.140Z
Answers.Text: I don't have any experience with this kind of import, so take this FWIIW. But here are tips for optimizing mysql bulk data loading in general that might be helpful: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html
Answers.UserDisplayName: twowheeler
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-07T15:39:36.787Z
Answers.Text: Yes to your diagnosis of the civicrm-tmp tables being related to memory. But just throwing memory in your computer may not resolve it. Mysql will generate tmp tables to split an operation into two bits when it doesn't have enough memory to do it in one step. But 'enough memory' is also about 'enough memory allocated to a specific task', so you need to worry about various mysql configuration, which gets very tricky and prone to counter-productive futzing.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-07T15:48:12.013Z
Answers.Text: If you do want to try tuning your mysql, I'd suggest using a tuner like this one: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
  It'll read your mysql logs and make suggestions of changes to your configuration file, e.g. "innodb_flush_log_at_trx_commit = 2". Your configuration for running a civicrm in production is very different from what you want for a massive import like this.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-02-01T18:53:34.677Z
Answers.LastActivityDate: 2022-02-07T15:45:22.923Z
Answers.LastEditDate: 2022-02-07T15:45:22.923Z
Answers.UserDisplayName: Alan Dixon
Body: <p>I'm testing CiviCRM on a home, not-web-linked, machine. Hence I haven't set up  a way to get admin-directed emails.
  Importing less than 20,000 records with less than 40 mostly-custom fields each has taken more than an hour.
  I'm importing from a db within the folder hosting this installation, using import contacts via SQL. I've successfully imported ~5,000 records, and with extreme patience, about 20,000 more. Goal: 2.7x million records. I expanded upload size and memory limits in wordpress using .htaccess and php.ini.
  I've tried multiple times to import data - only insignificant test portions work. Huge civicrm-tmp files get created, but nothing resolves unless it's tiny. I have to delete the civicrm-tmp file through phpmyadmin and restart apache2 to get the website to show up again after aborting import.
  LAMP stack
  Ubuntu 20.04 LTS
  Apache
  Mariadb
  PHP 7.4.3
  Wordpress 5.9
  CiviCRM 5.45.1
  &quot;All extensions are up-to-date:</p>
  <pre><code>CKEditor4: Version 5.45.1
  Contribution cancel actions: Version 5.45.1
  Custom search framework: Version 5.45.1
  Event Cart: Version 5.45.1
  Financial ACLs: Version 5.45.1
  FlexMailer: Version 5.45.1
  Form Builder: Version 5.45.1
  Form Code Editor: Version 5.45.1
  Form Core: Version 5.45.1
  reCAPTCHA: Version 5.45.1
  Search Kit: Version 5.45.1
  Sequential credit notes: Version 5.45.1
  Theme: Greenwich: Version 5.45.1 &quot;
  </code></pre>
  <p>edit: processor type: Intel® Pentium(R) CPU G850 @ 2.90GHz × 2
  250 Gb HD, 7.7 Gb RAM, graphics: Intel® HD Graphics 2000 (SNB GT1)
  <a href="https://i.sstatic.net/PUskQ.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/PUskQ.png" alt="Plug-ins present" /></a></p>
  <p>Edit 2022Feb6 I should have noted that the records are being chosen from a table with 4.7 million rows.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-01-31T02:28:22.457Z
Comments.Text: Can you add to your question the specs of the machine you are running this on? Processor type, amount of RAM and disk space.
Comments.UserDisplayName: twowheeler
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-01-31T18:34:55.513Z
Comments.Text: Curiously, without WP Data Access plugin, 'systemctl restart apache2' uniquely  resulted in info in the import progress bar '13,950 of 19,867 done 96 min 39sec to go'.
Comments.UserDisplayName: Brian Cady
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-01-31T18:56:32.453Z
Comments.Text: Can you also explain how you are doing the import? Not sure what this means: "I'm importing from a db within the folder hosting this installation, using import contacts via SQL." So it's not through the front end menu?
Comments.UserDisplayName: twowheeler
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-01-31T21:44:06.733Z
Comments.Text: Thanks twowheeler for responding: I'm using the front end menu (drop downmenu 'contacts' to 'import contacts') with the SQL (versus .csv) option chosen. I've previously brought the table over to the db hosting wordpress, so I'm just IDing db.table. I wrote SELECT * FROM db.table WHERE PartyAffiliation = 'U' and StateRepDistrict = '61' in step 1 of the four part process.
Comments.UserDisplayName: Brian Cady
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-02-06T14:21:35.693Z
Comments.Text: 'Systemctl restart mysql' resulted a second time in information on the import status bar, which is usually blank during these import troubles. (Screenshot available). '36% completed -   8150 of 22411 records - 161 minutes 37 seconds remaining'
Comments.UserDisplayName: Brian Cady
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-02-06T17:06:47.577Z
Comments.Text: In the process of importing of large data amounts, Step 1 "choose data source' succeeds, and a civicrm-tmp table is created with all the fields within the number of records selected, but then on step 2 'matching fields' things bog down.
Comments.UserDisplayName: Brian Cady
Tags: <import>
Title: Why is this CiviCRM with wordpress so slow to import data?
ThreadText: # Why is this CiviCRM with wordpress so slow to import data?
  
  (Asked by: username_0 on 2022-01-30)
  
  I'm testing CiviCRM on a home, not-web-linked, machine. Hence I haven't set up  a way to get admin-directed emails.
  Importing less than 20,000 records with less than 40 mostly-custom fields each has taken more than an hour.
  I'm importing from a db within the folder hosting this installation, using import contacts via SQL. I've successfully imported ~5,000 records, and with extreme patience, about 20,000 more. Goal: 2.7x million records. I expanded upload size and memory limits in wordpress using .htaccess and php.ini.
  I've tried multiple times to import data - only insignificant test portions work. Huge civicrm-tmp files get created, but nothing resolves unless it's tiny. I have to delete the civicrm-tmp file through phpmyadmin and restart apache2 to get the website to show up again after aborting import.
  LAMP stack
  Ubuntu 20.04 LTS
  Apache
  Mariadb
  PHP 7.4.3
  Wordpress 5.9
  CiviCRM 5.45.1
  "All extensions are up-to-date:
  CKEditor4: Version 5.45.1
  Contribution cancel actions: Version 5.45.1
  Custom search framework: Version 5.45.1
  Event Cart: Version 5.45.1
  Financial ACLs: Version 5.45.1
  FlexMailer: Version 5.45.1
  Form Builder: Version 5.45.1
  Form Code Editor: Version 5.45.1
  Form Core: Version 5.45.1
  reCAPTCHA: Version 5.45.1
  Search Kit: Version 5.45.1
  Sequential credit notes: Version 5.45.1
  Theme: Greenwich: Version 5.45.1 "
  
  edit: processor type: Intel® Pentium(R) CPU G850 @ 2.90GHz × 2
  250 Gb HD, 7.7 Gb RAM, graphics: Intel® HD Graphics 2000 (SNB GT1)
  
  Edit 2022Feb6 I should have noted that the records are being chosen from a table with 4.7 million rows.
  
  **Tags:** import
  
  > **Comments:**
  > **username_1**: Can you add to your question the specs of the machine you are running this on? Processor type, amount of RAM and disk space.  
  > **username_0**: Curiously, without WP Data Access plugin, 'systemctl restart apache2' uniquely  resulted in info in the import progress bar '13,950 of 19,867 done 96 min 39sec to go'.  
  > **username_1**: Can you also explain how you are doing the import? Not sure what this means: "I'm importing from a db within the folder hosting this installation, using import contacts via SQL." So it's not through the front end menu?  
  > **username_0**: Thanks username_1 for responding: I'm using the front end menu (drop downmenu 'contacts' to 'import contacts') with the SQL (versus .csv) option chosen. I've previously brought the table over to the db hosting wordpress, so I'm just IDing db.table. I wrote SELECT * FROM db.table WHERE PartyAffiliation = 'U' and StateRepDistrict = '61' in step 1 of the four part process.  
  > **username_0**: 'Systemctl restart mysql' resulted a second time in information on the import status bar, which is usually blank during these import troubles. (Screenshot available). '36% completed -   8150 of 22411 records - 161 minutes 37 seconds remaining'  
  > **username_0**: In the process of importing of large data amounts, Step 1 "choose data source' succeeds, and a civicrm-tmp table is created with all the fields within the number of records selected, but then on step 2 'matching fields' things bog down.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2022-02-01. Score: 1
  
  Seems slower than I'd expect, you'll want to figure out where your bottleneck(s) is(are).
  
  Most of machine resource use is likely in Disk I/O from mysql (to create the new records, especially with the custom fields). You could use a standard resource monitoring program to confirm that. If that's the case, getting yourself SSD disks is likely your best way to speed things up. You might be getting short on memory for mysql as well (though importing contacts isn't a hugely memory intensive process and caches are almost counter productive), depending on what else is using up memory on your machine.
  
  I do see that you've got a couple of extensions that might be generating unnecessary work for your machine (profile and member sync) - those extensions might be kicking in for each contact created in order to see if they need to do something. You could try disabling them (and any other ones you can) and see if that makes a difference.
  
  > "Huge civicrm-tmp files get created, but nothing resolves unless it's tiny"
  
  Not sure how to interpret this, that does suggest something is not optimal.
  
  On a different note, and not what you're asking, but in your position, I'd be tempted to try it on a different machine, perhaps some kind of large-instance db that you could use on a temporary basis to do the import, which is a specialty task that uses different resources from actually running a civicrm db in production. The 40 custom fields is a bit of a red flag for both architecture and performance. 2.7 million records with 40 custom fields is a bit of a mismatch with a home machine.
  
  > **Comments:**
  > **username_0**: Thanks, username_2, for your help. Since small (5,000rows selected from 4.7million) SELECT statements go through with little delay, yet larger (20,000) ones lock up, I think that there's a memory limit being exceeded. Does that make sense? On the huge civicrm-tmp-... tables created - these have the same number of rows as what I hope to import. This suggests to me that a great part of the work has been done - getting these 20,000 rows from the 4.7 million - and that the hang-up is in writing these into the civicrm fields somehow. Does that make sense?  
  > **username_0**: username_2, I disabled all wordpress plugins but CiviCRM. This did not change behavior.  
  > **username_1**: I don't have any experience with this kind of import, so take this FWIIW. But here are tips for optimizing mysql bulk data loading in general that might be helpful: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html  
  > **username_2**: Yes to your diagnosis of the civicrm-tmp tables being related to memory. But just throwing memory in your computer may not resolve it. Mysql will generate tmp tables to split an operation into two bits when it doesn't have enough memory to do it in one step. But 'enough memory' is also about 'enough memory allocated to a specific task', so you need to worry about various mysql configuration, which gets very tricky and prone to counter-productive futzing.  
  > **username_2**: If you do want to try tuning your mysql, I'd suggest using a tuner like this one: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl It'll read your mysql logs and make suggestions of changes to your configuration file, e.g. "innodb_flush_log_at_trx_commit = 2". Your configuration for running a civicrm in production is very different from what you want for a massive import like this.  
  
  ---
  
  