42

First of all what I know :

Index management is useful for increasing store performance.

EAV has one disadvantage.it will store data in different tables. So that retrieving data is time-consuming.

So that we will store data into a single table. when data is changed we will update this single table (nothing but indexing update)

mysql trigger: perform some query actions based on some table insert/update/delete.

So Magento using a trigger for example when the price is updating it will store entity_id into the changelog table.

there is a statement in devdocs for implementing triggers magento2 using Magento/Framework/Mview.

can you anyone explain the flow of this functionality.

I mean what is view, action, processor etc?

1
  • 2
    Not sure about the flow, but Mview refers to materialised views, which is what the index tables are.
    – nevvermind
    Commented May 26, 2016 at 12:36

4 Answers 4

87
+50

In official documentation: https://devdocs.magento.com/guides/v2.3/extension-dev-guide/indexing.html there is the stement:

`Allows tracking database changes for a certain entity (product, category and so on) and running change handler.
Emulates the materialized view technology for MySQL using triggers and separate materialization process (provides executing PHP code instead of SQL queries, which allows materializing multiple queries).`

MView stands for Materialized View which is a snapshot of the database at a point in time. https://en.wikipedia.org/wiki/Materialized_view Why would we need to duplicate tables. Indexers are costly to run, especially when there is traffic on category pages, customers place orders and admins save products. On product save the cache gets invalidated (off topic). In case of stock indexer, before it ends the execution, it sends the entity ids affected as cache tags to be cleaned (full page cache type). In Magento 2.0 categories ids of purchased products are sent. In Magento 2.1 the product ids are sent.

There are 2 MySQL tables that keep indexer codes and statuses:

  • indexer_state
  • mview_state

mview_state works with Update by Schedule in Admin > System > Indexer Management

Update by Schedule makes the indexers to be run in cron.

There are 3 entries in Magento_Indexer/etc/contab.xml:

<group id="index">
    <job name="indexer_reindex_all_invalid" instance="Magento\Indexer\Cron\ReindexAllInvalid" method="execute">
        <schedule>* * * * *</schedule>
    </job>
    <job name="indexer_update_all_views" instance="Magento\Indexer\Cron\UpdateMview" method="execute">
        <schedule>* * * * *</schedule>
    </job>
    <job name="indexer_clean_all_changelogs" instance="Magento\Indexer\Cron\ClearChangelog" method="execute">
        <schedule>0 * * * *</schedule>
    </job>
</group>
  • indexer_reindex_all_invalid is run on indexer_state. There are is still the need to run 'normal' indexers in cron
  • indexer_update_all_views is run on mview_state
  • indexer_clean_all_changelogs - clears changelogs used by mview_state

Note that cron indexer group tasks run in a separate php process, as declared in etc/contab_groups.xml: <use_separate_process>1</use_separate_process>.

Changelog tables are: [indexer name]_cl (suffixed with _cl). e.g. cataloginventory_stock_cl. If you have indexers set to Update by Schedule and save a product in admin you'll see the entity_id of that product in this table. It's a big circle, I'm thinking place order or create shipment will add here an entry too.

Someone provided an example in official devdoc on how to create new materialized views and what are the interface methods required (disregard the above statement about orders in the snippet bellow):

<?php
<VendorName>\Merchandizing\Model\Indexer;
class Popular implements \Magento\Framework\Indexer\ActionInterface,   \Magento\Framework\Mview\ActionInterface
{
public function executeFull(); //Should take into account all placed orders in the system
public function executeList($ids); //Works with a set of placed orders (mass actions and so on)
public function executeRow($id); //Works in runtime for a single order using plugins
public function execute($ids); //Used by mview, allows you to process multiple placed orders in the "Update on schedule" mode
}

This will make sense: //public function execute($ids); Used by mview, allows you to process multiple **entities** in the "Update on schedule" mode } Where $ids parameter has the entities ids from *_cl tables.

