Mysql – Transaction speed benchmarks for theSQL v5.6 replication – seems very slow

configurationMySQLperconaperformancereplication

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 out
 83886080 bytes (84 MB) copied, 0.0517404 s, 1.6 GB/s

/etc/my.cnf Configuration

Percona's wizard was used for the initial configurations.
I used the official manuals/websites and other reliable sources in order to learn and configure the replication.
InnoDB was used for default storage engine (the test table created by sysbench is also InnoDB)

Percona XtraDB v5.6 Galera Cluster: my.cnf for the first node

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[sst]
streamfmt=xbstream

[xtrabackup]
# compress
# compact
parallel=8
compress-threads=8
rebuild-threads=8

[mysqld]
wsrep_node_name=db1
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL
wsrep_cluster_address=gcomm://192.168.1.4,192.168.1.5,192.168.1.6
# This changes how |InnoDB| autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node #1 address
wsrep_node_address=192.168.1.4
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_cluster
# Authentication for SST method
wsrep_sst_auth="replicateuser:replicateuserpassword"

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# Rolandomysqldba's recommendation
innodb_thread_concurrency       = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64

# MyISAM #
key_buffer_size                = 32M
myisam_recover_options         = FORCE,BACKUP

# SAFETY #
max_allowed_packet              = 16M
max_connect_errors              = 1000000
skip_name_resolve
sysdate_is_now                  = 1
innodb                          = FORCE

# DATA STORAGE #
datadir                         = /var/lib/mysql/

# BINARY LOGGING #
log_bin                         = /var/lib/mysql/mysql-bin
expire_logs_days                = 14
sync_binlog                     = 1

# CACHES AND LIMITS #
tmp_table_size                  = 32M
max_heap_table_size             = 32M
query_cache_type                = 0
query_cache_size                = 0
max_connections                 = 500
thread_cache_size               = 50
open_files_limit                = 65535
table_definition_cache          = 4096
table_open_cache                = 8K

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 42G

# LOGGING #
long_query_time                 = 5
log_error                       = /var/log/mysql/error.log
log_queries_not_using_indexes   = 1
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M

Sysbench Test Results

mySQL v5.6.21 (Single)

transactions: 101001 (1683.29 per sec.)

mySQL v5.6.21 Replicated (Master + 1 Slave)

Master and Slave online

transactions: 10501  (174.95 per sec.)

Slave was offline, binlog sync was off

transactions: 11280  (187.86 per sec.)

Slave was offline

transactions: 10779  (179.55 per sec.)

With master to slave delay (1 hr)

transactions: 10595  (176.48 per sec.)

MariaDB v5.5 (Single)

transactions: 73683  (1228.00 per sec.)

Percona XtraDB v5.6 Galera Cluster (3 Master-Master, xtrabackup-v2)

Master (initial node)

transactions: 703    (11.65 per sec.)

Test on another node

transactions: 643    (10.67 per sec.)

Changing replication method to rsync

transactions: 652    (10.80 per sec.)

Best Answer

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.

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

MySQL Replication – Error 1236 ‘Found Old Binary Log Without GTIDs’ After Master Restart

Something is not right about your process.

Usually, when I see error 1236, such the following I used in my old post How can you monitor if MySQL binlog files get corrupted?

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

here was the situation: When doing MySQL Replication without GTID, the IO Thread examines the position from the latest Master binlog. If Read_Master_Log_Pos is bigger than the actual filesize of the binlog, you get error 1236.

When doing MySQL Replication with GTID, the situation is somewhat similar. The IO Thread is looking for some kind of closure with regard to the GTID it was last using. When you restarted MySQL on the Master, you closed the last binlog on the Master and opened a new binlog upon startup. The IO Thread on the Slave was still active. Thus, the same error number is coming up.

The next time you restart a Master, remember the Slaves are active.

The slave should have reconnected after a minute, but that is not happening for you.

To play it safe, you should do the following

On the Slave, STOP SLAVE;
On the Master, service mysql restart
On the Slave, START SLAVE;

You should not have to do this. As an alternative, try setting up replication with heartbeat set at one tenth of a second:

CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 100;

This should make the IO Thread on the Slave a little more sensitive