Magento Forums
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго

‎06-29-2017 08:28 AM
‎06-29-2017 08:28 AM

Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго

Интернет-магазин на Мадженто 1. Резко сайт стал загружаться очень долго.

 

Недавно мы установили SSL-сертификат.
Так же установили memory_limit = 3G для PHP 5.6.
И настроили Cron для Magento 1

/usr/bin/php /home/i/ivzhenko/postavki.plus/public_html/cron.php

Кто-то выключил кеш в панели адмиинистратора.

 

Доступы я давал поддержке разработчика темы, поддержке хостинга, поддержке модуля Одностраничная покупка.

 

Так же доступ есть у меня и у владельца сайта.

 

В чем причина этого не ясно.

E2p33X5tB9609m.jpg

SEO-продвижение, создание сайтов.
0 Kudos
Reply
6 REPLIES 6
‎06-29-2017 08:30 AM
‎06-29-2017 08:30 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго

Лог запросов к БД, в нем имеется подробная информация касательно запросов.

Reading from STDIN ...

# 60.7s user time, 4.1s system time, 37.31M rss, 122.27M vsz
# Current date: Thu Jun 29 13:24:22 2017
# Hostname: dock2.beget.ru
# Files: STDIN
# Overall: 34.80k total, 243 unique, 2.43 QPS, 0.76x concurrency _________
# Time range: 2017-06-29T09:25:07 to 2017-06-29T13:24:18
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         10934s   100ms      8s   314ms   992ms   680ms   100ms
# Lock time              0       0       0       0       0       0       0
# Query size        97.44M       6  92.14k   2.87k   4.27k   1.87k   3.69k
# id               377.13G  10.79M  11.40M  11.10M  11.30M 208.76k  10.76M

# Profile
# Rank Query ID           Response time   Calls R/Call V/M   Item
# ==== ================== =============== ===== ====== ===== =============
#    1 0xEB643089DC691D56 2061.7406 18.9%   383 5.3831  0.42 SELECT catalog_category_flat_store_? core_url_rewrite
#    2 0xA8220DB648779954 1869.9102 17.1%  5070 0.3688  0.07 SELECT catalog_product_flat_? catalog_product_index_price catalog_category_product catalog_category_product_index cataloginventory_stock_item report_event sales_flat_order_item review_entity_summary
#    3 0x2ECC0896D47296B4 1842.9372 16.9% 12977 0.1420  0.25 SELECT core_url_rewrite
#    4 0x7910A1361355D37A  920.2086  8.4%   339 2.7145  0.13 SELECT catalog_product_flat_? catalog_product_index_price report_event report_event_types review_entity_summary sales_flat_order_item sales_flat_order
#    5 0xB7AE252245088367  659.2552  6.0%   340 1.9390  0.09 SELECT catalog_product_flat_? catalog_product_index_price report_event report_event_types review_entity_summary sales_flat_order_item sales_flat_order
#    6 0xAFFD52FE50C17FD9  613.4297  5.6%  3721 0.1649  0.06 SELECT po_fpc_popularity
#    7 0x8AAB49CE69E40FA3  598.6388  5.5%  1600 0.3741  0.07 SELECT catalog_product_flat_? catalog_product_index_price catalog_category_product catalog_category_product_index cataloginventory_stock_item report_event sales_flat_order_item review_entity_summary
#    8 0x4220D2B1C805DF2F  341.3640  3.1%  2222 0.1536  0.05 SELECT po_fpc_popularity
#    9 0x6B792BBC2BB7F34C  304.2226  2.8%   356 0.8546  0.24 SELECT catalog_category_flat_store_? core_url_rewrite
#   10 0xB780E3512F42619D  289.1915  2.6%   110 2.6290  0.17 SELECT catalog_product_flat_? catalog_product_index_price report_event report_event_types review_entity_summary sales_flat_order_item sales_flat_order
#   11 0x417FB694EEAB6653  238.6005  2.2%   767 0.3111  0.11 SELECT catalog_product_flat_? catalog_product_index_price catalog_category_product catalog_category_product_index cataloginventory_stock_item report_event sales_flat_order_item review_entity_summary
#   12 0x2BF7CFB45A81D1F7  207.2399  1.9%   107 1.9368  0.06 SELECT catalog_product_flat_? catalog_product_index_price report_event report_event_types review_entity_summary sales_flat_order_item sales_flat_order
#   13 0x20D82A98B4FE5140  147.8540  1.4%   673 0.2197  0.16 SELECT catalog_category_flat_store_? core_url_rewrite
#   14 0x4B9C4C8C2B89052F  129.1835  1.2%   421 0.3068  0.11 SELECT catalog_product_flat_? catalog_product_index_price catalog_category_product catalog_category_product_index cataloginventory_stock_item report_event sales_flat_order_item review_entity_summary
#   15 0xC7633563FBEDE9E5   84.4893  0.8%   814 0.1038  0.01 SELECT cataloginventory_stock_item catalog_product_entity
#   16 0xD938ED3297973570   63.6100  0.6%   619 0.1028  0.01 SELECT UNION catalog_product_entity_decimal eav_entity_attribute catalog_product_entity_varchar eav_entity_attribute catalog_product_entity_int eav_entity_attribute catalog_product_entity_text eav_entity_attribute catalog_product_entity_datetime eav_entity_attribute
#   17 0x58A6FD689ED51D94   62.2021  0.6%   571 0.1089  0.02 SELECT catalog_product_flat_? catalog_product_index_price
# MISC 0xMISC              500.1891  4.6%  3715 0.1346   0.0 <226 ITEMS>

