Skip to content

Comparing Oracle MySQL Group Replication and Galera Cluster through a probability perpective seems quite interesting.

At commit time both use a group certification process that requires network round trips. The required time for these network roundtrips is what will mainly determined the cost of a transaction. Let us try to compute an estimate of the certification process cost. The duration of these network roundtrips duration can be model by random variable with an associated probability distribution law.
Keep on reading!

MaxScale is a Proxy for the MySQL protocol built with a modular architecture.
The underlying concept of modules allows to extend the MaxScale proxy services. The current version implements Read Write splitting and Connection Load Balancing. Internally MySQL queries go through a SQL parsing phase. This gives MaxScale great capabilities regarding queries routing.
Keep on reading!

At FOSDEM 2014 ProxySQL and MaxScale were both presented. Both are proxy that can help build sophisticated MariaDB/MySQL architectures.
But currently what is the most used proxy with MySQL? It is HAproxy. HAproxy is a level 4 proxy that has no knowledge of the MySQL protocol. Being low level makes it very fast but it cannot accomplish any advanced proxy task. In many case this is enough for pure load balancing. But it cannot handle filtering, routing, query rewriting. This requires to understand the MySQL protocol and to look at the query passing through the proxy.
Keep on reading!

The slides and replay of yesterday's webinar on the MariaDB CONNECT storage engine have just been posted. First I want to thank the numerous attendees.
You have shown great interest on the parallel execution of query on distributed MySQL Servers. I agree this is cool.
The ODBC capabilities seems also to generate interest. This make it simple to access an ODBC datasource (SQLServer, Oracle ...) from plain MySQL syntax.

Here to view the replay
Here to get the slides

Let us know about your test. You just need to download MariaDB 10.0.5 which includes the CONNECT storage engine. Give us your feedback : positive, negative, improvement requests, bugs, doc pb, ...

Here to download the latest MariaDB 10.0.5 Beta release
Here to get the CONNECT Storage Engine Documentation

The CONNECT Storage engine implement the concept of a table made of multiple tables. These underlying tables can be distributed remotely. For example the underlying remote tables can be of ODBC or MySQL table type. this allows to execute distributed queries. What is nice is that we can execute this distributed query with parallelism.

How does it work ?

To explain it let us suppose we have 4 nodes : Node0 and Node1 Node2 Node3
Node0 has a MariaDB 10.0.4 installed with the CONNECT storage engine activated.

MariaDB [dbt3]> install plugin connect  soname 'ha_connect.so';

On the 3 other nodes we have MariaDB or plain MySQL installed.
the 'lineitem' table of the dbt3 benchmark has been created and loaded with one chunk of the 'lineitem' table on each node. This is a sharded table. For example to generate the second chunk of 3 chunks 'lineitem' table:

./dbgen -T L -fF -q -b dists.dss -s 1 -C 3 -S 2

Now on node0 we can create a CONNECT table. This table definition embed a SRCDEF parameter that force the computation of an aggregate on the remote server :

create Table lineitem1   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node1:3306/dbt3/lineitem3';

if we do a select on this table on node0 we get the result data that has been aggregated on node1. We access only one shard of the data.

MariaDB [dbt3]> select * from lineitem1;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
10000 rows in set (26.82 sec)

Let us now do the same on for node2 and node3 :

create Table lineitem2   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node2:3306/dbt3/lineitem3';
 
create Table lineitem3   ENGINE=CONNECT TABLE_TYPE=MYSQL
SRCDEF='select l_suppkey, sum(l_quantity) qt from dbt3.lineitem3 group by l_suppkey'
connection='mysql://proxy:manager1@node3:3306/dbt3/lineitem3';

Now we can use the ability of the the CONNECT storage engine to build a table that allows to see the 3 shards as a single table :

