Mautic Community Forums

Slow Bulk Importing

Hi,



I have just configured Mautic on our Windows 2008 Server with IIS 7.0 using the relevant Apache / Fast CGI / PHP Manager / PHP 5.6.16 to allow PHP to operate within an IIS environment. I have actioned all the recommendations and requirements displayed on the initial install page. I have increased the upload limits and execution times.



I am looking to have Mautic replace MailChimp for our marketing department but they are complaining about how slow it is and that it isn’t as straight forward as MailChimp. I am not familiar with MailChimp (or Mautic) myself so am not sure on performance but it does appear to take a long time to bulk import with the default import settings.



Is there anything else that I should look into to improve the Batch Import? I have tried to import 60k records which is taking an age. The server is Windows server 2008 R2 64 bit Quad core 2.60GHz with 8GB RAM and plenty of disk space. There aren’t any issues showing in any of the logs (IIS, Mautic & PHP).



Thanks

Arif

Hi,

I have just configured Mautic on our Windows 2008 Server with IIS 7.0 using the relevant Apache / Fast CGI / PHP Manager / PHP 5.6.16 to allow PHP to operate within an IIS environment. I have actioned all the recommendations and requirements displayed on the initial install page. I have increased the upload limits and execution times.

I am looking to have Mautic replace MailChimp for our marketing department but they are complaining about how slow it is and that it isn’t as straight forward as MailChimp. I am not familiar with MailChimp (or Mautic) myself so am not sure on performance but it does appear to take a long time to bulk import with the default import settings.

Is there anything else that I should look into to improve the Batch Import? I have tried to import 60k records which is taking an age. The server is Windows server 2008 R2 64 bit Quad core 2.60GHz with 8GB RAM and plenty of disk space. There aren’t any issues showing in any of the logs (IIS, Mautic & PHP).

Thanks
Arif

Here’s the import speed I’m getting:
I imported 18,587 records.
38 were rejected (each/all because the “state” was “Washington, D.C.” (I will need to figure out what value Mautic will accept, but that’s another matter…)

It took ~5.2 hours. So that works out to about 1 per second.

To me, this is acceptable only for the moment because I’m not doing anything else at the moment (with Mautic), and neither is the instance.
But when the system is underway sending tens of thousands of emails, and registering responses, and we’re doing reporting and campaign work, it can’t be bogged with a long import like this. It won’t be acceptable. Especially when we get into importing 100,000 records.

Specs:
Mautic v2.5.1 on Amazon EC2 micro
1 tag applied, successfully.

[h]What speeds are YOU seeing (anyone?) [/h]

I could use an ETL tool to get the data in,
or… I am able to code something to help with the speed. But before I even look, I want to see if there’s a community need.

(Or perhaps, I’m the only one, and there’s something to correct on my instance?)

Hence, It’d be good to know your speeds! Chime in!

On a 2 GB VPS running Ubuntu, we were able to successfully upload 100,000 email addresses to Mautic using two .csv files of 50,000 names each (Mautic restricts list imports to < 2 MB each), with each import taking about 2 hours and server CPU utilization averaging 70%.

In this project for a client, we offloaded the MySQL database server to a separate 2 GB VPS in the same datacenter, and used the first VPS as a web server for Mautic only.

To draw a fair comparison, this was a test list with only three contact fields for each record: First Name, Last Name, Email. But that’s almost 850 records imported/second. You might have to play with the number of contacts you import per batch (below the file picker on the Import screen) to find the optimal number for your server configuration.

How does your guys php.ini configuration look like, especially pertaining to memory limits and max execution time?

@autoize : Sounds like you’re at about 14 records per second for those imports if they took two hours. Each import of 50,000 took ~2 hours… so that’s 50,000 / 3,600 = 14 per second.
So you were at 850 records per minute, maybe? Yeah… that looks right.

And that is still 14 times the speed I was experiencing. And that would be totally acceptable. We could do imports in off-peak hours and that speed would be ok.

Three fields, you say you brought in… Hmm… Yes…
What I did was (try to) import as companies. (Because it was company data.) Mapped the “name” field to company names, and I imported 7 fields. The extra database inserts required for the company table and the associated joins and could possibly account for the slower import I experienced… Depending on how the ‘save’ is done in the code… it could be 14 times slower. (It wouldn’t slow the database down as much if it were all done in one query.) I will definitely try again, just importing records as contacts/leads, leaving the “company” field unmapped.

This is Great information you shared, @autoize. You gave me something to test.

I’ll also report back here, as you asked, with my php.ini config info… Thanks for recommending that.

Anyone else? Chime in with the rough records-per-second import speed you experience. If there’s a need, I’ll code for speed!
(I won’t do it if I’m the only one who wants it, because I can make a workaround a lot faster.)

@pilot , it’s our pleasure to share experiences with Mautic.
You caught us with our pants down there, yes, we meant 850 records per minute. Company should have the same performance impact as any other field, just that it’s used to drive Mautic’s ABM features once you have the data in there.

What I would suggest if you have an idea how to optimize the query for the import is contributing your code directly to the open source project at Github. That way it’ll be merged into the core and tested with each new release, instead of being a separate mod/plugin that users would need to add manually and may or may not break with future releases.

I came across this article however, I would really need to read up more on how Mautic works with imported CSV’s but this would definitely be worth looking at as a possible plugin.

Bulk Imports

I would guess that this is possibly a similar solution to what Mailchimp use as they can parse a CSV is blistering fast times compared to Mautic. Much of the segmentation is done after the import process again very fast.