Skip to content

Instantly share code, notes, and snippets.

@mttjohnson
Last active November 9, 2017 19:53
Show Gist options
  • Save mttjohnson/b2d133c8eae22b9576cf to your computer and use it in GitHub Desktop.
Save mttjohnson/b2d133c8eae22b9576cf to your computer and use it in GitHub Desktop.
Magento 1.x Enterprise Partial Indexing Diagnostics Toolset
-- When products are added/removed from a category this table stores all the relation data
select * from catalog_category_product where product_id = '24526';
-- The index that is used for presenting products in a category on the frontend
select * from catalog_category_product_index where product_id = '24526';
-- The change log table used for Magento EE partial index functionality
select * from catalog_category_product_index_cl where product_id = '24526' and version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl');
-- When products are added/removed from a category this table stores all the relation data
select * from catalog_category_product where product_id = '218';
-- The index that is used for presenting products in a category on the frontend
select * from catalog_category_product_index where product_id = '218';
-- The change log table used for Magento EE partial index functionality (only list records not yet processed)
select * from catalog_category_product_index_cl where product_id = '218' and version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl');
-- Monitoring the cron job to check for error messages, execution time, and most recent finished time
select UTC_TIMESTAMP() `current_timestamp`,
case
when s.status = 'running' then TIMESTAMPDIFF(MINUTE,s.executed_at,UTC_TIMESTAMP())
when s.finished_at is null and s.executed_at is null then null
else concat(TIMESTAMPDIFF(MINUTE,s.executed_at,s.finished_at), ':', (TIMESTAMPDIFF(SECOND,s.executed_at,s.finished_at) - (TIMESTAMPDIFF(MINUTE,s.executed_at,s.finished_at) * 60)))
end `min_sec_executing`,
s.*
from cron_schedule s
where s.job_code in ('enterprise_refresh_index', 'enterprise_index_clean_changelog', 'smart_merchandiser_reindex');
order by executed_at desc;
delete from cron_schedule where job_code = 'enterprise_refresh_index';
delete from cron_schedule where job_code = 'enterprise_index_clean_changelog';
insert into cron_schedule (job_code, status, created_at, scheduled_at) values ('enterprise_index_clean_changelog', 'pending', UTC_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(),INTERVAL 0 MINUTE));
-- useful to see what the current UTC time is when comparing to the cron_schedule
select UTC_TIMESTAMP();
-- check for existance of table catalog_product_entity_tmp_indexer
show tables like 'catalog_product_entity_tmp_indexer';
-- Check index status
select * from index_process order by started_at;
-- Check to see how many records are in the change long table
-- Stage 20035887 2016-02-09 08:22:14
select count(*), UTC_TIMESTAMP() from catalog_category_product_index_cl;
-- check to see what version_id each enterprise partial index is currently at.
-- magento will use this when trying to process partial index rebuilds rather than full reindexing of the tables
select * from enterprise_mview_metadata;
select 'cataloginventory_stock_status_cl' tablename,
(select count(*) from cataloginventory_stock_status_cl) total_count,
(
select count(*)
from cataloginventory_stock_status_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'cataloginventory_stock_status_cl')
) unindexed_count
union all
select 'enterprise_url_rewrite_redirect_cl' tablename,
(select count(*) from enterprise_url_rewrite_redirect_cl) total_count,
(
select count(*)
from enterprise_url_rewrite_redirect_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_redirect_cl')
) unindexed_count
union all
select 'enterprise_url_rewrite_category_cl' tablename,
(select count(*) from enterprise_url_rewrite_category_cl) total_count,
(
select count(*)
from enterprise_url_rewrite_category_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_category_cl')
) unindexed_count
union all
select 'enterprise_url_rewrite_product_cl' tablename,
(select count(*) from enterprise_url_rewrite_product_cl) total_count,
(
select count(*)
from enterprise_url_rewrite_product_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_product_cl')
) unindexed_count
union all
select 'catalog_category_product_index_cl' tablename,
(select count(*) from catalog_category_product_index_cl) total_count,
(
select count(*)
from catalog_category_product_index_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl')
) unindexed_count
union all
select 'catalog_category_product_cat_cl' tablename,
(select count(*) from catalog_category_product_cat_cl) total_count,
(
select count(*)
from catalog_category_product_cat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_cat_cl')
) unindexed_count
union all
select 'catalog_product_index_price_cl' tablename,
(select count(*) from catalog_product_index_price_cl) total_count,
(
select count(*)
from catalog_product_index_price_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_product_index_price_cl')
) unindexed_count
union all
select 'catalog_category_flat_cl' tablename,
(select count(*) from catalog_category_flat_cl) total_count,
(
select count(*)
from catalog_category_flat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_flat_cl')
) unindexed_count
union all
select 'catalog_product_flat_cl' tablename,
(select count(*) from catalog_product_flat_cl) total_count,
(
select count(*)
from catalog_product_flat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_product_flat_cl')
) unindexed_count
union all
select 'catalogsearch_fulltext_cl' tablename,
(select count(*) from catalogsearch_fulltext_cl) total_count,
(
select count(*)
from catalogsearch_fulltext_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalogsearch_fulltext_cl')
) unindexed_count
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment