MySQL Bugs: #80784: Parallelize buffer pool initialization

Bug #80784	Parallelize buffer pool initialization
Submitted:	17 Mar 2016 18:06	Modified:	7 Mar 2018 20:47
Reporter:	Alexey Kopytov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Buffer pool initialization is single-threaded and may take minutes on
systems with large buffer pools. Startup time of often precious in
production systems, so any ways to decrease it can have considerable
effect.

One low-hanging fruit to optimize it would be to initialize buffer pool
instances in parallel threads. Which would result in roughly 8 times
speedup for the default innodb_buffer_pool_instances value.

How to repeat:
Start mysqld with a large buffer pool.

Hello Alexey,

Thank you for the reasonable feature request and feedback!

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming 8.0.5 release, and here's the changelog entry:

To improve startup performance on systems with large buffer pools, buffer
pool initialization is now multi-threaded.

sorry but i failed to reproduce this, the starting time is less than 2seconds, the logs are:

root@ubuntu:/usr/local/mysql/bin# ./mysqld --user=root
2018-06-02 10:25:35 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-02 10:25:35 16349 [Note] Plugin 'FEDERATED' is disabled.
2018-06-02 10:25:35 16349 [Note] InnoDB: The InnoDB memory heap is disabled
2018-06-02 10:25:35 16349 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-02 10:25:35 16349 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-06-02 10:25:35 16349 [Note] InnoDB: Using Linux native AIO
2018-06-02 10:25:35 16349 [Note] InnoDB: Using CPU crc32 instructions
2018-06-02 10:25:35 16349 [Note] InnoDB: Initializing buffer pool, total size = 3.7G, instances = 64
2018-06-02 10:25:35 16349 [Note] InnoDB: Completed initialization of buffer pool
2018-06-02 10:25:35 16349 [Note] InnoDB: Highest supported file format is Barracuda.
2018-06-02 10:25:35 16349 [Note] InnoDB: Creating shared tablespace for temporary tables
2018-06-02 10:25:35 16349 [Note] InnoDB: Setting file "./ibtmp1" size to 12 MB
2018-06-02 10:25:35 16349 [Note] InnoDB: Database physically writes the file full: wait ...
2018-06-02 10:25:35 16349 [Note] InnoDB: 128 rollback segment(s) are active.
2018-06-02 10:25:35 16349 [Note] InnoDB: Waiting for purge to start
2018-06-02 10:25:35 16349 [Note] InnoDB: 5.7.1 started; log sequence number 1633055
2018-06-02 10:25:35 16349 [Note] Server hostname (bind-address): '*'; port: 3306
2018-06-02 10:25:35 16349 [Note] IPv6 is available.
2018-06-02 10:25:35 16349 [Note]   - '::' resolves to '::';
2018-06-02 10:25:35 16349 [Note] Server socket created on IP: '::'.
2018-06-02 10:25:35 16349 [Note] Event Scheduler: Loaded 0 events
2018-06-02 10:25:35 16349 [Note] ./mysqld: ready for connections.
Version: '5.7.1-m11'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

And i check the configuration in client:
mysql> select @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                             64 |
+--------------------------------+
1 row in set (0.01 sec)

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4293918720 |
+---------------------------+
1 row in set (0.00 sec)

What did i miss?
Thanks!

In my environment, for 5.7,  this seems to be an issue when NUMA is enabled on very large (600G) innodb_buffer_size.  Our server takes 12 minutes to start wiht NUMA enabled.