分析-012(16494→ 18792)

sunakan commented 1 month ago

スコア：16494

sunakan commented 1 month ago

+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 23    | POST   | /api/livestream/\d+/moderate          | 0.148  | 5.454  | 11.256 | 125.451 |
| 968   | POST   | /api/livestream/\d+/livecomment       | 0.004  | 0.104  | 0.592  | 101.040 |
| 1006  | GET    | /api/livestream/\d+/reaction          | 0.012  | 0.088  | 0.696  | 88.073  |
| 10262 | GET    | /api/user/\w+/icon                    | 0.056  | 0.009  | 0.456  | 87.728  |
| 973   | GET    | /api/livestream/\d+/livecomment       | 0.004  | 0.089  | 0.385  | 86.520  |
| 900   | POST   | /api/livestream/\d+/reaction          | 0.008  | 0.095  | 0.436  | 85.054  |
| 74    | GET    | /api/livestream/search                | 0.044  | 1.060  | 2.420  | 78.464  |
| 5     | GET    | /api/livestream/\d+/statistics        | 2.768  | 13.121 | 20.001 | 65.603  |
| 316   | POST   | /api/register                         | 0.008  | 0.172  | 0.612  | 54.389  |
| 115   | POST   | /api/livestream/reservation           | 0.004  | 0.422  | 0.892  | 48.485  |
| 314   | POST   | /api/icon                             | 0.012  | 0.091  | 0.472  | 28.577  |
| 321   | POST   | /api/login                            | 0.004  | 0.046  | 0.280  | 14.856  |
| 1     | POST   | /api/initialize                       | 14.692 | 14.692 | 14.692 | 14.692  |
| 15    | GET    | /api/user/\w+/statistics              | 0.356  | 0.593  | 1.168  | 8.900   |
| 89    | POST   | /api/livestream/\d+/enter             | 0.008  | 0.065  | 0.228  | 5.816   |
| 104   | GET    | /api/tag                              | 0.004  | 0.054  | 0.236  | 5.604   |
| 80    | DELETE | /api/livestream/\d+/exit              | 0.008  | 0.057  | 0.300  | 4.564   |
| 164   | GET    | /api/livestream                       | 0.004  | 0.023  | 0.260  | 3.716   |
| 54    | GET    | /api/livestream/\d+/report            | 0.004  | 0.061  | 0.568  | 3.296   |
| 36    | GET    | /api/livestream/\d+/ngwords           | 0.008  | 0.048  | 0.184  | 1.740   |
| 14    | GET    | /api/user/\w+/theme                   | 0.004  | 0.066  | 0.248  | 0.928   |
| 2     | GET    | /api/user/kazuyasato0/livestream      | 0.064  | 0.110  | 0.156  | 0.220   |
| 2     | GET    | /api/user/yuitakahashi0/livestream    | 0.016  | 0.068  | 0.120  | 0.136   |
| 1     | GET    | /api/user/iyamamoto1/livestream       | 0.072  | 0.072  | 0.072  | 0.072   |
| 1     | GET    | /api/user/csasaki0/livestream         | 0.068  | 0.068  | 0.068  | 0.068   |
| 1     | GET    | /api/user/yamazakikyosuke0/livestream | 0.056  | 0.056  | 0.056  | 0.056   |
| 1     | GET    | /api/user/yamamotomai0/livestream     | 0.056  | 0.056  | 0.056  | 0.056   |
| 1     | GET    | /api/user/manabu070/livestream        | 0.052  | 0.052  | 0.052  | 0.052   |
| 1     | GET    | /api/user/nakamuraryohei0/livestream  | 0.048  | 0.048  | 0.048  | 0.048   |
| 1     | GET    | /api/user/reisakamoto0/livestream     | 0.044  | 0.044  | 0.044  | 0.044   |
| 1     | GET    | /api/user/yukitanaka0/livestream      | 0.040  | 0.040  | 0.040  | 0.040   |
| 1     | GET    | /api/livestream/\d+                   | 0.004  | 0.004  | 0.004  | 0.004   |
| 1     | GET    | /api/payment                          | 0.004  | 0.004  | 0.004  | 0.004   |
| 3     | GET    | /api/user/me                          | 0.000  | 0.000  | 0.000  | 0.000   |
| 1     | GET    | /api/user/test                        | 0.000  | 0.000  | 0.000  | 0.000   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+

sunakan commented 1 month ago

pt-query-digest --limit 10 tmp/analysis/latest/mysql-slow.log.*

# 14.2s user time, 100ms system time, 53.69M rss, 392.67G vsz
# Current date: Mon Sep 23 20:06:42 2024
# Hostname:
# Files: tmp/analysis/latest/mysql-slow.log.isu-1, tmp/analysis/latest/mysql-slow.log.isu-2, tmp/analysis/latest/mysql-slow.log.isu-3
# Overall: 317.38k total, 88 unique, 2.54k QPS, 1.97x concurrency ________
# Time range: 2024-09-23T11:01:58 to 2024-09-23T11:04:03
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           246s     1us      1s   774us     3ms     6ms   138us
# Lock time          838ms       0    19ms     2us     1us    90us     1us
# Rows sent        259.52k       0   7.40k    0.84    0.99   32.87       0
# Rows examine      15.35M       0   1.92M   50.73    1.96   4.81k       0
# Query size        60.09M       5   1.94M  198.51  329.68   4.28k   97.36

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0xFFFCA4D67EA0A788813031B8BBC3B329  29.4310 12.0%   5863 0.0050  0.01 COMMIT
#    2 0x4ADE2DC90689F1C4891749AF54FB8D14  23.1396  9.4%  37929 0.0006  0.00 DELETE SELECT livecomments
#    3 0xCCC66E25F4CBA18D3BA68482F057E31E  20.9062  8.5%    918 0.0228  0.05 SELECT livestream_tags tags reactions users themes icons livestreams users themes icons
#    4 0x800615AEAD71CE5F92FDFF37198A857F  20.0726  8.2%    884 0.0227  0.05 SELECT livestream_tags tags livecomments users themes icons livestreams users themes icons
#    5 0x59F1B6DD8D9FEC059E55B3BFD624E8C3  17.8875  7.3%   1012 0.0177  0.01 SELECT reservation_slots
#    6 0x22279D81D51006139E0C76405B54C261  15.1560  6.2%  26680 0.0006  0.00 SELECT domains domainmetadata
#    7 0x42EF7D7D98FBCC9723BF896EBFC51D24  13.0765  5.3%  21469 0.0006  0.00 SELECT records
#    8 0x8F7679D452333ED3C7D60D22131CEFD4  12.1788  5.0%  63376 0.0002  0.00 ADMIN RESET STMT
#    9 0x3D83BC87F3B3A00D571FFC8104A6E50C   9.8577  4.0%  15010 0.0007  0.00 SELECT records
#   10 0xFD38427AE3D09E3883A680F7BAF95D3A   8.6556  3.5%  25359 0.0003  0.00 SELECT livestreams livecomments
#   11 0x9F5924152C1756E0B72AB80A1924120F   8.2741  3.4%     15 0.5516  0.12 SELECT livestreams reactions livestreams livecomments users scores livestreams livecomments livestreams livestream_viewers_history livestreams reactions user_ranking
# MISC 0xMISC                              67.3186 27.4% 118862 0.0006   0.0 <77 ITEMS>

# Query 1: 77.14 QPS, 0.39x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 73004760
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-23T11:02:47 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    5863
# Exec time     11     29s    24us    56ms     5ms    15ms     6ms     4ms
# 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     0  34.35k       6       6       6       6       0       6
# String:
# Databases    isupipe (5548/94%), isudns (315/5%)
# Hosts        localhost
# Users        isucon (5548/94%), isudns (315/5%)
# Query_time distribution
#   1us
#  10us  ###############################################################
# 100us  ##########
#   1ms  ################################################################
#  10ms  ################################
# 100ms
#    1s
#  10s+
COMMIT\G

