search

sunakan / isunarabe13-suburi

0 stars 0 forks source link

分析-007(8012→ 9975) #8

Open sunakan opened 17 hours ago

sunakan commented 17 hours ago

の続き

スコア：8012

sunakan commented 17 hours ago 
make alp

alp json --sort sum -r -o count,method,uri,min,avg,max,sum --file tmp/analysis/latest/nginx-access.log.* -m '/api/user/\w+/statistics,/api/user/\w+/icon,/api/user/\w+/theme,/api/livestream/\d+/livecomment,/api/livestream/\d+/reaction,/api/livestream/\d+/moderate,/api/livestream/\d+/report,/api/livestream/\d+/ngwords,/api/livestream/\d+/exit,/api/livestream/\d+/enter,/api/livestream/\d+/statistics,/api/livestream/\d+'
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 4729  | GET    | /api/user/\w+/icon                    | 0.012  | 0.040  | 0.484  | 189.960 |
| 30    | POST   | /api/livestream/\d+/moderate          | 0.124  | 4.110  | 13.040 | 123.308 |
| 468   | GET    | /api/livestream/\d+/livecomment       | 0.024  | 0.248  | 0.928  | 116.120 |
| 501   | GET    | /api/livestream/\d+/reaction          | 0.004  | 0.213  | 0.832  | 106.724 |
| 546   | POST   | /api/livestream/\d+/livecomment       | 0.004  | 0.134  | 0.428  | 73.132  |
| 72    | GET    | /api/livestream/search                | 0.036  | 0.701  | 1.632  | 50.460  |
| 384   | POST   | /api/register                         | 0.008  | 0.130  | 0.416  | 49.844  |
| 10    | GET    | /api/user/\w+/statistics              | 0.456  | 4.903  | 7.700  | 49.028  |
| 128   | POST   | /api/livestream/reservation           | 0.136  | 0.379  | 0.940  | 48.476  |
| 439   | POST   | /api/livestream/\d+/reaction          | 0.004  | 0.074  | 0.372  | 32.584  |
| 384   | POST   | /api/icon                             | 0.012  | 0.085  | 0.416  | 32.492  |
| 388   | POST   | /api/login                            | 0.004  | 0.039  | 0.324  | 15.032  |
| 1     | POST   | /api/initialize                       | 14.208 | 14.208 | 14.208 | 14.208  |
| 2     | GET    | /api/livestream/\d+/statistics        | 2.724  | 2.762  | 2.800  | 5.524   |
| 111   | GET    | /api/tag                              | 0.004  | 0.046  | 0.216  | 5.080   |
| 213   | GET    | /api/livestream                       | 0.004  | 0.022  | 0.276  | 4.756   |
| 43    | GET    | /api/livestream/\d+/ngwords           | 0.004  | 0.087  | 0.184  | 3.744   |
| 45    | POST   | /api/livestream/\d+/enter             | 0.004  | 0.065  | 0.328  | 2.908   |
| 60    | GET    | /api/livestream/\d+/report            | 0.004  | 0.046  | 0.288  | 2.756   |
| 37    | DELETE | /api/livestream/\d+/exit              | 0.008  | 0.037  | 0.120  | 1.356   |
| 9     | GET    | /api/user/\w+/theme                   | 0.004  | 0.031  | 0.060  | 0.280   |
| 1     | GET    | /api/user/satomikobayashi0/livestream | 0.112  | 0.112  | 0.112  | 0.112   |
| 1     | GET    | /api/user/shayashi0/livestream        | 0.092  | 0.092  | 0.092  | 0.092   |
| 1     | GET    | /api/user/iyamamoto1/livestream       | 0.064  | 0.064  | 0.064  | 0.064   |
| 1     | GET    | /api/user/yamazakikyosuke0/livestream | 0.048  | 0.048  | 0.048  | 0.048   |
| 1     | GET    | /api/user/yuitakahashi0/livestream    | 0.040  | 0.040  | 0.040  | 0.040   |
| 1     | GET    | /api/user/mikimatsuda0/livestream     | 0.032  | 0.032  | 0.032  | 0.032   |
| 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   |
| 1     | GET    | /api/user/test                        | 0.000  | 0.000  | 0.000  | 0.000   |
| 3     | GET    | /api/user/me                          | 0.000  | 0.000  | 0.000  | 0.000   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
sunakan commented 17 hours ago 
make pt-query-digest
pt-query-digest --limit 10 tmp/analysis/latest/mysql-slow.log.*

