Magento 2.1 – Fix Slow Importing Prices CSV

csvimportmagento-2.1pricereindex

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?

Best Answer

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.

The Magento application requires MySQL 5.6.x.

Magento versions 2.1.2 and later are compatible with MySQL 5.7.x.

Magento uses MySQL database triggers to improve database access during reindexing.

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.

MariaDB and Percona are compatible with Magento because we support MySQL 5.6 APIs.

source: https://magento.stackexchange.com/a/151268/69

EDIT

Additional MySQL Settings regarding large import of data:

Related Solutions

Magento – Are there any csv import logs kept

If you look at the file Mage_ImportExport_Model_Abstract you will see that it has a method addLogComment. Now what this does is add log information at some time during the import and export process, as to when I am not sure.

It will log to files in the format var/log/import_export/%Y/%m/%d/%time%_%operation_type%_%entity_type%.log

There is a flag $_debugMode which you will need to set to activate this logging.

The function looks as follows.

/**
 * Log debug data to file.
 * Log file dir: var/log/import_export/%Y/%m/%d/%time%_%operation_type%_%entity_type%.log
 *
 * @param mixed $debugData
 * @return Mage_ImportExport_Model_Abstract
 */
public function addLogComment($debugData)
{
    if (is_array($debugData)) {
        $this->_logTrace = array_merge($this->_logTrace, $debugData);
    } else {
        $this->_logTrace[] = $debugData;
    }
    if (!$this->_debugMode) {
        return $this;
    }

    if (!$this->_logInstance) {
        $dirName  = date('Y' . DS .'m' . DS .'d' . DS);
        $fileName = join('_', array(
            str_replace(':', '-', $this->getRunAt()),
            $this->getScheduledOperationId(),
            $this->getOperationType(),
            $this->getEntity()
        ));
        $dirPath = Mage::getBaseDir('var') . DS . self::LOG_DIRECTORY
            . $dirName;
        if (!is_dir($dirPath)) {
            mkdir($dirPath, 0777, true);
        }
        $fileName = substr(strstr(self::LOG_DIRECTORY, DS), 1)
            . $dirName . $fileName . '.log';
        $this->_logInstance = Mage::getModel('core/log_adapter', $fileName)
            ->setFilterDataKeys($this->_debugReplacePrivateDataKeys);
    }
    $this->_logInstance->log($debugData);
    return $this;
}

Magento – Fix Empty Columns When Importing Products via CSV

You do not need these columns, the only required columns you need are the attributes that are required in the backend.

So in a base install of Magento, you need "description", "name", "price", "sku", "status", "tax_class_id", "visibility", and "weight". Below is a picture of the required options in a clean install of Magento EE 1.14 (so there are a few additional required fields as you can see).

Required Options

Many developers, will delete these columns from the CSV if we are not using them.

I would suggest using OpenOffice to create your CSV and make sure you select the option to "quote all text fields". Microsoft Excel can work, however I have noticed a lot of formatting issues that can screw up data (especially if you use Office for Mac).

Best Answer

EDIT

Related Solutions

Magento – Are there any csv import logs kept

Magento – Fix Empty Columns When Importing Products via CSV

Related Topic