GA

2015/03/31

MySQLのperformance_schemaをフルパワーにするとどんな情報が見られるのか

日々の覚書: MySQLのperformance_schemaでどれくらいの情報が見られるのか の続き。


* events_stages_summary_by_account_by_event_name
* events_stages_summary_by_host_by_event_name
* events_stages_summary_by_thread_by_event_name
* events_stages_summary_by_user_by_event_name
* events_stages_summary_global_by_event_name

mysql> SELECT * FROM events_stages_summary_by_account_by_event_name WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+------------+-----------+-------------------------+------------+----------------+----------------+----------------+----------------+
| USER       | HOST      | EVENT_NAME              | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------------+-----------+-------------------------+------------+----------------+----------------+----------------+----------------+
| isucon2app | localhost | stage/sql/Sending data  |       2509 | 39308705666000 |              0 |    15667080000 |   178525262000 |
| isucon2app | localhost | stage/sql/freeing items |       3000 |  7854180396000 |              0 |     2618060000 |    25104133000 |
| isucon2app | localhost | stage/sql/System lock   |       2743 |  2691080812000 |              0 |      981072000 |   244085523000 |
+------------+-----------+-------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

デフォルトではOFFになっていたステージの情報(5.5まででいうprofiling)の情報が見られるようになる。
はかどりそうだけど、event_stageの情報をdigest(つまりクエリー単位)でサマライズして見られたらもっと良かったのになぁ。。
profileと同じようにピンポイントで有効にすれば本来いいはず。


* events_waits_summary_by_account_by_event_name
* events_waits_summary_by_host_by_event_name
* events_waits_summary_by_instance
* events_waits_summary_by_thread_by_event_name
* events_waits_summary_by_user_by_event_name
* events_waits_summary_global_by_event_name

mysql> SELECT * FROM events_waits_summary_by_account_by_event_name WHERE count_star <> 0 and event_name <> 'idle' and user = 'isucon2app' ORDER BY sum_timer_wait DESC;
+------------+-----------+------------------------------------------+------------+----------------+----------------+----------------+----------------+
| USER       | HOST      | EVENT_NAME                               | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------------+-----------+------------------------------------------+------------+----------------+----------------+----------------+----------------+
| isucon2app | localhost | wait/io/table/sql/handler                |   32424969 | 41901091208900 |          49280 |        1292060 |   107226966770 |
| isucon2app | localhost | wait/io/socket/sql/client_connection     |       6455 |  8661436551845 |         510125 |     1341818170 |    25122662950 |
| isucon2app | localhost | wait/io/file/innodb/innodb_log_file      |        364 |  1616073950645 |        8077685 |     4439763405 |   418325359760 |
| isucon2app | localhost | wait/io/file/innodb/innodb_data_file     |        657 |  1146723865595 |        1064525 |     1745393650 |   209724324810 |
| isucon2app | localhost | wait/synch/mutex/innodb/trx_mutex        |     699706 |    41160329980 |          20790 |          58520 |     3020631845 |
| isucon2app | localhost | wait/synch/mutex/mysys/THR_LOCK::mutex   |       9634 |    11706377760 |          26950 |        1215060 |    10035856215 |
| isucon2app | localhost | wait/lock/table/sql/handler              |      11344 |     6277293330 |          26950 |         553245 |      126401660 |
| isucon2app | localhost | wait/synch/mutex/innodb/trx_undo_mutex   |      82384 |     4336347400 |          24640 |          52360 |       41428695 |
| isucon2app | localhost | wait/synch/mutex/sql/THD::LOCK_thd_data  |      18669 |     2553290740 |          25410 |         136675 |       45770340 |
| isucon2app | localhost | wait/io/file/sql/FRM                     |        225 |      605735515 |         199045 |        2691920 |       43222025 |
| isucon2app | localhost | wait/synch/rwlock/innodb/fil_space_latch |        675 |       66989230 |          24640 |          98945 |         846615 |
| isucon2app | localhost | wait/io/file/sql/dbopt                   |          4 |       31597335 |         232925 |        7899045 |       27579475 |
| isucon2app | localhost | wait/synch/rwlock/sql/MDL_lock::rwlock   |         20 |        2514050 |          81235 |         125510 |         405405 |
+------------+-----------+------------------------------------------+------------+----------------+----------------+----------------+----------------+
13 rows in set (0.00 sec)

さっきとそんなに塩梅は変わらないものの、mutexの情報とか増えてる。

↓吊るしで同じクエリーを叩くとこれだけしか情報は出ない。

mysql> SELECT * FROM events_waits_summary_by_account_by_event_name WHERE count_star <> 0 and event_name <> 'idle' and user = 'isucon2app' ORDER BY sum_timer_wait DESC;
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
| USER       | HOST      | EVENT_NAME                           | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
| isucon2app | localhost | wait/io/table/sql/handler            |   30563374 | 30001032268130 |          48125 |         981365 |    65554244140 |
| isucon2app | localhost | wait/io/file/innodb/innodb_log_file  |        362 |   941252670050 |        7715785 |     2600145240 |    61855393600 |
| isucon2app | localhost | wait/io/file/innodb/innodb_data_file |        386 |   769021622600 |         872795 |     1992283755 |   110223802535 |
| isucon2app | localhost | wait/lock/table/sql/handler          |      10640 |     3912227935 |          25410 |         367675 |      136991855 |
| isucon2app | localhost | wait/io/file/sql/FRM                 |        160 |      453758305 |         201355 |        2835910 |       42650685 |
| isucon2app | localhost | wait/io/file/sql/dbopt               |          4 |       24409000 |         222530 |        6102250 |       21301280 |
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
6 rows in set (0.00 sec)



* mutex_instances

mysql> SELECT * FROM mutex_instances;
+-------------------------------------------------+-----------------------+---------------------+
| NAME                                            | OBJECT_INSTANCE_BEGIN | LOCKED_BY_THREAD_ID |
+-------------------------------------------------+-----------------------+---------------------+
| wait/synch/mutex/mysys/my_thread_var::mutex     |       140714335799552 |                NULL |
| wait/synch/mutex/mysys/my_thread_var::mutex     |       140714402908416 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714805632408 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |       140714805616856 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714805660792 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |       140714805657816 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714805659576 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |       140714805645720 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714805679048 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |       140714805586648 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714805601848 |                NULL |
| wait/synch/mutex/mysys/THR_LOCK::mutex          |       140714805602632 |                NULL |
| wait/synch/mutex/mysys/my_thread_var::mutex     |       140714201581952 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              36850088 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              36853576 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              42562344 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              42565832 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              42577160 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              42580648 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              42740984 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              42744472 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714604308568 |                NULL |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data  |       140714604309576 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              42529224 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              42532712 |                NULL |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status |              42595464 |                NULL |
| wait/synch/mutex/sql/THD::LOCK_thd_data         |              42598952 |                NULL |
+-------------------------------------------------+-----------------------+---------------------+
27 rows in set (0.00 sec)

これはむしろクエリー流している最中に見て、locked_by_thread_idが埋まりがちだったらインスタンスを分けるとかしたほうがいいのかしら。


パッと見これくらいなので、普段は吊るしのまま(バックグラウンドスレッドはOFFにしてもいい)か、setup_instrumentsのWHERE name LIKE 'stage%'を有効にだけしておいて、実際に計測したいときにsetup_consumersのevents_stages_currentをYesにしてやればいいですかね。


前にそんなことちょっと書いたなぁと思いつつ。
performance_schema_instrumentの設定方法 | GMOメディア エンジニアブログ
performance_schema_instrumentの設定方法 | GMOメディア エンジニアブログ

MySQLのperformance_schemaでどれくらいの情報が見られるのか

MySQL :: MySQL 5.6 Reference Manual :: 22 MySQL Performance Schema

5.6以降「よくなったよ!」「確かにね! でもメモリー」「」みたいな感じで扱われて結局まだONにしているインスタンスの少ないPerformance Schemaなのでちょっと練習中。

tpcc-mysqlとかでやると割と綺麗なクエリーをしているので面白い情報が出ない(ibdata1のfsyncに時間がかかってることなんて知ってるよ! って感じになる)ので、isucon2のアプリで試してみることにしますた。モリスさん++

livedoor Techブログ : 自家製 #isucon2 のつくりかた


MySQLは5.6がいいのでMySQL :: Download MySQL Yum Repositoryで入れる。

$ sudo yum localinstall -y http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum install -y mysql-community-*

らくちん。
吊るしでPerformance Schemaはいくらか有効になっているので(中の人いわく、「よく使いそうなものはデフォルトでONになっている」)まずはそのまま見てみる。めぼしそうなもの(とりあえずcount_starカラムが0じゃないレコードを含むテーブル)を探してみるとこんなのがあった。

↓にずらーと出力結果ばかり並べているので先に結論。

events_statements_summary_by_digest マジ優秀。
これを見るためだけに(メモリーが許すなら)performance_schemaをONにしておく価値はある。


* events_statements_summary_by_account_by_event_name
* events_statements_summary_by_host_by_event_name
* events_statements_summary_by_thread_by_event_name
* events_statements_summary_by_user_by_event_name
* events_statements_summary_global_by_event_name

