1人で頑張るISUCON練習 #1

cureseven · 2021-05-30T05:12:05Z

ISUCON3予選練習

c5d.xlarge
netdata導入済み
kataribe導入済み
slowquery吐くようになってる
4時間のハーフタイム戦

初期ベンチ

Result:   SUCCESS
RawScore: 4201.3
Fails:    0
Score:    4201.3

The text was updated successfully, but these errors were encountered:

cureseven · 2021-05-30T05:13:34Z

git管理

今回はプロセス管理（プロセスマネージャー）はsystemdではなくsuperviserd
言語固有nodeだったらpm2とか
phpだったら別に立てる
プロセス管理してない場合もある

※ systemdではなかったら

一個ずつコマンド叩いて何使っているか調べる
プロセス見る
cent6系だったらこれって推測

$ sudo supervisorctl status

$ sudo supervisorctl status
isucon-app                       RUNNING   pid 2166, uptime 0:33:53

以下を変更しリポジトリを向くようにした
$ vi /etc/supervisord.d/app.ini

cureseven · 2021-05-30T05:29:07Z

切替後スコア

Result:   SUCCESS
RawScore: 4264.2
Fails:    0
Score:    4264.2

cureseven · 2021-05-30T05:34:31Z

db

memos

41397

+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| id         | int(11)    | NO   | PRI | NULL              | auto_increment              |
| user       | int(11)    | NO   |     | NULL              |                             |
| content    | text       | YES  |     | NULL              |                             |
| is_private | tinyint(4) | NO   |     | 0                 |                             |
| created_at | datetime   | NO   |     | NULL              |                             |
| updated_at | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+-------------------+-----------------------------+

id

users

400

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| username    | varchar(255) | NO   | UNI | NULL    |                |
| password    | varchar(255) | NO   |     | NULL    |                |
| salt        | varchar(255) | NO   |     | NULL    |                |
| last_access | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

id
users_username_idx

cureseven · 2021-05-30T05:48:33Z

pt-query-digest


# A software update is available:

# 2.9s user time, 280ms system time, 56.06M rss, 4.15G vsz
# Current date: Sun May 30 14:47:47 2021
# Hostname: curemacpro2017.local
# Files: mysql-slow.log
# Overall: 8.66k total, 8 unique, 0.19 QPS, 0.01x concurrency ____________
# Time range: 2021-05-29 17:02:42 to 2021-05-30 05:33:11
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           277s   248us      1s    32ms    68ms    29ms    24ms
# Lock time          230ms       0    19ms    26us    47us   210us    15us
# Rows sent        581.04k       0     201   68.72  166.51   56.35   92.72
# Rows examine     354.66M       0  60.54k  41.95k  54.03k   6.29k  40.32k
# Query size        81.85M      26 1021.69k   9.68k  112.70  96.75k   92.72

# Profile
# Rank Query ID                       Response time  Calls R/Call V/M   It
# ==== ============================== ============== ===== ====== ===== ==
#    1 0x29D4C98A0690D9F64517399CB... 114.5045 41.3%  2659 0.0431  0.01 SELECT memos
#    2 0xCD34663DDD11490E4B4B34854...  64.6903 23.4%  2383 0.0271  0.01 SELECT memos
#    3 0xE42484D79937BFFA08E3E2CA9...  58.5645 21.1%  2659 0.0220  0.04 SELECT memos
#    4 0xD4F4707656A73B9588929194A...  14.4488  5.2%    82 0.1762  0.02 INSERT UPDATE DELETE REPLACE UPDATE DELETE SELECT DELETE UPDATE DELETE UPDATE DELETE REPLACE DELETE SELECT UPDATE REPLACE UPDATE REPLACE UPDATE DELETE SELECT UPDATE SELECT REPLACE DELETE UPDATE DELETE UPDATE memos
#    5 0x775AAE4DC308AAEC402E13A51...  12.9024  4.7%   438 0.0295  0.00 SELECT memos
# MISC 0xMISC                          11.8794  4.3%   437 0.0272   0.0 <3 ITEMS>

