search

kirikiriyamama / isucon9-qualify

0 stars 0 forks source link

スコアログ #1

Open unasuke opened 3 years ago

unasuke commented 3 years ago

初期スコア

エラー省略

 2021/07/31 02:12:23 main.go:104: === initialize ===
 2021/07/31 02:12:29 main.go:123: === verify ===
 2021/07/31 02:12:32 main.go:146: === validation ===
 2021/07/31 02:12:32 scenario.go:54: - Start Load worker 1
 2021/07/31 02:12:33 scenario.go:62: - Start Load worker 2
 2021/07/31 02:13:33 main.go:180: === final check ===
 2021/07/31 02:13:33 main.go:212: 2110 0
 {"pass":true,"score":2110,"campaign":0,"language":"ruby","messages":[]}
isucon@ip-172-31-49-70:~/isucari$
kirikiriyamama commented 3 years ago 
isucon@ip-172-31-253-109:~$ sudo alp ltsv --file=/var/log/nginx/access.log --matching-groups='/users/\d+\.json, /items/\d+\.json, /upload/[0-9a-f]+\.jpg, /transactions/\d+\.png, /new_items/\d+\.json' --reverse --sort sum
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+------------+------------+-------------+------------+
| COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD |                 URI                 |  MIN  |  MAX  |   SUM    |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)  | AVG(BODY)  |
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+------------+------------+-------------+------------+
|   608 |   0 |   8 |   0 | 600 |   0 | POST   | /buy                                | 0.004 | 3.792 | 1255.763 | 2.065 | 3.072 | 3.116 | 3.220 |  1.027 |      0.000 |     48.000 |   14369.000 |     23.633 |
|   715 |   0 | 685 |   0 |  30 |   0 | POST   | /login                              | 0.019 | 3.732 |  817.108 | 1.143 | 1.944 | 2.856 | 3.360 |  0.711 |      0.000 |    271.000 |  177283.000 |    247.948 |
|   685 |   0 | 657 |   0 |  28 |   0 | GET    | /settings                           | 0.004 | 3.752 |  734.846 | 1.073 | 1.784 | 2.596 | 3.416 |  0.684 |      0.000 |   3109.000 | 2034042.000 |   2969.404 |
|   224 |   0 | 214 |   0 |  10 |   0 | GET    | /new_items/\d+\.json                | 0.016 | 5.185 |  478.213 | 2.135 | 3.660 | 4.084 | 4.716 |  1.078 |      0.000 |  24022.000 | 5035730.000 |  22480.938 |
|   467 |   0 | 460 |   0 |   7 |   0 | GET    | /items/\d+\.json                    | 0.004 | 3.556 |  362.717 | 0.777 | 1.632 | 2.280 | 3.096 |  0.716 |      0.000 |   3885.000 | 1014710.000 |   2172.827 |
|    79 |   0 |  76 |   0 |   3 |   0 | GET    | /new_items.json                     | 0.236 | 5.305 |  176.922 | 2.240 | 3.916 | 4.473 | 5.305 |  1.021 |      0.000 |  23931.000 | 1778195.000 |  22508.797 |
|    85 |   0 |  85 |   0 |   0 |   0 | GET    | /users/\d+\.json                    | 0.028 | 5.105 |  131.051 | 1.542 | 3.341 | 3.888 | 5.105 |  1.209 |     97.000 |  23882.000 | 1544496.000 |  18170.541 |
|    29 |   0 |  28 |   0 |   1 |   0 | GET    | /users/transactions.json            | 0.032 | 8.293 |   41.287 | 1.424 | 5.936 | 7.888 | 8.293 |  2.557 |      0.000 |  24879.000 |  577804.000 |  19924.276 |
|    30 |   0 |  27 |   0 |   3 |   0 | POST   | /sell                               | 0.004 | 2.308 |   23.704 | 0.790 | 1.612 | 2.232 | 2.308 |  0.627 |     12.000 |    105.000 |     562.000 |     18.733 |
|    11 |   0 |   8 |   0 |   3 |   0 | POST   | /ship_done                          | 0.004 | 2.352 |   10.100 | 0.918 | 2.256 | 2.352 | 2.352 |  1.023 |     28.000 |     82.000 |     410.000 |     37.273 |
|    10 |   0 |   8 |   0 |   2 |   0 | POST   | /ship                               | 0.012 | 2.737 |    9.889 | 0.989 | 2.396 | 2.737 | 2.737 |  1.045 |     28.000 |     60.000 |     544.000 |     54.400 |
|     7 |   0 |   7 |   0 |   0 |   0 | POST   | /complete                           | 0.004 | 2.704 |    8.448 | 1.207 | 2.704 | 2.704 | 2.704 |  1.055 |     33.000 |     33.000 |     231.000 |     33.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | POST   | /initialize                         | 5.652 | 5.652 |    5.652 | 5.652 | 5.652 | 5.652 | 5.652 |  0.000 |     32.000 |     32.000 |      32.000 |     32.000 |
|    10 |   0 |   8 |   0 |   2 |   0 | GET    | /transactions/\d+\.png              | 0.004 | 1.620 |    5.208 | 0.521 | 1.596 | 1.620 | 1.620 |  0.637 |     32.000 |    725.000 |    5787.000 |    578.700 |
|     5 |   0 |   5 |   0 |   0 |   0 | POST   | /bump                               | 0.016 | 1.345 |    2.809 | 0.562 | 1.345 | 1.345 | 1.345 |  0.477 |     90.000 |     91.000 |     453.000 |     90.600 |
|    52 |   0 |  52 |   0 |   0 |   0 | GET    | /upload/[0-9a-f]+\.jpg              | 0.004 | 0.048 |    1.036 | 0.020 | 0.036 | 0.044 | 0.048 |  0.011 |  51716.000 | 148635.000 | 4153902.000 |  79882.731 |
|     3 |   0 |   2 |   0 |   1 |   0 | POST   | /items/edit                         | 0.004 | 0.608 |    0.620 | 0.207 | 0.608 | 0.608 | 0.608 |  0.284 |     57.000 |     92.000 |     238.000 |     79.333 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /static/js/2.ff6e1067.chunk.js      | 0.532 | 0.532 |    0.532 | 0.532 | 0.532 | 0.532 | 0.532 |  0.000 | 508459.000 | 508459.000 |  508459.000 | 508459.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /static/css/main.19393e92.chunk.css | 0.204 | 0.204 |    0.204 | 0.204 | 0.204 | 0.204 | 0.204 |  0.000 |    994.000 |    994.000 |     994.000 |    994.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /static/js/main.babc3d4d.chunk.js   | 0.020 | 0.020 |    0.020 | 0.020 | 0.020 | 0.020 | 0.020 |  0.000 |  90365.000 |  90365.000 |   90365.000 |  90365.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /static/js/runtime~main.a8a9905a.js | 0.016 | 0.016 |    0.016 | 0.016 | 0.016 | 0.016 | 0.016 |  0.000 |   1502.000 |   1502.000 |    1502.000 |   1502.000 |
|     1 |   0 |   1 |   0 |   0 |   0 | GET    | /reports.json                       | 0.000 | 0.000 |    0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |  27771.000 |  27771.000 |   27771.000 |  27771.000 |
+-------+-----+-----+-----+-----+-----+--------+-------------------------------------+-------+-------+----------+-------+-------+-------+-------+--------+------------+------------+-------------+------------+
kirikiriyamama commented 3 years ago 
isucon@ip-172-31-253-109:~/isucon9-qualify$ sudo pt-query-digest /var/log/mysql/slow.log