What is the link between cache invalidation and indexers. Categories pages are now full page cached (built-in full page cache or through Varnish).

There is \Magento\Indexer\Model\Processor\InvalidateCache::afterUpdateMview:

/**
 * Update indexer views
 *
 * @param \Magento\Indexer\Model\Processor $subject
 * @return void
 * @SuppressWarnings(PHPMD.UnusedFormalParameter)
 */
public function afterUpdateMview(\Magento\Indexer\Model\Processor $subject)
{
    if ($this->moduleManager->isEnabled('Magento_PageCache')) {
        $this->eventManager->dispatch('clean_cache_after_reindex', ['object' => $this->context]);
    }
}

Back to Magento\Indexer\Cron\UpdateMview::execute():

/**
 * Regenerate indexes for all invalid indexers
 *
 * @return void
 */
public function execute()
{
    $this->processor->updateMview();
}

Magento\Indexer\Model\Processor::updateMview():

/**
 * Update indexer views
 *
 * @return void
 */
public function updateMview()
{
    $this->mviewProcessor->update('indexer');
}

In app/etc/di.xml there is:

<preference for="Magento\Framework\Mview\ProcessorInterface" type="Magento\Framework\Mview\Processor" />


/**
 * Materialize all views by group (all views if empty)
 *
 * @param string $group
 * @return void
 */
public function update($group = '')
{
    foreach ($this->getViewsByGroup($group) as $view) {
        $view->update();
    }
}

Magento\Framework\Mview\ViewInterface

/**
 * Materialize view by IDs in changelog
 *
 * @return void
 * @throws \Exception
 */
public function update();

app/etc/di.xml

 <preference for="Magento\Framework\Mview\ViewInterface" type="Magento\Framework\Mview\View" />

In Magento\Framework\Mview\View::update() there is:

$action = $this->actionFactory->get($this->getActionClass());
$this->getState()->setStatus(View\StateInterface::STATUS_WORKING)->save();
..
$action->execute($ids);
..

If you search in vendor/ directory for Magento\Framework\Mview\ActionInterface you'll find for example this:

In \Magento\CatalogInventory\Model\Indexer:

class Stock implements \Magento\Framework\Indexer\ActionInterface, \Magento\Framework\Mview\ActionInterface

In this class there is:

/**
 * Execute materialization on ids entities
 *
 * @param int[] $ids
 *
 * @return void
 */
public function execute($ids)
{
    $this->_productStockIndexerRows->execute($ids);
}

And it looks like it goes back to 'normal' class of indexers' execute` method which is used by MView.

About cache cleaning after Stock Indexer. When an order is placed on checkout, the quantities are subtracted using this observer: \Magento\CatalogInventory\Observer\SubtractQuoteInventoryObserver

$itemsForReindex = $this->stockManagement->registerProductsSale(
    $items,
    $quote->getStore()->getWebsiteId()
);

Further, another observer triggers indexer (but not directly on Mview/Indexer by Schedule): \Magento\CatalogInventory\Observer\ReindexQuoteInventoryObserver

if ($productIds) {
    $this->stockIndexerProcessor->reindexList($productIds);
}

In Mview case, when the new quantities are subtracted in SubtractQuoteInventoryObserver, the MySQL trigger (created for Mview) will insert a row in cataloginventory_stock_cl, marking that a reindex (stock & fulltext) needs to be done to those purchased product ids. There are many MySQL triggers created for Mview. See them all with SHOW TRIGGERS;.

When a product gets out of stock after checkout you'll see 2 rows inserted in that table (Magento saves 2 times stock item in these 2 observers).

When cron runs stock indexer in Mview mode the affected product ids (in M2.1) or categories ids (in M2.0) are sent to cache clean as cache tags. By cache I mean full page cache type. Example: catalog_product_99 or other cache tag format depending on the Magento version. The same when Mview is not enabled.

\Magento\CatalogInventory\Model\Indexer\Stock\AbstractAction::_reindexRows

