[MDEV-12288] Reset DB_TRX_ID when the history is removed, to speed up MVCC - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 10.3.1
Component/s: Storage Engine - InnoDB
Labels:
- performance
- transactions

Sprint:
10.3.1-2

Description

The InnoDB clustered index record system columns DB_TRX_ID,DB_ROLL_PTR are used by multi-versioning and for determining if a record is implicitly locked. After the history is no longer needed, these columns can safely be reset to 0 and 1<<55 (to indicate a fresh insert).

When a reader sees 0 in the DB_TRX_ID column, it can instantly determine that the record is present the read view. There is no need to acquire the transaction system mutex to check if the transaction exists, because writes can never be conducted by a transaction whose ID is 0.

The persistent InnoDB undo log is split into two parts: insert_undo and update_undo. The insert_undo log is discarded at transaction commit or rollback, and the update_undo log is processed by the purge subsystem. As part of this change, we must merge the two types of undo logs into one, and the purge subsystem will reset the DB_TRX_ID whenever a clustered index record is ‘touched’.

Upgrade considerations

This will change the persistent InnoDB file formats, not only in the undo log and redo log, but also in the data files. There are some debug assertions that would not allow any record to contain DB_TRX_ID=0.

A new redo log format tag must be introduced so that the writes of the system columns can be properly redo-logged. (See ~~MDEV-11432~~, ~~MDEV-11782~~.) This will prevent a startup of an older version with the new-version redo logs. We may also prevent a crash recovery of MariaDB 10.2 files with the newer version. (Crash recovery of files from 10.1 or earlier versions is already prevented in 10.2.)

The undo log format will be changed as well. To be able to get rid of legacy code, InnoDB startup should detect if any old-format undo logs are present. If yes, startup will be refused, and the user must perform a slow shutdown (SET GLOBAL innodb_fast_shutdown=0) with the old server in order to empty the undo logs.

A proof-of-concept implementation for 10.2 consists of 4 consecutive commits. It is missing any of the above-mentioned upgrade logic.

Attachments

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

mysql-5.7.17-MDEV-12288.patch
112 kB
2017-03-17 09:20
mysql-5.7.17-MDEV-12288-11585-followup.patch
52 kB
2017-03-17 09:57

Issue Links

blocks

MDEV-13697 DB_TRX_ID is not always reset when the history is removed

Closed

causes

MDEV-13475 InnoDB: Failing assertion: lsn == log_sys->lsn || srv_force_recovery == SRV_FORCE_NO_LOG_REDO

Closed

MDEV-13476 TRX_UNDO_PAGE_TYPE mismatch when writing undo log after upgrade

Closed

MDEV-13495 Crash in rollback of a recovered INSERT transaction after upgrade

Closed

MDEV-13536 DB_TRX_ID is not actually being reset when the history is removed

Closed

MDEV-13654 Various crashes due to DB_TRX_ID mismatch in table-rebuilding ALTER TABLE…LOCK=NONE

Closed

MDEV-13820 trx_id_check() fails during row_log_table_apply()

Closed

MDEV-15370 Upgrade fails when both insert_undo and update_undo exist for recovered transactions

Closed

MDEV-15912 InnoDB: Failing assertion: purge_sys.tail.commit <= purge_sys.rseg->last_commit upon upgrade from 10.0 or 10.1 to 10.3

Closed

MDEV-16017 galera mtr tests fail with sst_xtrabackup-v2

Closed

MDEV-16273 innodb.alter_kill fails in buildbot with Unknown storage engine 'InnoDB'

Closed

MDEV-18966 Transaction recovery may be broken after upgrade to 10.3

Closed

MDEV-21158 trx_undo_seg_free() is never redo-logged

Closed

relates to

MDEV-13542 Crashing on a corrupted page is unhelpful

Closed

MDEV-14848 MariaDB 10.3 refuses InnoDB crash-upgrade from MariaDB 10.2

Closed

MDEV-17745 innodb.innodb_stats_persistent failed in buildbot with wrong result

Closed

MDEV-20804 Use the sequence engine in main.index_merge_innodb and main.index_merge_myisam

Closed

MDEV-26475 InnoDB must not lock delete-marked records

