初期状態

[ue-sho]

さくらのVPS 1台

• 東京
  • SSD: 100GB
  • 仮想3Core: 2GB メモリ

ベンチマーカーも同じ場所に配置。

[ue-sho]

mysql> show tables;
+-------------------+
| Tables_in_isuconp |
+-------------------+
| comments          |
| posts             |
| users             |
+-------------------+
3 rows in set (0.01 sec)

[ue-sho]

mysql> show columns from comments;
+------------+-----------+------+-----+-------------------+-------------------+
| Field      | Type      | Null | Key | Default           | Extra             |
+------------+-----------+------+-----+-------------------+-------------------+
| id         | int       | NO   | PRI | NULL              | auto_increment    |
| post_id    | int       | NO   |     | NULL              |                   |
| user_id    | int       | NO   |     | NULL              |                   |
| comment    | text      | NO   |     | NULL              |                   |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-----------+------+-----+-------------------+-------------------+
5 rows in set (0.02 sec)

mysql> show columns from posts;
+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| id         | int         | NO   | PRI | NULL              | auto_increment    |
| user_id    | int         | NO   |     | NULL              |                   |
| mime       | varchar(64) | NO   |     | NULL              |                   |
| imgdata    | mediumblob  | NO   |     | NULL              |                   |
| body       | text        | NO   |     | NULL              |                   |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+-------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)

mysql> show columns from users;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field        | Type         | Null | Key | Default           | Extra             |
+--------------+--------------+------+-----+-------------------+-------------------+
| id           | int          | NO   | PRI | NULL              | auto_increment    |
| account_name | varchar(64)  | NO   | UNI | NULL              |                   |
| passhash     | varchar(128) | NO   |     | NULL              |                   |
| authority    | tinyint(1)   | NO   |     | 0                 |                   |
| del_flg      | tinyint(1)   | NO   |     | 0                 |                   |
| created_at   | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.01 sec)

[ue-sho]

mysql> show create table comments;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                            |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `post_id` int NOT NULL,
  `user_id` int NOT NULL,
  `comment` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100007 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[ue-sho]

mysql> show create table posts;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| posts | CREATE TABLE `posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int 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=10005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[ue-sho]

mysql> show create table users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account_name` varchar(64) NOT NULL,
  `passhash` varchar(128) NOT NULL,
  `authority` tinyint(1) NOT NULL DEFAULT '0',
  `del_flg` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `account_name` (`account_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1011 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[ue-sho]

サーバースペック

ubuntu@xxx:~$ grep physical.id /proc/cpuinfo
physical id : 0
physical id : 1
physical id : 2

ubuntu@xxx:~$ grep cpu.cores /proc/cpuinfo
cpu cores   : 1
cpu cores   : 1
cpu cores   : 1

ubuntu@xxx:~$ grep processor /proc/cpuinfo
processor   : 0
processor   : 1
processor   : 2

ubuntu@xxx:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           1.9Gi       711Mi       161Mi       0.0Ki       1.1Gi       1.0Gi
Swap:             0B          0B          0B

[ue-sho]

Go言語に切り替え

https://github.com/ue-sho/private-isu/blob/master/manual.md

[ue-sho]

初回ベンチ

ubuntu@xxx:~$ /home/isucon/private_isu/benchmarker/bin/benchmarker -u /home/isucon/private_isu/benchmarker/userdata -t http://xxx.xxx.xxx.xxx
{"pass":true,"score":2465,"success":2376,"fail":0,"messages":[]}

[ue-sho]

↑ ピーク時

• ホスト全体で使えるCPU3コアのうち、98％程度が使用されている
• MySQLのプロセス（mysqld）がCPUを291％（3コア）程度使用している
• WebアプリケーションのプロセスはCPUを4％、Webサーバーのプロセス（nginx）は0.3%程度しか使用していない