# Query 1: 0.03 QPS, 0.15x concurrency, ID 0xEB643089DC691D56 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.42
# Time range: 2017-06-29T09:25:28 to 2017-06-29T13:14:15
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     383
# Exec time     18   2062s   101ms      8s      5s      6s      2s      6s
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 203.95k     544     546  545.29  537.02       0  537.02
# id             1   4.16G  10.79M  11.37M  11.13M  11.30M 197.58k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ##
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_flat_store_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_flat_store_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'core_url_rewrite'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`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
SEO-продвижение, создание сайтов.
0 Kudos
Reply
‎06-29-2017 08:30 AM
‎06-29-2017 08:30 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго 

# Query 2: 0.35 QPS, 0.13x concurrency, ID 0xA8220DB648779954 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.07
# Time range: 2017-06-29T09:26:11 to 2017-06-29T13:24:17
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14    5070
# Exec time     17   1870s   100ms      1s   369ms   705ms   157ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size    19  18.92M   3.82k   3.82k   3.82k   3.82k       0   3.82k
# id            14  54.83G  10.79M  11.40M  11.07M  11.30M 224.21k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_16'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_16`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product_index'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product_index`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'cataloginventory_stock_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`cataloginventory_stock_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`gender`, `e`.`gender_value`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`image_label`, `e`.`is_recurring`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`occasion`, `e`.`occasion_value`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_category_id`.`category_id`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock`, `mv`.*, `bs`.*, `ra`.`reviews_count` AS `num_reviews_count`, `ra`.`rating_summary` AS `num_rating_summary` FROM `catalog_product_flat_16` AS `e`
 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
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=16 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1))
 LEFT JOIN (SELECT `report_event`.*, COUNT(`event_id`) AS `num_view_counts` FROM `report_event` WHERE (event_type_id = 1) GROUP BY `object_id`) AS `mv` ON mv.object_id = e.entity_id
 LEFT JOIN (SELECT `sales_flat_order_item`.`product_id`, SUM(`qty_ordered`) AS `ordered_qty` FROM `sales_flat_order_item` GROUP BY `product_id`) AS `bs` ON bs.product_id = e.entity_id
 LEFT JOIN `review_entity_summary` AS `ra` ON e.entity_id = ra.entity_pk_value AND ra.store_id=16 WHERE (e.status = 1) GROUP BY `entity_id` ORDER BY `e`.`name` ASC\G

# Query 3: 0.91 QPS, 0.13x concurrency, ID 0x2ECC0896D47296B4 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.25
# Time range: 2017-06-29T09:26:04 to 2017-06-29T13:24:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         37   12977
# Exec time     16   1843s   100ms      6s   142ms   198ms   187ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size    56  55.20M     239   4.38k   4.36k   4.27k  330.98   4.27k
# id            37 140.53G  10.79M  11.40M  11.09M  11.30M 214.81k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ##
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'core_url_rewrite'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`core_url_rewrite`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `core_url_rewrite`.`product_id`, `core_url_rewrite`.`request_path` FROM `core_url_rewrite` WHERE (store_id = 15) AND (is_system = 1) AND (category_id = 0 OR category_id IS NULL) AND (product_id IN('1531', '1534', '2091', '2184', '2185', '2187', '2188', '2189', '2190', '2191', '2192', '2193', '2194', '2195', '2196', '2205', '2206', '2136', '1546', '1552'/*... omitted 512 items ...*/)) ORDER BY `category_id` DESC\G

# Query 4: 0.02 QPS, 0.06x concurrency, ID 0x7910A1361355D37A at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.13
# Time range: 2017-06-29T09:26:02 to 2017-06-29T13:24:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     339
# Exec time      8    920s   100ms      5s      3s      3s   587ms      3s
# Lock time      0       0       0       0       0       0       0       0
# Query size     1   1.61M   4.85k   4.85k   4.85k   4.85k       0   4.85k
# id             0   3.67G  10.79M  11.40M  11.08M  11.30M 226.56k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  #
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_15'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_15`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event_types'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event_types`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`gender`, `e`.`gender_value`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`image_label`, `e`.`is_recurring`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`occasion`, `e`.`occasion_value`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, COUNT(re_table.event_id) AS `sm_views_count`, `rs_table`.`reviews_count` AS `sm_reviews_count`, `rs_table`.`rating_summary` AS `sm_rating_summary`, SUM(oi_table.qty_ordered) AS `sm_ordered_count` FROM `catalog_product_flat_15` AS `e`
 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
 LEFT JOIN `report_event` AS `re_table` ON e.entity_id = re_table.object_id
 LEFT JOIN `report_event_types` AS `ret_table` ON re_table.event_type_id = ret_table.event_type_id AND ret_table.event_name = 'catalog_product_view'
 LEFT JOIN `review_entity_summary` AS `rs_table` ON e.entity_id = rs_table.entity_pk_value AND rs_table.store_id=15
 LEFT JOIN `sales_flat_order_item` AS `oi_table` ON e.entity_id=oi_table.item_id AND oi_table.order_id IN (SELECT `o_table`.`entity_id` FROM `sales_flat_order` AS `o_table` WHERE (o_table.state<>'canceled')) WHERE (e.status = 1) AND (e.status = '1') AND (((e.visibility = '4') OR (e.visibility = '2'))) AND (e.entity_id IN('1361', '1362', '1363', '1364', '1365', '1366', '1367', '1368', '1369', '1370', '1371', '1372', '1373', '1374', '1375', '1376', '1377', '1378', '1379', '1380'/*... omitted 259 items ...*/)) GROUP BY `e`.`entity_id` ORDER BY `sm_ordered_count` desc LIMIT 20\G