# Query 2: 621.79 QPS, 0.38x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 76738299
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:03:02 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   37929
# Exec time      9     23s   102us    37ms   610us     2ms     1ms   194us
# Lock time     11    98ms       0     5ms     2us     1us    53us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  74.10k       2       4    2.00    1.96    0.03    1.96
# Query size    19  11.96M     263     520  330.52  381.65   27.34  313.99
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
DELETE FROM livecomments
            WHERE
            id = 999 AND
            livestream_id = 7526 AND
            (SELECT COUNT(*)
            FROM
            (SELECT '次のトーナメント、応援しています！' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '記憶選択錯誤', '%')  AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  livecomments
            WHERE
            id = 999 AND
            livestream_id = 7526 AND
            (SELECT COUNT(*)
            FROM
            (SELECT '次のトーナメント、応援しています！' AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', '記憶選択錯誤', '%')  AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1\G

# Query 3: 14.34 QPS, 0.33x concurrency, ID 0xCCC66E25F4CBA18D3BA68482F057E31E at byte 27984092
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2024-09-23T11:02:59 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     918
# Exec time      8     21s   361us   275ms    23ms    91ms    34ms     9ms
# Lock time      1    11ms     2us     2ms    11us     7us    72us     4us
# Rows sent      4  12.71k       0      59   14.18   38.53   12.38    9.83
# Rows examine   1 209.35k       0     887  233.52  563.87  192.90  166.51
# Query size     2   1.79M   1.99k   1.99k   1.99k   1.99k       0   1.99k
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ##########
#   1ms  ################################################################
#  10ms  #############################################################
# 100ms  ######
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_tags`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reactions'\G
#    SHOW CREATE TABLE `isupipe`.`reactions`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
# EXPLAIN /*!50100 PARTITIONS*/
select
  reactions.id as "reaction_id"
  , reactions.emoji_name as "reaction_emoji_name"
  , reactions.created_at as "reaction_created_at"
  , users.id as "user_id"
  , users.name as "user_name"
  , users.display_name as "user_display_name"
  , users.description as "user_description"
  , themes.id as "theme_id"
  , themes.dark_mode as "theme_dark_mode"
  , icons.image as "icon_image"
  , livestreams.id as "livestream_id"
  , livestreams.title as "livestream_title"
  , livestreams.description as "livestream_description"
  , livestreams.playlist_url as "livestream_playlist_url"
  , livestreams.thumbnail_url as "livestream_thumbnail_url"
  , livestreams.start_at as "livestream_start_at"
  , livestreams.end_at as "livestream_end_at"
  , livestream_owners.id as "livestream_owner_id"
  , livestream_owners.name as "livestream_owner_name"
  , livestream_owners.display_name as "livestream_owner_display_name"
  , livestream_owners.description as "livestream_owner_description"
  , livestream_owner_themes.id as "livestream_owner_theme_id"
  , livestream_owner_themes.dark_mode as "livestream_owner_theme_dark_mode"
  , livestream_owner_icons.image as "livestream_owner_icon_image"
  , IFNULL((select CONCAT('[', GROUP_CONCAT(CONCAT('{"id":', tags.id, ',"name":"', tags.name, '"}') SEPARATOR ','), ']') from livestream_tags inner join tags on livestream_tags.tag_id = tags.id where livestream_tags.livestream_id = reactions.livestream_id), '[]') as "livestream_tags"
from reactions
inner join users on users.id = reactions.user_id
inner join themes on themes.user_id = users.id
left join icons on icons.user_id = users.id
inner join livestreams on livestreams.id = reactions.livestream_id
inner join users as livestream_owners on livestream_owners.id = livestreams.user_id
inner join themes as livestream_owner_themes on livestream_owner_themes.user_id = livestream_owners.id
left join icons as livestream_owner_icons on livestream_owner_icons.user_id = livestream_owners.id
where reactions.livestream_id = 7525
order by created_at desc\G

# Query 4: 14.49 QPS, 0.33x concurrency, ID 0x800615AEAD71CE5F92FDFF37198A857F at byte 28004837
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.05
# Time range: 2024-09-23T11:03:02 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     884
# Exec time      8     20s   469us   273ms    23ms    91ms    34ms    10ms
# Lock time      1    11ms     3us     2ms    12us     7us    96us     4us
# Rows sent      5  13.14k       0      59   15.22   38.53   12.34   11.95
# Rows examine   1 221.23k       0     887  256.26  621.67  199.99  202.40
# Query size     2   1.79M   2.07k   2.07k   2.07k   2.07k       0   2.07k
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  #####
#   1ms  ##############################################################
#  10ms  ################################################################
# 100ms  ######
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_tags'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_tags`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
# EXPLAIN /*!50100 PARTITIONS*/
select
livecomments.id as "livecomment_id"
  , livecomments.comment as "livecomment_comment"
  , livecomments.tip as "livecomment_tip"
  , livecomments.created_at as "livecomment_created_at"
  , users.id as "user_id"
  , users.name as "user_name"
  , users.display_name as "user_display_name"
  , users.description as "user_description"
  , themes.id as "theme_id"
  , themes.dark_mode as "theme_dark_mode"
  , icons.image as "icon_image"
  , livestreams.id as "livestream_id"
  , livestreams.title as "livestream_title"
  , livestreams.description as "livestream_description"
  , livestreams.playlist_url as "livestream_playlist_url"
  , livestreams.thumbnail_url as "livestream_thumbnail_url"
  , livestreams.start_at as "livestream_start_at"
  , livestreams.end_at as "livestream_end_at"
  , livestream_owners.id as "livestream_owner_id"
  , livestream_owners.name as "livestream_owner_name"
  , livestream_owners.display_name as "livestream_owner_display_name"
  , livestream_owners.description as "livestream_owner_description"
  , livestream_owner_themes.id as "livestream_owner_theme_id"
  , livestream_owner_themes.dark_mode as "livestream_owner_theme_dark_mode"
  , livestream_owner_icons.image as "livestream_owner_icon_image"
  , IFNULL((select CONCAT('[', GROUP_CONCAT(CONCAT('{"id":', tags.id, ',"name":"', tags.name, '"}') SEPARATOR ','), ']') from livestream_tags inner join tags on livestream_tags.tag_id = tags.id where livestream_tags.livestream_id = livecomments.livestream_id), '[]') as "livestream_tags"
from livecomments
inner join users on users.id = livecomments.user_id
inner join themes on themes.user_id = users.id
left join icons on icons.user_id = users.id
inner join livestreams on livestreams.id = livecomments.livestream_id
inner join users as livestream_owners on livestream_owners.id = livestreams.user_id
inner join themes as livestream_owner_themes on livestream_owner_themes.user_id = livestream_owners.id
left join icons as livestream_owner_icons on livestream_owner_icons.user_id = livestream_owners.id
where livecomments.livestream_id = 7526
order by livecomments.created_at desc\G

# Query 5: 13.49 QPS, 0.24x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 123817857
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2024-09-23T11:02:47 to 2024-09-23T11:04:02
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1012
# Exec time      7     18s     2ms   108ms    18ms    46ms    14ms    13ms
# Lock time      0     2ms       0   286us     1us     1us    10us     1us
# Rows sent      0    1012       1       1       1       1       0       1
# Rows examine  55   8.45M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  84.99k      86      86      86      86       0      86
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###################################
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reservation_slots'\G
#    SHOW CREATE TABLE `isupipe`.`reservation_slots`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT slot FROM reservation_slots WHERE start_at = 1701604800 AND end_at = 1701608400\G

# Query 6: 296.44 QPS, 0.17x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 81382330
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:02:33 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8   26680
# Exec time      6     15s    84us    33ms   568us     2ms     1ms   185us
# Lock time     11    96ms       0     7ms     3us     2us    66us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     4   2.88M     113     113     113     113       0     113
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domains'\G
#    SHOW CREATE TABLE `isudns`.`domains`\G
#    SHOW TABLE STATUS FROM `isudns` LIKE 'domainmetadata'\G
#    SHOW CREATE TABLE `isudns`.`domainmetadata`\G
# EXPLAIN /*!50100 PARTITIONS*/
select kind,content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='t.isucon.pw'\G

# Query 7: 238.54 QPS, 0.15x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 79324994
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:02:33 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   21469
# Exec time      5     13s    93us    55ms   609us     2ms     1ms   204us
# Lock time      6    55ms       0     5ms     2us     1us    42us     1us
# Rows sent      0   2.28k       0       1    0.11    0.99    0.31       0
# Rows examine   0   2.28k       0       1    0.11    0.99    0.31       0
# Query size     4   2.89M     128     213  141.35  151.03   11.70  136.99
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.t.isucon.pw' and domain_id=1\G

# Query 8: 704.18 QPS, 0.14x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 98059968
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:02:33 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         19   63376
# Exec time      4     12s     9us    45ms   192us   657us   764us    42us
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3   1.99M      33      33      33      33       0      33
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us  #
#  10us  ################################################################
# 100us  ###############
#   1ms  ##
#  10ms  #
# 100ms
#    1s
#  10s+
administrator command: Reset stmt\G

# Query 9: 166.78 QPS, 0.11x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 127051945
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:02:33 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   15010
# Exec time      4     10s    97us    29ms   656us     3ms     1ms   224us
# Lock time      6    54ms       0     3ms     3us     1us    56us     1us
# Rows sent      2   7.39k       0       1    0.50    0.99    0.50    0.99
# Rows examine   0   7.39k       0       1    0.50    0.99    0.50    0.99
# Query size     3   1.94M     125     212  135.24  151.03   11.22  124.25
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ##########
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='t.isucon.pw'\G

# Query 10: 357.17 QPS, 0.12x concurrency, ID 0xFD38427AE3D09E3883A680F7BAF95D3A at byte 121010211
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2024-09-23T11:02:49 to 2024-09-23T11:04:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7   25359
# Exec time      3      9s    96us    27ms   341us     1ms   824us   131us
# Lock time      6    54ms       0     4ms     2us     1us    36us     1us
# Rows sent      9  24.76k       1       1       1       1       0       1
# Rows examine   0   3.26k       0       3    0.13    0.99    0.36       0
# Query size     4   2.90M     117     120  119.78  118.34    0.78  118.34
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ####
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(l2.tip), 0) FROM livestreams l INNER JOIN livecomments l2 ON l.id = l2.livestream_id WHERE l.id = 3442\G

# Query 11: 0.20 QPS, 0.11x concurrency, ID 0x9F5924152C1756E0B72AB80A1924120F at byte 9845565
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.12
# Time range: 2024-09-23T11:02:49 to 2024-09-23T11:04:03
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      15
# Exec time      3      8s   268ms      1s   552ms      1s   258ms   433ms
# Lock time      0    96us     4us     8us     6us     7us     1us     5us
# Rows sent      0      15       1       1       1       1       0       1
# Rows examine  26   4.08M  17.95k   1.92M 278.72k   1.86M 640.74k  18.47k
# Query size     0  22.12k   1.47k   1.48k   1.47k   1.46k       0   1.46k
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s  #########
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reactions'\G
#    SHOW CREATE TABLE `isupipe`.`reactions`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'scores'\G
#    SHOW CREATE TABLE `isupipe`.`scores`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestream_viewers_history'\G
#    SHOW CREATE TABLE `isupipe`.`livestream_viewers_history`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'user_ranking'\G
#    SHOW CREATE TABLE `isupipe`.`user_ranking`\G
with scores as (
select
  users.id as user_id
  , users.name as user_name
  , IFNULL((select count(1) from livestreams inner join reactions on reactions.livestream_id = livestreams.id where livestreams.user_id = users.id), 0) as total_reactions
  , IFNULL((select sum(livecomments.tip) from livestreams inner join livecomments on livecomments.livestream_id = livestreams.id where livestreams.user_id = users.id), 0) as total_tip
from users
), user_ranking as (
select
  scores.user_id as user_id
  , scores.user_name
  , scores.total_reactions
  , scores.total_tip
  , ROW_NUMBER() over (order by (scores.total_reactions+scores.total_tip) desc, scores.user_name desc) as "rank"
from scores
)
select
  user_ranking.rank
  , user_ranking.total_reactions
  , (select count(1) from livestreams inner join livecomments on livecomments.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id) as total_livecomments
  , user_ranking.total_tip
  , (select count(1) from livestreams inner join livestream_viewers_history on livestream_viewers_history.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id) as viewers_count
  , IFNULL((select reactions.emoji_name from livestreams inner join reactions on reactions.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id group by reactions.emoji_name order by count(1) desc, reactions.emoji_name desc limit 1), '') as favorite_emoji
from user_ranking
where user_ranking.user_name = 'suzukitsubasa0'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select
  users.id as user_id
  , users.name as user_name
  , IFNULL((select count(1) from livestreams inner join reactions on reactions.livestream_id = livestreams.id where livestreams.user_id = users.id), 0) as total_reactions
  , IFNULL((select sum(livecomments.tip) from livestreams inner join livecomments on livecomments.livestream_id = livestreams.id where livestreams.user_id = users.id), 0) as total_tip
from users
), user_ranking as (
select
  scores.user_id as user_id
  , scores.user_name
  , scores.total_reactions
  , scores.total_tip
  , ROW_NUMBER() over (order by (scores.total_reactions+scores.total_tip) desc, scores.user_name desc) as "rank"
from scores
)
select
  user_ranking.rank
  , user_ranking.total_reactions
  , (select count(1) from livestreams inner join livecomments on livecomments.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id) as total_livecomments
  , user_ranking.total_tip
  , (select count(1) from livestreams inner join livestream_viewers_history on livestream_viewers_history.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id) as viewers_count
  , IFNULL((select reactions.emoji_name from livestreams inner join reactions on reactions.livestream_id = livestreams.id where livestreams.user_id = user_ranking.user_id group by reactions.emoji_name order by count(1) desc, reactions.emoji_name desc limit 1), '') as favorite_emoji
from user_ranking
where user_ranking.user_name = 'suzukitsubasa0'\G

sunakan commented 1 month ago

+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 23    | POST   | /api/livestream/\d+/moderate          | 0.148  | 5.454  | 11.256 | 125.451 |

23回しかcallされてないけど、面積が大きいので見てみる

sunakan commented 1 month ago

// NGワードを登録
func moderateHandler(c echo.Context) error {
    ctx := c.Request().Context()
    defer c.Request().Body.Close()

    if err := verifyUserSession(c); err != nil {
        return err
    }

    livestreamID, err := strconv.Atoi(c.Param("livestream_id"))
    if err != nil {
        return echo.NewHTTPError(http.StatusBadRequest, "livestream_id in path must be integer")
    }

    // error already checked
    sess, _ := session.Get(defaultSessionIDKey, c)
    // existence already checked
    userID := sess.Values[defaultUserIDKey].(int64)

    var req *ModerateRequest
    if err := json.NewDecoder(c.Request().Body).Decode(&req); err != nil {
        return echo.NewHTTPError(http.StatusBadRequest, "failed to decode the request body as json")
    }

    tx, err := dbConn.BeginTxx(ctx, nil)
    if err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to begin transaction: "+err.Error())
    }
    defer tx.Rollback()

    // 配信者自身の配信に対するmoderateなのかを検証
    var ownedLivestreams []LivestreamModel
    if err := tx.SelectContext(ctx, &ownedLivestreams, "SELECT * FROM livestreams WHERE id = ? AND user_id = ?", livestreamID, userID); err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to get livestreams: "+err.Error())
    }
    if len(ownedLivestreams) == 0 {
        return echo.NewHTTPError(http.StatusBadRequest, "A streamer can't moderate livestreams that other streamers own")
    }

    rs, err := tx.NamedExecContext(ctx, "INSERT INTO ng_words(user_id, livestream_id, word, created_at) VALUES (:user_id, :livestream_id, :word, :created_at)", &NGWord{
        UserID:       int64(userID),
        LivestreamID: int64(livestreamID),
        Word:         req.NGWord,
        CreatedAt:    time.Now().Unix(),
    })
    if err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to insert new NG word: "+err.Error())
    }

    wordID, err := rs.LastInsertId()
    if err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to get last inserted NG word id: "+err.Error())
    }

    var ngwords []*NGWord
    if err := tx.SelectContext(ctx, &ngwords, "SELECT * FROM ng_words WHERE livestream_id = ?", livestreamID); err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to get NG words: "+err.Error())
    }

    // NGワードにヒットする過去の投稿も全削除する
    for _, ngword := range ngwords {
        // ライブコメント一覧取得
        var livecomments []*LivecommentModel
        if err := tx.SelectContext(ctx, &livecomments, "SELECT * FROM livecomments"); err != nil {
            return echo.NewHTTPError(http.StatusInternalServerError, "failed to get livecomments: "+err.Error())
        }

        for _, livecomment := range livecomments {
            query := `
            DELETE FROM livecomments
            WHERE
            id = ? AND
            livestream_id = ? AND
            (SELECT COUNT(*)
            FROM
            (SELECT ? AS text) AS texts
            INNER JOIN
            (SELECT CONCAT('%', ?, '%') AS pattern) AS patterns
            ON texts.text LIKE patterns.pattern) >= 1;
            `
            if _, err := tx.ExecContext(ctx, query, livecomment.ID, livestreamID, livecomment.Comment, ngword.Word); err != nil {
                return echo.NewHTTPError(http.StatusInternalServerError, "failed to delete old livecomments that hit spams: "+err.Error())
            }
        }
    }

    if err := tx.Commit(); err != nil {
        return echo.NewHTTPError(http.StatusInternalServerError, "failed to commit: "+err.Error())
    }

    return c.JSON(http.StatusCreated, map[string]interface{}{
        "word_id": wordID,
    })
}

