footmark

ひよっこエンジニアの足跡

【MySQL】オンメモリで動かしてみる

前回(【MySQL】インデックスを貼ったら32.2倍で動いた - footmark)に引き続き、MySQLのチューニング(?)ネタです。

同じく、MySQL上の単語の情報が格納されているテーブル word_table と、どの単語がどの文書に出現するかを表す情報が格納されているテーブル doc_word_table を使って、キーワードを入力するとそのキーワードが各文書にどれだけ含まれているかを計算する処理の高速化を目指します。

今回のベースラインはこちら。
(timeコマンドで計測)

real 6m41.622s
user 2m49.500s
sys 0m50.541s

real, user, sys はそれぞれ、

  • real=user + sys + その他(IOなど)
  • user=プログラム自信の処理時間
  • sys=OSの処理時間

なので、MySQLからの読み込みがネックになっていると予想できます。
ということは、MySQLをオンメモリで動かせれば高速化できるかも!

MEMORYストレージエンジンを使う

MEMORYストレージエンジンはメモリ上に情報を格納するテーブルです。

CREATE TABLE テーブル名(カラム名 型) ENGINE=MEMORY;

今回はベースラインのテーブルから作ります。

CREATE TABLE doc_word_table_memory ENGINE=MEMORY (SELECT * FROM doc_word_table);

で作成できます。
が、ここでエラー。
メモリ上に作成できるテーブルサイズの上限を超えていると言われました。
(スペックの限界ではなく設定の)

メモリ上に作成できるテーブルサイズの上限を増やす

SHOW VARIABLES コマンドで設定を確認してみます。

mysql> SHOW VARIABLES LIKE 'tmp_table%';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| tmp_table_size | 16777216 |
+----------------+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_heap%';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| max_heap_table_size | 16777216 |
+---------------------+------------+
1 row in set (0.00 sec)

どちらも16MBになっていますね。

ベースラインのテーブルサイズを確認して、どのくらいの容量が必要なのか調べてみます。

sizeはMB表示

mysql> SELECT table_name, FLOOR((data_length+index_length)/1024/1024) AS size FROM information_schema.tables WHERE table_schema=database();
+----------------------+------+
| table_name           | size |
+----------------------+------+
| all_word_table       |  690 |
| word_table           |    5 |
(略)
+----------------------+------+

1GBくらい確保してやれば良さそうです。
SETコマンドで設定を変更します。

mysql> SET tmp_table_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET max_heap_table_size=1*1024*1024*1024;
Query OK, 0 rows affected (0.00 sec)

MEMORYストレージエンジンを使う(2回目)

気を取り直して、

mysql> CREATE TABLE doc_word_table_memory ENGINE=MEMORY (SELECT * FROM doc_word_table);
Query OK, 16358934 rows affected (4.13 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE word_table_memory ENGINE=MEMORY (SELECT * FROM word_table);
Query OK, 68058 rows affected (0.06 sec)
Records: 68058  Duplicates: 0  Warnings: 0

無事作成できました。
インデックスも貼ります。
(ベースラインにも貼ってあります。)

mysql> CREATE INDEX index_surface ON word_table_memory(surface);
Query OK, 68058 rows affected (0.05 sec)
Records: 68058  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX index_doc_id_word_id ON doc_word_table_memory(doc_id, word_id);
Query OK, 16358934 rows affected (9.76 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

(実は前回と比べてかなりデータ量増えてます)

チューニング結果

なんかめっちゃ速くなりそうな気がしますね!

どん!

real 6m32.481s
user 2m51.671s
sys 0m48.149s

あれ?

変わらない?

使い方が間違っているのか、最初の仮説が間違っていたのか。
次回までに要検証です。