I am currently evaluating the migration from our current database server to a three node cluster.
Our current server is a VM running MariaDB 10.1 with asynchronous replication to another server in order to perform backups of the database without any performance hits.
The read to write ratio is about 70/30, we are running an IoT platform with many sensors sending raw data that is written to the database before it is consolidated and presented to the end user.
Because of the high write to read ratio, I want to make sure that the cluster can perform at least as well as our current server on writes before migrating.
For this test, I started a VM on the Google Compute Engine with 16 vCPUS, 60GB of RAM and SSD local storage with 10,000 read IOPS and 15,000 write IOPS.
On this VM running Debian 8.3 I installed MariaDB 10.1 (which includes the Galera cluster), in order to run some benchmarks with Sysbench 0.5.
These are the MySQL parameters that I changed from the default mysql.cnf file:
[mysqld] binlog_format=ROW innodb_autoinc_lock_mode=2 innodb_flush_log_at_trx_commit=0 innodb_log_file_size=2G innodb_buffer_pool_size=4G sync_binlog=0
When running the update test from sysbench, I am noticing a huge overhead from enabling wsrep on the single node that I have currently deployed.
This is the command that I am using to run the benchmark:
sysbench --db-driver=mysql --num-threads=32 --max-time=30 --max-requests=0 --oltp-tables-count=32 --oltp-table-size=100000 --oltp-test-mode=complex --test=/root/sysbench/sysbench/tests/db/update_index.lua --mysql-db=sbtest --oltp-skip-trx=on run
With wsrep_on=OFF, I get around 54000 updates per second on the benchmark.
Setting wsrep_on to ON, I get around 19000 updates per second.
I was expecting a performance hit from enabling synchronous replication, but I did not expect it to be this much.
It doesn't look like there's a bottleneck from the CPU nor the IO, so why is the performance hit from enabling wsrep so high?