# 310ms user time, 0 system time, 37.40M rss, 101.47M vsz
# Current date: Sat Jul 31 04:29:46 2021
# Hostname: ip-172-31-253-109
# Files: /var/log/mysql/slow.log
# Overall: 1.50k total, 9 unique, 23.76 QPS, 1.31x concurrency ___________
# Time range: 2021-07-31T04:25:54 to 2021-07-31T04:26:57
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            83s    71us   621ms    55ms   293ms   102ms   224us
# Lock time           96ms    13us     6ms    63us    98us   170us    35us
# Rows sent         26.05k       0      49   17.82   46.83   18.62    5.75
# Rows examine      29.68M      43  48.91k  20.30k  46.68k  22.98k   42.48
# Query size       165.59k      26     295  113.27  258.32   81.39   49.17

# Profile
# Rank Query ID                            Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
#    1 0x534F6185E0A0C71693761CC3349B416F  23.6502 28.6%   117 0.2021  0.11 SELECT items
#    2 0x5AF10ED6AD345D4B930FF1E60F9B9ED6  22.3992 27.1%   140 0.1600  0.11 SELECT items
#    3 0x6D959E4C28C709C1312243B842F41381  20.3381 24.6%   166 0.1225  0.10 SELECT items
#    4 0x6688844580F541EC2C1B6BE83F13FC2B   9.1273 11.0%   111 0.0822  0.06 SELECT items
#    5 0x61B4A126A90B2DEB4C0C6A2C3174E3A8   3.1607  3.8%    54 0.0585  0.02 SELECT items
# MISC 0xMISC                               4.0371  4.9%   909 0.0044   0.0 <4 ITEMS>

# Query 1: 2.05 QPS, 0.41x concurrency, ID 0x534F6185E0A0C71693761CC3349B416F at byte 38921
# Scores: V/M = 0.11
# Time range: 2021-07-31T04:25:55 to 2021-07-31T04:26:52
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7     117
# Exec time     28     24s    33ms   621ms   202ms   477ms   147ms   155ms
# Lock time     13    13ms    62us     1ms   112us   214us   113us    84us
# Rows sent     21   5.60k      49      49      49      49       0      49
# Rows examine  18   5.59M  48.88k  48.91k  48.90k  46.68k       0  46.68k
# Query size    14  24.68k     216     216     216     216       0     216
# String:
# Databases    isucari
# Hosts        localhost
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ###########################
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `status` IN ('on_sale', 'sold_out') AND (`created_at` < '2019-08-12 15:52:54'  OR (`created_at` <= '2019-08-12 15:52:54' AND `id` < '49967')) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

# Query 2: 3.68 QPS, 0.59x concurrency, ID 0x5AF10ED6AD345D4B930FF1E60F9B9ED6 at byte 38201
# Scores: V/M = 0.11
# Time range: 2021-07-31T04:25:55 to 2021-07-31T04:26:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9     140
# Exec time     27     22s    29ms   596ms   160ms   412ms   134ms    87ms
# Lock time     14    14ms    64us   896us   100us   185us    83us    84us
# Rows sent     25   6.70k      49      49      49      49       0      49
# Rows examine  22   6.68M  48.88k  48.90k  48.89k  46.68k       0  46.68k
# Query size    21  35.60k     257     267  260.36  258.32       5  246.02
# String:
# Databases    isucari
# Hosts        localhost
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #########################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `status` IN ('on_sale', 'sold_out') AND category_id IN ('21','22','23','24') AND (`created_at` < '2019-08-12 15:44:54'  OR (`created_at` <= '2019-08-12 15:44:54' AND `id` < '49487')) ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

