2015 June | dev-Ops

Galera replication – how to recover a PXC cluster

Galera replication for MySQL brings not only the new, great features to our ecosystem, but also introduces completely new maintenance techniques. Are you concerned about adding such new complexity to your MySQL environment? Perhaps that concern is unnecessarily.

I am going to present here some simple tips that hopefully will let fresh Galera users prevent headaches when there is the need to recover part or a whole cluster in certain situations. I used Percona XtraDB Cluster (project based on Percona Server and Galera library + MySQL extensions from Codership) to prepare this post, but most if not all of the scenarios should also apply to any solution based on MySQL+Galera tandem you actually chose, whether these are binaries from Codership, MariaDB Galera Cluster or maybe your own builds.

Unlike standard MySQL replication, a PXC cluster acts like one logical entity, which takes care about each node status and consistency as well as cluster status as a whole. This allows to maintain much better data integrity then you may expect from traditional asynchronous replication while allowing safe writes on multiple nodes in the same time. This is though for the price of more possible scenarios where database service will be stopped with no node being able to serve requests.

Lets assume the simplest case cluster of nodes A, B and C and few possible scenarios where some or all nodes are out of service. What may happen and what we have to do, to bring them (or whole cluster) back up.

Scenario 1

Node A is gracefully stopped. Likely for the purpose of maintenance, configuration change, etc.
In this case the other nodes receive “good bye” message from that node, hence the cluster size is reduced and some properties like quorum calculation or auto increment are automatically changed. Once we start the A node again, it will join the cluster based on it’s wsrep_cluster_address setting in my.cnf. This process is much different from normal replication – the joiner node won’t serve any requests until it is again fully synchronized with the cluster, so connecting to it’s peers isn’t enough, state transfer must succeed first. If the writeset cache (gcache.size), on nodes B and/or C has still all the transactions there were executed during the time this node was down, joining will be possible via (usually fast and light) IST. Otherwise, full SST will be needed, which in fact is full binary data snapshot copy. Hence it may be important here to determine the best donor, as shown in this article. If IST is impossible due to missing transactions in donor’s gcache, the fallback decision is made by the donor and SST is started automatically instead.

Scenario 2

Nodes A and B are gracefully stopped. Similar to previous case, cluster size is reduced to 1, hence even the single remaining node C forms aprimary component and is serving client requests. To get the nodes back into the cluster, you just need to start them. However, the node C will be switched to “Donor/Desynced” state as it will have to provide state transfer to at least first joining node. It is still possible to read/write to it during that process, but it may be much slower, depending how large state transfers it needs to send. Also some load balancers may consider the donor node as not operational and remove it from the pool. So it is best to avoid situation when only one node is up.

Note though, if you restart A and then B in that order, you may want to make sure B won’t use A as state transfer donor, as A may not have all the needed writesets in it’s gcache. So just specify the C node as donor this way (“nodeC” name is the one you specify with wsrep_node_name variable):

service mysql start --wsrep_sst_donor=nodeC

Scenario 3

All three nodes are gracefully stopped. Cluster is deformed. In this case, the problem is how to initialize it again. Here, it is important to know, that during clean shutdown, a PXC node writes it’s last executed position into the grastate.dat file. By comparing the seqno number inside, you will see which node is the most advanced one (most likely the last one stopped). Cluster must be bootstrapped using this node, otherwise nodes that had more advanced position will have to perform full SST to join cluster initialized from the less advanced one (and some transactions will be lost). To bootstrap the first node, invoke the startup script like this:

/etc/init.d/mysql bootstrap-pxc

service mysql bootstrap-pxc

service mysql start --wsrep_new_cluster

service mysql start --wsrep-cluster-address="gcomm://"

or in packages using systemd service manager (Centos7 at the moment):

systemctl start mysql@bootstrap.service

In older PXC versions, to bootstrap cluster, you had to edit my.cnf and replace previous wsrep_cluster_address line with empty value like this: wsrep_cluster_address=gcomm:// and start mysql normally. More details to be found here.

Scenario 4

Node A disappears from the cluster. By disappear I mean power outage, hardware failure, kernel panic, mysqld crash, kill -9 on mysqld pid, OOMkiller, etc. Two remaining nodes notice the connection to A node is down and will be trying to re-connect to it. After some timeouts, both agree that node A is really down and remove it “officially” from the cluster. Quorum is saved ( 2 out of 3 nodes are up), so no service disruption happens. After restarting, A will join automatically the same way as in scenario 1.

Scenario 5

Nodes A and B disappear. The node C is not able to form the quorum alone, so the cluster is switching into a non-primary mode, in which MySQL refuses to serve any SQL query. In this state, mysqld process on C will be still running, you can connect to it, but any statement related to data fails with:

mysql> select * from test.t1; ERROR 1047 (08S01): Unknown command

