Closed meihei3 closed 7 months ago
# 32.8s user time, 140ms system time, 42.10M rss, 105.98M vsz # Current date: Sat Feb 17 04:19:24 2024 # Hostname: ip-172-31-0-240 # Files: /var/log/mysql/slow.log # Overall: 536.29k total, 66 unique, 3.72k QPS, 1.53x concurrency ________ # Time range: 2024-02-17T04:14:31 to 2024-02-17T04:16:55 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 220s 1us 604ms 410us 384us 7ms 54us # Lock time 6s 0 13ms 10us 18us 133us 0 # Rows sent 230.38k 0 49 0.44 0.99 2.18 0 # Rows examine 39.78M 0 48.91k 77.78 0.99 1.75k 0 # Query size 138.18M 0 913.29k 270.18 40.45 12.44k 31.70 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ====== ====== ===== ============== # 1 0x930FF1E60F9B9ED6 60.9887 27.7% 688 0.0886 0.06 SELECT items # 2 0x93761CC3349B416F 27.5705 12.5% 117 0.2356 0.06 SELECT items # 3 0x99AA0165670CE848 25.2455 11.5% 178132 0.0001 0.01 ADMIN PREPARE # 4 0xD12C53668328DB0D 22.2226 10.1% 115890 0.0002 0.00 SELECT categories # 5 0x312243B842F41381 21.4706 9.8% 171 0.1256 0.06 SELECT items # 6 0x36DB5799406088D9 14.3239 6.5% 116 0.1235 0.06 SELECT items # 7 0xE070DA9421CA8C8D 12.6443 5.7% 53066 0.0002 0.00 SELECT users # 8 0x2C1B6BE83F13FC2B 10.5266 4.8% 122 0.0863 0.04 SELECT items # 9 0x9A74397A1FB13CDE 8.4788 3.9% 98 0.0865 0.07 SELECT items # 10 0x4C0C6A2C3174E3A8 5.4135 2.5% 59 0.0918 0.03 SELECT items # 11 0x54AA9C3947003532 3.1181 1.4% 100 0.0312 0.01 INSERT items # MISC 0xMISC 7.9169 3.6% 187730 0.0000 0.0 <55 ITEMS> # Query 1: 11.28 QPS, 1.00x concurrency, ID 0x930FF1E60F9B9ED6 at byte 143239941 # This item is included in the report because it matches --limit. # Scores: V/M = 0.06 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 688 # Exec time 27 61s 7ms 448ms 89ms 230ms 75ms 59ms # Lock time 0 47ms 27us 2ms 68us 224us 106us 36us # Rows sent 14 32.92k 49 49 49 49 0 49 # Rows examine 24 9.61M 3.84k 48.90k 14.30k 46.68k 15.51k 6.96k # Query size 0 169.72k 248 257 252.61 246.02 0 246.02 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # # 10ms ################################################################ # 100ms ################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE `status` IN ('on_sale','sold_out') AND category_id IN (21, 22, 23, 24) AND (`created_at` < '2019-08-12 15:23:18' OR (`created_at` <= '2019-08-12 15:23:18' AND `id` < 48197)) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G # Query 2: 2.13 QPS, 0.50x concurrency, ID 0x93761CC3349B416F at byte 131689075 # This item is included in the report because it matches --limit. # Scores: V/M = 0.06 # Time range: 2024-02-17T04:15:48 to 2024-02-17T04:16:43 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 117 # Exec time 12 28s 30ms 604ms 236ms 455ms 118ms 208ms # Lock time 0 11ms 26us 3ms 96us 260us 275us 36us # Rows sent 2 5.60k 49 49 49 49 0 49 # Rows examine 14 5.59M 48.88k 48.91k 48.90k 46.68k 0 46.68k # Query size 0 24.34k 213 213 213 213 0 213 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms #### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE `status` IN ('on_sale','sold_out') AND (`created_at` < '2019-08-12 15:52:48' OR (`created_at` <= '2019-08-12 15:52:48' AND `id` < 49958)) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G # Query 3: 2.78k QPS, 0.39x concurrency, ID 0x99AA0165670CE848 at byte 130507709 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2024-02-17T04:15:46 to 2024-02-17T04:16:50 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 33 178132 # Exec time 11 25s 23us 337ms 141us 384us 854us 63us # 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 # Query size 3 5.10M 30 30 30 30 0 30 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us ################################################################ # 100us ###################### # 1ms # # 10ms # # 100ms # # 1s # 10s+ administrator command: Prepare\G # Query 4: 1.84k QPS, 0.35x concurrency, ID 0xD12C53668328DB0D at byte 138983148 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:50 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 21 115890 # Exec time 10 22s 32us 26ms 191us 515us 446us 98us # Lock time 49 3s 8us 8ms 25us 40us 112us 13us # Rows sent 49 113.17k 1 1 1 1 0 1 # Rows examine 0 113.17k 1 1 1 1 0 1 # Query size 3 4.63M 41 42 41.88 40.45 0 40.45 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us ############################################################# # 100us ################################################################ # 1ms ## # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'categories'\G # SHOW CREATE TABLE `isucari`.`categories`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `categories` WHERE `id` = 20\G # Query 5: 2.76 QPS, 0.35x concurrency, ID 0x312243B842F41381 at byte 144077067 # This item is included in the report because it matches --limit. # Scores: V/M = 0.06 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:49 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 171 # Exec time 9 21s 17ms 322ms 126ms 279ms 84ms 95ms # Lock time 0 8ms 20us 764us 48us 125us 91us 26us # Rows sent 1 4.48k 0 49 26.81 46.83 22.62 46.83 # Rows examine 20 8.16M 48.83k 48.91k 48.88k 46.68k 0 46.68k # Query size 0 23.68k 141 142 141.80 136.99 0 136.99 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ########################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE `seller_id` = 3846 AND `status` IN ('on_sale','trading','sold_out') ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G # Query 6: 1.87 QPS, 0.23x concurrency, ID 0x36DB5799406088D9 at byte 133050893 # This item is included in the report because it matches --limit. # Scores: V/M = 0.06 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:49 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 116 # Exec time 6 14s 17ms 371ms 123ms 266ms 85ms 105ms # Lock time 0 10ms 26us 2ms 82us 273us 208us 36us # Rows sent 1 3.76k 15 49 33.16 46.83 15.01 46.83 # Rows examine 13 5.54M 48.85k 48.91k 48.88k 46.68k 0 46.68k # Query size 0 27.78k 244 246 245.21 234.30 0 234.30 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE `seller_id` = 3444 AND `status` IN ('on_sale','trading','sold_out') AND (`created_at` < '2019-08-12 09:56:14' OR (`created_at` <= '2019-08-12 09:56:14' AND `id` < 28573)) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G # Query 7: 842.32 QPS, 0.20x concurrency, ID 0xE070DA9421CA8C8D at byte 138999714 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:50 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 9 53066 # Exec time 5 13s 37us 41ms 238us 596us 744us 113us # Lock time 26 2s 9us 11ms 28us 49us 148us 15us # Rows sent 22 51.82k 1 1 1 1 0 1 # Rows examine 0 51.82k 1 1 1 1 0 1 # Query size 1 1.96M 36 39 38.74 38.53 0.78 38.53 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us ########################################################## # 100us ################################################################ # 1ms ## # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'users'\G # SHOW CREATE TABLE `isucari`.`users`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `users` WHERE `id` = 1594\G # Query 8: 1.97 QPS, 0.17x concurrency, ID 0x2C1B6BE83F13FC2B at byte 132126128 # This item is included in the report because it matches --limit. # Scores: V/M = 0.04 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:49 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 122 # Exec time 4 11s 18ms 359ms 86ms 189ms 61ms 78ms # Lock time 0 5ms 20us 245us 42us 119us 38us 28us # Rows sent 0 1.05k 4 11 8.85 10.84 2.49 10.84 # Rows examine 14 5.82M 48.84k 48.87k 48.86k 46.68k 0 46.68k # Query size 0 21.53k 179 181 180.67 174.84 0.00 174.84 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms #################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE (`seller_id` = 1738 OR `buyer_id` = 1738) AND `status` IN ('on_sale','trading','sold_out','cancel','stop') ORDER BY `created_at` DESC, `id` DESC LIMIT 11\G # Query 9: 1.61 QPS, 0.14x concurrency, ID 0x9A74397A1FB13CDE at byte 130888257 # This item is included in the report because it matches --limit. # Scores: V/M = 0.07 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 98 # Exec time 3 8s 8ms 463ms 87ms 230ms 77ms 61ms # Lock time 0 4ms 21us 436us 41us 119us 49us 26us # Rows sent 2 4.69k 49 49 49 49 0 49 # Rows examine 3 1.42M 3.84k 48.90k 14.84k 46.68k 15.97k 6.96k # Query size 0 13.86k 140 149 144.86 143.84 3.02 143.84 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # # 10ms ################################################################ # 100ms ######################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE `status` IN ('on_sale','sold_out') AND category_id IN (21, 22, 23, 24) ORDER BY created_at DESC, id DESC LIMIT 49\G # Query 10: 0.97 QPS, 0.09x concurrency, ID 0x4C0C6A2C3174E3A8 at byte 165711272 # This item is included in the report because it matches --limit. # Scores: V/M = 0.03 # Time range: 2024-02-17T04:15:47 to 2024-02-17T04:16:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 59 # Exec time 2 5s 21ms 232ms 92ms 180ms 54ms 93ms # Lock time 0 3ms 29us 502us 57us 93us 80us 36us # Rows sent 0 649 11 11 11 11 0 11 # Rows examine 7 2.82M 48.84k 48.87k 48.86k 46.68k 0 46.68k # Query size 0 16.42k 285 285 285 285 0 285 # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `items` WHERE (`seller_id` = 3844 OR `buyer_id` = 3844) AND `status` IN ('on_sale','trading','sold_out','cancel','stop') AND (`created_at` < '2019-08-12 14:47:53' OR (`created_at` <= '2019-08-12 14:47:53' AND `id` < 46065)) ORDER BY `created_at` DESC, `id` DESC LIMIT 11\G # Query 11: 20 QPS, 0.62x concurrency, ID 0x54AA9C3947003532 at byte 11563 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2024-02-17T04:15:41 to 2024-02-17T04:15:46 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 100 # Exec time 1 3s 18ms 150ms 31ms 59ms 17ms 27ms # Lock time 13 797ms 6ms 10ms 8ms 9ms 512us 8ms # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 64 88.65M 728.39k 913.29k 907.78k 871.90k 15.38k 871.90k # String: # Databases isucari # Hosts localhost # Users isucari # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G # SHOW CREATE TABLE `isucari`.`items`\G
meihei3
commented
7 months ago meihei3
commented
7 months ago