読者です 読者をやめる 読者になる 読者になる

c-bata web

@c_bata_ のメモ。python多め

MySQLメモ

はじめに

下記の構成で色々やってた時のメモ。自分が後で見返す用なのでまとまってないです。

  • Python/Flask
  • Flask-SQLAlchemy
  • Flask-Migrate

ローカルではSQLite3、プロダクションではMySQLを使用。 Flask-SQLAlchemyとかFlask-Migrateについては↓のページにまとめてる。

nwpct1.hatenablog.com


初期設定

文字コードの設定

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のチューニングがちょっと分かってきた気がするけどまだまだ。 もうちょっと経験積みたい。