スコア記録2 - Githubissues

Nagarei / isucon11q-check

MIT License

0 stars 0 forks source link

スコア記録2 #2

Open Nagarei opened 3 years ago

Nagarei commented 3 years ago

初期ユーザー=1+6 （1+4だともう少しtrend寄り）初期状態

+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| 68041 | POST   | /api/condition/[-a-z0-9]+    | 0.000 | 0.164 | 521.741 | 0.008 | 0.000 |
|   374 | GET    | /api/isu                     | 0.004 | 1.008 | 242.085 | 0.647 | 0.264 |
|   664 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 1.000 | 103.392 | 0.156 | 0.492 |
|  1355 | GET    | /api/isu/.+/icon             | 0.004 | 1.000 |  72.677 | 0.054 | 0.148 |
|    62 | GET    | /api/trend                   | 0.004 | 1.004 |  53.245 | 0.859 | 1.000 |
|   191 | GET    | /api/isu/.+/graph            | 0.004 | 1.000 |  29.753 | 0.156 | 0.424 |
|   219 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.356 |   8.384 | 0.038 | 0.028 |
|    48 | POST   | /api/isu                     | 0.056 | 0.104 |   3.184 | 0.066 | 0.104 |
|    38 | GET    | /api/user/me                 | 0.008 | 0.288 |   1.072 | 0.028 | 0.112 |
|    38 | POST   | /api/auth                    | 0.004 | 0.212 |   1.024 | 0.027 | 0.036 |
|    31 | POST   | /api/signout                 | 0.012 | 0.040 |   0.552 | 0.018 | 0.032 |
|    62 | GET    | /assets/vendor.519e803f.js   | 0.004 | 0.016 |   0.240 | 0.004 | 0.004 |
|    62 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.016 |   0.168 | 0.003 | 0.004 |
|    62 | GET    | /assets/index.b1cd63b1.js    | 0.004 | 0.012 |   0.164 | 0.003 | 0.004 |
|    62 | GET    | /assets/index.ee7b4942.css   | 0.008 | 0.016 |   0.156 | 0.003 | 0.000 |
|   100 | GET    | /                            | 0.000 | 0.012 |   0.112 | 0.001 | 0.000 |
|     1 | POST   | /initialize                  | 0.088 | 0.088 |   0.088 | 0.088 | 0.088 |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
##  2021-08-15 21:49:00.05 +0900
 240 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
 119 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC
  61 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  26 COMMIT
  21 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
  13 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND S <= `timestamp` ORDER BY `timestamp` DESC
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `character` = S
^Cmake: *** [Makefile:46: myprofiler] Interrupt
21:48:59.445229 TIME:
condition: 2451, repair: 676, graph: 1311
21:48:59.457555 <=== LOAD END
21:48:59.457631 SCORE: 00.StartBenchmark       : 1
21:48:59.457637 SCORE: 01.GraphExcellent       : 0
21:48:59.457640 SCORE: 02.GraphGood            : 0
21:48:59.457644 SCORE: 03.GraphNormal          : 0
21:48:59.457648 SCORE: 04.GraphBad             : 0
21:48:59.457652 SCORE: 05.GraphWorst           : 154
21:48:59.457655 SCORE: 06.TodayGraphExcellent  : 0
21:48:59.457658 SCORE: 07.TodayGraphGood       : 0
21:48:59.457662 SCORE: 08.TodayGraphNormal     : 0
21:48:59.457665 SCORE: 09.TodayGraphBad        : 0
21:48:59.457668 SCORE: 10.TodayGraphWorst      : 53
21:48:59.457672 SCORE: 11.ReadInfoCondition    : 50
21:48:59.457675 SCORE: 12.ReadWarningCondition : 125
21:48:59.457678 SCORE: 13.ReadCriticalCondition: 40
21:48:59.457681 SCORE: _1.IsuInitialize        : 48
21:48:59.457685 SCORE: _2.NormalUserInitialize : 7
21:48:59.457688 SCORE: _3.ViewerInitialize     : 21
21:48:59.457692 SCORE: _4.ViewerDropout        : 21
21:48:59.457695 SCORE: _5.RepairIsu            : 43
21:48:59.457699 SCORE: _6.PostInfoCondition    : 1368
21:48:59.457702 SCORE: _7.PostWarningCondition : 4203
21:48:59.457706 SCORE: _8.PostCriticalCondition: 8061
21:48:59.462454 score: 7634(7652 - 18) : pass
21:48:59.462462 deduction: 0 / timeout: 184
21:48:59.462470 <=== sendResult finish

indexを張った状態

+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| 69874 | POST   | /api/condition/[-a-z0-9]+    | 0.004 | 0.188 | 630.600 | 0.009 | 0.100 |
|  6215 | GET    | /api/isu/.+/icon             | 0.004 | 0.328 | 227.580 | 0.037 | 0.056 |
|  3507 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.312 | 204.808 | 0.058 | 0.120 |
|   192 | GET    | /api/trend                   | 0.004 | 1.020 | 177.380 | 0.924 | 1.000 |
|   829 | GET    | /api/isu                     | 0.004 | 0.336 |  46.616 | 0.056 | 0.048 |
|   841 | GET    | /api/isu/.+/graph            | 0.004 | 0.260 |  46.184 | 0.055 | 0.088 |
|   865 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.244 |  25.824 | 0.030 | 0.044 |
|    48 | POST   | /api/isu                     | 0.056 | 0.088 |   3.276 | 0.068 | 0.072 |
|   150 | POST   | /api/auth                    | 0.004 | 0.076 |   2.800 | 0.019 | 0.040 |
|   150 | GET    | /api/user/me                 | 0.004 | 0.116 |   2.348 | 0.016 | 0.012 |
|   143 | POST   | /api/signout                 | 0.004 | 0.140 |   2.296 | 0.016 | 0.024 |
|   342 | GET    | /                            | 0.000 | 0.048 |   0.940 | 0.003 | 0.004 |
|   192 | GET    | /assets/vendor.519e803f.js   | 0.004 | 0.028 |   0.908 | 0.005 | 0.000 |
|   192 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.028 |   0.812 | 0.004 | 0.000 |
|   192 | GET    | /assets/index.b1cd63b1.js    | 0.000 | 0.028 |   0.696 | 0.004 | 0.000 |
|   192 | GET    | /assets/index.ee7b4942.css   | 0.000 | 0.028 |   0.680 | 0.004 | 0.000 |
|     1 | POST   | /initialize                  | 0.096 | 0.096 |   0.096 | 0.096 | 0.096 |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
##  2021-08-15 21:42:41.65 +0900
  67 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC
  26 COMMIT
  23 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  20 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
   7 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   6 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   1 SELECT name FROM `isu` WHERE `jia_isu_uuid` = S AND `jia_user_id` = S
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY `timestamp` DESC LIMIT N
21:38:39.807116 TIME:
condition: 837, repair: 241, graph: 410
21:38:39.818612 <=== LOAD END
21:38:39.818704 SCORE: 00.StartBenchmark       : 1
21:38:39.818714 SCORE: 01.GraphExcellent       : 0
21:38:39.818741 SCORE: 02.GraphGood            : 0
21:38:39.818772 SCORE: 03.GraphNormal          : 0
21:38:39.818793 SCORE: 04.GraphBad             : 0
21:38:39.818801 SCORE: 05.GraphWorst           : 567
21:38:39.818823 SCORE: 06.TodayGraphExcellent  : 0
21:38:39.818830 SCORE: 07.TodayGraphGood       : 0
21:38:39.818851 SCORE: 08.TodayGraphNormal     : 0
21:38:39.818859 SCORE: 09.TodayGraphBad        : 0
21:38:39.818884 SCORE: 10.TodayGraphWorst      : 269
21:38:39.818905 SCORE: 11.ReadInfoCondition    : 386
21:38:39.818915 SCORE: 12.ReadWarningCondition : 553
21:38:39.818940 SCORE: 13.ReadCriticalCondition: 161
21:38:39.818961 SCORE: _1.IsuInitialize        : 48
21:38:39.818968 SCORE: _2.NormalUserInitialize : 7
21:38:39.818971 SCORE: _3.ViewerInitialize     : 21
21:38:39.818991 SCORE: _4.ViewerDropout        : 21
21:38:39.818997 SCORE: _5.RepairIsu            : 227
21:38:39.819018 SCORE: _6.PostInfoCondition    : 4607
21:38:39.819038 SCORE: _7.PostWarningCondition : 6789
21:38:39.819059 SCORE: _8.PostCriticalCondition: 2468
21:38:39.819375 score: 35854(35856 - 2) : pass
21:38:39.819381 deduction: 0 / timeout: 21
21:38:39.819401 <=== sendResult finish

Nagarei commented 3 years ago

index+ trendのlimit1

