MySQL Cluster
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:
- Shinguz's blog
- Login or register to post comments
- Read more
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...
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.
FromDual
FromDual - Vendor independent and neutral MySQL consulting!
News:
| 2010-09-07 | FromDual becomes Oracle Silver Level Partner | ![]() |
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
- PBXT
- XtraDB
- Master - Slave replication set-ups
- Unix processes (mysqld, ndbd)
- MySQL/MariaDB specific information
Other tools
FromDual also provides some other tools:MySQL Cluster Monitor (cmon)
To provide you higher availability and accessibility we have mirrored the following tools from severalnines.com: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.

