[ISUCON用メモ] MySQL

## インストール 
### Ubuntu
```shell-session
$ sudo apt-get install -y mysql-server
```

### Mac

```shell-session
$ brew update && brew install mysql
```

## ログイン

`mysql -h[host] -P[port] -u[user_name] -p[password] ([database])`

```shell-session
$ mysql -h0.0.0.0 -P3306 -uisucon -pisucon isuketch
```

localhost なら

```shell-session
$ mysql -uroot
```

### ユーザー作成

```sql
CREATE USER 'isucon'@'localhost' IDENTIFIED BY 'isucon';
GRANT ALL PRIVILEGES ON *.* TO 'isucon'@'localhost' WITH GRANT OPTION;
```

## 設定ファイル
パス

```
$ mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
```
左から順に読み込まれる。


## コマンド
### database 一覧
`show databases`

```sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isuconp            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
```

### table 一覧

`show tables`

```sql

mysql> use isuconp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_isuconp |
+-------------------+
| comments          |
| posts             |
| users             |
+-------------------+
3 rows in set (0.00 sec)
```

### schema 確認

`describe <table>`

```sql
mysql> describe comments;
+------------+-----------+------+-----+-------------------+----------------+
| Field      | Type      | Null | Key | Default           | Extra          |
+------------+-----------+------+-----+-------------------+----------------+
| id         | int(11)   | NO   | PRI | NULL              | auto_increment |
| post_id    | int(11)   | NO   |     | NULL              |                |
| user_id    | int(11)   | NO   |     | NULL              |                |
| comment    | text      | NO   |     | NULL              |                |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-----------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

```

### データ量確認
```
SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
```

```sql

mysql>  SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+---------------+--------+------------+----------------+-------+------+------+
| table_name    | engine | table_rows | avg_row_length | allMB | dMB  | iMB  |
+---------------+--------+------------+----------------+-------+------+------+
| points        | InnoDB |    1439020 |             48 |    97 |   66 |   30 |
| tokens        | InnoDB |      49856 |            116 |    12 |    5 |    6 |
| room_watchers | InnoDB |      53519 |            108 |     8 |    5 |    2 |
| strokes       | InnoDB |      41184 |             64 |     4 |    2 |    1 |
| rooms         | InnoDB |       1000 |            131 |     0 |    0 |    0 |
| room_owners   | InnoDB |       1002 |             65 |     0 |    0 |    0 |
+---------------+--------+------------+----------------+-------+------+------+
6 rows in set (0.00 sec)
```

### index 確認

`show index from <table>`

```sql
mysql> show index from posts;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| posts |          0 | PRIMARY    |            1 | id          | A         |        9382 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | user_id    |            1 | user_id     | A         |        1876 |     NULL | NULL   |      | BTREE      |         |               |
| posts |          1 | created_at |            1 | created_at  | A         |         180 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

```
cardinality？


### index を追加

`alter table <table_name> add index <index_name> (<column_name>);`
OR `create index <index_name> on <table_name> (<column_name>);`

```sql
mysql> alter table comments add index post_id (post_id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

複合 index は `column_name` をコンマ区切りで複数指定する。

```sql
mysql> alter table comments add index index_post_id_and_created_at (post_id, created_at);
```


### index を削除

`alter table <table_name> drop index <index_name>`


## dump と restore


### 基本
#### dump
`mysqldump -u[user] -p[password] [dbname] > dbname.sql`

```shell-session
$ mysqldump -u user -p password dbname > dbname.sql
```

#### restore
`mysql -u[user] -p[password] < dbname.sql`

```shell-session
$ mysql -u user -p password < dbname.sql
```

dump 時は `--single-transaction` `--quick` などのオプションを付けるといいらしい。
https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c

### gzip で dump & restore する

#### dump

```shell-session
$ mysqldump -u user -p password dbname | gzip > dbname.dump.gz 
```

#### restore

```shell-session
$ zcat dbname.dump.gz | mysql -u user -p password dbname 