+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| 75707 | POST   | /api/condition/[-a-z0-9]+    | 0.004 | 0.192 | 501.874 | 0.007 | 0.000 |
|   511 | GET    | /api/trend                   | 0.112 | 1.032 | 410.867 | 0.804 | 1.000 |
| 10448 | GET    | /api/isu/.+/icon             | 0.004 | 0.224 | 283.618 | 0.027 | 0.048 |
|  4551 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.208 | 209.144 | 0.046 | 0.040 |
|  1539 | GET    | /api/isu                     | 0.004 | 0.180 |  66.873 | 0.043 | 0.136 |
|  1150 | GET    | /api/isu/.+/graph            | 0.008 | 0.168 |  48.756 | 0.042 | 0.044 |
|  1567 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.268 |  36.889 | 0.024 | 0.036 |
|   302 | GET    | /api/user/me                 | 0.004 | 0.196 |   4.548 | 0.015 | 0.004 |
|   302 | POST   | /api/auth                    | 0.004 | 0.064 |   4.440 | 0.015 | 0.008 |
|   293 | POST   | /api/signout                 | 0.024 | 0.100 |   3.700 | 0.013 | 0.004 |
|    53 | POST   | /api/isu                     | 0.056 | 0.108 |   3.632 | 0.069 | 0.100 |
|   511 | GET    | /assets/vendor.519e803f.js   | 0.000 | 0.032 |   1.596 | 0.003 | 0.004 |
|   813 | GET    | /                            | 0.000 | 0.020 |   1.396 | 0.002 | 0.004 |
|   511 | GET    | /assets/favicon.d0f5f504.svg | 0.000 | 0.028 |   1.372 | 0.003 | 0.004 |
|   511 | GET    | /assets/index.b1cd63b1.js    | 0.004 | 0.028 |   1.340 | 0.003 | 0.004 |
|   511 | GET    | /assets/index.ee7b4942.css   | 0.004 | 0.028 |   1.320 | 0.003 | 0.004 |
|     1 | POST   | /initialize                  | 0.120 | 0.120 |   0.120 | 0.120 | 0.120 |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+

##  2021-08-15 22:05:16.36 +0900
  32 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  27 COMMIT
  18 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  13 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   4 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   4 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
   3 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   2 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = S
   1 SELECT name FROM `isu` WHERE `jia_isu_uuid` = ? AND `jia_user_id` = ?
22:05:14.278751 LOAD WAIT
22:05:14.279207 --- userAdder END
22:05:14.288930 TIME:
condition: 477, repair: 185, graph: 275
22:05:14.306262 <=== LOAD END
22:05:14.306320 SCORE: 00.StartBenchmark       : 1
22:05:14.306329 SCORE: 01.GraphExcellent       : 0
22:05:14.306336 SCORE: 02.GraphGood            : 0
22:05:14.306342 SCORE: 03.GraphNormal          : 0
22:05:14.306348 SCORE: 04.GraphBad             : 0
22:05:14.306355 SCORE: 05.GraphWorst           : 533
22:05:14.306362 SCORE: 06.TodayGraphExcellent  : 0
22:05:14.306365 SCORE: 07.TodayGraphGood       : 0
22:05:14.306377 SCORE: 08.TodayGraphNormal     : 0
22:05:14.306385 SCORE: 09.TodayGraphBad        : 0
22:05:14.306396 SCORE: 10.TodayGraphWorst      : 502
22:05:14.306407 SCORE: 11.ReadInfoCondition    : 585
22:05:14.306414 SCORE: 12.ReadWarningCondition : 618
22:05:14.306424 SCORE: 13.ReadCriticalCondition: 61
22:05:14.306432 SCORE: _1.IsuInitialize        : 53
22:05:14.306443 SCORE: _2.NormalUserInitialize : 9
22:05:14.306450 SCORE: _3.ViewerInitialize     : 27
22:05:14.306461 SCORE: _4.ViewerDropout        : 27
22:05:14.306468 SCORE: _5.RepairIsu            : 339
22:05:14.306478 SCORE: _6.PostInfoCondition    : 6603
22:05:14.306485 SCORE: _7.PostWarningCondition : 7512
22:05:14.306496 SCORE: _8.PostCriticalCondition: 951
22:05:14.306899 score: 44706(44708 - 2) : pass
22:05:14.306906 deduction: 0 / timeout: 27
22:05:14.306923 <=== sendResult finish

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   5663 mysql     20   0 1716192 214312  19052 S  67.0   5.7   6:25.58 mysqld
   6543 isucon    20   0 1451896  20724   7776 S  65.7   0.5   0:07.22 isucondition
   2370 www-data  20   0   10660   7508   5036 R  17.0   0.2   1:26.03 nginx
    165 root      19  -1  394456 271120 269960 S  16.0   7.2   1:24.25 systemd-journal
   2371 www-data  20   0   10460   7580   5036 R  13.3   0.2   1:03.00 nginx
    504 syslog    20   0  224508   5808   3916 S   8.3   0.2   0:48.95 rsyslogd

Nagarei commented 3 years ago

systemd-journalが上にいるのでログを切る

+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
|   686 | GET    | /api/trend                   | 0.004 | 1.004 | 555.376 | 0.810 | 1.000 |
| 82711 | POST   | /api/condition/[-a-z0-9]+    | 0.008 | 0.120 | 441.071 | 0.005 | 0.004 |
| 11127 | GET    | /api/isu/.+/icon             | 0.004 | 0.232 | 293.668 | 0.026 | 0.040 |
|  5055 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.280 | 239.694 | 0.047 | 0.200 |
|  1691 | GET    | /api/isu                     | 0.004 | 0.200 |  67.665 | 0.040 | 0.040 |
|  1338 | GET    | /api/isu/.+/graph            | 0.004 | 0.212 |  56.193 | 0.042 | 0.024 |
|  1722 | GET    | /api/isu/[-a-z0-9]+          | 0.008 | 0.196 |  38.577 | 0.022 | 0.028 |
|   360 | POST   | /api/auth                    | 0.008 | 0.088 |   5.260 | 0.015 | 0.004 |
|   360 | GET    | /api/user/me                 | 0.004 | 0.100 |   5.044 | 0.014 | 0.048 |
|   350 | POST   | /api/signout                 | 0.004 | 0.124 |   4.500 | 0.013 | 0.084 |
|    60 | POST   | /api/isu                     | 0.056 | 0.140 |   4.364 | 0.073 | 0.108 |
|   686 | GET    | /assets/vendor.519e803f.js   | 0.000 | 0.028 |   1.148 | 0.002 | 0.004 |
|   686 | GET    | /assets/favicon.d0f5f504.svg | 0.000 | 0.016 |   0.912 | 0.001 | 0.004 |
|   686 | GET    | /assets/index.b1cd63b1.js    | 0.000 | 0.016 |   0.904 | 0.001 | 0.004 |
|  1046 | GET    | /                            | 0.000 | 0.016 |   0.780 | 0.001 | 0.000 |
|   686 | GET    | /assets/index.ee7b4942.css   | 0.000 | 0.016 |   0.772 | 0.001 | 0.004 |
|     1 | POST   | /initialize                  | 0.088 | 0.088 |   0.088 | 0.088 | 0.088 |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
condition: 490, repair: 181, graph: 276
22:11:49.654298 <=== LOAD END
22:11:49.654372 SCORE: 00.StartBenchmark       : 1
22:11:49.654381 SCORE: 01.GraphExcellent       : 0
22:11:49.654434 SCORE: 02.GraphGood            : 0
22:11:49.654446 SCORE: 03.GraphNormal          : 0
22:11:49.654494 SCORE: 04.GraphBad             : 0
22:11:49.654501 SCORE: 05.GraphWorst           : 562
22:11:49.654544 SCORE: 06.TodayGraphExcellent  : 0
22:11:49.654555 SCORE: 07.TodayGraphGood       : 0
22:11:49.654598 SCORE: 08.TodayGraphNormal     : 0
22:11:49.654610 SCORE: 09.TodayGraphBad        : 0
22:11:49.654697 SCORE: 10.TodayGraphWorst      : 548
22:11:49.654718 SCORE: 11.ReadInfoCondition    : 645
22:11:49.654738 SCORE: 12.ReadWarningCondition : 687
22:11:49.654760 SCORE: 13.ReadCriticalCondition: 80
22:11:49.654779 SCORE: _1.IsuInitialize        : 60
22:11:49.654801 SCORE: _2.NormalUserInitialize : 10
22:11:49.654820 SCORE: _3.ViewerInitialize     : 30
22:11:49.654854 SCORE: _4.ViewerDropout        : 30
22:11:49.654861 SCORE: _5.RepairIsu            : 394
22:11:49.654882 SCORE: _6.PostInfoCondition    : 7110
22:11:49.654902 SCORE: _7.PostWarningCondition : 8248
22:11:49.654923 SCORE: _8.PostCriticalCondition: 1114
22:11:49.655355 score: 49149(49152 - 3) : pass
22:11:49.655361 deduction: 0 / timeout: 30

Nagarei commented 3 years ago

二台構成 benchは詰まってない dstatの余りはsql30のapp15～20くらい。

