MySQL Cluster

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

Hallo MySQL Gemeinde,

Im neuen iX (2012-10) hat es zwei nette Artikel über: MySQL HA-Lösungen (S. 116) und MySQL 5.6 (S. 66).

Viel Spass beim Lesen!

The dates for the next MySQL Cluster (ndb) trainings with the Linux Hotel are scheduled now: March 11 - 12 2013 and September 23 - 24 2013.

Die Daten für die nächsten MySQL Cluster (ndb) Schulungen im Linux Hotel sind jetzt festgelegt: 11./12. März 2013 und 23./24. September 2013.

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL:

Am 30. und 31. Mai findet im Linux-Hotel in Essen ein MySQL-Cluster Kurs statt. Der Kurs wird durchgeführt und es hat noch Plätze frei!

Weitere Infos unter: http://www.linuxhotel.de/kurs/mysql-cluster/

Anmeldung: http://www.linuxhotel.de/cgi-bin/anmeldung_kurs.pl?veranstaltung=MySQL-C...

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

Vor ein paar Tagen hatte ich wieder einmal mit einer MySQL Cluster Replikation zu tun. Ich habe das schon eine Weile nicht mehr angelangt und war somit vorbereitet, wieder einmal ein paar Überraschungen zu erleben.

Diejenige, für welche MySQL Cluster - Cluster Ring-Replikationen das tägliche Brot ist, können diesen Artikel getrost überspringen. Alle anderen können möglicherweise von unseren Erfahrungen profitieren.

Wir hatten das folgende MySQL Cluster Konstrukt im Einsatz:

A few days ago I had to deal with MySQL Cluster replication. I did not do this for a while so I was prepared to expect some interesting surprises once again.

For those who MySQL Cluster - Cluster circular replication is the daily business they may skip this article. For all the others they possibly can profit from our learnings.

I am talking about the following MySQL Cluster set-up:

MySQL Cluster is mainly an in-memory database. Nevertheless it requires a good I/O system for writing various different information to disk.

The information MySQL Cluster writes to disk are the:

• Global Checkpoints (GCP) which are the transactions.
• Local Checkpoints (LCP) which is a dirty image of the data.
• Backup.

In the following schema (a 2-node Cluster) you can see what is related to each other:

Bei einem Kunden sind wir auf ein nettes Beispiel gestossen, wie der MySQL Optimizer schummelt, wenn er mit dem MySQL Cluster zusammen arbeiten sollte. Dieser Kunde hat Abfragen, welche auf dem Entwicklungssystem nicht sonderlich langsam gelaufen sind. Aber wenn er diese Abfragen auf dem Abnahmetest-System (mit wesentlich mehr Daten) ausführt, benötigen diese viel zu viel Zeit, was für ihn nicht akzeptabel ist, da diese Abfragen mehrmals pro Sekunde abgesetzt werden können.

At a customer we had a nice example of how the MySQL Optimizer is cheating when used in combination with the MySQL Cluster. The customer had queries running not too slow in the development environment but when he tried them on the acceptance test environment (with more data) the query was running much too long which was unacceptable because this query can occur many times per second.

What has happened?

First of all we had a look at the execution plan of the query generated by the MySQL Optimizer:

This week we had a very interesting problem at a customer: They complained, that their MySQL Cluster takes about 1 hour for a --initial start-up. After some tuning on the hardware and the config.ini they brought it down to around 40 minutes. But this is still unacceptable long...

This sounds a little strange to me. But let us have a look at their config.ini first. It looked more or less like the following (which is already tuned!):

Recently we were migrating an InnoDB/MyISAM schema to NDB. I was too lazy to calculate all the needed MySQL Cluster parameters (for example with ndb_size.pl) and just took my default config.ini template.

Because I am really lazy I have a little script doing this for me (alter_engine.pl).

But suddenly my euphoria was stopped abruptly by the following error:

According to Johans wishes I write down my concerns about MySQL (Cluster) replication. These items are things I run again and again into it with customers:

The parameter DiskSyncSize is a MySQL Cluster parameter and was added in MySQL 5.1.23.

After the amount of stored bytes of data per file, the data node will fsync (flush) the LCP file to disk, even if a fsync is not needed for consistency.

This is done because the OS will otherwise buffer all the writes, and when a fsync is really needed, it can take a lot of time...

Originally this parameter was hard coded. Now it defaults to 4 Mbyte.