MySQL Bugs: #88746: Simplify configuration of InnoDB page size

Bug #88746	Simplify configuration of InnoDB page size
Submitted:	4 Dec 2017 22:59	Modified:	19 Oct 2020 12:34
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, installation, page size

Description:
I am testing different InnoDB page size settings and notice that once I use a different page size I have to ensure that the server is configured with this value. If I have multiple servers in the same replication using different page sizes then I need to pay careful attention to ensure the configuration file /etc/my.cnf setting matches the server's on disk setting.

In reality InnoDB only needs this setting when it starts up. Once the initial InnoDB pages/files are created it can figure out what the right page size is on its own so the extra checks are unnecessary and a configuration setting should not be necessary. Unless the innodb page checksums are disabled it should be straightforward to scan the required files and startup using the "right setting" thus avoiding special configuration on affected servers.

How to repeat:
Setup a server with a page size of 8kB. Shut it down and remove the page size configuration.

On startup mysqld will log:

2017-12-04T21:33:36.098899Z 0 [ERROR] InnoDB: Data file './ibdata1' uses page size 8192, but the innodb_page_size start-up parameter is 16384
2017-12-04T21:33:36.098967Z 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2017-12-04T21:33:36.098980Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-12-04T21:33:36.699619Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-12-04T21:33:36.699638Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-12-04T21:33:36.699652Z 0 [ERROR] Failed to initialize plugins.
2017-12-04T21:33:36.699657Z 0 [ERROR] Aborting

Notice how it "knows" the right page size. Why can't it use that value?
Unless configured explicitly there should be no need to specify the size: innodb can figure this out on startup. The innodb page checksums will ensure that the intended page size is correct and the page is valid.

Suggested fix:
* Normal behaviour: use the default setting when creating a new mysqld installation.
* Allow for the page size to be provided on mysqld installation (that's also possible now I think)

On normal startup of an existing mysqld server with Innodb
* check the "base InnoDB pages" and find out what size they use and use these values rather than any setting from the command line or /etc/my.cnf.
* If there is an explicit setting in /etc/my.cnf or from the command line and it doesn't match the value seen on the InnoDB pages then log a warning.
* Ensure startup logging shows the detected page size.

This should not change the behaviour much but makes it much easier to build a new server and then clone it to make a new slave without having to be too concerned about the server's configuration. With the changes suggested this will just work rather than now, for servers with non-standard page sizes, requiring their specific configuration setting being copies to any cloned server.

A colleague figured out how to check the page size in 5.7 and earlier.

$ perl -Mstrict -we'  # strict + warnings
  $/ = \0x1000;  # use a 4k IO-buffer
  defined(my $s = <>) or exit;  # read from file
  $s = substr($s, 54, 4);  # isolate 4 bytes
  $s = unpack "L", $s;  # convert to unsigned long
  $s >>= 14;  # drop some bits
  $s &= 15;  # keep bottom 4 bits
  $s ||= 5;  # default to 5 (16k); 1=1k, 2=2k, 3=4k, 4=8k, 6=32k, 7=64k 
  print"innodb_page_size=", 1<<($s-1), "k\n";
' path/to/ibdata1

innodb_page_size=8k

Written in perl but explained nicely.

I'll probably add some version of this to my configuration code so the my.cnf file has the right value, but I do think that it should NOT be necessary to configure this setting except when building a new instance and only if you want to try a non-default value.

I'd also like to know which would be the best file to test in 8.0 given there have been changes there and there's a new data dictionary and maybe these are the tables that should be checked?

Hi Mr. Mudd,

Thank you for your feature request.

We find that your request is fully valid.

Verified as reported.