[MDEV-21423] lock-free trx_sys get performance regression cause by lf_find and ut_delay - Jira

XML

Word

Printable

Details

Type: Bug
Status: Stalled (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5.0
Fix Version/s: 10.6
Component/s: Storage Engine - InnoDB
Labels:
Environment:
linux

Epic Link:
InnoDB trx_sys improvements

Description

Hello, guys

we have port the lock-free trx-sys, however I find that the oltp_read_write case get too much performance regression compare with non-lock-free version..
Especially when the isolation-level is "read-committed", lock-free trx-sys get about 40% performance regression.
I guess Mariadb has the same problem..

This is my sysbench test configure

bench_type=oltp_read_write;

threads=560

tables=8

table_size=500000

There is another issue that relate to the lock-free trx_sys
https://jira.mariadb.org/browse/MDEV-20630?filter=-2

Below is the sysbench result:
you can find that the lockfree trx-sys vs non-lockfree trx-sys is

tps 13405.28 vs 20095.02

qps 268105.66 vs 401900.40

lockfree trx-sys :  isolation-level

read-committed

SQL statistics:

    queries performed:

        read:                            33803098

        write:                           9658028

        other:                           4829014

        total:                           48290140

    transactions:                        2414507 (13405.28 per sec.)

    queries:                             48290140 (268105.66 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

General statistics:

    total time:                          180.1141s

    total number of events:              2414507

Latency (ms):

         min:                                    2.96

         avg:                                   41.75

         max:                                 4487.73

         95th percentile:                       92.42

         sum:                            100805088.64

Threads fairness:

    events (avg/stddev):           4311.6196/167.34

    execution time (avg/stddev):   180.0091/0.01

non-lockfree: read-committed

SQL statistics:

    queries performed:

        read:                            50672678

        write:                           14477908

        other:                           7238954

        total:                           72389540

    transactions:                        3619477 (20095.02 per sec.)

    queries:                             72389540 (401900.40 per sec.)

    ignored errors:                      0      (0.00 per sec.)

    reconnects:                          0      (0.00 per sec.)

General statistics:

    total time:                          180.1161s

    total number of events:              3619477

Latency (ms):

         min:                                    2.47

         avg:                                   27.85

         max:                                  198.43

         95th percentile:                       52.89

         sum:                            100798260.68

Threads fairness:

    events (avg/stddev):           6463.3518/107.19

    execution time (avg/stddev):   179.9969/0.01

Attachments

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

08569298-111E-4929-A922-9E9AE6541BDF.png
238 kB
2020-01-05 17:24
50A6071F-CF5C-44EB-A8F5-14FAA7EFEFD4.png
262 kB
2020-01-05 17:24
image-2021-10-29-17-26-46-207.png
796 kB
2021-10-29 15:26
load_test.cc
4 kB
2021-10-29 16:46
screenshot-1.png
816 kB
2021-10-29 15:21

Issue Links

relates to

MDEV-28313 InnoDB transactions are not aligned at cache lines

Closed

MDEV-28445 Secondary index locking invokes costly trx_sys.get_min_trx_id()

Closed

MDEV-30357 Performance regression in locking reads from secondary indexes

Closed

MDEV-13190 InnoDB write-only performance regression

Open

MDEV-20630 lf_hash get performance regression since the bucket size won't decrease

Confirmed

MDEV-33067 SCN(Sequence Commit Number) based MVCC

Open

(1 relates to)

Activity

People

Assignee:: Marko Mäkelä

Reporter:: zongzhi chen

Votes:: 0 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2020-01-05 17:24

Updated:: 2024-03-19 12:44

Git Integration

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