MySQL Cluster

Features

Galera is a synchronous multi-master replication Cluster for MySQL/InnoDB databases, having features like:

• Synchronous replication
• Active-active multi-master topology
• Read and write to any cluster node
• Automatic membership control, failed nodes drop from the cluster
• Automatic node joining
• True parallel replication, on row level
• Direct client connections, native MySQL look & feel

Benefits

The benefits of these features are:

• No slave lag
• No lost transactions
• Read scalability and write …

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.

We used the wsrep_notify_cmd variable to hook our own script into the Galera Cluster which disables each Node on the Load Balancer when its state changed.

# my.cnf
#
[mysqld] …

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. You can book your training here.

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:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they …

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-Cluster&termin=30.05.-31.05.2012

Introduction

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

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But …

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:

More detailed information about such set-ups you can find in the MySQL Cluster documentation.

Situations that lead to a …

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:

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:

EXPLAIN …

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.

5.1-dd is for tables stored on disk. The ndb_size.pl estimates are experimental and should not be trusted. Notably we don’t take into account indexed columns being in DataMemory versus …

This is a chaotic collection of my MySQL Cluster experience…

Table of Contents

• config.ini template
• my.cnf template
• General Rules and/or experience
• MySQL Cluster restore
• Skript for converting tables to NDB (alter_engine.pl)
• MySQL Cluster memory sizing

config.ini template

A generic MySQL Cluster configuration file (config.ini) to start with. It is pretty much what the MySQL Cluster experts recommend right now:

#
# config.ini
#

# This configuration file is fore MySQL Clusters 6.2 and above...

# …

MySQL Cluster is pretty fast. The reason for this is, that it is completely 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 …

Table of Contents

• 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
• Test restore procedure
• Reset a MySQL user password
• Reset the MySQL root user password
• How to enable the InnoDB plugin
• Storage Engines shipped with MariaDB / MySQL
• Compiling MySQL Cluster ndb-test fails
• NDB information schema does not show up
• Hyper …

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

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

Table of Contents

• General limitations of MySQL
• Limitations of MySQL 4.1
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of MySQL 5.0
• Limitations of Joins
• Limitations of the MyISAM storage engine
• Limitations of InnoDB
• Limitations of MySQL 5.1
• Limitations of Joins
• Limitations of Partitions
• Limitations of MySQL Cluster
• Limitations in Galera Cluster …

Performance Monitor for MySQL (mpm)

The FromDual Performance Monitor for MySQL (mpm) is a monitoring solution for MySQL, MySQL Cluster, Galera Cluster, Percona Server and MariaDB databases. The mpm can be downloaded from here.

It is based on the monitoring solution Zabbix.

The Performance Monitor for MySQL (mpm) supports the following Storage Engines:

• MyISAM / Aria
• InnoDB / XtraDB
• NDB
• PBXT

Further you can also monitor:

• Master - Slave replication set-ups
• Unix processes (mysqld, ndbd)
• MySQL/Percona/MariaDB …

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!):

#
# config.ini
#

[NDB_MGMD DEFAULT]
DataDir                      = …

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:

MySQL error code 136: No more room in index file

The usual command that helps me in such a situation is a follows:

# perror 136
MySQL error code 136: No …

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 failover or reconnection is just a dream (maybe it works in about 90% of the cases at least).
• Gaps: Whenever I do a cluster reconfiguration (should not be necessary too often, see loosing connection …

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.

The parameter DiskSyncSize is related to the parameters …