SEO-продвижение, создание сайтов.
0 Kudos
Reply
‎06-29-2017 08:38 AM
‎06-29-2017 08:38 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго 

# Query 5: 0.02 QPS, 0.05x concurrency, ID 0xB7AE252245088367 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.09
# Time range: 2017-06-29T09:26:07 to 2017-06-29T13:24:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     340
# Exec time      6    659s   100ms      3s      2s      3s   413ms      2s
# Lock time      0       0       0       0       0       0       0       0
# Query size     1   1.59M   4.79k   4.79k   4.79k   4.79k       0   4.79k
# id             0   3.68G  10.79M  11.40M  11.08M  11.30M 226.32k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ##
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_16'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_16`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event_types'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event_types`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`gender`, `e`.`gender_value`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`image_label`, `e`.`is_recurring`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`occasion`, `e`.`occasion_value`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, COUNT(re_table.event_id) AS `sm_views_count`, `rs_table`.`reviews_count` AS `sm_reviews_count`, `rs_table`.`rating_summary` AS `sm_rating_summary`, SUM(oi_table.qty_ordered) AS `sm_ordered_count` FROM `catalog_product_flat_16` AS `e`
 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
 LEFT JOIN `report_event` AS `re_table` ON e.entity_id = re_table.object_id
 LEFT JOIN `report_event_types` AS `ret_table` ON re_table.event_type_id = ret_table.event_type_id AND ret_table.event_name = 'catalog_product_view'
 LEFT JOIN `review_entity_summary` AS `rs_table` ON e.entity_id = rs_table.entity_pk_value AND rs_table.store_id=16
 LEFT JOIN `sales_flat_order_item` AS `oi_table` ON e.entity_id=oi_table.item_id AND oi_table.order_id IN (SELECT `o_table`.`entity_id` FROM `sales_flat_order` AS `o_table` WHERE (o_table.state<>'canceled')) WHERE (e.status = 1) AND (e.status = '1') AND (((e.visibility = '4') OR (e.visibility = '2'))) AND (e.entity_id IN('1932', '1933', '1934', '1935', '1936', '1937', '1938', '1939', '1940', '1941', '1942', '1943', '1944', '1945', '1946', '1947', '1948', '1949', '1950', '1951'/*... omitted 251 items ...*/)) GROUP BY `e`.`entity_id` ORDER BY `e`.`created_at` desc LIMIT 20\G

# Query 6: 0.27 QPS, 0.04x concurrency, ID 0xAFFD52FE50C17FD9 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.06
# Time range: 2017-06-29T09:25:28 to 2017-06-29T13:14:54
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10    3721
# Exec time      5    613s   100ms      1s   165ms   393ms    98ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 693.33k     134     263  190.80  212.52   17.57  183.58
# id            10  40.36G  10.79M  11.37M  11.11M  11.30M 194.58k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'po_fpc_popularity'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`po_fpc_popularity`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.* FROM `po_fpc_popularity` AS `main_table` WHERE (`request_url` = 'https://postavki.plus/ukraine/gidravlika/gidromotory-gidronasosy.html') AND (`store_id` = '16')\G

# Query 7: 0.14 QPS, 0.05x concurrency, ID 0x8AAB49CE69E40FA3 at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.07
# Time range: 2017-06-29T09:56:57 to 2017-06-29T13:12:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4    1600
# Exec time      5    599s   100ms      1s   374ms   992ms   166ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     6   5.96M   3.82k   3.82k   3.82k   3.82k       0   3.82k
# id             4  17.35G  10.86M  11.37M  11.10M  11.30M 179.91k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product_index'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product_index`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'cataloginventory_stock_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`cataloginventory_stock_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`gender`, `e`.`gender_value`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`occasion`, `e`.`occasion_value`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`color`, `e`.`color_value`, `e`.`status`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_category_id`.`category_id`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock`, `mv`.*, `bs`.*, `ra`.`reviews_count` AS `num_reviews_count`, `ra`.`rating_summary` AS `num_rating_summary` FROM `catalog_product_flat_1` AS `e`
 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
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 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 = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1))
 LEFT JOIN (SELECT `report_event`.*, COUNT(`event_id`) AS `num_view_counts` FROM `report_event` WHERE (event_type_id = 1) GROUP BY `object_id`) AS `mv` ON mv.object_id = e.entity_id
 LEFT JOIN (SELECT `sales_flat_order_item`.`product_id`, SUM(`qty_ordered`) AS `ordered_qty` FROM `sales_flat_order_item` GROUP BY `product_id`) AS `bs` ON bs.product_id = e.entity_id
 LEFT JOIN `review_entity_summary` AS `ra` ON e.entity_id = ra.entity_pk_value AND ra.store_id=1 WHERE (e.status = 1) GROUP BY `entity_id` ORDER BY `e`.`name` ASC\G

