インストール
Ubuntu
$ sudo apt-get install -y mysql-server
Mac
$ brew update && brew install mysql
ログイン
mysql -h[host] -P[port] -u[user_name] -p[password] ([database])
$ mysql -h0.0.0.0 -P3306 -uisucon -pisucon isuketch
localhost なら
$ mysql -uroot
ユーザー作成
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
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)