はじめに
下記の構成で色々やってた時のメモ。自分が後で見返す用なのでまとまってないです。
- Python/Flask
- Flask-SQLAlchemy
- Flask-Migrate
ローカルではSQLite3、プロダクションではMySQLを使用。 Flask-SQLAlchemyとかFlask-Migrateについては↓のページにまとめてる。
初期設定
文字コードの設定
mysql> show variables like "chara%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
基本的にはこれを全部（character_set_filesystem, character_sets_dir以外）utf-8にすればok
my.cnf はメモリに合わせてどれ使うか決める。
$ cat /proc/meminfo で4GBあることが分かった。下記ページで調べて引っ張ってくる。
4GB以上でinnodbを使ってるやつを使用。innodbかどうかはDBを作った後に下記ページに載ってる方法で調べられる
[mysqld] ... character-set-server=utf8 # mysqldセクションの末尾に追加 [client] default-character-set=utf8 # clientセクションを追加
これを追加してmysqlをrestart
mysql> show variables like "chara%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
OK
DataBase URIの指定
- 標準のpython-mysqlはpython3だと動かないっぽいのでpymysqlを指定
- Unix domain socketの活用(mysqlの設定ファイル、my.cnfに合わせてsocketを設定)
SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://<usename>:<password>@localhost/<database_name>?unix_socket=/var/lib/mysql/mysql.sock'
ユーザとデータベースの作成
mysql って打ったらrootで入ると思ってたんだけど、create user とか create database が出来なかった。
mysql -u root で入ったら出来るようになった。
mysql> create user <username> identified by '<password>'; mysql> create database <database_name>; mysql> grant all on <database_name>.* to <username>@localhost identified by '<password>';
ここで manage.py db upgrade してから確認。
$ mysql -u <username> -p<password> mysql> create database <database_name>; mysql> drop database <database_name>;
TSVファイルをインポート
大量に初期化データがあったんだけど、PythonでTSVをパースしながらだと時間掛かったのでそれぞれDBに備え付けのインポート機能使う。
MySQL編
$ mysql -u mtb1 -pmtbpass1 > drop database mtb1; > create database mtb1; > quit $ python manage.py db upgrade $ mysql -u mtb1 -pmtbpass1 > use mtb; > load data local infile '/home/player/load_data/user.tsv' into table users; > load data local infile '/home/player/load_data/item.tsv' into table items; > load data local infile '/home/player/load_data/order.tsv' into table orders;
SQLite3編
tab区切りの指定は Ctl-v Tab で行なった。
$ sqlite3 -separator " " db.sqlite3 ".import ./sample_data/user.tsv users" $ sqlite3 -separator " " db.sqlite3 ".import ./sample_data/order.tsv orders" $ sqlite3 -separator " " db.sqlite3 ".import ./sample_data/item.tsv items" $ sqlite3 db.sqlite3 SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. sqlite> select count(*) from users; 10000
MySQL
SQL
show databases;use <database>;select * from <table>;desc <table>drop database <database_name>; create database <database_name>;
ダンプ
- まるごとdump:
mysqldump -u root <database_name> > hoge.dump - テーブル指定:
mysqldump -u root <database_name>.<table_name> > hoge.dump - 復元:
mysql -u <username> -p<password> -D <database_name> < hoge.dump
Slow Query Logでボトルネックを探す
Slow Query Logの出力
のページがすごい分かりやすかった。
my.cnf にSlow Query Logの設定があるけど、コンソールから確認出来る。
mysql> show variables like 'slow%'; +---------------------+----------------+ | Variable_name | Value | +---------------------+----------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | mysql-slow.log | +---------------------+----------------+ mysql> set global slow_query_log = ON;
Slow Query Logの集計
Slow Query LogはAWKとか使ってパースするのは面倒な形式なのでツールを使うのがいい。 mysqldumpslowコマンドが最初から使える。
$ mysqldumpslow -s t /var/log/mysql/mysql-slow.sql :
Slow Query Logの結果をEXPLAINする。
mysql> EXPLAIN SELECT orders.id AS orders_id, orders.order_date_time AS orders_order_date_time, orders.order_user_id AS orders_order_user_id, orders.order_item_id AS orders_order_item_id, orders.order_quantity AS orders_order_quantity, orders.order_state AS orders_order_state, orders.tags AS orders_tags FROM orders INNER JOIN users ON users.id = orders.order_user_id INNER JOIN items ON items.id = orders.order_item_id WHERE items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' LIMIT N; ERROR 1327 (42000): Undeclared variable: N
'S' とかはただの文字列だからそのままでもEXPLAINできるんだけど、Nとかは変数だから何か実際の値を入れないといけない。
mysql> EXPLAIN SELECT orders.id AS orders_id, orders.order_date_time AS orders_order_date_time, orders.order_user_id AS orders_order_user_id, orders.order_item_id AS orders_order_item_id, orders.order_quantity AS orders_order_quantity, orders.order_state AS orders_order_state, orders.tags AS orders_tags FROM orders INNER JOIN users ON users.id = orders.order_user_id INNER JOIN items ON items.id = orders.order_item_id WHERE items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' AND items.tags LIKE 'S' LIMIT 10; +----+-------------+--------+--------+-----------------------------+---------------+---------+--------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+--------+-----------------------------+---------------+---------+--------------------------+------+-------------+ | 1 | SIMPLE | items | range | PRIMARY,ix_items_tags | ix_items_tags | 387 | NULL | 1 | Using where | | 1 | SIMPLE | orders | ref | order_item_id,order_user_id | order_item_id | 51 | mtb.items.id | 100 | Using where | | 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 50 | mtb.orders.order_user_id | 1 | Using index | +----+-------------+--------+--------+-----------------------------+---------------+---------+--------------------------+------+-------------+ 3 rows in set (0.01 sec)
EXPLAINの読み方については下記の記事が分かりやすい。
INDEXチューニング
後から役に立つかもしれないので手順をそのままメモ。この辺の経験がまだ足りなすぎるのでもうちょっと積極的に使っていかないといけない。
Slow Query Logを見てたら10秒かかってるクエリがあった。とりあえずexplainでSQLの方を確認。
mysql> explain SELECT orders.id AS orders_id, orders.order_date_time AS orders_order_date_time, orders.order_user_id AS orders_order_user_id, orders.order_item_id AS orders_order_item_id, order
s.order_quantity AS orders_order_quantity, orders.order_state AS orders_order_state, orders.tags AS orders_tags
-> FROM orders INNER JOIN users ON users.id = orders.order_user_id INNER JOIN items ON items.id = orders.order_item_id
-> WHERE orders.order_date_time >= 1060015360;
+----+-------------+--------+--------+-------------------------------------------------------+---------+---------+--------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+-------------------------------------------------------+---------+---------+--------------------------+---------+-------------+
| 1 | SIMPLE | orders | ALL | order_item_id,order_user_id,ix_orders_order_date_time | NULL | NULL | NULL | 1000000 | Using where |
| 1 | SIMPLE | items | eq_ref | PRIMARY | PRIMARY | 50 | mtb.orders.order_item_id | 1 | Using index |
| 1 | SIMPLE | users | eq_ref | PRIMARY | PRIMARY | 50 | mtb.orders.order_user_id | 1 | Using index |
+----+-------------+--------+--------+-------------------------------------------------------+---------+---------+--------------------------+---------+-------------+
複合INDEX貼り過ぎらしい。使われてないので消して、普通のINDEXを作ってみる。
mysql> show index from orders; +--------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | orders | 0 | PRIMARY | 1 | id | A | 1000000 | NULL | NULL | | BTREE | | | | orders | 1 | order_item_id | 1 | order_item_id | A | 10000 | NULL | NULL | YES | BTREE | | | | orders | 1 | order_user_id | 1 | order_user_id | A | 10000 | NULL | NULL | YES | BTREE | | | | orders | 1 | ix_orders_order_date_time | 1 | order_date_time | A | 1000000 | NULL | NULL | YES | BTREE | | | | orders | 1 | ix_orders_order_quantity | 1 | order_quantity | A | 49 | NULL | NULL | YES | BTREE | | | | orders | 1 | ix_orders_order_state | 1 | order_state | A | 7 | NULL | NULL | YES | BTREE | | | | orders | 1 | ix_orders_tags | 1 | tags | A | 6756 | NULL | NULL | YES | BTREE | | | +--------+------------+---------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.00 sec) mysql> alter table orders drop index ix_orders_order_date_time; Query OK, 1000000 rows affected (16.98 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> alter table orders add index ix_order_date_time(order_date_time); Query OK, 1000000 rows affected (17.02 sec) Records: 1000000 Duplicates: 0 Warnings: 0
こうやってindex消したり貼ったりしながら確認。他のクエリで必要なインデックスが消えてしまう危険性ももちろんあるのでその都度slow query log見ながら調整していくらしい。大変だ...
おわりに
ふう、MySQLのチューニングがちょっと分かってきた気がするけどまだまだ。 もうちょっと経験積みたい。
