While this is interesting for various reasons, this particular post will focus on the impact this change has on data dictionary performance.
Test configuration
Server configuration:
- Hardware: Amazon EC2 m4.4xlarge, 500GB, 15K IOPS
- OS: Debian Jessie
- MySQL versions tested: 5.7.15, 8.0.0-dmr
- Notable customer MySQL configuration values:
- innodb_buffer_pool_size = 4GB
- innodb_log_file_size = 256MB
- innodb_buffer_pool_dump_at_shutdown = 0
- innodb_buffer_pool_load_at_startup = 0
- information_schema_stats = latest (8.0 only)
Database was populated with 100 schemas, 10K tables each (1M tables total) from a SQL script generated as follows:
#!/bin/bash
schema_script="schema_script.sql"
rm -f ${schema_script}
touch ${schema_script}
for s in {1..100}; do
echo "create schema s_${s};" >> ${schema_script}
for t in {1..10000}; do
echo "create table s_${s}.t_${t} (id int primary key auto_increment, s1 varchar(32));" >> ${schema_script}
done
done
Miscellaneous notes:
- Script was executed on a single thread through a local unix socket connection.
- Before running each test, MySQL process was restarted and OS buffers/caches were synced and freed ("sync; echo 3 > /proc/sys/vm/drop_caches").
- Each test was executed three times. The final result (run time) is as an average over these three runs.
Test
To give the 8.0's dictionary a go, I ran a few simple queries against information_schema. I queried both static and dynamic metadata:
- Static metadata are table's characteristics that don't change dynamically e.g. table name, table's schema, table type and engine.
- Dynamic metadata on the other hand can change rather frequently. For example data_length, index_length and auto_increment changes dynamically as table data is modified.
Query 1
The query calculates the total data size for all tables, grouped by schema. This query needs dynamic metadata.
select table_schema, sum(data_length) from information_schema.tables group by table_schema;
Query run time:
- 5.7: 57 min 46 sec
- 8.0: 1 min 55 sec (30x faster)
Query 2
The query calculates the total data size for all tables in a single schema (one of 100 in the database). This query also needs dynamic metadata.
Query 3
The query calculates the number of tables per storage engine used, per schema. The query relies on static metadata only.
The query calculates the total data size for all tables in a single schema (one of 100 in the database). This query also needs dynamic metadata.
select sum(data_length) from information_schema.tables where table_schema = 's_1';Query run time:
- 5.7: 30 sec
- 8.0: 1.7 sec (17.6x faster)
Query 3
The query calculates the number of tables per storage engine used, per schema. The query relies on static metadata only.
select table_schema, engine, count(*) from information_schema.tables group by table_schema, engine;Query run time:
- 5.7: 14 min 24 sec
- 8.0: 3.1 sec (279x faster, yay!)
These results make it pretty obvious that there are substantial performance benefits associated with not having to operate on FRM files. But are there any scenarios where the new dictionary doesn't yield such impressive results? Yes, there are.
On the flip side
First of all, it took 5 hours and 13 minutes to create the 1 million tables in MySQL 5.7. The same operation took 6 hours and 12 minutes (~19% more) in MySQL 8.0.
Not a big deal... you don't create 1M tables daily and if you do, you should probably take a second look at your workload.
The second thing I noticed is extra locking overhead coming from transactional dictionary during certain types of operations. Taking "mysqldump" as an example, I ran a full schema-only dump of all 1 million tables in default locking mode where mysqldump acquires a read lock on all table it dumps.
Results:
- Dump time in 5.7 (default locking): 10 min 45 sec
- Dump time in 8.0 (default locking): 36 min 5 sec
- Dump time in 5.7 (--skip-lock-tables): 5 min 21 sec
- Dump time in 8.0 (--skip-lock-tables): 5 min 8 sec
Perf top output while dumping from 5.7 (default locking):
42.15% mysqld [.] close_thread_tables(THD*) 9.46% mysqld [.] my_qsort 3.18% mysqld [.] TABLE::cleanup_gc_items() 2.80% [kernel] [k] ext4_htree_store_dirent 2.61% [kernel] [k] half_md4_transform 2.47% mysqld [.] handler::ha_thd() const 2.04% mysqld [.] MDL_context::find_ticket(MDL_request*, enum_mdl_duration*) 1.64% libc-2.19.so [.] readdir64_r 1.46% mysqld [.] ha_innobase::extra(ha_extra_function) 1.44% [kernel] [k] str2hashbuf_signed 1.32% [kernel] [k] __kmalloc 1.28% [kernel] [k] kfree 1.28% libc-2.19.so [.] 0x0000000000091db3 0.88% [kernel] [k] rb_insert_color 0.85% [kernel] [k] memcpy
Perf top output while dumping from 8.0 (default locking) showing contention on metadata locking:
59.42% mysqld [.] MDL_context::find_ticket(MDL_request*, enum_mdl_duration*) 10.92% mysqld [.] close_thread_tables(THD*) 9.23% mysqld [.] Release_acl_cache_locks::release(MDL_ticket*) 2.83% mysqld [.] MDL_context::release_locks(MDL_release_locks_visitor*) 0.97% mysqld [.] btr_search_guess_on_hash(dict_index_t*, btr_search_t*, dtuple_t const*, unsigned long, unsigned long, btr_cur_t*, unsigned long, mtr_t*) 0.87% mysqld [.] TABLE::cleanup_gc_items() 0.67% mysqld [.] handler::ha_thd() const 0.56% mysqld [.] rec_init_offsets(unsigned char const*, dict_index_t const*, unsigned long*) 0.51% mysqld [.] ha_innobase::extra(ha_extra_function) 0.49% mysqld [.] MDL_ticket::get_key() const 0.47% mysqld [.] row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) 0.40% libc-2.19.so [.] 0x0000000000092123 0.38% libc-2.19.so [.] 0x0000000000092111 0.37% mysqld [.] row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, unsigned char const*, unsigned long) 0.37% libc-2.19.so [.] 0x0000000000092117 0.34% libc-2.19.so [.] 0x000000000009211d
And a 8.0 stack for the dessert:
That's it. This was by no means a complete test, I just wanted to share some good news with you.
Go MysQL!
#0 MDL_context::find_ticket (this=this@entry=0x7fcf68000be8, mdl_request=mdl_request@entry=0x7fcf54115c48,
result_duration=result_duration@entry=0x7fd12c23bf4c) at /home/mysql/source/mysql-8.0/sql/mdl.cc:2805
#1 0x000000000101854f in MDL_context::try_acquire_lock_impl (this=this@entry=0x7fcf68000be8,
mdl_request=mdl_request@entry=0x7fcf54115c48, out_ticket=out_ticket@entry=0x7fd12c23c008)
at /home/mysql/source/mysql-8.0/sql/mdl.cc:3011
#2 0x000000000101942e in MDL_context::acquire_lock (this=this@entry=0x7fcf68000be8,
mdl_request=mdl_request@entry=0x7fcf54115c48, lock_wait_timeout=)
at /home/mysql/source/mysql-8.0/sql/mdl.cc:3596
#3 0x00000000008e58f1 in open_table_get_mdl_lock (mdl_ticket=, flags=3, table_list=0x7fcf541158a8,
ot_ctx=0x7fd12c23c400, thd=0x7fcf68000b50) at /home/mysql/source/mysql-8.0/sql/sql_base.cc:2801
#4 open_table (thd=thd@entry=0x7fcf68000b50, table_list=table_list@entry=0x7fcf541158a8, ot_ctx=ot_ctx@entry=0x7fd12c23c400)
at /home/mysql/source/mysql-8.0/sql/sql_base.cc:3163
#5 0x00000000008ecfa6 in open_and_process_table (ot_ctx=0x7fd12c23c400, has_prelocking_list=false,
prelocking_strategy=0x7fd12c23c490, flags=1, counter=0x7fd12c23c48c, tables=0x7fcf541158a8, lex=,
thd=0x7fcf68000b50) at /home/mysql/source/mysql-8.0/sql/sql_base.cc:5226
#6 open_tables (thd=0x7fcf68000b50, start=start@entry=0x7fd12c23c498, counter=counter@entry=0x7fd12c23c48c,
flags=flags@entry=1, prelocking_strategy=prelocking_strategy@entry=0x7fd12c23c490)
at /home/mysql/source/mysql-8.0/sql/sql_base.cc:5855
#7 0x00000000010e0fc4 in open_tables (flags=1, counter=0x7fd12c23c48c, tables=0x7fd12c23c498, thd=)
at /home/mysql/source/mysql-8.0/sql/sql_base.h:432
#8 dd::Open_dictionary_tables_ctx::open_tables (this=this@entry=0x7fd12c23c530)
at /home/mysql/source/mysql-8.0/sql/dd/impl/transaction_impl.cc:100
#9 0x0000000000a534bf in dd::cache::Storage_adapter::get (thd=thd@entry=0x7fcf68000b50,
key=..., isolation=isolation@entry=ISO_READ_COMMITTED, object=object@entry=0x7fd12c23c608)
at /home/mysql/source/mysql-8.0/sql/dd/impl/cache/storage_adapter.cc:100
#10 0x0000000000a501c5 in dd::cache::Shared_dictionary_cache::get_uncached (
this=, thd=thd@entry=0x7fcf68000b50, key=..., isolation=isolation@entry=ISO_READ_COMMITTED,
object=object@entry=0x7fd12c23c608) at /home/mysql/source/mysql-8.0/sql/dd/impl/cache/shared_dictionary_cache.cc:103
#11 0x0000000000a25d18 in dd::cache::Dictionary_client::acquire_uncached (this=,
schema_name="information_schema", object_name="TABLES", object=object@entry=0x7fcf63810380)
at /home/mysql/source/mysql-8.0/sql/dd/impl/cache/dictionary_client.cc:1057
#12 0x0000000000bfaa5d in open_table_def (thd=thd@entry=0x7fcf68000b50, share=share@entry=0x7fcf63810040,
open_view=open_view@entry=true, table_def=table_def@entry=0x0) at /home/mysql/source/mysql-8.0/sql/dd_table_share.cc:2260
#13 0x00000000008e44d2 in get_table_share (thd=thd@entry=0x7fcf68000b50, table_list=table_list@entry=0x7fcf628e6628,
key=0x7fcf628e69ed "information_schema", key_length=key_length@entry=26, open_view=open_view@entry=true,
hash_value=hash_value@entry=3834831062) at /home/mysql/source/mysql-8.0/sql/sql_base.cc:565
#14 0x00000000008e56e9 in get_table_share_with_discover (hash_value=3834831062, error=, key_length=26,
key=, table_list=0x7fcf628e6628, thd=0x7fcf68000b50) at /home/mysql/source/mysql-8.0/sql/sql_base.cc:685
#15 open_table (thd=thd@entry=0x7fcf68000b50, table_list=table_list@entry=0x7fcf628e6628, ot_ctx=ot_ctx@entry=0x7fd12c23e760)
That's it. This was by no means a complete test, I just wanted to share some good news with you.
Go MysQL!
No comments:
Post a Comment