Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
10.1.17, 10.1, 10.2.8, 10.2.13
-
None
Description
When doing sysbench tests against standalone mysqld vs. single node Galera instance vs. two node cluster performance differs between MySQL and MariaDB setups.
With both MySQL and MariaDB standaline non-Galera nodes show best transactions per second throughput when not having to do any commit time fsync, as expected. Meaning that things are fast with innodb_flush_at_trx_commit != 1 and log_bin disabled or sync_binlog=OFF
A single node MySQL Galera setup shows almost the same numbers, is fast in the same config combinations.
A MariaDB Galera 10.x cluster, with 0 <= x <= 2 at least, shows a slightly different behavior though. Here only the combination "innodb_flush_at_trx_commit != 1, log_bin enabled, sync_binlog=OFF" is almost as fast as a standalone non-galera-enabled instance with the same "no fsync" settings. When disabling log_bin transactions per second go down quite a bit, to similar values as if innodb_flush_at_trx_commit was actually enabled.
I first suspected that group_commit might play a role, but Binlog_commits and Binlog_group_commits counters were always showing equal values in SHOW STATUS output, so this can be ruled out.
When running sysbench against a 2 node cluster performance differences were not as massive as in the single node setup, which was to be expected, but still visible.
So while turning off innodb_flush_at_trx_commit; and sync_binlog if log_bin is enabled, is usually considered safe in a cluster (if the hardware setup is redundant enough, e.g. simultaneous power failure on all nodes can be ruled out), and to improve performance/throughput a little bit, it turns out that with MariaDB the performance improvement is only visible if binlog writing is enabled, which is at least counter-intuitive.
Attachments
Issue Links
- duplicates
-
MDEV-16509 MariaDB 10.1+ with wsrep_on=ON performs better when log_bin is enabled
-
- Closed
-
- relates to
-
MDEV-11376 AliSQL: [Feature] Issue#11 REDO LOG GROUP COMMIT AT SERVER LAYER
-
- Stalled
-
-
MDEV-11937 InnoDB flushes redo log too often
-
- Closed
-
-
MDEV-16442 Document tc.log usage by default and the fact that wsrep in 10.1+ is an XA-capable storage engine
-
- Closed
-