maria-discuss team mailing list archive

[Kristian Nielsen <knielsen@xxxxxxxxxxxxxxx>]

Greg <skygreg@xxxxxxxxx> writes:

> This morning I've removed DRBD to verify that it is not the bottleneck, and
> .... It's much faster with DRBD !

Yes, because with DRBD, an fdatasync() only needs to do a network trip to the
other node. On local disk, it needs a physical disk write, which can take 10
milliseconds (on non-SSD and non-battery-backup disk controller cache).

> I didn't found anywere if Bug#13669 (http://bugs.mysql.com/bug.php?id=13669)
> was fixed, but it's seems to,

Yes, it is fixed from MariaDB 5.3.

> as I see Binlog_commits
> =~ Binlog_group_commits. So I still have performance problems and perhaps

If Binlog_commits =~ Binlog_group_commits, then yes, you do not get much
benefit from group commit.

How many transactions do you have running in parallel? Binlog group commit
only helps if you have more than 2 transactions writing concurrently (and the
more, the better).

Of course, it could also be that the fdatasync() at commit is not a
bottlenect, which would also result in Binlog_commits =~
Binlog_group_commits. How many commits per second do you do?

> WL#4925 could help .... or I'll have to buy some better hardware.

Binlog pre-allocation can help a little bit. Basically, in MariaDB 5.5, you
need three fdatasync() calls per group commit (one in innodb prepare, one in
binlog write, one in innodb commit). The one in binlog write is more expensive
because the file length changes, so it requires to sync both the data and the
metadata. Though on DRBD it is probably not much more costly to send two disk
blocks to the other node as compared to one.

So binlog pre-allocate would remove the "more expensive" for binlog write, but
you would still have 3 fdatasync() calls per group commit.

In MariaDB 10.0, I implemented MDEV-181
(https://mariadb.atlassian.net/browse/MDEV-181). This reduces the 3
fdatasync() calls to 2.

Another task that would help is MDEV-47
(https://mariadb.atlassian.net/browse/MDEV-47). This would reduce the overhead
to a single fdatasync() call. However, like binlog pre-allocation, there are
currently no concrete plans for this.

 - Kristian.