2.3 Customer module Recurring setup script performance problems.

[vbuck]

When running bin/magento setup:upgrade for a Magento CE 2.3.x installation(or just use Magento Open source), there is an unexpected delay in the recurring setup script execution on the Magento_Customer module(every time when you run bin/magento setup:upgrade) . This is more pronounced on a large data set (>500K customers).

References

• MAGETWO-85326
• https://github.com/magento/magento2/commit/dc4bb0eae824588459f1e82b64dae96ab6026092#diff-0ac6816ed3ec11b7a9c59731fae99d4b

Preconditions (*)

1. Magento CE 2.2.x(or 2.3.x) -> 2.3.x upgraded codebase (pre DB upgrade)
2. A large customer database (>500K records).

Steps to reproduce (*)

1. After codebase upgrade, proceed to run bin/magento setup:upgrade
2. Observe execution delay on process step:

   Module 'Magento_Customer':
   Running data recurring...

Repeat these steps and you will notice, since there is a recurring upgrade script, that it runs every time.

Expected result (*)

1. No recurring data scripts run, or they are or more performant.

Actual result (*)

1. Recurring data scripts run with each attempt to upgrade the DB.

---

After ending of update you can run again bin/magento setup:upgrade and you will meet this problem again. I am not sure of the need/reason to run a recurring upgrade, but from the reference posted at the top of this issue it's clear the intent to is to handle reindexing on upgrades. This seems unwise and gives room for abusing recurring upgrade scripts with patch-like behavior or long-running processes which can delay deployment times.

Do you have any background regarding the nature of the change?

[LiamTrioTech]

Still a problem in 2.3.5 btw. Upgrade to 2.4.x?

[hostep]

@LiamTrioTech: have you read this comment and this comment? It says it is fixed in 2.4.0 and will be fixed in 2.3.6 as well.

[adrian-martinez-interactiv4]

Hi, we've recently run into this issue within a Magento installation with 1100K customers. I've been investigating, and this is what I found, just in case it is useful for someone.

I know this issue is related with setup:upgrade performance related with customer_grid indexer, and this comment is about customer_grid indexer inner performance, but since it affects also setup:upgrade when reindexing all, I thought it would make sense to post it here.

About this comment:

It's works just because it's invalidated and reindexed in background by cron job. There are no mview processor for this indexer just dummy.

Although it's true it has only a dummy mview, indexer does not get invalidated and reindexed by cron job, but synchronously upon Customer and Customer Address save, at \Magento\Customer\Model\Customer::reindex and \Magento\Customer\Model\Address::reindex, respectively. Index only gets invalidated when customer attribute is added and used in grid / modified and used in grid changed / deleted and used in grid, so a full reindex is needed to rebuilt the grid table properly.

At https://support.magento.com/hc/en-us/articles/360025481892-New-customer-records-are-not-displayed-in-the-Customers-grid-after-importing-them-from-CSV it says customer_grid index is not supported by "Update by schedule" due to performance reasons, but it does not specify any detail.

Digging a little deeper, we arrive soon at https://github.com/magento/magento2/blob/2.4-develop/app/code/Magento/Customer/Model/Indexer/Source.php, the data source provider for customer grid data. It provides an iterator to supply data to be indexed:

    /**
     * Retrieve an iterator
     *
     * @return Traversable
     */
    public function getIterator()
    {
        $this->customerCollection->setPageSize($this->batchSize);
        $lastPage = $this->customerCollection->getLastPageNumber();
        $pageNumber = 1;
        do {
            $this->customerCollection->clear();
            $this->customerCollection->setCurPage($pageNumber);
            foreach ($this->customerCollection->getItems() as $key => $value) {
                yield $key => $value;
            }
            $pageNumber++;
        } while ($pageNumber <= $lastPage);
    }

Benchmarking this method, we found that at each step, execution time increases a bit. After many steps, time elapsed at each step can be increased even by 10x. Taking a quick look at the code shows the issue here.

At each step, the same query is performed to retrieve data, with different sql LIMIT offset values. Having _LIMIT [offset,] rowcount, assuming a batch size of 10000, consecutive queries would look something like (very simplified):

• SELECT * FROM huge_table LIMIT 0, 10000
• SELECT * FROM huge_table LIMIT 10000, 10000
• SELECT * FROM huge_table LIMIT 20000, 10000
• (...)
• SELECT * FROM huge_table LIMIT 1090000, 10000
• SELECT * FROM huge_table LIMIT 1100000, 10000