# Query 8: 0.16 QPS, 0.02x concurrency, ID 0x4220D2B1C805DF2F at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2017-06-29T09:26:01 to 2017-06-29T13:24:18
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6    2222
# Exec time      3    341s   100ms      1s   154ms   293ms    88ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 443.43k     156     287  204.35  234.30   22.17  192.76
# id             6  24.15G  10.79M  11.40M  11.13M  11.30M 189.93k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'po_fpc_popularity'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`po_fpc_popularity`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.* FROM `po_fpc_popularity` AS `main_table` WHERE (`request_url` = 'https://postavki.plus/ukraine/specialni-propozycii''___from_store=ukraine') AND (`store_id` = '16')\G
SEO-продвижение, создание сайтов.
0 Kudos
Reply
‎06-29-2017 08:39 AM
‎06-29-2017 08:39 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго 

# Query 9: 0.03 QPS, 0.02x concurrency, ID 0x6B792BBC2BB7F34C at byte 0 __
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.24
# Time range: 2017-06-29T09:33:13 to 2017-06-29T13:14:26
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     356
# Exec time      2    304s   100ms      2s   855ms      1s   453ms   580ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 196.95k     563     575  566.50  563.87       6  563.87
# id             1   3.87G  10.81M  11.37M  11.14M  11.30M 164.10k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #############################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_flat_store_16'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_flat_store_16`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'core_url_rewrite'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`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_16` 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 = 16 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/%') AND (`level` <= 2) ORDER BY `main_table`.`position` ASC\G

