計測結果

[youdofoo]

初期スコア

{"pass":true,"score":260,"messages":[],"reason":"OK","language":"go"}

[youdofoo]

score: 487,

a9f2b0ea485605d99fe20ec2acf6cdefc6be9fb3

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1059.txt +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | 1080 | 1077 | 0 | 3 | 0 | GET | /api/estate/search | 0.024 | 0.552 | 153.001 | 0.142 | 0.212 | | 809 | 807 | 0 | 2 | 0 | GET | /api/chair/search | 0.028 | 0.472 | 126.079 | 0.156 | 0.216 | | 87 | 81 | 0 | 6 | 0 | POST | /api/estate/nazotte | 0.068 | 2.001 | 42.802 | 0.492 | 1.540 | | 275 | 275 | 0 | 0 | 0 | GET | /api/recommended_estate/\d+ | 0.064 | 0.424 | 34.984 | 0.127 | 0.212 | | 400 | 400 | 0 | 0 | 0 | GET | /api/chair/low_priced | 0.020 | 0.172 | 32.340 | 0.081 | 0.084 | | 400 | 399 | 0 | 1 | 0 | GET | /api/estate/low_priced | 0.020 | 0.436 | 27.641 | 0.069 | 0.040 | | 676 | 675 | 0 | 1 | 0 | GET | /api/estate/\d+ | 0.000 | 0.052 | 3.648 | 0.005 | 0.000 | | 279 | 276 | 0 | 3 | 0 | GET | /api/chair/\d+ | 0.000 | 0.152 | 3.340 | 0.012 | 0.008 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 2.372 | 2.372 | 2.372 | 2.372 | 2.372 | | 134 | 134 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 0.316 | 2.120 | 0.016 | 0.012 | | 353 | 353 | 0 | 0 | 0 | POST | /api/estate/req_doc/\d+ | 0.000 | 0.044 | 1.156 | 0.003 | 0.000 | | 1 | 1 | 0 | 0 | 0 | POST | /api/chair | 0.300 | 0.300 | 0.300 | 0.300 | 0.300 | | 1 | 1 | 0 | 0 | 0 | POST | /api/estate | 0.300 | 0.300 | 0.300 | 0.300 | 0.300 | | 33 | 33 | 0 | 0 | 0 | GET | /images/chair/.+\.png | 0.000 | 0.032 | 0.167 | 0.005 | 0.000 | | 182 | 182 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.000 | 0.004 | 0.100 | 0.001 | 0.000 | | 141 | 141 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.004 | 0.064 | 0.000 | 0.000 | | 10 | 10 | 0 | 0 | 0 | GET | /images/estate/.+\.png | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | | 1 | 1 | 0 | 0 | 0 | GET | / | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1059.txt # 7.5s user time, 60ms system time, 31.71M rss, 86.83M vsz # Current date: Sun Jul 17 01:59:50 2022 # Hostname: isu1 # Files: /var/log/mysql/mysql-slow.log # Overall: 61.41k total, 98 unique, 149.41 QPS, 1.09x concurrency ________ # Time range: 2022-07-17T01:50:16 to 2022-07-17T01:57:07 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 447s 1us 360ms 7ms 68ms 23ms 224us # Lock time 1s 0 19ms 23us 25us 271us 0 # Rows sent 91.18k 0 902 1.52 0.99 10.55 0 # Rows examine 141.47M 0 30.18k 2.36k 28.66k 7.79k 0 # Query size 32.00M 6 241.10k 546.45 313.99 9.75k 31.70 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============= # 1 0x387A1968AED87BF9 35.4920 7.9% 275 0.1291 0.01 SELECT estate # 2 0xA3BBB20FFD7BCE7C 35.2204 7.9% 400 0.0881 0.01 SELECT chair # 3 0x9C572B62B2210476 29.9024 6.7% 400 0.0748 0.00 SELECT estate # 4 0xE37BD5D8F0FF2FDE 28.8598 6.4% 324 0.0891 0.01 SELECT estate # 5 0x30222108492FF4F7 23.9242 5.3% 324 0.0738 0.00 SELECT estate # 6 0xBB5556170BADF35E 16.4943 3.7% 12337 0.0013 0.00 SELECT estate # 7 0xA9A4F380692DE1C8 16.3259 3.6% 180 0.0907 0.01 SELECT chair # 8 0x99AA0165670CE848 15.6653 3.5% 20128 0.0008 0.00 ADMIN PREPARE # 9 0x9810769325CA5A5E 13.5227 3.0% 178 0.0760 0.00 SELECT estate # 10 0xF3EFAD4903F0402F 13.4162 3.0% 180 0.0745 0.01 SELECT chair # 11 0xC73BFA73427CD78F 11.0259 2.5% 178 0.0619 0.00 SELECT estate # 12 0x7F578E6167724DBF 10.9181 2.4% 145 0.0753 0.00 SELECT estate # 13 0xF418F8958C3A84C3 9.8316 2.2% 111 0.0886 0.01 SELECT estate # 14 0x85DF0F23BB694539 9.7666 2.2% 87 0.1123 0.00 SELECT estate # 15 0x8EED46376BCF2AE3 9.5578 2.1% 107 0.0893 0.01 SELECT estate # 16 0xF291508A52D09114 9.0701 2.0% 145 0.0626 0.00 SELECT estate # 17 0x5ADF2C461C6F48FE 8.2827 1.9% 78 0.1062 0.01 SELECT chair # 18 0x0B23D92EDD8D61BC 8.0446 1.8% 111 0.0725 0.00 SELECT estate # 19 0x31E27AD9C15C891F 7.8089 1.7% 107 0.0730 0.00 SELECT estate # 20 0x7FEEE42B5C7412D2 7.3206 1.6% 72 0.1017 0.01 SELECT chair # MISC 0xMISC 126.9902 28.4% 25540 0.0050 0.0 <78 ITEMS> # Query 1: 4.51 QPS, 0.58x concurrency, ID 0x387A1968AED87BF9 at byte 33284574 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:52 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 275 # Exec time 7 35s 77ms 204ms 129ms 171ms 27ms 122ms # Lock time 0 11ms 27us 880us 39us 42us 67us 28us # Rows sent 5 5.37k 20 20 20 20 0 20 # Rows examine 5 7.87M 28.83k 29.32k 29.31k 28.66k 0 28.66k # Query size 0 90.26k 329 341 336.10 329.68 3.78 329.68 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ##### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE (door_width >= 43 AND door_height >= 192) OR (door_w # Query 2: 4.12 QPS, 0.36x concurrency, ID 0xA3BBB20FFD7BCE7C at byte 32966926 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:16 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 400 # Exec time 7 35s 21ms 183ms 88ms 122ms 22ms 82ms # Lock time 2 33ms 17us 7ms 83us 31us 466us 21us # Rows sent 8 7.81k 20 20 20 20 0 20 # Rows examine 8 11.45M 28.83k 29.32k 29.32k 28.66k 0 28.66k # Query size 0 27.73k 71 71 71 71 0 71 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20G # Query 3: 4.12 QPS, 0.31x concurrency, ID 0x9C572B62B2210476 at byte 34557778 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:16 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 400 # Exec time 6 30s 19ms 132ms 75ms 105ms 18ms 71ms # Lock time 2 35ms 17us 4ms 86us 49us 371us 19us # Rows sent 8 7.81k 20 20 20 20 0 20 # Rows examine 8 11.45M 28.83k 29.32k 29.32k 28.66k 0 28.66k # Query size 0 21.48k 55 55 55 55 0 55 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate ORDER BY rent ASC, id ASC LIMIT 20G # Query 4: 5.59 QPS, 0.50x concurrency, ID 0xE37BD5D8F0FF2FDE at byte 39931543 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:54 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 324 # Exec time 6 29s 13ms 150ms 89ms 128ms 22ms 82ms # Lock time 0 13ms 20us 2ms 39us 31us 112us 23us # Rows sent 8 7.91k 25 25 25 25 0 25 # Rows examine 6 9.29M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 34.85k 109 112 110.14 107.34 0 107.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE rent >= 50000 AND rent < 100000 ORDER BY popularity # Query 5: 5.59 QPS, 0.41x concurrency, ID 0x30222108492FF4F7 at byte 30217822 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:54 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 324 # Exec time 5 24s 17ms 141ms 74ms 105ms 18ms 68ms # Lock time 0 13ms 17us 5ms 41us 31us 270us 21us # Rows sent 0 324 1 1 1 1 0 1 # Rows examine 6 9.27M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 20.71k 65 66 65.45 65.89 0.50 62.76 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 50000 AND rent < 100000G # Query 6: 134.10 QPS, 0.18x concurrency, ID 0xBB5556170BADF35E at byte 44742042 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:21 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 20 12337 # Exec time 3 16s 60us 38ms 1ms 5ms 2ms 596us # Lock time 33 491ms 17us 11ms 39us 27us 237us 20us # Rows sent 9 8.56k 0 1 0.71 0.99 0.45 0.99 # Rows examine 0 8.56k 0 1 0.71 0.99 0.45 0.99 # Query size 11 3.62M 238 954 307.53 329.68 81.30 299.03 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ############################### # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE id = 23955 AND ST_Contains(ST_PolygonFromText('POLYG # Query 7: 3.05 QPS, 0.28x concurrency, ID 0xA9A4F380692DE1C8 at byte 36284462 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 180 # Exec time 3 16s 55ms 145ms 91ms 128ms 22ms 82ms # Lock time 0 12ms 23us 4ms 66us 44us 358us 25us # Rows sent 4 4.39k 25 25 25 25 0 25 # Rows examine 3 5.16M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 21.50k 121 125 122.29 118.34 0.97 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE price >= 12000 AND price < 15000 AND stock > 0 ORDER # Query 8: 48.97 QPS, 0.04x concurrency, ID 0x99AA0165670CE848 at byte 42609877 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:16 to 2022-07-17T01:57:07 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 32 20128 # Exec time 3 16s 49us 27ms 778us 4ms 1ms 260us # 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 1 589.69k 30 30 30 30 0 30 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ############# # 10ms # # 100ms # 1s # 10s+ administrator command: PrepareG # Query 9: 2.97 QPS, 0.23x concurrency, ID 0x9810769325CA5A5E at byte 36159726 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 178 # Exec time 3 14s 41ms 128ms 76ms 110ms 18ms 68ms # Lock time 0 5ms 20us 284us 26us 33us 19us 23us # Rows sent 4 4.35k 25 25 25 25 0 25 # Rows examine 3 5.10M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 16.11k 92 94 92.66 92.72 1 92.72 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE rent >= 150000 ORDER BY popularity DESC, id ASC LIMI # Query 10: 3.05 QPS, 0.23x concurrency, ID 0xF3EFAD4903F0402F at byte 32965414 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 180 # Exec time 2 13s 35ms 319ms 75ms 105ms 24ms 68ms # Lock time 0 11ms 19us 6ms 62us 31us 441us 22us # Rows sent 0 180 1 1 1 1 0 1 # Rows examine 3 5.15M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 13.66k 77 79 77.68 76.28 0 76.28 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price >= 3000 AND price < 6000 AND stock > 0G # Query 11: 2.97 QPS, 0.18x concurrency, ID 0xC73BFA73427CD78F at byte 36282759 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 178 # Exec time 2 11s 28ms 111ms 62ms 87ms 15ms 59ms # Lock time 0 12ms 17us 8ms 65us 26us 562us 21us # Rows sent 0 178 1 1 1 1 0 1 # Rows examine 3 5.09M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 8.34k 48 48 48 48 0 48 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 150000G # Query 12: 2.42 QPS, 0.18x concurrency, ID 0x7F578E6167724DBF at byte 45316142 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 145 # Exec time 2 11s 39ms 129ms 75ms 100ms 16ms 71ms # Lock time 0 4ms 20us 520us 29us 28us 43us 23us # Rows sent 3 3.54k 25 25 25 25 0 25 # Rows examine 2 4.16M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 12.84k 90 92 90.68 88.31 0 88.31 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE rent < 50000 ORDER BY popularity DESC, id ASC LIMIT # Query 13: 1.95 QPS, 0.17x concurrency, ID 0xF418F8958C3A84C3 at byte 30395430 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:54 to 2022-07-17T01:51:51 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 111 # Exec time 2 10s 43ms 156ms 89ms 122ms 22ms 78ms # Lock time 0 7ms 21us 2ms 62us 33us 240us 23us # Rows sent 2 2.71k 25 25 25 25 0 25 # Rows examine 2 3.18M 29.32k 29.42k 29.36k 28.66k 0 28.66k # Query size 0 12.59k 115 118 116.12 112.70 0 112.70 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE door_width >= 110 AND door_width < 150 ORDER BY popu # Query 14: 0.96 QPS, 0.11x concurrency, ID 0x85DF0F23BB694539 at byte 37386630 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:21 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 87 # Exec time 2 10s 12ms 173ms 112ms 134ms 21ms 110ms # Lock time 0 8ms 31us 5ms 96us 54us 469us 33us # Rows sent 13 12.05k 0 902 141.80 592.07 206.98 64.33 # Rows examine 1 2.50M 28.81k 30.18k 29.41k 28.66k 267.71 28.66k # Query size 0 16.55k 192 197 194.83 192.76 1.38 192.76 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE latitude <= 37.100045591699974 AND latitude >= 36.38 # Query 15: 1.78 QPS, 0.16x concurrency, ID 0x8EED46376BCF2AE3 at byte 45413820 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 107 # Exec time 2 10s 52ms 153ms 89ms 128ms 22ms 78ms # Lock time 0 3ms 21us 362us 28us 28us 31us 23us # Rows sent 2 2.61k 25 25 25 25 0 25 # Rows examine 2 3.07M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 12.35k 117 120 118.21 118.34 1.50 112.70 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM estate WHERE door_height >= 80 AND door_height < 110 ORDER BY pop # Query 16: 2.42 QPS, 0.15x concurrency, ID 0xF291508A52D09114 at byte 35925070 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 145 # Exec time 2 9s 31ms 103ms 63ms 95ms 15ms 59ms # Lock time 0 4ms 16us 712us 29us 35us 62us 20us # Rows sent 0 145 1 1 1 1 0 1 # Rows examine 2 4.15M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 6.51k 46 46 46 46 0 46 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms #### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent < 50000G # Query 17: 1.32 QPS, 0.14x concurrency, ID 0x5ADF2C461C6F48FE at byte 35328735 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 78 # Exec time 1 8s 70ms 157ms 106ms 148ms 24ms 100ms # Lock time 0 2ms 24us 54us 28us 33us 4us 26us # Rows sent 2 1.90k 25 25 25 25 0 25 # Rows examine 1 2.24M 29.32k 29.42k 29.36k 28.66k 0 28.66k # Query size 0 9.02k 109 126 118.44 124.25 5.46 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms #################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE kind = 'エルゴノミクス' AND stock > 0 ORDER BY # Query 18: 1.95 QPS, 0.14x concurrency, ID 0x0B23D92EDD8D61BC at byte 41410198 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:54 to 2022-07-17T01:51:51 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 111 # Exec time 1 8s 34ms 123ms 72ms 100ms 17ms 68ms # Lock time 0 3ms 18us 245us 25us 33us 20us 21us # Rows sent 0 111 1 1 1 1 0 1 # Rows examine 2 3.18M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 7.75k 71 72 71.46 69.19 0 69.19 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 110 AND door_width < 150G # Query 19: 1.78 QPS, 0.13x concurrency, ID 0x31E27AD9C15C891F at byte 33284053 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T01:50:53 to 2022-07-17T01:51:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 107 # Exec time 1 8s 38ms 115ms 73ms 100ms 18ms 65ms # Lock time 0 7ms 18us 4ms 61us 27us 365us 21us # Rows sent 0 107 1 1 1 1 0 1 # Rows examine 2 3.06M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 7.69k 73 74 73.59 72.65 0 72.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150G # Query 20: 1.31 QPS, 0.13x concurrency, ID 0x7FEEE42B5C7412D2 at byte 35570994 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T01:50:56 to 2022-07-17T01:51:51 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 72 # Exec time 1 7s 51ms 148ms 102ms 134ms 23ms 95ms # Lock time 0 3ms 25us 733us 41us 31us 89us 26us # Rows sent 1 1.76k 25 25 25 25 0 25 # Rows examine 1 2.06M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 7.50k 104 114 106.60 112.70 4.27 102.22 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ###################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE color = '白' AND stock > 0 ORDER BY popularity DESC, ```

