Last active
August 24, 2022 16:55
-
-
Save yvoronoy/06e297d7bc46956e3bf3d739bc58d23d to your computer and use it in GitHub Desktop.
How many unprocessed entities by indexer
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
export DB_NAME=$(grep [\']db[\'] -A 20 app/etc/env.php | grep dbname | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); | |
export MYSQL_HOST=$(grep [\']db[\'] -A 20 app/etc/env.php | grep host | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); | |
export DB_USER=$(grep [\']db[\'] -A 20 app/etc/env.php | grep username | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/['][,]//"); | |
export MYSQL_PWD=$(grep [\']db[\'] -A 20 app/etc/env.php | grep password | head -n1 | sed "s/.*[=][>][ ]*[']//" | sed "s/[']$//" | sed "s/['][,]//"); | |
mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -U | |
pager less -S; | |
#URLs | |
mysql -h $MYSQL_HOST -u $DB_USER --password=$MYSQL_PWD $DB_NAME -e "SELECT * FROM core_config_data WHERE path = 'web/unsecure/base_url' OR path = 'web/secure/base_url'" | |
#DEBUG SQL | |
SET GLOBAL general_log = 1; | |
SET GLOBAL log_output = 'table'; | |
SELECT * FROM mysql.general_log; | |
SET GLOBAL general_log = 0; | |
TRUNCATE mysql.general_log; | |
# Unproccessed entities | |
SELECT COUNT(*) AS version_count FROM catalog_product_category_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_category' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_category_flat_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_category_flat' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_product_flat_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_flat' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_category_product_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_category_product' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_product_category_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_category' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_product_price_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_price' | |
); | |
SELECT COUNT(*) AS version_count FROM catalog_product_attribute_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalog_product_attribute' | |
); | |
SELECT COUNT(*) AS version_count FROM catalogrule_rule_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalogrule_rule' | |
); | |
SELECT COUNT(*) AS version_count FROM catalogrule_product_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalogrule_product' | |
); | |
SELECT COUNT(*) AS version_count FROM targetrule_product_rule_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'targetrule_product_rule' | |
); | |
SELECT COUNT(*) AS version_count FROM targetrule_rule_product_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'targetrule_rule_product' | |
); | |
SELECT COUNT(*) AS version_count FROM salesrule_rule_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'salesrule_rule' | |
); | |
SELECT COUNT(*) AS version_count FROM cataloginventory_stock_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'cataloginventory_stock' | |
); | |
SELECT COUNT(*) AS version_count FROM catalogsearch_fulltext_cl WHERE version_id > ( | |
SELECT version_id FROM mview_state WHERE view_id = 'catalogsearch_fulltext' | |
); | |
# product with attribute | |
SELECT * FROM catalog_product_entity AS e | |
LEFT JOIN catalog_product_entity_varchar AS v ON v.row_id = e.row_id | |
LEFT JOIN eav_attribute AS eav ON eav.attribute_id = v.attribute_id | |
WHERE eav.attribute_code = 'url_key' AND eav.entity_type_id = 4; | |
# Finds conflicts product url keys with custom url rewrites | |
SELECT * FROM url_rewrite AS ur | |
INNER JOIN catalog_product_entity_varchar AS v ON v.value = LEFT (ur.request_path, LENGTH(ur.request_path)-5) | |
WHERE ur.entity_type = 'custom'; | |
SELECT * FROM catalog_product_entity_varchar AS v | |
INNER JOIN catalog_product_entity_varchar AS v2 ON v.value_id <> v2.value_id AND v.value = TRIM(TRAILING '-' FROM v2.value ) | |
LEFT JOIN catalog_product_entity AS e ON e.row_id = v2.row_id | |
WHERE v.attribute_id = 124 AND v2.attribute_id = 124; | |
# Cron Schedule | |
SELECT job_code, status, created_at, scheduled_at, executed_at, finished_at, TIMEDIFF(finished_at, executed_at) AS duration | |
FROM cron_schedule ORDER BY schedule_id DESC; | |
SELECT job_code, status, created_at, scheduled_at, executed_at, finished_at, TIMEDIFF(finished_at, executed_at) AS duration | |
FROM cron_schedule WHERE job_code LIKE "%index%" ORDER BY schedule_id DESC; | |
SELECT job_code, status, COUNT(*) as count_status FROM cron_schedule GROUP BY job_code, status ORDER BY count_status DESC; | |
for I in 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23; do echo -n "2019-06-17 $I - "; grep "\[2019-06-17 $I" /var/log/cron.log | wc -l; done | |
#EVENTS | |
SELECT event_code, time, action FROM magento_logging_event WHERE event_code LIKE "%backup%"; | |
strace -t -o var/strace.log -s 2048 -f php pub/index.php | |
php bin/magento cron:run --group=index --bootstrap=standaloneProcessStarted=1 | |
curl -O https://gist.githubusercontent.com/yvoronoy/bdc31ca9bb42898189b61ff668b7537a/raw/4021c250b1d56a093f292ee9a1b5928f3047ce8a/MAGENTO_ENABLE_MYSQL_DEBUG.patch | |
patch -p0 < MAGENTO_ENABLE_MYSQL_DEBUG.patch | |
#Elastic | |
curl -XGET localhost:9200/_cat/indices | |
curl -XGET localhost:9200/<index-name>/document/<id> | |
curl -XGET localhost:9200/<index-name>/_search -d ' | |
{ | |
"fields": ["_ids"], | |
"size": 1000 | |
} | |
' | json_pp | grep _id | |
curl -XGET localhost:9200/<index-name>/_search -d ' | |
{ | |
"size": 1000 | |
} | |
' | json_pp | less -S | |
# Get document by product ID | |
curl localhost:9200/magento2_product_7/_search -d ' | |
{ | |
"query": { | |
"match": { | |
"_id": 753 | |
} | |
} | |
}' | |
# Get all images for configurable product | |
SET @entity_id = 236279; #Configurable Id | |
SET @row_id = (SELECT row_id FROM catalog_product_entity WHERE entity_id = @entity_id); | |
SELECT DISTINCT g.value FROM catalog_product_entity_media_gallery_value gv | |
LEFT JOIN catalog_product_entity e ON e.row_id = gv.row_id | |
LEFT JOIN catalog_product_entity_media_gallery g ON g.value_id = gv.value_id | |
WHERE e.entity_id = @entity_id OR e.entity_id IN (SELECT child_id FROM catalog_product_relation WHERE parent_id = @row_id); | |
#debug | |
$data = array( | |
__CLASS__ => time(), | |
); | |
ob_start(); | |
var_dump($data); | |
$data = ob_get_clean(); | |
$backtrace = \Magento\Framework\Debug::backtrace(true, false, true); | |
$message = sprintf("Data: %s %s", $data, $backtrace); | |
file_put_contents('/tmp/magento.support.debug.data.log', $message, FILE_APPEND); | |
#Debug end | |
################### | |
# Debug and catch values | |
################### | |
# vendor/magento/framework/Model/AbstractModel.php | |
# vendor/magento/framework/DataObject.php | |
# Method: setData() | |
if (is_scalar($key)) { | |
$breakpoint = null; // is_scalar($value) && stripos($value, 'xxx') !== false | |
} | |
if (is_array($key)) { | |
foreach ($key as $k => $v) { | |
$breakpoint = null; // is_scalar($v) && stripos($v, 'xxx') !== false | |
} | |
} | |
####################### | |
# Debug and catch SQL | |
####################### | |
vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php::query() | |
// stripos($sql, 'table-name') !== false | |
#Media | |
TMP_MEDIA_FILE=pub/media/catalog/product/p/d/yourmedia.jpg | |
magento@b62318b17328:/var/www/html/c$ find vendor/ -name "*.${TMP_MEDIA_FILE##*.}" | head -n 1 | xargs -t -I {} bash -c "mkdir -p `dirname $TMP_MEDIA_FILE`; cp {} $TMP_MEDIA_FILE" | |
#URL REWRITE | |
SET @default_store_id = ( | |
SELECT store.store_id as default_store_id FROM store_website | |
LEFT JOIN store_group ON store_group.website_id = store_website.website_id | |
LEFT JOIN store ON store.store_id = store_group.default_store_id | |
WHERE is_default = 1 | |
); | |
SELECT @default_store_id; | |
SELECT v.store_id, v.entity_id, v.value, url.* FROM catalog_product_entity_varchar v | |
INNER JOIN catalog_product_entity p ON p.entity_id = v.entity_id | |
INNER JOIN eav_attribute eav ON eav.attribute_id = v.attribute_id AND eav.attribute_code = 'url_key' | |
INNER JOIN eav_entity_type eav_type ON eav_type.entity_type_id = eav.entity_type_id AND eav_type.entity_type_code = 'catalog_product' | |
INNER JOIN store ON store.store_id = v.store_id AND store.store_id = @default_store_id | |
INNER JOIN store_website website ON website.website_id = store.website_id | |
INNER JOIN catalog_product_website ON catalog_product_website.website_id = website.website_id AND v.entity_id = catalog_product_website.product_id | |
LEFT JOIN url_rewrite url ON url.entity_id = v.entity_id | |
WHERE url.request_path IS NULL | |
; | |
#rdb redis report | |
cat c/memory.csv | awk -F ',' '{print $3}' | sort -n | awk -F '_' '{print $2}' | sort -n | uniq -c | sort -k1 | tail | |
206 SERVICEINTERFACEMETHODSMAP | |
264 SERVICE | |
1915 CONFIGURABLE | |
2819 STRUCTURE | |
2834 LAYOUT | |
4109 COLLECTION | |
5477 CATALOG | |
15457 CAT | |
16288 IMG | |
36960 BLOCK |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment