Question Adjusting mysql innodb_page_size global variable. Implications?

[Paul Larson]

Server operating system version

Ubuntu 22.04

Plesk version and microupdate number

18.0.61 Update #5

We are inheriting a site hosted on a customer's server. Current developer writes: "You will need a container / environment that has mysql setup with this higher rowsize. Changing the innodb_page_size in an existing environment with other DBs with the default rowsize will corrupt (can't think of the right word), all the other DBs in that current environment."

Settings they recommend are:

innodb_strict_mode = 0
innodb_page_size=32K
sql_mode = ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

a) Is it correct that I can't amend the innodb_page_size without damaging current databases?
b) If I were to instead spin up a new Plesk instance, at what point is it best to change innodb_page_size?

The server I intended for this domain currently has innodb_page_size of 16384

 

[Mikhail_S]

Hello @Paul Larson

In general, the value of 16K should be enough for most of the databases. As the first step, the existing databases should be checked. Probably, they are not optimized and there is no reason to alter the configuration.

I would note that it is definitely not recommended to change the value of innodb_page_size on the working databases. This operation will lead to the state when MySQL stops working at all.
As it is stated in the official documentation, the value should be specified during the installation only:

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 17.8.1, “InnoDB Startup Configuration”.

Thus, I would say the best way is to deploy a new fresh server, configure MySQL with the higher innodb_page_size, and install Plesk on top.

---

As far as I know, the following way also can help but it is not recommended:

1. Make a dump of all the databases
2. Stop the service
3. Remove file ibdata1, ib_logfile0 and ib_logfile1
4. Change the value of innodb_page_size
5. Start the service
6. Restore the databases

Please note that it can be potentially dangerous. It is recommended to have a snapshot of the server so you can restore the previous state.

 

[Kaspar@Plesk]

As an addition to the excellent suggestions from @Mikhail_S, you could consider spinning up a (small) server for use as a dedicated database server. Configured to your own needs. Which you could then add as an remote database server to your existing Plesk server. The benefit being that you don't need to change your current Plesk server or spin up a whole new Plesk server just for this website.

Some Cloud providers offer managed database servers. Mitigating the need to configure your own database server.

 

[Paul Larson]

As an addition to the excellent suggestions from @Mikhail_S, you could consider spinning up a (small) server for use as a dedicated database server. Configured to your own needs. Which you could then add as an remote database server to your existing Plesk server. The benefit being that you don't need to change your current Plesk server or spin up a whole new Plesk server just for this website.

Some Cloud providers offer managed database servers. Mitigating the need to configure your own database server.

That's a good idea. Do you have any provider suggestions? I'm sure AWS/RD would work but Amazon solutions seem to be so heavy-handed for this.

 

[Kaspar@Plesk]

Ideally you'd host the database server with the same provider, on the same network even (if they offer multiple locations), using local address ranges to route traffic internally, to keep network latency to a minimum (and to not have database traffic get routed publicly). Maybe you can ask your provider?