create Table alllineitem   (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3';

We can now query this table. All the aggregation computation will be done on the remote nodes and not on node0:

MariaDB [dbt3]> select * from alllineitem;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9998 | 2395 |
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
30000 rows in set (1 min 19.23 sec)

This basically takes 3 times longer than querying a single shard. This is because the request is executed sequentially on node 1 2 and 3.

We can request the CONNECT storage engine to do the job in parallel by adding the extra option : option_list='thread=1';

create Table alllineitem2   (`l_suppkey` INT(4) NOT NULL,`qt` DOUBLE ) ENGINE=CONNECT
TABLE_TYPE=TBL
table_list='lineitem1,lineitem2,lineitem3' option_list='thread=1';

If we rerun the query we get

MariaDB [dbt3]> select * from alllineitem2;
+-----------+------+
| l_suppkey | qt   |
+-----------+------+
|         1 | 2302 |
|         2 | 1960 |
...
|      9999 | 1908 |
|      1000 | 1756 |
+-----------+------+
30000 rows in set (26.69 sec)

We go back to the same execution time as with a single shard ! Is not that cool. I am sure you have a lot of objections, ideas about this approach. I do. But it is nice anyways.

If you want to know more do not forget to attend to the MariaDB CONNECT Storage Engine webinar :
November 7, 2013 - 5 pm CET / 4 PM UTC / 8 AM PST Register

and if you want to give it a try you are welcome and the doc to help is here:
MariaDB Connect Storage Engine documentation
Your help is welcome : bug reports, documentation fixes, usage feedback, suggestions.
Be indulgent it is still Alpha software 😉

MySQL Fabric is a very promising sharding framework. If I take Ulf Wendel definition of MySQL Fabric :

MySQL Fabric is an administration tool to build large “farms” of MySQL servers. In its most basic form, a farm is a collection of MySQL Replication clusters. In its most advanced form, a farm is a collection of MySQL Replication clusters with sharding on top.

So MySQL Fabric takes care of two very orthogonal features :

  • High availability of servers
  • Sharding of data

Let us forget about sharding and look at the High availability infrastructure.

Servers are included in groups, called "High Availability Groups" when we talk about HA.
Each Server has an associated Status (or Role): primary secondary, spare
Each Server has also a mode : Offline, Read-only, and Read-Write.
The implementation has been made to allow various HA implementation patterns.
The most common HA pattern is the Master/Slave HA group ( in that case we should call it a "replica set" which is the terminology used in MongoDB or Facebook MySQL Pool Scanner (MPS).

Mats Kindahl in his blog post on MySQL Fabric High Availability Groups mentioned that other HA solutions are possible for an availability group :

  • Shared Storage with SAN or NAS
  • Replicated storage like DRBD
  • MySQL Cluster shared nothing cluster

In the case of a HA group based on MySQL Cluster the group is self-managing regarding HA and MySQL Fabric does not handles the failover. With the "Shared Storage" and "Replicated storage" availability groups the secondary servers will be offline.

So one of my ideas that I hope is feasible would be to use MariaDB Galera Cluster as another HA solution with MySQL Fabric. The main advantage of this solution relates to the characteristics of MariaDB Galera Cluster. MariaDB Galera Cluster is an Active-active multi-master topology with synchronous replication. MariaDB Galera Cluster being innoDB based does not carry all the usage limitations associated with MySQL Cluster (main one being limited join capabilities).

Regarding to MySQL fabric the behavior of an availability group based on MariaDB Galera Cluster is identical to MySQL Cluster. It is a self-managing availability group.

MariaDB Galera Cluster

MariaDB Galera Cluster
Getting Started with MariaDB Galera Cluster

MySQL Fabric

MySQL Fabric: A new kid in the MySQL sharding world  2013-10-09 Serge Frezefond
MySQL Fabric: High Availability Groups  2013-10-21 Mats Kindahl
A Brief Introduction to MySQL Fabric  2013-09-21 Mats Kindahl
MySQL Fabric - Sharding - Introduction  2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Simple Example  2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Shard Maintenance  2013-09-27 VN
MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup  2013-09-22 VN
Installing MySQL Fabric on Windows  2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good?  2013-09-23 Ulf Wendel

Writing a Fault-tolerant Database Application using MySQL Fabric  2013-09-21 Alfranio Junior
Sharding PHP with MySQL Fabric  2013-10-09 Johannes Schlüter
MySQL Fabric support in Connector/Python  2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support  2013-09-21 Jess Balint

Like TokuDB, InfiniDB is now a fully open source server product. In the past infiniDB was "almost open source". The open source version was an old release with no access to the advance functions like MPP multi-server execution. This is no more the case. With InfiniDB 4 the open source version is the latest release giving access to all the advanced functionalities.

This is a really great move for the MariaDB / MySQL ecosystem. InfiniDB and TokuDB were two unique pieces of technology in the MySQL ecosystem. Having them both open source will trigger a broader adoption that will benefit to their enterprise releases.

Having in the MySQL ecosystem a column oriented database specifically designed for big data analytics is filling a real customer need. Column stores overcome the query limitations that exist in traditional RDBMS. InfiniDB is extremely good at using multicore server and massively parallel processing with multi-servers. InfiniDB can scale up on multi-cores server and scale out on a distributed architecture.

So thanks a lot to the InfiniDB team for their move. This will greatly benefit to them and to the dynamism of the MySQL / MariaDB ecosystem.

InfiniDB community web site
InfiniDB 4 sources
InfiniDB Enterprise web site

MySQL Connect 2013 has been a great edition. There was of course a lot of nice announcements of improvements in the the core MySQL server technology. One of the major announcement that received a lot of buzz was MySQL Fabric. MySQL Fabric is an infrastructure component aimed at simplifying construction of a highly available, sharded, MySQL server based architecture.

Horizontal scale out for MySQL is a hard problem. The MySQL sharding requirement has up till now only be addressed in a non general solution way. Using MySQL at Scale remains a big challenge.

All the big actors of the web have been faced with this scale out issue. They all have developed their own tools/framework to address this need. The new cloud providers have also been faced with this requirement when trying to offer database as a service solutions around MySQL with transparent elasticity.

We can mention the following Sharding solutions davelopped by the key players of the web :

  • Google/Youtube has built Vitess (in Go) and open sourced it
  • Twitter has built Gizzard (in Scala) and open sourced it
  • Tumblr has built Jetpants (in Ruby) and open sourced it
  • Facebook has also it own sharding framework which is a range based model. The HA is addressed with MHA. they have presented their architecture at various events

Theses frameworks cover the needs:

  • Master promotions,
  • Cloning slaves,
  • Supports a range-based sharding scheme for MySQL
  • Rebalancing shards,
  • Split a range-based shard into N new shards

High Availability is also addressed by most of these frameworks. MySQL Fabric also handles HA. All the improvements made around replication have helped handle correctly the HA part of the architecture in a more resilient way.

So now with the introduction of MySQL Fabric there is new sharding framework available. So what ? Interesting ?
Yes it is very interesting.

First, MySQL fabric has been written in python which in my opinion is a good point as devops love python.
Second, MySQL fabric has been released under the GPL license which is also a very good point.
Last MySQL Fabric is aimed as a general purpose sharding framework.

 

MySQL fabric is quite simple :

  • A Fabric Server holding and serving the sharding metadata.
  • A set of commands to do various action (split shard ,…)
  • Specific connectors : Java, Python to request shard location to the Fabric server through XMLRPC call.

Two questions for me:

  • What is the status of the PHP and C drivers regarding MySQL Fabric ?
  • Is it possible to fully abstract the client code from the fact that the data is sharded. Some connector config could specify the fabric server. The client code would then not be changed at all.

======

A Brief Introduction to MySQL Fabric  2013-09-21 Mats Kindahl
MySQL Fabric - Sharding - Introduction  2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Simple Example  2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Shard Maintenance  2013-09-27 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup  2013-09-22 VN (Narayanan Venkateswaran)
Installing MySQL Fabric on Windows  2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good?  2013-09-23 Ulf Wendel
---
Writing a Fault-tolerant Database Application using MySQL Fabric  2013-09-21 Alfranio Junior
MySQL Fabric support in Connector/Python  2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support  2013-09-21 Jess Balint

As alway with new technology there is always different approaches regarding the adoption.
You can try to use the bleeding edge features or start with a very standard configuration.
My personal advise to new users is to start with the most basic configuration.

This allow you to get familiar with the fundamentals :
- how to install
- how to operate
- how to monitor

For MariaDB Galera Cluster he most basic configuration is a 3 nodes cluster. You can chose to use it :
as an HA solution
Galera Cluster is currently the easiest way to solve the HA problem.
when you think HA think Galera Cluster it is so much simpler. Failover is totally transparent and you have nothing to do like you would have with standard replication. if you have a lodbalancer in front you just have to push out from the configuration THE FAILED NODE and that is done.

as a scale out solution
A usual MySQL scale out architecture is based on master/slaves architecture. This solution incurs to the application the choice of where to send the read (Master or most up to date slave…). Scale out of read with Galera cluster synchronous replication is much simpler. Nothing need to be done at the application layer.
You have a synchronous data up to data available on all nodes . You do not have the risk to read stale data when the replication lags. Nothing need to be done at the application level like taking care of reading were you write to have correct data.

Contrary to usual HA solutions or compare to MySQL cluster Galera Cluster is very simple to setup and operate.
Getting Started with MariaDB Galera Cluster

Then of course next step is to push Galera usage a little bit further. One main area is of course write scalability.
A few benchmark have been produced but we do not have yet much experience and  you have to be more careful. With write/write configurations you have to be careful about hot spots in the database. This can lead to deadlock and the behavior has to be correctly understood.

A good distribution for download to do your testing is : MariaDB Galera Cluster 5.5.29 Stable

Some useful pointers to understand various behaviours of Galera Cluster.

 

------- upcoming events :

Tomorrow Seppo Jaakola, Codership, will present Galera Cluster for MySQL & MariaDB at the Meetup SkySQL & MariaDB - Paris

Henrik Ingo, Max Mether and Colin Charles will present "MariaDB Galera Cluster Overview" at the free MySQL & Cloud Solutions Day taking place in Santa Clara the 26th of April. You can register for free.

 

By default InnoDB uses REPEATABLE READ as its isolation level. So this is the isolation level used with innoDB by almost all MySQL users.

The default isolation level of Oracle is READ COMMITTED. READ COMMITTED is the mode widely used by Oracle users. This mode incurs less penalty on the server scalability by allowing to support more concurrency.

The reason why InnoDB use REPEATABLE READ as its default is historical. This is a related to the way MySQL replication was developed . MySQL replication until 5.1 functioned with a statement based replication mechanism. This means that statements that occurs on the master server are replayed on the slave server. The statement base replication mode does not permit to use the READ COMMITTED isolation level. In that case replication will not guaranty consistency between the slave and the master.

The MySQL widely used statement based replication has some major drawbacks. The first one is that to guaranty the same effect of statements on slave as on master innoDB need to be careful when generating id through the autoincrement mechanism. The second impact is that InnoDB also needs to avoid phantoms to guaranty the same effect of a statement on the master and slave.

To generate consecutive autoincrement id during a multi rows insert a table level lock is necessary.

To handle phantom issue InnoDB has implemented a mechanism that consist in locking gaps.

UPDATE ... WHERE or a DELETE ... FROM ... WHERE requires InnoDB to set an exclusive next-key lock on every record the search encounters. This can dramatically impact concurrency on the server by forbidding inserts into the gaps.
INSERT ... SELECT will put a shared lock on all the selected rows. This basically prevent any update on the selected rows. This also seriously impact concurrency. This case is very common when reports are run on an OLTP server.
SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the search encounters and also on the corresponding clustered index records if a secondary index is used in the search.
SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the search encounters

Oracle does not behave that way. The Oracle replication mechanism is a row based mechanism. None of the blocking locks mentioned above with InnoDB will happen with Oracle.

Can we have with MySQL the same behavior as Oracle users ?

Yes we can ! Since version 5.1 MySQL offers a row based replication mode closed to what ORACLE is doing. This basically allow to run MySQL in READ COMMITTED isolation level. This can really benefit to MySQL performance and scalability. To achieve that it is mandatory to use Row based replication. This suppress most restrictions related to MySQL statement based replication. The READ COMMITTED isolation level relax most of the locking issues impaired by the REPEATABLE READ MODE. This seems anyway to be used rarely by MySQL 5.1 users !

I have no figures to prove performance improvement impacts of READ COMMITED isolation level with InnoDB,
so you should give it a try ! 🙂