[youdofoo]

score:

9cdd7f62a95b4eca93918cff259b70978f839d8b

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1341.txt +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | 1379 | 1376 | 0 | 3 | 0 | GET | /api/estate/search | 0.024 | 0.564 | 147.711 | 0.107 | 0.140 | | 830 | 828 | 0 | 2 | 0 | GET | /api/chair/search | 0.056 | 0.504 | 132.799 | 0.160 | 0.160 | | 115 | 107 | 0 | 8 | 0 | POST | /api/estate/nazotte | 0.060 | 2.001 | 54.844 | 0.477 | 0.943 | | 480 | 480 | 0 | 0 | 0 | GET | /api/chair/low_priced | 0.016 | 0.448 | 37.964 | 0.079 | 0.136 | | 278 | 276 | 0 | 2 | 0 | GET | /api/recommended_estate/\d+ | 0.008 | 0.472 | 29.180 | 0.105 | 0.120 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 5.416 | 5.416 | 5.416 | 5.416 | 5.416 | | 796 | 795 | 0 | 1 | 0 | GET | /api/estate/\d+ | 0.000 | 0.088 | 4.240 | 0.005 | 0.004 | | 480 | 480 | 0 | 0 | 0 | GET | /api/estate/low_priced | 0.000 | 0.324 | 3.272 | 0.007 | 0.004 | | 282 | 280 | 0 | 2 | 0 | GET | /api/chair/\d+ | 0.004 | 0.072 | 2.628 | 0.009 | 0.008 | | 137 | 137 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 0.056 | 1.836 | 0.013 | 0.016 | | 426 | 426 | 0 | 0 | 0 | POST | /api/estate/req_doc/\d+ | 0.004 | 0.032 | 0.864 | 0.002 | 0.000 | | 1 | 1 | 0 | 0 | 0 | POST | /api/estate | 0.340 | 0.340 | 0.340 | 0.340 | 0.340 | | 1 | 1 | 0 | 0 | 0 | POST | /api/chair | 0.320 | 0.320 | 0.320 | 0.320 | 0.320 | | 231 | 231 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.000 | 0.028 | 0.160 | 0.001 | 0.000 | | 140 | 140 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.012 | 0.128 | 0.001 | 0.004 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1341.txt # 9.2s user time, 90ms system time, 32.28M rss, 87.25M vsz # Current date: Sun Jul 17 04:41:51 2022 # Hostname: isu1 # Files: /var/log/mysql/mysql-slow.log # Overall: 74.22k total, 89 unique, 1.11k QPS, 6.62x concurrency _________ # Time range: 2022-07-17T04:39:59 to 2022-07-17T04:41:06 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 443s 1us 439ms 6ms 59ms 21ms 247us # Lock time 2s 0 81ms 32us 25us 641us 0 # Rows sent 108.21k 0 1.34k 1.49 0.99 11.39 0 # Rows examine 122.68M 0 30.64k 1.69k 28.66k 6.49k 0 # Query size 35.09M 6 241.10k 495.73 420.77 8.87k 31.70 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============= # 1 0xD8B5789B35E4B085 43.5035 9.8% 450 0.0967 0.01 SELECT estate # 2 0xA3BBB20FFD7BCE7C 41.9546 9.5% 480 0.0874 0.01 SELECT chair # 3 0x6D9DEB6E6F2D0E24 29.0334 6.5% 277 0.1048 0.01 SELECT estate # 4 0x97CA268535DE5FE7 22.3759 5.0% 15543 0.0014 0.00 SELECT estate # 5 0x99AA0165670CE848 21.2067 4.8% 24369 0.0009 0.00 ADMIN PREPARE # 6 0x8F9D1B54BDE66E79 17.8614 4.0% 204 0.0876 0.01 SELECT estate # 7 0x823EF79E3217E98F 15.6631 3.5% 183 0.0856 0.01 SELECT estate # 8 0xA9A4F380692DE1C8 14.9112 3.4% 162 0.0920 0.01 SELECT chair # 9 0x1EC7023E53F22C70 14.3196 3.2% 115 0.1245 0.01 SELECT estate # 10 0xF7B688170EE1A027 12.3789 2.8% 132 0.0938 0.01 SELECT estate # 11 0xF3EFAD4903F0402F 11.9726 2.7% 162 0.0739 0.01 SELECT chair # 12 0x7FEEE42B5C7412D2 11.4128 2.6% 120 0.0951 0.01 SELECT chair # 13 0xBF86746AF450D0B4 10.9196 2.5% 104 0.1050 0.01 SELECT estate # 14 0x65DBB57A0B593F83 9.3529 2.1% 120 0.0779 0.00 SELECT chair # 15 0x61F4988BE0D150F8 8.9453 2.0% 90 0.0994 0.01 SELECT chair # 16 0x5ADF2C461C6F48FE 8.8732 2.0% 84 0.1056 0.01 SELECT chair # 17 0x30222108492FF4F7 8.0180 1.8% 450 0.0178 0.00 SELECT estate # 18 0x6707B7697BC14E2F 7.9577 1.8% 87 0.0915 0.01 SELECT estate # 19 0x31E27AD9C15C891F 7.3797 1.7% 132 0.0559 0.01 SELECT estate # 20 0x31953C185D32A8B5 7.2117 1.6% 84 0.0859 0.01 SELECT chair # MISC 0xMISC 118.1127 26.6% 30868 0.0038 0.0 <69 ITEMS> # Query 1: 7.50 QPS, 0.73x concurrency, ID 0xD8B5789B35E4B085 at byte 49370503 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 450 # Exec time 9 44s 42ms 379ms 97ms 141ms 28ms 87ms # Lock time 0 22ms 21us 4ms 48us 47us 198us 23us # Rows sent 10 10.99k 25 25 25 25 0 25 # Rows examine 10 12.90M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 98.52k 223 226 224.20 223.14 1.50 223.14 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ##################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 2: 7.87 QPS, 0.69x concurrency, ID 0xA3BBB20FFD7BCE7C at byte 49387198 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:05 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 480 # Exec time 9 42s 15ms 439ms 87ms 122ms 29ms 82ms # Lock time 1 27ms 19us 3ms 55us 44us 203us 21us # Rows sent 8 9.38k 20 20 20 20 0 20 # Rows examine 11 13.74M 28.83k 29.32k 29.32k 28.66k 0.00 28.66k # Query size 0 33.28k 71 71 71 71 0 71 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20G # Query 3: 4.54 QPS, 0.48x concurrency, ID 0x6D9DEB6E6F2D0E24 at byte 49842324 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:05 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 277 # Exec time 6 29s 7ms 233ms 105ms 148ms 28ms 95ms # Lock time 0 10ms 23us 913us 37us 38us 87us 23us # Rows sent 4 5.41k 20 20 20 20 0 20 # Rows examine 6 7.82M 250 29.32k 28.89k 28.66k 3.37k 28.66k # Query size 0 72.71k 267 271 268.79 258.32 0 258.32 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # # 10ms ############################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 4: 254.80 QPS, 0.37x concurrency, ID 0x97CA268535DE5FE7 at byte 49311968 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T04:40:05 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 20 15543 # Exec time 5 22s 56us 32ms 1ms 5ms 2ms 690us # Lock time 32 789ms 18us 12ms 50us 33us 289us 21us # Rows sent 10 10.95k 0 1 0.72 0.99 0.44 0.99 # Rows examine 0 10.95k 0 1 0.72 0.99 0.44 0.99 # Query size 17 6.09M 353 480 411.00 441.81 21.50 400.73 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ##################################### # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 5: 399.49 QPS, 0.35x concurrency, ID 0x99AA0165670CE848 at byte 49304197 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T04:40:05 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 32 24369 # Exec time 4 21s 46us 113ms 870us 4ms 2ms 316us # 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 1 713.94k 30 30 30 30 0 30 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ################ # 10ms # # 100ms # # 1s # 10s+ administrator command: PrepareG # Query 6: 3.46 QPS, 0.30x concurrency, ID 0x8F9D1B54BDE66E79 at byte 49392881 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 204 # Exec time 4 18s 50ms 211ms 88ms 122ms 23ms 78ms # Lock time 0 14ms 21us 5ms 69us 30us 409us 22us # Rows sent 4 4.98k 25 25 25 25 0 25 # Rows examine 4 5.85M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 41.16k 206 208 206.60 202.40 0 202.40 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 7: 3.10 QPS, 0.27x concurrency, ID 0x823EF79E3217E98F at byte 36852947 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 183 # Exec time 3 16s 39ms 145ms 86ms 122ms 22ms 75ms # Lock time 0 6ms 22us 1ms 32us 28us 99us 23us # Rows sent 4 4.47k 25 25 25 25 0 25 # Rows examine 0 635.81k 3.44k 3.54k 3.47k 3.52k 50 3.35k # Query size 0 36.58k 204 206 204.67 202.40 0 202.40 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 8: 2.75 QPS, 0.25x concurrency, ID 0xA9A4F380692DE1C8 at byte 48049789 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 162 # Exec time 3 15s 47ms 299ms 92ms 141ms 31ms 82ms # Lock time 0 14ms 21us 9ms 86us 31us 716us 25us # Rows sent 3 3.96k 25 25 25 25 0 25 # Rows examine 3 4.64M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 19.37k 121 125 122.43 118.34 1.02 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ######################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE price >= 12000 AND price < 15000 AND stock > 0 ORDER # Query 9: 1.89 QPS, 0.23x concurrency, ID 0x1EC7023E53F22C70 at byte 49411212 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:05 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 115 # Exec time 3 14s 76ms 289ms 125ms 155ms 29ms 116ms # Lock time 0 13ms 31us 3ms 116us 80us 423us 35us # Rows sent 14 15.18k 0 1.34k 135.16 563.87 227.39 26.08 # Rows examine 2 3.30M 28.81k 30.64k 29.41k 28.66k 233.86 28.66k # Query size 0 34.66k 305 311 308.63 299.03 0.00 299.03 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 10: 2.36 QPS, 0.22x concurrency, ID 0xF7B688170EE1A027 at byte 44970769 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:08 to 2022-07-17T04:41:04 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 132 # Exec time 2 12s 40ms 170ms 94ms 141ms 26ms 82ms # Lock time 0 4ms 22us 220us 28us 31us 17us 25us # Rows sent 2 3.22k 25 25 25 25 0 25 # Rows examine 3 3.78M 29.32k 29.42k 29.36k 28.66k 0 28.66k # Query size 0 29.93k 231 234 232.17 223.14 0 223.14 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 11: 2.75 QPS, 0.20x concurrency, ID 0xF3EFAD4903F0402F at byte 48988102 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 162 # Exec time 2 12s 23ms 166ms 74ms 116ms 22ms 65ms # Lock time 0 4ms 18us 202us 24us 27us 14us 21us # Rows sent 0 162 1 1 1 1 0 1 # Rows examine 3 4.63M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 12.30k 77 79 77.76 76.28 0 76.28 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price >= 6000 AND price < 9000 AND stock > 0G # Query 12: 2.07 QPS, 0.20x concurrency, ID 0x7FEEE42B5C7412D2 at byte 33953353 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:07 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 120 # Exec time 2 11s 52ms 168ms 95ms 141ms 23ms 87ms # Lock time 0 12ms 23us 5ms 101us 33us 522us 26us # Rows sent 2 2.93k 25 25 25 25 0 25 # Rows examine 2 3.44M 29.32k 29.42k 29.36k 28.66k 0.00 28.66k # Query size 0 12.64k 104 115 107.88 112.70 4.47 102.22 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE color = 'ネイビー' AND stock > 0 ORDER BY popular # Query 13: 1.76 QPS, 0.19x concurrency, ID 0xBF86746AF450D0B4 at byte 47912866 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:07 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 104 # Exec time 2 11s 63ms 198ms 105ms 171ms 33ms 91ms # Lock time 0 4ms 22us 925us 41us 30us 96us 23us # Rows sent 2 2.54k 25 25 25 25 0 25 # Rows examine 1 2.23M 5.23k 29.42k 21.94k 28.66k 10.83k 28.66k # Query size 0 23.38k 229 232 230.16 223.14 0 223.14 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ###################################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 14: 2.07 QPS, 0.16x concurrency, ID 0x65DBB57A0B593F83 at byte 43483784 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T04:40:07 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 120 # Exec time 2 9s 45ms 135ms 78ms 116ms 19ms 71ms # Lock time 0 3ms 21us 69us 25us 28us 6us 23us # Rows sent 0 120 1 1 1 1 0 1 # Rows examine 2 3.43M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 7.40k 60 69 63.15 65.89 2.75 59.77 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE color = '黒' AND stock > 0G # Query 15: 1.70 QPS, 0.17x concurrency, ID 0x61F4988BE0D150F8 at byte 44952298 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:12 to 2022-07-17T04:41:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 90 # Exec time 2 9s 45ms 162ms 99ms 134ms 27ms 91ms # Lock time 0 3ms 22us 259us 28us 28us 23us 23us # Rows sent 2 2.20k 25 25 25 25 0 25 # Rows examine 2 2.58M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 10.64k 120 123 121.11 118.34 0.00 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########################################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE height >= 110 AND height < 150 AND stock > 0 ORDER BY # Query 16: 1.53 QPS, 0.16x concurrency, ID 0x5ADF2C461C6F48FE at byte 38267998 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:07 to 2022-07-17T04:41:02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 84 # Exec time 2 9s 69ms 163ms 106ms 148ms 25ms 95ms # Lock time 0 3ms 25us 351us 32us 31us 35us 26us # Rows sent 1 2.05k 25 25 25 25 0 25 # Rows examine 1 2.41M 29.32k 29.42k 29.36k 28.66k 0.00 28.66k # Query size 0 9.72k 109 126 118.51 124.25 5.19 112.70 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE kind = '座椅子' AND stock > 0 ORDER BY popularity # Query 17: 7.50 QPS, 0.13x concurrency, ID 0x30222108492FF4F7 at byte 50615888 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 450 # Exec time 1 8s 2ms 49ms 18ms 34ms 9ms 17ms # Lock time 0 14ms 19us 3ms 31us 27us 146us 22us # Rows sent 0 450 1 1 1 1 0 1 # Rows examine 3 3.78M 8.60k 8.60k 8.60k 8.46k 0 8.46k # Query size 0 28.80k 65 66 65.53 65.89 0.50 65.89 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################# # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 50000 AND rent < 100000G # Query 18: 1.53 QPS, 0.14x concurrency, ID 0x6707B7697BC14E2F at byte 48246252 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:06 to 2022-07-17T04:41:03 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 87 # Exec time 1 8s 49ms 149ms 91ms 128ms 24ms 82ms # Lock time 0 5ms 21us 3ms 61us 33us 315us 23us # Rows sent 1 2.12k 25 25 25 25 0 25 # Rows examine 2 2.49M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 17.81k 209 211 209.60 202.40 0 202.40 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 19: 2.36 QPS, 0.13x concurrency, ID 0x31E27AD9C15C891F at byte 48913617 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:08 to 2022-07-17T04:41:04 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 132 # Exec time 1 7s 21ms 129ms 56ms 91ms 20ms 51ms # Lock time 0 3ms 19us 56us 24us 30us 5us 23us # Rows sent 0 132 1 1 1 1 0 1 # Rows examine 3 3.78M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 9.47k 73 74 73.45 72.65 0 72.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150G # Query 20: 1.53 QPS, 0.13x concurrency, ID 0x31953C185D32A8B5 at byte 48950608 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T04:40:07 to 2022-07-17T04:41:02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 84 # Exec time 1 7s 45ms 171ms 86ms 122ms 24ms 78ms # Lock time 0 2ms 20us 364us 28us 28us 35us 23us # Rows sent 0 84 1 1 1 1 0 1 # Rows examine 1 2.40M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 6.05k 65 80 73.79 76.28 4.86 69.19 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE kind = '座椅子' AND stock > 0G ```