# 18.2s user time, 150ms system time, 56.53M rss, 392.67G vsz
# Current date: Sun Sep 22 21:05:16 2024
# Hostname: CWPC20001.local
# 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: 418.03k total, 94 unique, 3.91k QPS, 2.78x concurrency ________
# Time range: 2024-09-22T12:00:14 to 2024-09-22T12:02:01
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           297s     1us   375ms   711us     3ms     3ms   159us
# Lock time             1s       0    34ms     3us     1us   115us     1us
# Rows sent        355.56k       0   7.32k    0.87    0.99   21.19    0.99
# Rows examine      19.94M       0  14.03k   50.02    6.98  720.13    0.99
# Query size        68.00M       5   1.94M  170.58  329.68   3.82k   42.48

# Profile
# Rank Query ID                            Response time Calls  R/Call V/M
# ==== =================================== ============= ====== ====== ===
#    1 0x4ADE2DC90689F1C4891749AF54FB8D14  39.5250 13.3%  49351 0.0008  0.00 DELETE SELECT livecomments
#    2 0x84B457C910C4A79FC9EBECB8B1065C66  25.7570  8.7%  27653 0.0009  0.00 SELECT icons
#    3 0xFBC5564AE716EAE82F20BFB45F6C37E7  23.9936  8.1%  56906 0.0004  0.00 SELECT tags
#    4 0xFFFCA4D67EA0A788813031B8BBC3B329  21.8210  7.3%   4210 0.0052  0.01 COMMIT
#    5 0x64CC8A4E8E4B390203375597CE4D611F  18.9523  6.4%    491 0.0386  0.01 SELECT ng_words
#    6 0x59F1B6DD8D9FEC059E55B3BFD624E8C3  18.7411  6.3%   1124 0.0167  0.01 SELECT reservation_slots
#    7 0x22279D81D51006139E0C76405B54C261  15.5229  5.2%  25314 0.0006  0.00 SELECT domains domainmetadata
#    8 0x42EF7D7D98FBCC9723BF896EBFC51D24  12.7195  4.3%  20490 0.0006  0.00 SELECT records
#    9 0x38BC86A45F31C6B1EE324671506C898A  12.5962  4.2%  22934 0.0005  0.00 SELECT themes
#   10 0x8F7679D452333ED3C7D60D22131CEFD4  11.3854  3.8%  61495 0.0002  0.00 ADMIN RESET STMT
# MISC 0xMISC                              96.3822 32.4% 148061 0.0007   0.0 <84 ITEMS>

# Query 1: 809.03 QPS, 0.65x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 33079963
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:01:00 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         11   49351
# Exec time     13     40s    98us    36ms   800us     3ms     1ms   236us
# Lock time     13   183ms       0     9ms     3us     1us    86us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  96.40k       2       4    2.00    1.96    0.02    1.96
# Query size    22  15.54M     263     529  330.18  363.48   26.13  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 = 7 AND
            livestream_id = 7528 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 = 7 AND
            livestream_id = 7528 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 2: 373.69 QPS, 0.35x concurrency, ID 0x84B457C910C4A79FC9EBECB8B1065C66 at byte 47329375
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:47 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   27653
# Exec time      8     26s    71us    45ms   931us     3ms     2ms   348us
# Lock time      6    94ms       0     5ms     3us     1us    66us     1us
# Rows sent      6  22.90k       0       1    0.85    0.99    0.36    0.99
# Rows examine   0  22.90k       0       1    0.85    0.99    0.36    0.99
# Query size     1   1.16M      41      44   43.86   42.48    0.23   42.48
# 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 'icons'\G
#    SHOW CREATE TABLE `isupipe`.`icons`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT image FROM icons WHERE user_id = 1016\G

