Transaction speed benchmarks for mySQL v5.6 replication - seems very slow

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results.

I used sysbench v0.5 for the tests:

prepare data

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
 --oltp-test-mode=complex --oltp-table-size=1000000 \
 --mysql-db=mydb --mysql-user=root --mysql-password=mypassword prepare

do the test

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
 --oltp-test-mode=complex --oltp-table-size=1000000 --oltp-read-only=off \
 --num-threads=6 --max-time=60 --max-requests=0 \
 --mysql-db=mydb --mysql-user=root --mysql-password=mypassword run

As you can see from the results below, master-master replication (percona with 3 machines) had the worst performance, then comes mySQL master-slave (2 machines) configuration and the fastest is mySQL as a single standalone server.

Is this the normal situation with replication solutions? It seemed so damn slow, 10x difference between the configurations looks abnormal to me. Maybe I am missing something... I was totally disappointed about Percona Galera Cluster, it has a reputation for being fast for innodb. Phew :)

Please check the information provided below and advise, thank you.

About the servers

Hardware

• Intel® Xeon® E5-1650 v2 Hexa-Core

• 64 GB ECC RAM

• 2 x 240 GB 6 Gb/s SSD Datacenter Edition (Software-RAID 1)

• 1 Gbit/s bandwidth

OS

• Debian Wheezy

• All packages updated/upgraded.

Connection etc.

-
Servers are at the same datacenter, all connected with a Gbit switch and have second ethernet cards, all configured for private networking between them.

-
Currently, there is no load on the servers.

Disk performance

first test

# hdparm -Tt /dev/sda

/dev/sda:
 Timing cached reads:   27166 MB in  2.00 seconds = 13599.63 MB/sec
 Timing buffered disk reads: 1488 MB in  3.00 seconds = 495.64 MB/sec

second test

```
# dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/output
10240+0 records in
10240+0 records o

You should put everything on a level playing field. How ?

Without proper tuning, it is possible for older versions of MySQL to outrun and outgun new versions.

• Sep 25, 2013 : Why should I use InnoDB and MySql instead of XtraDB and MariaDB?

• Mar 26, 2012 : Percona vs MySQL

• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

• Oct 05, 2011 : Query runs a long time in some newer MySQL versions

• Jun 19, 2011 : How do I properly perform a MySQL bake-off?

Before running SysBench on the three environments

• Make sure all InnoDB settings are identical for all DB Servers

• For the Master/Slave, run STOP SLAVE; on the Slave

• For PXC (Percona XtraDB Cluster), shutdown two Masters

Compare the speeds of just standalone MySQL, Percona, and MariaDB.
ANALYSIS

If MySQL is best (Percona people, please don't throw rotten vegetables at me just yet. This is just conjecture), run START SLAVE;. Run SysBench on the Master/Slave. If the performance is significant slower, you may have to implement semisynchronous replication.

If PXC is best, you may need to tune the wsrep settings or the network itself.

If MariaDB is best, you could switch to MariaDB Cluster (if you have the Money) or setup Master/Slave with MariaDB. Run Sysbench. If the performance is significant slower, you may need to tune the wsrep settings or the network itself.

Why tune wsrep settings ? Keep in mind that Galera wsrep (WriteSet Replication) uses virtually synchronous commits and rollbacks. In other words, either all nodes commit or all nodes rollback. In this instance, the weakest link would have to be

• how fast the communication between Nodes happens (especially true if the Nodes are in different data centers)

• if any one node has underconfigured hardware settings

• if any one node communicates slower than other node

Side Note : You should also make sure tune MySQL for multiple CPUs

• Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?

• May 07, 2012 : MySQL Server Performance

• Apr 26, 2012 : Is the CPU performance relevant for a database server?

• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

• Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

• Sep 20, 2011 : Multi cores and MySQL Performance

• Sep 12, 2011 : Possible to make MySQL use more than one core?

• May 26, 2011 : About single threaded versus multithreaded databases performance

UPDATE 2014-11-04 21:06 EST

Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):

This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.

SUGGESTION #1

For PXC, turn off one node. Run SysBench against a two node cluster. If the write performance is better than a three node cluster, then it is obvious that the communication between the nodes is the bottleneck.
SUGGESTION #2

I noticed you have a 42GB Buffer Pool, which is more than half the server's RAM. You need to partition the buffer pool by setting innodb_buffer_pool_instances to 2 or more. Otherwise, you can expect some swapping.
SUGGESTION #3

Your innodb_log_buffer_size is 8M by default. Try making it 256M to increase log write performance.
SUGGESTION #4

Your innodb_log_file_size is 512M. Try making it 2G to increase log write performance. If you apply this setting, then set innodb_log_buffer_size to 512M.

StackExchange Database Administrators Q#81619, answer score: 2