[youdofoo]

score: 795,

9f0ca259aded3815cf37a27132ba8a2d290fd883

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1445.txt +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | 1030 | 825 | 0 | 3 | 202 | GET | /api/chair/search | 0.035 | 0.568 | 155.563 | 0.151 | 0.244 | | 2922 | 2717 | 0 | 3 | 202 | GET | /api/estate/search | 0.004 | 0.584 | 112.944 | 0.039 | 0.028 | | 133 | 126 | 0 | 7 | 0 | POST | /api/estate/nazotte | 0.064 | 2.002 | 71.554 | 0.538 | 0.796 | | 721 | 721 | 0 | 0 | 0 | GET | /api/chair/low_priced | 0.012 | 0.300 | 61.728 | 0.086 | 0.092 | | 1254 | 1252 | 0 | 2 | 0 | GET | /api/estate/\d+ | 0.000 | 0.168 | 4.976 | 0.004 | 0.000 | | 721 | 721 | 0 | 0 | 0 | GET | /api/estate/low_priced | 0.004 | 0.056 | 4.752 | 0.007 | 0.000 | | 275 | 275 | 0 | 0 | 0 | GET | /api/recommended_estate/\d+ | 0.004 | 0.280 | 3.076 | 0.011 | 0.004 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 2.732 | 2.732 | 2.732 | 2.732 | 2.732 | | 2 | 2 | 0 | 0 | 0 | POST | /api/estate | 0.328 | 1.672 | 2.000 | 1.000 | 0.328 | | 281 | 278 | 0 | 3 | 0 | GET | /api/chair/\d+ | 0.004 | 0.052 | 1.964 | 0.007 | 0.000 | | 2 | 2 | 0 | 0 | 0 | POST | /api/chair | 0.304 | 1.392 | 1.696 | 0.848 | 0.304 | | 659 | 659 | 0 | 0 | 0 | POST | /api/estate/req_doc/\d+ | 0.004 | 0.056 | 1.668 | 0.003 | 0.004 | | 136 | 136 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 0.060 | 1.216 | 0.009 | 0.016 | | 455 | 455 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.000 | 0.020 | 0.284 | 0.001 | 0.000 | | 139 | 139 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.008 | 0.072 | 0.001 | 0.000 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1445.txt # 12.9s user time, 130ms system time, 33.23M rss, 88.29M vsz # Current date: Sun Jul 17 05:45:20 2022 # Hostname: isu1 # Files: /var/log/mysql/mysql-slow.log # Overall: 102.34k total, 217 unique, 1.57k QPS, 6.79x concurrency _______ # Time range: 2022-07-17T05:43:54 to 2022-07-17T05:44:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 441s 1us 391ms 4ms 14ms 18ms 236us # Lock time 3s 0 51ms 25us 26us 366us 0 # Rows sent 159.84k 0 1.27k 1.60 19.46 10.73 0 # Rows examine 105.61M 0 31.06k 1.06k 3.35k 5.01k 0 # Query size 38.44M 6 241.10k 393.82 441.81 7.56k 31.70 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============= # 1 0xA3BBB20FFD7BCE7C 68.4376 15.5% 721 0.0949 0.01 SELECT chair # 2 0x99AA0165670CE848 29.3984 6.7% 33620 0.0009 0.00 ADMIN PREPARE # 3 0x97CA268535DE5FE7 27.9144 6.3% 19933 0.0014 0.00 SELECT estate # 4 0xEE12F4F53D0D9870 18.2977 4.1% 133 0.1376 0.00 SELECT estate # 5 0x30222108492FF4F7 17.1594 3.9% 761 0.0225 0.00 SELECT estate # 6 0xA9A4F380692DE1C8 15.8303 3.6% 153 0.1035 0.01 SELECT chair # 7 0x31E27AD9C15C891F 13.6800 3.1% 219 0.0625 0.01 SELECT estate # 8 0xF3EFAD4903F0402F 12.3838 2.8% 153 0.0809 0.01 SELECT chair # 9 0x5ADF2C461C6F48FE 9.3270 2.1% 78 0.1196 0.02 SELECT chair # 10 0x7FEEE42B5C7412D2 9.3081 2.1% 87 0.1070 0.01 SELECT chair # 11 0xC73BFA73427CD78F 8.1943 1.9% 420 0.0195 0.01 SELECT estate # 12 0x65DBB57A0B593F83 7.5399 1.7% 87 0.0867 0.01 SELECT chair # 13 0x31953C185D32A8B5 7.5014 1.7% 78 0.0962 0.01 SELECT chair # 14 0x6C1D02F49D3D340F 6.4337 1.5% 120 0.0536 0.00 SELECT estate # 15 0x18692940986AC2AD 6.2796 1.4% 42 0.1495 0.01 SELECT estate # 16 0x61F4988BE0D150F8 6.2506 1.4% 60 0.1042 0.01 SELECT chair # 17 0xC85534CCE521D098 6.0591 1.4% 108 0.0561 0.00 SELECT estate # 18 0xBC792221FF0E74ED 5.2940 1.2% 60 0.0882 0.01 SELECT chair # 19 0x8A99728C0EE94E2E 4.8571 1.1% 45 0.1079 0.01 SELECT chair # 20 0xB626D23952297F80 4.4109 1.0% 761 0.0058 0.00 SELECT estate # MISC 0xMISC 156.7608 35.5% 44705 0.0035 0.0 <197 ITEMS> # Query 1: 11.82 QPS, 1.12x concurrency, ID 0xA3BBB20FFD7BCE7C at byte 29815704 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:57 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 721 # Exec time 15 68s 11ms 297ms 95ms 134ms 25ms 91ms # Lock time 2 58ms 19us 12ms 80us 47us 483us 21us # Rows sent 8 14.08k 20 20 20 20 0 20 # Rows examine 19 20.72M 28.83k 29.80k 29.43k 28.66k 0.00 28.66k # Query size 0 49.99k 71 71 71 71 0 71 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20G # Query 2: 542.26 QPS, 0.47x concurrency, ID 0x99AA0165670CE848 at byte 29718822 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:57 to 2022-07-17T05:44:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 32 33620 # Exec time 6 29s 39us 35ms 874us 4ms 1ms 316us # 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 2 984.96k 30 30 30 30 0 30 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ################### # 10ms # # 100ms # 1s # 10s+ administrator command: PrepareG # Query 3: 321.50 QPS, 0.45x concurrency, ID 0x97CA268535DE5FE7 at byte 50343423 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:57 to 2022-07-17T05:44:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 19 19933 # Exec time 6 28s 64us 36ms 1ms 5ms 2ms 690us # Lock time 43 1s 18us 35ms 56us 33us 408us 21us # Rows sent 8 14.23k 0 1 0.73 0.99 0.44 0.99 # Rows examine 0 14.23k 0 1 0.73 0.99 0.44 0.99 # Query size 20 7.82M 353 480 411.33 463.90 29.52 400.73 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ########################################## # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 4: 2.18 QPS, 0.30x concurrency, ID 0xEE12F4F53D0D9870 at byte 40465111 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:57 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 133 # Exec time 4 18s 82ms 214ms 138ms 171ms 22ms 128ms # Lock time 0 12ms 32us 2ms 93us 214us 270us 35us # Rows sent 12 19.47k 0 1.27k 149.87 592.07 218.27 33.64 # Rows examine 3 3.84M 28.81k 31.06k 29.60k 30.09k 368.52 28.66k # Query size 0 40.13k 305 311 308.95 299.03 0 299.03 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 5: 12.68 QPS, 0.29x concurrency, ID 0x30222108492FF4F7 at byte 29725714 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 761 # Exec time 3 17s 2ms 93ms 23ms 38ms 11ms 21ms # Lock time 2 68ms 18us 42ms 89us 35us 2ms 21us # Rows sent 0 761 1 1 1 1 0 1 # Rows examine 6 6.40M 8.60k 8.74k 8.61k 8.46k 0 8.46k # Query size 0 48.65k 65 66 65.46 65.89 0.50 62.76 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ####### # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 100000 AND rent < 150000G # Query 6: 2.59 QPS, 0.27x concurrency, ID 0xA9A4F380692DE1C8 at byte 29856836 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 153 # Exec time 3 16s 31ms 303ms 103ms 141ms 29ms 95ms # Lock time 2 64ms 21us 51ms 417us 214us 4ms 23us # Rows sent 2 3.74k 25 25 25 25 0 25 # Rows examine 4 4.40M 29.32k 29.91k 29.42k 28.66k 0 28.66k # Query size 0 18.33k 121 125 122.66 124.25 1.46 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms #################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE price >= 12000 AND price < 15000 AND stock > 0 ORDER # Query 7: 3.71 QPS, 0.23x concurrency, ID 0x31E27AD9C15C891F at byte 29781684 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:57 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 219 # Exec time 3 14s 28ms 139ms 62ms 91ms 18ms 59ms # Lock time 0 7ms 18us 1ms 34us 28us 99us 21us # Rows sent 0 219 1 1 1 1 0 1 # Rows examine 5 6.27M 29.30k 29.79k 29.32k 28.66k 0 28.66k # Query size 0 15.72k 73 74 73.52 72.65 0 72.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110G # Query 8: 2.59 QPS, 0.21x concurrency, ID 0xF3EFAD4903F0402F at byte 29743861 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 153 # Exec time 2 12s 33ms 164ms 81ms 122ms 22ms 75ms # Lock time 0 10ms 18us 5ms 62us 31us 410us 20us # Rows sent 0 153 1 1 1 1 0 1 # Rows examine 4 4.39M 29.30k 29.79k 29.36k 28.66k 0 28.66k # Query size 0 11.65k 77 79 77.98 76.28 0 76.28 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price >= 12000 AND price < 15000 AND stock > 0G # Query 9: 1.34 QPS, 0.16x concurrency, ID 0x5ADF2C461C6F48FE at byte 29788580 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:57 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 78 # Exec time 2 9s 60ms 391ms 120ms 155ms 43ms 110ms # Lock time 0 7ms 24us 5ms 93us 52us 518us 26us # Rows sent 1 1.90k 25 25 25 25 0 25 # Rows examine 2 2.24M 29.32k 29.91k 29.40k 28.66k 0 28.66k # Query size 0 8.94k 109 126 117.37 124.25 5.80 112.70 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE kind = 'ゲーミングチェア' AND stock > 0 ORDER # Query 10: 1.85 QPS, 0.20x concurrency, ID 0x7FEEE42B5C7412D2 at byte 40432434 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:46 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 87 # Exec time 2 9s 27ms 210ms 107ms 148ms 29ms 95ms # Lock time 0 5ms 25us 1ms 51us 31us 148us 26us # Rows sent 1 2.12k 25 25 25 25 0 25 # Rows examine 2 2.50M 29.32k 29.91k 29.37k 28.66k 0.00 28.66k # Query size 0 9.23k 104 115 108.67 112.70 4.73 107.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE color = '黒' AND stock > 0 ORDER BY popularity DESC, # Query 11: 7 QPS, 0.14x concurrency, ID 0xC73BFA73427CD78F at byte 47169858 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 420 # Exec time 1 8s 2ms 175ms 20ms 36ms 17ms 18ms # Lock time 0 25ms 17us 4ms 58us 44us 319us 19us # Rows sent 0 420 1 1 1 1 0 1 # Rows examine 3 3.57M 8.68k 8.83k 8.70k 8.46k 0 8.46k # Query size 0 19.69k 48 48 48 48 0 48 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ############### # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 150000G # Query 12: 1.81 QPS, 0.16x concurrency, ID 0x65DBB57A0B593F83 at byte 34096931 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:46 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 87 # Exec time 1 8s 43ms 148ms 87ms 122ms 23ms 78ms # Lock time 0 6ms 20us 3ms 67us 28us 322us 23us # Rows sent 0 87 1 1 1 1 0 1 # Rows examine 2 2.49M 29.30k 29.79k 29.31k 28.66k 0.00 28.66k # Query size 0 5.44k 60 69 64.03 65.89 2.90 62.76 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE color = '赤' AND stock > 0G # Query 13: 1.34 QPS, 0.13x concurrency, ID 0x31953C185D32A8B5 at byte 29750390 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:57 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 78 # Exec time 1 8s 54ms 202ms 96ms 134ms 27ms 91ms # Lock time 0 2ms 21us 41us 23us 28us 2us 22us # Rows sent 0 78 1 1 1 1 0 1 # Rows examine 2 2.23M 29.30k 29.79k 29.33k 28.66k 0 28.66k # Query size 0 5.53k 65 80 72.62 76.28 5.85 69.19 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE kind = 'ゲーミングチェア' AND stock > 0G # Query 14: 3.16 QPS, 0.17x concurrency, ID 0x6C1D02F49D3D340F at byte 37078052 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:37 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 120 # Exec time 1 6s 20ms 97ms 54ms 82ms 16ms 51ms # Lock time 0 4ms 18us 1ms 36us 33us 115us 20us # Rows sent 0 120 1 1 1 1 0 1 # Rows examine 3 3.43M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 5.86k 50 50 50 50 0 50 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height < 80G # Query 15: 2.33 QPS, 0.35x concurrency, ID 0x18692940986AC2AD at byte 51139915 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:44:39 to 2022-07-17T05:44:57 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 42 # Exec time 1 6s 91ms 219ms 150ms 180ms 33ms 155ms # Lock time 0 4ms 20us 3ms 102us 38us 431us 22us # Rows sent 0 42 1 1 1 1 0 1 # Rows examine 0 514.42k 5.28k 29.79k 12.25k 28.66k 10.60k 5.20k # Query size 0 4.42k 107 109 107.86 107.34 1 107.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 80 AND door_width < 110 AND rent >= 50000 AND rent < # Query 16: 1.02 QPS, 0.11x concurrency, ID 0x61F4988BE0D150F8 at byte 40666293 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 60 # Exec time 1 6s 61ms 180ms 104ms 141ms 28ms 95ms # Lock time 0 2ms 22us 512us 34us 35us 59us 23us # Rows sent 0 1.46k 25 25 25 25 0 25 # Rows examine 1 1.72M 29.32k 29.88k 29.38k 28.66k 0.00 28.66k # Query size 0 7.09k 120 123 121.05 118.34 0 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ########################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE height >= 80 AND height < 110 AND stock > 0 ORDER BY # Query 17: 2.51 QPS, 0.14x concurrency, ID 0xC85534CCE521D098 at byte 48296573 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:44:00 to 2022-07-17T05:44:43 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 108 # Exec time 1 6s 24ms 94ms 56ms 82ms 15ms 53ms # Lock time 0 3ms 17us 408us 24us 26us 36us 19us # Rows sent 0 108 1 1 1 1 0 1 # Rows examine 2 3.09M 29.30k 29.79k 29.32k 28.66k 0 28.66k # Query size 0 5.48k 52 52 52 52 0 52 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 150G # Query 18: 1.03 QPS, 0.09x concurrency, ID 0xBC792221FF0E74ED at byte 34110986 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:59 to 2022-07-17T05:44:57 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 60 # Exec time 1 5s 31ms 133ms 88ms 122ms 23ms 91ms # Lock time 0 1ms 19us 63us 22us 25us 6us 20us # Rows sent 0 60 1 1 1 1 0 1 # Rows examine 1 1.72M 29.30k 29.79k 29.32k 28.66k 0.00 28.66k # Query size 0 4.48k 76 77 76.40 76.28 0.50 72.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ######################################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE height >= 110 AND height < 150 AND stock > 0G # Query 19: 1.22 QPS, 0.13x concurrency, ID 0x8A99728C0EE94E2E at byte 37059097 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:35 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 45 # Exec time 1 5s 70ms 154ms 108ms 141ms 23ms 105ms # Lock time 0 6ms 23us 1ms 125us 690us 318us 23us # Rows sent 0 1.10k 25 25 25 25 0 25 # Rows examine 1 1.29M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 5.23k 118 121 119.04 118.34 1.50 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ###################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE depth >= 110 AND depth < 150 AND stock > 0 ORDER BY p # Query 20: 12.68 QPS, 0.07x concurrency, ID 0xB626D23952297F80 at byte 29711650 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T05:43:58 to 2022-07-17T05:44:58 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 761 # Exec time 0 4s 1ms 62ms 6ms 14ms 5ms 4ms # Lock time 1 41ms 21us 5ms 54us 42us 237us 23us # Rows sent 11 18.58k 25 25 25 25 0 25 # Rows examine 0 154.85k 78 455 208.36 441.81 135.28 158.58 # Query size 0 166.57k 223 226 224.14 223.14 1.50 223.14 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ########### # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width ```

