[MDEV-12353] Efficient InnoDB redo log record format - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 10.5.2
Component/s: Storage Engine - InnoDB
Labels:
- performance
- recovery

Description

The InnoDB crash recovery performance can be improved a little while not changing the file format. ~~MDEV-12699~~ removed unnecessary reads of pages that can be initialized via the redo log. ~~MDEV-19586~~ will make recovery read the pages in more sequential order.

We should fix some fundamental issues that exist with the current InnoDB redo log record format:

Records do not contain their length. When buffering records, we must painstakingly parse entire records in order to determine the length. This idea was mentioned in MySQL Bug#82937.
For B-tree operations, we are writing a lot of redundant data for mlog_parse_index(). We should use a lower-level format and lower-level apply functions. MySQL Bug#82176 merely speeds up the code around mlog_parse_index().
If a mini-transaction is writing multiple records to a page, the page identifier is being repeated for every record. We should omit the page identifier if multiple consecutive records are modifying the same page.

In this task, we will only improve the redo log record format. Format changes to the redo log blocks and files will be covered by ~~MDEV-14425~~.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

prt
60 kB
2020-01-06 18:43
simp_page_rec_set_n_owned.test
0.7 kB
2020-01-06 18:43
simp_page_rec_set_n_owned-master.opt
0.1 kB
2020-01-06 18:43

Issue Links

causes

MDEV-21724 Optimize page_cur_insert_rec_low() redo logging

Closed

MDEV-21725 Optimize btr_page_reorganize_low() redo logging

Stalled

MDEV-21727 Optimize redo logging for ROW_FORMAT=COMPRESSED

Open

MDEV-21744 Assertion `!rec_offs_nth_sql_null(offsets, n)' failed in btr_cur_upd_rec_in_place

Closed

MDEV-21748 ASAN use-after-poison in PageBulk::insertPage on table rebuild

Closed

MDEV-21749 storage/innobase/page/page0cur.cc:1306: rec_t* page_cur_insert_rec_low(const page_cur_t*, dict_index_t*, const rec_t*, offset_t*, mtr_t*): Assertion `rdm - rd + bd <= insert_buf + rec_size' failed.

Closed

MDEV-21779 Assertion `!memcmp(insert_buf, rec - extra_size, rec_size)' failed in page_cur_insert_rec_low

Closed

MDEV-21850 ASAN use-after-poison in page_cur_insert_rec_low

Closed

MDEV-21892 Assertion `index != clust_index || row_get_rec_trx_id(rec, index, offsets)' failed in row_search_mvcc

Closed

MDEV-21893 Assertion `log_block_get_start_lsn(lsn, log_block_get_hdr_no(buf)) == lsn' failed upon attempt to start on encrypted datadir from previous versions

Closed

MDEV-21945 Assertion w==OPT failed in trx_purge_add_undo_to_history()

Closed

MDEV-21949 key rotation for innodb_encrypt_log is not working in 10.5

Closed

MDEV-22107 Restore accidentally orphaned MTR_MEMO_MODIFY

Closed

MDEV-22108 Crash recovery fails with [ERROR] InnoDB: Malformed log record

Closed

MDEV-22242 B-trees can become extremely skewed

Closed

MDEV-23806 Undo page corruption on recovery

Closed

MDEV-24196 WITH_UBSAN runtime error: member call on null pointer of type 'struct log_phys_t'

Closed

MDEV-24412 Mariadb 10.5: InnoDB: Upgrade after a crash is not supported

Closed

MDEV-24652 mtr fails while reusing the cached undo log block

Closed

MDEV-24848 Assertion `rlen < llen' failed in log_phys_t::apply_status log_phys_t::apply upon MariaBackup prepare

Closed

MDEV-27059 page_zip_dir_insert() may corrupt ROW_FORMAT=COMPRESSED tables

Closed

MDEV-27444 Perform backup prepare using mariabbackup 10.4 version when performing rolling upgrade on joiner node with 10.5

Stalled

MDEV-27486 Refuse Galera SST if major version of donor and joiner are different

Stalled

MDEV-28731 Race condition on log checkpoint

Closed

is blocked by

MDEV-19747 Deprecate and ignore innodb_log_optimize_ddl

Closed

MDEV-21174 Refactor mlog_write_ulint, mlog_memset, mlog_write_string

Closed