# Query 1: 0.06 QPS, 0.00x concurrency, ID 0x29D4C98A0690D9F64517399CB130EF84 at byte 86235133
# Scores: V/M = 0.01
# Time range: 2021-05-29 17:02:42 to 2021-05-30 05:23:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         30    2659
# Exec time     41    115s    16ms   615ms    43ms    87ms    25ms    34ms
# Lock time     18    44ms    10us    55us    16us    23us     4us    14us
# Rows sent     44 259.67k     100     100     100     100       0     100
# Rows examine  34 121.41M  40.31k  60.54k  46.76k  59.57k   6.80k  42.34k
# Query size     0 231.99k      82      95   89.34   92.72    6.21   92.72
# String:
# Databases    isucon
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ##
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
#    SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC LIMIT 100 OFFSET 16800\G

# Query 2: 0.71 QPS, 0.02x concurrency, ID 0xCD34663DDD11490E4B4B34854741F958 at byte 85156850
# Scores: V/M = 0.01
# Time range: 2021-05-30 04:27:15 to 05:23:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         27    2383
# Exec time     23     65s    14ms   231ms    27ms    46ms    13ms    23ms
# Lock time     14    32ms     6us     2ms    13us    19us    35us    11us
# Rows sent     43 250.94k       2     201  107.83  183.58   55.20  112.70
# Rows examine  26  94.07M  40.22k  40.61k  40.42k  40.32k  202.50  40.32k
# Query size     0 234.53k      99     101  100.78   97.36       0   97.36
# String:
# Databases    isucon
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
#    SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=170  ORDER BY created_at\G

# Query 3: 0.06 QPS, 0.00x concurrency, ID 0xE42484D79937BFFA08E3E2CA950A9030 at byte 0
# Scores: V/M = 0.04
# Time range: 2021-05-29 17:02:42 to 2021-05-30 05:23:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         30    2659
# Exec time     21     59s    11ms      1s    22ms    38ms    30ms    18ms
# Lock time     61   141ms    23us    19ms    53us    57us   376us    40us
# Rows sent      0   2.60k       1       1       1       1       0       1
# Rows examine  29 104.70M  40.21k  40.43k  40.32k  40.32k     111  38.40k
# Query size     0 129.83k      50      50      50      50       0      50
# String:
# Databases    isucon
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #
#    1s  #
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
#    SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(*) AS c FROM memos WHERE is_private=0\G

# Query 4: 4.32 QPS, 0.76x concurrency, ID 0xD4F4707656A73B9588929194AB4B746F at byte 1037479
# Scores: V/M = 0.02
# Time range: 2021-05-30 04:26:51 to 04:27:10
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      82
# Exec time      5     14s   104ms   542ms   176ms   253ms    64ms   163ms
# 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    99  81.18M 974.38k 1021.69k 1013.73k 1009.33k  19.89k 1009.33k
# String:
# Databases    isucon
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
#    SHOW CREATE TABLE `isucon`.`memos`\G
INSERT INTO `memos` VALUES (1,1,'npm-owner(1) -- Manage package owners\n=====================================\n\n## SYNOPSIS\n\n    npm owner ls <package name>\n    npm owner add <user> <package name>\n    npm owner rm <user> <package name>\n\n## DESCRIPTION\n\nManage ownership of published packages.\n\n* ls:\n  List all the users who have access to modify a package and push new versions.\n  Handy when you need to know who to bug for help.\n* add:\n  Add a new user as a maintainer of a package.  This user is enabled to modify\n  metadata, publish new versions, and add other owners.\n* rm:\n  Remove a user from the package owner list.  This immediately revokes their\n  privileges.\n\nNote that there is only one level of access.  Either you can modify a package,\nor you can\'t.  Future versions may contain more fine-grained access levels, but\nthat is not implemented at this time.\n\n## SEE ALSO\n\n* npm-publish(1)\n* npm-registry(7)\n* npm-adduser(1)\n* npm-disputes(7)\n',1,'2013-10-03 20:39:13','2013-10-04 11:39:12') /*... omitted ...*/\G