mysql> SELECT * FROM events_statements_summary_by_account_by_event_name WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+------------+-----------+----------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+
| USER       | HOST      | EVENT_NAME           | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | SUM_LOCK_TIME | SUM_ERRORS | SUM_WARNINGS | SUM_ROWS_AFFECTED | SUM_ROWS_SENT | SUM_ROWS_EXAMINED | SUM_CREATED_TMP_DISK_TABLES | SUM_CREATED_TMP_TABLES | SUM_SELECT_FULL_JOIN | SUM_SELECT_FULL_RANGE_JOIN | SUM_SELECT_RANGE | SUM_SELECT_RANGE_CHECK | SUM_SELECT_SCAN | SUM_SORT_MERGE_PASSES | SUM_SORT_RANGE | SUM_SORT_ROWS | SUM_SORT_SCAN | SUM_NO_INDEX_USED | SUM_NO_GOOD_INDEX_USED |
+------------+-----------+----------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+
| isucon2app | localhost | statement/sql/select |       2738 | 35991922775000 |       83997000 |    13145333000 |   160056655000 |  195344000000 |          0 |            0 |                 0 |       1653914 |          29363625 |                           0 |                    557 |                  556 |                          0 |                0 |                      0 |            1558 |                     0 |              0 |          4986 |           557 |              1558 |                      0 |
| isucon2app | localhost | statement/sql/insert |        148 |  3698267239000 |       81107000 |    24988292000 |  1354468459000 |  113248000000 |          0 |            0 |             82048 |             0 |                 0 |                           0 |                      0 |                    0 |                          0 |                0 |                      0 |               0 |                     0 |              0 |             0 |             0 |                 0 |                      0 |
| isucon2app | localhost | statement/sql/update |         94 |  1648364786000 |     5976157000 |    17535795000 |    42219182000 |    5534000000 |          0 |            0 |                94 |             0 |            385118 |                           0 |                      0 |                    0 |                          0 |                0 |                      0 |               0 |                     0 |             94 |            94 |             0 |                 0 |                      0 |
+------------+-----------+----------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM events_statements_summary_by_account_by_event_name WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
                       USER: isucon2app
                       HOST: localhost
                 EVENT_NAME: statement/sql/select
                 COUNT_STAR: 2738
             SUM_TIMER_WAIT: 35991922775000
             MIN_TIMER_WAIT: 83997000
             AVG_TIMER_WAIT: 13145333000
             MAX_TIMER_WAIT: 160056655000
              SUM_LOCK_TIME: 195344000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1653914
          SUM_ROWS_EXAMINED: 29363625
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 557
       SUM_SELECT_FULL_JOIN: 556
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 1558
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 4986
              SUM_SORT_SCAN: 557
          SUM_NO_INDEX_USED: 1558
     SUM_NO_GOOD_INDEX_USED: 0
1 row in set (0.00 sec)

うーん、ちょっと大まかすぎるかな。。sum_sort_scanとかsum_sort_merge_passesとかは出てるから、どちらかというと全体の傾向をグラフにした方がいいような気がする(や、それならinformation_schemaで今のところ足りてるけど、こっちはupdateかselectか分けて見られるってことだよね)


* events_statements_summary_by_digest

mysql> SELECT * FROM events_statements_summary_by_digest WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+-------------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+---------------------+---------------------+
| SCHEMA_NAME | DIGEST                           | DIGEST_TEXT                                                                                                                                                                                                                                                                                                                                                                                        | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | SUM_LOCK_TIME | SUM_ERRORS | SUM_WARNINGS | SUM_ROWS_AFFECTED | SUM_ROWS_SENT | SUM_ROWS_EXAMINED | SUM_CREATED_TMP_DISK_TABLES | SUM_CREATED_TMP_TABLES | SUM_SELECT_FULL_JOIN | SUM_SELECT_FULL_RANGE_JOIN | SUM_SELECT_RANGE | SUM_SELECT_RANGE_CHECK | SUM_SELECT_SCAN | SUM_SORT_MERGE_PASSES | SUM_SORT_RANGE | SUM_SORT_ROWS | SUM_SORT_SCAN | SUM_NO_INDEX_USED | SUM_NO_GOOD_INDEX_USED | FIRST_SEEN          | LAST_SEEN           |
+-------------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+---------------------+---------------------+
| isucon2     | 68ff5779415a5d171d700f4a20fd47d0 | SELECT `stock` . `seat_id` , `variation` . `name` AS `v_name` , `ticket` . `name` AS `t_name` , `artist` . `name` AS `a_name` FROM `stock` JOIN `variation` ON `stock` . `variation_id` = `variation` . `id` JOIN `ticket` ON `variation` . `ticket_id` = `ticket` . `id` JOIN `artist` ON `ticket` . `artist_id` = `artist` . `id` WHERE `order_id` IS NOT NULL ORDER BY `order_id` DESC LIMIT ?  |        556 | 19618662892000 |    12253383000 |    35285364000 |   160056655000 |   62943000000 |          0 |            0 |                 0 |          4931 |          22848064 |                           0 |                    556 |                  556 |                          0 |                0 |                      0 |             556 |                     0 |              0 |          4931 |           556 |               556 |                      0 | 2015-03-31 14:50:03 | 2015-03-31 14:50:21 |
| isucon2     | 32f72c3a9a23b6bdf3847bbdddeb16c7 | SELECT COUNT ( * ) FROM `variation` INNER JOIN `stock` ON `stock` . `variation_id` = `variation` . `id` WHERE `variation` . `ticket_id` = ? AND `stock` . `order_id` IS NULL                                                                                                                                                                                                                       |        351 |  8184198181000 |     6442619000 |    23316803000 |    50954069000 |   27705000000 |          0 |            0 |                 0 |           351 |           2878902 |                           0 |                      0 |                    0 |                          0 |                0 |                      0 |             351 |                     0 |              0 |             0 |             0 |               351 |                      0 | 2015-03-31 14:50:03 | 2015-03-31 14:50:21 |
| isucon2     | 886e82ad6d641a9be797d0b5b1f2bf73 | SELECT `seat_id` , `order_id` FROM `stock` WHERE `variation_id` = ?                                                                                                                                                                                                                                                                                                                                |        402 |  5404182832000 |     3303525000 |    13443240000 |    38198522000 |   13646000000 |          0 |            0 |                 0 |       1646592 |           1646592 |                           0 |                      0 |                    0 |                          0 |                0 |                      0 |               0 |                     0 |              0 |             0 |             0 |                 0 |                      0 | 2015-03-31 14:50:06 | 2015-03-31 14:50:21 |
+-------------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------------+----------------+----------------+----------------+---------------+------------+--------------+-------------------+---------------+-------------------+-----------------------------+------------------------+----------------------+----------------------------+------------------+------------------------+-----------------+-----------------------+----------------+---------------+---------------+-------------------+------------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM events_statements_summary_by_digest WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
                SCHEMA_NAME: isucon2
                     DIGEST: 68ff5779415a5d171d700f4a20fd47d0
                DIGEST_TEXT: SELECT `stock` . `seat_id` , `variation` . `name` AS `v_name` , `ticket` . `name` AS `t_name` , `artist` . `name` AS `a_name` FROM `stock` JOIN `variation` ON `stock` . `variation_id` = `variation` . `id` JOIN `ticket` ON `variation` . `ticket_id` = `ticket` . `id` JOIN `artist` ON `ticket` . `artist_id` = `artist` . `id` WHERE `order_id` IS NOT NULL ORDER BY `order_id` DESC LIMIT ?
                 COUNT_STAR: 556
             SUM_TIMER_WAIT: 19618662892000
             MIN_TIMER_WAIT: 12253383000
             AVG_TIMER_WAIT: 35285364000
             MAX_TIMER_WAIT: 160056655000
              SUM_LOCK_TIME: 62943000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 4931
          SUM_ROWS_EXAMINED: 22848064
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 556
       SUM_SELECT_FULL_JOIN: 556
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 556
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 4931
              SUM_SORT_SCAN: 556
          SUM_NO_INDEX_USED: 556
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-03-31 14:50:03
                  LAST_SEEN: 2015-03-31 14:50:21
1 row in set (0.00 sec)

これは判りやすい。ステートメント(pt-query-digestみたいに可変部分は握りつぶしてくれる)単位でSelect_scanやSum_rows_examinedが見られる。ステキ。
Percona Serverのスローログ + pt-query-digest並みの情報量な気がする。パーセンタイルな情報はないけどまあいいや。


* events_waits_summary_by_account_by_event_name
* events_waits_summary_by_host_by_event_name
* events_waits_summary_by_instance
* events_waits_summary_by_thread_by_event_name
* events_waits_summary_by_user_by_event_name
* events_waits_summary_global_by_event_name

