Author Topic: super slow import (10-15 records per minute), suggestions? (Read 5435 times)

jcims · September 16, 2008, 04:39:10 pm

Hi Folks,

I'm importing contacts (individuals) with probably 8 standard fields populated and another 10 custom fields populated. The import is nothing special, no unusual characters or anything, just basic CSV.

If i import this at my web host, it will take approximately an hour for it to complete a file with 750 users in it. When done, and no errors reported, only about 150 are actually present. If i do not import any of the custom fields, the performance is about the same, but i get 450 or so users imported.

I tried a sanitized version of the same file against the sandbox civicrm install (standard fields only) and it took less than a minute easily.

So i'm thinking this is a hosting issue. I'm using 1and1.com hosting, who outsources their mysql hosting to perfora.net...is it possible i'm just running into a latency issue or something? Is there a 'benchmark' of some type (e.g. the sample users as a csv) that i could run to test things out?

This is mysql 5.x php 5.x civicrm 2.1b4 on drupal 6.4.

jcims · September 17, 2008, 04:18:08 am

I tried again on a different system, got a timeout while just importing the standard contact information.

I'm going to do a small import today and diff the database. I think i'm just going to write the SQL by hand and go that route, having a browser wait 1300 minutes to import a file isn't going to be reliable.

Alternatively, has anyone experimented with 'importing' via iterating over the list and using an http post to stick it in the database? That seems to be a reasonable option.

jcims · September 17, 2008, 05:31:03 am

One other thing i wanted to add is that the system doing the import (apache and mysql located on the same box) was _not_ busy during the import. Load average was ~.1, and firefox actually was using more CPU than apache or mysql.

I read the note about not doing a geolocation on import, i made sure that option was unchecked on the import screen.

jcims · September 19, 2008, 04:36:53 am

For what it's worth, I ultimately wrote a perl script (with some inspiration from nmiracle) to pull data from our staging table, butter it up a bit, and then push it into the civi contact/email/address tables. It's really pretty easy.

xavier · September 20, 2008, 01:47:11 am

Hi,

Importing about 2000 companies on a local lighttpd... Can confirm that it's a handful of records per minute only.

(I remember something faster on 2.0.x, but no hard datas, just an impression)

X+

kenlyle · October 10, 2008, 05:39:30 am

Same here on 2.1. I am on an almost new, lightly loaded quad processor box, and appear to be getting roughly a dozen households per minute, estimating on the counts of contacts in PHPMyAdmin.

I am importing just over 1300 records, 10 fields, only 4 or 5 of which are being used for the import.

OK, before the job timed out (500 error), I got 22 in a minute, based on my local clock, and the record count from PHPMyAdmin.

Here is the server status from cPanel:
Service    Details    Status
named    up
lfd    up
cpsrvd    up
Server Load    0.08 (4 cpus)
Memory Used    32.8 %
Swap Used    0.01 %
Disk /dev/sda1 (/boot)    9 %
Disk /dev/sda2 (/tmp)    3 %
Disk /dev/sda3 (/)    43 %
Disk /dev/sdb2 (/home2)    24 %

I might be able to help debug this situation further, given the right instructions...

The record count continued to increase as the import continued after the timeout...which is interesting...have to wait to see where it stops before re-trying...

at 9:22 EST, I had 1100 imported.

The import finished successfully between 9:22 EST and 10:10 EST, don't know when. I have 1317 records in Contacts (may be an estimate per PHPMA), with 7 Individuals who were there before.

Thanks,
Ken

Donald Lobo · October 10, 2008, 06:49:11 am

ok, time for a public confession

Unfortunately we shipped 2.1 with a debugging sleep(3) in import (CRM/Import/Parser/Contact.php, line 792). This has been fixed in 2.1.1 (coming out later today). This will speed up 2.1 import a bit. But overall import is still not a speedy operation

lobo

kenlyle · October 10, 2008, 09:38:24 am

Thanks for stepping up, Donald

Thanks for the laugh!

Ken

CiviCRM Community Forums (archive)

News:

Author Topic: super slow import (10-15 records per minute), suggestions? (Read 5435 times)

jcims

super slow import (10-15 records per minute), suggestions?

jcims

Re: super slow import (10-15 records per minute), suggestions?

jcims

Re: super slow import (10-15 records per minute), suggestions?

jcims

Re: super slow import (10-15 records per minute), suggestions?

xavier

Slow import on 2.1 as well

kenlyle

Re: super slow import (10-15 records per minute), suggestions?

Donald Lobo

Re: super slow import (10-15 records per minute), suggestions?

kenlyle

Re: super slow import (10-15 records per minute), suggestions?