This article explains how the oversized change log tables cause delays in entity updates being applied, and provides recommendations on how to avoid change log tables from getting oversized.
Affected products and versions
- Magento Commerce Cloud 2.2.x, 2.3.x
- Magento Commerce 2.2.x, 2.3.x
Changes you make in the database are not reflected on the store front, or there is a significant delay in the application of entity updates. The entities that might be affected include products, categories, prices, inventory, catalog rules, sales rules and target rules.
If your indexers are configured to update by schedule, the issue might be caused by one or more tables with change logs being too large. The change log tables grow that big if the
indexer_update_all_views cron job is not completed successfully multiple times.
Change log tables are the database tables where the changes to entities are tracked. A record is stored in a change log table as long as the change is not applied, which is performed by the
indexer_update_all_views cron job. There are multiple change log tables in a Magento database, they are named according to the following pattern: INDEXER_TABLE_NAME + ‘_cl’, for example
catalog_product_category_cl. You can find more details on how changes are tracked in database in the Indexing overview > Mview article on Magento DevDocs.
Ensure that the
indexer_update_all_views cron job is always successfully completed.
You can use the following SQL query to get the data about the statuses of all instances of the
indexer_update_all_views cron job:
select * from cron_schedule where job_code = "indexer_update_all_views" and status <> "success" and status <> "pending";
Or you can check its status in the logs by searching for the
<install_directory>/var/log/cron.log- for versions 2.3.1+ and 2.2.8+
<install_directory>/var/log/system.log- for earlier versions