# Query 3: 849.34 QPS, 0.36x concurrency, ID 0xFBC5564AE716EAE82F20BFB45F6C37E7 at byte 116327762
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:54 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         13   56906
# Exec time      8     24s    46us    33ms   421us     2ms   858us   125us
# Lock time     13   179ms       0     9ms     3us     1us    64us     1us
# Rows sent     15  55.57k       1       1       1       1       0       1
# Rows examine   0  55.57k       1       1       1       1       0       1
# Query size     2   1.73M      31      33   31.92   31.70    0.44   31.70
# 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 'tags'\G
#    SHOW CREATE TABLE `isupipe`.`tags`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM tags WHERE id = 54\G

# Query 4: 56.89 QPS, 0.29x concurrency, ID 0xFFFCA4D67EA0A788813031B8BBC3B329 at byte 58335868
# Scores: V/M = 0.01
# Time range: 2024-09-22T12:00:47 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    4210
# Exec time      7     22s    22us    97ms     5ms    16ms     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  24.67k       6       6       6       6       0       6
# String:
# Databases    isupipe (3829/90%), isudns (381/9%)
# Hosts        localhost
# Users        isucon (3829/90%), isudns (381/9%)
# Query_time distribution
#   1us
#  10us  #################################
# 100us  ###############
#   1ms  ################################################################
#  10ms  ######################
# 100ms  #
#    1s
#  10s+
COMMIT\G

# Query 5: 7.01 QPS, 0.27x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 70955801
# Scores: V/M = 0.01
# Time range: 2024-09-22T12:00:51 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     491
# Exec time      6     19s     4ms   115ms    39ms    71ms    20ms    34ms
# Lock time      0   592us       0    23us     1us     1us     1us     1us
# Rows sent      0     149       0       2    0.30    0.99    0.48       0
# Rows examine  33   6.72M  14.00k  14.03k  14.01k  13.78k       0  13.78k
# Query size     0  47.47k      97      99   98.99   97.36    0.30   97.36
# 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 'ng_words'\G
#    SHOW CREATE TABLE `isupipe`.`ng_words`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1040 AND livestream_id = 7532\G

# Query 6: 15.19 QPS, 0.25x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 62879481
# Scores: V/M = 0.01
# Time range: 2024-09-22T12:00:47 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1124
# Exec time      6     19s     2ms    82ms    17ms    42ms    13ms    12ms
# Lock time      0    12ms       0     5ms    10us     1us   202us     1us
# Rows sent      0   1.10k       1       1       1       1       0       1
# Rows examine  47   9.39M   8.55k   8.55k   8.55k   8.55k       0   8.55k
# Query size     0  94.40k      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 = 1701162000 AND end_at = 1701165600\G

# Query 7: 287.66 QPS, 0.18x concurrency, ID 0x22279D81D51006139E0C76405B54C261 at byte 45918507
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:33 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   25314
# Exec time      5     16s    85us    23ms   613us     2ms     1ms   214us
# Lock time      8   116ms       0    11ms     4us     1us   107us     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.73M     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 8: 232.84 QPS, 0.14x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 34983871
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:33 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   20490
# Exec time      4     13s    90us    33ms   620us     2ms     1ms   224us
# Lock time      5    69ms       0     8ms     3us     1us    76us     1us
# Rows sent      0   2.47k       0       1    0.12    0.99    0.33       0
# Rows examine   0   2.47k       0       1    0.12    0.99    0.33       0
# Query size     4   2.76M     128     219  141.31  151.03   11.82  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='xzcb48zsww3ly70.t.isucon.pw' and domain_id=1\G

# Query 9: 309.92 QPS, 0.17x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 144176724
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:47 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   22934
# Exec time      4     13s    77us    19ms   549us     2ms     1ms   176us
# Lock time      5    78ms       0     5ms     3us     1us    55us     1us
# Rows sent      6  22.40k       1       1       1       1       0       1
# Rows examine   0  22.40k       1       1       1       1       0       1
# Query size     1 915.63k      38      41   40.88   40.45    0.60   40.45
# 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 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1075\G

# Query 10: 698.81 QPS, 0.13x concurrency, ID 0x8F7679D452333ED3C7D60D22131CEFD4 at byte 24719611
# Scores: V/M = 0.00
# Time range: 2024-09-22T12:00:33 to 2024-09-22T12:02:01
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14   61495
# Exec time      3     11s     9us    22ms   185us   657us   607us    47us
# 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   1.94M      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
sunakan commented 17 hours ago

indexだけでは効果が微妙になってきた

他のところをやってみて、また戻ってくるのもありかもしれない

sunakan commented 17 hours ago

やったことを一覧しておく

-- 1. livestream_tagsテーブルに1つ(3068→4180)
alter table livestream_tags add index livestream_id_idx (livestream_id);

-- 2. iconsテーブルに1つ(4183→5705)
alter table icons add index user_id_idx (user_id);

-- 3. プリペアードステートメントのやつ(5705→6476)
interpolateParams=true

-- 4. themesテーブルに1つ(6476→7303)
alter table themes add index user_id_idx (user_id);

-- 5. livecommentsとlivestreamsテーブルに1つ(7303→7391→7875)
alter table livecomments add index livestream_id_idx (livestream_id);
alter table livestreams add index user_id_idx (user_id);

-- 6. reactionsテーブルに1つ(7875→8012)
alter table reactions add index livestream_id_idx (livestream_id);
sunakan commented 17 hours ago 
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 4729  | GET    | /api/user/\w+/icon                    | 0.012  | 0.040  | 0.484  | 189.960 |
| 30    | POST   | /api/livestream/\d+/moderate          | 0.124  | 4.110  | 13.040 | 123.308 |
| 468   | GET    | /api/livestream/\d+/livecomment       | 0.024  | 0.248  | 0.928  | 116.120 |
| 501   | GET    | /api/livestream/\d+/reaction          | 0.004  | 0.213  | 0.832  | 106.724 |
| 546   | POST   | /api/livestream/\d+/livecomment       | 0.004  | 0.134  | 0.428  | 73.132  |
| 72    | GET    | /api/livestream/search                | 0.036  | 0.701  | 1.632  | 50.460  |
| 384   | POST   | /api/register                         | 0.008  | 0.130  | 0.416  | 49.844  |

/api/user/\w+/icon が圧倒的にcallされている & 合計も大きい = 面積がデカい

つまり効果が高そうなので、その辺のコードを見る

sunakan commented 17 hours ago

画像そのものがDBに入ってそう（もしかしたら、nginxで配信させたら高速化狙えるかも）

image
sunakan commented 17 hours ago

実装を読む

grep -rn '/icon'
main.go:176:    e.GET("/api/user/:username/icon", getIconHandler)
main.go:177:    e.POST("/api/icon", postIconHandler)
grep -rn 'getIconHandler('
user_handler.go:88:func getIconHandler(c echo.Context) error {
func getIconHandler(c echo.Context) error {
        ctx := c.Request().Context()

        username := c.Param("username")

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

        var user UserModel
        if err := tx.GetContext(ctx, &user, "SELECT * FROM users WHERE name = ?", username); err != nil {
                if errors.Is(err, sql.ErrNoRows) {
                        return echo.NewHTTPError(http.StatusNotFound, "not found user that has the given username")
                }
                return echo.NewHTTPError(http.StatusInternalServerError, "failed to get user: "+err.Error())
        }

        var image []byte
        if err := tx.GetContext(ctx, &image, "SELECT image FROM icons WHERE user_id = ?", user.ID); err != nil {
                if errors.Is(err, sql.ErrNoRows) {
                        return c.File(fallbackImage)
                } else {
                        return echo.NewHTTPError(http.StatusInternalServerError, "failed to get user icon: "+err.Error())
                }
        }

        return c.Blob(http.StatusOK, "image/jpeg", image)
}
sunakan commented 17 hours ago

全部jpegなのか。。 :thinking:

Nginxで配信するようにして、なかったらupstreamに流すとか、一回これで計測するといいかもしれない

書き込みの際に、DBの方にはINSERT時には空をINSERTするようにして、nginxのrootに置けば良さそう

sunakan commented 17 hours ago

まず変更するべきは

sunakan commented 16 hours ago

そろそろnginxやwebappそのものをGitで管理しようか

sunakan commented 16 hours ago

nginxとwebappを持ってきてgit管理下に加える

sunakan commented 15 hours ago

ハッシュ値計算しているところがあるので、そこは事前にハッシュ値を計算してINSERTしてあげても良さそう

ただ、差分が大きくなるため、INSERTはするがGETでnginxに配信チャレさせるところから

sunakan commented 14 hours ago

Nginx

/etc/nginx/sites-available/isupipe.conf

   location / {
     try_files $uri /index.html;
   }
+  location ~ ^/api/user/(.+)/icon$ {
+    try_files /images/$1.jpeg @api;
+  }
+  location @api {
+    proxy_set_header Host $host;
+    proxy_pass http://localhost:8080;
+  }
   location /api {
     proxy_set_header Host $host;
     proxy_pass http://localhost:8080;

これで、一旦Nginxから配信を試みる（キャッシュはしていない。。はず）

なかった場合はアプリ側へ流す

Golang

/home/isucon/webapp/go/user_handler.go

                        return echo.NewHTTPError(http.StatusInternalServerError, "failed to get user icon: "+err.Error())
                }
        }
+       // 画像をファイルに保存
+       // エラーが発生しても処理を続行し、ログに記録
+       imagePath := filepath.Join("/home/isucon/webapp/public/images", user.Name+".jpeg")
+       if err := os.WriteFile(imagePath, image, 0644); err != nil {
+               log.Printf("Failed to save image for user %s: %v", user.Name, err)
+       }

        return c.Blob(http.StatusOK, "image/jpeg", image)
 }

画像は一度リセットする必要があった

ログリセット時に入れる

@cat tmp/webapp-servers | xargs -I{} ssh {} "rm -rf /home/isucon/webapp/public/images/*"
sunakan commented 14 hours ago 
make rsync-app-and-build-and-restart
sunakan commented 14 hours ago 
make clean-log

の後に計測する

sunakan commented 14 hours ago

スコア：9975

image
sunakan commented 14 hours ago

Before

1位だった

+-------+--------+---------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                  URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+---------------------------------------+--------+--------+--------+---------+
| 4729  | GET    | /api/user/\w+/icon                    | 0.012  | 0.040  | 0.484  | 189.960 |
| 30    | POST   | /api/livestream/\d+/moderate          | 0.124  | 4.110  | 13.040 | 123.308 |

After

5位まで下げることに成功（嬉しい）

+-------+--------+--------------------------------------+--------+--------+--------+---------+
| COUNT | METHOD |                 URI                  |  MIN   |  AVG   |  MAX   |   SUM   |
+-------+--------+--------------------------------------+--------+--------+--------+---------+
| 578   | GET    | /api/livestream/\d+/livecomment      | 0.004  | 0.252  | 1.136  | 145.417 |
| 609   | GET    | /api/livestream/\d+/reaction         | 0.004  | 0.230  | 1.332  | 139.882 |
| 34    | POST   | /api/livestream/\d+/moderate         | 0.188  | 3.536  | 6.985  | 120.239 |
| 627   | POST   | /api/livestream/\d+/livecomment      | 0.004  | 0.153  | 0.632  | 95.801  |
| 5468  | GET    | /api/user/\w+/icon                   | 0.016  | 0.013  | 0.612  | 71.424  |
| 11    | GET    | /api/user/\w+/statistics             | 0.452  | 5.127  | 6.713  | 56.393  |