In MariaDB Server 10.3, the MVCC implementation as well as the data structures for transactions were changed in MDEV-12288, MDEV-15104, and MDEV-15158.

Which types of operations is this expected to improve and how? I think that currently the largest problem with MVCC are secondary indexes. That would be fixed by MDEV-17598.

We already have a monotonically increasing sequence number that is stored in each data page at FIL_PAGE_LSN. I have previously encountered the SCN (System Change Number) in Oracle documentation, and I was under the impression that it corresponds to the InnoDB Log Sequence Number (LSN).

I have created a pull request on github: https://github.com/MariaDB/server/pull/2955

The purpose of this patch is to reduce the overhead of creating a snapshot(readview) by avoiding the traversal of active transaction IDs. In our testing scenario, we observed an approximately 5% improvement in QPS for read-write workload. However, there was a decrease of approximately 3% in the write workload.

zhangyuan, thank you. I posted some initial review comments.

I would like to know more about the benchmarks that you ran. The read performance bug could be what we already know as MDEV-20630 and MDEV-21423. I have not studied the lock-free hash table code in deep enough detail to be able to do something to it. There could be some low-hanging fruit in that code, such as replacing some of the sequential consistent memory ordering with release-acquire.

Hello Marko, thanks for your reply. Our test configurations are:

The result of oltp_read_only:

The result of oltp_read_write:

The result of oltp_write_only:

The result of oltp_update_index:

We also conducted some mixed tests using two instances of sysbench.
oltp_update_non_index(128 threads) + oltp_point_select(128 threads):

oltp_update_non_index(256 threads) + oltp_point_select(256 threads):

oltp_write_only(128 threads) + oltp_read_only(128 threads):

oltp_write_only(128 threads) + oltp_read_only(128 threads):

Note: All tests were conducted with the patch in MDEV-19749 applied.

I will make the modifications according to your review comments and then do the performance testing again.

Thank you, zhangyuan. For the oltp_read_only test, if the history list was empty at the start of the test, all DB_TRX_ID should be 0 and any transaction ID lookups should be avoided. In this type of scenario I would expect no big difference in throughput.

I have filed https://github.com/akopytov/sysbench/pull/419 for enabling the use of MDEV-515 and MDEV-24621 (and disabling row-level undo logging) during sysbench prepare. It could be useful to apply that patch, to keep the history list small. Starting with MDEV-12288 in MariaDB Server 10.3, also INSERT operations will result in a purge of history (resetting the DB_TRX_ID).

It would be good to specify the exact commit hash when not testing a tagged release. The pull request appears to be based on 11.4 as of this commit, which does not include a merge of MDEV-32050. The current head of 11.4 should be more interesting to compare. The last big InnoDB change was MDEV-29694 in 11.0; since then we had some fairly small changes such as MDEV-14795 and MDEV-32452. There have been no InnoDB changes in the 11.3 or 11.4 branches.

Hello Marko, I have made some modifications according to your review comments. Here are some answers to the questions mentioned in the pull request.

1 How older versions would react to undo logs that exist in this format?
Older versions can't not recognize the undo log generated WITH innodb_use_scn=ON.

2. Did all test suites pass when running ./mtr -mysqld=-loose-innodb-use-scn=on?
Mostly test cases passed with -mysqld=-loose-innodb-use-scn=on except for: innodb.instant_alter_debug, innodb.temp_truncate, innodb.sys_truncate_debug and innodb.sys_truncate_shutdown_debug
I don't think these failed test cases are significant, as there is a reported mismatch:

3. What is the test suite execution time with both values of the parameter?
innodb_use_scn=OFF: Spent 13604.633 of 2496 seconds executing testcases
innodb_use_scn=ON: Spent 13438.079 of 2480 seconds executing testcases

4. Has this been tested with Galera or with replication? Do all nodes have to use the same value of the parameter? How does the SCN affect checks for implicit locks?
All replication test cases have passed. We did not test it with Galera, but in theory, SCN does not have any side effects on logical replication. It is not necessary for different nodes to use the same parameter. Additionally, SCN does not affect implicit locks. If a transaction is active, the DB_TRX_ID field in the record inserted by the transaction represents the transaction's ID. Write-back operation occures only when the transaction has been committed.

5. All builders (including ASAN, MSAN, UBSAN) need to pass all tests in both modes.
When building the current version 11.4 (commit id: 3fad2b115569864d8c1b7ea90ce92aa895cfef08) with options -DWITH_ASAN=ON, -DWITH_MSAN=ON, -DWITH_UBSAN=ON, the mtr encountered a failure during bootstrap:

6. Could we have a cmake option for enabling this feature, similar to PLUGIN_PERFSCHEMA, WITH_WSREP, or WITH_INNODB_AHI?
We added a cmake option: WITH_INNODB_SCN.

I have rebased the code to current 11.4(commit id: 7ee16b1e2978d6b73377d9a5bf7d9fd83c841f92), but following tests are done based on commit 3fad2b115569864d8c1b7ea90ce92aa895cfef08.

The test result of oltp_point_select:

The test result of oltp_read_only:

The test result of oltp_read_write:

The test result of oltp_write_only:

The test result of oltp_update_index:

oltp_update_non_index(128 threads) + oltp_point_select(128 threads):

oltp_update_non_index(256 threads) + oltp_point_select(256 threads):

oltp_write_only(128 threads) + oltp_read_only(128 threads):

oltp_write_only(128 threads) + oltp_read_only(128 threads):

According to these tests for mariadb, SCN doesn't make much sense for most sysbench scenarios. However, it does show promising improvements for the oltp_point_select workload when combined with other write operations.