search

txxxxc / isucon11-qualify

0 stars 0 forks source link

api/trendの速度改善 #28

Closed txxxxc closed 1 month ago

txxxxc commented 2 months ago

api/trendが遅すぎるので改善します まずは実装読みましょう

txxxxc commented 2 months ago 
// 性格のリストを取得(別DBに保存できたら良さそう)(いうてisuの数が少ないから速度改善には寄与しなさそう)
err := db.Select(&characterList, "SELECT `character` FROM `isu` GROUP BY `character`")
txxxxc commented 2 months ago

imageを読み込んだり、余分にselectしてたからそれをなくした。 かなり早くなったんじゃないだろうか

txxxxc commented 2 months ago

だるすぎてハゲそう 16:06:19.231519 ERR: prepare: status code: 期待する HTTP ステータスコード以外が返却されました (expected: 200,304): 500 (GET: /api/trend) 16:06:19.231586 ERR: prepare: critical: アプリケーション互換性チェックに失敗しました

characterが予約後なだけでした

txxxxc commented 2 months ago

まえ2万くらいやから7000くらい上がった気がする

16:25:47.988135 SCORE: 11.ReadCriticalCondition: 38
16:25:47.988398 score: 27366(27368 - 2) : pass
16:25:47.988399 deduction: 0 / timeout: 21
16:25:47.988406 <=== sendResult finish
txxxxc commented 2 months ago

全然あがってないやんけ 16:28:37.729651 SCORE: 08.TodayGraphWorst : 521 16:28:37.729656 SCORE: 09.ReadInfoCondition : 612 16:28:37.729657 SCORE: 10.ReadWarningCondition : 632 16:28:37.729658 SCORE: 11.ReadCriticalCondition: 56 16:28:37.729937 score: 26332(26334 - 2) : pass 16:28:37.729947 deduction: 0 / timeout: 22 16:28:37.729952 <=== sendResult finish

txxxxc commented 2 months ago

なんかめっちゃ遅いかもですｗ

+-------+--------+------------------------------+-------+-------+-------+---------+
| COUNT | METHOD |             URI              |  MIN  |  AVG  |  MAX  |   SUM   |
+-------+--------+------------------------------+-------+-------+-------+---------+
| 415   | GET    | /api/trend                   | 0.018 | 0.940 | 1.008 | 389.945 |
+-------+--------+------------------------------+-------+-------+-------+---------+
txxxxc commented 2 months ago

sumは増えてるけどね

txxxxc commented 2 months ago

characterにindexつけたら多少はマシになるかなぁ

txxxxc commented 2 months ago

これ結局テーブルフルスキャンしてもうてるからやばいのかも "SELECT * FROM isu_condition WHERE jia_isu_uuid = ? ORDER BY timestamp DESC LIMIT 1",

txxxxc commented 2 months ago

てかフルスキャンしてるっぽい感じでした

txxxxc commented 2 months ago

redisを導入することでconditionテーブルのスキャン数をめっちゃ下げた

txxxxc commented 2 months ago

avgは下がったけどまだ一位ではある