# Query 3: 2.63 QPS, 0.32x concurrency, ID 0x6D959E4C28C709C1312243B842F41381 at byte 45333
# Scores: V/M = 0.10
# Time range: 2021-07-31T04:25:54 to 2021-07-31T04:26:57
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11     166
# Exec time     24     20s    19ms   428ms   123ms   356ms   112ms    71ms
# Lock time     18    18ms    45us     6ms   107us   103us   453us    63us
# Rows sent     16   4.33k       0      49   26.70   46.83   22.63   46.83
# Rows examine  26   7.92M  48.83k  48.91k  48.87k  46.68k       0  46.68k
# Query size    14  23.61k     144     146  145.66  143.84       0  143.84
# String:
# Databases    isucari
# Hosts        localhost
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ######################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `seller_id` = '3657' AND `status` IN ('on_sale', 'trading', 'sold_out') ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

# Query 4: 1.82 QPS, 0.15x concurrency, ID 0x6688844580F541EC2C1B6BE83F13FC2B at byte 36049
# Scores: V/M = 0.06
# Time range: 2021-07-31T04:25:55 to 2021-07-31T04:26:56
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7     111
# Exec time     11      9s    22ms   527ms    82ms   180ms    69ms    59ms
# Lock time      8     8ms    51us   193us    73us    84us    20us    69us
# Rows sent      3     997       4      11    8.98   10.84    2.31   10.84
# Rows examine  17   5.30M  48.84k  48.87k  48.86k  46.68k       0  46.68k
# Query size    12  20.41k     185     189  188.30  183.58       0  183.58
# String:
# Databases    isucari
# Hosts        localhost
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ########################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE (`seller_id` = '197' OR `buyer_id` = '197') AND `status` IN ('on_sale', 'trading', 'sold_out', 'cancel', 'stop') ORDER BY `created_at` DESC, `id` DESC LIMIT 11\G

# Query 5: 0.89 QPS, 0.05x concurrency, ID 0x61B4A126A90B2DEB4C0C6A2C3174E3A8 at byte 8984
# Scores: V/M = 0.02
# Time range: 2021-07-31T04:25:55 to 2021-07-31T04:26:56
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3      54
# Exec time      3      3s    22ms   139ms    59ms   110ms    31ms    53ms
# Lock time      5     5ms    74us   239us    93us   113us    26us    89us
# Rows sent      2     594      11      11      11      11       0      11
# Rows examine   8   2.58M  48.84k  48.87k  48.86k  46.68k       0  46.68k
# Query size     9  15.53k     293     295  294.56  284.79       0  284.79
# String:
# Databases    isucari
# Hosts        localhost
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ############
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE (`seller_id` = '3159' OR `buyer_id` = '3159') AND `status` IN ('on_sale', 'trading', 'sold_out', 'cancel', 'stop') AND (`created_at` < '2019-08-12 13:31:57'  OR (`created_at` <= '2019-08-12 13:31:57' AND `id` < '41514')) ORDER BY `created_at` DESC, `id` DESC LIMIT 11\G
unasuke commented 3 years ago

8 merge 後

