Bug #86702 please disable persistent stats on the mysql.gtid_executed table
Submitted: 14 Jun 2017 19:50 Modified: 15 Jun 2017 7:04
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.18 OS:Any
Assigned to: CPU Architecture:Any

[14 Jun 2017 19:50] Shane Bester
Description:
I feel there's no need for all the overhead of persistent stats on this table.

How to repeat:
I found a fair number of memory allocations occurring due to this, it seems not needed to have persistent stacks here.

Suggested fix:
Add STATS_PERSISTENT=0 to the table options.
[14 Jun 2017 19:51] MySQL Verification Team
The allocations happen often here in 5.7.git

./storage/innobase/mem/mem0mem.cc:302:mem_heap_create_block_func
./storage/innobase/mem/mem0mem.cc:407:mem_heap_add_block
./storage/innobase/mem/mem0mem.cc:59:mem_heap_dup
./storage/innobase/dict/dict0mem.cc:676:dict_mem_index_create
./storage/innobase/dict/dict0dict.cc:2600:dict_index_add_to_cache_w_vcol
./storage/innobase/dict/dict0load.cc:2608:dict_load_indexes
./storage/innobase/dict/dict0load.cc:3132:dict_load_table_one
./storage/innobase/dict/dict0load.cc:2864:dict_load_table
./storage/innobase/include/dict0priv.ic:59:dict_table_get_low
./storage/innobase/dict/dict0dict.cc:6237:dict_table_schema_check
./storage/innobase/dict/dict0stats.cc:255:dict_stats_persistent_storage_check
./storage/innobase/dict/dict0stats.cc:3164:dict_stats_update
./storage/innobase/handler/ha_innodb.cc:5944:ha_innobase::open
./sql/handler.cc:2758:handler::ha_open
./sql/table.cc:3361:open_table_from_share
./sql/sql_base.cc:3517:open_table
./sql/sql_base.cc:5718:open_tables
./sql/sql_base.cc:6440:open_and_lock_tables
./sql/sql_base.cc:6278:open_n_lock_single_table
./sql/rpl_table_access.cc:57:System_table_access::open_table
./sql/rpl_gtid_persist.cc:189:Gtid_table_access_context::init
./sql/rpl_gtid_persist.cc:745:Gtid_table_persistor::fetch_gtids
./sql/mysqld.cc:4711:mysqld_main
[15 Jun 2017 6:21] MySQL Verification Team
I'm not saying this is a leak.  It's not.  It is just plenty overhead to maintain persistent stats for small tables.  All other innodb tables in mysql db have it disabled,  so why not this?
[15 Jun 2017 7:01] MySQL Verification Team
mysql> select table_schema,table_name,engine,create_options from information_schema.tables where table_schema='mysql' and engine='innodb';
+--------------+---------------------------+--------+--------------------+
| table_schema | table_name                | engine | create_options     |
+--------------+---------------------------+--------+--------------------+
| mysql        | engine_cost               | InnoDB | stats_persistent=0 |
| mysql        | gtid_executed             | InnoDB |                    |
| mysql        | help_category             | InnoDB | stats_persistent=0 |
| mysql        | help_keyword              | InnoDB | stats_persistent=0 |
| mysql        | help_relation             | InnoDB | stats_persistent=0 |
| mysql        | help_topic                | InnoDB | stats_persistent=0 |
| mysql        | innodb_index_stats        | InnoDB | stats_persistent=0 |
| mysql        | innodb_table_stats        | InnoDB | stats_persistent=0 |
| mysql        | plugin                    | InnoDB | stats_persistent=0 |
| mysql        | server_cost               | InnoDB | stats_persistent=0 |
| mysql        | servers                   | InnoDB | stats_persistent=0 |
| mysql        | slave_master_info         | InnoDB | stats_persistent=0 |
| mysql        | slave_relay_log_info      | InnoDB | stats_persistent=0 |
| mysql        | slave_worker_info         | InnoDB | stats_persistent=0 |
| mysql        | time_zone                 | InnoDB | stats_persistent=0 |
| mysql        | time_zone_leap_second     | InnoDB | stats_persistent=0 |
| mysql        | time_zone_name            | InnoDB | stats_persistent=0 |
| mysql        | time_zone_transition      | InnoDB | stats_persistent=0 |
| mysql        | time_zone_transition_type | InnoDB | stats_persistent=0 |
+--------------+---------------------------+--------+--------------------+
19 rows in set (0.00 sec)
[15 Jun 2017 7:04] MySQL Verification Team
Hello Shane,

Thank you for the report and feedback!

regards,
Umesh