Actually reads will be possible for a moment until C decides that it cannot reach A and B, but immediately no new writes will be allowed thanks to the certification based replication in Galera. This is what we are going to see in the remaining node’s log:

140814 0:42:13 [Note] WSREP: commit failed for reason: 3 140814 0:42:13 [Note] WSREP: conflict state: 0 140814 0:42:13 [Note] WSREP: cluster conflict due to certification failure for threads: 140814 0:42:13 [Note] WSREP: Victim thread: THD: 7, mode: local, state: executing, conflict: cert failure, seqno: -1 SQL: insert into t values (1)

The single node C is then waiting for it’s peers to show up again, and in some cases if that happens, like when there was network outage and those nodes were up all the time, the cluster will be formed again automatically. Also if the nodes B and C were just network-severed from the first node, but they can still reach each other, they will keep functioning as they still form the quorum. If A and B were crashed ( due to data inconsistency, bug, etc. ) or off due to power outage, you need to do manual action to enable primary component on the C node, before you can bring A and B back. This way, we tell the C node “Hey, you can now form a new cluster alone, forget A and B!”. The command to do this is:

SET GLOBAL wsrep_provider_options='pc.bootstrap=true';

However, you should double check in order to be very sure the other nodes are really down before doing that! Otherwise, you will most likely end up with two clusters having different data.

Scenario 6

All nodes went down without proper shutdown procedure. Such situation may happen in case of datacenter power failure, hitting some MySQL or Galera bug leading to crash on all nodes, but also as a result of data consistency being compromised where cluster detects that each node has different data. In each of those cases, the grastate.dat file is not updated and does not contain valid sequence number (seqno). It may look like this:

cat /var/lib/mysql/grastate.dat # GALERA saved state version: 2.1 uuid: 220dcdcb-1629-11e4-add3-aec059ad3734 seqno: -1 cert_index:

In this case, we are not sure if all nodes were consistent with each other, hence it is crucial to find the most advanced one in order to boostrap the cluster using it. Before starting mysql daemon on any node, you have to extract the last sequence number by checking it’s transactional state. You can do it this way:

[root@percona3 ~]# mysqld_safe --wsrep-recover 140821 15:57:15 mysqld_safe Logging to '/var/lib/mysql/percona3_error.log'. 140821 15:57:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140821 15:57:15 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.6bUIqM' --pid-file='/var/lib/mysql/percona3-recover.pid' 140821 15:57:17 mysqld_safe WSREP: Recovered position 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:2 140821 15:57:19 mysqld_safe mysqld from pid file /var/lib/mysql/percona3.pid ended

So the last committed transaction sequence number on this node was 2. Now you just need to bootstrap from the latest node first and then start the others.

However, the above procedure won’t be needed in the recent Galera versions (3.6+?), available since PXC 5.6.19. There is a new option – pc.recovery (enabled by default), which saves the cluster state into a file named gvwstate.dat on each member node. As the variable name says (pc – primary component), it saves only a cluster being in PRIMARY state. An example content of that file may look like this:

cat /var/lib/mysql/gvwstate.dat my_uuid: 76de8ad9-2aac-11e4-8089-d27fd06893b9 #vwbeg view_id: 3 6c821ecc-2aac-11e4-85a5-56fe513c651f 3 bootstrap: 0 member: 6c821ecc-2aac-11e4-85a5-56fe513c651f 0 member: 6d80ec1b-2aac-11e4-8d1e-b2b2f6caf018 0 member: 76de8ad9-2aac-11e4-8089-d27fd06893b9 0 #vwend

We can see three node cluster above with all members being up. Thanks to this new feature, in the case of power outage in our datacenter, after power is back, the nodes will read the last state on startup and will try to restore primary component once all the members again start to see each other. This makes the PXC cluster to automatically recover from being powered down without any manual intervention! In the logs we will see:

