Benchmarking Write Scalability

Overview

This is an insert performance test implemented by running just inserts with mysqlslap against 1-3 node MySQL/Galera clusters. The results show how multi-master cluster can process more inserts than single native MySQL 5.1.38 server.

Test Platform

We used 3 HP proliant servers for insert performance testing. The servers are Xeon 3.4 GHz 4 core CPU's with 3G RAM. Networking runs at 1Gb/s.
Linux:

Linux abyssinian 2.6.28-11-server #42-Ubuntu SMP Fri Apr 17 02:45:36 UTC 2009 x86_64 GNU/Linux

procinfo:

processor	: 3
vendor_id	: GenuineIntel
cpu family	: 15
model		: 4
model name	: Intel(R) Xeon(TM) CPU 3.40GHz
stepping	: 1
cpu MHz		: 3400.092
cache size	: 1024 KB
physical id	: 3
siblings	: 2
core id		: 0
cpu cores	: 1
apicid		: 7
initial apicid	: 7
fpu		: yes
fpu_exception	: yes
cpuid level	: 5
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov 
pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant
_tsc pebs bts pni dtes64 monitor ds_cpl est cid cx16 xtpr
bogomips	: 6800.42
clflush size	: 64
cache_alignment	: 128
address sizes	: 36 bits physical, 48 bits virtual
power management:

MySQL/Galera release is 0.7pre, which uses vsbes group communication backend. The vsbes daemon was running in one of the cluster nodes.
The mysqlap load was generated from a separate test client. Test client had sufficient performance and test client was not limiting benchmark results.

mysqlslap

The benchmark uses one table and keeps on inserting rows to this table. There are only inserts and no reads, so this is 100% write scalability test. The table spec is as follows:

CREATE TABLE test_insert (
  i INT AUTO_INCREMENT PRIMARY KEY,
  f FLOAT,
  d DATE,
  t TIME,
  c CHAR(8),
  v VARCHAR(1024)
) ENGINE=InnoDB;

We used mysqlslap to feed inserts in 'test_insert' table. The test runs 1M inserts altogether, and in the end we measure the total run time of the inserts. Mysqlslap invocation is like this:

mysqlslap --user=$DBMS_TEST_USER --password=$DBMS_TEST_PSWD \
          --host=$DBMS_HOST --port=$DBMS_PORT \
          --delimiter=';' --create-schema=$DBMS_TEST_SCHEMA \
	  --query="insert.sql" \
          --commit=$TRX_LEN \
	  --concurrency=16 --number-of-queries=1000000 \
	  --verbose --csv 

We varied the transaction length from 1 up to 1000 inserts per transaction, and longer transactions yield better insert rate.
The individual insert statements looks like:

INSERT INTO test.test_insert VALUES (
  DEFAULT, RAND(), CURRENT_DATE, CURRENT_TIME, 'pokemon', 'drizzl'
);

mysqlslap 1 row insert

This test has only one insert statement in each transaction. It will therefore result in one replication call per each issued insert statement.

system                  rows/sec
--------------------------------
mysql 5.1.38            8340

Galera 1 node           4122
Galera 2 nodes          4728
Galera 3 nodes          5807

Galera 1 node (no gcs)  6541

mysqlslap 10 row insert

This test executes 10 insert statements in each transaction.

system                  rows/sec
--------------------------------
mysql 5.1.38            14347

Galera 1 node           11403
Galera 2 nodes          15248
Galera 3 nodes          17504

Galera 1 node (no gcs)  14126

mysqlslap 100 row insert

This test executes 100 insert statements in each transaction.

system                  rows/sec
--------------------------------
mysql 5.1.38            17304

Galera 1 node           15081
Galera 2 nodes          22147
Galera 3 nodes          24737

Galera 1 node (no gcs)  15857

mysqlslap 1000 row insert

This test executes 1000 insert statements in each transaction.

system                  rows/sec
--------------------------------
mysql 5.1.38            16543

Galera 1 node           16009
Galera 2 nodes          21670
Galera 3 nodes          23225

Galera 1 node (no gcs)  16062

Conclusion

Results with 1 insert/transaction case show that the vsbes group communication system (in TCP socket transport) running at 1Gb/sec networking has reached its perfromance limits. With this platform vsbes can deliver max 5800 transactions/sec and this becomes as the bottleleneck for the benchmark.
The vsbes daemon was running in one mysqld node, which slows down the system a bit, due to flow control stepping in. The vsbes process uses ~20% of the CPU.
With 10 inserts per transaction, Galera cluster is already faster that native mysql 5.1.38. Best overall insert rate is achived with 100 inserts per transaction case, where 3 node Galera cluster can process 24 737 inserts per second, which is 1.4 times faster than native mysql.