Open

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

Closed

MDEV-515 innodb bulk insert

Closed

MDEV-8139 Fix scrubbing

Closed

MDEV-11432 Change the informational redo log format tag to say "MariaDB 10.2.3"

Closed

MDEV-11658 Simpler, faster IMPORT of InnoDB tables

Open

MDEV-11782 Redefine the innodb_encrypt_log format

Closed

MDEV-12258 InnoDB: Fix the bogus debug assertion introduced in MDEV-12219

Closed

MDEV-13269 Upgrade tests for InnoDB undo log

Closed

MDEV-15522 Change galera suite MTR tests to use mariabackup instead of xtrabackup

Closed

MDEV-16168 Performance regression on sysbench write benchmarks from 10.2 to 10.3

Closed

MDEV-17044 mysql_upgrade is causing crash of mysqld

Closed

MDEV-23017 range query performance regression in 10.5.4

Closed

MDEV-24153 innodb assertion - trx_undo_t *trx_undo_mem_create_at_db_start(trx_rseg_t *, ulint, ulint, mtr_t *): Assertion `type == 2' faile

Closed

MDEV-33067 SCN(Sequence Commit Number) based MVCC

Open

mentioned in: Page Loading...

(8 causes, 19 relates to, 1 mentioned in)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2017-03-17 07:00

Marko Mäkelä made changes - 2017-03-17 07:00

Field	Original Value	New Value
Link		This issue relates to MDEV-11658 [ MDEV-11658 ]

Marko Mäkelä made changes - 2017-03-17 07:00

Link

This issue relates to ~~MDEV-11782~~ [ ~~MDEV-11782~~ ]

Marko Mäkelä made changes - 2017-03-17 07:00

Link

This issue relates to ~~MDEV-11432~~ [ ~~MDEV-11432~~ ]

Marko Mäkelä made changes - 2017-03-17 09:20

Attachment

mysql-5.7.17-MDEV-12288.patch [ 43453 ]

Marko Mäkelä made changes - 2017-03-17 09:57

Attachment

mysql-5.7.17-MDEV-12288-11585-followup.patch [ 43456 ]

Marko Mäkelä added a comment - 2017-03-17 10:00 - edited

I attached experimental patches to port this to MySQL 5.7.17 for performance evaluation. The second patch also depends on a patch that is attached to ~~MDEV-11585~~.
In addition to these patches it could be useful to apply the patch attached to ~~MDEV-12121~~.

Based on a quick test with MySQL 5.7.17 and these patches, the setting internal_tmp_disk_storage_engine=MyISAM should be used when testing the patches related to temporary tables. (MariaDB does not support InnoDB as the optimizer-internal storage engine, and ~~MDEV-11487~~ removed the InnoDB implementation of such tables, leaving only the user-visible possibility to CREATE TEMPORARY TABLE…ENGINE=InnoDB.)

Marko Mäkelä added a comment - 2017-03-17 10:00 - edited I attached experimental patches to port this to MySQL 5.7.17 for performance evaluation. The second patch also depends on a patch that is attached to MDEV-11585 . In addition to these patches it could be useful to apply the patch attached to MDEV-12121 . Based on a quick test with MySQL 5.7.17 and these patches, the setting internal_tmp_disk_storage_engine=MyISAM should be used when testing the patches related to temporary tables. (MariaDB does not support InnoDB as the optimizer-internal storage engine, and MDEV-11487 removed the InnoDB implementation of such tables, leaving only the user-visible possibility to CREATE TEMPORARY TABLE…ENGINE=InnoDB.)

Marko Mäkelä made changes - 2017-03-18 18:38

Link

This issue relates to ~~MDEV-12258~~ [ ~~MDEV-12258~~ ]

Marko Mäkelä added a comment - 2017-04-18 14:28

The following tasks are prerequisites for including the patch in a release:

Support upgrade from the old undo log format. (Recover transactions from both insert_undo and update_undo logs.)
Add consistency checks: Delete-marked records must never ever carry DB_TRX_ID=0 (except in secondary indexes, where DB_TRX_ID does not exist).

Marko Mäkelä added a comment - 2017-04-18 14:28 The following tasks are prerequisites for including the patch in a release: Support upgrade from the old undo log format. (Recover transactions from both insert_undo and update_undo logs.) Add consistency checks: Delete-marked records must never ever carry DB_TRX_ID=0 (except in secondary indexes, where DB_TRX_ID does not exist).

Marko Mäkelä made changes - 2017-07-06 00:10

Status

Open [ 1 ]

In Progress [ 3 ]

Marko Mäkelä added a comment - 2017-07-06 00:15

bb-10.3-marko passes an upgrade test (see the commit message for details).

The only thing that remains to be done is redo logging for setting DB_TRX_ID,DB_ROLL_PTR on ROW_FORMAT=COMPRESSED pages. We probably have to introduce a new redo log record type for that.

Marko Mäkelä added a comment - 2017-07-06 00:15 bb-10.3-marko passes an upgrade test (see the commit message for details). The only thing that remains to be done is redo logging for setting DB_TRX_ID,DB_ROLL_PTR on ROW_FORMAT=COMPRESSED pages. We probably have to introduce a new redo log record type for that.

Marko Mäkelä made changes - 2017-07-06 00:15

Assignee	Marko Mäkelä [ marko ]	Jan Lindström [ jplindst ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Jan Lindström (Inactive) added a comment - 2017-07-06 09:50

Firstly, changes look correct but I have to say most of the critical changes are on code that is not familiar to me in great detail. Secondly, if upgrade tests pass, this code also works, there could be a new test case where both insert and update undo records are produced to persistent storage and then see that crash recovery works correctly (on different page sizes including compressed row format). Ok to push.

Jan Lindström (Inactive) added a comment - 2017-07-06 09:50 Firstly, changes look correct but I have to say most of the critical changes are on code that is not familiar to me in great detail. Secondly, if upgrade tests pass, this code also works, there could be a new test case where both insert and update undo records are produced to persistent storage and then see that crash recovery works correctly (on different page sizes including compressed row format). Ok to push.

Jan Lindström (Inactive) made changes - 2017-07-06 09:50

Assignee	Jan Lindström [ jplindst ]	Marko Mäkelä [ marko ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä added a comment - 2017-07-06 12:30

Thank you for the review. I responded to your review comments, and will next implement the remaining redo log format changes.

Marko Mäkelä added a comment - 2017-07-06 12:30 Thank you for the review . I responded to your review comments, and will next implement the remaining redo log format changes.

Marko Mäkelä made changes - 2017-07-06 19:12

Link

This issue relates to ~~MDEV-13269~~ [ ~~MDEV-13269~~ ]

Rasmus Johansson (Inactive) made changes - 2017-07-06 20:22

Sprint

10.3.1-2 [ 174 ]

Rasmus Johansson (Inactive) made changes - 2017-07-06 20:22

Rank

Ranked lower

Marko Mäkelä added a comment - 2017-07-07 00:00

I implemented a new redo log format version and the MLOG_ZIP_WRITE_TRX_ID record.
While testing the latter, I found out that we are not resetting the DB_TRX_ID as often as I would expect. Some further revision will be needed. Maybe the upgrade compatibility changes broke it, or maybe it was not fully working. It is hard to test this, because the DB_TRX_ID column is hidden from the SQL layer.

Nevertheless, the fields do get reset sometimes (during innodb_zip.bug56680 even for ROW_FORMAT=COMPRESSED tables). The implemented file format changes will allow the resetting to be improved later. So, I would push this now, before the file formats are frozen.

Marko Mäkelä added a comment - 2017-07-07 00:00 I implemented a new redo log format version and the MLOG_ZIP_WRITE_TRX_ID record . While testing the latter, I found out that we are not resetting the DB_TRX_ID as often as I would expect. Some further revision will be needed. Maybe the upgrade compatibility changes broke it, or maybe it was not fully working. It is hard to test this, because the DB_TRX_ID column is hidden from the SQL layer. Nevertheless, the fields do get reset sometimes (during innodb_zip.bug56680 even for ROW_FORMAT=COMPRESSED tables). The implemented file format changes will allow the resetting to be improved later. So, I would push this now, before the file formats are frozen.

Marko Mäkelä added a comment - 2017-07-07 14:51

When testing the recovery of the added MLOG_ZIP_WRITE_TRX_ID record, I noticed that the system columns are not being reset in every case, such as soon after committing an INSERT.

I decided to push this nevertheless, so that we will have the necessary file format changes in place. The bug that the history is not always being reset can be fixed later when time permits.

Marko Mäkelä added a comment - 2017-07-07 14:51 When testing the recovery of the added MLOG_ZIP_WRITE_TRX_ID record, I noticed that the system columns are not being reset in every case, such as soon after committing an INSERT. I decided to push this nevertheless, so that we will have the necessary file format changes in place. The bug that the history is not always being reset can be fixed later when time permits.

Marko Mäkelä made changes - 2017-07-07 14:51

Fix Version/s		10.3.1 [ 22532 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Elena Stepanova made changes - 2017-08-08 22:18

Link

This issue causes ~~MDEV-13475~~ [ ~~MDEV-13475~~ ]

Elena Stepanova made changes - 2017-08-08 22:30

Link

This issue causes ~~MDEV-13476~~ [ ~~MDEV-13476~~ ]

Marko Mäkelä made changes - 2017-08-11 08:47

Link

This issue causes ~~MDEV-13495~~ [ ~~MDEV-13495~~ ]

Marko Mäkelä made changes - 2017-08-15 14:18

Link

This issue causes ~~MDEV-13536~~ [ ~~MDEV-13536~~ ]

Marko Mäkelä made changes - 2017-08-16 06:02

Link

This issue relates to ~~MDEV-13542~~ [ ~~MDEV-13542~~ ]

Marko Mäkelä added a comment - 2017-08-28 08:28

The resetting of the DB_TRX_ID column was fixed and regression tests added in
~~MDEV-13536~~ DB_TRX_ID is not actually being reset when the history is removed

Marko Mäkelä added a comment - 2017-08-28 08:28 The resetting of the DB_TRX_ID column was fixed and regression tests added in MDEV-13536 DB_TRX_ID is not actually being reset when the history is removed

Marko Mäkelä made changes - 2017-08-28 14:20

Link

This issue causes ~~MDEV-13654~~ [ ~~MDEV-13654~~ ]

Marko Mäkelä made changes - 2017-09-01 11:26

Link

This issue blocks ~~MDEV-13697~~ [ ~~MDEV-13697~~ ]

Marko Mäkelä made changes - 2017-09-13 14:04

Link

This issue relates to ~~MDEV-8139~~ [ ~~MDEV-8139~~ ]

Marko Mäkelä added a comment - 2017-12-04 09:45

svoj noticed that the function lock_rec_convert_impl_to_expl() was unnecessarily looking up trx_id=0, and acquiring trx_sys->mutex when doing the futile lookup.
The follow-up fix in 10.3.3 fixes this omission. The initial MDEV-12288 commit already included a corresponding fast-path for the secondary index lock check in the function row_vers_impl_x_locked_low().

Marko Mäkelä added a comment - 2017-12-04 09:45 svoj noticed that the function lock_rec_convert_impl_to_expl() was unnecessarily looking up trx_id=0, and acquiring trx_sys->mutex when doing the futile lookup. The follow-up fix in 10.3.3 fixes this omission. The initial MDEV-12288 commit already included a corresponding fast-path for the secondary index lock check in the function row_vers_impl_x_locked_low().

Marko Mäkelä made changes - 2017-12-06 18:55

Link

This issue causes ~~MDEV-13820~~ [ ~~MDEV-13820~~ ]

Marko Mäkelä made changes - 2018-01-01 20:33

Link

This issue relates to ~~MDEV-14848~~ [ ~~MDEV-14848~~ ]

Marko Mäkelä made changes - 2018-02-21 17:04

Link

This issue causes ~~MDEV-15370~~ [ ~~MDEV-15370~~ ]

Marko Mäkelä added a comment - 2018-03-07 13:24

For the record: Due to this change, InnoDB moved to a single persistent undo log. By design, this ought to fix the upstream MySQL Bug #55283, which to my knowledge is still open. The bug should be present in all upstream InnoDB versions at least since MySQL 5.0, where the two-phase commit mechanism was introduced.

Marko Mäkelä added a comment - 2018-03-07 13:24 For the record: Due to this change, InnoDB moved to a single persistent undo log. By design, this ought to fix the upstream MySQL Bug #55283 , which to my knowledge is still open. The bug should be present in all upstream InnoDB versions at least since MySQL 5.0, where the two-phase commit mechanism was introduced.

Marko Mäkelä made changes - 2018-04-30 14:07

Link

This issue causes ~~MDEV-16017~~ [ ~~MDEV-16017~~ ]

Marko Mäkelä made changes - 2018-05-16 11:24

Link

This issue relates to ~~MDEV-16168~~ [ ~~MDEV-16168~~ ]

Marko Mäkelä made changes - 2018-07-25 12:31

Link

This issue causes ~~MDEV-15912~~ [ ~~MDEV-15912~~ ]

Marko Mäkelä made changes - 2018-09-06 06:24

Link

This issue relates to ~~MDEV-15522~~ [ ~~MDEV-15522~~ ]

Marko Mäkelä made changes - 2018-10-10 03:11

Link

This issue causes ~~MDEV-16273~~ [ ~~MDEV-16273~~ ]

Marko Mäkelä made changes - 2018-11-16 17:19

Link

This issue relates to ~~MDEV-17745~~ [ ~~MDEV-17745~~ ]

Marko Mäkelä made changes - 2019-03-19 07:48

Link

This issue relates to ~~MDEV-18960~~ [ ~~MDEV-18960~~ ]

Marko Mäkelä made changes - 2019-03-19 08:49

Link

This issue causes ~~MDEV-18966~~ [ ~~MDEV-18966~~ ]

Marko Mäkelä made changes - 2019-03-19 08:52

Link

This issue relates to ~~MDEV-18960~~ [ ~~MDEV-18960~~ ]

Marko Mäkelä made changes - 2019-10-11 09:28

Link

This issue relates to ~~MDEV-20804~~ [ ~~MDEV-20804~~ ]

Marko Mäkelä made changes - 2019-10-11 10:01

Link

This issue relates to ~~MDEV-515~~ [ ~~MDEV-515~~ ]

Marko Mäkelä made changes - 2019-11-27 11:59

Link

This issue causes ~~MDEV-21158~~ [ ~~MDEV-21158~~ ]

Marko Mäkelä made changes - 2020-07-01 09:36

Link

This issue relates to ~~MDEV-23017~~ [ ~~MDEV-23017~~ ]

Marko Mäkelä made changes - 2020-07-21 10:58

Link

This issue causes ~~MDEV-23244~~ [ ~~MDEV-23244~~ ]

Marko Mäkelä made changes - 2020-07-23 12:45

Link

This issue causes ~~MDEV-23244~~ [ ~~MDEV-23244~~ ]

Marko Mäkelä made changes - 2020-11-06 05:21

Link

This issue relates to ~~MDEV-24153~~ [ ~~MDEV-24153~~ ]

Marko Mäkelä made changes - 2021-04-26 13:07

Link

This issue relates to ~~MDEV-17044~~ [ ~~MDEV-17044~~ ]

Marko Mäkelä made changes - 2021-08-25 08:39

Link

This issue relates to MDEV-26475 [ MDEV-26475 ]

Sergei Golubchik made changes - 2021-12-06 21:23

Workflow

MariaDB v3 [ 80015 ]

MariaDB v4 [ 133182 ]

Marko Mäkelä made changes - 2022-01-14 13:44

Link

This issue relates to TODO-3302 [ TODO-3302 ]

Marko Mäkelä made changes - 2022-01-14 15:04

Link

This issue relates to ~~MDEV-27437~~ [ ~~MDEV-27437~~ ]

Rob Schwyzer (Inactive) made changes - 2022-09-27 20:26

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 34500 ]

Marko Mäkelä made changes - 2023-12-19 13:35

Link

This issue relates to MDEV-33067 [ MDEV-33067 ]

People

Assignee:: Marko Mäkelä

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2017-03-17 07:00

Updated:: 2024-03-23 18:37

Resolved:: 2017-07-07 14:51

Git Integration

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

MariaDB Server

Details

Description

Upgrade considerations

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration