MySQL Cluster
Migrating from MySQL Master-Master Replication to Galera Multi-Master Replication
Submitted by Shinguz on Fri, 2011-12-02 14:48Introduction
Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:
MySQL Cluster - Cluster Ring-Replikation mit 2 Replikations-Kanälen
Submitted by oli on Wed, 2011-01-12 20:25Vor 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:
MySQL Cluster - Cluster circular replication with 2 replication channels
Submitted by Shinguz on Mon, 2011-01-10 22:30A 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 Local Checkpoint (LCP) and Global Checkpoint (GCP)
Submitted by Shinguz on Thu, 2010-07-29 15:29MySQL 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:
Wie der MySQL Optimizer schummelt, wenn es um MySQL Cluster geht...
Submitted by oli on Thu, 2010-05-20 10:50Bei 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.
How the MySQL Optimizer with MySQL Cluster is cheating you...
Submitted by Shinguz on Mon, 2010-05-17 19:10At 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.
MySQL Cluster analysis for foodmart
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.
MySQL Cluster overview
This is a chaotic collection of my MySQL Cluster experience...
MySQL Cluster memory sizing
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...
MySQL Questions & Answers
Content
Limitations of MySQL
Often asked but informations are spread around: The limitations of MySQL.
If you know any other MySQL limitations, please let us know.
MySQL Tools
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
Why does MySQL Cluster takes so long for --initial?
Submitted by Shinguz on Thu, 2008-11-13 21:43This 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.
MySQL Cluster: No more room in index file
Submitted by Shinguz on Tue, 2008-09-23 16:18Recently we were migrating an InnoDB/MyISAM schema to NDB.
My thoughts about MySQL (Cluster) replication
Submitted by Shinguz on Sat, 2008-06-28 12:00According 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).
Some more details about DiskSyncSize
Submitted by Shinguz on Thu, 2008-05-29 13:43The 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.
