[MDEV-11900] Run benchmarks with sync-binlog before it gets enabled in 10.2 by default Created: 2017-01-24  Updated: 2017-08-28  Resolved: 2017-02-07

Status: Closed
Project: MariaDB Server
Component/s: Server
Fix Version/s: N/A

Type: Task Priority: Blocker
Reporter: Elena Stepanova Assignee: Axel Schwenke
Resolution: Fixed Votes: 0
Labels: 10.2-rc

Attachments: File sync_vs_async_HDD.ods     File sync_vs_async_SSD.ods    

 Description   

We are about to enable sync-binlog by default in 10.2. Please check how it affects performance on some standard read-write load with binary log enabled. No need for accurate numbers, but we should have at least a rough picture.



 Comments   
Comment by Axel Schwenke [ 2017-01-29 ]

I've run a series of OLTP benchmarks with varying write intensity (10, 22, 80 and 100% writes). I tested 3 different log settings:

  1. innodb_flush_log_at_trx_commit=0, sync-binlog=0
  2. innodb_flush_log_at_trx_commit=1, sync-binlog=0
  3. innodb_flush_log_at_trx_commit=1, sync-binlog=1

Naturally there is a big difference between 1. and 2., but also between 2. and 3. Here I see a performance drop between 10% and 50%. The effect is more pronounced when the datadir resides on a hard (rotating) disk. But even on a (fast) SSD the effect is clearly visible. For details see the attached spread sheets.

I tested MariaDB 10.1 and 10.2 and also MySQL 5.7. The numbers are in the same ballpark for all 3 of them with some slight advantage for 10.2. This however is no reason to celebrate, because in absolute numbers 10.2 comes out at the bottom.

I also spotted an irregularity here: it seems that MariaDB 10.2 does two fsyncs per commit on the InnoDB redo log as soon as one sets innodb_flush_log_at_trx_commit=1 and enables the binlog (no matter if sync-binlog=0 or 1). This is someting to investigate in a separate task.

I also checked the bytes written per transaction to the redo log and the binlog. There is a moderate increase of ~6 bytes per binlog event in 10.2 compared to 10.1. No numbers here for MySQL (it does not have a status variable for binlog writes).

Comment by Elena Stepanova [ 2017-01-29 ]

I'd say 50% is not too bad, that's something that I would initially expect, my results were much worse (but they aren't reliable).

I don't see it mentioned anywhere, did you run with the default statement binlog_format, or were there different ones? I wonder if it might make a difference.
Our new default is expected to be mixed, which can easily switch to row.

Generated at Thu Feb 08 07:53:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.