Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.11.6
-
None
Description
Hello,
When running backup during ongoing ALTER operation backup fails with error:
[00] FATAL ERROR: 2024-02-02 10:16:28 Was only able to copy log from 2825766826 to 2892744515, not 3030682034; try increasing innodb_log_file_size |
Attached full log.
It is easy to reproduce issue. In my test case I used all the defaults running simple rebuild on a table.
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| test_data | CREATE TABLE `test_data` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`data` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=10000015 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec) |
|
MariaDB [testdb]> select * from test_data limit 5; |
+----+-----------------------+
|
| id | data |
|
+----+-----------------------+
|
| 1 | This is a test string | |
| 2 | This is a test string | |
| 3 | This is a test string | |
| 4 | This is a test string | |
| 5 | This is a test string | |
+----+-----------------------+
|
5 rows in set (0.001 sec) |
|
|
MariaDB [testdb]> select count(*) from test_data;
|
+----------+
|
| count(*) |
|
+----------+
|
| 10000014 | |
+----------+
|
1 row in set (9.947 sec) |
## SESSION 1 |
MariaDB [testdb]> alter table test_data engine=innodb;
|
Query OK, 0 rows affected (1 min 2.641 sec) |
Records: 0 Duplicates: 0 Warnings: 0 |
|
## SESSION 2 |
mariabackup --backup --target-dir=/backup --user=root --password=root
|
The same error happens on a server with workload and bigger log file size.
Received the same error on Percona (5.7.42) with xtrabackup, just error log was slightly different:
240202 11:19:36 >> log scanned up to (1948476139) |
InnoDB: Last flushed lsn: 1947667398 load_index lsn 1948481497 |
InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.
|
PXB will not be able to make a consistent backup. Retry the backup operation
|
I am not sure if this is a bug.
If this is expected behavior what actions should we do to avoid backup failures.
Increase innodb_log_file_size? Change other parameters?
Thank you
Attachments
Issue Links
- relates to
-
MDEV-14992 BACKUP: in-server backup
- Open
-
MDEV-33894 MariaDB does unexpected storage read IO for the redo log
- Closed
Yes, this is expected, and SET GLOBAL innodb_log_file_size to a larger value (
MDEV-27812) should help. Another thing that you could try is to execute SET GLOBAL innodb_log_file_buffering=ON (MDEV-28766) before starting the backup. In that way, mariadb-backup --backup should have a chance of reading the log data from the operating system’s file system cache and not have to read it from the physical storage.The error message was changed, because in
MDEV-14425the log file format was changed and a log block sequence number was shrunk to 1 bit. Previously the log was divided into 512-byte blocks. In theMDEV-14425format, each mini-transaction is a logical log block on its own.I believe that this design issue would be fixed by implementing the log copying inside the server process (MDEV-14992).
Edit: innodb_log_file_buffering=ON disables the use of O_DIRECT. The O_DIRECT would bypass the operating system’s file system cache. The default is innodb_log_file_buffering=OFF, because bypassing the cache could improve normal write performance.
Edit: Another thing that could help is to trigger a full InnoDB log checkpoint before initiating backup, so that there will be less log to be copied. MDEV-30000 is a request for mariadb-backup --backup to do that automatically.