2021/07/31 05:47:13 main.go:212: 2520 0
{"pass":true,"score":2520,"campaign":0,"language":"ruby","messages":[]}
sudo alp ltsv --file=/var/log/nginx/access.log --matching-groups='/users/\d+\.json, /items/\d+\.json, /upload/[0-9a-f]+\.jpg, /transactions/\d+\.png, /new_items/\d+\.json' --reverse --sort sum
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                 URI                 |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
|   171 |   0 |  160 |   0 |  11 |   0 | GET    | /users/transactions.json            | 0.024 | 7.116 | 480.153 | 2.808 | 5.040 | 5.681 | 6.172 |  1.735 |      0.000 |  26801.000 |  3333590.000 |  19494.678 |
|   957 |   0 |  955 |   0 |   2 |   0 | GET    | /new_items/\d+\.json                | 0.002 | 1.720 | 108.715 | 0.114 | 0.260 | 0.312 | 0.412 |  0.107 |      0.000 |  23995.000 | 22394404.000 |  23400.631 |
|   275 |   0 |  275 |   0 |   0 |   0 | GET    | /users/\d+\.json                    | 0.028 | 1.368 |  89.977 | 0.327 | 0.840 | 1.000 | 1.232 |  0.295 |     96.000 |  23901.000 |  3893080.000 |  14156.655 |
|    56 |   0 |   32 |   0 |  24 |   0 | POST   | /buy                                | 0.000 | 1.652 |  55.097 | 0.984 | 1.624 | 1.636 | 1.652 |  0.786 |     28.000 |     48.000 |     1974.000 |     35.250 |
|    41 |   0 |   25 |   0 |  16 |   0 | POST   | /ship_done                          | 0.271 | 0.817 |  21.456 | 0.523 | 0.816 | 0.816 | 0.817 |  0.380 |      0.000 |     82.000 |     1555.000 |     37.927 |
|    41 |   0 |   29 |   0 |  12 |   0 | POST   | /ship                               | 0.004 | 0.848 |  21.304 | 0.520 | 0.820 | 0.824 | 0.848 |  0.389 |     28.000 |     60.000 |     2124.000 |     51.805 |
|  2742 |   0 | 2742 |   0 |   0 |   0 | GET    | /items/\d+\.json                    | 0.000 | 0.140 |  21.284 | 0.008 | 0.016 | 0.028 | 0.060 |  0.011 |   1842.000 |   3976.000 |  5956418.000 |   2172.290 |
|   134 |   0 |  134 |   0 |   0 |   0 | GET    | /new_items.json                     | 0.024 | 0.448 |  20.632 | 0.154 | 0.300 | 0.364 | 0.428 |  0.111 |  23002.000 |  23663.000 |  3131802.000 |  23371.657 |
|    25 |   0 |   25 |   0 |   0 |   0 | POST   | /complete                           | 0.008 | 0.868 |  17.945 | 0.718 | 0.813 | 0.824 | 0.868 |  0.258 |     33.000 |     33.000 |      825.000 |     33.000 |
|    61 |   0 |   53 |   0 |   8 |   0 | POST   | /login                              | 0.060 | 0.736 |  13.452 | 0.221 | 0.416 | 0.544 | 0.736 |  0.166 |     72.000 |    267.000 |    14304.000 |    234.492 |
|    53 |   0 |   53 |   0 |   0 |   0 | GET    | /settings                           | 0.000 | 0.548 |   8.300 | 0.157 | 0.404 | 0.476 | 0.548 |  0.157 |   3090.000 |   3105.000 |   164142.000 |   3097.019 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                         | 5.736 | 5.736 |   5.736 | 5.736 | 5.736 | 5.736 | 5.736 |  0.000 |     32.000 |     32.000 |       32.000 |     32.000 |
|    56 |   0 |   38 |   0 |  18 |   0 | POST   | /sell                               | 0.004 | 0.772 |   4.940 | 0.088 | 0.372 | 0.640 | 0.772 |  0.191 |     12.000 |    105.000 |     1884.000 |     33.643 |
|    56 |   0 |   56 |   0 |   0 |   0 | GET    | /upload/[0-9a-f]+\.jpg              | 0.004 | 0.080 |   1.140 | 0.020 | 0.040 | 0.064 | 0.080 |  0.016 |  51522.000 | 140911.000 |  4479482.000 |  79990.750 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/2.ff6e1067.chunk.js      | 0.452 | 0.452 |   0.452 | 0.452 | 0.452 | 0.452 | 0.452 |  0.000 | 508459.000 | 508459.000 |   508459.000 | 508459.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/main.babc3d4d.chunk.js   | 0.216 | 0.216 |   0.216 | 0.216 | 0.216 | 0.216 | 0.216 |  0.000 |  90365.000 |  90365.000 |    90365.000 |  90365.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/css/main.19393e92.chunk.css | 0.192 | 0.192 |   0.192 | 0.192 | 0.192 | 0.192 | 0.192 |  0.000 |    994.000 |    994.000 |      994.000 |    994.000 |
|    13 |   0 |   13 |   0 |   0 |   0 | POST   | /bump                               | 0.004 | 0.028 |   0.172 | 0.013 | 0.024 | 0.028 | 0.028 |  0.008 |     89.000 |     91.000 |     1168.000 |     89.846 |
|    38 |   0 |   26 |   0 |  12 |   0 | GET    | /transactions/\d+\.png              | 0.004 | 0.024 |   0.156 | 0.004 | 0.008 | 0.012 | 0.024 |  0.005 |     32.000 |    725.000 |    19010.000 |    500.263 |
|     9 |   0 |    3 |   0 |   6 |   0 | POST   | /items/edit                         | 0.000 | 0.024 |   0.088 | 0.010 | 0.024 | 0.024 | 0.024 |  0.009 |     57.000 |     92.000 |      615.000 |     68.333 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/runtime~main.a8a9905a.js | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 |   1502.000 |   1502.000 |     1502.000 |   1502.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /reports.json                       | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 | 111260.000 | 111260.000 |   111260.000 | 111260.000 |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
unasuke commented 3 years ago

10 merge後

2021/07/31 06:02:34 main.go:212: 2410 0
{"pass":true,"score":2410,"campaign":0,"language":"ruby","messages":[]}
 sudo alp ltsv --file=/var/log/nginx/access.log --matching-groups='/users/\d+\.json, /items/\d+\.json, /upload/[0-9a-f]+\.jpg, /transactions/\d+\.png, /new_items/\d+\.json' --reverse --sort sum
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                 URI                 |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
|   161 |   0 |  152 |   0 |   9 |   0 | GET    | /users/transactions.json            | 0.016 | 7.400 | 481.946 | 2.993 | 5.017 | 5.660 | 6.528 |  1.724 |      0.000 |  25215.000 |  3113923.000 |  19341.137 |
|  1055 |   0 | 1055 |   0 |   0 |   0 | GET    | /new_items/\d+\.json                | 0.020 | 1.716 | 101.340 | 0.096 | 0.232 | 0.276 | 0.336 |  0.093 |  22616.000 |  24065.000 | 24732813.000 |  23443.425 |
|   276 |   0 |  276 |   0 |   0 |   0 | GET    | /users/\d+\.json                    | 0.020 | 1.176 |  93.184 | 0.338 | 0.796 | 0.888 | 1.088 |  0.283 |     95.000 |  23993.000 |  3905955.000 |  14152.011 |
|    58 |   0 |   32 |   0 |  26 |   0 | POST   | /buy                                | 0.857 | 1.676 |  57.006 | 0.983 | 1.624 | 1.628 | 1.676 |  0.772 |      0.000 |     48.000 |     1974.000 |     34.034 |
|    44 |   0 |   25 |   0 |  19 |   0 | POST   | /ship_done                          | 0.150 | 0.820 |  22.162 | 0.504 | 0.812 | 0.816 | 0.820 |  0.386 |      0.000 |     82.000 |     1701.000 |     38.659 |
|    41 |   0 |   28 |   0 |  13 |   0 | POST   | /ship                               | 0.004 | 0.832 |  20.559 | 0.501 | 0.816 | 0.824 | 0.832 |  0.392 |      0.000 |     60.000 |     2064.000 |     50.341 |
|   134 |   0 |  134 |   0 |   0 |   0 | GET    | /new_items.json                     | 0.020 | 0.356 |  18.804 | 0.140 | 0.268 | 0.296 | 0.352 |  0.091 |  23003.000 |  23746.000 |  3132633.000 |  23377.858 |
|  2955 |   0 | 2955 |   0 |   0 |   0 | GET    | /items/\d+\.json                    | 0.000 | 0.476 |  18.680 | 0.006 | 0.012 | 0.020 | 0.040 |  0.012 |   1842.000 |   3984.000 |  6410963.000 |   2169.531 |
|    25 |   0 |   24 |   0 |   1 |   0 | POST   | /complete                           | 0.008 | 0.872 |  17.689 | 0.708 | 0.816 | 0.836 | 0.872 |  0.259 |      0.000 |     33.000 |      792.000 |     31.680 |
|    61 |   0 |   53 |   0 |   8 |   0 | POST   | /login                              | 0.060 | 0.604 |  13.292 | 0.218 | 0.516 | 0.568 | 0.604 |  0.175 |     72.000 |    265.000 |    14312.000 |    234.623 |
|    53 |   0 |   53 |   0 |   0 |   0 | GET    | /settings                           | 0.004 | 0.584 |   8.196 | 0.155 | 0.400 | 0.452 | 0.584 |  0.160 |   3092.000 |   3103.000 |   164150.000 |   3097.170 |
|    56 |   0 |   38 |   0 |  18 |   0 | POST   | /sell                               | 0.004 | 1.280 |   5.976 | 0.107 | 0.256 | 0.912 | 1.280 |  0.266 |     12.000 |    105.000 |     1884.000 |     33.643 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                         | 5.792 | 5.792 |   5.792 | 5.792 | 5.792 | 5.792 | 5.792 |  0.000 |     32.000 |     32.000 |       32.000 |     32.000 |
|    57 |   0 |   57 |   0 |   0 |   0 | GET    | /upload/[0-9a-f]+\.jpg              | 0.004 | 0.136 |   1.444 | 0.025 | 0.044 | 0.100 | 0.136 |  0.026 |  53262.000 | 141542.000 |  4927931.000 |  86454.930 |
|    13 |   0 |   13 |   0 |   0 |   0 | POST   | /bump                               | 0.004 | 0.224 |   0.384 | 0.030 | 0.044 | 0.224 | 0.224 |  0.057 |     89.000 |     91.000 |     1169.000 |     89.923 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/2.ff6e1067.chunk.js      | 0.336 | 0.336 |   0.336 | 0.336 | 0.336 | 0.336 | 0.336 |  0.000 | 508459.000 | 508459.000 |   508459.000 | 508459.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/main.babc3d4d.chunk.js   | 0.204 | 0.204 |   0.204 | 0.204 | 0.204 | 0.204 | 0.204 |  0.000 |  90365.000 |  90365.000 |    90365.000 |  90365.000 |
|    38 |   0 |   26 |   0 |  12 |   0 | GET    | /transactions/\d+\.png              | 0.000 | 0.024 |   0.148 | 0.004 | 0.008 | 0.020 | 0.024 |  0.005 |     32.000 |    722.000 |    18997.000 |    499.921 |
|     9 |   0 |    3 |   0 |   6 |   0 | POST   | /items/edit                         | 0.000 | 0.020 |   0.068 | 0.008 | 0.020 | 0.020 | 0.020 |  0.007 |     57.000 |     92.000 |      616.000 |     68.444 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/runtime~main.a8a9905a.js | 0.008 | 0.008 |   0.008 | 0.008 | 0.008 | 0.008 | 0.008 |  0.000 |   1502.000 |   1502.000 |     1502.000 |   1502.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/css/main.19393e92.chunk.css | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 |    994.000 |    994.000 |      994.000 |    994.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /reports.json                       | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 | 119977.000 | 119977.000 |   119977.000 | 119977.000 |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
kirikiriyamama commented 3 years ago

15

2021/07/31 06:53:19 main.go:212: 2710 0
{"pass":true,"score":2710,"campaign":0,"language":"ruby","messages":[]}
lime1024 commented 3 years ago

14