+--------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT  | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+--------+--------+------------------------------+-------+-------+---------+-------+-------+
|   1297 | GET    | /api/trend                   | 0.004 | 1.008 | 873.890 | 0.674 | 0.924 |
| 102213 | POST   | /api/condition/[-a-z0-9]+    | 0.004 | 0.104 | 450.083 | 0.004 | 0.000 |
|  22366 | GET    | /api/isu/.+/icon             | 0.004 | 0.248 | 443.345 | 0.020 | 0.044 |
|   7696 | GET    | /api/condition/[-a-z0-9]+    | 0.016 | 0.232 | 239.961 | 0.031 | 0.036 |
|   3235 | GET    | /api/isu                     | 0.004 | 0.212 |  90.138 | 0.028 | 0.064 |
|   2321 | GET    | /api/isu/.+/graph            | 0.000 | 0.172 |  62.068 | 0.027 | 0.028 |
|   3248 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.172 |  55.776 | 0.017 | 0.008 |
|    605 | POST   | /api/auth                    | 0.004 | 0.076 |   7.236 | 0.012 | 0.016 |
|    605 | GET    | /api/user/me                 | 0.012 | 0.108 |   6.197 | 0.010 | 0.012 |
|    593 | POST   | /api/signout                 | 0.004 | 0.080 |   5.429 | 0.009 | 0.004 |
|     74 | POST   | /api/isu                     | 0.052 | 0.120 |   4.964 | 0.067 | 0.060 |
|   1297 | GET    | /assets/vendor.519e803f.js   | 0.000 | 0.020 |   2.688 | 0.002 | 0.000 |
|   1297 | GET    | /assets/index.b1cd63b1.js    | 0.008 | 0.020 |   2.224 | 0.002 | 0.004 |
|   1297 | GET    | /assets/favicon.d0f5f504.svg | 0.008 | 0.024 |   2.164 | 0.002 | 0.000 |
|   1297 | GET    | /assets/index.ee7b4942.css   | 0.008 | 0.016 |   2.012 | 0.002 | 0.000 |
|   1902 | GET    | /                            | 0.004 | 0.016 |   1.960 | 0.001 | 0.000 |
|      1 | POST   | /initialize                  | 0.100 | 0.100 |   0.100 | 0.100 | 0.100 |
+--------+--------+------------------------------+-------+-------+---------+-------+-------+
##  2021-08-15 23:25:21.25 +0900
  27 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
  24 COMMIT
  23 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  10 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
   7 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   4 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   2 SELECT * FROM `isu` WHERE `character` = S
   2 SELECT name FROM `isu` WHERE `jia_isu_uuid` = S AND `jia_user_id` = S
   1 UPDATE `isu` SET `character` = ? WHERE `jia_isu_uuid` = ?
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `character` = ?
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ?     AND `timestamp` < ?     ORDER BY `timestamp` DESC
23:37:39.896648 TIME:
condition: 275, repair: 133, graph: 189
23:37:39.913204 <=== LOAD END
23:37:39.913259 SCORE: 00.StartBenchmark       : 1
23:37:39.913267 SCORE: 01.GraphExcellent       : 0
23:37:39.913274 SCORE: 02.GraphGood            : 0
23:37:39.913280 SCORE: 03.GraphNormal          : 0
23:37:39.913287 SCORE: 04.GraphBad             : 0
23:37:39.913293 SCORE: 05.GraphWorst           : 555
23:37:39.913299 SCORE: 06.TodayGraphExcellent  : 0
23:37:39.913305 SCORE: 07.TodayGraphGood       : 0
23:37:39.913317 SCORE: 08.TodayGraphNormal     : 0
23:37:39.913324 SCORE: 09.TodayGraphBad        : 0
23:37:39.913330 SCORE: 10.TodayGraphWorst      : 1052
23:37:39.913343 SCORE: 11.ReadInfoCondition    : 1036
23:37:39.913349 SCORE: 12.ReadWarningCondition : 837
23:37:39.913356 SCORE: 13.ReadCriticalCondition: 42
23:37:39.913369 SCORE: _1.IsuInitialize        : 74
23:37:39.913375 SCORE: _2.NormalUserInitialize : 12
23:37:39.913381 SCORE: _3.ViewerInitialize     : 36
23:37:39.913394 SCORE: _4.ViewerDropout        : 36
23:37:39.913401 SCORE: _5.RepairIsu            : 657
23:37:39.913413 SCORE: _6.PostInfoCondition    : 10858
23:37:39.913420 SCORE: _7.PostWarningCondition : 9121
23:37:39.913431 SCORE: _8.PostCriticalCondition: 434
23:37:39.913935 score: 69355(69358 - 3) : pass
23:37:39.913941 deduction: 0 / timeout: 36
23:37:39.913949 <=== sendResult finish

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   7561 isucon    20   0 1304932  25396   7648 S 127.2   0.7   1:59.31 isucondition
   2370 www-data  20   0   12512   9632   5036 S  25.2   0.3   2:30.01 nginx
   2371 www-data  20   0   12920  10040   5036 S  19.3   0.3   1:58.18 nginx

Nagarei commented 3 years ago

二台構成 + trendのcache 負荷増加に耐えきれていないように見える特にnginx
nginx 100 / app 100 / sql 170

+--------+--------+------------------------------+-------+-------+----------+-------+-------+
| COUNT  | METHOD |             URI              |  MIN  |  MAX  |   SUM    |  AVG  |  P99  |
+--------+--------+------------------------------+-------+-------+----------+-------+-------+
| 118209 | POST   | /api/condition/[-a-z0-9]+    | 0.004 | 0.216 | 2100.112 | 0.018 | 0.020 |
|  35670 | GET    | /assets/vendor.519e803f.js   | 0.008 | 0.184 |  415.626 | 0.012 | 0.016 |
|  35670 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.176 |  410.890 | 0.012 | 0.016 |
|  35670 | GET    | /assets/index.b1cd63b1.js    | 0.004 | 0.176 |  410.821 | 0.012 | 0.016 |
|   4375 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.992 |  397.676 | 0.091 | 0.632 |
|   8495 | GET    | /api/isu/.+/icon             | 0.004 | 0.724 |  397.380 | 0.047 | 0.308 |
|  35670 | GET    | /assets/index.ee7b4942.css   | 0.008 | 0.184 |  390.560 | 0.011 | 0.016 |
|  35643 | GET    | /api/trend                   | 0.004 | 0.176 |  383.255 | 0.011 | 0.016 |
|  35984 | GET    | /                            | 0.004 | 0.184 |  382.352 | 0.011 | 0.016 |
|   1243 | GET    | /api/isu/.+/graph            | 0.004 | 0.748 |   98.928 | 0.080 | 0.392 |
|   1371 | GET    | /api/isu                     | 0.004 | 0.612 |   85.846 | 0.063 | 0.056 |
|   1431 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.756 |   68.390 | 0.048 | 0.084 |
|    122 | POST   | /api/isu                     | 0.052 | 0.444 |   13.336 | 0.109 | 0.324 |
|    300 | GET    | /api/user/me                 | 0.004 | 0.336 |    9.900 | 0.033 | 0.032 |
|    300 | POST   | /api/auth                    | 0.004 | 0.252 |    9.624 | 0.032 | 0.024 |
|    277 | POST   | /api/signout                 | 0.004 | 0.344 |    8.245 | 0.030 | 0.080 |
|      1 | POST   | /initialize                  | 0.108 | 0.108 |    0.108 | 0.108 | 0.108 |
+--------+--------+------------------------------+-------+-------+----------+-------+-------+
##  2021-08-16 00:02:24.43 +0900
   7 COMMIT
   3 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
   3 SELECT * FROM `isu` WHERE `character` = S
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   1 SELECT name FROM `isu` WHERE `jia_isu_uuid` = S AND `jia_user_id` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ?     AND `timestamp` < ?     ORDER BY `timestamp` DESC
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   1 INSERT IGNORE INTO user (`jia_user_id`) VALUES (?)
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
00:03:02.889690 TIME:
condition: 1191, repair: 371, graph: 658
00:03:02.901841 <=== LOAD END
00:03:02.902001 SCORE: 00.StartBenchmark       : 1
00:03:02.902063 SCORE: 01.GraphExcellent       : 0
00:03:02.902113 SCORE: 02.GraphGood            : 0
00:03:02.902170 SCORE: 03.GraphNormal          : 0
00:03:02.902221 SCORE: 04.GraphBad             : 0
00:03:02.902258 SCORE: 05.GraphWorst           : 775
00:03:02.902292 SCORE: 06.TodayGraphExcellent  : 0
00:03:02.902325 SCORE: 07.TodayGraphGood       : 0
00:03:02.902359 SCORE: 08.TodayGraphNormal     : 0
00:03:02.902393 SCORE: 09.TodayGraphBad        : 0
00:03:02.902426 SCORE: 10.TodayGraphWorst      : 426
00:03:02.902460 SCORE: 11.ReadInfoCondition    : 428
00:03:02.902493 SCORE: 12.ReadWarningCondition : 629
00:03:02.902527 SCORE: 13.ReadCriticalCondition: 195
00:03:02.902561 SCORE: _1.IsuInitialize        : 122
00:03:02.902594 SCORE: _2.NormalUserInitialize : 23
00:03:02.902628 SCORE: _3.ViewerInitialize     : 69
00:03:02.902681 SCORE: _4.ViewerDropout        : 0
00:03:02.902715 SCORE: _5.RepairIsu            : 308
00:03:02.902748 SCORE: _6.PostInfoCondition    : 6032
00:03:02.902783 SCORE: _7.PostWarningCondition : 11458
00:03:02.902818 SCORE: _8.PostCriticalCondition: 6537
00:03:02.902912 score: 42104(42104 - 0) : pass
00:03:02.902947 deduction: 0 / timeout: 2
00:03:02.902984 <=== sendResult finish

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   8973 isucon    20   0 1452644  22012   8344 S 104.0   0.6   4:36.03 isucondition
   2370 www-data  20   0   12824   9560   4652 R  42.3   0.3   3:26.74 nginx
   2371 www-data  20   0   12884   9620   4652 R  41.7   0.3   2:50.36 nginx

バルクインサートと、limitの無いselectかな

Nagarei commented 3 years ago

ベンチ詰まってる

Nagarei commented 3 years ago

viewerは最遅で70msくらい

Nagarei commented 3 years ago

benchを軽くした

