Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Research on perf degradation with binlog_transaction_dependency_tracking=WRITESET

Description

  1. Oracle changed in MySQL 8.3.0 default value of binlog_transaction_dependency_tracking from COMMIT_ORDER to WRITESET

  2. Using binlog_transaction_dependency_tracking=WRITESET causes about 5% perf regression for write workloads on source

  3. Oracle removed binlog_transaction_dependency_tracking in MySQL 8.4.0 so we can’t switch back to COMMIT_ORDER e.g. when we don’t use replication

Environment

None

Activity

Show:

Yura Sorokin September 10, 2024 at 11:47 PM

The patch has been back-ported to 8.0 branch (https://perconadev.atlassian.net/browse/PS-9378 ) and will be available in the upcoming PS 8.0.39 release.

Unfortunately, as 5.7 does not use c++11 by default, std::unordered_map is not available there and therefore the patch won’t be back-ported to this branch.

Yura Sorokin August 23, 2024 at 6:54 PM

We haven’t contributed this to upstream yet. We are still discussing this, to be honest (although the change itself is that simple that Oracle can definitely implement the same on their own).

Backporting this to 8.0 and even 5.7 seems like a good idea - I do not see any obstacles to do so.

BTW, we were also experimenting with additional container tuning, so most probably there will be more improvements in 8.4.2.

Jean-François Gagné August 14, 2024 at 6:08 PM

I see there is a patch for this for 8.4, will this be contributed upstream ?

Also, the binlog_transaction_dependency_tracking=WRITESET feature is in 8.0 and 5.7, but not enabled by default. Are-you considering back-porting ?

And btw, this is a very nice catch !

Przemyslaw Skibinski July 10, 2024 at 12:34 PM

It seems that we found a workaround for this issue. The results are average QPS for 6 write workloads:

mysql-8.3.0~407 binlog_transaction_dependency_tracking=COMMIT_ORDER 56741 mysql-8.4.1 binlog_transaction_dependency_history_size=1 56177 mysql-8.4.1 binlog_transaction_dependency_history_size=1K 53687 mysql-8.4.1 binlog_transaction_dependency_history_size=10K 53006 mysql-8.4.1 binlog_transaction_dependency_history_size=25000 (default) 52751 mysql-8.4.1 binlog_transaction_dependency_history_size=256K 51812

Probably the structure that keeps row hashes is not a hash table.

Done

Details

Assignee

Reporter

Labels

Upstream Bug URL

https://bugs.mysql.com/bug.php?id=114903

Needs QA

Yes

Sprint

Fix versions

Priority

Smart Checklist

Created July 10, 2024 at 10:56 AM
Updated September 10, 2024 at 11:47 PM
Resolved August 23, 2024 at 2:26 PM