The best MySql Cluster Setup I have benchmarked to date

Lately, I have been working trying out different mysql clustering environments. There always seems to be a drawback of the traditional methods. Either one is good for write, but not so much for read or vice versa. Also I have never found a stable multi master setup that performs well. Then I found Galera and installed it using this very handy tool at http://severalnines.com/ . I proceeded to bench it and perform redundancy tests, It did great on every level It is a multi-master setup that provides maximum performance doing reads and writes. As far as the comparisons and explanations, I really cannot do it any better then they do at severalnines, go I would advise anyone looking for the best cluster software to check it out. It also includes config tools for all other cluster setups as well. It literally saved me hours of work using the config tool.


For now we have 4 nodes (Vmware vms). 3 serve as master nodes, and one as cluster controller/load balancer. With our tests it performed higher on read and write then all other clustering methods.

We are using public port ips as some remote sites will have to use this so I have not tested against lan latency.

Any other galera users here? And if anyone wants to compare benches, that would be nice.

 

Sysbench results, and with anything hardware matters most but I was real pleased with this on 3 vps - OLTP test statistics:
queries performed:
read: 1401876
write: 500598
other: 200211
total: 2102685
transactions: 100000 (630.41 per sec.)
deadlocks: 134 (0.84 per sec.)
read/write requests: 1902474 (11993.37 per sec.)
other operations: 200211 (1262.15 per sec.)

Test execution summary:
total time: 158.6272s
total number of events: 100000
total time taken by event execution: 2537.2173
per-request statistics:
min: 12.37ms
avg: 25.37ms
max: 153.99ms
approx. 95 percentile: 33.48ms

Threads fairness:
events (avg/stddev): 6250.0000/527.56
execution time (avg/stddev): 158.5761/0.01

 

For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.

First of all, create the tables and add some data:

mysql> CREATE DATABASE clusterdb;USE clusterdb;
mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
ENGINE=ndb;
mysql> INSERT INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
('Oxfordshire','England'),('Buckinghamshire','England');
mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county)
REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
mysql> SELECT * FROM counties;SELECT * FROM towns;
+-----------------+---------+
| county | country |
+-----------------+---------+
| Shropshire | England |
| Buckinghamshire | England |
| Berkshire | England |
| Oxfordshire | England |
+-----------------+---------+
4 rows in set (0.00 sec)

+------------+-------------+
| town | county |
+------------+-------------+
| Reading | Berkshire |
| Shrewsbury | Shropshire |
| Maidenhead | Berkshire |
| Oxford | Oxfordshire |
+------------+-------------+
4 rows in set (0.00 sec)