+--------+--------+------------------------------+-------+-------+----------+-------+-------+
| COUNT  | METHOD |             URI              |  MIN  |  MAX  |   SUM    |  AVG  |  P99  |
+--------+--------+------------------------------+-------+-------+----------+-------+-------+
| 107249 | POST   | /api/condition/[-a-z0-9]+    | 0.000 | 0.128 | 1929.643 | 0.018 | 0.016 |
|  50693 | GET    | /assets/vendor.519e803f.js   | 0.016 | 0.068 |  567.204 | 0.011 | 0.012 |
|  50693 | GET    | /assets/index.b1cd63b1.js    | 0.000 | 0.068 |  565.247 | 0.011 | 0.008 |
|  50694 | GET    | /assets/favicon.d0f5f504.svg | 0.016 | 0.068 |  564.754 | 0.011 | 0.016 |
|  50680 | GET    | /api/trend                   | 0.004 | 0.068 |  554.279 | 0.011 | 0.008 |
|  50694 | GET    | /assets/index.ee7b4942.css   | 0.016 | 0.068 |  553.825 | 0.011 | 0.016 |
|  50872 | GET    | /                            | 0.016 | 0.056 |  541.583 | 0.011 | 0.008 |
|   3036 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 1.004 |  373.315 | 0.123 | 0.260 |
|   5366 | GET    | /api/isu/.+/icon             | 0.004 | 0.996 |  342.024 | 0.064 | 0.156 |
|    855 | GET    | /api/isu/.+/graph            | 0.008 | 1.000 |   98.279 | 0.115 | 0.032 |
|    831 | GET    | /api/isu                     | 0.004 | 0.836 |   68.925 | 0.083 | 0.088 |
|    876 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.872 |   54.528 | 0.062 | 0.080 |
|     87 | POST   | /api/isu                     | 0.056 | 0.364 |    8.496 | 0.098 | 0.248 |
|    160 | POST   | /api/auth                    | 0.004 | 1.000 |    7.024 | 0.044 | 0.280 |
|    159 | GET    | /api/user/me                 | 0.008 | 0.252 |    6.140 | 0.039 | 0.156 |
|    143 | POST   | /api/signout                 | 0.004 | 0.300 |    5.176 | 0.036 | 0.176 |
|      1 | POST   | /initialize                  | 0.108 | 0.108 |    0.108 | 0.108 | 0.108 |
+--------+--------+------------------------------+-------+-------+----------+-------+-------+
10:51:15.392208 TIME:
condition: 1604, repair: 418, graph: 851, viewer: 46
10:51:15.439727 <=== LOAD END
10:51:15.439795 SCORE: 00.StartBenchmark       : 1
10:51:15.439805 SCORE: 01.GraphExcellent       : 0
10:51:15.439812 SCORE: 02.GraphGood            : 0
10:51:15.439820 SCORE: 03.GraphNormal          : 0
10:51:15.439826 SCORE: 04.GraphBad             : 0
10:51:15.439833 SCORE: 05.GraphWorst           : 568
10:51:15.439840 SCORE: 06.TodayGraphExcellent  : 0
10:51:15.439845 SCORE: 07.TodayGraphGood       : 0
10:51:15.439849 SCORE: 08.TodayGraphNormal     : 0
10:51:15.439853 SCORE: 09.TodayGraphBad        : 0
10:51:15.439859 SCORE: 10.TodayGraphWorst      : 257
10:51:15.439865 SCORE: 11.ReadInfoCondition    : 271
10:51:15.439871 SCORE: 12.ReadWarningCondition : 461
10:51:15.439877 SCORE: 13.ReadCriticalCondition: 180
10:51:15.439884 SCORE: _1.IsuInitialize        : 87
10:51:15.439887 SCORE: _2.NormalUserInitialize : 16
10:51:15.439893 SCORE: _3.ViewerInitialize     : 48
10:51:15.439900 SCORE: _4.ViewerDropout        : 0
10:51:15.439906 SCORE: _5.RepairIsu            : 191
10:51:15.439912 SCORE: _6.PostInfoCondition    : 4290
10:51:15.439918 SCORE: _7.PostWarningCondition : 9580
10:51:15.439922 SCORE: _8.PostCriticalCondition: 7465
10:51:15.440006 score: 29568(29568 - 0) : pass
10:51:15.440012 deduction: 0 / timeout: 4
10:51:15.440024 <=== sendResult finish

Nagarei commented 3 years ago

post conditionを二台目に向ける

+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM   |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
| 63011 | GET    | /assets/vendor.519e803f.js   | 0.000 | 0.076 | 585.988 | 0.009 | 0.004 |
| 63011 | GET    | /assets/index.b1cd63b1.js    | 0.004 | 0.076 | 584.244 | 0.009 | 0.004 |
| 63011 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.076 | 582.096 | 0.009 | 0.004 |
| 62993 | GET    | /api/trend                   | 0.000 | 0.068 | 556.932 | 0.009 | 0.008 |
| 63011 | GET    | /assets/index.ee7b4942.css   | 0.000 | 0.072 | 554.380 | 0.009 | 0.004 |
| 63344 | GET    | /                            | 0.000 | 0.076 | 533.820 | 0.008 | 0.008 |
|  4592 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.584 | 387.919 | 0.084 | 0.344 |
|  8881 | GET    | /api/isu/.+/icon             | 0.004 | 0.376 | 382.122 | 0.043 | 0.040 |
|  1300 | GET    | /api/isu/.+/graph            | 0.004 | 0.468 | 122.018 | 0.094 | 0.196 |
|  1478 | GET    | /api/isu                     | 0.004 | 0.460 | 120.625 | 0.082 | 0.124 |
|  1504 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.364 |  60.884 | 0.040 | 0.052 |
|   108 | POST   | /api/isu                     | 0.056 | 0.292 |  11.032 | 0.102 | 0.132 |
|   310 | POST   | /api/auth                    | 0.004 | 0.152 |   8.972 | 0.029 | 0.044 |
|   310 | GET    | /api/user/me                 | 0.004 | 0.176 |   7.696 | 0.025 | 0.012 |
|   289 | POST   | /api/signout                 | 0.012 | 0.268 |   7.636 | 0.026 | 0.100 |
|     1 | POST   | /initialize                  | 0.104 | 0.104 |   0.104 | 0.104 | 0.104 |
+-------+--------+------------------------------+-------+-------+---------+-------+-------+
##  2021-08-16 10:55:50.32 +0900
  20 COMMIT
  16 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  12 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
   4 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
   1 SELECT * FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 START TRANSACTION
   1 SELECT * FROM `isu` WHERE `character` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = S
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   1 SELECT * FROM `isu` WHERE `jia_user_id` = S ORDER BY `id` DESC
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
10:56:25.950985 TIME:
condition: 974, repair: 347, graph: 612, viewer: 46
10:56:25.969378 --- userAdder END
10:56:25.969519 LOAD WAIT
10:56:25.981894 <=== LOAD END
10:56:25.982033 SCORE: 00.StartBenchmark       : 1
10:56:25.982086 SCORE: 01.GraphExcellent       : 0
10:56:25.982138 SCORE: 02.GraphGood            : 0
10:56:25.982148 SCORE: 03.GraphNormal          : 0
10:56:25.982156 SCORE: 04.GraphBad             : 0
10:56:25.982164 SCORE: 05.GraphWorst           : 774
10:56:25.982171 SCORE: 06.TodayGraphExcellent  : 0
10:56:25.982183 SCORE: 07.TodayGraphGood       : 0
10:56:25.982195 SCORE: 08.TodayGraphNormal     : 0
10:56:25.982208 SCORE: 09.TodayGraphBad        : 0
10:56:25.982220 SCORE: 10.TodayGraphWorst      : 454
10:56:25.982228 SCORE: 11.ReadInfoCondition    : 481
10:56:25.982245 SCORE: 12.ReadWarningCondition : 676
10:56:25.982252 SCORE: 13.ReadCriticalCondition: 161
10:56:25.982259 SCORE: _1.IsuInitialize        : 108
10:56:25.982266 SCORE: _2.NormalUserInitialize : 21
10:56:25.982273 SCORE: _3.ViewerInitialize     : 63
10:56:25.982281 SCORE: _4.ViewerDropout        : 0
10:56:25.982285 SCORE: _5.RepairIsu            : 337
10:56:25.982288 SCORE: _6.PostInfoCondition    : 6304
10:56:25.982296 SCORE: _7.PostWarningCondition : 10453
10:56:25.982304 SCORE: _8.PostCriticalCondition: 5590
10:56:25.982316 score: 44926(44926 - 0) : pass
10:56:25.982322 deduction: 0 / timeout: 0
10:56:25.982335 <=== sendResult finish

app
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  12002 isucon    20   0 1378656  25420   8284 S  97.3   0.7   0:52.38 isucondition
   2370 www-data  20   0   13716  10480   4680 S  40.7   0.3   5:46.95 nginx
   2371 www-data  20   0   12944   9680   4652 S  38.0   0.3   5:09.35 nginx

mysql+post condition（CPU使い切った）
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1327 mysql     20   0 1717876 239476  20156 S  63.0   6.3 218:26.65 mysqld
    709 isucon    20   0 1377396  15172   7492 R  44.0   0.4   0:17.38 isucondition
    507 www-data  20   0   10884   7692   5032 R  32.3   0.2   0:06.95 nginx
    508 www-data  20   0   10896   7832   5032 R  29.0   0.2   0:04.78 nginx
    164 root      19  -1  145632  94644  93568 R  21.0   2.5   0:06.08 systemd-journal

Nagarei commented 3 years ago

二台目の3000番に向ける