+-------+--------+------------------------------+-------+-------+-------+---------+
| COUNT | METHOD |             URI              |  MIN  |  AVG  |  MAX  |   SUM   |
+-------+--------+------------------------------+-------+-------+-------+---------+
| 665   | GET    | /api/trend                   | 0.030 | 0.627 | 1.023 | 416.706 |
| 80494 | POST   | ^/api/condition/[\w\d-]+$    | 0.001 | 0.004 | 0.186 | 349.830 |
| 2012  | GET    | /api/isu                     | 0.001 | 0.091 | 0.467 | 183.446 |
| 5723  | GET    | ^/api/condition/[\w\d-]+$    | 0.004 | 0.031 | 0.201 | 180.024 |
| 1877  | GET    | ^/api/isu/[\w\d-]+/graph$    | 0.001 | 0.039 | 0.220 | 73.480  |
| 2288  | GET    | ^/api/isu/[\w\d-]+/icon$     | 0.001 | 0.023 | 0.180 | 53.328  |
| 2108  | GET    | ^/api/isu/[\w\d-]+$          | 0.001 | 0.020 | 0.198 | 41.569  |
| 1144  | POST   | /api/auth                    | 0.001 | 0.005 | 0.195 | 6.158   |
| 60    | POST   | /api/isu                     | 0.002 | 0.075 | 0.282 | 4.516   |
| 806   | GET    | /api/user/me                 | 0.016 | 0.005 | 0.197 | 4.218   |
| 436   | POST   | /api/signout                 | 0.003 | 0.008 | 0.040 | 3.540   |
| 1114  | GET    | /                            | 0.002 | 0.001 | 0.025 | 1.012   |
| 403   | GET    | /assets/vendor.ee7444dd.js   | 0.000 | 0.000 | 0.030 | 0.199   |
| 1     | POST   | /initialize                  | 0.082 | 0.082 | 0.082 | 0.082   |
| 403   | GET    | /assets/favicon.d0f5f504.svg | 0.000 | 0.000 | 0.030 | 0.065   |
| 403   | GET    | /assets/index.23dac98b.js    | 0.000 | 0.000 | 0.010 | 0.016   |
| 403   | GET    | /assets/index.144d8ca8.css   | 0.000 | 0.000 | 0.010 | 0.014   |
| 18    | GET    | ^/isu/[\w\d-]+/condition$    | 0.000 | 0.000 | 0.001 | 0.008   |
| 3     | GET    | /register                    | 0.001 | 0.001 | 0.001 | 0.003   |
| 6     | GET    | ^/isu/[\w\d-]+$              | 0.000 | 0.000 | 0.001 | 0.002   |
| 6     | GET    | ^/isu/[\w\d-]+/graph$        | 0.001 | 0.000 | 0.001 | 0.002   |
| 402   | GET    | /assets/logo_orange.svg      | 0.000 | 0.000 | 0.000 | 0.000   |
| 403   | GET    | /assets/logo_white.svg       | 0.000 | 0.000 | 0.000 | 0.000   |
+-------+--------+------------------------------+-------+-------+-------+---------+
txxxxc commented 2 months ago 
+-------+--------+------------------------------+-------+-------+-------+---------+
| COUNT | METHOD |             URI              |  MIN  |  AVG  |  MAX  |   SUM   |
+-------+--------+------------------------------+-------+-------+-------+---------+
| 949   | GET    | /api/trend                   | 0.023 | 0.756 | 1.003 | 717.729 |
| 87988 | POST   | ^/api/condition/[\w\d-]+$    | 0.002 | 0.005 | 0.127 | 418.423 |
| 6554  | GET    | ^/api/condition/[\w\d-]+$    | 0.014 | 0.038 | 0.197 | 250.883 |
| 2145  | GET    | ^/api/isu/[\w\d-]+/graph$    | 0.001 | 0.049 | 0.169 | 104.372 |
| 2665  | GET    | /api/isu                     | 0.011 | 0.037 | 0.142 | 98.919  |
| 3029  | GET    | ^/api/isu/[\w\d-]+/icon$     | 0.006 | 0.026 | 0.148 | 79.624  |
| 2743  | GET    | ^/api/isu/[\w\d-]+$          | 0.001 | 0.024 | 0.132 | 65.320  |
| 1222  | POST   | /api/auth                    | 0.001 | 0.006 | 0.073 | 7.893   |
| 73    | POST   | /api/isu                     | 0.003 | 0.090 | 0.216 | 6.580   |
| 1023  | GET    | /api/user/me                 | 0.005 | 0.006 | 0.076 | 6.340   |
| 542   | POST   | /api/signout                 | 0.009 | 0.010 | 0.084 | 5.400   |
| 1506  | GET    | /                            | 0.001 | 0.001 | 0.022 | 1.204   |
| 520   | GET    | /assets/vendor.ee7444dd.js   | 0.000 | 0.000 | 0.013 | 0.183   |
| 1     | POST   | /initialize                  | 0.091 | 0.091 | 0.091 | 0.091   |
| 520   | GET    | /assets/favicon.d0f5f504.svg | 0.000 | 0.000 | 0.010 | 0.027   |
| 18    | GET    | ^/isu/[\w\d-]+/condition$    | 0.000 | 0.000 | 0.001 | 0.008   |
| 520   | GET    | /assets/index.23dac98b.js    | 0.000 | 0.000 | 0.002 | 0.006   |
| 520   | GET    | /assets/index.144d8ca8.css   | 0.000 | 0.000 | 0.004 | 0.004   |
| 6     | GET    | ^/isu/[\w\d-]+$              | 0.000 | 0.001 | 0.001 | 0.003   |
| 6     | GET    | ^/isu/[\w\d-]+/graph$        | 0.000 | 0.001 | 0.001 | 0.003   |
| 3     | GET    | /register                    | 0.001 | 0.001 | 0.001 | 0.002   |
| 519   | GET    | /assets/logo_orange.svg      | 0.000 | 0.000 | 0.000 | 0.000   |
| 520   | GET    | /assets/logo_white.svg       | 0.000 | 0.000 | 0.000 | 0.000   |
+-------+--------+------------------------------+-------+-------+-------+---------+
txxxxc commented 1 month ago

なんかいうて全然早くなってないので、trendの結果自体をcacheしてみようかしら

txxxxc commented 1 month ago

まあ一旦いい感じになったのでclose