mysql> SELECT * FROM events_waits_summary_by_account_by_event_name WHERE count_star <> 0 AND event_name <> 'idle' ORDER BY sum_timer_wait DESC LIMIT 3;
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
| USER       | HOST      | EVENT_NAME                           | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
| isucon2app | localhost | wait/io/table/sql/handler            |   29763732 | 28437697326885 |          48125 |         955185 |  1355498746525 |
| isucon2app | localhost | wait/io/file/innodb/innodb_data_file |        417 |  2104885627075 |        2718100 |     5047687260 |  1191383874065 |
| isucon2app | localhost | wait/io/file/innodb/innodb_log_file  |        350 |   865270214655 |        7838215 |     2472200500 |    58576612710 |
+------------+-----------+--------------------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM events_waits_summary_by_account_by_event_name WHERE count_star <> 0 AND event_name <> 'idle' ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
          USER: isucon2app
          HOST: localhost
    EVENT_NAME: wait/io/table/sql/handler
    COUNT_STAR: 29763732
SUM_TIMER_WAIT: 28437697326885
MIN_TIMER_WAIT: 48125
AVG_TIMER_WAIT: 955185
MAX_TIMER_WAIT: 1355498746525
1 row in set (0.00 sec)

どこのイベントで待ちが発生したかの情報なんだけど、なんかやっぱりちょっと粒度が荒い。
ibdata1で待ってるのは知ってるよ! でもdouble write切れないんだよ! って感じ。


* file_summary_by_event_name

mysql> SELECT * FROM file_summary_by_event_name WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+--------------------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| EVENT_NAME                           | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+--------------------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| wait/io/file/innodb/innodb_data_file |       1519 |  2481586224040 |              0 |     1633697065 |  1191383874065 |        163 |     3469540305 |              0 |       21285495 |      896338905 |                  4734976 |        1029 |    206577099575 |               0 |       200755170 |     20881438270 |                 119422976 |        327 |  2271539584160 |              0 |     6946603895 |  1191383874065 |
| wait/io/file/innodb/innodb_log_file  |        434 |  1022073887450 |              0 |     2355008810 |    58576612710 |          6 |       27545210 |              0 |        4590740 |       22502480 |                    69632 |         211 |    262934620795 |               0 |      1246135275 |     58576612710 |                   7907840 |        217 |   759111721445 |              0 |     3498210485 |    41712168190 |
| wait/io/file/sql/FRM                 |       1663 |   374523815050 |              0 |      225209600 |   205524644710 |        739 |   124995936065 |              0 |      169141665 |    29619563050 |                   167156 |         105 |       176487850 |               0 |         1680525 |        15076215 |                     26609 |        819 |   249351391135 |              0 |      304458000 |   205524644710 |
+--------------------------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM file_summary_by_event_name WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_STAR: 1519
           SUM_TIMER_WAIT: 2481586224040
           MIN_TIMER_WAIT: 0
           AVG_TIMER_WAIT: 1633697065
           MAX_TIMER_WAIT: 1191383874065
               COUNT_READ: 163
           SUM_TIMER_READ: 3469540305
           MIN_TIMER_READ: 0
           AVG_TIMER_READ: 21285495
           MAX_TIMER_READ: 896338905
 SUM_NUMBER_OF_BYTES_READ: 4734976
              COUNT_WRITE: 1029
          SUM_TIMER_WRITE: 206577099575
          MIN_TIMER_WRITE: 0
          AVG_TIMER_WRITE: 200755170
          MAX_TIMER_WRITE: 20881438270
SUM_NUMBER_OF_BYTES_WRITE: 119422976
               COUNT_MISC: 327
           SUM_TIMER_MISC: 2271539584160
           MIN_TIMER_MISC: 0
           AVG_TIMER_MISC: 6946603895
           MAX_TIMER_MISC: 1191383874065
1 row in set (0.00 sec)

WAIT, READ, WRITE, MISCに分かれているので多少見やすそうだけど、粒度が荒くてちょっとつらい。
対して、↓の


* file_summary_by_instance

mysql> SELECT * FROM file_summary_by_instance WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+----------------------------------+--------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| FILE_NAME                        | EVENT_NAME                           | OBJECT_INSTANCE_BEGIN | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | SUM_NUMBER_OF_BYTES_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | SUM_NUMBER_OF_BYTES_WRITE | COUNT_MISC | SUM_TIMER_MISC | MIN_TIMER_MISC | AVG_TIMER_MISC | MAX_TIMER_MISC |
+----------------------------------+--------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
| /var/lib/mysql/ibdata1           | wait/io/file/innodb/innodb_data_file |       140132153275264 |        819 |  1604593676840 |        2331560 |     1959210715 |  1191383874065 |        157 |     3398451980 |        3504655 |       21645855 |      896338905 |                  4636672 |         626 |    181296946215 |         7489790 |       289611630 |     20881438270 |                  88735744 |         36 |  1419898278645 |        2331560 |    39441618755 |  1191383874065 |
| /var/lib/mysql/ib_logfile0       | wait/io/file/innodb/innodb_log_file  |       140132153275968 |        431 |  1022063454720 |         382690 |     2371376700 |    58576612710 |          6 |       27545210 |         382690 |        4590740 |       22502480 |                    69632 |         211 |    262934620795 |         7044730 |      1246135275 |     58576612710 |                   7907840 |        214 |   759101288715 |         934780 |     3547202120 |    41712168190 |
| /var/lib/mysql/isucon2/stock.ibd | wait/io/file/innodb/innodb_data_file |       140132153361152 |        310 |   273430299835 |        3560095 |      882033075 |     9891016520 |          0 |              0 |              0 |              0 |              0 |                        0 |         239 |     12590424770 |         8717170 |        52679550 |       827383095 |                  15532032 |         71 |   260839875065 |        3560095 |     3673800900 |     9891016520 |
+----------------------------------+--------------------------------------+-----------------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+--------------------------+-------------+-----------------+-----------------+-----------------+-----------------+---------------------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM file_summary_by_instance WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
                FILE_NAME: /var/lib/mysql/ibdata1
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
    OBJECT_INSTANCE_BEGIN: 140132153275264
               COUNT_STAR: 819
           SUM_TIMER_WAIT: 1604593676840
           MIN_TIMER_WAIT: 2331560
           AVG_TIMER_WAIT: 1959210715
           MAX_TIMER_WAIT: 1191383874065
               COUNT_READ: 157
           SUM_TIMER_READ: 3398451980
           MIN_TIMER_READ: 3504655
           AVG_TIMER_READ: 21645855
           MAX_TIMER_READ: 896338905
 SUM_NUMBER_OF_BYTES_READ: 4636672
              COUNT_WRITE: 626
          SUM_TIMER_WRITE: 181296946215
          MIN_TIMER_WRITE: 7489790
          AVG_TIMER_WRITE: 289611630
          MAX_TIMER_WRITE: 20881438270
SUM_NUMBER_OF_BYTES_WRITE: 88735744
               COUNT_MISC: 36
           SUM_TIMER_MISC: 1419898278645
           MIN_TIMER_MISC: 2331560
           AVG_TIMER_MISC: 39441618755
           MAX_TIMER_MISC: 1191383874065
1 row in set (0.00 sec)

こっちはファイル名まで見られるのでいくらか良い感じ。更新が集中しているテーブルとか見られる。グラフ化したい。


* objects_summary_global_by_type

mysql> SELECT * FROM objects_summary_global_by_type WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+-------------+---------------+---------------+------------+----------------+----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME   | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
+-------------+---------------+---------------+------------+----------------+----------------+----------------+----------------+
| TABLE       | isucon2       | stock         |   29755167 | 25975000162265 |          38885 |         872795 |    60737987310 |
| TABLE       | isucon2       | order_request |        343 |  2256830278395 |         177870 |     6579680030 |  1355498746525 |
| TABLE       | isucon2       | artist        |       3653 |   139255591860 |          25795 |       38120775 |    14495216505 |
+-------------+---------------+---------------+------------+----------------+----------------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM objects_summary_global_by_type WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
   OBJECT_TYPE: TABLE
 OBJECT_SCHEMA: isucon2
   OBJECT_NAME: stock
    COUNT_STAR: 29755167
SUM_TIMER_WAIT: 25975000162265
MIN_TIMER_WAIT: 38885
AVG_TIMER_WAIT: 872795
MAX_TIMER_WAIT: 60737987310
1 row in set (0.00 sec)

こっちはファイルに触れる触れないに関わらずテーブル単位で待ちが発生した回数と時間の累計。
バッファプールだけで完結するSELECTならundoのためにibdata1とib_logfileに触ることはあっても.ibdファイルには触らないだろうから、とかそんなところが違うんだと思う。


* table_io_waits_summary_by_index_usage
* table_io_waits_summary_by_table