+-------+--------+------------------------------+-------+-------+----------+-------+-------+
| COUNT | METHOD |             URI              |  MIN  |  MAX  |   SUM    |  AVG  |  P99  |
+-------+--------+------------------------------+-------+-------+----------+-------+-------+
| 52812 | GET    | /assets/vendor.519e803f.js   | 0.004 | 0.136 | 1618.823 | 0.031 | 0.040 |
| 26406 | GET    | /assets/index.ee7b4942.css   | 0.004 | 0.136 |  813.905 | 0.031 | 0.040 |
| 26406 | GET    | /assets/favicon.d0f5f504.svg | 0.004 | 0.136 |  813.104 | 0.031 | 0.048 |
| 26406 | GET    | /assets/index.b1cd63b1.js    | 0.004 | 0.136 |  812.590 | 0.031 | 0.040 |
| 26390 | GET    | /api/trend                   | 0.004 | 0.128 |  795.887 | 0.030 | 0.052 |
| 26664 | GET    | /                            | 0.004 | 0.152 |  779.407 | 0.029 | 0.036 |
|  3933 | GET    | /api/condition/[-a-z0-9]+    | 0.004 | 0.632 |  418.112 | 0.106 | 0.128 |
|  5714 | GET    | /api/isu/.+/icon             | 0.004 | 0.428 |  400.355 | 0.070 | 0.116 |
|  1009 | GET    | /api/isu/.+/graph            | 0.008 | 0.388 |  116.036 | 0.115 | 0.172 |
|   946 | GET    | /api/isu                     | 0.004 | 0.516 |  109.928 | 0.116 | 0.196 |
|  1008 | GET    | /api/isu/[-a-z0-9]+          | 0.004 | 0.288 |   61.598 | 0.061 | 0.164 |
|   108 | POST   | /api/isu                     | 0.056 | 0.316 |   12.968 | 0.120 | 0.264 |
|   225 | POST   | /api/auth                    | 0.004 | 0.212 |   12.044 | 0.054 | 0.116 |
|   225 | GET    | /api/user/me                 | 0.004 | 0.184 |   10.821 | 0.048 | 0.040 |
|   204 | POST   | /api/signout                 | 0.012 | 0.188 |   10.100 | 0.050 | 0.076 |
|     1 | POST   | /initialize                  | 0.136 | 0.136 |    0.136 | 0.136 | 0.136 |
+-------+--------+------------------------------+-------+-------+----------+-------+-------+
##  2021-08-16 13:32:59.15 +0900
  18 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  11 COMMIT
   9 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
   4 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   2 START TRANSACTION
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   1 SELECT * FROM `isu` WHERE `character` = S
^Cmake: *** [Makefile:46: myprofiler] Interrupt
13:33:13.172975 TIME:
condition: 1604, repair: 458, graph: 862, viewer: 107
13:33:13.192654 <=== LOAD END
13:33:13.192713 SCORE: 00.StartBenchmark       : 1
13:33:13.192722 SCORE: 01.GraphExcellent       : 0
13:33:13.192729 SCORE: 02.GraphGood            : 0
13:33:13.192735 SCORE: 03.GraphNormal          : 0
13:33:13.192742 SCORE: 04.GraphBad             : 0
13:33:13.192750 SCORE: 05.GraphWorst           : 672
13:33:13.192756 SCORE: 06.TodayGraphExcellent  : 0
13:33:13.192767 SCORE: 07.TodayGraphGood       : 0
13:33:13.192774 SCORE: 08.TodayGraphNormal     : 0
13:33:13.192786 SCORE: 09.TodayGraphBad        : 0
13:33:13.192792 SCORE: 10.TodayGraphWorst      : 288
13:33:13.192803 SCORE: 11.ReadInfoCondition    : 337
13:33:13.192809 SCORE: 12.ReadWarningCondition : 667
13:33:13.192822 SCORE: 13.ReadCriticalCondition: 224
13:33:13.192827 SCORE: _1.IsuInitialize        : 108
13:33:13.192834 SCORE: _2.NormalUserInitialize : 21
13:33:13.192840 SCORE: _3.ViewerInitialize     : 63
13:33:13.192853 SCORE: _4.ViewerDropout        : 0
13:33:13.192860 SCORE: _5.RepairIsu            : 239
13:33:13.192863 SCORE: _6.PostInfoCondition    : 5465
13:33:13.192875 SCORE: _7.PostWarningCondition : 11773
13:33:13.192882 SCORE: _8.PostCriticalCondition: 8403
13:33:13.192894 score: 37932(37932 - 0) : pass
13:33:13.192900 deduction: 0 / timeout: 0

app
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  14197 isucon    20   0 1305280  22484   8560 R 100.0   0.6   0:26.95 isucondition
  14326 www-data  20   0   14772  11944   5084 R  45.8   0.3   0:12.01 nginx
  14325 www-data  20   0   14500  11372   5084 R  43.5   0.3   0:11.80 nginx

Nagarei commented 3 years ago

1台目のnginxを落とす benchとdbが詰まる。 appはOK

##  2021-08-16 13:55:45.55 +0900
  68 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  58 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  49 COMMIT
  14 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   6 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   5 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   5 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
   3 SELECT `image` FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND S <= `timestamp` ORDER BY `timestamp` DESC
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY `timestamp` ASC
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ?     AND `timestamp` < ?     AND ? <= `timestamp` ORDER BY `timestamp` DESC
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY `timestamp` DESC LIMIT N
   1 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
13:55:10.031212 LOAD WAIT
13:55:10.031613 TIME:
condition: 686, repair: 249, graph: 418, viewer: 55
13:55:10.110735 <=== LOAD END
13:55:10.110807 SCORE: 00.StartBenchmark       : 1
13:55:10.110818 SCORE: 01.GraphExcellent       : 0
13:55:10.110826 SCORE: 02.GraphGood            : 0
13:55:10.110833 SCORE: 03.GraphNormal          : 0
13:55:10.110839 SCORE: 04.GraphBad             : 0
13:55:10.110846 SCORE: 05.GraphWorst           : 1111
13:55:10.110853 SCORE: 06.TodayGraphExcellent  : 0
13:55:10.110859 SCORE: 07.TodayGraphGood       : 0
13:55:10.110863 SCORE: 08.TodayGraphNormal     : 0
13:55:10.110869 SCORE: 09.TodayGraphBad        : 0
13:55:10.110876 SCORE: 10.TodayGraphWorst      : 938
13:55:10.110882 SCORE: 11.ReadInfoCondition    : 843
13:55:10.110888 SCORE: 12.ReadWarningCondition : 1048
13:55:10.110896 SCORE: 13.ReadCriticalCondition: 226
13:55:10.110900 SCORE: _1.IsuInitialize        : 179
13:55:10.110906 SCORE: _2.NormalUserInitialize : 32
13:55:10.110911 SCORE: _3.ViewerInitialize     : 96
13:55:10.110919 SCORE: _4.ViewerDropout        : 9
13:55:10.110925 SCORE: _5.RepairIsu            : 597
13:55:10.110932 SCORE: _6.PostInfoCondition    : 11414
13:55:10.110939 SCORE: _7.PostWarningCondition : 16856
13:55:10.110945 SCORE: _8.PostCriticalCondition: 7545
13:55:10.111159 score: 72801(72802 - 1) : pass
13:55:10.111165 deduction: 0 / timeout: 14
13:55:10.111184 <=== sendResult finish

db
    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1327 mysql     20   0 1730428 323848  20272 S 116.6   8.6 350:08.53 mysqld
   5917 isucon    20   0 1446860  72508   8240 R  78.1   1.9   2:32.70 isucondition

Nagarei commented 3 years ago

ちなみに初期データ戻しても bench側は80%くらいCPUを使っている（アプリ側にネックが移動するけど、trendかな）

condition: 191, repair: 109, graph: 148, viewer: 28
14:12:05.527243 <=== LOAD END
14:12:05.527324 SCORE: 00.StartBenchmark       : 1
14:12:05.527334 SCORE: 01.GraphExcellent       : 0
14:12:05.527361 SCORE: 02.GraphGood            : 0
14:12:05.527390 SCORE: 03.GraphNormal          : 0
14:12:05.527394 SCORE: 04.GraphBad             : 0
14:12:05.527419 SCORE: 05.GraphWorst           : 322
14:12:05.527441 SCORE: 06.TodayGraphExcellent  : 0
14:12:05.527448 SCORE: 07.TodayGraphGood       : 0
14:12:05.527468 SCORE: 08.TodayGraphNormal     : 0
14:12:05.527475 SCORE: 09.TodayGraphBad        : 0
14:12:05.527479 SCORE: 10.TodayGraphWorst      : 931
14:12:05.527504 SCORE: 11.ReadInfoCondition    : 786
14:12:05.527524 SCORE: 12.ReadWarningCondition : 557
14:12:05.527531 SCORE: 13.ReadCriticalCondition: 20
14:12:05.527534 SCORE: _1.IsuInitialize        : 51
14:12:05.527555 SCORE: _2.NormalUserInitialize : 8
14:12:05.527563 SCORE: _3.ViewerInitialize     : 24
14:12:05.527584 SCORE: _4.ViewerDropout        : 0
14:12:05.527590 SCORE: _5.RepairIsu            : 530
14:12:05.527594 SCORE: _6.PostInfoCondition    : 8592
14:12:05.527614 SCORE: _7.PostWarningCondition : 5945
14:12:05.527635 SCORE: _8.PostCriticalCondition: 241
14:12:05.527671 score: 50724(50724 - 0) : pass
14:12:05.527677 deduction: 0 / timeout: 0
14:12:05.527697 <=== sendResult finish

Nagarei commented 3 years ago

app / post condition / db の三台構成 benchネック

app 80 : post condition 50 : sql 85 くらい