[youdofoo]

score: 1143,

101adc4b0ff45699ec1f7b213e46ef4d485d4cb7

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1510.txt +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ | 1219 | 829 | 0 | 4 | 386 | GET | /api/chair/search | 0.122 | 1.224 | 182.914 | 0.150 | 0.000 | | 2777 | 2387 | 0 | 4 | 386 | GET | /api/estate/search | 0.105 | 1.580 | 153.059 | 0.055 | 0.324 | | 1191 | 1187 | 0 | 4 | 0 | GET | /api/chair/low_priced | 0.007 | 0.652 | 137.721 | 0.116 | 0.136 | | 1191 | 1189 | 0 | 2 | 0 | GET | /api/estate/low_priced | 0.004 | 0.544 | 29.761 | 0.025 | 0.044 | | 651 | 651 | 0 | 0 | 0 | POST | /api/estate/nazotte | 0.012 | 0.936 | 29.020 | 0.045 | 0.096 | | 1554 | 1547 | 0 | 7 | 0 | GET | /api/estate/\d+ | 0.004 | 0.428 | 25.785 | 0.017 | 0.032 | | 1011 | 1010 | 0 | 1 | 0 | POST | /api/estate/req_doc/\d+ | 0.004 | 0.536 | 15.682 | 0.016 | 0.056 | | 6 | 5 | 0 | 1 | 0 | POST | /api/estate | 0.396 | 2.225 | 10.877 | 1.813 | 1.988 | | 274 | 272 | 0 | 2 | 0 | GET | /api/recommended_estate/\d+ | 0.004 | 1.088 | 10.219 | 0.037 | 0.032 | | 6 | 6 | 0 | 0 | 0 | POST | /api/chair | 0.320 | 1.508 | 7.472 | 1.245 | 1.380 | | 288 | 281 | 0 | 7 | 0 | GET | /api/chair/\d+ | 0.004 | 0.256 | 4.900 | 0.017 | 0.012 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 3.476 | 3.476 | 3.476 | 3.476 | 3.476 | | 133 | 133 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 0.128 | 2.248 | 0.017 | 0.040 | | 401 | 401 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.000 | 0.004 | 0.208 | 0.001 | 0.000 | | 141 | 141 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.008 | 0.100 | 0.001 | 0.000 | +-------+------+-----+-----+-----+--------+------------------------------+-------+-------+---------+-------+-------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1510.txt # 7.7s user time, 40ms system time, 34.22M rss, 89.30M vsz # Current date: Sun Jul 17 06:10:37 2022 # Hostname: isu1 # Files: /var/log/mysql/mysql-slow.log # Overall: 58.80k total, 290 unique, 639.08 QPS, 5.38x concurrency _______ # Time range: 2022-07-17T06:08:08 to 2022-07-17T06:09:40 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 495s 1us 900ms 8ms 71ms 28ms 214us # Lock time 2s 0 48ms 31us 35us 366us 0 # Rows sent 153.21k 0 50 2.67 24.84 7.53 0 # Rows examine 114.81M 0 31.86k 2.00k 28.66k 6.92k 0 # Query size 32.34M 6 241.10k 576.73 563.87 9.97k 31.70 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============= # 1 0xA3BBB20FFD7BCE7C 131.1319 26.5% 1191 0.1101 0.01 SELECT chair # 2 0x30222108492FF4F7 17.0805 3.4% 619 0.0276 0.03 SELECT estate # 3 0x99AA0165670CE848 16.2604 3.3% 19148 0.0008 0.00 ADMIN PREPARE # 4 0xA9A4F380692DE1C8 14.3844 2.9% 132 0.1090 0.01 SELECT chair # 5 0x31D376B5DACE6EAC 13.0783 2.6% 651 0.0201 0.22 SELECT estate # 6 0xF3EFAD4903F0402F 11.3983 2.3% 132 0.0864 0.01 SELECT chair # 7 0x31E27AD9C15C891F 11.0630 2.2% 186 0.0595 0.02 SELECT estate # 8 0x0F8CBC0194D0F55E 7.4880 1.5% 3000 0.0025 0.01 INSERT estate # 9 0x18692940986AC2AD 7.0305 1.4% 51 0.1379 0.00 SELECT estate # 10 0x7FEEE42B5C7412D2 6.7961 1.4% 66 0.1030 0.01 SELECT chair # 11 0xC73BFA73427CD78F 5.9223 1.2% 309 0.0192 0.01 SELECT estate # 12 0x6C1D02F49D3D340F 5.2812 1.1% 102 0.0518 0.00 SELECT estate # 13 0x65DBB57A0B593F83 5.2103 1.1% 66 0.0789 0.01 SELECT chair # 14 0x5ADF2C461C6F48FE 5.0200 1.0% 45 0.1116 0.01 SELECT chair # 15 0x31953C185D32A8B5 4.4834 0.9% 45 0.0996 0.02 SELECT chair # 16 0x61F4988BE0D150F8 4.4262 0.9% 42 0.1054 0.01 SELECT chair # 17 0x1557814C5AD04AAB 4.3777 0.9% 2565 0.0017 0.00 SELECT estate # 18 0x8A99728C0EE94E2E 4.2979 0.9% 42 0.1023 0.02 SELECT chair # 19 0x63C508D1F648A860 4.0809 0.8% 33 0.1237 0.00 SELECT estate # 20 0x750EE375DC7D6A64 3.8784 0.8% 39 0.0994 0.00 SELECT chair # MISC 0xMISC 212.5724 42.9% 30331 0.0070 0.0 <270 ITEMS> # Query 1: 19.21 QPS, 2.12x concurrency, ID 0xA3BBB20FFD7BCE7C at byte 33838395 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:38 to 2022-07-17T06:09:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 1191 # Exec time 26 131s 38ms 201ms 110ms 141ms 25ms 110ms # Lock time 4 79ms 19us 5ms 66us 49us 289us 22us # Rows sent 15 23.26k 20 20 20 20 0 20 # Rows examine 30 34.71M 28.83k 31.76k 29.84k 30.09k 717.90 28.66k # Query size 0 82.58k 71 71 71 71 0 71 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################ # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE stock > 0 ORDER BY price ASC, id ASC LIMIT 20G # Query 2: 10.32 QPS, 0.28x concurrency, ID 0x30222108492FF4F7 at byte 38862930 # This item is included in the report because it matches --limit. # Scores: V/M = 0.03 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 619 # Exec time 3 17s 2ms 190ms 28ms 51ms 28ms 22ms # Lock time 1 33ms 22us 4ms 53us 38us 271us 23us # Rows sent 0 619 1 1 1 1 0 1 # Rows examine 4 5.22M 8.60k 9.16k 8.64k 8.89k 98.71 8.46k # Query size 0 39.58k 65 66 65.48 65.89 0.50 62.76 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ######### # 10ms ################################################################ # 100ms ## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 100000 AND rent < 150000G # Query 3: 308.84 QPS, 0.26x concurrency, ID 0x99AA0165670CE848 at byte 39778450 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:08:38 to 2022-07-17T06:09:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 32 19148 # Exec time 3 16s 44us 28ms 849us 4ms 2ms 260us # 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 1 560.98k 30 30 30 30 0 30 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ################# # 10ms # # 100ms # 1s # 10s+ administrator command: PrepareG # Query 4: 2.40 QPS, 0.26x concurrency, ID 0xA9A4F380692DE1C8 at byte 35305424 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:35 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 132 # Exec time 2 14s 41ms 181ms 109ms 155ms 31ms 100ms # Lock time 0 4ms 23us 203us 28us 47us 16us 23us # Rows sent 2 3.22k 25 25 25 25 0 25 # Rows examine 3 3.81M 29.32k 31.37k 29.59k 30.09k 536.64 28.66k # Query size 0 15.80k 121 125 122.57 118.34 1.01 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################################ # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE price >= 6000 AND price < 9000 AND stock > 0 ORDER BY # Query 5: 10.85 QPS, 0.22x concurrency, ID 0x31D376B5DACE6EAC at byte 27416174 # This item is included in the report because it matches --limit. # Scores: V/M = 0.22 # Time range: 2022-07-17T06:08:39 to 2022-07-17T06:09:39 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 651 # Exec time 2 13s 514us 900ms 20ms 53ms 67ms 6ms # Lock time 1 25ms 22us 2ms 38us 36us 105us 25us # Rows sent 10 16.37k 0 50 25.75 49.17 22.24 23.65 # Rows examine 0 250.04k 0 28.81k 393.30 874.75 2.26k 59.77 # Query size 0 251.94k 336 504 396.29 420.77 24.65 381.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us ################# # 1ms ################################################################ # 10ms ###################################################### # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 6: 2.40 QPS, 0.21x concurrency, ID 0xF3EFAD4903F0402F at byte 38028591 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:35 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 132 # Exec time 2 11s 30ms 152ms 86ms 128ms 27ms 78ms # Lock time 0 8ms 19us 4ms 62us 36us 311us 21us # Rows sent 0 132 1 1 1 1 0 1 # Rows examine 3 3.81M 29.30k 31.25k 29.53k 30.09k 536.64 28.66k # Query size 0 10.05k 77 79 77.95 76.28 0 76.28 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price >= 6000 AND price < 9000 AND stock > 0G # Query 7: 5.47 QPS, 0.33x concurrency, ID 0x31E27AD9C15C891F at byte 32969532 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:14 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 186 # Exec time 2 11s 24ms 379ms 59ms 91ms 30ms 53ms # Lock time 0 5ms 21us 52us 25us 28us 4us 23us # Rows sent 0 186 1 1 1 1 0 1 # Rows examine 4 5.32M 29.30k 29.79k 29.30k 28.66k 0 28.66k # Query size 0 13.35k 73 74 73.48 72.65 0 72.65 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150G # Query 8: 49.18 QPS, 0.12x concurrency, ID 0x0F8CBC0194D0F55E at byte 38802010 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:39 to 2022-07-17T06:09:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 5 3000 # Exec time 1 7s 114us 37ms 2ms 8ms 4ms 972us # Lock time 10 199ms 29us 5ms 66us 60us 239us 35us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 5 1.73M 513 737 603.43 652.75 35.61 592.07 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms ############################################################ # 10ms ##### # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G INSERT INTO estate(id, name, description, thumbnail, address, latitude, longitude, rent, door_height # Query 9: 1.96 QPS, 0.27x concurrency, ID 0x18692940986AC2AD at byte 38090344 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:09:12 to 2022-07-17T06:09:38 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 51 # Exec time 1 7s 108ms 208ms 138ms 180ms 23ms 128ms # Lock time 0 5ms 23us 3ms 94us 54us 360us 25us # Rows sent 0 51 1 1 1 1 0 1 # Rows examine 1 1.23M 5.20k 31.25k 24.65k 30.09k 10.48k 30.09k # Query size 0 5.37k 107 109 107.82 107.34 1 107.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 80 AND door_width < 110 AND rent >= 100000 AND rent # Query 10: 1.74 QPS, 0.18x concurrency, ID 0x7FEEE42B5C7412D2 at byte 33610034 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:41 to 2022-07-17T06:09:19 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 66 # Exec time 1 7s 67ms 181ms 103ms 163ms 29ms 91ms # Lock time 0 4ms 24us 2ms 61us 31us 268us 26us # Rows sent 1 1.61k 25 25 25 25 0 25 # Rows examine 1 1.89M 29.32k 29.91k 29.38k 28.66k 0 28.66k # Query size 0 6.93k 104 115 107.47 112.70 4.08 102.22 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE color = '赤' AND stock > 0 ORDER BY popularity DESC, # Query 11: 5.72 QPS, 0.11x concurrency, ID 0xC73BFA73427CD78F at byte 36880347 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:34 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 309 # Exec time 1 6s 2ms 171ms 19ms 38ms 16ms 16ms # Lock time 3 63ms 20us 48ms 203us 47us 3ms 22us # Rows sent 0 309 1 1 1 1 0 1 # Rows examine 2 2.63M 8.68k 9.26k 8.71k 8.46k 83.71 8.46k # Query size 0 14.48k 48 48 48 48 0 48 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################ # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 150000G # Query 12: 2.32 QPS, 0.12x concurrency, ID 0x6C1D02F49D3D340F at byte 34407631 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:24 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 102 # Exec time 1 5s 15ms 100ms 52ms 82ms 16ms 48ms # Lock time 0 3ms 20us 59us 24us 31us 6us 22us # Rows sent 0 102 1 1 1 1 0 1 # Rows examine 2 2.92M 29.30k 30.27k 29.34k 28.66k 247.89 28.66k # Query size 0 4.98k 50 50 50 50 0 50 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height < 80G # Query 13: 1.74 QPS, 0.14x concurrency, ID 0x65DBB57A0B593F83 at byte 33528831 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:41 to 2022-07-17T06:09:19 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 66 # Exec time 1 5s 21ms 158ms 79ms 128ms 28ms 68ms # Lock time 0 2ms 20us 592us 32us 28us 66us 22us # Rows sent 0 66 1 1 1 1 0 1 # Rows examine 1 1.89M 29.30k 29.79k 29.32k 28.66k 0 28.66k # Query size 0 4.04k 60 69 62.73 65.89 2.80 59.77 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms #################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE color = '赤' AND stock > 0G # Query 14: 1.61 QPS, 0.18x concurrency, ID 0x5ADF2C461C6F48FE at byte 32895333 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:08 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 45 # Exec time 1 5s 60ms 184ms 112ms 155ms 33ms 105ms # Lock time 0 1ms 25us 41us 28us 30us 3us 26us # Rows sent 0 1.10k 25 25 25 25 0 25 # Rows examine 1 1.29M 29.32k 29.42k 29.36k 28.66k 0.00 28.66k # Query size 0 5.09k 109 126 115.91 124.25 5.60 112.70 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################ # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE kind = 'ハンモック' AND stock > 0 ORDER BY popul # Query 15: 1.61 QPS, 0.16x concurrency, ID 0x31953C185D32A8B5 at byte 33292007 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2022-07-17T06:08:40 to 2022-07-17T06:09:08 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 45 # Exec time 0 4s 50ms 323ms 100ms 134ms 44ms 87ms # Lock time 0 1ms 21us 37us 24us 28us 2us 23us # Rows sent 0 45 1 1 1 1 0 1 # Rows examine 1 1.29M 29.30k 29.30k 29.30k 29.30k 0 29.30k # Query size 0 3.13k 65 80 71.20 76.28 5.21 69.19 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE kind = 'ハンモック' AND stock > 0G # Query 16: 1.08 QPS, 0.11x concurrency, ID 0x61F4988BE0D150F8 at byte 33526145 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:08:41 to 2022-07-17T06:09:20 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 42 # Exec time 0 4s 59ms 180ms 105ms 148ms 29ms 95ms # Lock time 0 1ms 23us 51us 26us 28us 4us 25us # Rows sent 0 1.03k 25 25 25 25 0 25 # Rows examine 1 1.21M 29.32k 29.91k 29.42k 28.66k 0.00 28.66k # Query size 0 4.96k 120 123 120.98 118.34 0 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ########################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE height >= 80 AND height < 110 AND stock > 0 ORDER BY # Query 17: 41.37 QPS, 0.07x concurrency, ID 0x1557814C5AD04AAB at byte 27401876 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:08:38 to 2022-07-17T06:09:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 2565 # Exec time 0 4s 76us 40ms 2ms 5ms 2ms 881us # Lock time 8 148ms 20us 9ms 57us 36us 360us 23us # Rows sent 1 2.50k 0 1 1.00 0.99 0.05 0.99 # Rows examine 0 2.50k 0 1 1.00 0.99 0.05 0.99 # Query size 1 377.33k 148 151 150.64 143.84 0 143.84 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # # 100us ################################################################ # 1ms ######################################################### # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 18: 1.45 QPS, 0.15x concurrency, ID 0x8A99728C0EE94E2E at byte 33887257 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2022-07-17T06:08:41 to 2022-07-17T06:09:10 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 42 # Exec time 0 4s 65ms 369ms 102ms 141ms 47ms 87ms # Lock time 0 1ms 22us 55us 27us 42us 7us 23us # Rows sent 0 1.03k 25 25 25 25 0 25 # Rows examine 1 1.20M 29.32k 29.42k 29.35k 28.66k 0.00 28.66k # Query size 0 4.88k 118 121 119 118.34 1.50 118.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ####################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE depth >= 110 AND depth < 150 AND stock > 0 ORDER BY p # Query 19: 1.22 QPS, 0.15x concurrency, ID 0x63C508D1F648A860 at byte 45055166 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:09:11 to 2022-07-17T06:09:38 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 33 # Exec time 0 4s 104ms 144ms 124ms 134ms 10ms 122ms # Lock time 0 4ms 24us 3ms 112us 33us 438us 26us # Rows sent 0 33 1 1 1 1 0 1 # Rows examine 0 998.54k 29.30k 31.25k 30.26k 30.09k 730.55 30.09k # Query size 0 3.54k 109 111 109.82 107.34 0 107.34 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND rent >= 50000 AND ren # Query 20: 1.50 QPS, 0.15x concurrency, ID 0x750EE375DC7D6A64 at byte 34697571 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:08:44 to 2022-07-17T06:09:10 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 39 # Exec time 0 4s 62ms 144ms 99ms 134ms 22ms 95ms # Lock time 0 1ms 22us 240us 31us 28us 33us 23us # Rows sent 0 975 25 25 25 25 0 25 # Rows examine 0 1.12M 29.32k 29.42k 29.35k 28.66k 0 28.66k # Query size 0 3.95k 103 105 103.62 102.22 0 102.22 # String: # Databases isuumo # Hosts localhost # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################################################ # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT Makefile post-issue.sh FROM chair WHERE price < 3000 AND stock > 0 ORDER BY popularity DESC, ```

