Bug #75032 Don't write binlog position in trx_sys page if binlog_order_commits is disabled
Submitted: 28 Nov 2014 5:18
Reporter: zhai weixiang (OCA) Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Any
Assigned to: 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