MySQL Bugs: #75032: Don't write binlog position in trx_sys page if binlog_order

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

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

Hi,

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

thanks

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".

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.

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