Bug #99638 | xa transaction with low performance | ||
---|---|---|---|
Submitted: | 20 May 2020 0:53 | Modified: | 3 Dec 2020 19:31 |
Reporter: | phoenix Zhang (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
Version: | 8.0.18 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | xa |
[20 May 2020 0:53]
phoenix Zhang
[20 May 2020 0:54]
phoenix Zhang
the sysbench test file.
Attachment: oltp_xa.lua (text/x-lua), 1.52 KiB.
[20 May 2020 1:00]
phoenix Zhang
adding the patch as contribution (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
[20 May 2020 1:00]
phoenix Zhang
my patch is
Attachment: xa_performance.diff (text/x-patch), 369 bytes.
[20 May 2020 1:19]
phoenix Zhang
BTW, my env basic config is: cpu: 64core memory: 256G
[20 May 2020 12:42]
MySQL Verification Team
Hi Mr. Zhang, Thank you for your bug report. However, in order to consider whether this change is acceptable, we need to know exactly how did you run the sysbench. Thanks in advance.
[20 May 2020 12:51]
MySQL Verification Team
One more question: Have you tried tuning it with: --innodb-adaptive-max-sleep-delay=#
[20 May 2020 13:31]
MySQL Verification Team
Hi Mr. Zhang, We have discussed this problem with our Development and it turns out that the change would not be effective. This is the explanation ..... The callstack involves Clone_persist_gtid::wait_thread, which calls : /* Sleep for 1 millisecond */ Clone_Msec sleep_time(1); ... static_cast<void>(Clone_Sys::wait(sleep_time, time_out, alert_interval, wait_cond, nullptr, is_timeout)); so the std::this_thread::sleep_for(sleep_time) request the thread to sleep for 0.001s (1 millisecond). However, ut_delay(10) does not go to sleep at all, and does "some amount" of spinning, defined to be something like 0.00001s (10 microseconds) on 100 MHz Pentium, so probably much less on modern machines. Not a bug. So, there are two differences: duration and (not)releasing the CPU.
[21 May 2020 0:37]
phoenix Zhang
Hi. While use sleep 1 millisecond maybe too expensive for mysql server. When high concurrency(in my sysbench test above, it is 400 threads), the ordered_commit request queue become large, and will wait for much more time than 1ms. And, this will lead low performance of xa transactions. If use ut_delay replace the origin std::this_thread::sleep_for, the qps of sysbench have a obvious high improvement (9210.17 to 25834.32). So, i still think here should do optimize, maybe not use ut_delay, but it should have some way to reduce the sleep time.
[9 Jun 2020 13:33]
MySQL Verification Team
Hi Mr. Zhang, We have had further discussions and concluded that this would be a good performance improvement request. Verified as performance improvement bug. Thanks.
[3 Dec 2020 19:31]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.23 release, and here's the proposed changelog entry from the documentation team: Persisting GTID values for XA transactions affected XA transaction performance. Two GTID values are generated for XA transactions, one for the prepare stage and another for the commit stage. The first GTID value is written to the undo log and later overwritten by the second GTID value. Writing of the second GTID value could only occur after flushing the first GTID value to the gtid_executed table. Space is now reserved in the undo log for both XA transaction GTID values. Thank you for the bug report.
[4 Dec 2020 12:54]
MySQL Verification Team
Thank you, Daniel.