GA

2015/07/01

innodb_stats_on_metadataが0でも問題なく（？）統計情報は再計算されるよ

innodb_stats_on_metadata=1でディスク容量激増とCPU負荷が発生 | DEVLAB を読んだ誰か（忘れた）に「innodb_stats_on_metadata= 0だと統計情報ズレない？ 手でANALYZE TABLEしないといけないの？」って聞かれて答えたメモ（だと思う）

そもそも8ページとか20ページじゃ全然足りないじゃんというのはここでいう「問題なく」の中には含まれない。

innodb_stats_on_metadataは *メタデータにアクセス(=information_schema, SHOW TABLE STATUSなど)* した時に統計情報を再作成するかどうかのフラグで、データが大量に更新された時の統計情報の再作成は ここらへん でinnodb_stats_on_metadataを見ずにトリガーされている。


(gdb) b dict_stats_update
Breakpoint 1 at 0xa7c997: file /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc, line 3085.

gdbを突き刺しておいて


mysql56> SELECT @@innodb_stats_on_metadata;
+----------------------------+
| @@innodb_stats_on_metadata |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql56> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 99750
 Avg_row_length: 68
    Data_length: 6832128
..

統計情報はこんな感じ。
ここに、統計情報がアップデートされるくらいの行数を変更するクエリーを投げ込むと


mysql56> DELETE FROM t1 WHERE num < 10000;
ここで止まる。 
Breakpoint 1, dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
3085            if (table->ibd_file_missing) {
(gdb) bt
#0  dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x0000000000a7e25a in dict_stats_process_entry_from_recalc_pool (arg=)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:313
#2  dict_stats_thread (arg=) at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:355
#3  0x00000036dac079d1 in start_thread (arg=0x7fdaf75fe700) at pthread_create.c:301
#4  0x00000036da8e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115
バックグラウンドスレッドらしい。こんなのいたのか。。 gdbを放してやるとクエリーが返ってきて、 
mysql56> DELETE FROM t1 WHERE num < 10000;
Query OK, 9999 rows affected (28.51 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..
統計情報は無事に更新されている。 
mysql56> DELETE FROM t1 WHERE num < 100;
Query OK, 0 rows affected (0.01 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..

テーブルの10%または6.25%が更新されたら、っぽいので、それよりずっと小さい値にすればしばらく待ってても統計情報の更新は走らない。

他に統計情報が更新されるトリガーとしてはこのへん？

* テーブルキャッシュが存在しない場合は統計情報がアップデートされる。
* テーブルキャッシュが足りていないと頻繁にこれが走る羽目に遭う。 

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a4fa4 in dict_stats_init (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/include/dict0stats.ic:174
#2  ha_innobase::open (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:4859
#3  0x000000000059370e in handler::ha_open (this=0x7f8bea635410, table_arg=,
    name=0x7f8bea634f38 "./d1/City", mode=2, test_if_locked=2) at /home/yoku0825/mysql-5.6.25/sql/handler.cc:2505
#4  0x0000000000766c8c in open_table_from_share (thd=0x7f8be9f45000, share=,
    alias=, db_stat=39, prgflag=, ha_open_flags=0, outparam=0x7f8bea61da00,
    is_create_table=false) at /home/yoku0825/mysql-5.6.25/sql/table.cc:2355
#5  0x000000000068e2fe in open_table (thd=0x7f8be9f45000, table_list=0x7f8bea65a210, ot_ctx=0x7f8bfb8a74d0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:3167
#6  0x0000000000690d48 in open_and_process_table (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:4662
#7  open_tables (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5095
#8  0x0000000000690ea8 in open_normal_and_derived_tables (thd=0x7f8be9f45000, tables=0x7f8bea65a210,
    flags=) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5791
#9  0x00000000006d64d3 in execute_sqlcom_select (thd=0x7f8be9f45000, all_tables=0x7f8bea65a210)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5107
#10 0x00000000006db682 in mysql_execute_command (thd=0x7f8be9f45000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#11 0x00000000006dde67 in mysql_parse (thd=0x7f8be9f45000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#12 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8be9f45000,
    packet=0x7f8bea6a6001 "SELECT * FROM City", packet_length=3932528674) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#13 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#14 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea251000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#15 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d56a0) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#16 0x00000030628079d1 in start_thread (arg=0x7f8bfb8a9700) at pthread_create.c:301