mysql> SELECT * FROM table_io_waits_summary_by_index_usage WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+-------------+---------------+---------------+----------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME   | INDEX_NAME     | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_FETCH | SUM_TIMER_FETCH | MIN_TIMER_FETCH | AVG_TIMER_FETCH | MAX_TIMER_FETCH | COUNT_INSERT | SUM_TIMER_INSERT | MIN_TIMER_INSERT | AVG_TIMER_INSERT | MAX_TIMER_INSERT | COUNT_UPDATE | SUM_TIMER_UPDATE | MIN_TIMER_UPDATE | AVG_TIMER_UPDATE | MAX_TIMER_UPDATE | COUNT_DELETE | SUM_TIMER_DELETE | MIN_TIMER_DELETE | AVG_TIMER_DELETE | MAX_TIMER_DELETE |
+-------------+---------------+---------------+----------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| TABLE       | isucon2       | stock         | NULL           |   23155059 | 15405691875035 |          51590 |         665280 |    60737987310 |   23073139 | 14505802679445 |          51590 |         628320 |    29693793745 |       81920 |    899889195590 |         3240930 |        10984820 |     60737987310 |    23073139 |  14505802679445 |           51590 |          628320 |     29693793745 |        81920 |     899889195590 |          3240930 |         10984820 |      60737987310 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | isucon2       | stock         | variation_seat |    6596170 | 10556789714240 |          48125 |        1600060 |    35806416030 |    6596170 | 10556789714240 |          48125 |        1600060 |    35806416030 |           0 |               0 |               0 |               0 |               0 |     6596170 |  10556789714240 |           48125 |         1600060 |     35806416030 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | isucon2       | order_request | NULL           |         94 |  2256572540145 |       24644620 |    24006090570 |  1355498746525 |          0 |              0 |              0 |              0 |              0 |          94 |   2256572540145 |        24644620 |     24006090570 |   1355498746525 |           0 |               0 |               0 |               0 |               0 |           94 |    2256572540145 |         24644620 |      24006090570 |    1355498746525 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
+-------------+---------------+---------------+----------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM table_io_waits_summary_by_index_usage WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: isucon2
     OBJECT_NAME: stock
      INDEX_NAME: NULL
      COUNT_STAR: 23155059
  SUM_TIMER_WAIT: 15405691875035
  MIN_TIMER_WAIT: 51590
  AVG_TIMER_WAIT: 665280
  MAX_TIMER_WAIT: 60737987310
      COUNT_READ: 23073139
  SUM_TIMER_READ: 14505802679445
  MIN_TIMER_READ: 51590
  AVG_TIMER_READ: 628320
  MAX_TIMER_READ: 29693793745
     COUNT_WRITE: 81920
 SUM_TIMER_WRITE: 899889195590
 MIN_TIMER_WRITE: 3240930
 AVG_TIMER_WRITE: 10984820
 MAX_TIMER_WRITE: 60737987310
     COUNT_FETCH: 23073139
 SUM_TIMER_FETCH: 14505802679445
 MIN_TIMER_FETCH: 51590
 AVG_TIMER_FETCH: 628320
 MAX_TIMER_FETCH: 29693793745
    COUNT_INSERT: 81920
SUM_TIMER_INSERT: 899889195590
MIN_TIMER_INSERT: 3240930
AVG_TIMER_INSERT: 10984820
MAX_TIMER_INSERT: 60737987310
    COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
    COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0
1 row in set (0.01 sec)

更に細かく、インデックス単位でのWAIT, READ, WRITE, FETCH, INSERT, UPDATE, DELETE(table_io_waits_summary_by_tableの方はテーブル単位)
INDEX_NAME = 'NULL'ってWITH ROLLUP的な感じで合算なのかと思ったけどどうもそうではなさげ。。なんだろう。
使われてないインデックス(writeばっかりでfetchが少ないとかになるのかな)とか算出するsysスキーマがあったような気がする。これ使ってるのかな。


* table_lock_waits_summary_by_table

mysql> SELECT * FROM table_lock_waits_summary_by_table WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 3;
+-------------+---------------+-------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_READ_NORMAL | SUM_TIMER_READ_NORMAL | MIN_TIMER_READ_NORMAL | AVG_TIMER_READ_NORMAL | MAX_TIMER_READ_NORMAL | COUNT_READ_WITH_SHARED_LOCKS | SUM_TIMER_READ_WITH_SHARED_LOCKS | MIN_TIMER_READ_WITH_SHARED_LOCKS | AVG_TIMER_READ_WITH_SHARED_LOCKS | MAX_TIMER_READ_WITH_SHARED_LOCKS | COUNT_READ_HIGH_PRIORITY | SUM_TIMER_READ_HIGH_PRIORITY | MIN_TIMER_READ_HIGH_PRIORITY | AVG_TIMER_READ_HIGH_PRIORITY | MAX_TIMER_READ_HIGH_PRIORITY | COUNT_READ_NO_INSERT | SUM_TIMER_READ_NO_INSERT | MIN_TIMER_READ_NO_INSERT | AVG_TIMER_READ_NO_INSERT | MAX_TIMER_READ_NO_INSERT | COUNT_READ_EXTERNAL | SUM_TIMER_READ_EXTERNAL | MIN_TIMER_READ_EXTERNAL | AVG_TIMER_READ_EXTERNAL | MAX_TIMER_READ_EXTERNAL | COUNT_WRITE_ALLOW_WRITE | SUM_TIMER_WRITE_ALLOW_WRITE | MIN_TIMER_WRITE_ALLOW_WRITE | AVG_TIMER_WRITE_ALLOW_WRITE | MAX_TIMER_WRITE_ALLOW_WRITE | COUNT_WRITE_CONCURRENT_INSERT | SUM_TIMER_WRITE_CONCURRENT_INSERT | MIN_TIMER_WRITE_CONCURRENT_INSERT | AVG_TIMER_WRITE_CONCURRENT_INSERT | MAX_TIMER_WRITE_CONCURRENT_INSERT | COUNT_WRITE_DELAYED | SUM_TIMER_WRITE_DELAYED | MIN_TIMER_WRITE_DELAYED | AVG_TIMER_WRITE_DELAYED | MAX_TIMER_WRITE_DELAYED | COUNT_WRITE_LOW_PRIORITY | SUM_TIMER_WRITE_LOW_PRIORITY | MIN_TIMER_WRITE_LOW_PRIORITY | AVG_TIMER_WRITE_LOW_PRIORITY | MAX_TIMER_WRITE_LOW_PRIORITY | COUNT_WRITE_NORMAL | SUM_TIMER_WRITE_NORMAL | MIN_TIMER_WRITE_NORMAL | AVG_TIMER_WRITE_NORMAL | MAX_TIMER_WRITE_NORMAL | COUNT_WRITE_EXTERNAL | SUM_TIMER_WRITE_EXTERNAL | MIN_TIMER_WRITE_EXTERNAL | AVG_TIMER_WRITE_EXTERNAL | MAX_TIMER_WRITE_EXTERNAL |
+-------------+---------------+-------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| TABLE       | isucon2       | stock       |       3844 |     1580755330 |          38885 |         411180 |       84922530 |       3612 |     1451608235 |          38885 |         401555 |       84922530 |         232 |       129147095 |          192885 |          556325 |         9873325 |              1806 |             503861820 |                 48125 |                278740 |              84922530 |                            0 |                                0 |                                0 |                                0 |                                0 |                        0 |                            0 |                            0 |                            0 |                            0 |                    0 |                        0 |                        0 |                        0 |                        0 |                1806 |               947746415 |                   38885 |                  524755 |                13140050 |                     114 |                    40675635 |                      192885 |                      356510 |                     1143450 |                             0 |                                 0 |                                 0 |                                 0 |                                 0 |                   0 |                       0 |                       0 |                       0 |                       0 |                        0 |                            0 |                            0 |                            0 |                            0 |                  2 |                1497265 |                 525525 |                 748440 |                 971740 |                  116 |                 86974195 |                   418110 |                   749595 |                  9873325 |
| TABLE       | isucon2       | variation   |       2260 |      722340080 |          58905 |         319550 |        9551850 |       2216 |      705739265 |          58905 |         318395 |        9551850 |          44 |        16600815 |          180180 |          376915 |          656040 |              1108 |             360249120 |                114345 |                324940 |               9551850 |                            0 |                                0 |                                0 |                                0 |                                0 |                        0 |                            0 |                            0 |                            0 |                            0 |                    0 |                        0 |                        0 |                        0 |                        0 |                1108 |               345490145 |                   58905 |                  311465 |                 1170785 |                      20 |                     5014625 |                      180180 |                      250635 |                      358050 |                             0 |                                 0 |                                 0 |                                 0 |                                 0 |                   0 |                       0 |                       0 |                       0 |                       0 |                        0 |                            0 |                            0 |                            0 |                            0 |                  2 |                 860860 |                 338030 |                 430430 |                 522830 |                   22 |                 10725330 |                   423885 |                   487410 |                   656040 |
| TABLE       | isucon2       | artist      |       2236 |      466900280 |          25795 |         208670 |        3642485 |       2224 |      455622090 |          25795 |         204820 |        3642485 |          12 |        11278190 |          210595 |          939785 |         2962960 |              1112 |             224735280 |                 44660 |                201740 |               1176560 |                            0 |                                0 |                                0 |                                0 |                                0 |                        0 |                            0 |                            0 |                            0 |                            0 |                    0 |                        0 |                        0 |                        0 |                        0 |                1112 |               230886810 |                   25795 |                  207515 |                 3642485 |                       4 |                     2527140 |                      210595 |                      631785 |                     1158850 |                             0 |                                 0 |                                 0 |                                 0 |                                 0 |                   0 |                       0 |                       0 |                       0 |                       0 |                        0 |                            0 |                            0 |                            0 |                            0 |                  2 |                1629705 |                 699545 |                 814660 |                 930160 |                    6 |                  7121345 |                   438130 |                  1186570 |                  2962960 |
+-------------+---------------+-------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------------+-----------------------+-----------------------+-----------------------+-----------------------+------------------------------+----------------------------------+----------------------------------+----------------------------------+----------------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------------------+---------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+------------------------------+------------------------------+------------------------------+------------------------------+--------------------+------------------------+------------------------+------------------------+------------------------+----------------------+--------------------------+--------------------------+--------------------------+--------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM table_lock_waits_summary_by_table WHERE count_star <> 0 ORDER BY sum_timer_wait DESC LIMIT 1\G
*************************** 1. row ***************************
                      OBJECT_TYPE: TABLE
                    OBJECT_SCHEMA: isucon2
                      OBJECT_NAME: stock
                       COUNT_STAR: 3844
                   SUM_TIMER_WAIT: 1580755330
                   MIN_TIMER_WAIT: 38885
                   AVG_TIMER_WAIT: 411180
                   MAX_TIMER_WAIT: 84922530
                       COUNT_READ: 3612
                   SUM_TIMER_READ: 1451608235
                   MIN_TIMER_READ: 38885
                   AVG_TIMER_READ: 401555
                   MAX_TIMER_READ: 84922530
                      COUNT_WRITE: 232
                  SUM_TIMER_WRITE: 129147095
                  MIN_TIMER_WRITE: 192885
                  AVG_TIMER_WRITE: 556325
                  MAX_TIMER_WRITE: 9873325
                COUNT_READ_NORMAL: 1806
            SUM_TIMER_READ_NORMAL: 503861820
            MIN_TIMER_READ_NORMAL: 48125
            AVG_TIMER_READ_NORMAL: 278740
            MAX_TIMER_READ_NORMAL: 84922530
     COUNT_READ_WITH_SHARED_LOCKS: 0
 SUM_TIMER_READ_WITH_SHARED_LOCKS: 0
 MIN_TIMER_READ_WITH_SHARED_LOCKS: 0
 AVG_TIMER_READ_WITH_SHARED_LOCKS: 0
 MAX_TIMER_READ_WITH_SHARED_LOCKS: 0
         COUNT_READ_HIGH_PRIORITY: 0
     SUM_TIMER_READ_HIGH_PRIORITY: 0
     MIN_TIMER_READ_HIGH_PRIORITY: 0
     AVG_TIMER_READ_HIGH_PRIORITY: 0
     MAX_TIMER_READ_HIGH_PRIORITY: 0
             COUNT_READ_NO_INSERT: 0
         SUM_TIMER_READ_NO_INSERT: 0
         MIN_TIMER_READ_NO_INSERT: 0
         AVG_TIMER_READ_NO_INSERT: 0
         MAX_TIMER_READ_NO_INSERT: 0
              COUNT_READ_EXTERNAL: 1806
          SUM_TIMER_READ_EXTERNAL: 947746415
          MIN_TIMER_READ_EXTERNAL: 38885
          AVG_TIMER_READ_EXTERNAL: 524755
          MAX_TIMER_READ_EXTERNAL: 13140050
          COUNT_WRITE_ALLOW_WRITE: 114
      SUM_TIMER_WRITE_ALLOW_WRITE: 40675635
      MIN_TIMER_WRITE_ALLOW_WRITE: 192885
      AVG_TIMER_WRITE_ALLOW_WRITE: 356510
      MAX_TIMER_WRITE_ALLOW_WRITE: 1143450
    COUNT_WRITE_CONCURRENT_INSERT: 0