...
$this->eventManager->dispatch('clean_cache_by_tags', ['object' => $this->cacheContext]);

And Magento_PageCache has observer \Magento\PageCache\Observer\FlushCacheByTags that will clean full page cache type by tags. It does it for buil-in full page cache. Varnish related code is in \Magento\CacheInvalidate\Observer\InvalidateVarnishObserver.

There is a free extension that will deny cache clean on still in stock products after customer checkout:

https://github.com/daniel-ifrim/innovo-cache-improve

Cache cleaning only on out of stock products after checkout was introduced in default Magento 2.2.x. See \Magento\CatalogInventory\Model\Indexer\Stock\CacheCleaner.

I'm thinking the cron execution for indexer in Admin > Stores > Configuration > Advanced > System > Cron configuration options for group: index should be set to much more than 1 minute.

9

Reference from Magento document is already here so I'm skipping that part.
Magento implemented materialized view in 2.0 which tracks changes for all indexers. Each indexer has a _cl table which gets entity_id and a auto_increment version_id from triggers added on main tables.
When cron job executes, indexer gets last version_id for each view from mview_state table and index next available entities in _cl table.
Reindexing was a headache till 1.9.x.x and with huge catalog it always slow down the system.
In Magento 2.0 indexer only update the particular entity information on indexer tables rather than reindexing whole data. This keeps ball rolling without slower down the server.
Note: Materialized View is not supported in mysql so in Magento, it is managed by PHP code and it works similar to Materialized view which is a feature in enterprise level DBMS like oracle.

3
  • 2
    "Magento implemented materialized view in 2.0" - actually it's been there in Magento 1 EE for a while
    – scrowler
    Commented Jun 20, 2016 at 18:05
  • "In Magento 2.0 indexer only update the particular entity information on indexer tables rather than reindexing whole data." - again, partial reindexing exists in Magento 1 too
    – scrowler
    Commented Jun 20, 2016 at 18:07
  • 1
    I made statements based on community editions only. Commented Jun 21, 2016 at 5:51
7

The mview.xml is used along with indexer.xml to setup indexers.

The mview.xml file declares:

  • indexer view ID
  • indexer class
  • the database tables the indexer tracks
  • what column data is sent to the indexer

The indexer.xml file declares:

  • indexer ID
  • indexer class name
  • indexer title
  • indexer description
  • indexer view ID

You can find more information about custom indexer declaration here: Custom indexer on Magento2

From what I understood, there's two different things here:

  • The indexer from the Magento_Indexer module
  • The Mview from Magento\Framework\Mview which emulates the materialized view for MySQL using triggers.

Here are some englighted info from the official documentation

Indexing types

Each index can perform the following types of reindex operations:

  • Full reindex, which means rebuilding all the indexing-related database tables.

  • Full reindexing can be caused by a variety of things, including creating a new web store or new customer group. You can optionally fully reindex at any time using the command line.

  • Partial reindex, which means rebuilding the database tables only for the things that changed (for example, changing a single product attribute or price).

The type of reindex performed in each particular case depends on the type of changes made in the dictionary or in the system. This dependency is specific for each indexer.

Regarding the Workflow, here it is for partial reindexing:

enter image description here

1
0

Magento's Partial Reindexing via Materialized View (mview)

The Materialized View (mview) mechanism in Magento facilitates partial reindexing. This system allows Magento to more efficiently index data by only processing records that have changed, rather than reindexing the entire dataset.

The mview.xml Configuration

The heart of the mview system lies within the mview.xml configuration. This XML file dictates which database tables and columns Magento should monitor for changes. Such granularity ensures that only relevant data modifications prompt reindexing.

Let's check how partial reindex of product price update works. Here's an example from the Magento Catalog module (Magento/Catalog/etc/mview.xml):

