Configure server and config file for import large data

💬 General Discussion
1

I have 5,000,000 records in CSV and I want to import to Leads Module. I try to import 100,000 records too much time needed about 7 hours.

My Server spec is :
4core CPU(8,4GHz)
16GB Memory
SSD 500GB

Apache2, MariaDB

Anybody can help me to give advise for tuning performance for import file ?

If possible, 1 minute able to import 10,000 records

Many thanks

2

I doubt 1 minute to import 10,000 records would be possible with the import wizard. If you want to import large amounts of records I would look into writing a PHP script that will import the records directly into the database.

1 Like
3

You definitely want to check this excellent post by Jim Mackin:

http://www.jsmackin.co.uk/suitecrm/large-imports-in-suitecrm/

2 Likes
4

Thanks a lot, great article

5

One minute would be awesome! It’s currently taking me over 3 hours to import 11,000 records! I can live with this as I wont be importing that kind of volume very often, but I also need to run mass updates against these records, which is also painfully slow.

Does anyone know of an optimum VM / PHP config to help with these slow procedures?

Many Thanks

Luke

6

The article linked above gives you several clues on how to import directly into the database. It should be very quick for only 10.000 lines.

But on the initial import you need to be careful to get every necessary field right.

For the mass updates it should be really easy to use SQL to tweak one or two fields according to some condition - well, easy if you have the necessary basic technical skills : - )

Make sure you have a full backup of the database before you start playing with all this… it’s easy to completely and irreversibly ruin all your data with just one SQL command.

7

Thanks for this.

The mass updates will need to be done every other week, the conditions will be set by users, ie what ever criteria they select in the list view for contacts.

I was hoping for a potential config tweak so I could avoid customising the CRM, I’ve submitted a separate post on this…

https://suitecrm.com/forum/developer-help/13437-custom-mass-update

Thanks again for the reply!

8

My SuiteCRM instance is updating 30 records every minute, there must be some config that can help here?

Many Thanks

Luke

9

Which records is it updating? Do you mean during the imports? Or during normal system use? Or just when it is supposed to be idle?

Maybe you should check your logs and see what is making it slow. You might have a general performance problem (that you only notice during large imports), instead a slow-import problem.

You might have indexing going on all the time due to some index corruption.

When you import a record, hooks are fired (for workflows and other things). You might have something broken here, making it too slow.

I don’t think you will find and easy, magic, config change to solve your still undiagnosed problem…

10

Hi pgr

Thanks for the replay and sorry for the late response.

It’'s updating one field as part of the contacts module via mass update in list view.

I had disabled any workflows on the module, and removed any audit fields to see if this helped but it didn’t

I did enable query logs and examined them as it was performing the mass update. SuiteCRM is executing an update query for every record selected in the list view, in my case 10,000+. I think it’s this approach that’s making the whole process so slow. An export from list view uses a different approach, it appears to select the records in big batches, using the SELECT WHERE IN syntax, this is much faster and I’ll be looking at adopting this approach for a custom mass update at some point. It’s a shame the the reports export feature doesn’t select the data the same way as list view, as I cannot export large amounts of records from reports, which would have been nice as you can create more complex queries there and also define what fields are in the report and therefor exported.

I also cloned the production instance, and it’s does update a lot faster, still takes around an hour though, I think this performance increase it due to me having SSD drives locally.