Всем добрый день.
Уже неделю сильно превысилась нагрузка на базу данных MySql сайта. Я не могу определить причину. На сайт поставили ограничения, в админку зайти не могу.
Удалила логи с базы данных, подключила Memcached и Redis. Но отчеты читать не могу, понимаю, что с каталогами проблема...Можете подсказать, в чем причина и в какую сторону капать? И решит ли проблему установка плагина Полностраничного кеша? Буду рада любым мыслям, что искать и в какую сторону смотреть, и вообще, что с этим делают?
Логирование долгих запросов к базе выдал отчет (многое повторяется, поэтому сократила схожие):
# A software update is available:
# * The current version for Percona::Toolkit is 3.0.5
Reading from STDIN ...
DBI connect(';host=hosthost;mysql_read_default_group=client','BD_a_0_f',...) failed: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) at /usr/local/bin/pt-query-digest line 1001.
# 36.9s user time, 3.4s system time, 32.55M rss, 113.36M vsz
# Current date: Sun Mar 3 18:50:26 2019
# Hostname: callisto.beget.ru
# Files: STDIN
# Overall: 12.12k total, 105 unique, 0.84 QPS, 0.81x concurrency _________
# Time range: 2019-03-03T14:50:26 to 2019-03-03T18:50:22
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 11661s 100ms 394s 962ms 2s 5s 100ms
# Lock time 0 0 0 0 0 0 0
# Query size 6.91M 6 2.07k 597.69 1.53k 345.91 537.02
# id 22.24G 211 53.62M 1.88M 871.90k 8.30M 440.37k
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================ ===== ====== ===== ============
# 1 0xEB643089DC691D56 10559.7760 90.6% 4667 2.2626 22.93 SELECT catalog_category_flat_store_? core_url_rewrite
# 2 0x20D82A98B4FE5140 398.2087 3.4% 3037 0.1311 0.20 SELECT catalog_category_flat_store_? core_url_rewrite
# 3 0x723CB561EE729BE7 101.7170 0.9% 444 0.2291 0.15 SELECT catalog_category_entity catalog_category_entity_int core_url_rewrite
# 4 0x52C86F2562BB8F86 93.1478 0.8% 444 0.2098 0.52 SELECT catalog_product_flat_? catalog_category_product_index catalog_product_index_price catalog_product_index_eav
# MISC 0xMISC 508.0706 4.4% 3526 0.1441 0.0 <101 ITEMS>
# Query 1: 0.32 QPS, 0.73x concurrency, ID 0xEB643089DC691D56 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 22.93
# Time range: 2019-03-03T14:50:26 to 2019-03-03T18:50:22
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 38 4667
# Exec time 90 10560s 100ms 394s 2s 3s 7s 2s
# Lock time 0 0 0 0 0 0 0 0
# Query size 35 2.42M 544 544 544 544 0 544
# id 40 8.92G 211 53.62M 1.96M 871.90k 8.53M 419.40k
# String:
# Databases BD_a_0_f
# Hosts hosthost
# Users BD_a_0_f
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ##
# 1s ################################################################
# 10s+ #
# Tables
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_category_flat_store_1'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_category_flat_store_1`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'core_url_rewrite'\G
# SHOW CREATE TABLE `BD_a_0_f`.`core_url_rewrite`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.`entity_id`, main_table.`name`, main_table.`path`, `main_table`.`is_active`, `main_table`.`is_anchor`, `url_rewrite`.`request_path` FROM `catalog_category_flat_store_1` AS `main_table`
LEFT JOIN `core_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 1 AND url_rewrite.id_path LIKE 'category/%' WHERE (main_table.include_in_menu = '1') AND (main_table.is_active = '1') AND (main_table.path like '1/2/%') ORDER BY `main_table`.`position` ASC\G
////////////
# Query 10: 0.03 QPS, 0.00x concurrency, ID 0x0E81A911B2AB9098 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2019-03-13T16:35:18 to 2019-03-13T20:35:01
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 456
# Exec time 1 46s 100ms 604ms 102ms 100ms 23ms 100ms
# Lock time 0 0 0 0 0 0 0 0
# Query size 1 498.39k 1.06k 1.13k 1.09k 1.09k 25.10 1.09k
# id 1 6.39G 13.90M 14.77M 14.35M 14.43M 347.79k 13.74M
# String:
# Databases BD_a_0_f
# Hosts hosthost
# Users BD_a_0_f
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_flat_1'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_flat_1`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_category_product_index'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_category_product_index`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_index_price'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_index_price`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_index_eav'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_index_eav`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_entity_datetime'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_entity_datetime`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT COUNT(DISTINCT e.entity_id) FROM `catalog_product_flat_1` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '52'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_product_index_eav` AS `manufacturer_idx` ON manufacturer_idx.entity_id = e.entity_id AND manufacturer_idx.attribute_id = '81' AND manufacturer_idx.store_id = 1 AND manufacturer_idx.value IN('148', '313', '314', '318', '321', '348', '463', '483', '503', '737', '771', '852')
LEFT JOIN `catalog_product_entity_datetime` AS `newsFromDate` ON newsFromDate.`entity_id`=`e`.entity_id AND
`newsFromDate`.`attribute_id` = 93
LEFT JOIN `catalog_product_entity_datetime` AS `newsToDate` ON newsToDate.`entity_id`=`e`.entity_id AND
`newsToDate`.`attribute_id` = 94 WHERE (e.status = 1) AND (newsFromDate.value < NOW()) AND (newsToDate.value > NOW() OR newsToDate.value IS NULL)\G
/////////////////
# Query 15: 0.02 QPS, 0.00x concurrency, ID 0x92CE243BC286553B at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-03-13T16:35:28 to 2019-03-13T20:34:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 345
# Exec time 0 35s 100ms 214ms 101ms 100ms 6ms 100ms
# Lock time 0 0 0 0 0 0 0 0
# Query size 1 336.63k 971 1.00k 999.17 1012.63 18.51 964.41
# id 1 4.84G 13.90M 14.77M 14.36M 14.43M 350.30k 13.74M
# String:
# Databases BD_a_0_f
# Hosts hosthost
# Users BD_a_0_f
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_flat_1'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_flat_1`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_category_product_index'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_category_product_index`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_index_price'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_index_price`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'catalog_product_index_eav'\G
# SHOW CREATE TABLE `BD_a_0_f`.`catalog_product_index_eav`\G
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'cataloginventory_stock_status'\G
# SHOW CREATE TABLE `BD_a_0_f`.`cataloginventory_stock_status`\G
# EXPLAIN /*!50100 PARTITIONS*/
select SUM(stock.salable) as in_stock, COUNT(stock.salable) - SUM(stock.salable) as out_stock from (SELECT DISTINCT `stock_status`.`stock_status` AS `salable`, `e`.`entity_id` FROM `catalog_product_flat_1` AS `e`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '52'
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN `catalog_product_index_eav` AS `manufacturer_idx` ON manufacturer_idx.entity_id = e.entity_id AND manufacturer_idx.attribute_id = '81' AND manufacturer_idx.store_id = 1 AND manufacturer_idx.value IN('148', '314', '321', '463', '483', '737')
LEFT JOIN `cataloginventory_stock_status` AS `stock_status` ON e.entity_id = stock_status.product_id AND stock_status.website_id=1 WHERE (e.status = 1)) as stock\G
//////////////////////////////////////
# Query 20: 0.02 QPS, 0.00x concurrency, ID 0x1765417248CA7A0F at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2019-03-13T16:35:12 to 2019-03-13T20:34:45
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 291
# Exec time 0 29s 100ms 201ms 101ms 100ms 6ms 100ms
# Lock time 0 0 0 0 0 0 0 0
# Query size 0 37.23k 131 131 131 131 0 131
# id 1 4.08G 13.90M 14.77M 14.36M 14.43M 347.49k 13.74M
# String:
# Databases BD_a_0_f
# Hosts hosthost
# Users BD_a_0_f
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `BD_a_0_f` LIKE 'magegiant_dailydeal'\G
# SHOW CREATE TABLE `BD_a_0_f`.`magegiant_dailydeal`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.* FROM `magegiant_dailydeal` AS `main_table` WHERE (`status` = '3') AND ((close_time <= '2019-03-13 18:22:19'))\G