[youdofoo]

score: 1143,

ab38d6597aa5721f31b46b54bbbc8a9c2e08f5b4

alp

``` ```

pt-query-digest

``` ```

[youdofoo]

score: 2107,

ab38d6597aa5721f31b46b54bbbc8a9c2e08f5b4

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1544.txt +-------+------+-----+-----+------+--------+------------------------------+-------+-------+---------+-------+-------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+------+--------+------------------------------+-------+-------+---------+-------+-------+ | 3776 | 2745 | 0 | 8 | 1023 | GET | /api/estate/search | 0.003 | 1.496 | 345.627 | 0.092 | 0.296 | | 2865 | 1835 | 0 | 7 | 1023 | GET | /api/chair/search | 0.008 | 1.432 | 317.928 | 0.111 | 0.464 | | 2160 | 2157 | 0 | 3 | 0 | GET | /api/estate/low_priced | 0.004 | 1.364 | 120.156 | 0.056 | 0.012 | | 2160 | 2155 | 0 | 5 | 0 | GET | /api/chair/low_priced | 0.008 | 1.028 | 108.192 | 0.050 | 0.052 | | 2586 | 2569 | 0 | 17 | 0 | GET | /api/estate/\d+ | 0.004 | 1.520 | 107.924 | 0.042 | 0.020 | | 1356 | 1353 | 0 | 3 | 0 | POST | /api/estate/nazotte | 0.004 | 1.232 | 95.467 | 0.070 | 0.032 | | 1805 | 1803 | 0 | 2 | 0 | POST | /api/estate/req_doc/\d+ | 0.004 | 1.692 | 82.852 | 0.046 | 0.012 | | 613 | 611 | 0 | 2 | 0 | GET | /api/recommended_estate/\d+ | 0.004 | 1.140 | 37.777 | 0.062 | 0.200 | | 637 | 625 | 0 | 12 | 0 | GET | /api/chair/\d+ | 0.004 | 0.872 | 16.272 | 0.026 | 0.008 | | 11 | 11 | 0 | 0 | 0 | POST | /api/chair | 0.280 | 1.980 | 14.912 | 1.356 | 1.980 | | 304 | 304 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 1.192 | 11.892 | 0.039 | 0.016 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 8.676 | 8.676 | 8.676 | 8.676 | 8.676 | | 11 | 11 | 0 | 0 | 0 | POST | /api/estate | 0.080 | 0.556 | 4.264 | 0.388 | 0.556 | | 470 | 470 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.004 | 0.040 | 0.480 | 0.001 | 0.000 | | 329 | 329 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.036 | 0.392 | 0.001 | 0.000 | +-------+------+-----+-----+------+--------+------------------------------+-------+-------+---------+-------+-------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1544.txt # 5.2s user time, 50ms system time, 34.66M rss, 89.54M vsz # Current date: Sun Jul 17 06:44:58 2022 # Hostname: isu2 # Files: /var/log/mysql/mysql-slow.log # Overall: 29.86k total, 269 unique, 420.63 QPS, 5.90x concurrency _______ # Time range: 2022-07-17T06:43:11 to 2022-07-17T06:44:22 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 419s 2us 1s 14ms 95ms 42ms 273us # Lock time 4s 0 63ms 126us 131us 828us 76us # Rows sent 255.89k 0 50 8.77 24.84 11.85 0.99 # Rows examine 60.23M 0 34.18k 2.07k 8.46k 6.37k 19.46 # Query size 34.91M 6 241.10k 1.20k 563.87 14.60k 158.58 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============== # 1 0x31D376B5DACE6EAC 22.5593 5.4% 1356 0.0166 0.51 SELECT estate # 2 0x63C508D1F648A860 8.0111 1.9% 95 0.0843 0.00 SELECT estate # 3 0x2BADAF35121DE0DA 6.1076 1.5% 111 0.0550 0.01 SELECT chair # 4 0x508494EE7F044B2E 6.0620 1.4% 5500 0.0011 0.02 INSERT chair # 5 0x30222108492FF4F7 5.9768 1.4% 568 0.0105 0.04 SELECT estate # 6 0xF3F0AA53B49E113A 5.9746 1.4% 65 0.0919 0.00 SELECT estate # 7 0x24BAB32B2AE0D7A7 5.9706 1.4% 31 0.1926 0.10 SELECT estate # 8 0xC61E11AF8CECCADA 5.2900 1.3% 69 0.0767 0.00 SELECT estate # 9 0x78B040859E65BE51 5.2446 1.3% 39 0.1345 0.01 SELECT chair # 10 0x7D4B8746ECF73190 5.2431 1.3% 36 0.1456 0.00 SELECT chair # 11 0x51BE7844A8E1CF76 5.1375 1.2% 59 0.0871 0.05 INSERT isuumo.estate # 12 0xA13AA3D6D9AACFA7 5.1085 1.2% 39 0.1310 0.00 SELECT chair # 13 0x4B46BB4B5A7FD55B 5.0380 1.2% 65 0.0775 0.05 SELECT estate # 14 0x18692940986AC2AD 4.9825 1.2% 58 0.0859 0.00 SELECT estate # 15 0x31E27AD9C15C891F 4.9300 1.2% 138 0.0357 0.05 SELECT estate # 16 0x25BCA482E6D53439 4.9049 1.2% 36 0.1362 0.01 SELECT chair # 17 0x3824D6EDB46CDE0B 4.8223 1.2% 30 0.1607 0.00 SELECT chair # 18 0x90D3568D05898BFF 4.7688 1.1% 30 0.1590 0.00 SELECT chair # 19 0x816ECD8C81A430B8 4.6484 1.1% 30 0.1549 0.01 SELECT chair # 20 0x67A0CB124E70256F 4.5174 1.1% 54 0.0837 0.00 SELECT estate # MISC 0xMISC 293.7819 70.1% 21456 0.0137 0.0 <249 ITEMS> # Query 1: 21.87 QPS, 0.36x concurrency, ID 0x31D376B5DACE6EAC at byte 31013195 # This item is included in the report because it matches --limit. # Scores: V/M = 0.51 # Time range: 2022-07-17T06:43:20 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 1356 # Exec time 5 23s 180us 1s 17ms 42ms 92ms 1ms # Lock time 3 128ms 54us 8ms 94us 125us 238us 73us # Rows sent 14 37.71k 0 50 28.48 49.17 22.21 40.45 # Rows examine 1 664.67k 0 29.30k 501.93 1.04k 2.50k 112.70 # Query size 1 524.84k 315 525 396.34 420.77 24.26 381.65 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms ####################################### # 10ms ########################## # 100ms # # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 2: 2.02 QPS, 0.17x concurrency, ID 0x63C508D1F648A860 at byte 40524117 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:35 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 95 # Exec time 1 8s 27ms 114ms 84ms 105ms 17ms 82ms # Lock time 0 6ms 44us 344us 63us 80us 30us 54us # Rows sent 0 95 1 1 1 1 0 1 # Rows examine 4 2.97M 29.30k 34.18k 32.05k 33.17k 1.69k 31.59k # Query size 0 10.20k 109 111 109.94 107.34 0 107.34 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND rent >= 100000 AND re # Query 3: 6.94 QPS, 0.38x concurrency, ID 0x2BADAF35121DE0DA at byte 30678772 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:43:22 to 2022-07-17T06:43:38 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 111 # Exec time 1 6s 12ms 105ms 55ms 87ms 20ms 51ms # Lock time 0 11ms 68us 421us 97us 125us 33us 89us # Rows sent 0 111 1 1 1 1 0 1 # Rows examine 5 3.18M 29.30k 29.79k 29.31k 28.66k 0 28.66k # Query size 0 6.29k 58 58 58 58 0 58 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE kind_id = 3 AND stock > 0G # Query 4: 105.77 QPS, 0.12x concurrency, ID 0x508494EE7F044B2E at byte 36752547 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2022-07-17T06:43:21 to 2022-07-17T06:44:13 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 18 5500 # Exec time 1 6s 141us 56ms 1ms 6ms 4ms 176us # Lock time 12 481ms 70us 5ms 87us 103us 67us 80us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 8 2.82M 443 656 538.30 592.07 35.23 511.45 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms ## # 10ms ## # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G INSERT INTO chair(id, name, description, thumbnail, price, height, width, depth, color, features, ki # Query 5: 11.14 QPS, 0.12x concurrency, ID 0x30222108492FF4F7 at byte 34653780 # This item is included in the report because it matches --limit. # Scores: V/M = 0.04 # Time range: 2022-07-17T06:43:22 to 2022-07-17T06:44:13 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 568 # Exec time 1 6s 2ms 142ms 11ms 28ms 20ms 4ms # Lock time 1 38ms 33us 5ms 67us 76us 243us 47us # Rows sent 0 568 1 1 1 1 0 1 # Rows examine 7 4.81M 8.60k 10.01k 8.68k 8.89k 212.54 8.46k # Query size 0 36.34k 65 66 65.51 65.89 0.50 65.89 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ################## # 100ms ## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 50000 AND rent < 100000G # Query 6: 1.81 QPS, 0.17x concurrency, ID 0xF3F0AA53B49E113A at byte 43091556 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:46 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 65 # Exec time 1 6s 60ms 130ms 92ms 105ms 13ms 91ms # Lock time 0 5ms 47us 267us 69us 89us 30us 60us # Rows sent 0 65 1 1 1 1 0 1 # Rows examine 3 2.10M 31.25k 34.18k 33.02k 33.17k 886.42 31.59k # Query size 0 9.68k 151 154 152.49 151.03 1.50 151.03 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND door_width >= 80 AND # Query 7: 0.91 QPS, 0.18x concurrency, ID 0x24BAB32B2AE0D7A7 at byte 43312679 # This item is included in the report because it matches --limit. # Scores: V/M = 0.10 # Time range: 2022-07-17T06:43:48 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 31 # Exec time 1 6s 5ms 422ms 193ms 393ms 141ms 116ms # Lock time 0 3ms 63us 415us 98us 131us 59us 80us # Rows sent 0 775 25 25 25 25 0 25 # Rows examine 0 350.99k 3.27k 25.03k 11.32k 21.38k 7.51k 7.31k # Query size 0 8.84k 290 294 291.90 284.79 0 284.79 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ### # 10ms ########################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 8: 1.47 QPS, 0.11x concurrency, ID 0xC61E11AF8CECCADA at byte 43500617 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:35 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 69 # Exec time 1 5s 33ms 104ms 77ms 95ms 15ms 75ms # Lock time 0 4ms 40us 311us 65us 89us 39us 54us # Rows sent 0 69 1 1 1 1 0 1 # Rows examine 3 2.16M 29.30k 34.18k 32.08k 33.17k 1.55k 31.59k # Query size 0 5.90k 87 88 87.52 84.10 0 84.10 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ######## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 150 AND rent >= 100000 AND rent < 150000G # Query 9: 1.22 QPS, 0.16x concurrency, ID 0x78B040859E65BE51 at byte 37686565 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:43:50 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 39 # Exec time 1 5s 23ms 185ms 134ms 171ms 27ms 128ms # Lock time 0 5ms 93us 175us 131us 159us 19us 131us # Rows sent 0 975 25 25 25 25 0 25 # Rows examine 0 12.21k 177 533 320.62 420.77 84.12 299.03 # Query size 0 9.65k 253 254 253.28 246.02 0.00 246.02 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,name,description,thumbnail,price,height,width,depth,color,features,kind,popularity,stock F # Query 10: 1.03 QPS, 0.15x concurrency, ID 0x7D4B8746ECF73190 at byte 34801028 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:42 to 2022-07-17T06:44:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 36 # Exec time 1 5s 73ms 204ms 146ms 180ms 25ms 141ms # Lock time 0 5ms 95us 206us 125us 176us 26us 119us # Rows sent 0 900 25 25 25 25 0 25 # Rows examine 0 14.60k 190 817 415.17 685.39 152.93 381.65 # Query size 0 8.35k 237 239 237.58 234.30 0 234.30 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,name,description,thumbnail,price,height,width,depth,color,features,kind,popularity,stock F # Query 11: 11.80 QPS, 1.03x concurrency, ID 0x51BE7844A8E1CF76 at byte 496353 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2022-07-17T06:43:12 to 2022-07-17T06:43:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 59 # Exec time 1 5s 26ms 435ms 87ms 163ms 65ms 87ms # Lock time 8 320ms 5ms 13ms 5ms 5ms 1ms 5ms # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 39 13.78M 237.48k 241.10k 239.21k 233.54k 0 233.54k # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G INSERT INTO isuumo.estate (id, thumbnail, name, latitude, longitude, address, rent, door_height, doo # Query 12: 1.22 QPS, 0.16x concurrency, ID 0xA13AA3D6D9AACFA7 at byte 37358682 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:50 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 39 # Exec time 1 5s 61ms 179ms 131ms 155ms 25ms 128ms # Lock time 0 4ms 83us 175us 115us 144us 19us 108us # Rows sent 0 39 1 1 1 1 0 1 # Rows examine 0 10.92k 152 508 286.64 381.65 80.02 271.23 # Query size 0 4.42k 116 116 116 116 0 116 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ##### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price_id = '0' AND height_id = '3' AND depth_id = '3' AND kind_id = # Query 13: 1.86 QPS, 0.14x concurrency, ID 0x4B46BB4B5A7FD55B at byte 39518891 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2022-07-17T06:43:47 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 65 # Exec time 1 5s 3ms 225ms 78ms 171ms 60ms 65ms # Lock time 0 6ms 64us 487us 87us 108us 50us 76us # Rows sent 0 1.59k 25 25 25 25 0 25 # Rows examine 0 317.45k 1.57k 12.79k 4.88k 10.29k 3.11k 3.88k # Query size 0 19.75k 309 313 311.20 299.03 0 299.03 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################### # 10ms ####################################################### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 14: 1.23 QPS, 0.11x concurrency, ID 0x18692940986AC2AD at byte 43438144 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:35 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 58 # Exec time 1 5s 37ms 120ms 86ms 116ms 19ms 82ms # Lock time 0 4ms 45us 124us 64us 98us 18us 57us # Rows sent 0 58 1 1 1 1 0 1 # Rows examine 2 1.67M 5.20k 34.18k 29.41k 33.17k 8.18k 31.59k # Query size 0 6.11k 107 109 107.88 107.34 1 107.34 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 110 AND door_width < 150 AND rent >= 100000 AND rent # Query 15: 7.26 QPS, 0.26x concurrency, ID 0x31E27AD9C15C891F at byte 33845424 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2022-07-17T06:43:22 to 2022-07-17T06:43:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 138 # Exec time 1 5s 8ms 335ms 36ms 59ms 43ms 27ms # Lock time 0 8ms 34us 1ms 61us 76us 102us 47us # Rows sent 0 138 1 1 1 1 0 1 # Rows examine 6 3.96M 29.30k 30.27k 29.36k 28.66k 274.17 28.66k # Query size 0 9.92k 73 74 73.63 72.65 0 72.65 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150G # Query 16: 1.03 QPS, 0.14x concurrency, ID 0x25BCA482E6D53439 at byte 34901129 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:43:42 to 2022-07-17T06:44:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 36 # Exec time 1 5s 91ms 203ms 136ms 180ms 27ms 134ms # Lock time 0 4ms 84us 168us 111us 159us 21us 108us # Rows sent 0 36 1 1 1 1 0 1 # Rows examine 0 12.85k 165 692 365.64 685.39 146.30 346.17 # Query size 0 3.52k 100 100 100 100 0 100 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ### # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price_id = '5' AND height_id = '3' AND depth_id = '0' AND stock > 0 # Query 17: 1.03 QPS, 0.17x concurrency, ID 0x3824D6EDB46CDE0B at byte 43935870 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:53 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 30 # Exec time 1 5s 125ms 214ms 161ms 198ms 24ms 159ms # Lock time 0 4ms 102us 221us 129us 152us 22us 125us # Rows sent 0 750 25 25 25 25 0 25 # Rows examine 0 14.07k 273 906 480.20 833.10 168.60 420.77 # Query size 0 7.43k 253 255 253.53 246.02 0 246.02 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,name,description,thumbnail,price,height,width,depth,color,features,kind,popularity,stock F # Query 18: 1 QPS, 0.16x concurrency, ID 0x90D3568D05898BFF at byte 43911330 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:52 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 30 # Exec time 1 5s 120ms 212ms 159ms 198ms 26ms 155ms # Lock time 0 3ms 89us 171us 112us 138us 19us 103us # Rows sent 0 30 1 1 1 1 0 1 # Rows examine 0 12.70k 248 831 433.50 793.42 155.49 363.48 # Query size 0 3.40k 116 116 116 116 0 116 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM chair WHERE price_id = '5' AND height_id = '0' AND width_id = '0' AND kind_id = # Query 19: 0.77 QPS, 0.12x concurrency, ID 0x816ECD8C81A430B8 at byte 43222714 # This item is included in the report because it matches --limit. # Scores: V/M = 0.01 # Time range: 2022-07-17T06:43:43 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 30 # Exec time 1 5s 72ms 210ms 155ms 189ms 32ms 155ms # Lock time 0 4ms 101us 152us 125us 144us 14us 119us # Rows sent 0 750 25 25 25 25 0 25 # Rows examine 0 12.80k 202 853 436.93 755.64 161.54 420.77 # Query size 0 6.96k 237 239 237.63 234.30 0 234.30 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'chair'G # SHOW CREATE TABLE `isuumo`.`chair`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,name,description,thumbnail,price,height,width,depth,color,features,kind,popularity,stock F # Query 20: 1.26 QPS, 0.11x concurrency, ID 0x67A0CB124E70256F at byte 41617537 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T06:43:39 to 2022-07-17T06:44:22 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 54 # Exec time 1 5s 40ms 135ms 84ms 105ms 20ms 82ms # Lock time 0 3ms 37us 93us 56us 76us 11us 52us # Rows sent 0 54 1 1 1 1 0 1 # Rows examine 0 201.25k 3.48k 4.01k 3.73k 3.88k 210.23 3.69k # Query size 0 4.66k 88 89 88.39 88.31 0.50 84.10 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_width >= 80 AND door_width < 110 AND rent < 50000G ```

