2015年3月6日金曜日

MySQL 5.6 InnoDB FTS関連のinformation_schemaを覗くにはinnodb_ft_aux_tableを指定する

5.6の情報だよ!


日々の覚書: 全文検索のテスト用にtweets.csvを食わせるSQL のMroonga用な部分をInnoDBに変えて(FTインデックスはまだ作らない)

mysql56> ALTER TABLE tweets ADD fts text NOT NULL, ADD FULLTEXT KEY (fts);
Query OK, 0 rows affected, 1 warning (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql56> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.03 sec)

分かち書き用のカラムを足して、そこにInnoDB FTインデックスを作る。

( ´-`).oO(LOAD DATA INFILEするときに久々に /usr/my.cnfの罠 にはまった。面倒だからMySQLに色々切り詰めさせてるのよね。。


FTインデックスを足したら https://gist.github.com/yoku0825/f0708574294042cbf350 こんなので分かち書きしてやって、

mysql56> SELECT table_name, index_name, COUNT(*) AS pages, SUM(data_size) AS size FROM innodb_buffer_page GROUP BY 1, 2; -- 1回再起動してバッファプールを空にしておいた
+--------------------------------+-----------------------+-------+------+
| table_name                     | index_name            | pages | size |
+--------------------------------+-----------------------+-------+------+
| NULL                           | NULL                  | 65521 |    0 |
| `mysql`.`innodb_index_stats`   | PRIMARY               |     1 |  545 |
| `mysql`.`innodb_table_stats`   | PRIMARY               |     1 |   56 |
| `mysql`.`slave_master_info`    | PRIMARY               |     1 |    0 |
| `mysql`.`slave_relay_log_info` | PRIMARY               |     1 |    0 |
| `mysql`.`slave_worker_info`    | PRIMARY               |     1 |    0 |
| `SYS_COLUMNS`                  | CLUST_IND             |     1 | 7341 |
| `SYS_DATAFILES`                | SYS_DATAFILES_SPACE   |     1 |  813 |
| `SYS_FIELDS`                   | CLUST_IND             |     1 | 1586 |
| `SYS_FOREIGN`                  | FOR_IND               |     1 |    0 |
| `SYS_FOREIGN`                  | REF_IND               |     1 |    0 |
| `SYS_INDEXES`                  | CLUST_IND             |     1 | 1756 |
| `SYS_TABLESPACES`              | SYS_TABLESPACES_SPACE |     1 |  801 |
| `SYS_TABLES`                   | CLUST_IND             |     1 | 1931 |
| `SYS_TABLES`                   | ID_IND                |     1 |  986 |
+--------------------------------+-----------------------+-------+------+
15 rows in set (1.06 sec)

mysql56> SELECT tweet_id, text FROM tweets WHERE match(fts) against('肉') ORDER BY _rowid DESC LIMIT 3;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | text                                                                                                                                                                                                                                                                             |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 564643277329014784 | RT @groonga: Groonga 5.0.0リリース (2015-02-09) 年に一度の2月9日肉の日!Groongaはメジャーバージョンアップしました。シャーディングを実現する実験的なプラグインがはいったよ。http://t.co/XisU7ppRl0                                                                                |
| 560652761247645700 | RT @ktou: 今日は今年最初の肉の日だから最新PostgreSQLでGroongaを使えるようにするPGroongaをリリースしたよ!最初のリリースだよ!CentOS 7用にはパッケージがあるよ! http://t.co/wOufGV1GS7                                                                                           |
| 549583026510774272 | RT @groonga: 今日は今年最後の肉の日ですね!Mroonga 4.09をリリースしました!BOOLEAN MODEでGroongaのスク リプト構文を使える機能とか普通のMroongaユーザーにはあまり必要のない改良が主です。 http://t.co/qL6qxsO5LN                                                                   |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql56> SELECT table_name, index_name, COUNT(*) AS pages, SUM(data_size) AS size FROM innodb_buffer_page GROUP BY 1, 2;
+------------------------------------------------------+-----------------------+-------+--------+
| table_name                                           | index_name            | pages | size   |
+------------------------------------------------------+-----------------------+-------+--------+
| NULL                                                 | NULL                  | 65469 |      0 |
| `d1`.`FTS_0000000000000045_000000000000006e_INDEX_6` | FTS_INDEX_TABLE_IND   |     2 |  16322 |
| `d1`.`FTS_0000000000000045_CONFIG`                   | IND                   |     1 |    234 |
| `d1`.`FTS_0000000000000045_DELETED_CACHE`            | IND                   |     1 |      0 |
| `d1`.`FTS_0000000000000045_DELETED`                  | IND                   |     1 |      0 |
| `d1`.`tweets`                                        | FTS_DOC_ID_INDEX      |    19 | 271784 |
| `d1`.`tweets`                                        | PRIMARY               |    28 | 412807 |
| `mysql`.`innodb_index_stats`                         | PRIMARY               |     1 |    545 |
| `mysql`.`innodb_table_stats`                         | PRIMARY               |     1 |     56 |
| `mysql`.`slave_master_info`                          | PRIMARY               |     1 |      0 |
| `mysql`.`slave_relay_log_info`                       | PRIMARY               |     1 |      0 |
| `mysql`.`slave_worker_info`                          | PRIMARY               |     1 |      0 |
| `SYS_COLUMNS`                                        | CLUST_IND             |     1 |   7341 |
| `SYS_DATAFILES`                                      | SYS_DATAFILES_SPACE   |     1 |    813 |
| `SYS_FIELDS`                                         | CLUST_IND             |     1 |   1586 |
| `SYS_FOREIGN`                                        | FOR_IND               |     1 |      0 |
| `SYS_FOREIGN`                                        | REF_IND               |     1 |      0 |
| `SYS_INDEXES`                                        | CLUST_IND             |     1 |   1756 |
| `SYS_TABLESPACES`                                    | SYS_TABLESPACES_SPACE |     1 |    801 |
| `SYS_TABLES`                                         | CLUST_IND             |     1 |   1931 |
| `SYS_TABLES`                                         | ID_IND                |     1 |    986 |
+------------------------------------------------------+-----------------------+-------+--------+
21 rows in set (1.05 sec)

とまあフツーに検索もできるし、インデックスも使われてる。

さてここから、information_schemaのInnoDB FT関連の何かを探すと、


mysql56> SHOW TABLES LIKE 'INNODB\_FT\_%';
+----------------------------------------------+
| Tables_in_information_schema (INNODB\_FT\_%) |
+----------------------------------------------+
| INNODB_FT_DELETED                            |
| INNODB_FT_DEFAULT_STOPWORD                   |
| INNODB_FT_INDEX_TABLE                        |
| INNODB_FT_BEING_DELETED                      |
| INNODB_FT_INDEX_CACHE                        |
| INNODB_FT_CONFIG                             |
+----------------------------------------------+
6 rows in set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_DELETED;
Empty set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_INDEX_TABLE;
Empty set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_INDEX_CACHE;
Empty set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_CONFIG;
Empty set (0.00 sec)

:(;゙゚'ω゚'): あっれえINNODB_DEFAULT_STOPWORD以外何も出力してくれない。
と思ったら、情報を出力させたいテーブルを innodb_ft_aux_table に指定するらしい。my.cnfに書いてもダメで、必ずSET GLOBALで設定しなきゃダメよとか書いてある。


mysql56> SET GLOBAL innodb_ft_aux_table= 'd1/tweets';
Query OK, 0 rows affected (0.01 sec)

mysql56> SELECT * FROM INNODB_FT_INDEX_TABLE;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| 00   |          572 |       23196 |        77 |    572 |      224 |
| 00   |          572 |       23196 |        77 |    578 |      111 |
| 00   |          572 |       23196 |        77 |    666 |      151 |
..

mysql56> SELECT word, count(*) FROM INNODB_FT_INDEX_TABLE GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
+------+----------+
| word | count(*) |
+------+----------+
| の   |    16573 |
| て   |    11059 |
| に   |    11036 |
| が   |    10630 |
| た   |     9673 |
| は   |     8460 |
| _    |     7707 |
| で   |     7624 |
| を   |     6804 |
| と   |     6432 |
+------+----------+
10 rows in set (5.34 sec)

どうも INNODB_FT_INDEX_TABLE では転置索引の中身を確認できるっぽい。MyISAMでいうところの myisam_ftdump といったところか。この機能好きなので嬉しい。Mroongaさんにはコレないのよねぇ。。(Rroongaのgroonga-index-dumpの出力をパースしてがんばってたなぁ。。 日々の覚書: groonga-index-dumpで遊んでみる )


mysql56> SELECT * FROM INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 23475 |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.00 sec)

mysql56> SELECT * FROM INNODB_FT_INDEX_CACHE;
Empty set (0.00 sec)

INNODB_FT_CONFIG が表示されるようになったのはいいし、INNODB_FT_*DELETEDが空っぽなのもいいんだけど、INNODB_FT_INDEX_CACHE はそのままでは何も出力してくれない。OPTIMIZE TABLEが必要だって書いてある。OPTIMIZE TABLEしたらINNODB_FT_INDEX_TABLEと同じような形式で表示された。

ANALYZE TABLEじゃなくてOPTIMIZE TABLEってどういうことだろう。ANALYZE TABLEだとダメだった。

バッファプールにはもちろん載ってるんだけど、バッファプールと別のパラメーター(innodb_ft_cache_size)で制御するから別物のことを言ってるんだよなぁ。。"Index inserts and updates are only committed to disk when the innodb_ft_cache_size size limit is reached."って書いてあるから、チェンジバッファ的な振る舞いをするのかしらん。

5.7で日本語全文検索が来てもいいように、練習中なのでした。

0 件のコメント :

コメントを投稿