# Query 10: 0.01 QPS, 0.02x concurrency, ID 0xB780E3512F42619D at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.17
# Time range: 2017-06-29T09:56:49 to 2017-06-29T13:12:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     110
# Exec time      2    289s   100ms      4s      3s      3s   678ms      3s
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 533.35k   4.85k   4.85k   4.85k   4.85k       0   4.85k
# id             0   1.19G  10.86M  11.37M  11.11M  11.30M 183.69k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ###
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event_types'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event_types`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`gender`, `e`.`gender_value`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`occasion`, `e`.`occasion_value`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`color`, `e`.`color_value`, `e`.`status`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, COUNT(re_table.event_id) AS `sm_views_count`, `rs_table`.`reviews_count` AS `sm_reviews_count`, `rs_table`.`rating_summary` AS `sm_rating_summary`, SUM(oi_table.qty_ordered) AS `sm_ordered_count` FROM `catalog_product_flat_1` AS `e`
 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
 LEFT JOIN `report_event` AS `re_table` ON e.entity_id = re_table.object_id
 LEFT JOIN `report_event_types` AS `ret_table` ON re_table.event_type_id = ret_table.event_type_id AND ret_table.event_name = 'catalog_product_view'
 LEFT JOIN `review_entity_summary` AS `rs_table` ON e.entity_id = rs_table.entity_pk_value AND rs_table.store_id=1
 LEFT JOIN `sales_flat_order_item` AS `oi_table` ON e.entity_id=oi_table.item_id AND oi_table.order_id IN (SELECT `o_table`.`entity_id` FROM `sales_flat_order` AS `o_table` WHERE (o_table.state<>'canceled')) WHERE (e.status = 1) AND (e.status = '1') AND (((e.visibility = '4') OR (e.visibility = '2'))) AND (e.entity_id IN('1361', '1362', '1363', '1364', '1365', '1366', '1367', '1368', '1369', '1370', '1371', '1372', '1373', '1374', '1375', '1376', '1377', '1378', '1379', '1380'/*... omitted 259 items ...*/)) GROUP BY `e`.`entity_id` ORDER BY `sm_ordered_count` desc LIMIT 20\G