MDEV-21351 Replace recv_sys.heap with list of buf_block_t*

Closed

MDEV-21674 purge_sys.stop() no longer waits for purge workers to complete

Closed

relates to

MDEV-12699 Improve crash recovery of corrupted data pages

Closed

MDEV-14425 Change the InnoDB redo log format to reduce write amplification

Closed

MDEV-18976 Implement a CHECKSUM redo log record for improved validation

Closed

MDEV-19586 Replace recv_sys_t::addr_hash with a std::map

Closed

MDEV-20562 btr_cur_open_at_rnd_pos() fails to return error for corrupted page

Closed

MDEV-20584 Clearing delete-mark on R-tree pages does not appear crash-safe

Closed

MDEV-20636 Potential SPATIAL INDEX corruption with ROW_FORMAT=COMPRESSED

Open

MDEV-21024 InnoDB is issuing redundant writes to redo log

Closed

MDEV-22126 Rename confusing constant mtr_t::OPT

Closed

MDEV-23136 InnoDB init fail after upgrade from 10.4 to 10.5

Closed

MDEV-23986 [ERROR] [FATAL] InnoDB: Page ... name ... page_type ... key_version 1 lsn ... compressed_len ...

Closed

MDEV-27437 Galera snapshot transfer fails to upgrade between some major versions

Closed

MDEV-28256 Galera SST fails with 'InnoDB: Upgrade after a crash is not supported'

Closed

MDEV-29153 2022-07-22 18:12:56 0x7f3319fff700 InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.4.25/storage/innobase/page/page0cur.cc line 11

Closed

MDEV-32144 Debug assertion failure w == MAYBE_NOP in mtr_t::memcpy(), trx_undo_write_xid()

Closed

MDEV-32445 InnoDB may corrupt its log before upgrading it on startup

Closed

MDEV-33274 The test encryption.innodb-redo-nokeys often fails

Closed

MDEV-15274 innodb_gis.rtree_recovery failed in buildbot with error on check table

Closed

MDEV-19747 Deprecate and ignore innodb_log_optimize_ddl

Closed

MDEV-20608 innodb_log_optimize_ddl=OFF may omit some redo log

Closed

MDEV-21899 INSERT into a secondary index with zero-data-length key is not crash-safe

Closed

MDEV-22097 Not applying DELETE_ROW_FORMAT_REDUNDANT due to corruption

Closed

MDEV-23474 InnoDB fails to start with Invalid log block checksum after unsetting innodb_log_checksums dynamically

Closed

MDEV-24719 backport MDEV-24705 from 10.5 to 10.2, 10.3, 10.4

Closed

MDEV-26322 Last binlog file and position are "empty" in mariabackup --prepare output

Closed

MDEV-31042 Certain tables are missing in innodb storage engine of a TSE enabled database after an incremental backup restore

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(19 causes, 4 is blocked by, 26 relates to, 6 mentioned in)

Activity

Ascending order - Click to sort in descending order

View 15 older comments

Marko Mäkelä added a comment - 2020-02-14 09:36

The following test demonstrates that we are doing fine with in-place UPDATE:

--source include/have_innodb.inc

CREATE TABLE t2 (a BIGINT) ENGINE=InnoDB;

delimiter |;

CREATE PROCEDURE p(low BIGINT, high BIGINT)

begin

  SHOW STATUS LIKE 'innodb_lsn_current';

  INSERT INTO t2 VALUES(low);

  WHILE low<high DO

    UPDATE t2 SET a=low;

    SET low = low + 1;

  END WHILE;

  SHOW STATUS LIKE 'innodb_lsn_current';

end|

delimiter ;|

CALL p(9223372036854644735,9223372036854775807);

DROP PROCEDURE p;

DROP TABLE t2;

./mtr --mysqld=--innodb-force-recovery=2 main.ibu

revision	LSN1	LSN2-LSN1	time
before	61,792	27,098,532	3.92s
after	52,361	22,173,911	3.41s

Let us repeat the same in a single transaction (less overhead to manage undo log pages, or to flush the redo log):

./mtr --mysqld=--skip-autocommit --mysqld=--innodb-force-recovery=2 main.ibu

revision	LSN1	LSN2-LSN1	time
before	61,792	11,512,365	1.49s
after	52,361	8,299,770	1.38s