14:33:32.676368 --- userAdder END
14:33:32.676665 LOAD WAIT
14:33:32.699795 TIME:
condition: 662, repair: 277, graph: 438, viewer: 80
14:33:33.007774 <=== LOAD END
14:33:33.007856 SCORE: 00.StartBenchmark       : 1
14:33:33.007879 SCORE: 01.GraphExcellent       : 0
14:33:33.007887 SCORE: 02.GraphGood            : 0
14:33:33.007893 SCORE: 03.GraphNormal          : 0
14:33:33.007900 SCORE: 04.GraphBad             : 0
14:33:33.007905 SCORE: 05.GraphWorst           : 1523
14:33:33.007916 SCORE: 06.TodayGraphExcellent  : 0
14:33:33.007927 SCORE: 07.TodayGraphGood       : 0
14:33:33.007934 SCORE: 08.TodayGraphNormal     : 0
14:33:33.007947 SCORE: 09.TodayGraphBad        : 0
14:33:33.007954 SCORE: 10.TodayGraphWorst      : 1182
14:33:33.007960 SCORE: 11.ReadInfoCondition    : 1015
14:33:33.007972 SCORE: 12.ReadWarningCondition : 1319
14:33:33.007979 SCORE: 13.ReadCriticalCondition: 297
14:33:33.007989 SCORE: _1.IsuInitialize        : 287
14:33:33.007995 SCORE: _2.NormalUserInitialize : 53
14:33:33.008008 SCORE: _3.ViewerInitialize     : 159
14:33:33.008017 SCORE: _4.ViewerDropout        : 78
14:33:33.008023 SCORE: _5.RepairIsu            : 806
14:33:33.008035 SCORE: _6.PostInfoCondition    : 14183
14:33:33.008042 SCORE: _7.PostWarningCondition : 23422
14:33:33.008045 SCORE: _8.PostCriticalCondition: 7513
14:33:33.010131 score: 90892(90908 - 16) : pass
14:33:33.010139 deduction: 1 / timeout: 157
14:33:33.010148 <=== sendResult finish

Nagarei commented 3 years ago

~~drop rate = 0.5~~ benchとsql

##  2021-08-16 14:41:38.99 +0900
  94 COMMIT
  46 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  41 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  16 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   5 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   4 SELECT * FROM `isu` WHERE `jia_user_id` = S ORDER BY `id` DESC
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ?     AND `timestamp` < ?     ORDER BY `timestamp` DESC
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
   2 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND S <= `timestamp` ORDER BY `timestamp` DESC
   2 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   1 SELECT * FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT `image` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `character` = ?
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY `timestamp` ASC
14:39:23.414956 TIME:
condition: 768, repair: 284, graph: 460, viewer: 91
14:39:23.717171 <=== LOAD END
14:39:23.717236 SCORE: 00.StartBenchmark       : 1
14:39:23.717246 SCORE: 01.GraphExcellent       : 0
14:39:23.717252 SCORE: 02.GraphGood            : 0
14:39:23.717260 SCORE: 03.GraphNormal          : 0
14:39:23.717263 SCORE: 04.GraphBad             : 0
14:39:23.717317 SCORE: 05.GraphWorst           : 1551
14:39:23.717326 SCORE: 06.TodayGraphExcellent  : 0
14:39:23.717329 SCORE: 07.TodayGraphGood       : 0
14:39:23.717334 SCORE: 08.TodayGraphNormal     : 0
14:39:23.717340 SCORE: 09.TodayGraphBad        : 0
14:39:23.717346 SCORE: 10.TodayGraphWorst      : 1175
14:39:23.717352 SCORE: 11.ReadInfoCondition    : 1106
14:39:23.717358 SCORE: 12.ReadWarningCondition : 1516
14:39:23.717365 SCORE: 13.ReadCriticalCondition: 337
14:39:23.717371 SCORE: _1.IsuInitialize        : 298
14:39:23.717378 SCORE: _2.NormalUserInitialize : 57
14:39:23.717386 SCORE: _3.ViewerInitialize     : 171
14:39:23.717393 SCORE: _4.ViewerDropout        : 82
14:39:23.717398 SCORE: _5.RepairIsu            : 809
14:39:23.717404 SCORE: _6.PostInfoCondition    : 15892
14:39:23.717410 SCORE: _7.PostWarningCondition : 24116
14:39:23.717416 SCORE: _8.PostCriticalCondition: 9650
14:39:23.719667 score: 99123(99140 - 17) : pass
14:39:23.719674 deduction: 0 / timeout: 170
14:39:23.719683 <=== sendResult finish
14:39:23.719815 --- ISU協会サービス END
14:39:23.720957 --- JiaAPIService END

Nagarei commented 3 years ago

ガチャ


14:45:38.002384 LOAD WAIT
14:45:38.047795 TIME:
condition: 765, repair: 273, graph: 446, viewer: 77
14:45:38.167676 <=== LOAD END
14:45:38.167798 SCORE: 00.StartBenchmark       : 1
14:45:38.167828 SCORE: 01.GraphExcellent       : 0
14:45:38.167837 SCORE: 02.GraphGood            : 0
14:45:38.167861 SCORE: 03.GraphNormal          : 0
14:45:38.167868 SCORE: 04.GraphBad             : 0
14:45:38.167896 SCORE: 05.GraphWorst           : 1569
14:45:38.167920 SCORE: 06.TodayGraphExcellent  : 0
14:45:38.167942 SCORE: 07.TodayGraphGood       : 0
14:45:38.167950 SCORE: 08.TodayGraphNormal     : 0
14:45:38.167972 SCORE: 09.TodayGraphBad        : 0
14:45:38.167979 SCORE: 10.TodayGraphWorst      : 1177
14:45:38.168007 SCORE: 11.ReadInfoCondition    : 1164
14:45:38.168011 SCORE: 12.ReadWarningCondition : 1563
14:45:38.168036 SCORE: 13.ReadCriticalCondition: 338
14:45:38.168039 SCORE: _1.IsuInitialize        : 287
14:45:38.168066 SCORE: _2.NormalUserInitialize : 53
14:45:38.168086 SCORE: _3.ViewerInitialize     : 159
14:45:38.168093 SCORE: _4.ViewerDropout        : 91
14:45:38.168113 SCORE: _5.RepairIsu            : 784
14:45:38.168133 SCORE: _6.PostInfoCondition    : 15932
14:45:38.168155 SCORE: _7.PostWarningCondition : 25770
14:45:38.168174 SCORE: _8.PostCriticalCondition: 8062
14:45:38.170745 score: 102579(102598 - 19) : pass
14:45:38.170753 deduction: 0 / timeout: 196
14:45:38.170763 <=== sendResult finish

Nagarei commented 3 years ago

drop-rate=0.5


14:47:59.337961 --- userAdder END
14:47:59.338139 LOAD WAIT
14:47:59.365905 TIME:
condition: 1248, repair: 325, graph: 580, viewer: 63
14:47:59.477537 <=== LOAD END
14:47:59.477610 SCORE: 00.StartBenchmark       : 1
14:47:59.477618 SCORE: 01.GraphExcellent       : 0
14:47:59.477624 SCORE: 02.GraphGood            : 0
14:47:59.477628 SCORE: 03.GraphNormal          : 0
14:47:59.477633 SCORE: 04.GraphBad             : 1
14:47:59.477638 SCORE: 05.GraphWorst           : 1446
14:47:59.477642 SCORE: 06.TodayGraphExcellent  : 0
14:47:59.477645 SCORE: 07.TodayGraphGood       : 0
14:47:59.477649 SCORE: 08.TodayGraphNormal     : 0
14:47:59.477653 SCORE: 09.TodayGraphBad        : 0
14:47:59.477658 SCORE: 10.TodayGraphWorst      : 732
14:47:59.477661 SCORE: 11.ReadInfoCondition    : 1362
14:47:59.477664 SCORE: 12.ReadWarningCondition : 1810
14:47:59.477668 SCORE: 13.ReadCriticalCondition: 326
14:47:59.477672 SCORE: _1.IsuInitialize        : 193
14:47:59.477677 SCORE: _2.NormalUserInitialize : 35
14:47:59.477682 SCORE: _3.ViewerInitialize     : 105
14:47:59.477687 SCORE: _4.ViewerDropout        : 25
14:47:59.477690 SCORE: _5.RepairIsu            : 578
14:47:59.477694 SCORE: _6.PostInfoCondition    : 6498
14:47:59.477699 SCORE: _7.PostWarningCondition : 13431
14:47:59.477702 SCORE: _8.PostCriticalCondition: 8835
14:47:59.478561 score: 112383(112388 - 5) : pass
14:47:59.478568 deduction: 0 / timeout: 57
14:47:59.478577 <=== sendResult finish

Nagarei commented 3 years ago

drop-rate=0.5 + iconをdbに入れない app と dbが詰まる benchは浮く

commitはpost isuかと思ったが違うらしい

