Change innodb_page_size with Parameter
Username1233423 opened this issue · 7 comments
Hello,
I am using Holland backup on with a MariaDB instance on version 10.4 with an innodb_pgage_size of 32k.
When I start the backup process using mysqldump-lvm process I get the following error:
[ERROR] InnoDB: Data file './ibdata1' uses page size 32768, but the innodb_page_size start-up parameter is 16384.
Is there a way to set the page size manually?
It looks like there isn't. I'm I understanding the error correctly. The database engine is failing to start on the lvm-snapshot?
This issue is repeatable if you configure MySQL with innodb_page_size=32k and then launch mysql-lvm with the innodb-recovery=yes option:
[InnoDB] Data file './ibdata1' uses page size 32768, but the innodb_page_size start-up parameter is 16384
I tried to make a patch but didn't get it working
--- a/actions/mysql/_mysqld.py
+++ b/actions/mysql/_mysqld.py
@@ -92,6 +92,7 @@
"innodb-data-home-dir",
"innodb-data-file-path",
"innodb-fast-shutdown",
+ "innodb-page-size",
"open-files-limit",
"key-buffer-size",
"tmpdir",
--- a/plugin/innodb.py
+++ b/plugin/innodb.py
@@ -26,6 +26,7 @@
"innodb_data_home_dir",
"innodb_data_file_path",
"abs_tablespace_paths",
+ "innodb_page_size",
)
def __new__(
@@ -36,6 +37,7 @@
innodb_data_home_dir,
innodb_data_file_path,
abs_tablespace_paths,
+ innodb_page_size,
):
return tuple.__new__(
cls,
@@ -46,6 +48,7 @@
innodb_data_home_dir,
innodb_data_file_path,
abs_tablespace_paths,
+ innodb_page_size,
),
)
@@ -61,7 +64,8 @@
return (
"MySQLPathInfo(datadir=%r, innodb_log_group_home_dir=%r, \
innodb_log_files_in_group=%r, innodb_data_home_dir=%r, \
- innodb_data_file_path=%r, abs_tablespace_paths=%r)"
+ innodb_data_file_path=%r, abs_tablespace_paths=%r, \
+ innodb_page_size=%r)"
% self
)
@@ -75,6 +79,7 @@
"innodb_data_home_dir": tmp[3],
"innodb_data_file_path": tmp[4],
"abs_tablespace_paths": tmp[5],
+ "innodb_page_size": tmp[6],
}
def _replace(self, **kwds):
@@ -90,6 +95,7 @@
"innodb_data_home_dir",
"innodb_data_file_path",
"abs_tablespace_paths",
+ "innodb_page_size",
),
self,
)
@@ -108,6 +114,7 @@
innodb_data_home_dir = property(itemgetter(3))
innodb_data_file_path = property(itemgetter(4))
abs_tablespace_paths = property(itemgetter(5))
+ innodb_page_size = property(itemgetter(6))
@classmethod
def from_mysql(cls, mysql):
@@ -121,6 +128,7 @@
innodb_data_home_dir=ibd_homedir,
innodb_data_file_path=mysql.show_variable("innodb_data_file_path"),
abs_tablespace_paths=abs_tablespace_paths,
+ innodb_page_size=mysql.show_variable("innodb_page_size"),
)
def get_innodb_logdir(self):
--- a/plugin/raw/util.py
+++ b/plugin/raw/util.py
@@ -58,6 +58,8 @@
mysqld_config["tmpdir"] = tempfile.gettempdir()
ib_log_size = client.show_variable("innodb_log_file_size")
mysqld_config["innodb-log-file-size"] = ib_log_size
+ ib_page_size = client.show_variable("innodb_page_size")
+ mysqld_config["innodb-page-size"] = ib_page_size
act = InnodbRecoveryAction(mysqld_config)
snapshot.register("post-mount", act, priority=100)
if config["mysql-lvm"]["archive-method"] == "dir":
--- a/plugin/mysqldump/util.py
+++ b/plugin/mysqldump/util.py
@@ -46,6 +46,9 @@
ib_log_size = client.show_variable("innodb_log_file_size")
if ib_log_size:
mysqld_config["innodb-log-file-size"] = ib_log_size
+ ib_page_size = client.show_variable("innodb_page_size")
+ if ib_page_size:
+ mysqld_config["innodb-page-size"] = ib_page_size
ibd_home_dir = pathinfo.innodb_data_home_dir
if ibd_home_dir:
The problem is that the innodb_page_size needs to be read from the running server and land in eg my.innodb_recovery.cnf but the above patch doesn't accomplish it. I guess it doesn't appear in config.items for generate_server_config
There's a couple of ways we can solve this. Reading the value from the mysql server is a good option, but I'm not sure if that's possible and isn't a generic solution. Another option would be to allow any arbitrary options under [mysqld] in the holland config file
The patch is reading the innodb_page_size from running the MySQL instance, as I copied the behavior of innodb_log_file_size which is using the approach.
Here is general_log evidence of the claim:
2022-02-03T03:10:23.482065Z 38 Connect root@localhost on using TCP/IP
2022-02-03T03:10:23.482356Z 38 Query SET AUTOCOMMIT = 0
2022-02-03T03:10:23.482661Z 38 Query SHOW GLOBAL VARIABLES LIKE 'datadir'
2022-02-03T03:10:23.533884Z 39 Connect root@localhost on using TCP/IP
2022-02-03T03:10:23.534246Z 39 Query SET AUTOCOMMIT = 0
2022-02-03T03:10:23.534713Z 39 Query SHOW GLOBAL VARIABLES LIKE 'have_innodb'
2022-02-03T03:10:23.537147Z 38 Query SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size'
2022-02-03T03:10:23.539148Z 38 Query SHOW GLOBAL VARIABLES LIKE 'innodb_page_size'
2022-02-03T03:10:23.543728Z 38 Query FLUSH /*!40101 LOCAL */ TABLES
2022-02-03T03:10:23.545497Z 38 Query FLUSH TABLES WITH READ LOCK
2022-02-03T03:10:23.545781Z 38 Query SHOW MASTER STATUS
2022-02-03T03:10:23.546510Z 38 Query SHOW SLAVE STATUS
2022-02-03T03:10:23.837610Z 38 Query UNLOCK TABLES
The problem seems to be that, in my patch, innodb_page_size is not then passed to the spawned instance.
There are variables which you wouldn't want to read from the running MySQL instance like innodb_buffer_pool_size since your backup instance very likely wants to use a significantly smaller configuration value (only sequential reads by mysqldump and crash recovery doesn't benefit much beyond a few GB in my experience, not to mention that the running instance will typically have 70% or more memory allocated to the buffer pool)
If one wanted to deploy with this variable, it is worth mention that MySQL must be instantiated with the option before creating a datadir. So for example in MySQL 8.0 you would set the variable in my.cnf before calling mysqld --user=mysql --initialize-insecure with an empty writable /var/lib/mysql
As an aside, investigating this bug report shows that innodb_log_files_in_group is not currently read from the server. This omission would affect MySQL 8.0 deployments that opt in to the innodb_dedicated_server setting and some other less common deployments which manually changed this.
Adding proper support for innodb_log_files_in_group would include handling the situation that MariaDB has recently removed this configuration item. The code would need to read a innodb_log_files_in_group value from running service and pass that to backup instance, unless the result of the SHOW GLOBAL VARIABLES is an empty set)
In mysqldump as well as in the raw plugin util.py does similar to what we would need for handling absence of innodb_log_files_in_group configuration variable:
if mysql.show_variable("have_innodb") == "YES":
Upon closer look, the patch seems to work correctly with mysqldump lvm sub-plugin but I was testing with innodb_recovery and didn't notice that other bugs in this sub-plugin were actually causing a failure (I didn't read the log carefully enough.)
The page size was copied to the temporary instance mysqld configuration with the original patch but innodb_recovery sub-plugin was failing for unrelated issues:
- innodb_recovery uses wrong path for temporary mysqld unix socket (mysqldump does this correctly)
- innodb_recovery uses
--bootstrapwhich fails in MySQL 8 (it seems that detecting 8.0 and passing in--log-error-verbosity=3,--init-file=/path/to/shutdown/command.txtlikely does what we need for this sub-plugin)
This should probably be multiple pull requests so that blame can be properly attributed.
Additionally, during debugging I can up with two feature requests that were easily added in:
- lvm providers sleep time (eg
failed_lvm_debug_sleep_timedefault0or120) that allows you to glance at the instance datadir/config/log before the mount and snapshot are removed and failed backup is purged (even with auto purge failures debugging was difficult) - lvm providers write out each line (less than a dozen) of the configuration file for the temporary mysqld
Hi,
is there already a solution for the problem?