# Query 11: 0.06 QPS, 0.02x concurrency, ID 0x417FB694EEAB6653 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.11
# Time range: 2017-06-29T09:30:44 to 2017-06-29T13:14:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     767
# Exec time      2    239s   101ms      1s   311ms   900ms   184ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     3   3.12M   4.17k   4.17k   4.17k   4.17k       0   4.17k
# id             2   8.36G  10.80M  11.37M  11.16M  11.30M 214.08k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_15'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_15`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product_index'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product_index`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'cataloginventory_stock_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`cataloginventory_stock_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`gender`, `e`.`gender_value`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`image_label`, `e`.`is_recurring`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`occasion`, `e`.`occasion_value`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_category_id`.`category_id`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock`, `mv`.*, `bs`.*, `ra`.`reviews_count` AS `num_reviews_count`, `ra`.`rating_summary` AS `num_rating_summary` FROM `catalog_product_flat_15` AS `e`
 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
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=15 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1))
 LEFT JOIN (SELECT `report_event`.*, COUNT(`event_id`) AS `num_view_counts` FROM `report_event` WHERE (event_type_id = 1) GROUP BY `object_id`) AS `mv` ON mv.object_id = e.entity_id
 LEFT JOIN (SELECT `sales_flat_order_item`.`product_id`, SUM(`qty_ordered`) AS `ordered_qty` FROM `sales_flat_order_item` GROUP BY `product_id`) AS `bs` ON bs.product_id = e.entity_id
 LEFT JOIN `review_entity_summary` AS `ra` ON e.entity_id = ra.entity_pk_value AND ra.store_id=15 WHERE (e.status = 1) AND ((at_category_id.category_id IN('80', '5', '134', '135', '10', '17', '88', '14', '115', '111', '50', '116', '11', '16', '72', '73', '12', '13', '52', '15'/*... omitted 31 items ...*/))) GROUP BY `entity_id` LIMIT 3\G

# Query 12: 0.01 QPS, 0.02x concurrency, ID 0x2BF7CFB45A81D1F7 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.06
# Time range: 2017-06-29T09:56:53 to 2017-06-29T13:12:20
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     107
# Exec time      1    207s   502ms      3s      2s      2s   348ms      2s
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 511.91k   4.78k   4.78k   4.78k   4.78k       0   4.78k
# id             0   1.16G  10.86M  11.37M  11.11M  11.30M 180.07k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  #
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event_types'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event_types`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`gender`, `e`.`gender_value`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`occasion`, `e`.`occasion_value`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`color`, `e`.`color_value`, `e`.`status`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, COUNT(re_table.event_id) AS `sm_views_count`, `rs_table`.`reviews_count` AS `sm_reviews_count`, `rs_table`.`rating_summary` AS `sm_rating_summary`, SUM(oi_table.qty_ordered) AS `sm_ordered_count` FROM `catalog_product_flat_1` AS `e`
 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
 LEFT JOIN `report_event` AS `re_table` ON e.entity_id = re_table.object_id
 LEFT JOIN `report_event_types` AS `ret_table` ON re_table.event_type_id = ret_table.event_type_id AND ret_table.event_name = 'catalog_product_view'
 LEFT JOIN `review_entity_summary` AS `rs_table` ON e.entity_id = rs_table.entity_pk_value AND rs_table.store_id=1
 LEFT JOIN `sales_flat_order_item` AS `oi_table` ON e.entity_id=oi_table.item_id AND oi_table.order_id IN (SELECT `o_table`.`entity_id` FROM `sales_flat_order` AS `o_table` WHERE (o_table.state<>'canceled')) WHERE (e.status = 1) AND (e.status = '1') AND (((e.visibility = '4') OR (e.visibility = '2'))) AND (e.entity_id IN('1932', '1933', '1934', '1935', '1936', '1937', '1938', '1939', '1940', '1941', '1942', '1943', '1944', '1945', '1946', '1947', '1948', '1949', '1950', '1951'/*... omitted 251 items ...*/)) GROUP BY `e`.`entity_id` ORDER BY `e`.`created_at` desc LIMIT 20\G
SEO-продвижение, создание сайтов.
0 Kudos
Reply
‎06-29-2017 08:40 AM
‎06-29-2017 08:40 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго 

# Query 13: 0.05 QPS, 0.01x concurrency, ID 0x20D82A98B4FE5140 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.16
# Time range: 2017-06-29T09:25:56 to 2017-06-29T13:14:31
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     673
# Exec time      1    148s   100ms      1s   220ms   992ms   189ms   198ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 290.52k     433     451  442.04  441.81       9  420.77
# id             1   7.32G  10.79M  11.37M  11.14M  11.30M 217.59k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_flat_store_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_flat_store_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'core_url_rewrite'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`core_url_rewrite`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.`entity_id`, `main_table`.`name`, `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.entity_id IN ('72', '80')) AND (main_table.is_active = '1') ORDER BY `main_table`.`path` ASC\G

# Query 14: 0.03 QPS, 0.01x concurrency, ID 0x4B9C4C8C2B89052F at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.11
# Time range: 2017-06-29T09:26:04 to 2017-06-29T13:12:13
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     421
# Exec time      1    129s   101ms      1s   307ms   777ms   182ms   293ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1   1.71M   4.17k   4.17k   4.17k   4.17k       0   4.17k
# id             1   4.56G  10.79M  11.37M  11.09M  11.30M 141.79k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  ###
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_1'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_1`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_category_product_index'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_category_product_index`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'cataloginventory_stock_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`cataloginventory_stock_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'report_event'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`report_event`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'sales_flat_order_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`sales_flat_order_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'review_entity_summary'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`review_entity_summary`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT DISTINCT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`gender`, `e`.`gender_value`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`occasion`, `e`.`occasion_value`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`color`, `e`.`color_value`, `e`.`status`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `at_category_id`.`category_id`, `cat_index`.`position` AS `cat_index_position`, `at_inventory_in_stock`.`is_in_stock` AS `inventory_in_stock`, `mv`.*, `bs`.*, `ra`.`reviews_count` AS `num_reviews_count`, `ra`.`rating_summary` AS `num_rating_summary` FROM `catalog_product_flat_1` AS `e`
 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
 LEFT JOIN `catalog_category_product` AS `at_category_id` ON (at_category_id.`product_id`=e.entity_id)
 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 = '2'
 INNER JOIN `cataloginventory_stock_item` AS `at_inventory_in_stock` ON (at_inventory_in_stock.`product_id`=e.entity_id) AND ((at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=1 AND at_inventory_in_stock.is_in_stock=1) OR (at_inventory_in_stock.use_config_manage_stock = 0 AND at_inventory_in_stock.manage_stock=0) OR (at_inventory_in_stock.use_config_manage_stock = 1))
 LEFT JOIN (SELECT `report_event`.*, COUNT(`event_id`) AS `num_view_counts` FROM `report_event` WHERE (event_type_id = 1) GROUP BY `object_id`) AS `mv` ON mv.object_id = e.entity_id
 LEFT JOIN (SELECT `sales_flat_order_item`.`product_id`, SUM(`qty_ordered`) AS `ordered_qty` FROM `sales_flat_order_item` GROUP BY `product_id`) AS `bs` ON bs.product_id = e.entity_id
 LEFT JOIN `review_entity_summary` AS `ra` ON e.entity_id = ra.entity_pk_value AND ra.store_id=1 WHERE (e.status = 1) AND ((at_category_id.category_id IN('51', '10', '14', '46', '132', '114', '116', '50', '111', '80', '115', '87', '5', '52', '73', '134', '4', '135', '49', '12'/*... omitted 31 items ...*/))) GROUP BY `entity_id` LIMIT 3\G

