Galera: runtime adjustment of applier threads

In a MariaDB Galera node, writesets received from other node(s) can be applied parallely through multiple applier threads. The number of slave applier threads is controlled by server’s wsrep_slave_threads system variable. Its a dynamic variable and thus the number of slave applier threads can be adjusted in runtime. The current number of slave applier threads can be checked through “SHOW PROCESSLIST” or wsrep_thread_count status variable (MDEV-6206).

One interesting point to note here is that when the number of @@global.wsrep_slave_threads is increased at runtime, the additional requested applier threads gets spawned immediately. However, when the number is decreased, the effect can not be noticed immediately. What happens internally is that when the number is decreased, the extra applier threads are not killed right away. The process is deferred, and the extra threads exit gracefully only after each apply one last writeset (transaction) after receiving it. So, one will not notice the number of applier threads decreasing on an idle node. The thread count will decrease only after the node starts receiving writesets to apply.

Here are some snippets to help you understand this further :

1. Calculate the change.

  wsrep_slave_count_change += (var->value->val_int() - wsrep_slave_threads);

2a: If its positive, spawn new applier threads.

  if (wsrep_slave_count_change > 0)
    wsrep_slave_count_change = 0;

2b: else mark the thread as “done” after it has applied (commits or rollbacks) the given last writeset.

wsrep_cb_status_t wsrep_commit_cb(void*         const     ctx,
                                  uint32_t      const     flags,
                                  const wsrep_trx_meta_t* meta,
                                  wsrep_bool_t* const     exit,
                                  bool          const     commit)
  if (commit)
    rcode = wsrep_commit(thd, meta->gtid.seqno);
    rcode = wsrep_rollback(thd, meta->gtid.seqno);


  if (wsrep_slave_count_change < 0 && commit && WSREP_CB_SUCCESS == rcode)
    if (wsrep_slave_count_change < 0)
      *exit = true;

And the thread exits :

static void wsrep_replication_process(THD *thd)
  rcode = wsrep->recv(wsrep, (void *)thd);
  DBUG_PRINT("wsrep",("wsrep_repl returned: %d", rcode));

  WSREP_INFO("applier thread exiting (code:%d)", rcode);

APT: How to prefer local packages over remote?

I recently had to test some of my locally generated MariaDB debian packages. So, created a local repository of generated packages and added it to sources.list file. However, since these packages required some other packages stored on a mirror, I had to add the mirror to the sources.list file as well. Since, this mirror also contained the packages that I intended to test, now when I try to install the packages, APT would always pick/prefer the ones stored on the mirror. How to solve this? How to make APT prefer the local packages instead? Lets start by taking a peek into the sources.list file:

$cat /etc/apt/sources.list
# remote repo
deb precise main

# local repo
deb file:///home/nirbhay/project/repo/maria/testing/debian/5.5/debs binary/

$ sudo apt-cache policy mariadb-galera-server
  Installed: (none)
  Candidate: 5.5.37+maria-1~precise
  Version table:
     5.5.37+maria-1~precise 0
        500 file:/home/nirbhay/project/repo/maria/testing/debian/5.5/debs/ binary/ Packages
        500 precise/main i386 Packages

The following tips can help you fix this problem :

  1. If remote and local packages have the same version (as in my case), place one that you want to be preferred higher in sources.list file.

  2. APT prefers authenticated repository over unauthenticated. So, as against the above case, even if the local repository is placed over the remote, APT will prefer remote one if its authenticated and the local repository is not. In that case, –allow-unauthenticated can be used to make local packages take precedence.

  3. In case local and remote packages have different versions, APT would always prefer the package with higher version. However, this rule can be eased by apt-pinning, where a higher priority is assigned to a particular repository. For example, local repository can be pinned with higher priority (or precedence in APT’s context) by adding a file under /etc/apt/preferences.d with .pref extension and the following content :

    Package: *
    Pin: origin ""
    Pin-Priority: 1000

    This has been explained really well in this thread.

Lastly, do not forget to run “apt-get update” for changes to take effect.

Generating SSH key pair

SSH key pair is a set of private/public keys normally used in securing network communication. These keys are normally required for passwordless SSH login to a remote host running SSH daemin (sshd). Here is how you would generate a pair RSA keys:

$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/nirbhay/.ssh/id_rsa):
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/nirbhay/.ssh/id_rsa.
Your public key has been saved in /home/nirbhay/.ssh/
The key fingerprint is:
5f:1a:b5:50:a8:b6:d6:2b:48:1b:b6:df:4c:54:a2:28 nirbhay@nirbhay-VirtualBox
The key's randomart image is:
+--[ RSA 2048]----+
|           ..    |
|          ..     |
|         .o o    |
|       .o. = .   |
|       ...o      |
$ ls ~/.ssh/