In both cases, the compact physical log format outperforms the old format, both in terms of redo log written and time elapsed.

Marko Mäkelä added a comment - 2020-02-14 09:36 The following test demonstrates that we are doing fine with in-place UPDATE : --source include/have_innodb.inc CREATE TABLE t2 (a BIGINT ) ENGINE=InnoDB; delimiter |; CREATE PROCEDURE p(low BIGINT , high BIGINT ) begin SHOW STATUS LIKE 'innodb_lsn_current' ; INSERT INTO t2 VALUES (low); WHILE low<high DO UPDATE t2 SET a=low; SET low = low + 1; END WHILE; SHOW STATUS LIKE 'innodb_lsn_current' ; end | delimiter ;| CALL p(9223372036854644735,9223372036854775807); DROP PROCEDURE p; DROP TABLE t2; ./mtr --mysqld=--innodb-force-recovery=2 main.ibu revision LSN1 LSN2-LSN1 time before 61,792 27,098,532 3.92s after 52,361 22,173,911 3.41s Let us repeat the same in a single transaction (less overhead to manage undo log pages, or to flush the redo log): ./mtr --mysqld=--skip-autocommit --mysqld=--innodb-force-recovery=2 main.ibu revision LSN1 LSN2-LSN1 time before 61,792 11,512,365 1.49s after 52,361 8,299,770 1.38s In both cases, the compact physical log format outperforms the old format, both in terms of redo log written and time elapsed.

Vladislav Vaintroub added a comment - 2020-02-14 15:57

as far as I can tell, the page_cur_insert_rec_low() dominates the perf top for small (index) updates for the test case for ~~MDEV-21534~~ (basically, update_index sysbench). it even beats the log_write_up_to() in most scenarios in baseline,

Vladislav Vaintroub added a comment - 2020-02-14 15:57 as far as I can tell, the page_cur_insert_rec_low() dominates the perf top for small (index) updates for the test case for MDEV-21534 (basically, update_index sysbench). it even beats the log_write_up_to() in most scenarios in baseline,

Marko Mäkelä added a comment - 2020-02-16 12:26

Before MDEV-21725 is completed, page reorganize operations will essentially copy the entire page payload to the redo log by invoking page_cur_insert_rec_low() for every record, instead of only writing a minimal amount of log to cover the physical changes to the page.

Updating an indexed record will cause an insert, deferred delete (purge) and potentially a large number of page reorganize operations. Also, page splits and merges will cause deletions of record ranges, which has not been optimized yet either. (We write redundant log records for updating some page header fields multiple times, for example.) I think that in MDEV-21725, we should log the deletions of record ranges in the same way we would log a page reorganize (and combine the deletion with a reorganization). That should cure the observed regression.

Marko Mäkelä added a comment - 2020-02-16 12:26 Before MDEV-21725 is completed, page reorganize operations will essentially copy the entire page payload to the redo log by invoking page_cur_insert_rec_low() for every record, instead of only writing a minimal amount of log to cover the physical changes to the page. Updating an indexed record will cause an insert, deferred delete (purge) and potentially a large number of page reorganize operations. Also, page splits and merges will cause deletions of record ranges, which has not been optimized yet either. (We write redundant log records for updating some page header fields multiple times, for example.) I think that in MDEV-21725 , we should log the deletions of record ranges in the same way we would log a page reorganize (and combine the deletion with a reorganization). That should cure the observed regression.

Marko Mäkelä added a comment - 2020-02-18 06:47

I now believe that ~~MDEV-21724~~ has an even larger impact on performance. The amount of redo log written for an INSERT of an index record has almost doubled for records with small numbers of fields. To address that, we must introduce custom log records for those operations.

Marko Mäkelä added a comment - 2020-02-18 06:47 I now believe that MDEV-21724 has an even larger impact on performance. The amount of redo log written for an INSERT of an index record has almost doubled for records with small numbers of fields. To address that, we must introduce custom log records for those operations.

Marko Mäkelä added a comment - 2020-02-19 06:50 - edited

I collected statistics on the amount of redo log written for the following SQL:

--source include/have_innodb.inc

CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1),(2);

DROP TABLE t1;

I used the following commands in GDB to identify the mini-transactions:

break ha_innobase::write_row

run

break mtr_t::finish_write()

command 2

up 2

up

end

continue

continue

…

Here are the mini-transactions (excluding purge and log checkpoints):

