2

In newer versions of Enterprise Magento ships with change log tables (See Mechanics of the new Changelog Tables (ex: catalog_category_product_cat_cl) for more information on how they work). Now these tables can become large over time, for example my most recent dump from a client has the following:

+------------------------------------+------------+
| Table                              | Size in MB |
+------------------------------------+------------+
| catalogsearch_fulltext_cl          |     911.00 |
| catalog_product_index_price_cl     |     728.00 |
| cataloginventory_stock_status_cl   |     709.00 |
| catalog_product_flat_cl            |     208.75 |
| catalog_category_product_index_cl  |      59.59 |
| catalog_category_flat_cl           |      53.59 |
| enterprise_url_rewrite_category_cl |      48.58 |
| catalog_category_product_cat_cl    |      48.58 |
| enterprise_url_rewrite_product_cl  |       2.52 |
| enterprise_url_rewrite_redirect_cl |       0.09 |
+------------------------------------+------------+

Now there is a cron that can be enabled to clean these tables up. This can be done via the System > Configuration > (ADVANCED section) > Index Management. After running this cron I would expect to see these tables shrink in size but running the same query after returns the following:

+------------------------------------+------------+
| Table                              | Size in MB |
+------------------------------------+------------+
| catalogsearch_fulltext_cl          |     996.00 |
| catalog_product_index_price_cl     |     728.00 |
| cataloginventory_stock_status_cl   |     723.48 |
| catalog_product_flat_cl            |     208.75 |
| catalog_category_product_index_cl  |      59.59 |
| catalog_category_flat_cl           |      53.59 |
| enterprise_url_rewrite_category_cl |      48.58 |
| catalog_category_product_cat_cl    |      48.58 |
| enterprise_url_rewrite_product_cl  |       2.52 |
| enterprise_url_rewrite_redirect_cl |       0.02 |
+------------------------------------+------------+

What the heck is happening? For example catalogsearch_fulltext_cl has increased from 911MB to 996MB after cleaning the table?

3

While looking into the content of the tables I noticed that the size goes up but the content actually decreases. Let's keep the example of the catalogsearch_fulltext_cl table.

Before the clean it has 31748125 rows. After the clean it has 192892 rows.

So after the cleaning process I ran OPTIMIZE TABLE across all the _cl tables and then the size was as follows:

+------------------------------------+------------+
| Table                              | Size in MB |
+------------------------------------+------------+
| catalog_category_product_cat_cl    |      54.58 |
| catalogsearch_fulltext_cl          |       7.52 |
| cataloginventory_stock_status_cl   |       7.52 |
| catalog_product_index_price_cl     |       1.52 |
| enterprise_url_rewrite_category_cl |       0.02 |
| catalog_product_flat_cl            |       0.02 |
| catalog_category_product_index_cl  |       0.02 |
| catalog_category_flat_cl           |       0.02 |
| enterprise_url_rewrite_redirect_cl |       0.02 |
| enterprise_url_rewrite_product_cl  |       0.02 |
+------------------------------------+------------+

How this is much better results and a more manageable size database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.