1

In Magento 2, there are roughly 115,000 products in the store. There are 7 customer groups. When importing prices via csv, the batch upload slowly imports the price but eventually crawls to a stop. I've checked all error logs in my server, can't find any failure notices in magento logs, or any other server logs. No email is sent.

More details on the setup:

  • RHEL 7
  • PHP 7
  • Apache
  • MySQL
  • Magento 2.1 EE

How I setup up the import is through a scheduled task, and hitting 'run'. I've tried batches of 50000, 10000, 5000, 1000. All have the same effect. When uploading products, customers, and availability - Magento had no issue with big bulk uploading.

Another symptom I may be seeing is bin/magento indexer:reindex takes no more than :30 seconds for each category, except for Prices and EAV. It takes over 3 minutes each. Currently there only 3000 prices in the database, so 3 minutes to reindex seems like a lot.

Has anyone come across this? Any suggested fixes?

Improve this question

3 Answers 3

Reset to default
2

Magento 2.x even though it's "supposed to be faster than Magento 1.x", the stack has doubled in size going from ~40 PHP files per request to ~80 PHP files to fulfill a single request, with the inclusion of vendors/composer. Keep this in mind if you are seeing a lot of disk I/O when locating bottlenecks.

However most of the bottleneck will likely be at the MySQL level:

  • Disable any unnecessary indexing & cron jobs or shut down all other services that are not needed during the import processes.
  • Make sure to use MySQL 5.7. (Percona has a very nice InnoDB engine/tools)
  • Bump up its max_allowed_packet that is larger than the default, 16MB
  • Optimize to handle the large amounts of data you are putting in quickly since non-production fail-safe mechanisms aren't needed.
  • innodb_flush_log_at_trx_commit to the value 2 in your my.cnf config file to avoid over cautious writings while in a non production time frame.
  • Be sure to use PHP7 & OPCache configured properly per environment need. Use https://github.com/rlerdorf/opcache-status to monitor OPCache stats.
  • Utilize tools such as: htop, mytop, iotop, sysdig and the best weapon of choice is: strace, and while in htop "s" keyboard shortcut on the highlighted process will basically run strace with the parameter of the processes ID highlighted. Or just ps waux | grep mysql and locate the id of the process and strace <PID>.

For the slow indexing process: Recreate the MySQL triggers for the indexer, and enable the Scheduled indexing process (make sure cron is setup/enabled)

php bin/magento index:trigger:recreate -vvv

The indexer in Magento 2 relies heavily on the MySQL trigger feature to speed up indexing, along with changelogs & the cron jobs keeping the indexer updated with deltas, instead of ignoring whats changed when doing a full reindex.

Details: Magento 2.1 on MySQL.

Below are some main Magento2 MySQL Recommeded Configurations.

  1. The Magento application requires MySQL 5.6.x.
  2. Magento versions 2.1.2 and later are compatible with MySQL 5.7.x.
  3. Magento uses MySQL database triggers to improve database access during reindexing.
  4. If you expect to import large numbers of products into Magento, you can increase the value for max_allowed_packet that is larger than the default, 16MB.
  5. MariaDB and Percona are compatible with Magento because we support MySQL 5.6 APIs.

EDIT

Additional MySQL Settings regarding large import of data:

Improve this answer
1

I wrote a script similar today that allowed one of my clients to import there prices and stock and found that i was needing to reindex the data after roughly 500 records it had no issue but when i tried to import more it had issues id suggest doing 500 first if that works for you then id consider continuing

Improve this answer
2
  • Still had that issue. Hacked magento's core to get this fixed, not the best way to go about it. I'll post my findings if another answer doesn't present itself.
    – Spencer Rohan
    Commented Sep 11, 2017 at 14:38
  • Never mess with the core either extend or overwrite in your own extension never modify magentos core files
    – Dava Gordon
    Commented Sep 12, 2017 at 21:28
1

After a lot of deep diving in the magento code, discovered a config.xml limits the size of a bulk import.

This file can be found: /vendor/magento/module-import-export/etc/config.xml

I then dialed in the numbers to a setting optimal for our code pushes. Building a module for this override is the next step of course, but figured out the cause.

Improve this answer

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.