|
Since we are Stuck for weeks on this we will try your suggested Workaround now sergei. As mentioned on answers.launchpad.net/maria/+question/703793 a permanent setting of this parameter innodb_max_dirty_pages_pct_lwm=0.001 has a huge neagtive impact on write I/O (8-10 times):
* SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;
|
* Wait few seconds for a checkpoint to happen
|
* SET GLOBAL innodb_max_dirty_pages_pct_lwm=default;
|
* perform a backup.
|
I hope this will not impact performance too much atm of setting innodb_max_dirty_pages_pct_lwm .. we will let you know.
1 Question though: Since it is existing since mariadb 10.5.7 - does that mean we are the only ones on this planet using incremental mariadb backups?
BR
Sebastian
|
|
We managed to create a Script using these mechanics, it works for us. Still we are interested in a solid official solution of course.
BR
Sebastian
|
|
ryanthur, thanks for the confirmation!! Good that it worked
|
|
MDEV-23855 or MDEV-23399 or some related changes caused the bug MDEV-26626 (failure to advance checkpoints under some circumstances), which was fixed in MariaDB 10.5.13 and 10.6.5. Because the Launchpad discussion claims that the problem persists even in MariaDB 10.9, this must be something else than MDEV-26626. Another regression MDEV-24949 had been fixed even earlier.
For MariaDB 10.5.7 and 10.5.8, the default value of the parameter innodb_max_dirty_pages_pct_lwm=0 had a different meaning, which was corrected in MDEV-24537 (10.5.9).
I will try to reproduce this, to see if this would be fixed by MDEV-26055 and MDEV-26827, or if anything can be improved.
|
|
I created incremental_innodb.test based on the existing test mariabackup.incremental_backup. I tested it as follows:
./mtr --mysqld=--innodb-buffer-pool-size=1g --mysqld=--innodb-log-file-size=1g mariabackup.incremental_innodb,4k,undo0
|
On a development branch that includes fixes of MDEV-26055 and MDEV-26827, the output ended with the following:
SELECT COUNT(*) FROM t;
|
COUNT(*)
|
9999902
|
DROP TABLE t;
|
732M /dev/shm/10.6g/mysql-test/var/tmp/backup
|
3.3M /dev/shm/10.6g/mysql-test/var/tmp/backup_inc1
|
735M total
|
mariabackup.incremental_innodb '4k,innodb,undo0' [ pass ] 203447
|
I got a similar result with the current head of the 10.6 branch as well:
|
10.6 f169dfb41adcb637732507ed56d3038003170a15
|
SELECT COUNT(*) FROM t;
|
COUNT(*)
|
9999902
|
DROP TABLE t;
|
732M /dev/shm/10.6mg/mysql-test/var/tmp/backup
|
3.3M /dev/shm/10.6mg/mysql-test/var/tmp/backup_inc1
|
735M total
|
mariabackup.incremental_innodb '4k,innodb,undo0' [ pass ] 285978
|
Both executables were built with cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo, and because the build directory was in /dev/shm, so was the data directory. The number at the end of the last line is the test execution time in milliseconds (3 minutes and 23 seconds, or 4 minutes and 45 seconds).
ryanthur, can you please provide some exact steps for reproducing this problem, similar to the above test case?
|
|
Hello Marko,
sorry for the delay. I am a collegue of Sebastian Bergmann and like to provide information.
I tested the incremental backups with and without checkpoints.
To my opinion it is still necessary to provoke checkpoints manually with following mariadb commands before mariabackup starts:
SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001
|
SET GLOBAL innodb_max_dirty_pages_pct_lwm=0
|
Between the 2 mysql commands I waited up to 120 seconds.
The mariabackup command is in a bigger script but the mariabackup command is like this it uses --compress and the qpress command which is available from Percona:
mariabackup --extra-lsndir=/var/backup/mariadb_backup/Thursday --backup --compress --stream=xbstream --user=root --password=x xx --parallel=1 --compress-threads=1 --compress-threads=1 --target-dir=/var/backup/mariadb_backup/Thursday
|
I got following results with 10.6.10 and 10.6.12 with and without provoking checkpoints.
[root@mysqlsingle mariadb_backup]# ls -lh MariaBackup_10.6.1*
|
MariaBackup_10.6.10_checkpoints:
|
insgesamt 29M
|
-rw-r--r-- 1 root root 15M 12. Apr 14:31 full-2023-04-12_14-31-35.xbstream
|
-rw-r--r-- 1 root root 12M 12. Apr 15:11 incremental-2023-04-12_15-11-29.xbstream
|
-rw-r--r-- 1 root root 3,1M 12. Apr 15:16 incremental-2023-04-12_15-16-08.xbstream
|
|
MariaBackup_10.6.10_nocheckpoints:
|
insgesamt 25M
|
-rw-r--r-- 1 root root 4,5M 12. Apr 13:33 full-2023-04-12_13-33-24.xbstream
|
-rw-r--r-- 1 root root 9,5M 12. Apr 14:22 incremental-2023-04-12_14-22-52.xbstream
|
-rw-r--r-- 1 root root 11M 12. Apr 14:26 incremental-2023-04-12_14-26-50.xbstream
|
|
MariaBackup_10.6.12_checkpoints:
|
insgesamt 36M
|
-rw-r--r-- 1 root root 20M 13. Apr 08:56 full-2023-04-13_08-56-37.xbstream
|
-rw-r--r-- 1 root root 14M 13. Apr 09:42 incremental-2023-04-13_09-42-00.xbstream
|
-rw-r--r-- 1 root root 3,0M 13. Apr 09:45 incremental-2023-04-13_09-45-50.xbstream
|
|
MariaBackup_10.6.12_nocheckpoints:
|
insgesamt 29M
|
-rw-r--r-- 1 root root 7,9M 12. Apr 15:54 full-2023-04-12_15-54-02.xbstream
|
-rw-r--r-- 1 root root 9,8M 12. Apr 16:33 incremental-2023-04-12_16-33-07.xbstream
|
-rw-r--r-- 1 root root 11M 12. Apr 16:37 incremental-2023-04-12_16-37-24.xbstream
|
First incremental backup: +100,000 data sets
Second incremental backup: + 10,000 data sets
The second incremental backup should be smaller because of tenfold less data volume.
This is only the case, if I do checkpoints.
I produced the data sets with bash script:
#!/bin/bash
|
set -x
|
|
PERSONID=1
|
for NUM in $(seq 1 100000);do
|
mysql -e "INSERT INTO Addressbook.Persons(PersonID,LastName,FirstName,Address,City) VALUES ($NUM,\"Kent\",\"Clark\",\"Holzweg $NUM\",\"Tal\");"
|
done
|
For the second incremental backup I used 10000 (ten thousand) instead of 1000000 (One hundred thousand) in the script.
I guess it is still not so easy to reproduce, but I hope it is easy enough.
If you have any question, please let me know.
Kind regards
Michael
|
|
mroessler65536, thank you for the reproducer, and sorry for missing your update, causing this relatively simple fix to miss the MariaDB Server 10.6.13 release. I will take a look hopefully soon.
|
|
I can reproduce this with the following test case if I remove the two sections enclosed in SET GLOBAL:
--source include/have_sequence.inc
|
|
let $basedir=$MYSQLTEST_VARDIR/tmp/backup;
|
let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1;
|
|
CREATE TABLE t(id INT PRIMARY KEY, Name VARCHAR(100), Vorname VARCHAR(100), Anschrift VARCHAR(100), Ort VARCHAR(40)) ENGINE=InnoDB;
|
INSERT INTO t SELECT seq,'Kent','Clark',CONCAT('Holzweg ',seq),'Tal'
|
FROM seq_1_to_100000;
|
|
SET GLOBAL innodb_max_dirty_pages_pct=0.0;
|
let $wait_condition =
|
SELECT variable_value = 0 FROM information_schema.global_status
|
WHERE variable_name = 'INNODB_BUFFER_POOL_PAGES_DIRTY';
|
--source include/wait_condition.inc
|
SET GLOBAL innodb_max_dirty_pages_pct=90.0;
|
|
exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir;
|
|
SET GLOBAL innodb_max_dirty_pages_pct=0.0;
|
let $wait_condition =
|
SELECT variable_value = 0 FROM information_schema.global_status
|
WHERE variable_name = 'INNODB_BUFFER_POOL_PAGES_DIRTY';
|
--source include/wait_condition.inc
|
SET GLOBAL innodb_max_dirty_pages_pct=90.0;
|
|
INSERT INTO t SELECT seq,'Kent','Clark',CONCAT('Holzweg ',seq),'Tal'
|
FROM seq_100001_to_110000;
|
|
exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir;
|
|
#exec $XTRABACKUP --prepare --target-dir=$basedir;
|
#exec $XTRABACKUP --prepare --target-dir=$basedir --incremental-dir=$incremental_dir ;
|
#let $targetdir=$basedir;
|
#-- source include/restart_and_restore.inc
|
DROP TABLE t;
|
The commented-out lines at the end would replace the ib_logfile0 with zero-length files and then restore the backup.
If I run the test case as is (retaining the checkpoint control), I will get close to an optimal result:
| checkpoints |
backup/ib_logfile0 size/bytes |
backup_inc1/ib_logfile0 size/bytes |
| 0 |
1,803,264 |
1,963,520 |
| 1, between full and incremental |
1,803,264 |
872,960 |
| 1, before full backup |
2,560 |
872,960 |
| 2, before full and incremental |
2,560 |
872,960 |
The test confirms my understanding of the logic that triggering a log checkpoint only is useful before a full backup is being made. I may have misunderstood something, but incremental backups would seem to actually work based on copying a section of ib_logfile0 where the previous backup left off. Perhaps the .delta files were a work-around from an era when DDL operations were not crash-safe in InnoDB (before MariaDB Server 10.6)? I am basing this reasoning on the following error message:
msg("error: This incremental backup seems "
|
"not to be proper for the target. Check 'to_lsn' of the target and "
|
"'from_lsn' of the incremental.");
|
|
|
I forgot that possibly a typical use case of incremental backup is one where the current log checkpoint LSN is after the end LSN of a previous backup. In that case, all data file pages that have been modified since the previous end LSN will have to be copied. It might actually be useful to trigger a checkpoint also when initiating an incremental backup.
|
|
serg, can you please suggest how SET GLOBAL should be invoked by mariadb-backup in the most robust way, so that in case the connection between backup and the server is severed, the previous values will be restored?
|
|
Wouldn't it be better if the server will automatically trigger a checkpoint when backup starts? without manipulations with innodb_max_dirty_pages_pct
|
|
If we had server-side or server-assisted backup (MDEV-14992), it would be straightforward to implement logic "when backup starts".
InnoDB checkpoints will automatically be triggered at the end of each page writing batch. The default settings are such that the latest checkpoint may be rather old, mainly due to innodb_max_dirty_pages_pct=90. The actual checkpoint age (difference between checkpoint LSN and current LSN) depends on the key distribution accessed during the workload, and to some extent, on luck.
|
|
Here is a smaller test case:
--source include/have_sequence.inc
|
let $basedir=$MYSQLTEST_VARDIR/tmp/backup;
|
let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1;
|
CREATE TABLE t ENGINE=InnoDB SELECT * FROM seq_1_to_1000;
|
exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir;
|
INSERT INTO t VALUES(0);
|
exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir;
|
DROP TABLE t;
|
A possible code fix would be along these lines. It is missing the wait for INNODB_BUFFER_POOL_PAGES_DIRTY to reach 0, and I think that we would want to have a command line option to disable this feature. I would also find it better if the "roll back" of SET GLOBAL could be initiated on the server side.
diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc
|
index 83e3806eea1..b17ec22f9d0 100644
|
--- a/extra/mariabackup/xtrabackup.cc
|
+++ b/extra/mariabackup/xtrabackup.cc
|
@@ -4652,6 +4652,13 @@ static bool xtrabackup_backup_func()
|
return(false);
|
}
|
msg("cd to %s", mysql_real_data_home);
|
+
|
+ xb_mysql_query(
|
+ mysql_connection,
|
+ "SET @pct_lwm=@@GLOBAL.innodb_max_dirty_pages_pct_lwm,"
|
+ "@pct=@@GLOBAL.innodb_max_dirty_pages_pct,"
|
+ "@@GLOBAL.innodb_max_dirty_pages_pct_lwm=0,"
|
+ "@@GLOBAL.innodb_max_dirty_pages_pct=0;", false, true);
|
encryption_plugin_backup_init(mysql_connection);
|
msg("open files limit requested %lu, set to %lu",
|
xb_open_files_limit,
|
@@ -4673,6 +4680,12 @@ static bool xtrabackup_backup_func()
|
/* initialize components */
|
if(innodb_init_param()) {
|
fail:
|
+ xb_mysql_query(
|
+ mysql_connection,
|
+ "SET @@GLOBAL.innodb_max_dirty_pages_pct=@pct,"
|
+ "@@GLOBAL.innodb_max_dirty_pages_pct=@pct_lwm;",
|
+ false, false);
|
+
|
if (log_copying_running) {
|
mysql_mutex_lock(&log_sys.mutex);
|
metadata_to_lsn = 1;
|
@@ -4985,6 +4998,11 @@ static bool xtrabackup_backup_func()
|
msg("Error: corrupted innodb pages are found and logged to "
|
MB_CORRUPTED_PAGES_FILE " file");
|
}
|
+ xb_mysql_query(
|
+ mysql_connection,
|
+ "SET @@GLOBAL.innodb_max_dirty_pages_pct=@pct,"
|
+ "@@GLOBAL.innodb_max_dirty_pages_pct=@pct_lwm;",
|
+ false, false);
|
return(true);
|
}
|
|
|
|
I came up with a solution that almost works. The handler for 70100 (killed connection) does not seem to run either on KILL from another connection, nor on disconnect of the client that submitted the SQL for execution. Here is a test case that demonstrates the latter problem:
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
connect cleanup,localhost,root;
|
DELIMITER $$;
|
send BEGIN NOT ATOMIC
|
DECLARE c INT DEFAULT 15;
|
DECLARE CONTINUE HANDLER FOR SQLSTATE '70100'
|
SET
|
@@GLOBAL.innodb_max_dirty_pages_pct=@pct,
|
@@GLOBAL.innodb_max_dirty_pages_pct_lwm=@pct_lwm;
|
|
SET
|
@pct=@@GLOBAL.innodb_max_dirty_pages_pct,
|
@pct_lwm=@@GLOBAL.innodb_max_dirty_pages_pct_lwm,
|
@@GLOBAL.innodb_max_dirty_pages_pct_lwm=0,
|
@@GLOBAL.innodb_max_dirty_pages_pct=0;
|
|
WHILE (1 IN (SELECT variable_value>0 FROM information_schema.global_status
|
WHERE variable_name='INNODB_BUFFER_POOL_PAGES_DIRTY')
|
AND c>0) DO
|
DO SLEEP(1);
|
SET c:=c-1;
|
END WHILE;
|
|
SET
|
@@GLOBAL.innodb_max_dirty_pages_pct:=@pct,
|
@@GLOBAL.innodb_max_dirty_pages_pct_lwm:=@pct_lwm;
|
END;
|
$$
|
DELIMITER ;$$
|
|
connection default;
|
select sleep(.5);
|
disconnect cleanup;
|
SELECT @@GLOBAL.innodb_max_dirty_pages_pct;
|
SELECT variable_value FROM information_schema.global_status
|
WHERE variable_name='INNODB_BUFFER_POOL_PAGES_DIRTY';
|
The following output indicates that the SQL that was submitted by connection cleanup indeed did not run to completion, but it did not clean up afterwards either:
SELECT @@GLOBAL.innodb_max_dirty_pages_pct;
|
@@GLOBAL.innodb_max_dirty_pages_pct
|
0.000000
|
SELECT variable_value FROM information_schema.global_status
|
WHERE variable_name='INNODB_BUFFER_POOL_PAGES_DIRTY';
|
variable_value
|
7
|
If it had run to completion, the latter query would return 0. If the handler for 70100 had executed, the global variable would have been restored to its default value (90).
The logic behind the 70100 handler is also related to some rather frequent test failures (MDEV-30084).
|
|
In InnoDB (any MariaDB version), a log checkpoint can simply be initiated by calling the function log_make_checkpoint(). It could be simplest to call that function when executing the appropriate BACKUP STAGE statement.
|
|
serg, in debug builds, one could invoke
SET GLOBAL innodb_log_checkpoint_now=ON;
|
to force a log checkpoint. If we made that parameter available in all builds, then this bug could easily be fixed by making mariadb-backup --backup issue that statement under some (which?) conditions.
|
incremental_innodb.test