MySQL Bugs: #80783: Use default buffer pool size for faster startup with --initialize

Bug #80783	Use default buffer pool size for faster startup with --initialize
Submitted:	17 Mar 2016 17:59	Modified:	18 Mar 2016 8:24
Reporter:	Alexey Kopytov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL 5.7 has this neat optimization to avoid allocating and
initializing a large buffer pool (which can take minutes) when mysqld is
being started with the --help and --verbose options, i.e. use default
buffer pool size when the only purpose of initializing InnoDB is parsing
and listing configuration options.

This is a request to extend that optimization to --initialize and
--initialize-insecure. I now work on a project where I have to create
multiple server instances frequently with hundreds of GBs worth of
buffer pool memory, so all the time spent waiting on "mysqld
--initialize" adds up quickly.

I could of course override the buffer pool size with a command line
option, but that's one more thing to keep in mind. Making InnoDB do the
right thing by default in this case would be a simple, but useful
improvement.

How to repeat:
Run mysqld --initialize-insecure with a large enough buffer pool in
my.cnf.

Hello Alexey,

Thank you for the reasonable feature request and feedback!
I assume the optimization you referred was from Bug #76625.

Thanks,
Umesh

-- with buffer pool size 210GB

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: time bin/mysqld --defaults-file=./my.cnf --initialize-insecure
2016-03-18T06:48:39.556421Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-18T06:48:39.560271Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
2016-03-18T06:49:09.574425Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-03-18T06:49:09.594302Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-03-18T06:49:09.648733Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 844cdd1c-ecd5-11e5-9985-0010e05f3e06.
2016-03-18T06:49:09.649234Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-03-18T06:49:09.649791Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

real    0m38.098s
user    0m15.233s
sys     0m13.624s
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: vi my.cnf
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: rm -rf data/

-- with default buffer pool size (134217728)

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: time bin/mysqld --defaults-file=./my.cnf --initialize-insecure
2016-03-18T06:51:25.199058Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-18T06:51:25.202875Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
2016-03-18T06:51:32.277829Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-03-18T06:51:32.296935Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-03-18T06:51:32.351383Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d95b8f5b-ecd5-11e5-80dc-0010e05f3e06.
2016-03-18T06:51:32.351899Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-03-18T06:51:32.352409Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

real    0m9.053s
user    0m0.313s
sys     0m2.777s
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11:

Hello Umesh,

Yes, that's what I meant. Unfortunately, the commit message has no
references to the public bug number.

Contribution submitted via Github - Bug #80783: Use default buffer pool size for faster startup with --initialize (*) Contribution by Alexey Kopytov (Github akopytov, mysql-server/pull/59#issuecomment-200853373): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_63889983.txt (text/plain), 1.40 KiB.