bytes(old)	bytes(new)	bytes(revised)	source	operation
48	59	48	trx_undo_report_row_operation()	INSERT (1) undo log
36	47	27	row_ins_clust_index_entry_low()	INSERT (1) b-tree
14	22	14	trx_undo_report_row_operation()	INSERT (2) undo log
30	52	24	row_ins_clust_index_entry_low()	INSERT (2) b-tree
88	49	49	trx_commit_low()	COMMIT (INSERT)
107	108	97	trx_undo_report_row_operation()	DROP TABLE
20	17	17	row_upd_del_mark_clust_rec()
8	8	8	row_upd_clust_step()
63	72	64	trx_undo_report_row_operation()
20	17	17	row_upd_del_mark_clust_rec()
55	64	56	trx_undo_report_row_operation()
20	17	17	row_upd_del_mark_clust_rec()
52	61	53	trx_undo_report_row_operation()
20	17	17	row_upd_del_mark_clust_rec()
17	10	10	row_upd_sec_index_entry()
36	46	37	trx_undo_report_row_operation()
21	18	18	row_upd_del_mark_clust_rec()
36	45	37	trx_undo_report_row_operation()
21	18	18	row_upd_del_mark_clust_rec()
19	18	18	fil_delete_tablespace()
88	49	49	trx_commit_low()	COMMIT (DROP TABLE)

The third column shows the impact of introducing higher-level log records that correspond to MLOG_UNDO_INSERT and roughly correspond to MLOG_UNDO_INIT. Due to a different encoding, we sometimes lose 1 byte in our UNDO_APPEND replacement of MLOG_UNDO_INSERT.

The high-level log records for inserting index tree records will be introduced in ~~MDEV-21724~~. The deletion of records is not covered by this micro-benchmark, but it was improved earlier .

For updates, the new encoding is outperforming the old encoding, as expected.

Marko Mäkelä added a comment - 2020-02-19 06:50 - edited I collected statistics on the amount of redo log written for the following SQL: --source include/have_innodb.inc CREATE TABLE t1 (a INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); DROP TABLE t1; I used the following commands in GDB to identify the mini-transactions: break ha_innobase::write_row run break mtr_t::finish_write() command 2 up 2 up end continue continue … Here are the mini-transactions (excluding purge and log checkpoints): bytes(old) bytes(new) bytes(revised) source operation 48 59 48 trx_undo_report_row_operation() INSERT (1) undo log 36 47 27 row_ins_clust_index_entry_low() INSERT (1) b-tree 14 22 14 trx_undo_report_row_operation() INSERT (2) undo log 30 52 24 row_ins_clust_index_entry_low() INSERT (2) b-tree 88 49 49 trx_commit_low() COMMIT (INSERT) 107 108 97 trx_undo_report_row_operation() DROP TABLE 20 17 17 row_upd_del_mark_clust_rec() 8 8 8 row_upd_clust_step() 63 72 64 trx_undo_report_row_operation() 20 17 17 row_upd_del_mark_clust_rec() 55 64 56 trx_undo_report_row_operation() 20 17 17 row_upd_del_mark_clust_rec() 52 61 53 trx_undo_report_row_operation() 20 17 17 row_upd_del_mark_clust_rec() 17 10 10 row_upd_sec_index_entry() 36 46 37 trx_undo_report_row_operation() 21 18 18 row_upd_del_mark_clust_rec() 36 45 37 trx_undo_report_row_operation() 21 18 18 row_upd_del_mark_clust_rec() 19 18 18 fil_delete_tablespace() 88 49 49 trx_commit_low() COMMIT (DROP TABLE) The third column shows the impact of introducing higher-level log records that correspond to MLOG_UNDO_INSERT and roughly correspond to MLOG_UNDO_INIT . Due to a different encoding, we sometimes lose 1 byte in our UNDO_APPEND replacement of MLOG_UNDO_INSERT . The high-level log records for inserting index tree records will be introduced in MDEV-21724 . The deletion of records is not covered by this micro-benchmark, but it was improved earlier . For updates, the new encoding is outperforming the old encoding, as expected.

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 4 Vote for this issue

Watchers:: 15 Start watching this issue

Dates

Created:: 2017-03-24 08:35

Updated:: 2024-09-06 10:00

Resolved:: 2020-02-14 05:01

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration