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
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 5.7.16, 5.7.17
• OS: Any
• CPU Architecture: Any

[1 Dec 2016 4:02] Roel Van de Paar

Description:
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=127.0.0.1 --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.

Thanks,
Umesh

[16 Jan 2017 6:11] MySQL Verification Team

5.7.17 test details

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

[10 Feb 2023 9:35] MySQL Verification Team

Bug #110018 marked as duplicate of this one