2021/07/31 07:30:34 main.go:212: 3520 0
{"pass":true,"score":3520,"campaign":0,"language":"ruby","messages":[]}
sudo alp ltsv --file=/var/log/nginx/access.log --matching-groups='/users/\d+\.json, /items/\d+\.json, /upload/[0-9a-f]+\.jpg, /transactions/\d+\.png, /new_items/\d+\.json' --reverse --sort sum
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                 URI                 |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
|   194 |   0 |  183 |   0 |  11 |   0 | GET    | /users/transactions.json            | 0.016 | 7.336 | 538.549 | 2.776 | 4.828 | 4.940 | 7.324 |  1.682 |      0.000 |  28728.000 |  3849680.000 |  19843.711 |
|    70 |   0 |   41 |   0 |  29 |   0 | POST   | /buy                                | 0.077 | 1.668 |  71.273 | 1.018 | 1.616 | 1.644 | 1.668 |  0.776 |      0.000 |     48.000 |     2424.000 |     34.629 |
|   309 |   0 |  309 |   0 |   0 |   0 | GET    | /users/\d+\.json                    | 0.016 | 0.632 |  49.124 | 0.159 | 0.368 | 0.476 | 0.584 |  0.141 |     97.000 |  23946.000 |  4593481.000 |  14865.634 |
|  1587 |   0 | 1586 |   0 |   1 |   0 | GET    | /new_items/\d+\.json                | 0.006 | 1.572 |  46.230 | 0.029 | 0.072 | 0.092 | 0.124 |  0.052 |      0.000 |  24044.000 | 37197402.000 |  23438.817 |
|    57 |   0 |   38 |   0 |  19 |   0 | POST   | /ship_done                          | 0.004 | 0.820 |  33.229 | 0.583 | 0.812 | 0.812 | 0.820 |  0.360 |      0.000 |     82.000 |     2130.000 |     37.368 |
|    55 |   0 |   41 |   0 |  14 |   0 | POST   | /ship                               | 0.004 | 0.852 |  31.024 | 0.564 | 0.812 | 0.832 | 0.852 |  0.373 |     28.000 |     60.000 |     2908.000 |     52.873 |
|    35 |   0 |   35 |   0 |   0 |   0 | POST   | /complete                           | 0.008 | 0.820 |  25.888 | 0.740 | 0.808 | 0.812 | 0.820 |  0.221 |     33.000 |     33.000 |     1155.000 |     33.000 |
|  4064 |   0 | 4064 |   0 |   0 |   0 | GET    | /items/\d+\.json                    | 0.000 | 0.328 |  15.072 | 0.004 | 0.008 | 0.012 | 0.020 |  0.007 |   1842.000 |   4241.000 |  8794472.000 |   2163.994 |
|    62 |   0 |   54 |   0 |   8 |   0 | POST   | /login                              | 0.060 | 0.708 |  12.228 | 0.197 | 0.448 | 0.584 | 0.708 |  0.166 |     72.000 |    264.000 |    14529.000 |    234.339 |
|    54 |   0 |   54 |   0 |   0 |   0 | GET    | /settings                           | 0.000 | 0.704 |   8.060 | 0.149 | 0.376 | 0.516 | 0.704 |  0.169 |   3091.000 |   3102.000 |   167205.000 |   3096.389 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | /initialize                         | 7.524 | 7.524 |   7.524 | 7.524 | 7.524 | 7.524 | 7.524 |  0.000 |     32.000 |     32.000 |       32.000 |     32.000 |
|   142 |   0 |  142 |   0 |   0 |   0 | GET    | /new_items.json                     | 0.008 | 0.604 |   7.388 | 0.052 | 0.108 | 0.120 | 0.176 |  0.061 |  22988.000 |  23907.000 |  3320417.000 |  23383.218 |
|    69 |   0 |   48 |   0 |  21 |   0 | POST   | /sell                               | 0.004 | 1.044 |   5.028 | 0.073 | 0.256 | 0.688 | 1.044 |  0.208 |     12.000 |    105.000 |     2242.000 |     32.493 |
|    58 |   0 |   58 |   0 |   0 |   0 | GET    | /upload/[0-9a-f]+\.jpg              | 0.004 | 0.032 |   0.616 | 0.011 | 0.020 | 0.028 | 0.032 |  0.007 |  51322.000 | 150881.000 |  4804494.000 |  82836.103 |
|    13 |   0 |   13 |   0 |   0 |   0 | POST   | /bump                               | 0.004 | 0.456 |   0.572 | 0.044 | 0.020 | 0.456 | 0.456 |  0.119 |     89.000 |     90.000 |     1168.000 |     89.846 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/2.ff6e1067.chunk.js      | 0.516 | 0.516 |   0.516 | 0.516 | 0.516 | 0.516 | 0.516 |  0.000 | 508459.000 | 508459.000 |   508459.000 | 508459.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/css/main.19393e92.chunk.css | 0.188 | 0.188 |   0.188 | 0.188 | 0.188 | 0.188 | 0.188 |  0.000 |    994.000 |    994.000 |      994.000 |    994.000 |
|    53 |   0 |   39 |   0 |  14 |   0 | GET    | /transactions/\d+\.png              | 0.000 | 0.016 |   0.148 | 0.003 | 0.004 | 0.008 | 0.016 |  0.003 |     32.000 |    722.000 |    28327.000 |    534.472 |
|    10 |   0 |    3 |   0 |   7 |   0 | POST   | /items/edit                         | 0.000 | 0.012 |   0.048 | 0.005 | 0.012 | 0.012 | 0.012 |  0.005 |     57.000 |     92.000 |      673.000 |     67.300 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/runtime~main.a8a9905a.js | 0.012 | 0.012 |   0.012 | 0.012 | 0.012 | 0.012 | 0.012 |  0.000 |   1502.000 |   1502.000 |     1502.000 |   1502.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /static/js/main.babc3d4d.chunk.js   | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 |  90365.000 |  90365.000 |    90365.000 |  90365.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | GET    | /reports.json                       | 0.004 | 0.004 |   0.004 | 0.004 | 0.004 | 0.004 | 0.004 |  0.000 | 144131.000 | 144131.000 |   144131.000 | 144131.000 |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+
kirikiriyamama commented 3 years ago 
isucon@ip-172-31-194-167:~$ sudo pt-query-digest /var/log/mysql/slow.log

# 150ms user time, 20ms system time, 37.20M rss, 101.07M vsz
# Current date: Sat Jul 31 08:31:37 2021
# Hostname: ip-172-31-194-167
# Files: /var/log/mysql/slow.log
# Overall: 16 total, 2 unique, 8 QPS, 4.65x concurrency __________________
# Time range: 2021-07-31T08:30:30 to 2021-07-31T08:30:32
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time             9s   503ms   638ms   582ms   609ms    36ms   580ms
# Lock time            1ms    48us    90us    65us    84us    12us    60us
# Rows sent             37       0       7    2.31    5.75    2.06    1.96
# Rows examine     781.50k  48.84k  48.84k  48.84k  48.84k       0  48.84k
# Query size         2.40k     145     189  153.81  183.58   15.51  143.84

# Profile
# Rank Query ID                            Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
#    1 0x6D959E4C28C709C1312243B842F41381   7.5197 80.8%    13 0.5784  0.00 SELECT items
#    2 0x6688844580F541EC2C1B6BE83F13FC2B   1.7884 19.2%     3 0.5961  0.00 SELECT items

# Query 1: 6.50 QPS, 3.76x concurrency, ID 0x6D959E4C28C709C1312243B842F41381 at byte 1389
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2021-07-31T08:30:30 to 2021-07-31T08:30:32
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         81      13
# Exec time     80      8s   503ms   630ms   578ms   609ms    34ms   580ms
# Lock time     76   804us    48us    84us    61us    69us     9us    60us
# Rows sent     48      18       0       3    1.38    2.90    0.89    0.99
# Rows examine  81 634.97k  48.84k  48.84k  48.84k  48.84k       0  48.84k
# Query size    77   1.85k     145     146  145.85  143.84       0  143.84
# String:
# Databases    isucari
# Hosts        ip-172-31-253-109.ap-northeast-1.compute.int...
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `seller_id` = '1316' AND `status` IN ('on_sale', 'trading', 'sold_out') ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

# Query 2: 0 QPS, 0x concurrency, ID 0x6688844580F541EC2C1B6BE83F13FC2B at byte 3383
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-07-31T08:30:32
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         18       3
# Exec time     19      2s   530ms   638ms   596ms   609ms    39ms   609ms
# Lock time     23   247us    69us    90us    82us    89us     9us    84us
# Rows sent     51      19       6       7    6.33    6.98    0.50    5.75
# Rows examine  18 146.53k  48.84k  48.84k  48.84k  48.84k       0  48.84k
# Query size    22     565     187     189  188.33  183.58       0  183.58
# String:
# Databases    isucari
# Hosts        ip-172-31-253-109.ap-northeast-1.compute.int...
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE (`seller_id` = '2270' OR `buyer_id` = '2270') AND `status` IN ('on_sale', 'trading', 'sold_out', 'cancel', 'stop') ORDER BY `created_at` DESC, `id` DESC LIMIT 11\G
lime1024 commented 3 years ago

17

2021/07/31 08:36:44 main.go:212: 3210 0
{"pass":true,"score":3210,"campaign":0,"language":"ruby","messages":[]}

あとで revert するかも

lime1024 commented 3 years ago 
isucon@ip-172-31-194-167:~/isucon9-qualify$ sudo pt-query-digest /var/log/mysql/slow.log

# 160ms user time, 10ms system time, 37.18M rss, 101.09M vsz
# Current date: Sat Jul 31 09:08:05 2021
# Hostname: ip-172-31-194-167
# Files: /var/log/mysql/slow.log
# Overall: 31 total, 2 unique, 0.21 QPS, 0.12x concurrency _______________
# Time range: 2021-07-31T09:00:11 to 2021-07-31T09:02:36
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            18s   510ms   665ms   574ms   640ms    48ms   552ms
# Lock time            3ms    46us   227us    81us   176us    46us    60us
# Rows sent             70       0       8    2.26    5.75    2.00    1.96
# Rows examine       1.48M  48.84k  48.88k  48.85k  46.68k       0  46.68k
# Query size        10.76k     144   1.41k  355.58   1.39k  471.16  143.84

# Profile
# Rank Query ID                            Response time Calls R/Call V/M 
# ==== =================================== ============= ===== ====== ====
#    1 0x6D959E4C28C709C1312243B842F41381  14.9198 83.8%    26 0.5738  0.00 SELECT items
#    2 0x4442259452E8F07B052A2311EA959F65   2.8860 16.2%     5 0.5772  0.00 SELECT items users transaction_evidences shippings