SUM_TIMER_WRITE_CONCURRENT_INSERT: 0
MIN_TIMER_WRITE_CONCURRENT_INSERT: 0
AVG_TIMER_WRITE_CONCURRENT_INSERT: 0
MAX_TIMER_WRITE_CONCURRENT_INSERT: 0
              COUNT_WRITE_DELAYED: 0
          SUM_TIMER_WRITE_DELAYED: 0
          MIN_TIMER_WRITE_DELAYED: 0
          AVG_TIMER_WRITE_DELAYED: 0
          MAX_TIMER_WRITE_DELAYED: 0
         COUNT_WRITE_LOW_PRIORITY: 0
     SUM_TIMER_WRITE_LOW_PRIORITY: 0
     MIN_TIMER_WRITE_LOW_PRIORITY: 0
     AVG_TIMER_WRITE_LOW_PRIORITY: 0
     MAX_TIMER_WRITE_LOW_PRIORITY: 0
               COUNT_WRITE_NORMAL: 2
           SUM_TIMER_WRITE_NORMAL: 1497265
           MIN_TIMER_WRITE_NORMAL: 525525
           AVG_TIMER_WRITE_NORMAL: 748440
           MAX_TIMER_WRITE_NORMAL: 971740
             COUNT_WRITE_EXTERNAL: 116
         SUM_TIMER_WRITE_EXTERNAL: 86974195
         MIN_TIMER_WRITE_EXTERNAL: 418110
         AVG_TIMER_WRITE_EXTERNAL: 749595
         MAX_TIMER_WRITE_EXTERNAL: 9873325
1 row in set (0.00 sec)

テーブルごとのロック待ちの統計…なんだけど、i_sで確認するステータスとは違う変数名がならんでてどこがどう対応してるんだろう。
ブロックしてるクエリーとかも見られるi_sの方が優秀かなここは。


もういちど。

events_statements_summary_by_digest マジ優秀。

【2015/03/31 17:28】
フルパワーにしたらこんな感じだった。
日々の覚書: MySQLのperformance_schemaをフルパワーにするとどんな情報が見られるのか

2015/03/27

MySQL 5.7でやっと(?)1テーブル複数トリガーが仕掛けられるようになった

As of MySQL 5.7.2, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a trigger_order clause that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.
MySQL :: MySQL 5.7 Reference Manual :: 13.1.16 CREATE TRIGGER Syntax


試してみよう。

5.6のフツーの動作は

mysql56> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.02 sec)

mysql56> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.03 sec)

mysql56> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql56> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

もちろんこう。


5.7.6だと

