MySQL Bugs: #82422: GTIDS REDUCE PERFORMANCE ON WORKLOADS WITH MANY SMALL TRANSACTIONS

Bug #82422	GTIDS REDUCE PERFORMANCE ON WORKLOADS WITH MANY SMALL TRANSACTIONS
Submitted:	3 Aug 2016 11:27	Modified:	16 Dec 2016 14:41
Reporter:	Vitor Oliveira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
On modern machines with high throughput when the transactions are small and the number of client threads is high there is an unexpected overhead from using binlogs with GTIDs on the master compared to using binlogs but no GTID.

An example on sysbench update index in the following table:
-----------------------------------------------------
| threads | 5.7.14  |   +gtid |  +binlog | +bl+gtid |
----------|---------|---------|----------------------
|       1 |  10.330 |   10552 |    8.009 |    8.217 |
|       5 |  39.084 |   37058 |   33.214 |   32.554 |
|      10 |  73.881 |   76218 |   52.040 |   53.115 |
|      20 | 130.643 |  122096 |   82.155 |   74.603 |
|      30 | 148.363 |  144632 |   84.193 |   72.298 |
|      50 | 116.120 |  114126 |   84.460 |   66.195 |
|      70 | 110.324 |  108708 |   83.172 |   57.994 |
|     100 | 100.662 |  102192 |   81.470 |   57.119 |
|     200 |  72.117 |   71628 |   69.122 |   33.233 |
|     300 |  56.559 |   56301 |   57.755 |   28.657 |
-----------------------------------------------------
(this is for a server with binlog only and no slaves, in a machine with 36 cores
and SSD, without durability)

This seems to be a follow-up on Bug#74328, which solved the problem on lower threads counts/smaller machines.

How to repeat:
Execute the sysbench update index on a modern machine with many processors in a single server with binlog and GTID disabled, and them repeat with GTIDs enabled.

Suggested fix:
At present automatic GTIDs are fetched from the free_intervals and assigned to each transaction one-by-one, even if the transactions themselves are grouped for binary log group commit at the flush stage. 
Then, at the commit phase, the gtid_executed is also updated transaction-by-transaction, even if we can know in advance how it needs to be updated  as whole given that no intermixing of other transactions at this step is possible. This is more of a problem when no slave updates are done, and the gtid_executed table has to suffer more inserts and more complicated compression than would be needed if it was updated just once per group commit step.
A more efficient approach would be to get from the free_intervals not one at a time in flush_thread_caches/flush, but several GTIDs at a time (as many as the flush queue needs) in process_flush_stage_queue() an save those GTIDs in a set that would be summed to gtid_executed in the commit phase in a single operation.

Posted by developer:
 
The proposed patch was tested with Sysbench RW, Update Index and Write-only (RW without reads).

+-----------+---------+-----------------+----------------+--------------+
| BENCHMARK | THREADS |OVERHEAD BEFORE | OVERHEAD AFTER  | SANITY CHECK |
+-----------+---------+-----------------+----------------+--------------+
| Sysbench  |    20   |        0,1%     |       -0,5%    |      0.4%    |
| RW        |    50   |        1,4%     |        0,3%    |     -0.5%    |
| (SSD)     |   100   |        1,4%     |       -1,3%    |      0.0%    |
|           |   300   |        7,3%     |       -1,1%    |      0.2%    |
+-----------+---------+-----------------+----------------+--------------+
| Sysbench  |    20   |        9,0%     |        2,3%    |      1.2%    |
| Update    |    50   |       21,2%     |        3,0%    |     -0.3%    |
| Index     |   100   |       41,1%     |        4,5%    |      1.5%    |
| (SSD)     |   300   |       61,1%     |        5,5%    |      1.0%    |
+-----------+---------+-----------------+----------------+--------------+
| Sysbench  |    20   |        1,0%     |        0,5%    |      1.5%    |
| Write-    |    50   |       -3,0%     |        1,1%    |      1.1%    |
| only      |   100   |       -2,2%     |       -0,2%    |     -3.2%    |
| (SSD)     |   300   |       28,7%     |       -1,1%    |      0.1%    |
+-----------+---------+-----------------+----------------+--------------+

The sanity check column presents the results of the binlog without GTID setup with the new patch compared to baseline, in order to evaluate if the patch introduced a degradation.
The tests were repeated 5 times and the median overhead was chosen (small variations may be the result of this).

The issues seems to be solved: no degradation found in RW and Write-only and only a small degradation in Update Index, which I consider justified.

Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 5.7.17 and 8.0.1 changelogs:
The group commit update of GTIDs has been refactored to improve performance on workloads with many small transactions.

Bug#75299 group commit with GTID enabled need more optimization

was marked as a duplicate.