# mac なら gzcat を使う
$ gzcat dbname.dump.gz | mysql -u user -p password dbname 
```

データが大きい場合は restore で `max_allowed_packet` に引っかかる場合がある。設定で大きくしておくとよい（デフォルトは 4MB)

```
[mysqld]
max_allowed_packet=160M
```


## チューニング
my.cnf を書き換えたら MySQL サーバーを再起動する。
ローカルなら `mysql.server restart`

### [innodb_buffer_pool_size](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size)

- デフォルトは 128MB
- マシンのメモリサイズの ~75% 程度が目安。マシンのメモリサイズは `free -h` で確認。
    - Redis などを使う場合はそちらと相談

```
[mysqld]
innodb_buffer_pool_size=1G
```

- OSのファイルシステムとのダブルキャッシュを避けるため、以下にするといい。

```
innodb_flush_method=O_DIRECT
```

### [innodb_buffer_pool_instances](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances)

- innodb_buffer_pool_size > 1GB のとき、デフォルトは 8
- 増やすと concurrency が増す
- メモリが潤沢にある場合、instance 当りの buffer size が 1GB 程度になるように設定するといい

### [innodb_log_buffer_size](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size)

- デフォルトは 8MB or 16MB
- 1つの transaction で大量の row を insert, update, delete するケースがある場合には大きくしておくといい。

### [innodb_log_file_size](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size)

- デフォルトは 48MB
- innodb_log_file_size * innodb_log_files_in_group (デフォルトは 2 ) が実際の log file 全体のサイズ
- log file は固定領域を使い回す。書き込もうとして空き領域がないとき、ダーティーページが残っているとそれを flush する必要が生じて遅くなる。
- buffer_pool_size と同様ある程度大きくしておくべき

```
[mysqld]
innodb_log_file_size = 512M
```

### [innodb_flush_log_at_trx_commit](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit) 

- ログ書き込みのタイミングを制御

| 値 | log file への書き込み | disk への flush |
|:---|:---:|:---:|
| 0 | 1秒に1回 | 1秒に1回 | 
| 1 (default) | commit ごと | commit ごと |
| 2 | commit ごと | 1秒に1回 | 

- MySQL プロセスやマシンのクラッシュ時のデータの永続性が担保されるためには 1 であるべき
- ただ ISUCON に関しては本番のサービスと違ってそこまで厳密な永続性は求められないので 0, 2 にしたほうがパフォーマンスが上がる

```
[mysqld]
innodb_flush_log_at_trx_commit=2
```


## slow query log 解析

### 設定変数

```
slow_query_log=ON
long_query_time=0
```

出力ファイルのパスは `slow_query_log_file`


### [pt-query-digest](https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html) のインストール
https://github.com/percona/percona-toolkit/releases から最新のソースコードをダウンロード

```sh

$ wget https://github.com/percona/percona-toolkit/archive/3.0.5-test.tar.gz
$ tar zxvf 3.0.5-test.tar.gz
$ ./percona-toolkit-3.0.5-test/bin/pt-query-digest --version
pt-query-digest 3.0.2
```

### 走らせる
```
$ ./percona-toolkit-3.0.5-test/bin/pt-query-digest **-slow.log > slow-log.txt
```

見方等 https://thinkit.co.jp/article/9617


## Timezone の設定

初期状態

```sql
mysql> show variables like '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
```

### UTC にする

```
[mysqld_safe]
timezone = UTC
default-time-zone = UTC
```

これだけだと `time_zone` は SYSTEM のままで UTC にはならなかった。`SET TIME_ZONE = 'UTC';` すると、`time_zone` も UTC になった。

```sql
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> SET TIME_ZONE = 'UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| system_time_zone | UTC   |
| time_zone        | UTC   |
+------------------+-------+
2 rows in set (0.01 sec)
```