Skip to content

Galera cluster limitations

Conflicting write operations

You should avoid conflicting entries. In practice, this means that a separate connection (connector) for write operations. This ensures that write operations will be performed on only one node. A second connection will be balanced to all nodes and can only be used for read operations. It is not technically possible to prevent write operation from being sent to this connection. Therefore, it is necessary to prevent writes to this connecton inside the application. Otherwise, conflicting write operations will occur in the cluster, which the database will resolve with a deadlock. If the application does support such mechanism and its implementation would be complicated, it is possible to install MaxScale on the loadbalancer. It will split the query optimally based on type of query. However, this solution is naturally more CPU intensive than simple balancing of TCP traffic.

Quantity of write operations

Write operations are performed on all nodes. Therefore, performance for write operations does not increase with the number of servers in the cluster. On the contrary, due to the need to replicate the data to the remaining nodes, performance write operations is generally lower than on single server installation.

Writeset size

There should be no more than a few hundred MB of data (or tens of thousands of lines) changed in a single write operation. Such queries are extremely difficult to replicate to other nodes. If these recommended values are exceeded, it could lead to the collapse of entire Galera cluster and a service outage. Such queries need to be divided into several smaller writesets.

ALTER TABLE

You should avoid changes to table structure, especially for large tables. You should always test such operations in a lab environment and then execute in production environment, ideally at night/outside peak hours. If you cannot successfully execute the query in lab,  you will need to resolve the situation differently. For example by making the desired change elsewhere and re-importing the entire table.

InnoDB tables

Galera cluster supports replication of InnoDB tables only. All MyISAM tables must be converted to InnoDB tables.

Locking mechanisms

Locking mechanisms such as LOCK TABLES, FLUSH TABLES {explicit table list} WITH READ LOCK, (GET_LOCK (), RELEASE_LOCK () and so on are not supported. Global locking mechanisms such as FLUSH TABLES WITH READ LOCK are.

Primary key

All tables should have a primary key. Multi-column primary keys are supported and DELETE operations are not supported for tables without a primary key. Rows in tables without a primary key may have a different order on each node.

AUTOINCREMENT

Each node in the Galera cluster can write to a table. In case of multiple nodes writing to a table simultaneously, this could lead to duplicate values in the columns that use AUTOINCREMENT. To avoid such conflicts, Galera Cluster ensures that each record has a unique value. This is governed by auto_increment_increment and auto_increment_offset variable. Auto_increment_increment equals to the number of nodes in the cluster and auto_increment_offset has a different value on each node, ranging from 1 to n, where n is the number of nodes in the cluster.
Beware, that this is not a bug, but a feature. You must consider this when designing your appliacation.

Performance

The performance of the entire Galera cluster is limited by the performance of the slowest node in the cluster.

Query cache

Query cache must be deactivated.

wsrep_sync_wait

Setting this variable ensures that data is returned from a synchronized node. This is done by checking whether the node status before executing an operation type specified by the wresp_sync_wait value. New queries directed to the node are blocked during this check. This allows the data to synchronize up to the point when the check has started. At this point, the original query is executed. This will result in higher latency, but without the wresp_sync_wait setting, the node can return STALE data.