MySQL Bugs: #75299: group commit with GTID enabled need more optimization

Bug #75299	group commit with GTID enabled need more optimization
Submitted:	23 Dec 2014 15:45	Modified:	26 Jan 2017 19:14
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Recently, we are planning to enable GTID on production environment. But while testing the performance impact of GTID, We observed TPS decline and found backtrace like this  under high concurrency workload:

196 __lll_lock_wait(libpthread.so.0),_L_lock_854(libpthread.so.0),pthread_mutex_lock(libpthread.so.0),inline_mysql_mutex_lock(mysql_thread.h:673),lock(mysql_thread.h:673),lock_sidno(mysql_thread.h:673),Gtid_state::update_owned_gtids_impl(mysql_thread.h:673),MYSQL_BIN_LOG::finish_commit(binlog.cc:6785),MYSQL_BIN_LOG::ordered_commit(binlog.cc:7037),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:5299),mysql_parse(sql_parse.cc:6600),dispatch_command(sql_parse.cc:1430),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)

You can find a very hot mutex contention after group commit finished. every transaction will call finish_commit and remove it's gtid from owned_gtid set.

How to repeat:
With very extremely concurrency DML worklod

Suggested fix:
1. Why not remove gtid from owned_gtid set during the commit stage.

2. Or Maybe we can completely remove overhead of maintaining  owned_gtid if gtid is auto-generated . We can  immediately insert gtid  into logged_gtid after allocating.

Patch of the second advice ,just for discuss purpose and not fully tested (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: optimize_gtid.diff (application/octet-stream, text), 6.48 KiB.

How to repeat : With very extremely concurrency DML worklod ===> With extremely high concurrency DML worklod

Hello Zhai,

Thank you for the report and contribution.

Thanks,
Umesh

Posted by developer:
 
Thank you for the contribution.

We have addressed this in 5.7.17 (BUG#24398760). The bugfix took a different approach compared to this contribution: we do not want to add transactions to GTID_EXECUTED before they are committed as that would be a user-visible change which is potentially problematic (e.g. it might harm fail-over). Instead, it was possible to improve the locking schemes to avoid taking mutexes many times. In our benchmarks this reduced the overhead of enabling GTIDs (in the case of very high concurrency and very small transactions) from 60% before the bugfix to 5% after the bugfix.

If you have a chance to test 5.7.17, we would appreciate very much if you can confirm whether it fixes your problem or not.

I am closing this as a duplicate. If you feel this needs to be addressed in 5.6, please re-open the bug so we can evaluate if it is possible to backport the existing fix from 5.7 to 5.6.

Duplicate of

Bug#82422 GTIDS REDUCE PERFORMANCE ON WORKLOADS WITH MANY SMALL TRANSACTIONS