Mysql starts building query results, and returns them as soon as it has the needed number of them. It is easy for the first query, but for the last one, it has to generate internally (due to joins, ordering, etc) the offset + 10000 results, to return only the last 10000, discarding the offset results. In short:

• Step 1: Mysql generate 10000 results, returns 10000 results.
• Step 2: Mysql generate 20000 results, returns 10000 results.
• Step 3: Mysql generate 30000 results, returns 10000 results. (...)
• Step 109: Mysql generate 1090000 results, returns 10000 results.
• Step 110: Mysql generate 1100000 results, returns 10000 results.

A real example, using a query generated by the indexer, note the offset and the elapsed time:

1.7 ms vs 20.4 s is a huge difference. Our solution looks like this:

    /**
     * Retrieve an iterator
     *
     * @return Traversable
     */
    public function getIterator()
    {
        $customerIdLastPage = ceil($this->count() / $this->customerIdsBatchSize);

        if (0 < $customerIdLastPage) {
            $customerCollection = clone $this->customerCollection;
            $customerIdPageNumber = 0;

            do {
                $customerIds = $this->customerCollection->getAllIds($this->customerIdsBatchSize, $customerIdPageNumber * $this->customerIdsBatchSize);

                foreach (array_chunk($customerIds, $this->batchSize) as $customerIdsChunk) {
                    $customerCollection->clear();
                    $customerCollection->resetData();
                    $customerCollection->getSelect()->reset(\Magento\Framework\DB\Select::WHERE);
                    $customerCollection->addFieldToFilter($this->getIdFieldName(), ['in' => array_map('intval', $customerIdsChunk)]);

                    foreach ($customerCollection->getItems() as $key => $value) {
                        yield $key => $value;
                    }
                }

                $customerIdPageNumber++;

            } while ($customerIdPageNumber <= $customerIdLastPage);
        }
    }

Explained:

• We take advantage of $this->customerCollection having filters already applied, to retrieve which customer ids should be affected by reindex.
• We split customer ids retrieval in chunks. We have set customerIdsBatchSize to 100000, to avoid to retrieve 1100K customer ids at once.
• For each chunk of customer ids, we split that again in chunks of batch size, to return data in batches of that size.
• Once we know the customer ids we have to get data for, we can remove the collection WHERE part, and replace it by the customer ids to be processed. This is possible due to filters had been already applied when retrieving customer ids.
• Using customer id in WHERE clause allows to use column index to perform search faster, and avoids Mysql having to generate unneeded results due to offset.
• Out of this code, we have reduce batch size from 10000 to 100, to avoid locking tables for a long time (in this case we prefer to query more often, once solved the query issue), and to generate less customer items at the same time (10000 => 100) to try to reduce memory usage (it also seems to help with execution speed, but I'm not 100% sure of this). This is optional.

Result for us is as steps get executed, execution time for each of them remains almost the same. This may not make a difference for small reindexing, but it really does for databases with large customer tables.

Also, we've implemented the mview system and "Update by schedule" for this indexer separately; we'll check how that works, and find what performance issues are those which weren't explained at Magento page. I'll let you know if I find something new about that.

[ihor-sviziev]

@adrian-martinez-interactiv4 amazing job! This is really huge step forward!

@o-iegorov could you review following comment https://github.com/magento/magento2/issues/19469#issuecomment-679964528? Could you bring us more info which performance issues it were causing when used update by schedule for customer grid index? maybe we as a community could fix it?

[xxorax]

Does this fix will be realease in the 2.3 branch ? It just need to apply the patch I guess.

[ageffray]

Any news for this ? It's a huge thing, I've got performance issue because of this on many large projects

[jonathanribas]

We also have this issue at Zadig & Voltaire.

It's just crazy to recreate the whole customer grid flat table at every deploy we do every day.

[fredden]

From what I read this is solved in versions 2.3.6 and 2.4.0. If you're running versions below these, I'd recommend upgrading.

[xpoback]

So when the customer grid in admin needs a reindex, the whole world should wait? Some data in an admin grid might look outdated so let's make the downtime of the whole shop longer?

I've read the thread but still, why not to schedule the indexer so that it can later be run asynchronously instead of executing it directly in the upgrade script? The upgrade itself takes seconds to get executed but all those Recurring.php take dozens of seconds or sometimes minutes. I agree that some stuff must be checked to keep the DB consistent but does it make sense, that the shop is in maintenance mode only because something in a grid or in a sales report has changed?

Whatever.