Bug #84025 5.7 Shutdown and startup take a long time after creating 100k tables
Submitted: 1 Dec 2016 4:02 Modified: 16 Jan 2017 6:09
Reporter: Roel Van de Paar Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.16, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2016 4:02] Roel Van de Paar
If you create 100k tables with 100 records each (using Sysbench) and then try and shutdown the server, it will take a long time before shutdown completes.

How to repeat:
Use these options to mysqld:

--no-defaults --skip-external-locking --bind-address= --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --max_connections=4096 --expire_logs_days=1 --max_binlog_size=100M --innodb_buffer_pool_size=16G --innodb_log_file_size=1G --innodb_log_buffer_size=64M --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --innodb_io_capacity=1000 --innodb_io_capacity_max=10000 --innodb_checksum_algorithm=strict_crc32 --innodb_purge_threads=8 --innodb_flush_neighbors=0 --innodb_change_buffer_max_size=5 --innodb_flush_log_at_trx_commit=2 --innodb_max_dirty_pages_pct=95 --innodb_sync_array_size=4 --innodb_monitor_enable="%" --innodb_buffer_pool_dump_pct=100 --innodb_open_files=1000 --innodb_max_purge_lag=1000000 --innodb_max_purge_lag_delay=10000 --innodb_undo_tablespaces=2 --innodb_undo_log_truncate=1 --performance_schema=off --server_id=1 --table_definition_cache=200000 --table_open_cache=200000 --table_open_cache_instances=1

Sysbench prepare (all that's needed):
sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --report-interval=1 --oltp-tables-count=100000 --oltp-table-size=100 --max-time=0 --oltp-read-only=off --max-requests=0 --num-threads=64 --rand-type=uniform --db-driver=mysql --mysql-user=root --mysql-db=test --mysql-socket=/sda/MS151116-mysql-5.7.16-linux-x86_64-debug/socket.sock prepare

Then shutdown;
/sda/MS151116-mysql-5.7.16-linux-x86_64-debug/bin/mysqladmin -uroot -S/sda/MS151116-mysql-5.7.16-linux-x86_64-debug/socket.sock shutdown
[1 Dec 2016 8:03] Roel Van de Paar
Something similar on startup;

2016-12-01T07:28:02.461498Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-12-01T07:29:30.736288Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables

Notice the offset of 1.5 minute. Nothing reported in the log about what it is doing.
[5 Dec 2016 1:35] zhai weixiang
it will be helpful if providing output of pt-pmp/pstack while hang happens. :)
[16 Jan 2017 6:09] MySQL Verification Team
Hello Roel,

Thank you for the report.
Observed this with 5.7.17 source build.

[16 Jan 2017 6:11] MySQL Verification Team
5.7.17 test details

Attachment: 84025.results (application/octet-stream, text), 81.75 KiB.