Большая нагрузка mysql

Форум поддержки » Вопросы и ответы

Здравствуйте, в последнее время наблюдается большая нагрузка, а именно запросы вида:

CREATE TABLE shop_product_promo_price_tmp
DROP TABLE shop_product_promo_price_tmp
SHOW TABLE STATUS FROM --- LIKE ---
SHOW CREATE TABLE ----

4 ответа

  • 4
    Михаил Анатольевич Лацоев 21 октября 2019 11:15 #

    Началось все после обновления, лог:

    2019-10-10T16:00:15+03:0000001  B5F     TRACE   1.59Mb  1.70Mb  flush_heartbeat waInstaller::setState {------------------------------------------------------------ array (  'start_time' => 1570712415,  'start_time_float' => 1570712415.1213729381561279296875,  'elapsed_time' => 0.0,  'stage' => 1,  'stage_name' => 'flush',  'state_heartbeat' => 0.2299659252166748046875,  'thread_id' => 'B5F',  'chunk_id' => NULL,  'stage_status' => 'heartbeat',  'timestamp' => 1570712415,  'timestamp_float' => 1570712415.351337909698486328125,  'stage_performance' => 0,  'stage_current_value' => 0,  'stage_start_time' => 1570712415.351337909698486328125,  'datetime' => 'Thu, 10 Oct 2019 16:00:15 +0300',  'stage_value' => 0, ) }------------------------------------------------------------ 2019-10-10T16:00:15+03:0000002  B5F     TRACE   1.59Mb  1.70Mb  flush_complete waInstaller::setState {------------------------------------------------------------ array (  'start_time' => 1570712415,  'start_time_float' => 1570712415.1213729381561279296875,  'elapsed_time' => 0.0,  'stage' => 1,  'stage_name' => 'flush',  'state_heartbeat' => 0.001842975616455078125,  'thread_id' => 'B5F',  'chunk_id' => NULL,  'stage_status' => 'complete',  'timestamp' => 1570712415,  'timestamp_float' => 1570712415.3531780242919921875,  'stage_performance' => 0,  'stage_current_value' => 0,  'stage_start_time' => 1570712415.351337909698486328125,  'datetime' => 'Thu, 10 Oct 2019 16:00:15 +0300',  'stage_value' => 0, ) }------------------------------------------------------------ 2019-10-10T16:00:15+03:0000003  B5F     TRACE   1.67Mb  1.70Mb  flush_complete waInstaller::__destruct called

    Вот так выглядят запросы (имена бд убрал):

    # 162.4s user time, 620ms system time, 32.03M rss, 88.39M vsz
    # Current date: Sat Oct 19 15:16:22 2019
    # Overall: 710 total, 9 unique, 0.01 QPS, 0.03x concurrency ______________
    # Time range: 2019-10-18T21:02:52 to 2019-10-19T12:15:15
    # Attribute total min max avg 95% stddev median
    # ============ ======= ======= ======= ======= ======= ======= =======
    # Exec time 1660s 2s 5s 2s 3s 405ms 2s
    # Lock time 3s 0 425ms 4ms 0 40ms 0
    # Rows sent 40 0 10 0.06 0 0.62 0
    # Rows examine 19.15k 0 9.45k 27.62 0 506.34 0
    # Rows affecte 6.62k 0 3.31k 9.55 0 173.09 0
    # Bytes sent 85.54k 11 33.85k 123.37 10.84 1.76k 10.84
    # Query size 636.44k 37 957 917.91 918.49 167.98 918.49
    # Profile
    # Rank Query ID Response time Calls R/Call V/M Item
    # ==== ================== =============== ===== ====== ===== =============
    # 1 0xCDF0D1CFCAFF7CB3 1566.7169 94.4% 674 2.3245 0.06 CREATE TABLE shop_product_promo_price_tmp
    # 2 0x61B3CE3F6B05E12B 28.5112 1.7% 12 2.3759 0.06 DROP TABLE shop_product_promo_price_tmp
    # MISC 0xMISC 64.7040 3.9% 24 2.6960 0.0 <7 ITEMS>
    # Query 1: 0.01 QPS, 0.03x concurrency, ID 0xCDF0D1CFCAFF7CB3 at byte 232466559
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.06
    # Time range: 2019-10-18T21:02:52 to 2019-10-19T12:15:15
    # Attribute pct total min max avg 95% stddev median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count 94 674
    # Exec time 94 1567s 2s 4s 2s 3s 376ms 2s
    # Lock time 0 0 0 0 0 0 0 0
    # Rows sent 0 0 0 0 0 0 0 0
    # Rows examine 0 0 0 0 0 0 0 0
    # Rows affecte 0 0 0 0 0 0 0 0
    # Bytes sent 8 7.24k 11 11 11 11 0 11
    # Query size 98 629.90k 957 957 957 957 0 957
    # String:
    # Hosts localhost
    # Last errno 0
    # Query_time distribution
    # 1us
    # 10us
    # 100us
    # 1ms
    # 10ms
    # 100ms
    # 1s ################################################################
    # 10s+
    # Tables
    # SHOW TABLE STATUS FROM `---` LIKE 'shop_product_promo_price_tmp'\G
    # SHOW CREATE TABLE `---`.`shop_product_promo_price_tmp`\G
    CREATE TEMPORARY TABLE IF NOT EXISTS shop_product_promo_price_tmp (
    storefront VARCHAR(255) NOT NULL,
    product_id INT(11) NOT NULL,
    sku_id INT(11) NOT NULL,
    price DECIMAL(15, 4) DEFAULT '0.0000' NOT NULL,
    primary_price DECIMAL(15, 4) DEFAULT '0.0000' NOT NULL,
    compare_price DECIMAL(15, 4) DEFAULT '0.0000' NOT NULL,
    primary_compare_price DECIMAL(15, 4) DEFAULT '0.0000' NOT NULL,
    INDEX `storefront` (`storefront`),
    INDEX `product_id` (`product_id`),
    INDEX `sku_id` (`sku_id`),
    INDEX `primary_price` (`primary_price`),
    unique (storefront, product_id, sku_id)
    ) DEFAULT CHARSET utf8\G
    # Query 2: 0.00 QPS, 0.00x concurrency, ID 0x61B3CE3F6B05E12B at byte 438726794
    # This item is included in the report because it matches --limit.
    # Scores: V/M = 0.06
    # Time range: 2019-10-19T01:22:08 to 2019-10-19T12:08:16
    # Attribute pct total min max avg 95% stddev median
    # ============ === ======= ======= ======= ======= ======= ======= =======
    # Count 1 12
    # Exec time 1 29s 2s 3s 2s 3s 363ms 2s
    # Lock time 0 0 0 0 0 0 0 0
    # Rows sent 0 0 0 0 0 0 0 0
    # Rows examine 0 0 0 0 0 0 0 0
    # Rows affecte 0 0 0 0 0 0 0 0
    # Bytes sent 0 132 11 11 11 11 0 11
    # Query size 0 708 59 59 59 59 0 59
    # String:
    # Hosts localhost
    # Last errno 0
    # Query_time distribution
    # 1us
    # 10us
    # 100us
    # 1ms
    # 10ms
    # 100ms
    # 1s ################################################################
    # 10s+
    # Tables
    # SHOW TABLE STATUS FROM `---` LIKE 'shop_product_promo_price_tmp'\G
    # SHOW CREATE TABLE `---`.`shop_product_promo_price_tmp`\G
    DROP TEMPORARY TABLE IF EXISTS shop_product_promo_price_tmp\G
    ответить
  • 4
    Андрей sverkunov.ru 21 октября 2019 12:44 #

    Тоже наблюдаю после обновления запрос в slow-log. 

    ответить
  • 4
    Григорий 21 октября 2019 22:10 #

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

    ответить
  • 2
    Yaroslav 21 октября 2019 00:49 #

    А после чего или при каких обстоятельствах Вы заметили нагрузку?

    ответить

Добавить ответ

Чтобы добавить комментарий, зарегистрируйтесь или войдите