[ISUCON用メモ] MySQL #MySQL

インストール

Ubuntu

$ sudo apt-get install -y mysql-server

Mac

$ brew update && brew install mysql

設定ファイル

パス

$ 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

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

table 一覧

show tables


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>

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;


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>

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>);

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 をコンマ区切りで複数指定する。

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

$ mysqldump -u user -p password dbname > dbname.sql

restore

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

$ mysql -u user -p password < dbname.sql

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

gzip で dump & restore する

dump

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

restore

$ 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

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

[mysqld]
innodb_buffer_pool_size=1G

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

innodb_flush_method=O_DIRECT

innodb_buffer_pool_instances

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

innodb_log_buffer_size

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

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

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

値	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://github.com/percona/percona-toolkit/releases から最新のソースコードをダウンロード


$ 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 の設定

初期状態

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 になった。

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)

[ISUCON用メモ] MySQL

インストール

Ubuntu

Mac

ログイン

ユーザー作成

設定ファイル

コマンド

database 一覧

table 一覧

schema 確認

データ量確認

index 確認

index を追加

index を削除

dump と restore

基本

dump

restore

gzip で dump & restore する

dump

restore

チューニング

innodb_buffer_pool_size

innodb_buffer_pool_instances

innodb_log_buffer_size

innodb_log_file_size

innodb_flush_log_at_trx_commit

slow query log 解析

設定変数

pt-query-digest のインストール

走らせる

Timezone の設定

UTC にする