Now that we have the private/public key files, copy the public key ( to the remote machines for which you need the SSH access. DO NOT share the “private key”.

Note: On debian-based distributions, ssh-keygen is provided by openssh-client package.

Generating SSL certificates

** The (self-signed) SSL certificate generated by the procedure mentioned in this article should be used for testing purpose only **

Generating an SSL certificate is very simple. All you need is openssl package installed on your system. A key point to note here is that SSL certificates contain public key, which is always generated in pair with a private key. Here is the step-by-step procedure to generate one:

  1. Private key
    Lets first generate a 2048-bit RSA private key.

    openssl genrsa -out privkey.pem 2048

    $ openssl genrsa -out privkey.pem 2048
    Generating RSA private key, 2048 bit long modulus
    unable to write 'random state'
    e is 65537 (0x10001)
    $ ls

    So, we have the private key in place. This will be used to generate the certificate.

    If “unable to write ‘random state’” bothers you, then check this out for a possible solution:

  2. The self-signed certificate
    A certificate can now be generated using the following command.

    openssl req -new -x509 -key privkey.pem -out cacert.pem -days 1095

    $ openssl req -new -x509 -key privkey.pem -out cacert.pem -days 1095
    You are about to be asked to enter information that will be incorporated
    into your certificate request.
    What you are about to enter is what is called a Distinguished Name or a DN.
    There are quite a few fields but you can leave some blank
    For some fields there will be a default value,
    If you enter '.', the field will be left blank.
    Country Name (2 letter code) [AU]:XX
    State or Province Name (full name) [Some-State]:XX
    Locality Name (eg, city) []:XX
    Organization Name (eg, company) [Internet Widgits Pty Ltd]:XX
    Organizational Unit Name (eg, section) []:XX
    Common Name (e.g. server FQDN or YOUR name) []:XX
    Email Address []:XX
    $ ls
    cacert.pem  privkey.pem

These files can easily be tested by starting a test SSL/TLS server (s_server(1) and connecting to it using a client (s_client(1)).

$ openssl s_server -port <port> -cert /path/to/cacert.pem -key /path/to/privkey.pem

$ openssl s_client -host <server-host> -port <server-port> -key /path/to/privkey.pem

Reference :
Thats all!

Go projects in MySQL/MariaDB ecosystem

Please let me know if you come across a MySQL/MariaDB Go project not listed here.

Galera: Unknown command

Here is the scenario: You connect to one of the nodes in a MariaDB/MySQL Galera cluster, execute a valid command and the node responds with “Unknown command” error. Strange! After all, you executed a valid SQL command. What’s going on? I will try to demystify it in this article.

ERROR 1047 (08S01): Unknown command

In a “split-brain” situation, due to network partitioning for instance, there is a higher risk of data becoming inconsistent across various nodes in the cluster. So, in order to avoid this, Galera nodes in minority start rejecting incoming commands** until this issue has been resolved – and thus, it returns “Unknown command” error to the client. Its a reasonable decision that helps preserve data consistency in the cluster. Besides split-brain, there can be other situations when a node is not ready or fully prepared and thus would reject commands with the same error.

** SHOW and SET commands are not rejected.

EDIT: The error message has been corrected recently in MariaDB Galera Cluster (MDEV-6171) :

ERROR 1047 (08S01): WSREP has not yet prepared node for application use

enum types in Go?

Well, there is none! So, lets say one needs to implement an API with a parameter that accepts only a limited set of values. So that its invocation with invalid values can be detected at compile time. In C/C++, this could have been accomplished by defining an enum type of all valid entities and using the same enum type to declare the formal parameter. So, how to achieve this in Go? There is a pretty simple way : define a new “unexported” type and “exported” constants of this new type for all valid values. The “type” is not exported to forbid the API users from defining or adding new values and use of exported constants is thus enforced.

Here is a simple example to demonstrate this:

package main

import "fmt"

type myColor string

const (
        RED   myColor = "red"
        GREEN myColor = "green"
        BLUE  myColor = "blue"

func PrintColor(color myColor) {

func main() {

        // Compile error : undefined: BLACK
        // PrintColor(BLACK)

        // Compile error : cannot use 20 (type int) as type myColor in function argument
        // PrintColor(20)

A good example:

A note on Galera versioning

The knowledge of Galera versioning comes in handy when you are working with MariaDB Galera cluster. Galera libraries are versioned using the following format : xx.yy.zz (e.g. 25.3.2), where :

xx : WSREP Interface Version
yy : Galera library major version
zz : Galera library minor version

However, the library itself is often referred by its major and minor version only (galera-3.2, for instance). While the last two numbers (yy.zz) describe the set of features/bug fixes, the first one (xx) gives the WSREP interface version of the Galera library.

A MariaDB Galera cluster node consists of two main components :

  1. MariaDB Galera server
  2. Galera library

The Galera library (aka the WSREP provider) plugs into MariaDB Galera server to provide support for writeset replication. In order to successfully plug in, the WSREP interface version of galera library must match with that provided by MariaDB Galera Server. The WSREP interface version of MariaDB Galera Server can be found using version_comment system variable.

MariaDB [test]> select @@version_comment;
| @@version_comment                     |
| Source distribution, wsrep_25.9.r3961 |

So, what happens when interface versions of MariaDB Galera Server and Galera library are different? The Galera plugin would fail to load :

140311 14:14:02 [Note] WSREP: Read nil XID from storage engines, skipping position init
140311 14:14:02 [Note] WSREP: wsrep_load(): loading provider library '/home/packages/galera-23.2.7-src/'
140311 14:14:03 [ERROR] WSREP: provider interface version mismatch: need '25', found '23'
140311 14:14:03 [ERROR] WSREP: wsrep_load(): interface version mismatch: my version 25, provider version 23
140311 14:14:03 [ERROR] WSREP: wsrep_load(/home/packages/galera-23.2.7-src/ failed: Invalid argument (22). Reverting to no provider.
140311 14:14:03 [Note] WSREP: Read nil XID from storage engines, skipping position init
140311 14:14:03 [Note] WSREP: wsrep_load(): loading provider library 'none'
140311 14:14:03 [ERROR] Aborting

Lastly, it is interesting to note that a Galera library can be loaded into the MariaDB Galera Server regardless of its version (2.XX or 3.XX) as long as their WSREP interface versions are same.