pixiv private isucon 2016 鐃緒申略 (1/5)
鐃緒申略鐃緒申鐃緒申鐃緒申鐃緒申:
pixiv 鐃緒申鐃藷が種申鐃緒申燃鐃緒申鼎鐃緒申鐃緒申廛薀わ申戞鐃緒申鐃� ISUCON 鐃緒申 AMI 鐃緒申鐃緒申鐃緒申鐃緒申討鐃緒申譴随申里如鐃緒申鐃緒申鐃縦わ申鐃淑わ申鐃初攻略鐃緒申鐃銃わ申鐃緒申鐃殉わ申鐃緒申
鐃緒申鐃塾居申鐃緒申鐃緒申鐃出常申鐃宿者わ申鐃緒申鐃緒申撚鐃緒申鬚垢鐃出わ申鐃緒申鐃緒申鐃緒か鐃緒申覆鐃緒申覆辰討鐃緒申泙鐃� ISUCON 鐃初心鐃峻でわ申鐃緒申 Go 鐃緒申鐃緒申鐃術わ申鐃銃刻申略鐃緒申鐃銃わ申鐃緒申鐃殉わ申鐃緒申鐃緒申他鐃塾醐申鐃緒申濃鐃緒申辰鐃緒申鐃緒申鐃叔わ申佑鐃緒申鐃緒申覆匹六鐃緒申佑砲覆鐃夙思わ申鐃殉わ申鐃緒申
鐃緒申鐃緒申造僚鐃緒申鐃緒申鐃緒申
ssh 鐃塾醐申鐃緒申鐃緒申鐃緒申鐃緒申鐃曙た鐃所、鐃緒申鐃叔ワ申鐃緒申鐃緒申鐃緒申 git 鐃淑どのツ￥申鐃緒申鐃緒申鬟ぅ鵐好函鐃緒申襦�鐃緒申鐃所し鐃殉わ申鐃緒申
鐃緒申鐃塾わ申鐃緒申鐃緒申六鐃緒申鐃緒申房鐃淑�鐃術のワ申鐃緒申鐃夙ワ申鐃緒申鐃夙やス鐃緒申鐃緒申廛箸鐃緒申辰董鐃緒申鐃緒申瓩�鐃叔わ申鐃緒申茲�鐃祝わ申鐃銃わ申鐃緒申鐃殉わ申鐃順う鐃緒申
鐃殉ワ申鐃緒申調鐃緒申
鐃緒申鐃准ッワ申
鐃夙りあ鐃緒申鐃緒申 CPU 鐃夙ワ申鐃所く鐃初い鐃熟わ申鐃緒申鐃緒申鐃縦逸申鐃緒申鐃銃わ申鐃緒申鐃殉わ申鐃順う鐃緒申
$ grep processor /proc/cpuinfo
processor : 0
processor : 1
$ free -m
total used free shared buffers cached
Mem: 998 906 92 12 25 335
-/+ buffers/cache: 545 453
Swap: 0 0 0
CPU鐃緒申2鐃緒申鐃緒申鐃叔わ申鐃粛￥申鐃緒申鐃緒申魯函鐃緒申鐃緒申鐃�1GB鐃叔ワ申鐃緒申奪廚覆鐃緒申鐃緒申鐃緒申鐃緒申佞任鐃緒申鐃�
ps aux
鐃緒申鐃緒申動鐃緒申鐃銃わ申鐃暑か鐃緒申認鐃緒申鐃殉わ申鐃緒申 鐃潤ー鐃暑サ鐃緒申鐃出￥申鐃夙わ申鐃緒申AppArmor 鐃塾よう鐃淑￥申鐃緒申鐃緒申鐃緒申鐃緒申鐃初か鐃緒申鐃緒申鐃竣わ申鐃緒申鐃淑デ￥申鐃緒申鐃熟￥申鐃駿ワ申鐃緒申沺鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃所さ鐃緒申鐃暑た鐃緒申肇鐃緒申鐃緒申鐃緒申鐃緒申里鐃緒申鐃緒申鐃緒申箸鐃緒申討鐃緒申鐃緒申鐃緒申曚鐃緒申鐃緒申匹鐃緒申任鐃緒申鐃�
鐃緒申鐃緒申鐃緒申辰鵬鐃緒申發刻申鐃宿�鐃竣はわ申鐃緒申泙鐃緒申鐃叔わ申鐃緒申鐃緒申
鐃緒申鐃竣ワ申了鐃緒申鐃緒申聴鐃�
鐃緒申鐃緒申鐃緒申屬鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃叔э申鐃緒申討鐃緒申蕁�鐃渋際にワ申鐃竣ワ申鐃瞬ラウ鐃緒申鐃緒申鐃初さ鐃緒申辰討澆泙鐃緒申腓�鐃緒申
鐃緒申鐃緒申鐃緒申屬悗鐃緒申鐃緒申鐃緒申蓮鐃� tar 鐃叔ワ申鐃緒申鐃緒申鐃緒申鐃瞬わ申鐃銃ワ申鐃緒申鐃緒申鐃緒申忙鐃緒申辰討鐃緒申討鐃緒申鐃緒申鐃緒申蝓� mysqldump 鐃緒申鐃獣わ申鐃所し鐃銃わ申鐃緒申鐃緒申鐃宿わ申鐃叔わ申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申両鐃緒申鐃�AMI鐃緒申鐃緒申鐃緒申直鐃緒申鐃緒申里如鐃� /initialize 鐃夙わ申鐃緒申鐃術ワ申鐃叔緒申鐃緒申鐃緒申鐃緒申鐃緒申噺鐃緒申鐃緒申鐃緒申箸鐃緒申鐃緒申鐃緒申廛鐃塾ワ申鐃緒申鐃宿で鰹申認鐃緒申鐃緒申鐃出ッワ申鐃緒申鐃獣プは種申抜鐃緒申鐃緒申鐃殉わ申鐃緒申鐃緒申
ISUCON 鐃緒申鐃瞬では緒申鐃緒申鐃緒申屬鐃緒申鐃緒申鐃緒申鐃緒申鐃塾わ申必鐃竣なデ￥申鐃緒申鐃薯オペミワ申鐃叔種申鐃緒申鐃緒申即鐃準す鐃暑恐鐃曙が鐃緒申鐃緒申里鐃緒申鐃緒申佞鐃緒申泙鐃緒申腓�鐃緒申
鐃瞬ラウ鐃緒申鐃塾デバッワ申鐃緒申能鐃叔どわ申鐃緒申鐃緒申鐃縮随申鐃薯してわ申鐃緒申里鐃緒申鐃緒申鐃緒申蝓�鐃緒申鐃竣ワ申離鐃緒申鐃緒申匹鐃�URL鐃暑ー鐃銃ワ申鐃緒申鐃緒申分鐃緒申鐃醇心鐃祝軽わ申鐃旬わ申鐃縮わ申鐃緒申鐃所、 mysqldump --no-data isuconp 鐃叔種申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃殉を参考に￥申鐃緒申鐃獣わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申聴鐃緒申鐃緒申討鐃緒申鐃緒申泙鐃緒申鐃�
nginx 鐃塾器申鐃緒申鐃緒申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申儖佞鐃緒申討鐃緒申鐃緒申鐃緒申侫鐃緒申鐃緒申泪奪箸鐃緒申儿鐃緒申鐃緒申董鐃� upstream 鐃舜わ申 keepalive 鐃緒申鐃春わ申鐃暑。
鐃緒申鐃緒申鐃緒申鐃緒申鐃淑わ申鐃塾わ申 worker_processes 鐃緒申 1 鐃祝わ申鐃銃￥申 keepalive 鐃塾醐申率鐃緒申紊駕申襦�
鐃緒申的鐃春ワ申鐃緒申鐃緒申鐃� nginx 鐃緒申鐃緒申鐃瞬わ申鐃緒申鐃緒申蓮鐃緒申鐃緒申廛鐃緒申動鐃緒申鐃緒申聴鐃緒申鐃緒申討鐃緒申鐃叔わ申鐃駿わ申鐃淑わ申鐃緒申 Go 鐃淑鰹申鐃緒申箸鐃緒申佑蓮鐃緒申鐃緒申鐃緒申鐃緒申鐃重�鐃淑フワ申鐃緒申鐃暑が分鐃緒申鐃獣わ申鐃淑鰹申鐃叔わ申鐃緒申鐃祝￥申Go 鐃緒申箸鐃緒申佑呂鐃緒申鐃緒申鐃緒申戰鐃緒申紂種申縫鵐阿鐃緒申覆鐃緒申 nginx 鐃薯外わ申鐃緒申鐃宿わ申鐃緒申鐃緒申判鐃叔わ申鐃暑タ鐃緒申鐃淳ングわ申 nginx 鐃緒申鐃緒申鐃瞬わ申鐃処う鐃祝わ申鐃処う鐃緒申
/etc/nginx/nginx.conf:
worker_processes 1;
...
##
# Logging Settings
##
log_format isucon '$time_local $msec\t$status\treqtime:$request_time\t'
'in:$request_length\tout:$bytes_sent\trequest:$request\t'
'acceptencoding:$http_accept_encoding\treferer:$http_referer\t'
'ua:$http_user_agent';
access_log /var/log/nginx/access.log isucon;
/etc/nginx/sites-enabled/isucon.conf:
upstream app {
server 127.0.0.1:8080;
keepalive 32;
}
server {
listen 80;
client_max_body_size 10m;
root /home/isucon/private_isu/webapp/public/;
location / {
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_pass http://app;
}
}
鐃緒申鐃夙￥申鐃緒申鐃緒申鐃� Go 鐃緒申箸鐃緒申里鐃� ruby 鐃緒申 php 鐃塾ワ申鐃緒申鐃俊ワ申鐃緒申澆鐃銃わ申鐃殉わ申鐃殉わ申鐃緒申 Go 鐃塾ワ申鐃緒申鐃俊ワ申鐃熟鰹申発鐃緒申魯鐃緒申泪鐃宿ライ鐃緒申乃鐃銃逸申鐃緒申鐃緒申鐃緒申里任鐃緒申鐃緒申任鐃緒申鐃緒申蠅件申泙鐃緒申鐃�
$ sudo systemctl stop isu-ruby $ sudo systemctl disable isu-ruby $ sudo systemctl stop php7.0-fpm $ sudo systemctl disable php7.0-fpm
MySQL 鐃塾器申鐃緒申鐃緒申鐃緒申
/etc/mysql/my.cnf 鐃薯見てみまわ申鐃緒申鐃循ぼデワ申鐃緒申鐃夙ワ申離妊侫鐃緒申鐃夙で使ってわ申鐃緒申茲�鐃淑ので￥申鐃緒申鐃緒申鐃緒申鐃緒申椋鏝削申鐃�IO鐃峻わ申鐃緒減鐃塾わ申鐃緒申房鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃宿加わ申鐃銃わ申鐃緒申鐃殉わ申鐃緒申
innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2
鐃重わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃潤く鐃緒申鐃縦わ申鐃暑た鐃緒申離帖鐃緒申鐃緒申鐃術意わ申鐃暑。鐃粛の常申鐃緒申 myprofiler 鐃緒申箸鐃緒申鐃�
$ wget https://github.com/KLab/myprofiler/releases/download/0.1/myprofiler.linux_amd64.tar.gz $ tar xf myprofiler.linux_amd64.tar.gz $ sudo mv myprofiler /usr/local/bin/
myprofiler 鐃塾使わ申鐃緒申鐃熟￥申 myprofiler -user=root 鐃叔わ申鐃緒申鐃緒申鐃緒申鐃所が鐃緒申鐃獣てわ申鐃緒申鐃緒申 myprofiler -user=root -interval=0.2 鐃緒申鐃初い鐃祝ワ申鐃緒申廛鐃藷グ間隔わ申短鐃緒申鐃緒申鐃緒申鐃緒申匹鐃緒申任鐃緒申腓�鐃緒申鐃緒申鐃緒申奮鐃緒申離鐃緒申廛鐃緒申鐃緒申鐃� myprofiler -h 鐃叔鰹申認鐃緒申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申
鐃駿ワ申鐃緒申沺鐃緒申鐃緒申亮孫圓箸鐃緒申辰鐃緒申鐃緒申鐃緒申鐃縦逸申
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":4745,"success":4408,"fail":0,"messages":[]}
top:
Tasks: 81 total, 1 running, 80 sleeping, 0 stopped, 0 zombie %Cpu(s): 94.1 us, 4.2 sy, 0.0 ni, 0.3 id, 0.0 wa, 0.0 hi, 1.3 si, 0.0 st KiB Mem: 1022972 total, 701036 used, 321936 free, 21396 buffers KiB Swap: 0 total, 0 used, 0 free. 346564 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 720 mysql 20 0 1087768 140276 10724 S 142.8 13.7 0:52.09 mysqld 2908 isucon 20 0 582876 124364 8800 S 46.6 12.2 0:12.98 app 2904 www-data 20 0 91680 4800 2988 S 1.7 0.5 0:00.61 nginx
MySQL 鐃緒申 app 鐃緒申3鐃緒申CPU鐃緒申鐃獣てわ申里鐃緒申錣�鐃緒申泙鐃緒申鐃�
myprofiler:
198 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N 124 SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N 11 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC 9 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC 5 SELECT * FROM `posts` WHERE `id` = N 4 SELECT COUNT(*) AS count FROM `comments` WHERE `post_id` IN (...N) 2 SELECT * FROM `users` WHERE `id` = ? 2 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC 1 SELECT * FROM users WHERE account_name = S AND del_flg = N 1 SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N
comments 鐃緒申 SELECT 鐃緒申 COUNT 鐃緒申鐃駿わ申鐃緒申鐃緒申鐃緒か鐃暑。鐃緒申鐃塾種申鐃緒申 posts 鐃緒申 select鐃緒申
鐃緒申鐃瞬最緒申離鐃緒申紂種申縫鐃�: 鐃緒申鐃緒申妊奪鐃緒申鐃�
鐃緒申鐃�3鐃緒申離鐃緒申鐃緒申鐃祝つわ申鐃銃￥申鐃緒申鐃緒申妊奪鐃緒申鐃緒申鐃粛�鐃暑か鐃緒申認鐃緒申鐃銃￥申鐃淑わ申鐃緒申鐃縦ワ申辰討鐃緒申鐃緒申鐃�
mysql> show create table comments\G
*************************** 1. row ***************************
Table: comments
Create Table: CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`comment` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100049 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> show create table posts\G
*************************** 1. row ***************************
Table: posts
Create Table: CREATE TABLE `posts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`mime` varchar(64) NOT NULL,
`imgdata` mediumblob NOT NULL,
`body` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10056 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
comments.post_id 鐃緒申 posts.created_at 鐃祝ワ申鐃緒申妊奪鐃緒申鐃緒申鐃緒申覆鐃緒申鐃緒申鼎鐃緒申鐃緒申鐃緒申鐃緒申鐃�3鐃縦わ申鐃緒申鐃曙。
mysql> create index post_id on comments (post_id); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index created_at on posts (created_at); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
鐃夙りあ鐃緒申鐃緒申鐃緒申鐃緒申任發�鐃緒申鐃駿件申測鐃緒申鐃銃わ申鐃緒申鐃殉わ申鐃緒申
top:
top - 18:13:32 up 2:13, 1 user, load average: 2.01, 0.84, 0.52 Tasks: 79 total, 1 running, 78 sleeping, 0 stopped, 0 zombie %Cpu(s): 74.0 us, 19.3 sy, 0.0 ni, 3.2 id, 0.2 wa, 0.0 hi, 3.4 si, 0.0 st KiB Mem: 1022972 total, 944288 used, 78684 free, 23344 buffers KiB Swap: 0 total, 0 used, 0 free. 462656 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2908 isucon 20 0 640148 174444 8800 S 109.7 17.1 0:44.93 app 720 mysql 20 0 1093212 212252 10740 S 58.5 20.7 1:56.57 mysqld
app 鐃緒申 mysql 鐃緒申 2鐃旬になり、鐃緒申鐃緒申鐃所が鐃粛ッワ申鐃緒申鐃緒申覆鐃緒申覆辰鐃緒申里鐃緒申錣�鐃緒申泙鐃緒申鐃�
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":14208,"success":13157,"fail":0,"messages":[]}
myprofiler:
36 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC 15 SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = N 5 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N 4 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC
鐃緒申鐃�2鐃緒申鮓�わ申鐃�
posts鐃緒申鐃緒申鐃� SELECT 鐃緒申 LIMIT 鐃緒申無鐃緒申comments.user_id鐃祝ワ申鐃緒申妊奪鐃緒申鐃緒申鐃緒申覆鐃�
LIMIT 鐃緒申鐃淑わ申鐃塾はワ申鐃竣ワ申側鐃叔緒申鐃緒申鐃緒申必鐃竣なのと￥申鐃旬トワ申優奪鐃緒申鐃緒申鐃緒申廛鐃祝移ってわ申里如鐃緒申鐃緒申廛鐃緒申鐃緒申鐃祝移る。
comments.user_id 鐃熟つわ申鐃叔にワ申鐃緒申妊奪鐃緒申鐃縦ワ申辰討鐃緒申鐃緒申鐃緒申鐃�1鐃瞬重わ申鐃緒申鐃緒申鐃所じ鐃緒申覆鐃緒申里妊戰鐃緒申鐃熟常申略鐃緒申
mysql> create index user_id on comments (user_id); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0
鐃殉とわ申
鐃緒申鐃緒申鐃緒申: 4745 (鐃緒申鐃緒申鐃緒申鐃�) -> 14208 (鐃緒申鐃緒申妊奪鐃緒申鐃緒申鐃緒申紂種申縫鐃�)
systemd 鐃塾ワ申鐃殉ワ申匹箸鐃� nginx 鐃緒申鐃緒申鐃緒申箸鐃� MySQL 鐃緒申鐃緒申鐃緒申侫鐃緒申鐃緒申鐃薯見わ申箸鐃緒申鐃緒申鐃叔ッワ申鐃緒申張鐃緒申箸鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃銃￥申鐃緒申鐃緒申鐃殉ではワ申鐃準ー鐃緒申鐃祝でわ申鐃緒申茲�鐃祝なわ申泙鐃緒申腓�鐃緒申
鐃緒申鐃緒申鐃粛でわ申鐃緒申鐃緒申鐃緒申佑任鐃獣てわ申鐃緒申呂鐃緒申鐃緒申泙任鐃�2鐃緒申鐃瞬逸申鐃緒申砲禄鐃緒申鐃初せ鐃緒申鐃緒申茲�鐃祝わ申鐃殉わ申鐃順う鐃緒申1鐃緒申鐃瞬わ申鐃准わ申鐃出ワ申鐃緒申鐃緒申鐃叔わ申鐃緒申
@methane
