15
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

[ISUCON用メモ] MySQL

インストール

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)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
15
Help us understand the problem. What are the problem?