140823 15:28:55 [Note] WSREP: restore pc from disk successfully (...) 140823 15:29:59 [Note] WSREP: declaring 6c821ecc at tcp://192.168.90.3:4567 stable 140823 15:29:59 [Note] WSREP: declaring 6d80ec1b at tcp://192.168.90.4:4567 stable 140823 15:29:59 [Warning] WSREP: no nodes coming from prim view, prim not possible 140823 15:29:59 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3 140823 15:29:59 [Note] WSREP: Flow-control interval: [28, 28] 140823 15:29:59 [Note] WSREP: Received NON-PRIMARY. 140823 15:29:59 [Note] WSREP: New cluster view: global state: 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:11, view# -1: non-Primary, number of nodes: 3, my index: 2, protocol version -1 140823 15:29:59 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 140823 15:29:59 [Note] WSREP: promote to primary component 140823 15:29:59 [Note] WSREP: save pc into disk 140823 15:29:59 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = yes, my_idx = 2, memb_num = 3 140823 15:29:59 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID. 140823 15:29:59 [Note] WSREP: clear restored view (...) 140823 15:29:59 [Note] WSREP: Bootstrapped primary 00000000-0000-0000-0000-000000000000 found: 3. 140823 15:29:59 [Note] WSREP: Quorum results: version = 3, component = PRIMARY, conf_id = -1, members = 3/3 (joined/total), act_id = 11, last_appl. = -1, protocols = 0/6/2 (gcs/repl/appl), group UUID = 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a 140823 15:29:59 [Note] WSREP: Flow-control interval: [28, 28] 140823 15:29:59 [Note] WSREP: Restored state OPEN -> JOINED (11) 140823 15:29:59 [Note] WSREP: New cluster view: global state: 4b83bbe6-28bb-11e4-a885-4fc539d5eb6a:11, view# 0: Primary, number of nodes: 3, my index: 2, protocol version 2 140823 15:29:59 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 140823 15:29:59 [Note] WSREP: REPL Protocols: 6 (3, 2) 140823 15:29:59 [Note] WSREP: Service thread queue flushed. 140823 15:29:59 [Note] WSREP: Assign initial position for certification: 11, protocol version: 3 140823 15:29:59 [Note] WSREP: Service thread queue flushed. 140823 15:29:59 [Note] WSREP: Member 1.0 (percona3) synced with group. 140823 15:29:59 [Note] WSREP: Member 2.0 (percona1) synced with group. 140823 15:29:59 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 11) 140823 15:29:59 [Note] WSREP: Synchronized with group, ready for connections

Scenario 7

Cluster lost it’s primary state due to split brain situation. For the purpose of this example, let’s assume we have the cluster formed from even number of nodes – six and three of them are in one location while another three in second location (datacenter) and network connectivity is broken between them. Of course the best practice is to avoid such topology: if you can’t have odd number of real nodes, at least you can use an additional arbitrator (garbd) node or set higher pc.weight to some nodes. But when split brain happens any way, so none of the separated groups can maintain the quorum – all nodes must stop serving requests and both parts of the cluster are just continuously trying to re-connect. If you want to restore the service even before the network link is restored, you can make one of the groups primary again using the same command like in scenario 5:

SET GLOBAL wsrep_provider_options='pc.bootstrap=true';

After that, you are able to work on the manually restored part of the cluster, and the second half should be able to automatically re-join using incremental state transfer (IST) once the network link is restored. But beware: if you set the bootstrap option on both the separated parts, you will end up with two living cluster instances, with data likely diverging away from each other. Restoring network link in that case won’t make them to re-join until nodes are restarted and try to re-connect to members specified in configuration file. Then, as Galera replication model truly cares about data consistency – once the inconsistency will be detected, nodes that cannot execute row change statement due to different data – will perform emergency shutdown and the only way to bring them back to the cluster will be via full SST.

I hope I covered most of the possible failure scenarios of Galera-based clusters, and made the recovery procedures bit more clear.

2015: 06/12
CATEGORY: Scaling
Security
Write comment

5 subtle ways you’re using MySQL as a queue, and why it’ll bite you

Note: This guest post is from our friends at Percona about using MySQL as a job queue.

I work for Percona, a MySQL consulting company. To augment my memory, I keep a quick-reference text file with notes on interesting issues that customers ask us to solve. One of the categories of frequent problems is attempts to build a job queue in MySQL. I have so many URLs under this bullet point that I stopped keeping track anymore. Customers have endless problems with job queues in their databases. By “job queue” I simply mean some list of things they’ve inserted, which usually need to be processed and marked as completed. I’ve seen scores — maybe hundreds — of cases like this.

Many people realize the difficulties in building a good job queue or batch processing system, and try not to create one inside MySQL. Although the job queue is a great design pattern from the developer’s point of view, they know it’s often hard to implement well in a relational database. However, experience shows me that job queues sneak up in unexpected ways, even if you’re a seasoned developer.

Here are some of the most common ways I’ve seen the job-queue design pattern creep into an application’s database. Are you using MySQL for any of the following?

Storing a list of messages to send: whether it’s emails, SMS messages, or friend requests, if you’re storing a list of messages in a table and then looking through the list for messages that need to be sent, you’ve created a job queue.
Moderation, token claims, or approval: do you have a list of pending articles, comments, posts, email validations, or users? If so, you have a job queue.
Order processing: If your order-processing system looks for newly submitted orders, processes them, and updates their status, it’s a job queue.
Updating a remote service: does your ad-management software compute bid changes for ads, and then store them for some other process to communicate with the advertising service? That’s a job queue.
Incremental refresh or synchronization: if you store a list of items that has changed and needs some background processing, such as files to sync for your new file-sharing service, well, by now you know what that is.