#17 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


* ANALYZE TABLE実行時はもちろん統計情報をアップデートする 

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e410, flag=28, is_analyze=true)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000009a1c23 in ha_innobase::analyze (this=, thd=,
    check_opt=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:11186
#3  0x0000000000821cc5 in mysql_admin_table (thd=0x7f8bea738000, tables=0x0, check_opt=0x7f8bea73ab40,
    operator_name=0xbae3c6 "analyze", lock_type=TL_READ_NO_INSERT, open_for_modify=true, repair_table_use_frm=false,
    extra_open_options=0, prepare_func=0, operator_func=
    (int (handler::*)(handler *, THD *, HA_CHECK_OPT *)) 0x592970 ,
    view_operator_func=0) at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:654
#4  0x0000000000822a08 in Sql_cmd_analyze_table::execute (this=, thd=0x7f8bea738000)
    at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:1077
#5  0x00000000006db2dc in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:4976
#6  0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#7  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000, packet=0x7f8bea73c001 "",
    packet_length=3933540386) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#8  0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#9  0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#10 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#11 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#12 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


* innodb_stats_on_metadata= 1だとSHOW TABLE STATUSでも呼ばれる。 

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e810, flag=212, is_analyze=false)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000007125f0 in get_schema_tables_record (thd=0x7f8bea738000, tables=0x7f8bea7734d8, table=0x7f8bea472010,
    res=, db_name=, table_name=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4543
#3  0x000000000070c1ad in fill_schema_table_by_open (thd=0x7f8bea738000, is_show_fields_or_keys=false, table=0x7f8bea472010,
    schema_table=0x12496a0, orig_db_name=0x7f8bfb866210, orig_table_name=0x7f8be9f2e410,
    open_tables_state_backup=0x7f8bfb866170, can_deadlock=false) at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:3588
#4  0x00000000007181c8 in get_all_tables (thd=0x7f8bea738000, tables=, cond=0x7f8bea471eb8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4252
#5  0x0000000000706a61 in do_fill_table (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7412
#6  get_schema_tables_result (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7513
#7  0x00000000006fa85d in JOIN::prepare_result (this=0x7f8bea471a30, columns_list=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:822
#8  0x00000000006b628c in JOIN::exec (this=0x7f8bea471a30) at /home/yoku0825/mysql-5.6.25/sql/sql_executor.cc:116
#9  0x00000000006fc2d8 in mysql_execute_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0,
    fields=, conds=0x0, order=, group=0x7f8bea73a590, having=0x0,
    select_options=2684619520, result=0x7f8bea471a08, unit=0x7f8bea739e48, select_lex=0x7f8bea73a490)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1100
#10 mysql_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0, fields=, conds=0x0,
    order=, group=0x7f8bea73a590, having=0x0, select_options=2684619520, result=0x7f8bea471a08,
    unit=0x7f8bea739e48, select_lex=0x7f8bea73a490) at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1221
#11 0x00000000006fcbcf in handle_select (thd=0x7f8bea738000, result=0x7f8bea471a08, setup_tables_done_option=0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:110
#12 0x00000000006d6655 in execute_sqlcom_select (thd=0x7f8bea738000, all_tables=0x7f8bea7528f8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5134
#13 0x00000000006db682 in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#14 0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#15 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000,
    packet=0x7f8bea73c001 "show table status like 'City'", packet_length=3933540397)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#16 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#17 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#18 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#19 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#20 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115


もっと詳しく知りたい場合、前に翻訳した Yakst - InnoDBのインデックス統計情報についていくつか が役に立つかも。
投稿者 時刻:
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )