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:

Please find here the meaning of each parameter:

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.

This is an automated analysis of the DBI:mysql:database=foodmart;host=localhost database for migration into MySQL Cluster. No warranty is made to the accuracy of the information.

This information should be valid for MySQL 4.1 and 5.0. Since 5.1 is not a final release yet, the numbers should be used as a guide only.

This is a chaotic collection of my MySQL Cluster experience...

MySQL Cluster is pretty fast. The reason for this is, that it is completly memory based. Nowadays memory is still, in contrary to disk, limited to your systems. Thus, before installing a MySQL Cluster you have to calculate the amount of memory you need.

To say it in advance: You should consider to only use 64-bit Linux system with huge amount (4 - 64 GB) of RAM!

In release 5.1 MySQL Cluster became disk based. Now you have the possibility to swap out some data to disk. How much it is we will probably see a little further down...

Content

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL

Often asked but informations are spread around: The limitations of MySQL.

If you know any other MySQL limitations, please let us know.

Performance Monitor for MySQL

The FromDual Performance Monitor for MySQL is a monitoring solution for MySQL and MariaDB databases.
It is based on the monitoring solution Zabbix.

The Performance Monitor for MySQL supports the following Storage Engines:

• Aria
• InnoDB
• MyISAM
• NDB

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.

Recently we were migrating an InnoDB/MyISAM schema to NDB.

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:

• SQL-nodes are still loosing too easy connection to cluster after data node or management node restart (which leads into gaps, see next point). Automatic fail over or reconnection is just a dream (maybe it works in about 90% of the cases at least).

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.