# Query 15: 0.06 QPS, 0.01x concurrency, ID 0xC7633563FBEDE9E5 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2017-06-29T09:26:02 to 2017-06-29T13:24:05
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2     814
# Exec time      0     84s   100ms      1s   104ms   100ms    36ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 188.40k     237     237     237     237       0     237
# id             2   8.81G  10.79M  11.40M  11.08M  11.30M 211.46k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'cataloginventory_stock_item'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`cataloginventory_stock_item`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `cataloginventory_stock_item`.*, `p`.`type_id` FROM `cataloginventory_stock_item`
 INNER JOIN `catalog_product_entity` AS `p` ON product_id=p.entity_id WHERE (`cataloginventory_stock_item`.`product_id`='2233') AND (stock_id = '1')\G

# Query 16: 0.04 QPS, 0.00x concurrency, ID 0xD938ED3297973570 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2017-06-29T09:26:10 to 2017-06-29T13:24:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     619
# Exec time      0     64s   100ms      1s   103ms   100ms    37ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     0 910.26k   1.47k   1.47k   1.47k   1.46k       0   1.46k
# id             1   6.71G  10.79M  11.40M  11.10M  11.30M 219.93k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity_decimal'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity_decimal`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'eav_entity_attribute'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`eav_entity_attribute`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity_varchar'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity_varchar`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity_int'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity_int`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity_text'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity_text`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_entity_datetime'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_entity_datetime`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `attr_table`.* FROM `catalog_product_entity_decimal` AS `attr_table`
 INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '2212') AND (attr_table.store_id IN (0, 16)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_varchar` AS `attr_table`
 INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '2212') AND (attr_table.store_id IN (0, 16)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_int` AS `attr_table`
 INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '2212') AND (attr_table.store_id IN (0, 16)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_text` AS `attr_table`
 INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '2212') AND (attr_table.store_id IN (0, 16)) UNION ALL SELECT `attr_table`.* FROM `catalog_product_entity_datetime` AS `attr_table`
 INNER JOIN `eav_entity_attribute` AS `set_table` ON attr_table.attribute_id = set_table.attribute_id AND set_table.attribute_set_id = '4' WHERE (attr_table.entity_id = '2212') AND (attr_table.store_id IN (0, 16)) ORDER BY `store_id` ASC\G

# Query 17: 0.04 QPS, 0.00x concurrency, ID 0x58A6FD689ED51D94 at byte 0 _
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2017-06-29T09:26:13 to 2017-06-29T13:24:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1     571
# Exec time      0     62s   100ms   703ms   109ms   100ms    48ms   100ms
# Lock time      0       0       0       0       0       0       0       0
# Query size     1   1.33M   2.38k   2.38k   2.38k   2.38k       0   2.38k
# id             1   6.18G  10.79M  11.40M  11.09M  11.30M 223.86k  10.76M
# String:
# Databases    ivzhenko_magento
# Hosts        localhost
# Users        ivzhenko_magento
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_flat_15'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_flat_15`\G
#    SHOW TABLE STATUS FROM `ivzhenko_magento` LIKE 'catalog_product_index_price'\G
#    SHOW CREATE TABLE `ivzhenko_magento`.`catalog_product_index_price`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`open_amount_min`, `e`.`open_amount_max`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`accessories_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`color`, `e`.`color_value`, `e`.`cost`, `e`.`created_at`, `e`.`gender`, `e`.`gender_value`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`home_decor_type`, `e`.`home_decor_type_value`, `e`.`image_label`, `e`.`is_recurring`, `e`.`jewelry_type`, `e`.`jewelry_type_value`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`necklace_length`, `e`.`necklace_length_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`occasion`, `e`.`occasion_value`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`shoe_type`, `e`.`shoe_type_value`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`status`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_flat_15` AS `e`
 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 WHERE (e.status = 1)\G

Жаль нельзя вложить файл. Пришлось все в постах выкладывать.

SEO-продвижение, создание сайтов.
0 Kudos
Reply
‎06-29-2017 09:06 AM
‎06-29-2017 09:06 AM

Re: Очень большое превышение нагрузки на базу данных - Сайт стал загружаться долго

Только пароль забыли выложить, все остальное есть. А позже будете спрашивать как восстановить сайт после взлома.

Тут собственно даже хакером быть не нужно

0 Kudos
Reply