# Query 1: 0.18 QPS, 0.10x concurrency, ID 0x6D959E4C28C709C1312243B842F41381 at byte 2187
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2021-07-31T09:00:11 to 2021-07-31T09:02:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         83      26
# Exec time     83     15s   510ms   665ms   574ms   640ms    51ms   552ms
# Lock time     64     2ms    46us    95us    62us    76us    11us    60us
# Rows sent     54      38       0       4    1.46    2.90    0.94    0.99
# Rows examine  83   1.24M  48.84k  48.84k  48.84k  48.84k       0  48.84k
# Query size    34   3.71k     144     146  145.92  143.84       0  143.84
# String:
# Databases    isucari
# Hosts        ip-172-31-253-109.ap-northeast-1.compute.int...
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `seller_id` = '2513' AND `status` IN ('on_sale', 'trading', 'sold_out') ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G

# Query 2: 0.03 QPS, 0.02x concurrency, ID 0x4442259452E8F07B052A2311EA959F65 at byte 11568
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2021-07-31T09:00:13 to 2021-07-31T09:02:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         16       5
# Exec time     16      3s   527ms   598ms   577ms   580ms    22ms   580ms
# Lock time     35   911us   155us   227us   182us   224us    25us   176us
# Rows sent     45      32       5       8    6.40    7.70    0.98    5.75
# Rows examine  16 244.34k  48.86k  48.88k  48.87k  46.68k       0  46.68k
# Query size    65   7.06k   1.41k   1.41k   1.41k   1.39k       0   1.39k
# String:
# Databases    isucari
# Hosts        ip-172-31-253-109.ap-northeast-1.compute.int...
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
#    SHOW TABLE STATUS FROM `isucari` LIKE 'users'\G
#    SHOW CREATE TABLE `isucari`.`users`\G
#    SHOW TABLE STATUS FROM `isucari` LIKE 'transaction_evidences'\G
#    SHOW CREATE TABLE `isucari`.`transaction_evidences`\G
#    SHOW TABLE STATUS FROM `isucari` LIKE 'shippings'\G
#    SHOW CREATE TABLE `isucari`.`shippings`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
  items.id AS `items_id`,
  items.seller_id AS `items_seller_id`,
  items.buyer_id AS `items_buyer_id`,
  items.status AS `items_status`,
  items.name AS `items_name`,
  items.description AS `items_description`, 
  items.price AS `items_price`,
  items.image_name AS `items_image_name`,
  items.category_id AS `items_category_id`,
  items.created_at AS `items_created_at`,
  items.updated_at AS `items_iupdated_at`, 
  seller.id AS `s_id`,
  seller.account_name AS `s_account_name`,
  seller.address AS `s_address`,
  seller.num_sell_items AS `s_num_sell_items`, 
  buyer.id AS `b_id`,
  buyer.account_name AS `b_account_name`,
  buyer.address AS `b_address`,
  buyer.num_sell_items AS `b_num_sell_items`,
  transaction_evidences.id AS `t_e_id`,
  transaction_evidences.status AS `t_e_status`,
  shippings.status AS `shipping_status`,
  shippings.reserve_id AS `shipping_reserve_id`
FROM
  `items` LEFT JOIN users AS seller ON `seller`.`id` = `items`.`seller_id`
          LEFT JOIN users AS buyer ON `buyer`.`id` = `items`.`buyer_id`
          LEFT JOIN transaction_evidences ON `transaction_evidences`.`item_id` = `items`.`id`
          LEFT JOIN shippings ON `shippings`.`transaction_evidence_id` = `transaction_evidences`.`id`
WHERE
  (`items`.`seller_id` = '3989' OR `items`.`buyer_id` = '3989') AND `items`.`status` IN ('on_sale', 'trading', 'sold_out', 'cancel', 'stop')
ORDER BY `items`.`created_at` DESC, `items`.`id` DESC LIMIT 11\G
lime1024 commented 3 years ago

19

2021/07/31 09:35:45 main.go:212: 3210 0
{"pass":true,"score":3210,"campaign":0,"language":"ruby","messages":[]}
kirikiriyamama commented 3 years ago

https://github.com/kirikiriyamama/isucon9-qualify/pull/20

2021/07/31 09:43:58 main.go:212: 3810 0
{"pass":true,"score":3810,"campaign":0,"language":"ruby","messages":[]}
lime1024 commented 3 years ago 
# Query 1: 0 QPS, 0x concurrency, ID 0x6D959E4C28C709C1312243B842F41381 at byte 0
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-07-31T09:43:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       1
# Exec time    100   501ms   501ms   501ms   501ms   501ms       0   501ms
# Lock time    100    79us    79us    79us    79us    79us       0    79us
# Rows sent    100       4       4       4       4       4       0       4
# Rows examine 100  48.84k  48.84k  48.84k  48.84k  48.84k       0  48.84k
# Query size   100     145     145     145     145     145       0     145
# String:
# Databases    isucari
# Hosts        ip-172-31-253-109.ap-northeast-1.compute.int...
# Users        isucari
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucari` LIKE 'items'\G
#    SHOW CREATE TABLE `isucari`.`items`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `items` WHERE `seller_id` = '605' AND `status` IN ('on_sale', 'trading', 'sold_out') ORDER BY `created_at` DESC, `id` DESC LIMIT 49\G