sunakan commented 1 month ago

livestreamID, err := strconv.Atoi(c.Param("livestream_id"))

livestream_idを取る

sunakan commented 1 month ago

2a2a96c

スコア：18792

sunakan commented 1 month ago

/api/livestream/\d+/moderate

はだいぶ改善した

before

+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 23    | POST   | /api/livestream/\d+/moderate          | 0.148  | 5.454  | 11.256 | 125.451 |
| 968   | POST   | /api/livestream/\d+/livecomment       | 0.004  | 0.104  | 0.592  | 101.040 |

after

+-------+--------+--------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                 URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| 147   | GET    | /api/livestream/search               | 0.044  | 1.132  | 2.412  | 166.419 |
| 1193  | POST   | /api/livestream/\d+/livecomment      | 0.004  | 0.103  | 0.632  | 123.136 |
| 1014  | POST   | /api/livestream/\d+/reaction         | 0.004  | 0.090  | 0.460  | 90.827  |
| 1102  | GET    | /api/livestream/\d+/livecomment      | 0.004  | 0.080  | 0.324  | 88.089  |
| 1133  | GET    | /api/livestream/\d+/reaction         | 0.004  | 0.076  | 0.672  | 86.012  |
| 6     | GET    | /api/livestream/\d+/statistics       | 2.812  | 11.699 | 20.004 | 70.196  |
| 11717 | GET    | /api/user/\w+/icon                   | 0.000  | 0.006  | 0.336  | 69.205  |
| 311   | POST   | /api/register                        | 0.004  | 0.185  | 0.396  | 57.524  |
| 125   | POST   | /api/livestream/reservation          | 0.104  | 0.394  | 0.972  | 49.189  |
| 310   | POST   | /api/icon                            | 0.008  | 0.087  | 0.420  | 27.109  |
| 322   | GET    | /api/livestream                      | 0.004  | 0.043  | 0.320  | 13.900  |
| 1     | POST   | /api/initialize                      | 13.892 | 13.892 | 13.892 | 13.892  |
| 317   | POST   | /api/login                           | 0.004  | 0.038  | 0.280  | 12.144  |
| 16    | GET    | /api/user/\w+/statistics             | 0.032  | 0.453  | 0.664  | 7.248   |
| 145   | GET    | /api/livestream/\d+/report           | 0.004  | 0.047  | 0.304  | 6.844   |
| 104   | POST   | /api/livestream/\d+/enter            | 0.004  | 0.065  | 0.304  | 6.800   |
| 107   | GET    | /api/livestream/\d+/ngwords          | 0.004  | 0.062  | 0.292  | 6.588   |
| 113   | GET    | /api/tag                             | 0.004  | 0.044  | 0.260  | 4.948   |
| 93    | DELETE | /api/livestream/\d+/exit             | 0.004  | 0.048  | 0.232  | 4.452   |
| 67    | POST   | /api/livestream/\d+/moderate         | 0.008  | 0.057  | 0.172  | 3.796   |

sunakan / isunarabe13-suburi

分析-012(16494→ 18792) #15

before

after