As you can see, queues are sly; they slip into your design without you realizing it. Frankly, many of them aren’t really a problem in reality. But the potential is always there, and I’ve observed that it’s hard to predict which things will become problems. This is usually because it depends on behavior that you don’t know in advance, such as which parts of your application will get the most load, or what your users will promote to their friends.

Let’s dive a little bit into why job queues can cause trouble, and then I’ll show you some ways to help reduce the chance it’ll happen to you. The problem is usually very simple: performance. As time passes, the job queue table starts to either perform poorly, or cause other things to perform badly through collateral damage. There are three primary reasons for this:

Polling. Many of the job queue systems I see have one or more worker processes checking for something to do. This starts to become a problem pretty quickly in a heavily loaded application, for reasons I’m about to explain.
Locking. The specific implementation of the polling often looks like this: run a SELECT FOR UPDATE to see if there are items to process; if so, UPDATE them in some way to mark them as in-process; then process them and mark them as complete. There are variations on this, not necessarily involving SELECT FOR UPDATE, but often something with similar effects. The problem with SELECT FOR UPDATE is that it usually creates a single synchronization point for all of the worker processes, and you see a lot of processes waiting for the locks to be released with COMMIT. Bad implementations of this (not committing until the workers have processed the items, for example) are really horrible, but even “good” implementations can cause serious pile-ups.
Data growth. I can’t tell you how many times I’ve seen email list management applications that have a single huge emails table. New emails go into the table with a “new” status, and then they get updated to mark them as sent. As time passes, these email tables can grow into millions or even billions of rows. Even though there might only be hundreds to thousands of new messages to send, that big bloated table makes all the queries really, really slow. If you combine this with polling and/or locking and lots of load on the server, you have a recipe for epic disaster.

The solutions to these problems are actually rather simple: 1) avoid polling; 2) avoid locking; and 3) avoid storing your queue in the same table as other data. Implementing these solutions can take a bit of creativity, however.

First, let’s look at how to avoid polling. I wish that MySQL had listen/notify functionality, the way that PostgreSQL and Microsoft SQL Server do (just to mention two). Alas, MySQL doesn’t, but you can simulate it. Here are three ideas: use GET_LOCK() and RELEASE_LOCK(), or write a plugin to communicate through Spread, or make the consumers run a SLEEP(100000) query, and then kill these queries to “signal” to the worker that there’s something to do. These can work quite well, although it’d be nice to have a more straightforward solution.

Locking is actually quite easy to avoid. Instead of SELECT FOR UPDATE followed by UPDATE, just UPDATE with a LIMIT, and then see if any rows were affected. The client protocol tells you that; there’s no need for another query to the database to check. Make sure autocommit is enabled for this UPDATE, so that you don’t hold the resultant locks open for longer than the duration of the statement. If you don’t have autocommit enabled, the application must follow up with a COMMIT to release any locks, and that is really no different from SELECT FOR UPDATE. (The rest of the work can be done with autocommit disabled; you need to enable it for only this statement.) While I’m wishing for things, I wish that SELECT FOR UPDATE had never been invented. I haven’t seen a case yet where it can’t be done a better way, nor have I seen a case where it has failed to cause problems

Finally, it’s also really easy to avoid the one-big-table syndrome. Just create a separate table for new emails, and when you’re done processing them, INSERT them into long-term storage and then DELETE them from the queue table. The table of new emails will typically stay very small and operations on it will be fast. And if you do the INSERT before the DELETE, and use INSERT IGNORE or REPLACE, you don’t even need to worry about using a transaction across the two tables, in case your app crashes between. That further reduces locking and other overhead. If you fail to execute the DELETE, you can just have a regular cleanup task retry and purge the orphaned row. (Hmm, sounds like another job queue, no?) You can do much the same thing for any type of queue. For example, articles or comments that are pending approval can go into a separate table. This is really required on a large scale, although you shouldn’t worry that your WordPress blog doesn’t do things this way (unless you’ve been hired to rewrite CNN.com using WordPress as a backend).

Finally, and I’ve saved perhaps the most obvious solution for last, don’t use the database at all! Use a real queueing system, such as Resque, ActiveMQ, RabbitMQ, or Gearman. Be careful, however, that you don’t enable persistence to a database and choose to use MySQL for that. Depending on the queue system, that can just reintroduce the problem in a generic way that’s even less optimal. Some queue systems use all of the database worst practices I enumerated above.

I hope this article has given you some insight into the variety of ways that job queues inside of MySQL can sneak up on you and bite you in the tendermost parts. And I hope you can learn to recognize and avoid this design pattern yourself, or at least implement it in a way that won’t hurt you. It really is such a common problem that it’s become one of the classic questions I see. Now, I’m off to check my list of pending consulting requests and see what I should work on next.

via 5 subtle ways you’re using MySQL as a queue, and why it’ll bite you.