# Query 5: 0.01 QPS, 0.00x concurrency, ID 0x775AAE4DC308AAEC402E13A51340C830 at byte 85929508
# Scores: V/M = 0.00
# Time range: 2021-05-29 17:02:46 to 2021-05-30 05:23:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5     438
# Exec time      4     13s    15ms    71ms    29ms    48ms     9ms    27ms
# Lock time      2     6ms     8us    36us    14us    22us     3us    13us
# Rows sent      3  22.86k       1     108   53.44   92.72   26.55   54.21
# Rows examine   4  17.27M  40.24k  40.51k  40.38k  40.32k     142  40.32k
# Query size     0  49.94k     115     117  116.76  112.70       0  112.70
# String:
# Databases    isucon
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isucon` LIKE 'memos'\G
#    SHOW CREATE TABLE `isucon`.`memos`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=218 AND is_private=0 ORDER BY created_at\G

cureseven · 2021-05-30T05:56:24Z

memos.is_privateにINDEX

explain SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC100
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | memos | ALL  | NULL          | NULL | NULL    | NULL | 32835 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+

CREATE INDEX idx_memos_is_private ON memos (is_private);
explain SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC100
+----+-------------+-------+------+----------------------+----------------------+---------+-------+-------+-----------------------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows  | Extra                       |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | memos | ref  | idx_memos_is_private | idx_memos_is_private | 1       | const | 16417 | Using where; Using filesort |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+-------+-----------------------------+

スコア

Result:   SUCCESS
RawScore: 4252.8
Fails:    0
Score:    4252.8

(restartしてなかった〜)

cureseven · 2021-05-30T06:04:08Z

memos.userにINDEX

mysql> explain SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=170  ORDER BY created_at;
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | memos | ALL  | NULL          | NULL | NULL    | NULL | 37390 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX idx_memos_user ON memos (user);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=170  ORDER BY created_at;
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | memos | ref  | idx_memos_user | idx_memos_user | 4       | const |  112 | Using where; Using filesort |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

スコア

Result:   SUCCESS
RawScore: 4850.1
Fails:    0
Score:    4850.1

（restartした）

cureseven · 2021-05-30T06:16:23Z

2つのINDEX入れた結果

pt-query-digest

# Profile
# Rank Query ID                        Response time Calls R/Call V/M   It
# ==== =============================== ============= ===== ====== ===== ==
#    1 0x29D4C98A0690D9F64517399CB1...  8.6195 47.4%    73 0.1181  0.01 SELECT memos
#    2 0xCD34663DDD11490E4B4B348547...  7.5905 41.8%   263 0.0289  0.00 SELECT memos
#    3 0x2A759D69F7C6E64FD675B617BD...  1.6089  8.9%    46 0.0350  0.01 SELECT memos
# MISC 0xMISC                           0.3539  1.9%     3 0.1180   0.0 <1 ITEMS>
1
SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC LIMIT 100 OFFSET 20200
2
SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=271  ORDER BY created_at

kataribe

Top 20 Sort By Total
Count   Total    Mean  Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 1758  64.982  0.0370  0.0394  0.000  0.028  0.062  0.079  0.154  0.602  1641    0  117    0     6856537         10       3900      57920  get memo
  603  41.700  0.0692  0.0189  0.036  0.065  0.096  0.108  0.127  0.153   603    0    0    0     8795583      13493      14586      15495  GET / HTTP/1.1
   46   4.725  0.1027  0.0226  0.061  0.094  0.142  0.144  0.173  0.173    46    0    0    0      630395      13152      13704      13931  GET /recent/205 HTTP/1.1
   36   4.688  0.1302  0.0343  0.081  0.124  0.181  0.183  0.208  0.208    36    0    0    0      502092      13316      13947      14099  GET /recent/204 HTTP/1.1
  240   3.230  0.0135  0.0166  0.001  0.007  0.034  0.051  0.086  0.099   240    0    0    0     3730002       1920      15541      28283  GET /mypage HTTP/1.1
    9   1.054  0.1171  0.0425  0.067  0.100  0.195  0.195  0.195  0.195     9    0    0    0      126143      13752      14015      14379  GET /recent/195 HTTP/1.1
 1566   0.887  0.0006  0.0012  0.000  0.000  0.001  0.002  0.007  0.014  1566    0    0    0   145805562      93107      93107      93107  GET /js/jquery.min.js HTTP/1.1
 1566   0.827  0.0005  0.0011  0.000  0.000  0.001  0.002  0.006  0.015  1566    0    0    0   166019490     106015     106015     106015  GET /css/bootstrap.min.css HTTP/1.1
 1566   0.790  0.0005  0.0012  0.000  0.000  0.001  0.002  0.006  0.015  1566    0    0    0    26385534      16849      16849      16849  GET /css/bootstrap-responsive.min.css HTTP/1.1
    8   0.771  0.0964  0.0318  0.057  0.095  0.167  0.167  0.167  0.167     8    0    0    0      110144      13687      13768      13840  GET /recent/162 HTTP/1.1
 1566   0.761  0.0005  0.0012  0.000  0.000  0.001  0.002  0.005  0.019  1566    0    0    0    44836146      28631      28631      28631  GET /js/bootstrap.min.js HTTP/1.1
    8   0.756  0.0945  0.0373  0.056  0.089  0.188  0.188  0.188  0.188     8    0    0    0      117309      14250      14663      15065  GET /recent/134 HTTP/1.1
    8   0.680  0.0850  0.0384  0.058  0.070  0.177  0.177  0.177  0.177     8    0    0    0      112536      13824      14067      14329  GET /recent/111 HTTP/1.1
    7   0.675  0.0964  0.0169  0.075  0.092  0.127  0.127  0.127  0.127     7    0    0    0       96365      13589      13766      14150  GET /recent/198 HTTP/1.1
  240   0.662  0.0028  0.0019  0.001  0.002  0.004  0.007  0.012  0.015     0  240    0    0           0          0          0          0  POST /memo HTTP/1.1
    8   0.646  0.0808  0.0089  0.070  0.080  0.094  0.094  0.094  0.094     8    0    0    0      116884      14488      14610      14689  GET /recent/121 HTTP/1.1
  240   0.631  0.0026  0.0017  0.001  0.002  0.004  0.005  0.012  0.015     0  240    0    0           0          0          0          0  POST /signin HTTP/1.1
    7   0.624  0.0891  0.0328  0.054  0.085  0.151  0.151  0.151  0.151     7    0    0    0      101835      14350      14547      14712  GET /recent/140 HTTP/1.1
    7   0.619  0.0884  0.0249  0.056  0.085  0.138  0.138  0.138  0.138     7    0    0    0       96159      13537      13737      13983  GET /recent/152 HTTP/1.1
    8   0.602  0.0752  0.0279  0.054  0.069  0.147  0.147  0.147  0.147     8    0    0    0      111284      13618      13910      14131  GET /recent/1 HTTP/1.1

top

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM
10328 mysql     20   0 2855392 616232  14352 S 159.5  7.9
10037 root      20   0 1554332  21948   9204 S  43.9  0.3
12389 root      20   0 1002320 230884  11252 R  12.3  3.0
10063 nginx     20   0   40588   3984   2372 S   4.0  0.1
 2872 netdata   20   0  408732  90172   7320 S   0.7  1.2
 3010 netdata   20   0   58964   6624   3988 S   0.7  0.1
 3025 root      20   0  463112   5404   3796 S   0.7  0.1
   22 root      20   0       0      0      0 S   0.3  0.0
 1170 root      20   0       0      0      0 S   0.3  0.0
 9800 root      20   0       0      0      0 I   0.3  0.0
13603 root      20   0  138472   5860   3772 R   0.3  0.1
    1 root      20   0  191096   5448   3936 S   0.0  0.1
    2 root      20   0       0      0      0 S   0.0  0.0

考察

SELECT * FROM memos WHERE is_private=0 ORDER BY created_at DESC, id DESC LIMIT 100 OFFSET 20200 はtopHandlerで呼ばれている
SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=271 ORDER BY created_at は呼ばれてる数が多い．get memo(memoHander), mypageHandlerで使われているクエリ．

N+1はなさそう

cureseven · 2021-05-30T07:28:13Z

動的プレースホルダで

Result:   SUCCESS
RawScore: 4819.0
Fails:    0
Score:    4819.0

Top 20 Sort By Total
Count    Total    Mean  Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 3349  119.492  0.0357  0.0375  0.000  0.029  0.059  0.073  0.121  0.567  3111    0  238    0    13106558         10       3913      57920  get memo
 1232   83.225  0.0676  0.0205  0.035  0.062  0.092  0.105  0.143  0.193  1232    0    0    0    18127201      13478      14713      15942  GET / HTTP/1.1
   94   10.883  0.1158  0.0309  0.062  0.110  0.163  0.181  0.203  0.203    94    0    0    0     1291396      13084      13738      14009  GET /recent/205 HTTP/1.1
   88   10.202  0.1159  0.0313  0.058  0.112  0.150  0.176  0.239  0.239    88    0    0    0     1225963      13537      13931      14147  GET /recent/204 HTTP/1.1
  490    5.394  0.0110  0.0136  0.001  0.006  0.031  0.038  0.068  0.074   490    0    0    0     7589753       1920      15489      28478  GET /mypage HTTP/1.1
   15    1.529  0.1019  0.0472  0.055  0.091  0.183  0.231  0.231  0.231    15    0    0    0      213876      13999      14258      14406  GET /recent/199 HTTP/1.1
 3160    1.462  0.0005  0.0010  0.000  0.000  0.001  0.001  0.005  0.019  3160    0    0    0   294218120      93107      93107      93107  GET /js/jquery.min.js HTTP/1.1
 3160    1.462  0.0005  0.0010  0.000  0.000  0.001  0.001  0.005  0.011  3160    0    0    0   335007400     106015     106015     106015  GET /css/bootstrap.min.css HTTP/1.1
  490    1.301  0.0027  0.0022  0.001  0.002  0.004  0.006  0.013  0.028     0  490    0    0           0          0          0          0  POST /signin HTTP/1.1
  490    1.273  0.0026  0.0024  0.001  0.002  0.004  0.006  0.014  0.030     0  490    0    0           0          0          0          0  POST /memo HTTP/1.1
 3160    1.267  0.0004  0.0010  0.000  0.000  0.001  0.001  0.004  0.017  3160    0    0    0    53242840      16849      16849      16849  GET /css/bootstrap-responsive.min.css HTTP/1.1
   14    1.254  0.0896  0.0300  0.052  0.086  0.139  0.139  0.139  0.139    14    0    0    0      202826      14116      14487      15061  GET /recent/139 HTTP/1.1
 3160    1.233  0.0004  0.0009  0.000  0.000  0.001  0.001  0.004  0.011  3160    0    0    0    90473960      28631      28631      28631  GET /js/bootstrap.min.js HTTP/1.1
   16    1.195  0.0747  0.0152  0.053  0.079  0.097  0.100  0.100  0.100    16    0    0    0      231989      14152      14499      14858  GET /recent/160 HTTP/1.1
   15    1.108  0.0739  0.0245  0.048  0.069  0.114  0.148  0.148  0.148    15    0    0    0      210828      13637      14055      14454  GET /recent/94 HTTP/1.1
   16    1.047  0.0654  0.0161  0.041  0.062  0.091  0.095  0.095  0.095    16    0    0    0      230838      14122      14427      14847  GET /recent/61 HTTP/1.1
   15    0.990  0.0660  0.0154  0.043  0.064  0.089  0.101  0.101  0.101    15    0    0    0      201854      13254      13456      13666  GET /recent/66 HTTP/1.1
   14    0.989  0.0706  0.0147  0.049  0.071  0.097  0.101  0.101  0.101    14    0    0    0      192906      13522      13779      13981  GET /recent/102 HTTP/1.1
   12    0.975  0.0812  0.0219  0.058  0.076  0.116  0.135  0.135  0.135    12    0    0    0      165913      13338      13826      14448  GET /recent/189 HTTP/1.1
   11    0.974  0.0885  0.0175  0.056  0.090  0.110  0.113  0.113  0.113    11    0    0    0      150226      13408      13656      13797  GET /recent/193 HTTP/1.1

cureseven · 2021-05-30T08:20:09Z

N+1解消で

Result:   SUCCESS
RawScore: 4765.2
Fails:    0
Score:    4765.2

kataribe

Top 20 Sort By Total
Count    Total    Mean  Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max   2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
 3289  124.379  0.0378  0.0389  0.000  0.031  0.065  0.080  0.121  0.633  3055    0  234    0    12194284         10       3707      57920  get memo
 1210   87.959  0.0727  0.0221  0.036  0.068  0.104  0.117  0.139  0.194  1210    0    0    0    17869460      13478      14768      15842  GET / HTTP/1.1
   89   10.250  0.1152  0.0314  0.052  0.115  0.162  0.174  0.205  0.205    89    0    0    0     1220589      13166      13714      13975  GET /recent/205 HTTP/1.1
   77    9.191  0.1194  0.0362  0.054  0.115  0.169  0.188  0.242  0.242    77    0    0    0     1072994      13604      13934      14136  GET /recent/204 HTTP/1.1
  481    6.205  0.0129  0.0150  0.000  0.007  0.033  0.046  0.073  0.089   481    0    0    0     7395289       1920      15374      28610  GET /mypage HTTP/1.1
 3117    1.770  0.0006  0.0013  0.000  0.000  0.001  0.002  0.007  0.018  3117    0    0    0   330448755     106015     106015     106015  GET /css/bootstrap.min.css HTTP/1.1
 3117    1.671  0.0005  0.0013  0.000  0.000  0.001  0.002  0.006  0.026  3117    0    0    0   290214519      93107      93107      93107  GET /js/jquery.min.js HTTP/1.1
   10    1.594  0.1594  0.2301  0.047  0.073  0.840  0.840  0.840  0.840    10    0    0    0      136589      13496      13658      13878  GET /recent/166 HTTP/1.1
 3117    1.570  0.0005  0.0013  0.000  0.000  0.001  0.002  0.006  0.026  3117    0    0    0    89242827      28631      28631      28631  GET /js/bootstrap.min.js HTTP/1.1
 3117    1.521  0.0005  0.0012  0.000  0.000  0.001  0.002  0.007  0.018  3117    0    0    0    52518333      16849      16849      16849  GET /css/bootstrap-responsive.min.css HTTP/1.1
  481    1.379  0.0029  0.0028  0.001  0.002  0.005  0.008  0.013  0.035     0  481    0    0           0          0          0          0  POST /memo HTTP/1.1
  481    1.322  0.0027  0.0021  0.001  0.002  0.004  0.007  0.013  0.023     0  481    0    0           0          0          0          0  POST /signin HTTP/1.1
   13    1.222  0.0940  0.0299  0.066  0.077  0.128  0.167  0.167  0.167    13    0    0    0      183454      13756      14111      14384  GET /recent/195 HTTP/1.1
   17    1.177  0.0692  0.0204  0.044  0.066  0.099  0.118  0.118  0.118    17    0    0    0      236794      13568      13929      14264  GET /recent/137 HTTP/1.1
   14    1.175  0.0839  0.0363  0.050  0.075  0.113  0.198  0.198  0.198    14    0    0    0      195015      13480      13929      14496  GET /recent/175 HTTP/1.1
   13    1.172  0.0902  0.0317  0.051  0.091  0.107  0.178  0.178  0.178    13    0    0    0      180309      13562      13869      14106  GET /recent/172 HTTP/1.1
   15    1.159  0.0773  0.0258  0.046  0.069  0.112  0.136  0.136  0.136    15    0    0    0      211556      13869      14103      14278  GET /recent/148 HTTP/1.1
   13    1.130  0.0869  0.0425  0.051  0.076  0.168  0.181  0.181  0.181    13    0    0    0      184580      13931      14198      14564  GET /recent/184 HTTP/1.1
    9    1.025  0.1139  0.0412  0.051  0.115  0.208  0.208  0.208  0.208     9    0    0    0      125275      13730      13919      14159  GET /recent/178 HTTP/1.1
   12    1.024  0.0853  0.0231  0.054  0.089  0.123  0.127  0.127  0.127    12    0    0    0      168048      13739      14004      14286  GET /recent/201 HTTP/1.1

cureseven · 2021-05-30T08:29:49Z

index見直し．

where句だけに貼っていたけど，order by句に使っているカラムとの複合INDEXにした．
下の方の順位のクエリのindexも追加．

sudo mysql -u root isucon -e "CREATE INDEX idx_memos_is_private_created_at ON memos (is_private, created_at);"
sudo mysql -u root isucon -e "CREATE INDEX idx_memos_user_created_at ON memos (user, created_at);"
sudo mysql -u root isucon -e "CREATE INDEX idx_memos_is_private ON memos (is_private);"
sudo mysql -u root isucon -e "CREATE INDEX idx_memos_user_is_private_created_at ON memos (user, is_private, created_at);"

Result:   SUCCESS
RawScore: 6074.8
Fails:    0
Score:    6074.8

cureseven · 2021-05-30T09:00:21Z

dbConnPoolSizeあげてみた

Result:   SUCCESS
RawScore: 6104.5
Fails:    0
Score:    6104.5

cureseven · 2021-05-30T09:02:36Z

netdata stopしようとした

$ systemctl stop netdata
Failed to stop netdata.service: The name org.freedesktop.PolicyKit1 was not provided by any .service files
See system logs and 'systemctl status netdata.service' for details.

netdata killしたのに止まらなかった

$ systemctl is-enabled netdata
disabled

make restartしたらまた復活してしまう

slow-query-log止めた

/etc/my.cnf を修正した

cureseven · 2021-05-30T09:06:46Z

最終スコア

workload 1

Result:   SUCCESS
RawScore: 5924.7
Fails:    0
Score:    5924.7

Result:   SUCCESS
RawScore: 6020.9
Fails:    0
Score:    6020.9

Result:   SUCCESS
RawScore: 5959.8
Fails:    0
Score:    5959.8

workload2

Result:   SUCCESS
RawScore: 7859.2
Fails:    0
Score:    7859.2

workload3

Result:   SUCCESS
RawScore: 8543.1
Fails:    0
Score:    8543.1

workload4

Result:   SUCCESS
RawScore: 8655.4
Fails:    0
Score:    8655.4

workload5

Result:   SUCCESS
RawScore: 8566.9
Fails:    0
Score:    8566.9

cureseven · 2021-05-30T09:23:36Z

netdata止めれた

workload 4

Result:   SUCCESS
RawScore: 8838.4
Fails:    0
Score:    8838.4

cureseven mentioned this issue May 30, 2021

feat:動的プレースホルダ #2

Merged

cureseven mentioned this issue May 30, 2021

recentのusername取得 N+1解消 #3

Merged

cureseven closed this as completed Jul 17, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

1人で頑張るISUCON練習 #1

1人で頑張るISUCON練習 #1

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021

cureseven commented May 30, 2021

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021

cureseven commented May 30, 2021

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

1人で頑張るISUCON練習 #1

1人で頑張るISUCON練習 #1

Comments

cureseven commented May 30, 2021 • edited

cureseven commented May 30, 2021

git管理

cureseven commented May 30, 2021

cureseven commented May 30, 2021 • edited

db

memos

users

cureseven commented May 30, 2021

pt-query-digest

cureseven commented May 30, 2021 • edited

memos.is_privateにINDEX

cureseven commented May 30, 2021 • edited

cureseven commented May 30, 2021 • edited

2つのINDEX入れた結果

pt-query-digest

kataribe

top

考察

cureseven commented May 30, 2021 • edited

動的プレースホルダで

cureseven commented May 30, 2021 • edited

N+1解消で

cureseven commented May 30, 2021 • edited

index見直し．

cureseven commented May 30, 2021

dbConnPoolSizeあげてみた

cureseven commented May 30, 2021

netdata stopしようとした

slow-query-log止めた

cureseven commented May 30, 2021 • edited

最終スコア

cureseven commented May 30, 2021 • edited

netdata止めれた

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited

cureseven commented May 30, 2021 •

edited