1

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) Plug-ins present

Edit 2022Feb6 I should have noted that the records are being chosen from a table with 4.7 million rows.

6
  • Can you add to your question the specs of the machine you are running this on? Processor type, amount of RAM and disk space.
    – twowheeler
    Jan 31, 2022 at 2:28
  • 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'.
    – Brian Cady
    Jan 31, 2022 at 18:34
  • 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?
    – twowheeler
    Jan 31, 2022 at 18:56
  • 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.
    – Brian Cady
    Jan 31, 2022 at 21:44
  • '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'
    – Brian Cady
    Feb 6, 2022 at 14:21

1 Answer 1

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.

5
  • 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?
    – Brian Cady
    Feb 6, 2022 at 13:09
  • Alan Dixon, I disabled all wordpress plugins but CiviCRM. This did not change behavior.
    – Brian Cady
    Feb 6, 2022 at 15:31
  • 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: dev.mysql.com/doc/refman/8.0/en/…
    – twowheeler
    Feb 7, 2022 at 0:48
  • 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.
    – Alan Dixon
    Feb 7, 2022 at 15:39
  • If you do want to try tuning your mysql, I'd suggest using a tuner like this one: raw.githubusercontent.com/major/MySQLTuner-perl/master/… 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.
    – Alan Dixon
    Feb 7, 2022 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.