These topics will be discussed in more detail below.
Dear Schema Designer:
Galera's High Availability replication is available via
(This overview is valid even for same-datacenter nodes, but the issues of latency vanish.)
Cross-colo latency is an 'different' than with traditional replication, but not necessarily better or worse with Galera. The latency happens at a very different time for Galera.
In 'traditional' replication, these steps occur:
In Galera-based replication:
For an N-statement transaction: In a typical 'traditional' replication setup:
Bottom line: Depending on where your Clients are, and whether you clump statements into BEGIN...COMMIT transacitons, Galera may be faster or slower than traditional replication in a WAN topology.
With 'traditional' replication, especially if using "Statement Based Replication", all writes to the Master are applied to all Slaves, and they are replayed serially. Even with a multi-master setup, all writes are applied to all Masters. Hence, there is no way to get "write scaling"; that is, no way to increase the number of writes beyond what a single Master can handle.
With Galera, there is a moderate degree of write scaling.
There is no perfect number for wsrep_slave_threads; it varies with number of CPU cores, client connections, etc. A few dozen is likely to be optimal. Hence, this allows a significant degree of write scaling. If you are I/O-bound, that would be a scaling limitation.
By using wsrep_auto_increment_control = ON, the values of auto_increment_increment and auto_increment_offset will be automatically adjusted as nodes come/go.
If you are building a Galera cluster by starting with one node as a Slave to an existing non-Galera system, and if you have multi-row INSERTs that depend on AUTO_INCREMENTs, the read this Percona blog
Bottom line: There may be gaps in AUTO_INCREMENT values. Consecutive rows, even on one connection, will not have consecutive ids.
Beware of Proxies that try to implement a "read/write split". In some situations, a reference to LAST_INSERT_ID() will be sent to a "Slave".
For effective replication of data, you must use only InnoDB. This eliminates
You can use MyISAM and MEMORY for data that does not need to be replicated.
Also, you should use "START TRANSACTION READONLY" wherever appropriate.
Check for errors after issuing COMMIT. A "deadlock" can occur due to writes on other node(s).
Possible exception (could be useful for legacy code without such checks): Treat the system as single-Master, plus Slaves. By writing only to one node, COMMIT should always succeed(?)
What about autocommit = 1? wsrep_retry_autocommit tells Galera to retry if a single statement that is autocommited N times. So, there is still a chance (very slim) of getting a deadlock on such a statement. The default setting of "1" retry is probably good.
"Row Based Replication" will be used; this requires a PK on every table.
A non-replicated table (eg, MyISAM) does not have to have a PK.
(This section assumes you have Galera nodes in multiple colos.) Because of some of the issues discussed, it is wise to group your write statements into moderate sized BEGIN...COMMIT transactions. There is one latency hit per COMMIT or autocommit. So, combining statements will decrease those hits. On the other hand, it is unwise (for other reasons) to make huge transactions, such as inserting/modifying millions of rows in a single transaction.
To deal with failure on COMMIT, design your code so you can redo the SQL statements in the transaction without messing up other data. For example, move "normalization" statements out of the main transaction; there is arguably no compelling reason to roll them back if the main code rolls back.
In any case, doing what is "right" for the business logic overrides other considerations.
Galera's tx_isolation is between Serializable and Repeatable Read. tx_isolation variable is ignored.
Set wsrep_log_conflicts to get errors put in the regular MySQL mysqld.err.
XA transactions cannot be supported. (Galera is already doing a form of XA in order to do its thing.)
Here is a 'simple' (but not 'free') way to assure that a read-after-write, even from a different connection, will see the updated data.
SET SESSION wsrep_sync_wait = 1; SELECT ... SET SESSION wsrep_sync_wait = 0;
For non-SELECTs, use a different bit set for the first select. (TBD: Would 0xffff always work?) (Before Galera 3.6, it was wsrep_causal_reads = ON.) Doc for wsrep_sync_wait
This setting stalls the SELECT until all current updates have been applied to the node. That is sufficient to guarantee that a previous write will be visible. The time cost is usually zero. However, a large UPDATE could lead to a delay. Because of RBR and parallel application, delays are likely to be less than on traditional replication. Zaitsev's blog
It may be more practical (for a web app) to simply set wsrep_sync_wait right after connecting.
As said above, use InnoDB only. However, here is more info on the MyISAM (and hence FULLTEXT, SPATIAL, etc) issues.
MyISAM and MEMORY tables are not replicated.
Having MyISAM not replicated can be a big benefit -- You can "CREATE TEMPORARY TABLE ... ENGINE=MyISAM" and have it exist on only one node. RBR assures that any data transferred from that temp table into a 'real' table can still be replicated.
GRANTs and related operations act on the MyISAM tables in the database `mysql`. The GRANT statements will(?) be replicated, but the underlying tables will not.
Many DDL changes on Galera can be achieved without downtime, even if they take a long time.
RSU vs TOI:
Caution: Since there is no way to synchronize the clients with the DDL, you must make sure that the clients are happy with either the old or the new schema. Otherwise, you will probably need to take down the entire cluster while simultaneously switching over both the schema and the client code.
A "fast" DDL may as well be done via TOI. This is a tentative list of such:
Otherwise, use RSU. Do the following separately for each node: 1. SET GLOBAL wsrep_OSU_method='RSU'; -- This also takes the node out of the cluster. 2. ALTER TABLE ... 3. SET GLOBAL wsrep_OSU_method='TOI'; -- Puts back in, leading to resync (hopefully a quick IST, not a slow SST)
You can 'simulate' Master + Slaves by having clients write only to one node.
Until recently, FOREIGN KEYs were buggy.
LOAD DATA is auto-chunked. That is, it is passed to other nodes piecemeal, not all at once.
DROP USER may not replicate?
A slight difference in ROLLBACK for conflict: InnoDB rolls back smaller transaction; Galera rolls back last.
SET GLOBAL wsrep_debug = 1; leads to a lot of debug info in the error log.
Large UPDATEs / DELETEs should be broken up. This admonition is valid for all databases, but there are additional issues in Galera.
WAN: May need to increase (from the defaults) wsrep_provider_options = evs...
MySQL/Perona 5.6 or MariaDB 10 is recommended when going to Galera.
5.6 and Galera each use GTIDs (Global Transaction IDs).
A 5.6 GTID is the combination of a _server_ UUID and a sequence number.
A Galera GTID is the combination of a _cluster_ UUID and a sequence number.
If all the servers are in the same 'vulnerability zone' -- eg, rack or data center -- Have an odd number (at least 3) of nodes.
When spanning colos, you need 3 (or more) data centers in order to be 'always' up, even during a colo failure. With only 2 data centers, Galera can automatically recover from one colo outage, but not the other. (You pick which.)
If you use 3 or 4 colos, these number of nodes per colo are safe:
Posted 2013; VARIABLES: 2015; Refreshed Feb. 2016
Rick James graciously allowed us to use this article in the Knowledge Base.
Rick James' site has other useful tips, how-tos, optimizations, and debugging tips.
Original source: http://mysql.rjweb.org/doc.php/galera
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.