[youdofoo]

score: 4241,

0646f0b7c0888a3699f529991b81c29c719aefe4

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1636.txt +-------+------+-----+-----+------+--------+------------------------------+--------+--------+---------+--------+--------+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+------+-----+-----+------+--------+------------------------------+--------+--------+---------+--------+--------+ | 6225 | 4993 | 0 | 9 | 1223 | GET | /api/estate/search | 0.002 | 0.760 | 434.244 | 0.070 | 0.000 | | 4001 | 2768 | 0 | 10 | 1223 | GET | /api/chair/search | 0.006 | 0.712 | 428.725 | 0.107 | 0.192 | | 3116 | 3113 | 0 | 3 | 0 | POST | /api/estate/nazotte | 0.004 | 0.824 | 125.284 | 0.040 | 0.032 | | 4457 | 4457 | 0 | 0 | 0 | GET | /api/chair/low_priced | 0.004 | 0.364 | 116.948 | 0.026 | 0.044 | | 4457 | 4456 | 0 | 1 | 0 | GET | /api/estate/low_priced | 0.004 | 0.500 | 104.066 | 0.023 | 0.020 | | 5100 | 5087 | 0 | 13 | 0 | GET | /api/estate/\d+ | 0.004 | 0.620 | 99.442 | 0.019 | 0.148 | | 3747 | 3745 | 0 | 2 | 0 | POST | /api/estate/req_doc/\d+ | 0.001 | 0.640 | 74.370 | 0.020 | 0.032 | | 999 | 998 | 0 | 1 | 0 | GET | /api/recommended_estate/\d+ | 0.004 | 0.504 | 35.021 | 0.035 | 0.044 | | 1023 | 1011 | 0 | 12 | 0 | GET | /api/chair/\d+ | 0.004 | 0.164 | 12.620 | 0.012 | 0.072 | | 1 | 1 | 0 | 0 | 0 | POST | /initialize | 10.472 | 10.472 | 10.472 | 10.472 | 10.472 | | 497 | 497 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 0.004 | 0.320 | 10.176 | 0.020 | 0.020 | | 11 | 11 | 0 | 0 | 0 | POST | /api/estate | 0.060 | 0.604 | 4.800 | 0.436 | 0.400 | | 11 | 11 | 0 | 0 | 0 | POST | /api/chair | 0.040 | 0.436 | 2.592 | 0.236 | 0.436 | | 838 | 838 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.004 | 0.020 | 1.244 | 0.001 | 0.004 | | 508 | 508 | 0 | 0 | 0 | GET | /api/chair/search/condition | 0.000 | 0.020 | 0.700 | 0.001 | 0.004 | +-------+------+-----+-----+------+--------+------------------------------+--------+--------+---------+--------+--------+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1636.txt # 4.8s user time, 30ms system time, 32.88M rss, 87.94M vsz # Current date: Sun Jul 17 07:36:42 2022 # Hostname: isu2 # Files: /var/log/mysql/mysql-slow.log # Overall: 29.07k total, 150 unique, 409.48 QPS, 4.92x concurrency _______ # Time range: 2022-07-17T07:34:45 to 2022-07-17T07:35:56 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 349s 2us 819ms 12ms 87ms 37ms 260us # Lock time 3s 0 49ms 108us 98us 793us 57us # Rows sent 323.43k 0 50 11.39 36.69 13.37 0.99 # Rows examine 76.33M 0 34.18k 2.69k 9.80k 6.78k 19.46 # Query size 33.56M 6 241.10k 1.18k 381.65 14.79k 143.84 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============= # 1 0x31D376B5DACE6EAC 46.5788 13.3% 3116 0.0149 0.26 SELECT estate # 2 0xF3F0AA53B49E113A 19.2668 5.5% 205 0.0940 0.00 SELECT estate # 3 0x4B46BB4B5A7FD55B 16.5669 4.7% 205 0.0808 0.05 SELECT estate # 4 0x24BAB32B2AE0D7A7 16.2482 4.7% 96 0.1693 0.10 SELECT estate # 5 0x3713BE2BD71E6ED5 9.8541 2.8% 105 0.0938 0.00 SELECT estate # 6 0x81B0285E8EC89649 9.0345 2.6% 96 0.0941 0.00 SELECT estate # 7 0x435C972E2F3BA286 8.7651 2.5% 93 0.0942 0.00 SELECT estate # 8 0xD8DC9BFE9D564768 7.5387 2.2% 48 0.1571 0.08 SELECT estate # 9 0xF752775EA2C3B4A4 7.2331 2.1% 75 0.0964 0.00 SELECT estate # 10 0xF34E7E69DCC3018E 7.1340 2.0% 81 0.0881 0.00 SELECT estate # 11 0x6E623BB0703B1128 6.5251 1.9% 51 0.1279 0.09 SELECT estate # 12 0xC2E816033BC67E9F 5.8153 1.7% 105 0.0554 0.03 SELECT estate # 13 0x5F79CA5646A44C9E 5.4549 1.6% 66 0.0826 0.00 SELECT estate # 14 0x30222108492FF4F7 5.4141 1.6% 630 0.0086 0.04 SELECT estate # 15 0xD742910E525182E3 5.3308 1.5% 60 0.0888 0.00 SELECT estate # 16 0x9A779AAE5C3E92CB 5.1138 1.5% 57 0.0897 0.00 SELECT estate # 17 0xCC9CB2979615E01C 4.9865 1.4% 57 0.0875 0.00 SELECT estate # 18 0x12CF64A1A4723317 4.7938 1.4% 39 0.1229 0.09 SELECT estate # 19 0x980B682C304C25A6 4.7283 1.4% 51 0.0927 0.00 SELECT estate # 20 0x8D00DA06D670178F 4.6547 1.3% 75 0.0621 0.05 SELECT estate # MISC 0xMISC 148.1112 42.4% 23762 0.0062 0.0 <130 ITEMS> # Query 1: 51.08 QPS, 0.76x concurrency, ID 0x31D376B5DACE6EAC at byte 29773151 # This item is included in the report because it matches --limit. # Scores: V/M = 0.26 # Time range: 2022-07-17T07:34:55 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 10 3116 # Exec time 13 47s 179us 819ms 15ms 46ms 62ms 925us # Lock time 11 346ms 50us 27ms 110us 108us 726us 69us # Rows sent 25 81.27k 0 50 26.71 49.17 22.26 28.75 # Rows examine 2 1.86M 0 29.30k 625.62 1.09k 3.09k 80.10 # Query size 3 1.18M 315 483 395.50 420.77 23.59 381.65 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms ############################### # 10ms ############################# # 100ms ## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 2: 4.77 QPS, 0.45x concurrency, ID 0xF3F0AA53B49E113A at byte 41208610 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:13 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 205 # Exec time 5 19s 41ms 127ms 94ms 110ms 14ms 91ms # Lock time 0 13ms 43us 1ms 65us 80us 68us 57us # Rows sent 0 205 1 1 1 1 0 1 # Rows examine 8 6.77M 30.76k 34.18k 33.83k 33.17k 790.21 33.17k # Query size 0 30.55k 151 154 152.60 151.03 1.50 151.03 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND door_width >= 80 AND # Query 3: 4.77 QPS, 0.39x concurrency, ID 0x4B46BB4B5A7FD55B at byte 37670688 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2022-07-17T07:35:13 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 205 # Exec time 4 17s 2ms 277ms 81ms 198ms 64ms 59ms # Lock time 0 21ms 58us 3ms 100us 119us 200us 76us # Rows sent 1 5.00k 25 25 25 25 0 25 # Rows examine 1 1.00M 1.13k 13.58k 5.01k 10.29k 3.42k 3.70k # Query size 0 62.32k 309 314 311.31 299.03 2.07 299.03 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ######## # 10ms ################################################################ # 100ms ########################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 4: 2.18 QPS, 0.37x concurrency, ID 0x24BAB32B2AE0D7A7 at byte 39639352 # This item is included in the report because it matches --limit. # Scores: V/M = 0.10 # Time range: 2022-07-17T07:35:12 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 96 # Exec time 4 16s 14ms 580ms 169ms 433ms 132ms 105ms # Lock time 0 8ms 58us 366us 80us 108us 31us 69us # Rows sent 0 2.34k 25 25 25 25 0 25 # Rows examine 1 975.72k 3.32k 30.85k 10.16k 23.58k 6.90k 6.31k # Query size 0 27.33k 290 294 291.50 284.79 0 284.79 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 5: 2.44 QPS, 0.23x concurrency, ID 0x3713BE2BD71E6ED5 at byte 34739787 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:12 to 2022-07-17T07:35:55 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 105 # Exec time 2 10s 16ms 118ms 94ms 110ms 16ms 95ms # Lock time 0 8ms 40us 634us 72us 93us 62us 57us # Rows sent 0 105 1 1 1 1 0 1 # Rows examine 4 3.47M 30.27k 34.18k 33.86k 33.17k 633.33 33.17k # Query size 0 13.34k 129 131 130.06 130.47 1 124.25 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND door_width < 80 AND r # Query 6: 2.18 QPS, 0.21x concurrency, ID 0x81B0285E8EC89649 at byte 41430979 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:12 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 96 # Exec time 2 9s 42ms 135ms 94ms 122ms 17ms 95ms # Lock time 0 11ms 43us 5ms 109us 80us 464us 57us # Rows sent 0 96 1 1 1 1 0 1 # Rows examine 0 382.55k 3.55k 4.01k 3.98k 3.88k 89.34 3.88k # Query size 0 12.47k 132 134 132.97 130.47 0 130.47 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110 AND door_width >= 80 AND d # Query 7: 2.33 QPS, 0.22x concurrency, ID 0x435C972E2F3BA286 at byte 33832967 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:15 to 2022-07-17T07:35:55 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 93 # Exec time 2 9s 55ms 124ms 94ms 110ms 13ms 95ms # Lock time 0 6ms 43us 102us 59us 80us 11us 54us # Rows sent 0 93 1 1 1 1 0 1 # Rows examine 4 3.07M 31.25k 34.18k 33.79k 33.17k 725.95 33.17k # Query size 0 12.00k 131 133 132.13 130.47 0 130.47 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ############################################################ # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110 AND door_width >= 150 AND # Query 8: 1.14 QPS, 0.18x concurrency, ID 0xD8DC9BFE9D564768 at byte 34438831 # This item is included in the report because it matches --limit. # Scores: V/M = 0.08 # Time range: 2022-07-17T07:35:14 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 48 # Exec time 2 8s 41ms 401ms 157ms 356ms 110ms 110ms # Lock time 0 4ms 58us 150us 76us 98us 16us 69us # Rows sent 0 1.17k 25 25 25 25 0 25 # Rows examine 0 461.93k 3.64k 21.48k 9.62k 20.37k 6.19k 6.31k # Query size 0 12.61k 268 271 268.94 258.32 0 258.32 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ########################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 9: 1.92 QPS, 0.19x concurrency, ID 0xF752775EA2C3B4A4 at byte 33404560 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:17 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 75 # Exec time 2 7s 50ms 124ms 96ms 116ms 14ms 95ms # Lock time 0 4ms 41us 103us 59us 76us 10us 54us # Rows sent 0 75 1 1 1 1 0 1 # Rows examine 3 2.49M 32.23k 34.18k 33.95k 33.17k 525.65 33.17k # Query size 0 9.91k 134 136 135.28 130.47 0 130.47 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ######################################################## # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110 AND door_width >= 80 AND d # Query 10: 2.08 QPS, 0.18x concurrency, ID 0xF34E7E69DCC3018E at byte 38385382 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:16 to 2022-07-17T07:35:55 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 81 # Exec time 2 7s 39ms 121ms 88ms 105ms 15ms 87ms # Lock time 0 5ms 40us 126us 58us 69us 11us 54us # Rows sent 0 81 1 1 1 1 0 1 # Rows examine 3 2.69M 31.74k 34.18k 33.96k 33.17k 423.63 33.17k # Query size 0 10.23k 128 130 129.33 124.25 0 124.25 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ############################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height < 80 AND door_width >= 110 AND door_width < 150 AND re # Query 11: 1.27 QPS, 0.16x concurrency, ID 0x6E623BB0703B1128 at byte 37708198 # This item is included in the report because it matches --limit. # Scores: V/M = 0.09 # Time range: 2022-07-17T07:35:14 to 2022-07-17T07:35:54 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 51 # Exec time 1 7s 4ms 367ms 128ms 356ms 106ms 71ms # Lock time 0 4ms 55us 101us 75us 89us 11us 69us # Rows sent 0 1.25k 25 25 25 25 0 25 # Rows examine 0 387.21k 1.90k 21.13k 7.59k 20.37k 5.83k 4.71k # Query size 0 13.50k 270 273 271.04 271.23 1.50 258.32 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms #### # 10ms ################################################################ # 100ms ################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 12: 2.44 QPS, 0.14x concurrency, ID 0xC2E816033BC67E9F at byte 35498721 # This item is included in the report because it matches --limit. # Scores: V/M = 0.03 # Time range: 2022-07-17T07:35:12 to 2022-07-17T07:35:55 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 105 # Exec time 1 6s 2ms 158ms 55ms 134ms 43ms 42ms # Lock time 0 8ms 55us 320us 80us 108us 27us 73us # Rows sent 0 2.56k 25 25 25 25 0 25 # Rows examine 0 381.07k 1.19k 9.34k 3.63k 7.31k 2.28k 2.76k # Query size 0 29.60k 287 291 288.71 284.79 0 284.79 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################# # 10ms ################################################################ # 100ms #################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 13: 1.50 QPS, 0.12x concurrency, ID 0x5F79CA5646A44C9E at byte 35306549 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:12 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 66 # Exec time 1 5s 41ms 107ms 83ms 100ms 15ms 82ms # Lock time 0 4ms 41us 116us 61us 76us 12us 57us # Rows sent 0 66 1 1 1 1 0 1 # Rows examine 2 2.16M 30.27k 34.18k 33.57k 33.17k 1.08k 33.17k # Query size 0 8.46k 130 132 131.23 130.47 1 130.47 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ###################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 150 AND door_width >= 110 AND door_width < 150 AND # Query 14: 10.68 QPS, 0.09x concurrency, ID 0x30222108492FF4F7 at byte 33060044 # This item is included in the report because it matches --limit. # Scores: V/M = 0.04 # Time range: 2022-07-17T07:34:56 to 2022-07-17T07:35:55 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 630 # Exec time 1 5s 2ms 142ms 9ms 26ms 18ms 3ms # Lock time 1 49ms 32us 4ms 78us 113us 214us 52us # Rows sent 0 630 1 1 1 1 0 1 # Rows examine 7 5.44M 8.60k 10.01k 8.85k 9.80k 450.68 8.46k # Query size 0 40.31k 65 66 65.52 65.89 0.50 65.89 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms ########## # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE rent >= 100000 AND rent < 150000G # Query 15: 1.54 QPS, 0.14x concurrency, ID 0xD742910E525182E3 at byte 42255959 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:17 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 60 # Exec time 1 5s 60ms 116ms 89ms 105ms 12ms 87ms # Lock time 0 3ms 38us 111us 55us 69us 10us 52us # Rows sent 0 60 1 1 1 1 0 1 # Rows examine 2 1.98M 32.23k 34.18k 33.84k 33.17k 577.59 33.17k # Query size 0 6.23k 106 107 106.35 102.22 0 102.22 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ####################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height < 80 AND door_width < 80 AND rent >= 50000 AND rent < # Query 16: 1.58 QPS, 0.14x concurrency, ID 0x9A779AAE5C3E92CB at byte 37492825 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:15 to 2022-07-17T07:35:51 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 57 # Exec time 1 5s 45ms 119ms 90ms 105ms 16ms 87ms # Lock time 0 3ms 42us 105us 60us 84us 12us 54us # Rows sent 0 57 1 1 1 1 0 1 # Rows examine 2 1.90M 31.74k 34.18k 34.05k 33.17k 361.20 33.17k # Query size 0 6.31k 113 114 113.42 112.70 0 112.70 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ########################################### # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 150 AND door_width >= 80 AND door_width < 110 AND r # Query 17: 1.50 QPS, 0.13x concurrency, ID 0xCC9CB2979615E01C at byte 34040614 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:18 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 57 # Exec time 1 5s 47ms 123ms 87ms 110ms 16ms 87ms # Lock time 0 9ms 43us 6ms 156us 84us 696us 54us # Rows sent 0 57 1 1 1 1 0 1 # Rows examine 2 1.88M 32.71k 34.18k 33.78k 33.17k 638.36 33.17k # Query size 0 6.03k 108 109 108.28 107.34 0 107.34 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ######################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 150 AND door_width < 80 AND rent >= 50000 AND rent # Query 18: 1.08 QPS, 0.13x concurrency, ID 0x12CF64A1A4723317 at byte 37705307 # This item is included in the report because it matches --limit. # Scores: V/M = 0.09 # Time range: 2022-07-17T07:35:20 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 39 # Exec time 1 5s 18ms 397ms 123ms 356ms 106ms 65ms # Lock time 0 3ms 55us 122us 73us 89us 12us 69us # Rows sent 0 975 25 25 25 25 0 25 # Rows examine 0 279.26k 2.72k 20.90k 7.16k 20.37k 5.74k 3.04k # Query size 0 10.29k 269 271 270.08 258.32 0 258.32 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################################# # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width # Query 19: 1.27 QPS, 0.12x concurrency, ID 0x980B682C304C25A6 at byte 39960296 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2022-07-17T07:35:14 to 2022-07-17T07:35:54 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 51 # Exec time 1 5s 70ms 126ms 93ms 105ms 11ms 91ms # Lock time 0 11ms 40us 8ms 214us 89us 1ms 54us # Rows sent 0 51 1 1 1 1 0 1 # Rows examine 0 202.21k 3.61k 4.01k 3.96k 3.88k 101.41 3.88k # Query size 0 5.60k 112 113 112.41 112.70 0.50 107.34 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms ################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM estate WHERE door_height >= 110 AND door_height < 150 AND door_width >= 150 AND # Query 20: 1.92 QPS, 0.12x concurrency, ID 0x8D00DA06D670178F at byte 33509405 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2022-07-17T07:35:17 to 2022-07-17T07:35:56 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 75 # Exec time 1 5s 2ms 242ms 62ms 155ms 56ms 38ms # Lock time 0 6ms 62us 113us 76us 89us 10us 73us # Rows sent 0 1.83k 25 25 25 25 0 25 # Rows examine 0 303.51k 1.35k 12.39k 4.05k 8.46k 2.78k 2.76k # Query size 0 21.53k 292 296 293.91 284.79 0 284.79 # String: # Databases isuumo # Hosts ip-172-31-36-237.ap-northeast-1.compute.inte... # Users isucon # Query_time distribution # 1us # 10us # 100us # 1ms ############################ # 10ms ################################################################ # 100ms ######################################## # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `isuumo` LIKE 'estate'G # SHOW CREATE TABLE `isuumo`.`estate`G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, name, description, thumbnail, address, latitude, longitude, rent, door_height, door_width ```

[youdofoo]

score: 8949,

29893d505006ccc5e44a66cab1e72ef075fe1eac

alp

``` sudo alp ltsv --file /var/log/nginx/access.log -m "/api/recommended_estate/\d+,/api/chair/buy/\d+,/api/chair/\d+,/api/estate/\d+,/api/estate/req_doc/\d+,/images/chair/.+\.png,/images/estate/.+\.png" -o count,2xx,3xx,4xx,5xx,method,uri,min,max,sum,avg,p99 --sort sum --reverse | tee /tmp/alp-1800.txt +-------+-----+-----+-----+-----+--------+-----+-----+-----+-----+-----+-----+ | COUNT | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P99 | +-------+-----+-----+-----+-----+--------+-----+-----+-----+-----+-----+-----+ +-------+-----+-----+-----+-----+--------+-----+-----+-----+-----+-----+-----+ ```

pt-query-digest

``` sudo pt-query-digest /var/log/mysql/mysql-slow.log | tee /tmp/pt-1800.txt # No events processed. ```

[youdofoo]

最終スコア

{"pass":true,"score":9235,"messages":[],"reason":"OK","language":"go"}