##  2021-08-16 15:18:10.52 +0900
 151 COMMIT
 100 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  81 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?)
  30 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
  10 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   7 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   4 SELECT name FROM `isu` WHERE `jia_isu_uuid` = S AND `jia_user_id` = S
   4 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   3 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = ?
   3 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   2 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = S
   2 SELECT `image_hash` FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   2 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND S <= `timestamp` ORDER BY `timestamp` DESC
   1 UPDATE `isu` SET `character` = ? WHERE `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `character` = ?
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `character` = S
   1 SELECT * FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT COUNT(*) FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ?     AND `timestamp` < ?     AND ? <= `timestamp` ORDER BY `timestamp` DESC
   1 SELECT `image_hash` FROM `isu` WHERE `jia_user_id` = ? AND `jia_isu_uuid` = ?
   1 SELECT * FROM `isu` WHERE `jia_user_id` = S ORDER BY `id` DESC
15:09:56.445273 TIME:
condition: 1263, repair: 330, graph: 598, viewer: 72
15:09:56.538813 <=== LOAD END
15:09:56.538884 SCORE: 00.StartBenchmark       : 1
15:09:56.538894 SCORE: 01.GraphExcellent       : 0
15:09:56.538902 SCORE: 02.GraphGood            : 0
15:09:56.538908 SCORE: 03.GraphNormal          : 0
15:09:56.538915 SCORE: 04.GraphBad             : 2
15:09:56.538921 SCORE: 05.GraphWorst           : 1500
15:09:56.538928 SCORE: 06.TodayGraphExcellent  : 0
15:09:56.538931 SCORE: 07.TodayGraphGood       : 0
15:09:56.538943 SCORE: 08.TodayGraphNormal     : 0
15:09:56.538949 SCORE: 09.TodayGraphBad        : 0
15:09:56.538961 SCORE: 10.TodayGraphWorst      : 751
15:09:56.538967 SCORE: 11.ReadInfoCondition    : 1456
15:09:56.538974 SCORE: 12.ReadWarningCondition : 1715
15:09:56.538985 SCORE: 13.ReadCriticalCondition: 431
15:09:56.538992 SCORE: _1.IsuInitialize        : 201
15:09:56.539005 SCORE: _2.NormalUserInitialize : 37
15:09:56.539011 SCORE: _3.ViewerInitialize     : 111
15:09:56.539018 SCORE: _4.ViewerDropout        : 34
15:09:56.539029 SCORE: _5.RepairIsu            : 560
15:09:56.539036 SCORE: _6.PostInfoCondition    : 6483
15:09:56.539047 SCORE: _7.PostWarningCondition : 13902
15:09:56.539053 SCORE: _8.PostCriticalCondition: 9435
15:09:56.539826 score: 115969(115974 - 5) : pass
15:09:56.539832 deduction: 0 / timeout: 57
15:09:56.539841 <=== sendResult finish

Nagarei commented 3 years ago

バルクインサート　commitはこっちだった（それはそう） sqlとappネック

 373 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `message`)  VALUES (?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?),(?, ?, ?, ?, ?)
 297 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     ORDER BY `timestamp` DESC
  42 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
   6 INSERT INTO `isu_condition`        
condition: 3334, repair: 300, graph: 621, viewer: 59
15:30:23.078282 <=== LOAD END
15:30:23.078360 SCORE: 00.StartBenchmark       : 1
15:30:23.078377 SCORE: 01.GraphExcellent       : 0
15:30:23.078385 SCORE: 02.GraphGood            : 0
15:30:23.078391 SCORE: 03.GraphNormal          : 0
15:30:23.078398 SCORE: 04.GraphBad             : 6
15:30:23.078405 SCORE: 05.GraphWorst           : 478
15:30:23.078411 SCORE: 06.TodayGraphExcellent  : 0
15:30:23.078425 SCORE: 07.TodayGraphGood       : 0
15:30:23.078431 SCORE: 08.TodayGraphNormal     : 0
15:30:23.078436 SCORE: 09.TodayGraphBad        : 5
15:30:23.078450 SCORE: 10.TodayGraphWorst      : 305
15:30:23.078462 SCORE: 11.ReadInfoCondition    : 1293
15:30:23.078469 SCORE: 12.ReadWarningCondition : 1403
15:30:23.078481 SCORE: 13.ReadCriticalCondition: 455
15:30:23.078488 SCORE: _1.IsuInitialize        : 179
15:30:23.078507 SCORE: _2.NormalUserInitialize : 32
15:30:23.078543 SCORE: _3.ViewerInitialize     : 96
15:30:23.078565 SCORE: _4.ViewerDropout        : 17
15:30:23.078571 SCORE: _5.RepairIsu            : 185
15:30:23.078577 SCORE: _6.PostInfoCondition    : 5485
15:30:23.078583 SCORE: _7.PostWarningCondition : 12359
15:30:23.078589 SCORE: _8.PostCriticalCondition: 17178
15:30:23.079954 score: 91800(91810 - 10) : pass
15:30:23.079961 deduction: 0 / timeout: 100
15:30:23.079970 <=== sendResult finish

Nagarei commented 3 years ago

get conditionにlimitをかける benchとdbネック

 380 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `condition_level`, `message`)       VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)
 101 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` ASC
  26 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND `condition_level` IN (S, S, S)   ORDER BY `timestamp` DESC LIMIT N
  11 INSERT INTO `isu_condition`    
15:48:08.123078 TIME:
condition: 1939, repair: 390, graph: 838, viewer: 108
15:48:08.399939 <=== LOAD END
15:48:08.400024 SCORE: 00.StartBenchmark       : 1
15:48:08.400036 SCORE: 01.GraphExcellent       : 0
15:48:08.400043 SCORE: 02.GraphGood            : 0
15:48:08.400049 SCORE: 03.GraphNormal          : 0
15:48:08.400056 SCORE: 04.GraphBad             : 5
15:48:08.400063 SCORE: 05.GraphWorst           : 2218
15:48:08.400069 SCORE: 06.TodayGraphExcellent  : 0
15:48:08.400073 SCORE: 07.TodayGraphGood       : 0
15:48:08.400079 SCORE: 08.TodayGraphNormal     : 1
15:48:08.400083 SCORE: 09.TodayGraphBad        : 2
15:48:08.400092 SCORE: 10.TodayGraphWorst      : 910
15:48:08.400101 SCORE: 11.ReadInfoCondition    : 2605
15:48:08.400111 SCORE: 12.ReadWarningCondition : 3325
15:48:08.400121 SCORE: 13.ReadCriticalCondition: 920
15:48:08.400131 SCORE: _1.IsuInitialize        : 389
15:48:08.400141 SCORE: _2.NormalUserInitialize : 73
15:48:08.400151 SCORE: _3.ViewerInitialize     : 219
15:48:08.400161 SCORE: _4.ViewerDropout        : 154
15:48:08.400172 SCORE: _5.RepairIsu            : 706
15:48:08.400184 SCORE: _6.PostInfoCondition    : 10274
15:48:08.400191 SCORE: _7.PostWarningCondition : 21509
15:48:08.400197 SCORE: _8.PostCriticalCondition: 20073
15:48:08.405089 score: 207069(207108 - 39) : pass
15:48:08.405099 deduction: 2 / timeout: 372
15:48:08.405107 <=== sendResult finish

Nagarei commented 3 years ago

graphの取得範囲に制限 bench app db

 152 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `condition_level`, `message`)       VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)
  10 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   8 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND `condition_level` IN (S, S, S)   ORDER BY `timestamp` DESC LIMIT N
   6 SELECT name FROM `isu` WHERE `jia_isu_uuid` = S AND `jia_user_id` = S
   5 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   5 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S AND S <= `timestamp` AND `timestamp` < S ORDER BY `timestamp` ASC
   5 INSERT INTO `isu_condition`        
15:57:32.720863 TIME:
condition: 1498, repair: 345, graph: 611, viewer: 124
15:57:32.828119 負荷レベルは上昇しませんでした
15:57:32.880817 --- userAdder END
15:57:32.881288 LOAD WAIT
15:57:33.141323 <=== LOAD END
15:57:33.141404 SCORE: 00.StartBenchmark       : 1
15:57:33.141415 SCORE: 01.GraphExcellent       : 0
15:57:33.141422 SCORE: 02.GraphGood            : 0
15:57:33.141429 SCORE: 03.GraphNormal          : 0
15:57:33.141436 SCORE: 04.GraphBad             : 6
15:57:33.141440 SCORE: 05.GraphWorst           : 3084
15:57:33.141443 SCORE: 06.TodayGraphExcellent  : 0
15:57:33.141446 SCORE: 07.TodayGraphGood       : 0
15:57:33.141452 SCORE: 08.TodayGraphNormal     : 0
15:57:33.141459 SCORE: 09.TodayGraphBad        : 3
15:57:33.141465 SCORE: 10.TodayGraphWorst      : 1216
15:57:33.141472 SCORE: 11.ReadInfoCondition    : 2838
15:57:33.141481 SCORE: 12.ReadWarningCondition : 4476
15:57:33.141492 SCORE: 13.ReadCriticalCondition: 1193
15:57:33.141502 SCORE: _1.IsuInitialize        : 418
15:57:33.141511 SCORE: _2.NormalUserInitialize : 79
15:57:33.141521 SCORE: _3.ViewerInitialize     : 238
15:57:33.141534 SCORE: _4.ViewerDropout        : 160
15:57:33.141549 SCORE: _5.RepairIsu            : 982
15:57:33.141559 SCORE: _6.PostInfoCondition    : 11592
15:57:33.141568 SCORE: _7.PostWarningCondition : 25142
15:57:33.141580 SCORE: _8.PostCriticalCondition: 16298
15:57:33.146511 score: 252067(252106 - 39) : pass
15:57:33.146521 deduction: 2 / timeout: 375
15:57:33.146531 <=== sendResult finish

Nagarei commented 3 years ago

drop rate = 0 bench / sqlネック

##  2021-08-16 16:10:49.83 +0900
 392 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `condition_level`, `message`)       VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)
  14 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND `condition_level` IN (S, S, S)   ORDER BY `timestamp` DESC LIMIT N
  11 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   9 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   7 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S AND S <= `timestamp` AND `timestamp` < S ORDER BY `timestamp` ASC
   6 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   3 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = S
   3 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   2 SELECT * FROM `isu` WHERE `jia_user_id` = S AND `jia_isu_uuid` = S
   1 SELECT * FROM `isu_condition`