<view id="catalog_product_price" class="Magento\Catalog\Model\Indexer\Product\Price" group="indexer">
    <subscriptions>
        <table name="catalog_product_entity" entity_column="entity_id" />
        <table name="catalog_product_entity_datetime" entity_column="entity_id" />
        <table name="catalog_product_entity_decimal" entity_column="entity_id" />
        <table name="catalog_product_entity_int" entity_column="entity_id" />
        <table name="catalog_product_entity_tier_price" entity_column="entity_id" />
        <table name="catalog_product_link" entity_column="product_id" />
        <table name="catalog_product_website" entity_column="product_id" />
    </subscriptions>
</view>

This simplified configuration implies that whenever there's a change in one of the mentioned tables (subscriptions), particularly in the specified columns, an entry is added to the catalog_product_price_cl changelog table (_cl postfix is added to the view id value). This changelog table holds records of products that require reindexing.

Understanding Triggers

Triggers help ensure that changes in the database get reflected in the changelog for indexing. Database triggers in Magento's context are automated procedures set up to react to specific changes in the database. They work silently in the background, ensuring that the changelog table is updated when necessary.

For instance, a trigger on the catalog_product_entity_decimal table for the update action might look like:

BEGIN
SET @entity_id = (SELECT `entity_id` FROM `catalog_product_entity` WHERE `row_id` = NEW.`row_id` AND created_in <= UNIX_TIMESTAMP());
IF (@entity_id IS NOT NULL) THEN IF (NOT(NEW.`value_id` <=> OLD.`value_id`) OR NOT(NEW.`attribute_id` <=> OLD.`attribute_id`) OR NOT(NEW.`store_id` <=> OLD.`store_id`) OR NOT(NEW.`value` <=> OLD.`value`) OR NOT(NEW.`row_id` <=> OLD.`row_id`)) THEN INSERT IGNORE INTO `catalog_product_price_cl` (`entity_id`) values(@entity_id); END IF; END IF;
END

This trigger states that if there are changes (updates) in the catalog_product_entity_decimal table, then catalog_product_price_cl table should be updated to mark the relevant entity_id as having changed.

You can check your triggers by running SHOW TRIGGERS; mysql command.

Checking the MView State

To understand the current state of the view:

SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_price'

This shows the version_id, which will be important for tracking changes.

Let's now check catalog_product_price_cl table to see the version_id entries. If the system is up to date, the last version_id in the changelog should match the version_id from the mview_state table.

SELECT * from catalog_product_price_cl;

We see that the last version_id is the same as the version_id from mview_state table (catalog_product_price_cl.entity_id is the id of the product).

Switching the Indexer Mode to Schedule

Switch the indexer mode to 'schedule' so that indexing updates are managed by the Magento cron job:

bin/magento indexer:set-mode schedule

This mode processes updates in batches based on a set schedule, preventing immediate indexing for every small change, which can be especially beneficial for larger stores.

Triggering a Price Update

To see the system in action, let's simulate a price change. Update a product's price and save it. You can see now the changes reflected in the changelog table:

SELECT * from catalog_product_price_cl;

Analyzing the Changelog

After updating, the changelog table will show a new entry with the product's ID, indicating the version of the change, which will be incremented.

Checking MView state

SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_price'

MView still has outdated (not incremented) version_id.

Checking indexer status

To understand the indexing status after the price update, use the following command:

bin/magento indexer:status

catalog_product_price has 1 item in backlog. It's our updated product.

indexer:status command provides insights into the status of the indexers, which includes information about the backlog. The backlog is determined by comparing the version_id in the mview_state table with the version_ids value in the changelog tables.

Processing the Backlog with Cron

To process the recorded changes and update the index, you can run the Magento cron:

bin/magento cron:run

During this process, the platform refers to \Magento\Framework\Mview\View::update to compare the indexer's version IDs (both current and last) between the changelog table (catalog_product_price_cl) and the mview_state table. If discrepancies are found, the relevant indexer is updated.

Checking indexer status

bin/magento indexer:status

Now catalog_product_price indexer has no items in backlog as they were processed during partial reindex.

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.