mysql57> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql57> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 VALUES (2, 'two');
Query OK, 1 row affected (0.04 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
+-----+--------------------+
2 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_2
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:53:47.85
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

当たり前だけど張れた。順番の制御は、FOR EACH ROWのあとにPRECEDESまたはFOLLOWSで既存のトリガーを指定する(省略時は最後のトリガーの更に次に追加されるのかな)


mysql57> DROP TRIGGER before_insert_2;
Query OK, 0 rows affected (0.01 sec)

mysql57> CREATE TRIGGER before_insert_3 BEFORE INSERT ON t1 FOR EACH ROW PRECEDES before_insert_1 UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> SELECT * FROM t2;
+-----+--------------------+
| num | val                |
+-----+--------------------+
|   1 | one                |
|   2 | 残念だったな       |
|   3 | three              |
+-----+--------------------+
3 rows in set (0.00 sec)

mysql57> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_3
               Event: INSERT
               Table: t1
           Statement: UPDATE t2 SET val = '残念だったな' WHERE num = NEW.num
              Timing: BEFORE
             Created: 2015-03-27 02:56:45.90
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: before_insert_1
               Event: INSERT
               Table: t1
           Statement: INSERT INTO t2 SET num = NEW.num, val = NEW.val
              Timing: BEFORE
             Created: 2015-03-27 02:53:23.66
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

UPDATE t2が先に実行されるのでt2にレコードがなくて空ぶって、そのあとINSERT INTO t2でコピーされる。
ちょっと便利になるかも知れない。

2015/03/25

2015年版 InnoDB Monitorの仲間たち(InnoDBエンジン本体のアレ)

日々の覚書: InnoDB Monitorの仲間たち(InnoDBエンジン本体のアレ) で"CREATE TABLE innodb_monitor .."的なものを紹介しました。あれから2年。

というかよく調べてみたら1年前。

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.4 (2014-03-31, Milestone 14)

InnoDB: New global configuration parameters, innodb_status_output and innodb_status_output_locks, allow you to dynamically enable and disable the standard InnoDB Monitor and InnoDB Lock Monitor for periodic output. Previously, enabling and disabling these monitors for periodic output required creating and dropping specially named tables (innodb_monitor and innodb_lock_monitor). For additional information, see InnoDB Monitors.

というわけで今後は"SET GLOBAL innodb_status_output{,_locks} = 1"で有効化ですね。
CREATE TABLE innodb_lock_monitor (a int); ってしたらフツーにテーブルが出来ただけで何も吐いてくれなくて困ったのでメモを残しておきます。

MySQL :: MySQL 5.7 Reference Manual :: 14.15.2 Enabling InnoDB Monitors


あ、デフォルトでエラーログに吐くのは相変わらずですが、エラーログでなくdatadir/innodb_status.pidファイルに吐くように設定できるオプションがあります。pid部分は文字列じゃなくてプロセスIDが入るので、自分で名前や場所を変えることは今のところできなさげ。

MySQL :: MySQL 5.7 Reference Manual :: 14.12 InnoDB Startup Options and System Variables

2015/03/23

MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

MySQL 5.7.6-m16で導入されたgenerated columnについてメモ。タイトルでしゃらっと関数インデックスとか言っているけれど、俺はその機能を知らない。

MySQL :: MySQL 5.7 Reference Manual :: 13.1.14 CREATE TABLE Syntax

generated columnはあるカラムの値に関数を噛ませた結果をカラムとして実体化できたり、実体化せずに毎回計算できたりするカラム定義のことらしくて、


mysql57> CREATE TABLE t1 (num int, val varchar(32)) Engine = MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql57> INSERT INTO t1 SET num = 1, val = 'one';
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

$ od -c t1.MYD
0000000 003  \0  \n 006  \0 376 001  \0  \0  \0 003   o   n   e  \0  \0
0000020  \0  \0  \0  \0
0000024

こんなテーブルに対して


mysql57> ALTER TABLE t1 ADD new_val varchar(96) AS (REPEAT(val, 3)) STORED;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql57> ALTER TABLE t1 ADD KEY (new_val);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

"STORED"キーワードは演算結果を実体化してデータとして格納するオプション。STOREDオプションで作成されたgenerated columnにはインデックスを作成することができる。

mysql57> SELECT * FROM t1;
+-----+------+-----------+
| num | val  | new_val   |
+-----+------+-----------+
|   1 | one  | oneoneone |
+-----+------+-----------+
1 row in set (0.00 sec)

$ od -c t1.MYD
0000000 003  \0 024  \0  \0 374 001  \0  \0  \0 003   o   n   e  \t   o
0000020   n   e   o   n   e   o   n   e
0000030

$ od -c t1.MYI
..
*
0004000  \0 024  \0 001  \t   o   n   e   o   n   e   o   n   e  \0  \0
0004020  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0
*
..

一応、インデックスとしても実体化されているぽい。


mysql57> INSERT INTO t1 SET num = 2, val = 'two';
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t1 SET num = 3, val = 'three';
Query OK, 1 row affected (0.00 sec)

mysql57> explain SELECT * FROM t1 WHERE new_val LIKE 'one%';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | new_val       | new_val | 99      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql57> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                         |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t1`.`new_val` AS `new_val` from `d1`.`t1` where (`d1`.`t1`.`new_val` like 'one%') |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

極めてフツーにカラムを作って、極めてフツーに値を突っ込んで、極めてフツーに検索しているのと同じ感じ(当たり前)

ただし、generated columnからフツーの(?)カラムに対する逆関数は定義できないので、


mysql57> INSERT INTO t1 SET num = 4, new_val = 'fourfourfour';
ERROR 3105 (HY000): The value specified for generated column 'new_val' in table 't1' is not allowed.

突っ込めない。
おお、噂(?)の3000番台エラーだ。


で、このgenerated columnでやりたいのって

mysql57> create table json (id int auto_increment, col1 varchar(1000), primary key(id));
Query OK, 0 rows affected (0.05 sec)

mysql57> INSERT INTO json(id, col1) VALUES (1, '{"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]}');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO json(id, col1) VALUES (2, '{"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]}');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO json(id, col1) VALUES (3, '{"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]}');
Query OK, 1 row affected (0.02 sec)

mysql57> INSERT INTO json(id, col1) VALUES (4, '{"id":4,"Name":"Yoshiaki Yamasaki"}');
Query OK, 1 row affected (0.01 sec)

mysql57> ALTER TABLE json ADD price int unsigned AS (json_extract(col1, 'price')) STORED;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql57> ALTER TABLE json ADD KEY (price);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM json;
+----+--------------------------------------------------------------------------------+---------+
| id | col1                                                                           | price   |
+----+--------------------------------------------------------------------------------+---------+
|  1 | {"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]}      |   50000 |
|  2 | {"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]} |  300000 |
|  3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]}     | 1000000 |
|  4 | {"id":4,"Name":"Yoshiaki Yamasaki"}                                            |    NULL |
+----+--------------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)

mysql57> SELECT * FROM json WHERE price > 500000;
+----+----------------------------------------------------------------------------+---------+
| id | col1                                                                       | price   |
+----+----------------------------------------------------------------------------+---------+
|  3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]} | 1000000 |
+----+----------------------------------------------------------------------------+---------+
1 row in set (0.00 sec)

こーゆーことで合ってますかね? :)


( ´-`).oO(サンプルのJSONは 日本語JSON UDFの一番詳しいスライド から拝借した


ところでADD price int unsigned AS (..) STORED KEYってやるとPRIMARYつけてなくてもPRIMARY KEYにしたがるんだけどたぶんバグだよな。。

【2015/03/23 19:44】
とりあえずばぐれぽしてみた。
MySQL Bugs: #76450: generated column with "KEY" makes PRIMARY KEY


【2015/03/24 16:34】
もともと、カラム定義に"KEY"キーワードを与えた場合はPRIMARY KEYを作る動作なんだと教えてもらった(´・ω・`)

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

http://dev.mysql.com/doc/refman/5.7/en/create-table.html

2015/03/20

MySQL 5.7でLOAD DATA INFILEに失敗する時に疑うこと(--secure-file-privの暗黙のデフォルトが少し変わった)

日々の覚書: MySQL 5.7.6は--secure-file-privを設定してないとWarningを吐くようになった のちょっと続き。

--secure-file-priv そのものについては↑の記事を読んでいただけると幸い。LOAD DATA INFILEやSELECT .. INTO OUTFILEの動きを制限する。


ドキュメントのsysvarのところに書いてある けれど、
* -DINSTALL_LAYOUT=STANDALONE, WIN でビルドされた場合は暗黙のデフォルトなし
* -DINSTALL_LAYOUT=DEB, RPM, SLES, SVR4 でビルドされた場合は"/var/lib/mysql-files"
* それ以外の設定の場合、"CMAKE_INSTALL_PREFIX(MySQLから見るとbasedir)の下のmysql-files"
が暗黙のデフォルトになる。

ソースはこのへん。
mysql-server/install_layout.cmake at 35e498b12e4e64e639cb8096d1a7362fd3272fa6 · mysql/mysql-server


INSTALL_LAYOUTの取りうる値はこのへんなので、FREEBSD, OSX, TARGZあたりがbasedir/mysql-filesになるのだろうか(ところで、Oracle公式のLinux Genericのバイナリー.tar.gzってSTANDALONEでビルドしてあるんだけど、他のプラットフォーム用のでTARGZ使ってるところもあるのかな)
mysql-server/install_layout.cmake at 35e498b12e4e64e639cb8096d1a7362fd3272fa6 · mysql/mysql-server



取り敢えず、バイナリーの.tar.gzは未設定なのと、Oracle Linux 6用のrpm bundleに入ってるやつはこの通りになるのを確認した。OSXとか誰か試してほしい。

あと、新しくsecure-file-privにディレクトリパスでなくて"NULL"を指定すると、いかなるディレクトリーに対してもFile_privがらみの操作を行えなくできるんだけど、


$ bin/mysqld_safe --no-defaults &

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LOAD_FILE('/etc/hosts');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOAD_FILE('/etc/hosts')                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 172.17.0.3    0a8104f4882d
127.0.0.1       localhost
::1     localhost ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



$ bin/mysqld_safe --no-defaults --secure-file-priv=NULL &

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT LOAD_FILE('/etc/hosts');
+-------------------------+
| LOAD_FILE('/etc/hosts') |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set (0.00 sec)

:(;゙゚'ω゚'): 区別がつかねぇ。。


【2015/03/20 12:44】
区別が付かない件はばぐれぽしました。

MySQL Bugs: #76401: Can't distinguish secure_file_priv = NULL and ""

2015/03/13

MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.6 (2015-03-09, Milestone 16) から抜粋。

MySQL Server from Community Edition distributions now tries to deploy with SSL support enabled automatically if no SSL options are specified explicitly and it finds any of the ca.pem, server-cert.pem, and server-key.pem files in the data directory. In this case, clients can use a secure connection merely by specifying --ssl on the command line.

今までは--ssl指定するとついでに--ssl-ca, --ssl-cert, --ssl-keyを指定してやらないといけなかったのが、datadirからその辺のファイルが見つかれば指定しなくてもよしなにやってくれるらしい。

あと、サーバー側の--sslはデフォルトで有効状態に。

というわけで、 前回 mysqld --initializeがSSL証明書を作ろうとして失敗したと思っていたもの は、実はSSLを有効化しようとしていた(そして、証明書がdatadirにないので失敗していた)だけだった。


mysqld --initializeで作ったdatadirで起動すると、*.pemファイルはないので、

$ bin/mysqld --no-defaults --initialize-insecure
..

$ ll data/*.pem
ls: cannot access data/*.pem: No such file or directory

$ bin/mysqld_safe --no-defaults

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

$ bin/mysql --no-defaults -uroot --ssl
ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it

使えない。

mysql_install_dbで作ったdatadirには*.pemがあるので(ただし、mysql_install_db --insecureだと作られない)


$ ./bin/mysql_install_db --no-defaults --basedir=./ --datadir=./data
..

$ ll data/*.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/ca-key.pem
-rw-r--r-- 1 mysql mysql 1082 Mar 13 13:27 data/ca.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:27 data/client-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/client-key.pem
-rw------- 1 mysql mysql 1675 Mar 13 13:27 data/private_key.pem
-rw-r--r-- 1 mysql mysql  451 Mar 13 13:27 data/public_key.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:27 data/server-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:27 data/server-key.pem

$ bin/mysqld_safe --no-defaults

mysql> SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

$ bin/mysql --no-defaults -uroot --ssl
mysql> status
--------------
bin/mysql  Ver 14.14 Distrib 5.7.6-m16, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.6-m16 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 53 sec

Threads: 1  Questions: 13  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.245
--------------

何の面倒なこともなしにSSLが有効化できる。これはいい。

mysql_install_dbからは mysql_ssl_rsa_setup というコマンドを呼んでいるだけなので、これを手で叩いてやれば他の方法でセットアップしてもOK。


$ bin/mysql_ssl_rsa_setup
OpenSSL 1.0.1e-fips 11 Feb 2013
Generating a 2048 bit RSA private key
...............+++
..........+++
writing new private key to 'ca-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_CA_Certificate
Getting Private key
Generating a 2048 bit RSA private key
...........................................................................+++
...........................................................................+++
writing new private key to 'server-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_Server_Certificate
Getting CA Private Key
Generating a 2048 bit RSA private key
.................+++
...................+++
writing new private key to 'client-key.pem'
-----
writing RSA key
Signature ok
subject=/CN=MySQL_Server_5.7.6-m16_Auto_Generated_Server_Certificate
Getting CA Private Key
server-cert.pem: OK
client-cert.pem: OK
Generating RSA private key, 2048 bit long modulus
............................................................................................................................+++
............+++
e is 65537 (0x10001)
writing RSA key

$ ll data/*.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/ca-key.pem
-rw-r--r-- 1 mysql mysql 1082 Mar 13 13:33 data/ca.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:33 data/client-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/client-key.pem
-rw------- 1 mysql mysql 1675 Mar 13 13:34 data/private_key.pem
-rw-r--r-- 1 mysql mysql  451 Mar 13 13:34 data/public_key.pem
-rw-r--r-- 1 mysql mysql 1086 Mar 13 13:33 data/server-cert.pem
-rw------- 1 mysql mysql 1679 Mar 13 13:33 data/server-key.pem

SSLのセットアップがラクになっていい :)

2015/03/12

MySQL 5.7.6でmysql_install_db(とmysqld --initialize)がSSL証明書のファイルを作っているぽいのであとで調べたい

MySQL 5.7.5のmysql_install_dbだと


$ bin/mysql_install_db --no-defaults --basedir=./ --datadir=./test_575 -v
2015-03-12 11:23:45 [NOTE]    Creating data directory ./test_575
2015-03-12 11:23:45 [NOTE]    Generating random password to /root/.mysql_secret...done.
2015-03-12 11:23:45 [NOTE]    Executing /opt/mysql/5.7.5/bin/mysqld --no-defaults --bootstrap --datadir=./test_575 --lc-messages-dir=./share --lc-messages=en_US --basedir=.
2015-03-12 11:23:46 [NOTE]    Creating system tables...done.
2015-03-12 11:23:46 [NOTE]    Filling system tables with data...done.
2015-03-12 11:23:47 [NOTE]    Filling help table with data...done.
2015-03-12 11:23:47 [NOTE]    Creating default user root@localhost
2015-03-12 11:23:47 [NOTE]    Creating default proxy root@localhost
2015-03-12 11:23:48 [NOTE]    Success!

.mysql_secretを作ってシステムテーブルを作ってroot@localhostを作るだけだったのが、5.7.6のmysql_install_dbでは


$ bin/mysql_install_db --no-defaults --basedir=./ --datadir=./test_mysql_install_db -v
2015-03-12 11:25:04 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2015-03-12 11:25:04 [NOTE]    Creating data directory ./test_mysql_install_db
2015-03-12 11:25:04 [NOTE]    Generating random password to /home/ttanaka/.mysql_secret...done.
2015-03-12 11:25:04 [NOTE]    Executing /usr/mysql/5.7.6/bin/mysqld --no-defaults --bootstrap --datadir=./test_mysql_install_db --lc-messages-dir=./share --lc-messages=en_US --basedir=.
2015-03-12 11:25:06 [NOTE]    Creating system tables...done.
2015-03-12 11:25:06 [NOTE]    Filling system tables with data...done.
2015-03-12 11:25:07 [NOTE]    Filling help table with data...done.
2015-03-12 11:25:07 [NOTE]    Creating default user root@localhost
2015-03-12 11:25:07 [NOTE]    Creating default proxy root@localhost
2015-03-12 11:25:07 [WARNING] The bootstrap log isn't empty:
2015-03-12 11:25:07 [WARNING] mysqld:
2015-03-12 11:25:07 [NOTE]    Generating SSL Certificates
OpenSSL 1.0.1e-fips 11 Feb 2013
server-cert.pem: OK
client-cert.pem: OK

$ ll test_mysql_install_db
合計 110640
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 ca-key.pem
-rw-r--r-- 1 mysql mysql     1082  3月 12 11:25 2015 ca.pem
-rw-r--r-- 1 mysql mysql     1086  3月 12 11:25 2015 client-cert.pem
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 client-key.pem
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile0
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile1
-rw-r----- 1 mysql mysql 12582912  3月 12 11:25 2015 ibdata1
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 mysql
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 performance_schema
-rw------- 1 mysql mysql     1675  3月 12 11:25 2015 private_key.pem
-rw-r--r-- 1 mysql mysql      451  3月 12 11:25 2015 public_key.pem
-rw-r--r-- 1 mysql mysql     1086  3月 12 11:25 2015 server-cert.pem
-rw------- 1 mysql mysql     1679  3月 12 11:25 2015 server-key.pem

そのあとにサーバー用, クライアント用のSSL証明書を作っているぽい。
( ´-`).oO(mysqld: で止まってるワーニングメッセージがあるんだけど、これ MeCabのやつ と一緒でOpenSSLのライブラリーから戻ってきた何かをうまくハンドルできてないんじゃないか。。

そして5.7.6のmysqld --initialize。


$ bin/mysqld --no-defaults --initialize-insecure --datadir=./test_mysqld --log-error-verbosity=3
2015-03-12T02:25:54.901505Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-12T02:25:54.901602Z 0 [Note] Ignoring --secure-file-priv value as server is running with --initialize(-insecure) or --bootstrap.
2015-03-12T02:25:54.901846Z 0 [Note] bin/mysqld (mysqld 5.7.6-m16) starting as process 23958 ...
2015-03-12T02:25:54.938092Z 0 [Note] Creating the data directory /usr/mysql/5.7.6/test_mysqld/
..
2015-03-12T02:25:56.202015Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-03-12T02:25:56.202631Z 0 [Warning] Failed to setup SSL
2015-03-12T02:25:56.202643Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-12T02:25:56.203497Z 1 [Note] Creating the system database
2015-03-12T02:25:56.203521Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2015-03-12T02:25:56.204099Z 1 [Note] Creating the system tables
2015-03-12T02:25:57.234370Z 1 [Note] Filling in the system tables, part 1
2015-03-12T02:25:57.235332Z 1 [Note] Filling in the system tables, part 2
2015-03-12T02:25:57.235347Z 1 [Note] Filling in the mysql.help table
2015-03-12T02:25:57.969635Z 1 [Note] Bootstrapping complete
2015-03-12T02:25:57.969924Z 0 [Note] Giving 0 client threads a chance to die gracefully
2015-03-12T02:25:57.969945Z 0 [Note] Shutting down slave threads
2015-03-12T02:25:57.969955Z 0 [Note] Forcefully disconnecting 0 remaining clients
2015-03-12T02:25:57.970165Z 0 [Note] Binlog end
2015-03-12T02:25:57.970630Z 0 [Note] InnoDB: FTS optimize thread exiting.
2015-03-12T02:25:57.970950Z 0 [Note] InnoDB: Starting shutdown...
2015-03-12T02:25:59.100867Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2321861
2015-03-12T02:25:59.104330Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

$ ll test_mysqld/
合計 110604
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile0
-rw-r----- 1 mysql mysql 50331648  3月 12 11:25 2015 ib_logfile1
-rw-r----- 1 mysql mysql 12582912  3月 12 11:25 2015 ibdata1
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 mysql
drwxr-x--- 2 mysql mysql     4096  3月 12 11:25 2015 performance_schema

同じくなんかSSL証明書を作ろうとしてるけど失敗してる。全く同じ動作ってわけではないぽい。ちょっと調べようか。


【2015/03/13 13:36】
調べてみたところ、作ろうとしているのではなく、使おうとして(証明書がないので)失敗しているのであった。
日々の覚書: MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった

2015/03/10

MySQL 5.7.6のInnoDB日本語全文検索 ngram

日々の覚書: プレビュー "MySQL 5.7.6のInnoDB日本語全文検索 MeCab Plugin" に引き続き、今度はngramなトークナイザー。ドキュメントは ここ

MeCab Pluginと違って特にINSTALL PLUGINとかせずに即使える。


mysql> ALTER TABLE articles ADD FULLTEXT KEY (title, content) WITH PARSER ngram;
Query OK, 0 rows affected (6 hours 25 min 20.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

( д ) ゚ ゚ 6時間半!? しょうがないとはいえ、MeCabのときは1時間半でお釣りが来てたのにと思うとちょっとしょんぼり。

時間もさることながら、このやり方(データをインポートしてからALTER TABLEでインデックス追加)だと容量がかなり持っていかれる。


[root@v157-7-235-254 mysql]# while true ; do
> date ; df -h
> echo ""
> sleep 60
> done
Tue Mar 10 10:13:41 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   16G   76G  18% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot
..
Tue Mar 10 16:12:45 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   74G   18G  81% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot
..
Tue Mar 10 16:44:51 JST 2015
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
                       97G   26G   66G  28% /
tmpfs                 499M     0  499M   0% /dev/shm
/dev/vda1             485M   32M  428M   7% /boot


# lsof -p `pidof mysqld`
COMMAND  PID  USER   FD   TYPE             DEVICE    SIZE/OFF    NODE NAME
mysqld  3049 mysql  cwd    DIR              253,0        4096  919114 /usr/local/mysql/data
mysqld  3049 mysql  rtd    DIR              253,0        4096       2 /
mysqld  3049 mysql  txt    REG              253,0   193624979  919090 /usr/local/mysql/bin/mysqld
mysqld  3049 mysql  mem    REG              253,0        5624  914553 /lib64/libaio.so.1.0.1
mysqld  3049 mysql  DEL    REG              253,0              914859 /lib64/libfreebl3.so
mysqld  3049 mysql  mem    REG              253,0    49199027 2231548 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/sys.dic
mysqld  3049 mysql  mem    REG              253,0     3463716 2231541 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/matrix.bin
mysqld  3049 mysql  mem    REG              253,0      262496 2231546 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/char.bin
mysqld  3049 mysql  mem    REG              253,0     3988451 2231491 /usr/local/mysql/lib/plugin/libpluginmecab.so
mysqld  3049 mysql  mem    REG              253,0       65928  914006 /lib64/libnss_files-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913936 /lib64/libc-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              915115 /lib64/libgcc_s-4.4.7-20120601.so.1.#prelink#.JjQM5q
mysqld  3049 mysql  DEL    REG              253,0              913990 /lib64/libm-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              263139 /usr/lib64/libstdc++.so.6.0.13.#prelink#.htv5XJ
mysqld  3049 mysql  DEL    REG              253,0              914081 /lib64/librt-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913976 /lib64/libdl-2.12.so
mysqld  3049 mysql  DEL    REG              253,0              913940 /lib64/libcrypt-2.12.so.#prelink#.3QGWZ8
mysqld  3049 mysql  DEL    REG              253,0              913960 /lib64/libpthread-2.12.so.#prelink#.xdy3QH
mysqld  3049 mysql  DEL    REG              253,0              913930 /lib64/ld-2.12.so
mysqld  3049 mysql  mem    REG              253,0        5690 2231545 /usr/local/mysql/lib/mecab/dic/ipadic_utf-8/unk.dic
mysqld  3049 mysql    0r   CHR                1,3         0t0    3788 /dev/null
mysqld  3049 mysql    1w   REG              253,0       15868  919127 /usr/local/mysql/data/error.log
mysqld  3049 mysql    2w   REG              253,0       15868  919127 /usr/local/mysql/data/error.log
mysqld  3049 mysql    3uW  REG              253,0    79691776  919158 /usr/local/mysql/data/ibdata1
..
mysqld  3049 mysql   45uW  REG              253,0 11286872064 1305615 /usr/local/mysql/data/wikipedia/articles.ibd
mysqld  3049 mysql   46uW  REG              253,0       98304 1305604 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_1.ibd
mysqld  3049 mysql   47uW  REG              253,0       98304 1305607 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_2.ibd
mysqld  3049 mysql   48uW  REG              253,0       98304 1305611 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_3.ibd
mysqld  3049 mysql   49uW  REG              253,0       98304 1305613 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_4.ibd
mysqld  3049 mysql   50uW  REG              253,0       98304 1305614 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_5.ibd
mysqld  3049 mysql   51uW  REG              253,0       98304 1305616 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_000000000000013a_INDEX_6.ibd
mysqld  3049 mysql   52uW  REG              253,0       98304 1305603 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_DELETED.ibd
mysqld  3049 mysql   53uW  REG              253,0       98304 1305605 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_DELETED_CACHE.ibd
mysqld  3049 mysql   54uW  REG              253,0       98304 1305606 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_BEING_DELETED.ibd
mysqld  3049 mysql   55uW  REG              253,0       98304 1305610 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_BEING_DELETED_CACHE.ibd
mysqld  3049 mysql   56uW  REG              253,0       98304 1305612 /usr/local/mysql/data/wikipedia/FTS_00000000000000f7_CONFIG.ibd
mysqld  3049 mysql   58u   REG              253,0 10842275840 1046745 /data/tmp/ibDDeVbj (deleted)
mysqld  3049 mysql   59u   REG              253,0   696254464 1046746 /data/tmp/ibaNLTUS (deleted)
mysqld  3049 mysql   60u   REG              253,0  1790967808 1046747 /data/tmp/ibNm7Vmi (deleted)
mysqld  3049 mysql   61u   REG              253,0  1623195648 1046748 /data/tmp/ibl17OJP (deleted)
mysqld  3049 mysql   62u   REG              253,0  8006926336 1046749 /data/tmp/ibBoSvqQ (deleted)
mysqld  3049 mysql   63u   REG              253,0  8006926336 1046750 /data/tmp/ibudnROb (deleted)
mysqld  3049 mysql   64u   REG              253,0 10904141824 1046751 /data/tmp/ibNbFRxL (deleted)
mysqld  3049 mysql   65u   REG              253,0   698351616 1046752 /data/tmp/ibYchSgl (deleted)
mysqld  3049 mysql   66u   REG              253,0  1802502144 1046753 /data/tmp/ibYYJGHW (deleted)
mysqld  3049 mysql   67u   REG              253,0  1631584256 1046754 /data/tmp/ibQOsAMA (deleted)
mysqld  3049 mysql   68u   REG              253,0  8050966528 1046755 /data/tmp/ibiSAO6i (deleted)
mysqld  3049 mysql   69u   REG              253,0  8050966528 1046756 /data/tmp/ibGrGYaE (deleted)
mysqld  3049 mysql   70u   REG              253,0   361758720 1046757 /data/tmp/ibQvfudp (deleted)
mysqld  3049 mysql   71u   REG              253,0   359661568 1046758 /data/tmp/ibN0BTAJ (deleted)

最終的に元に戻るとはいえ、tmpdirに数GB単位のテンポラリーファイルをいくつもつくられてしかもそれが途中で解放されないのは結構つらい。。


まあそこはそれとして


mysql> CREATE TABLE t1 (val varchar(32));
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO t1 VALUES ('にっこにっこにー');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM t1;
+--------------------------+
| val                      |
+--------------------------+
| にっこにっこにー         |
+--------------------------+
1 row in set (0.00 sec)

こんなサンプルがあるじゃろ?


mysql> SELECT @@ngram_token_size;
+--------------------+
| @@ngram_token_size |
+--------------------+
|                  2 |
+--------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ADD FULLTEXT KEY idx_2gram(val) WITH PARSER ngram;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1';
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.36 sec)

mysql> SELECT * FROM information_schema.innodb_ft_index_cache ORDER BY position;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| にっ   |            2 |           2 |         1 |      2 |        0 |
| っこ   |            2 |           2 |         1 |      2 |        3 |
| こに   |            2 |           2 |         1 |      2 |        6 |
| っこ   |            2 |           2 |         1 |      2 |        9 |
| にっ   |            2 |           2 |         1 |      2 |        9 |
| こに   |            2 |           2 |         1 |      2 |        9 |
| にー   |            2 |           2 |         1 |      2 |       18 |
+--------+--------------+-------------+-----------+--------+----------+
7 rows in set (0.02 sec)

mysql> SELECT * FROM information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 0     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.03 sec)

ngram_token_size変数でトークンのサイズを制御できる。この変数はオンライン変更できないので、my.cnfに設定してからmysqld再起動。


mysql> SET GLOBAL innodb_ft_aux_table = 'd1/t1';
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.31 sec)

mysql> SELECT * FROM information_schema.innodb_ft_index_cache ORDER BY position;
+-----------+--------------+-------------+-----------+--------+----------+
| WORD      | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+-----------+--------------+-------------+-----------+--------+----------+
| にっこ    |            2 |           2 |         1 |      2 |        0 |
| っこに    |            2 |           2 |         1 |      2 |        3 |
| こにっ    |            2 |           2 |         1 |      2 |        6 |
| にっこ    |            2 |           2 |         1 |      2 |        9 |
| っこに    |            2 |           2 |         1 |      2 |        9 |
| こにー    |            2 |           2 |         1 |      2 |       15 |
+-----------+--------------+-------------+-----------+--------+----------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.innodb_ft_config;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 0     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+
4 rows in set (0.01 sec)

あ、あれ? 再作成しなくても切りなおされるの?;