Bug #75032 Don't write binlog position in trx_sys page if binlog_order_commits is disabled
Submitted: 28 Nov 2014 5:18 Modified: 23 Mar 2020 21:15
Reporter: zhai weixiang (OCA) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[28 Nov 2014 5:18] zhai weixiang
Description:
While disabling binlog_order_commits , I noticed a very hot contention on trx sys page.

backtraces like :

     32 pthread_cond_wait,os_cond_wait(os0sync.cc:214),os_event_wait_low(os0sync.cc:214),sync_array_wait_event(sync0arr.cc:424),rw_lock_x_lock_func(sync0rw.cc:675),pfs_rw_lock_x_lock_func(sync0rw.ic:593),buf_page_get_gen(buf0buf.cc:3005),trx_sysf_get(trx0sys.ic:103),trx_sys_update_mysql_binlog_offset(trx0sys.ic:103),trx_write_serialisation_history(trx0trx.cc:1031),trx_commit_low(trx0trx.cc:1031),trx_commit(trx0trx.cc:1416),trx_commit_for_mysql(trx0trx.cc:1635),innobase_commit_low(ha_innodb.cc:3413),innobase_commit(ha_innodb.cc:3413),ha_commit_low(handler.cc:1512),MYSQL_BIN_LOG::finish_commit(binlog.cc:6754),MYSQL_BIN_LOG::ordered_commit(binlog.cc:7036),MYSQL_BIN_LOG::commit(binlog.cc:6340),ha_commit_trans(handler.cc:1455),trans_commit_stmt(transaction.cc:434),mysql_execute_command(sql_parse.cc:5180),mysql_parse(sql_parse.cc:6481),dispatch_command(sql_parse.cc:1369),do_handle_one_connection(sql_connect.cc:1000),handle_one_connection(sql_connect.cc:916),pfs_spawn_thread(pfs.cc:1858),start_thread(libpthread.so.0),clone(libc.so.6)

I think we don't need to log the binlog position in trx_sys page because  we can't guarantee the order of innodb commit if binlog_order_commits is disabled.

How to repeat:
Under high concurrent update and disable binlog_order_commits, then check the output of pt-pmp or pstack

Suggested fix:
Don't write binlog position in trx_sys page if binlog_order_commits is disabled
[23 Dec 2019 7:19] MySQL Verification Team
Hi,

Thanks for the report. Not reproduced yet, you are running a regular master-slave replication, right? 

thanks
[16 Feb 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[12 Mar 2020 9:32] zhai weixiang
I've checked the latest code of MySQL8.0.19. It looks like innodb doesn't store binlog position anymore unless clone is used. quoted code from function trx_write_serialisation_history:

  /* Update the latest MySQL binlog name and offset information
  in trx sys header only if MySQL binary logging is on and clone
  is has ensured commit order at final stage. */
  if (Clone_handler::need_commit_order()) {
    trx_sys_update_mysql_binlog_offset(trx, mtr);
  }

I'm afraid this will break functionity of backup tools which relies on the position.
[23 Mar 2020 21:15] MySQL Verification Team
Hi Zhai,

Yes, that's not related to the original report. If you can show the backup tool issue please create a separate bug report with a test case, I will appreciate that very much.

best regards
Bogdan