EmergencyEMERGENCY? Get 24/7 Help Now!

Test your knowledge: Percona XtraDB Cluster (PXC) quiz

 | April 28, 2015 |  Posted In: High-availability, Insight for DBAs, MySQL, Percona XtraDB Cluster

PREVIOUS POST
NEXT POST

Test your knowledge: Percona XtraDB Cluster (PXC)I often talk with people who are very interested in the features of Percona XtraDB Cluster (PXC) such as synchronous and parallel replication, multi-node writing and high availability. However some get confused when operating a real PXC cluster because they do not fully realize the implications of these features. So here is a fun way to test your PXC knowledge: try to solve these 12 questions related to PXC! (you will find the answers at the end of the post.)

Workload

1. With Galera 3.x, support for MyISAM is experimental. When can we expect to have full MyISAM support?
a. This will never happen as Galera is designed for transactional storage engines.
b. This is planned for Galera 4.0.

2. Why aren’t all workloads a good fit for PXC?
a. Execution plans can change compared to a regular MySQL server, so performance is sometimes not as good as with a regular MySQL server.
b. Large transactions and write hotspots can create performance issues with Galera.

3. For workloads with a write hot spot, writing on all nodes to distribute the load is a good way to solve the issue.
a. True
b. False

4. Optimistic locking is used in a PXC cluster. What does it mean?
a. When a transaction starts on a node, locks are only set on this node but never on the remote nodes.
b. When a transaction starts on a node, locks are only set on the remote nodes but never on the local node.
c. Write conflict detection is built-in, so there is no need to set locks at all.

Replication

5. Galera implements virtually synchronous replication. What does it mean?
a. A transaction is first committed locally, and then it is committed on all remote nodes at the same exact point in time.
b. Transactions are replicated synchronously, but they are applied asynchronously on remote nodes.
c. Replication is actually asynchronous, but as it is faster than MySQL replication, so marketing decided to name it ‘virtually synchronous’.

6. When the receive queue of a node exceeds a threshold, the node sends flow control messages. What is the goal of these flow control messages?
a. They instruct the other nodes that they must pause processing writes for some time, to allow the slow node to catch up.
b. The other nodes trigger an election and if they have quorum they will evict the slow node.
c. The messages can be used by monitoring systems to detect a slow node, but they have no effect.

7. When you change the state of a node to Donor/Desynced, what happens?
a. The node stops receiving writes from the other nodes.
b. The node intentionally replicates writes at a slower pace, this is roughly equivalent to a delayed replica when using MySQL replication.
c. The node keeps working as usual, but it will not send flow control messages if its receive queue becomes large.

High Availability

8. You should always use an odd number of nodes, because with an even number (say 4 or 6), the failure of one node will create a split-brain situation.
a. True
b. False

9. With a 3-node cluster, what happens if you gracefully stop 2 nodes?
a. The remaining node can process queries normally.
b. The remaining node is up but it stops processing queries as it does not have quorum.

Operations

10. If a node has been stopped for less than 5 minutes, it will always perform an IST.
a. True: SST is only performed after a node crash, never after a regular shutdown.
b. False: it depends on the gcache size.

11. Even with datasets under 5GB, the preferred SST method is xtrabackup-v2 not mysqldump.
a. True
b. False

12. Migration from a master-slave setup to a PXC cluster always involves a downtime to dump and reload the database.
a. True, because MySQL replication and Galera replication are incompatible.
b. False, one node of the PXC cluster can be set up as an asynchronous replica of the old master.

Solutions

1. a      2. b      3. b
4. a      5. b      6. a
7. c      8. b      9. a
10. b    11. a    12. b

PREVIOUS POST
NEXT POST
Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom. In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

4 Comments

  • Hi Stephane, this is a excellent idea and i would also like to know a more details explanation about each of the answers so that it gives me more information on how to improve my current cluster, e.g for work loads with write hotspots what is a good way to deal with it? or about using even number of nodes (i was always under impression we need odd number of nodes)

Leave a Reply