16:10:21.201380 TIME:
condition: 1188, repair: 278, graph: 490, viewer: 90
16:10:21.202406 --- userAdder END
16:10:21.427864 <=== LOAD END
16:10:21.427947 SCORE: 00.StartBenchmark       : 1
16:10:21.427960 SCORE: 01.GraphExcellent       : 0
16:10:21.427980 SCORE: 02.GraphGood            : 32
16:10:21.428010 SCORE: 03.GraphNormal          : 0
16:10:21.428021 SCORE: 04.GraphBad             : 283
16:10:21.428043 SCORE: 05.GraphWorst           : 2969
16:10:21.428064 SCORE: 06.TodayGraphExcellent  : 0
16:10:21.428081 SCORE: 07.TodayGraphGood       : 14
16:10:21.428091 SCORE: 08.TodayGraphNormal     : 0
16:10:21.428106 SCORE: 09.TodayGraphBad        : 46
16:10:21.428132 SCORE: 10.TodayGraphWorst      : 1244
16:10:21.428145 SCORE: 11.ReadInfoCondition    : 3071
16:10:21.428155 SCORE: 12.ReadWarningCondition : 4616
16:10:21.428167 SCORE: 13.ReadCriticalCondition: 1335
16:10:21.428177 SCORE: _1.IsuInitialize        : 340
16:10:21.428187 SCORE: _2.NormalUserInitialize : 63
16:10:21.428200 SCORE: _3.ViewerInitialize     : 189
16:10:21.428212 SCORE: _4.ViewerDropout        : 113
16:10:21.428222 SCORE: _5.RepairIsu            : 1037
16:10:21.428232 SCORE: _6.PostInfoCondition    : 7031
16:10:21.428246 SCORE: _7.PostWarningCondition : 15651
16:10:21.428255 SCORE: _8.PostCriticalCondition: 9950
16:10:21.431973 score: 287869(287900 - 31) : pass
16:10:21.431981 deduction: 3 / timeout: 282
16:10:21.431997 <=== sendResult finish

Nagarei commented 3 years ago

indexからprimary keyに変更しいて言うならbenchが95で一番つまってる dbは95だけどたまに50 appは80

 227 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `condition_level`, `message`)       VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)
  11 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S     AND `timestamp` < S     AND `condition_level` IN (S, S, S)   ORDER BY `timestamp` DESC LIMIT N
   8 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY timestamp DESC LIMIT N
   7 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S ORDER BY `timestamp` DESC LIMIT N
   7 SELECT COUNT(*) FROM `user` WHERE `jia_user_id` = S
   7 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = ?
   5 SELECT COUNT(*) FROM `isu` WHERE `jia_isu_uuid` = S
   5 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = S AND S <= `timestamp` AND `timestamp` < S ORDER BY `timestamp` ASC
   3 SELECT * FROM `isu_condition` WHERE `jia_isu_uuid` = ? ORDER BY timestamp DESC LIMIT N
16:19:46.745648 LOAD WAIT
16:19:46.749895 --- userAdder END
16:19:46.780451 TIME:
condition: 1418, repair: 314, graph: 560, viewer: 105
16:19:47.147430 <=== LOAD END
16:19:47.147514 SCORE: 00.StartBenchmark       : 1
16:19:47.147540 SCORE: 01.GraphExcellent       : 0
16:19:47.147549 SCORE: 02.GraphGood            : 30
16:19:47.147562 SCORE: 03.GraphNormal          : 0
16:19:47.147569 SCORE: 04.GraphBad             : 265
16:19:47.147580 SCORE: 05.GraphWorst           : 3081
16:19:47.147587 SCORE: 06.TodayGraphExcellent  : 0
16:19:47.147593 SCORE: 07.TodayGraphGood       : 13
16:19:47.147604 SCORE: 08.TodayGraphNormal     : 0
16:19:47.147616 SCORE: 09.TodayGraphBad        : 40
16:19:47.147623 SCORE: 10.TodayGraphWorst      : 1192
16:19:47.147636 SCORE: 11.ReadInfoCondition    : 3350
16:19:47.147642 SCORE: 12.ReadWarningCondition : 4562
16:19:47.147655 SCORE: 13.ReadCriticalCondition: 1273
16:19:47.147661 SCORE: _1.IsuInitialize        : 378
16:19:47.147673 SCORE: _2.NormalUserInitialize : 70
16:19:47.147680 SCORE: _3.ViewerInitialize     : 210
16:19:47.147694 SCORE: _4.ViewerDropout        : 136
16:19:47.147700 SCORE: _5.RepairIsu            : 1008
16:19:47.147712 SCORE: _6.PostInfoCondition    : 7606
16:19:47.147718 SCORE: _7.PostWarningCondition : 16841
16:19:47.147724 SCORE: _8.PostCriticalCondition: 11346
16:19:47.151060 score: 296660(296688 - 28) : pass
16:19:47.151069 deduction: 3 / timeout: 253
16:19:47.151077 <=== sendResult finish

Nagarei commented 3 years ago

あとtrend改善は残ってるけど、benchが詰まってる段階なので手を付けても上がらない可能性が高そう

Nagarei commented 3 years ago

ちなみに初期データを戻した場合 CPU 75%くらいだけど、どこもCPU100になっていないので、たぶんtrendが詰まってるだけなので改善したら同じ結果になりそう

 112 INSERT INTO `isu_condition`        (`jia_isu_uuid`, `timestamp`, `is_sitting`, `condition`, `condition_level`, `message`)       VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)
16:32:04.384950 TIME:
condition: 1528, repair: 91, graph: 164, viewer: 35
16:32:04.423387 <=== LOAD END
16:32:04.423475 SCORE: 00.StartBenchmark       : 1
16:32:04.423485 SCORE: 01.GraphExcellent       : 0
16:32:04.423519 SCORE: 02.GraphGood            : 329
16:32:04.423524 SCORE: 03.GraphNormal          : 60
16:32:04.423550 SCORE: 04.GraphBad             : 8
16:32:04.423572 SCORE: 05.GraphWorst           : 111
16:32:04.423579 SCORE: 06.TodayGraphExcellent  : 0
16:32:04.423583 SCORE: 07.TodayGraphGood       : 165
16:32:04.423609 SCORE: 08.TodayGraphNormal     : 35
16:32:04.423614 SCORE: 09.TodayGraphBad        : 13
16:32:04.423638 SCORE: 10.TodayGraphWorst      : 86
16:32:04.423641 SCORE: 11.ReadInfoCondition    : 3069
16:32:04.423666 SCORE: 12.ReadWarningCondition : 4165
16:32:04.423692 SCORE: 13.ReadCriticalCondition: 1491
16:32:04.423713 SCORE: _1.IsuInitialize        : 74
16:32:04.423720 SCORE: _2.NormalUserInitialize : 12
16:32:04.423748 SCORE: _3.ViewerInitialize     : 36
16:32:04.423768 SCORE: _4.ViewerDropout        : 0
16:32:04.423774 SCORE: _5.RepairIsu            : 208
16:32:04.423795 SCORE: _6.PostInfoCondition    : 4474
16:32:04.423815 SCORE: _7.PostWarningCondition : 7267
16:32:04.423837 SCORE: _8.PostCriticalCondition: 5335
16:32:04.423870 score: 290866(290866 - 0) : pass
16:32:04.423876 deduction: 0 / timeout: 0
16:32:04.423895 <=== sendResult finish

Nagarei commented 3 years ago

やったこと

indexを張る https://github.com/Nagarei/isucon11q-check/blob/main/sql/2_patch.sql
trendの計算にlimit 1を入れる
ログを切る
trendの計算を、1ルーチンで計算して、ハンドラはその結果をひたすら返すだけに
limit no fileと limit no proc
nginxを切る
アプリ + post condition専用アプリ + dbの三台構成に（post conditionは直接二台目に投げる）
iconをdbに入れない + 304を返す
キャラクターをDBに入れてget conditionにlimitをかける
get graphの取得範囲に制限をかける

やってないこと

trendの一回の計算の効率化
post conditionを、複数リクエスト間でまとめる

Nagarei commented 3 years ago

xlにしたときのやつ


condition: 579, repair: 156, graph: 245, viewer: 36
17:58:01.557871 <=== LOAD END
17:58:01.557939 SCORE: 00.StartBenchmark       : 1
17:58:01.557950 SCORE: 01.GraphExcellent       : 0
17:58:01.557968 SCORE: 02.GraphGood            : 13
17:58:01.557976 SCORE: 03.GraphNormal          : 11
17:58:01.557983 SCORE: 04.GraphBad             : 395
17:58:01.558007 SCORE: 05.GraphWorst           : 2322
17:58:01.558023 SCORE: 06.TodayGraphExcellent  : 0
17:58:01.558033 SCORE: 07.TodayGraphGood       : 42
17:58:01.558041 SCORE: 08.TodayGraphNormal     : 9
17:58:01.558064 SCORE: 09.TodayGraphBad        : 256
17:58:01.558073 SCORE: 10.TodayGraphWorst      : 1953
17:58:01.558080 SCORE: 11.ReadInfoCondition    : 5242
17:58:01.558087 SCORE: 12.ReadWarningCondition : 5415
17:58:01.558106 SCORE: 13.ReadCriticalCondition: 924
17:58:01.558113 SCORE: _1.IsuInitialize        : 298
17:58:01.558120 SCORE: _2.NormalUserInitialize : 57
17:58:01.558128 SCORE: _3.ViewerInitialize     : 171
17:58:01.558136 SCORE: _4.ViewerDropout        : 98
17:58:01.558143 SCORE: _5.RepairIsu            : 1548
17:58:01.558150 SCORE: _6.PostInfoCondition    : 9733
17:58:01.558159 SCORE: _7.PostWarningCondition : 14018
17:58:01.558166 SCORE: _8.PostCriticalCondition: 4723
17:58:01.560425 score: 395006(395026 - 20) : pass
17:58:01.560433 deduction: 2 / timeout: 180
17:58:01.560443 <=== sendResult finish
17:58:01.560577 --- ISU協会サービス END
17:58:01.562607 --- JiaAPIService END

Nagarei commented 3 years ago

DEBUG=false
304じゃなくてキャッシュにしてリクエスト自体が来ないようにする
DBのidごとの分割あたりすると跳ねそう