Buy Percona ServicesBuy Now!
Subscribe to Latest MySQL Performance Blog posts feed
Updated: 1 hour 53 min ago

Percona Live 2017 Tutorials Day

April 24, 2017 - 10:38pm

Welcome to the first day of the Percona Live Open Source Database Conference: Percona Live 2017 tutorials day! While technically the first day of the conference, this day focused on provided hands-on tutorials for people interested in learning directly how to use open source tools and technologies.

Today attendees went to training sessions taught by open source database experts and got first-hand experience configuring, working with, and experimenting with various open source technologies and software.

The first full day (which includes opening keynote speakers and breakout sessions) starts Tuesday 4/25 at 9:00 am.

Some of the tutorial topics covered today were:

MySQL Performance Schema in Action

Sveta Smirnova, Alexander Rubin

Performance Schema in MySQL is becoming more mature from version to version. In version 5.7, it includes extended lock instrumentation, memory usage statistics, new tables for server variables, first time ever instrumentation for user variables, prepared statements and stored routines. In this tutorial Sveta and Alexander helped the participants try these new instruments out. They provided a test environment and a few typical problems that were hard to solve before MySQL 5.7.

Just a few examples: “Where is memory going?” , “Why do these queries hang?”, “How huge is the overhead of my stored procedures?”, “Why are queries waiting for metadata locks?”. Attendees learned how to collect and use this information.

Best Practices for MySQL High Availability in 2017

Colin Charles

The MySQL world is full of tradeoffs, and choosing a high availability (HA) solution is no exception. This session aims to look at all of the alternatives in an unbiased nature. Topics covered included but weren’t limited to MySQL replication, MHA, DRBD, Tungsten Replicator, Galera Cluster, NDB Cluster, Vitess, etc. The focus of the talk was what is recommended for today, and what to look out for.

InnoDB Architecture and Performance Optimization

Peter Zaitsev

InnoDB is the most commonly used storage engine for MySQL and Percona Server and is the focus for the majority of storage engine development by the MySQL and Percona Server teams. This tutorial looked at the InnoDB architecture, including new developments in MySQL 5.6 as well as Percona Server. It provided specific advice on server configuration, schema design, application architecture, and hardware choices.

MongoDB 101: What NoSQL is All About

Jon Tobin, Rick Golba, Barrett Chambers

MongoDB is quickly becoming one of the NoSQL standards, but represents a very different way of thinking from traditional RDBMSs. Many database users tend to think of things from the perspective of the transactional DBs that they know and love, but there are other ways of doing things. The Percona Solutions Engineering team helped attendees fill out their database resume and become a more knowledgeable user by showing them the basics. This tutorial gave users with little or no experience with NoSQL databases an introduction to MongoDB.

Join us tomorrow for the first full day of the Percona Live Open Source Database Conference 2017!

Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster

April 24, 2017 - 2:22pm

In this blog post, we’ll look at how we’ve improved wsrep-stages and related instrumentation in Percona XtraDB Cluster.


When you execute a workload and need to find out what the given thread is working on, “SHOW PROCESSLIST” comes to the top of your mind. It is an effective way to track the thread status. We decided to improve the stages in Percona XtraDB Cluster to make “SHOW PROCESSLIST” more meaningful.

In the blog below, we will check out the different wsrep-stages and the significance associated with them.

Loading of data

Running a simple insert/sysbench prepare workload. The state is stable as it mainly captures MySQL stages indicating that the table is being updated:

| 9 | root | localhost | test | Query | 0 | update | INSERT INTO sbtest3(id, k, c, pad) VALUES(893929,515608,'28459951974-62599818307-78562787160-7859397 | 0 | 0 |

Running UPDATE workload

Running simple sysbench update-key workload. Let’s look at the different states that the user sees and their significance. (MASTER and SLAVE states are different and are marked accordingly.)

MASTER view:

  • This stage indicates that the write-set is trying to replicate. Global sequence numbers are assigned after the write-set is replicated and so the global-seqno is currently -1:

| 80 | root | localhost | test | Query | 0 | wsrep: initiating replication for write set (-1) | UPDATE sbtest4 SET k=k+1 WHERE id=502338 | 0 | 1 |

  • This stage indicates successful replication of the write-set. This means the write-set is now added to the group-channel. Global-seqno is updated in the message too:

| 79 | root | localhost | test | Query | 0 | wsrep: write set replicated (196575) | UPDATE sbtest3 SET k=k+1 WHERE id=502723 | 0 | 1 |

  • This stage indicates the write-set has successfully passed the certification stage (making its path clear for commit):

| 85 | root | localhost | test | Query | 0 | wsrep: pre-commit/certification passed (196574) | UPDATE sbtest7 SET k=k+1 WHERE id=495551 | 0 | 1 |

  • This stage indicates that InnoDB commit has been triggered for the write-set:

| 138 | root | localhost | test | Query | 0 | innobase_commit_low (585721) | UPDATE sbtest6 SET k=k+1 WHERE id=500551 | 0 | 1 |

SLAVE/Replicating node view:

  • This stage indicates that the slave thread is trying to commit the replicated write-set with the given seqno. It is likely waiting for its turn of the CommitMonitor:

|  6 | system user |           | NULL | Sleep   |    0 | wsrep: committing write set (224905) | NULL             |         0 |             0 |

  • This stage indicates a successful commit of the replicated write-set with the given seqno:

| 2 | system user | | NULL | Sleep | 0 | wsrep: committed write set (224896) | NULL | 0 | 0 |

  • This stage indicates that updating the rows is in progress. (Often it was difficult to know what the workload is trying to do: UPDATE/INSERT/DELETE.) Now there is an easy way to find out:

| 13 | system user |           | NULL | Sleep   |    0 | wsrep: updating row for write-set (178711) | NULL             |         0 |             0 |

| 18 | system user | | NULL | Sleep | 0 | wsrep: writing row for write-set (793454) | NULL | 0 | 0 |

| 11 | system user | | NULL | Sleep | 0 | wsrep: deleting row for write-set (842123) | NULL | 0 | 0 |

  • This stage indicates that the given write-set is being applied:

| 10 | system user | | NULL | Sleep | 0 | wsrep: applying write-set (899370) | NULL | 0 | 0 |

Improved Instrumentation

Let’s answer some simple questions that most profiling experts will face:

  • How long did replication take (adding write-set to channel)?

mysql> select event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%in replicate%' order by time_in_mics desc limit 5; +---------------------------------------+--------------+ | event_name | time_in_mics | +---------------------------------------+--------------+ | stage/wsrep/wsrep: in replicate stage | 1.2020 | | stage/wsrep/wsrep: in replicate stage | 0.7880 | | stage/wsrep/wsrep: in replicate stage | 0.7740 | | stage/wsrep/wsrep: in replicate stage | 0.7550 | | stage/wsrep/wsrep: in replicate stage | 0.7480 | +---------------------------------------+--------------+ 5 rows in set (0.01 sec)

  • How long did it take for pre-commit/certification checks?

mysql> select event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%in pre-commit%' order by time_in_mics desc limit 5; +----------------------------------------+--------------+ | event_name | time_in_mics | +----------------------------------------+--------------+ | stage/wsrep/wsrep: in pre-commit stage | 1.3450 | | stage/wsrep/wsrep: in pre-commit stage | 1.0000 | | stage/wsrep/wsrep: in pre-commit stage | 0.9480 | | stage/wsrep/wsrep: in pre-commit stage | 0.9180 | | stage/wsrep/wsrep: in pre-commit stage | 0.9030 | +----------------------------------------+--------------+ 5 rows in set (0.01 sec)

  • How long did it take to commit a transaction on the slave (slave_thread=16 threads)?

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%committing%' order by time_in_mics desc limit 5; +-----------+-------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+-------------------------------+--------------+ | 56 | stage/wsrep/wsrep: committing | 0.5870 | | 58 | stage/wsrep/wsrep: committing | 0.5860 | | 47 | stage/wsrep/wsrep: committing | 0.5810 | | 59 | stage/wsrep/wsrep: committing | 0.5740 | | 60 | stage/wsrep/wsrep: committing | 0.5220 | +-----------+-------------------------------+--------------+ 5 rows in set (0.00 sec)

  • Increasing the number of slave thread creates more contention (slave_thread=64):

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%committing%' order by time_in_mics desc limit 5; +-----------+-------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+-------------------------------+--------------+ | 90 | stage/wsrep/wsrep: committing | 1.6930 | | 97 | stage/wsrep/wsrep: committing | 1.5870 | | 103 | stage/wsrep/wsrep: committing | 1.5140 | | 87 | stage/wsrep/wsrep: committing | 1.2560 | | 102 | stage/wsrep/wsrep: committing | 1.1040 | +-----------+-------------------------------+--------------+ 5 rows in set (0.00 sec)

  • The amount oftTime taken to apply a write-set:

mysql> select thread_id, event_name, timer_wait/1000000 as time_in_mics from events_stages_history where event_name like '%applying%' order by time_in_mics desc limit 5; +-----------+---------------------------------------+--------------+ | thread_id | event_name | time_in_mics | +-----------+---------------------------------------+--------------+ | 166 | stage/wsrep/wsrep: applying write set | 1.6880 | | 168 | stage/wsrep/wsrep: applying write set | 1.5820 | | 146 | stage/wsrep/wsrep: applying write set | 1.5270 | | 124 | stage/wsrep/wsrep: applying write set | 1.4760 | | 120 | stage/wsrep/wsrep: applying write set | 1.4440 | +-----------+---------------------------------------+--------------+ 5 rows in set (0.00 sec)


The improved wsrep-stage framework makes it more effective for a user to find out the state of a given thread. Using the derived instrumentation through wsrep-stage is a good way to understand where the time is being spent.

Percona XtraDB Cluster: SST tmpdir option

April 24, 2017 - 1:46pm

In this blog post, I’ll discuss some changes to the behavior of the Percona XtraDB Cluster SST tmpdir option in the latest versions of Percona XtraDB Cluster 5.6.35-26.20-3 and 5.7.17-29.30.

Previously, we did not use the path specified by the tmpdir. From Percona XtraDB Cluster 5.6.35-26.20-3 and 5.7.17-29.20, we use the tmpdir option to specify the location of temporary files used by the SST (on the DONOR this may be very large since the backup logs may be stored here).

Specifying the tmpdir is as expected. You supply the directory path, as in the following example:

[sst] tmpdir=/path/to/tmp/dir/

We look for the tmpdir in the [sst], [xtrabackup], and [mysqld] sections, in that order. If left unspecified, “mktemp” follows the default behavior (on Linux distributions this will typically create the directory in $TMPDIR, followed by /tmp).

Normal security settings and permissions apply here. The directory must already exist and be readable and writable by MySQL otherwise a fatal error will be generated by the SST script.

Percona XtraDB Cluster: “dh key too small” error during an SST using SSL

April 23, 2017 - 9:16am

If you’ve tried to use SSL in Percona XtraDB Cluster and saw an error in the logs like SSL3_CHECK_CERT_AND_ALGORITHM:dh key too small, we’ve implemented some changes in Percona XtraDB Cluster 5.6.34 and 5.7.16 that get rid of these errors.

Some background

dh key too small refers to the Diffie-Hellman parameters used by the SSL code that are shorter than recommended.

Due to the Logjam vulnerability (, the required key-lengths for the Diffie-Hellman parameters were changed from 512 bits to 2048 bits. Unfortunately, older versions of OpenSSL/socat still use 512 bits (and thus caused the error to appear).

Changes made to Percona XtraDB Cluster

Since versions of socat greater than 1.7.3 now use 2048 bits for the Diffie-Hellman parameters, we only do extra work for the older versions of socat (less than 1.7.3). The SST code now:

  1. Looks for a file with the DH params
    1. Uses the “ssl_dhparams” option in the [sst] section if it exists
    2. Looks for a “dhparams.pem” file in the datadir
  2. If the file is specified and exists, uses that file as a source for the DH parameters
  3. If the file does not exist, creates a dhparams.pem file in the datadir
Generating the dhparams yourself

Unfortunately, the time it can take several minutes to create the dhparams file. We recommend that the dhparams.pem be created prior to starting the SST.

openssl dhparam -out path/to/datadir/dhparams.pem 2048

Percona XtraDB Cluster Transaction Replay Anomaly

April 23, 2017 - 9:05am

In this blog post, we’ll look at a transaction replay anomaly in Percona XtraDB Cluster.


Percona XtraDB Cluster/Galera replays a transaction if the data is non-conflicting but, the transaction happens to have conflicting locks.


Let’s understand this with an example:

  • Let’s assume a two-node cluster (node-1 and node-2)
  • Base table “t” is created as follows:

create database test; use test; create table t (i int, c char(20), primary key pk(i)) engine=innodb; insert into t values (1, 'abc'), (2, 'abc'), (4, 'abc'); select * from t; mysql> select * from t; +---+------+ | i | c | +---+------+ | 1 | abc | | 2 | abc | | 4 | abc | +---+------+

  • node-2 starts runs a transaction (trx-2):

trx-2: update t set c = 'pqr';

  • node-2 creates a write-set and is just about to replicate it. At the same time, node-1 executes the following transaction (trx-1), and is first to add it to the group-channel (before node-2 adds transaction (trx-2))

trx-1: insert into t values (3, 'a');

  • trx-1 is replicated on node-2, and it proceeds with the apply action. Since there is a lock conflict (no certification conflict), node-2 local transaction (trx-2) is aborted and scheduled for replay.
  • trx-1 causes addition of (3, ‘a’) and then node-2 transaction is REPLAYed.
  • REPLAY is done using the pre-created write-set that only modifies existing entries (1,2,4).


mysql> select * from t; +---+------+ | i | c | +---+------+ | 1 | pqr | | 2 | pqr | | 3 | a | | 4 | pqr | +---+------+

  • At first, nothing looks wrong. If you look closely, however, the REPLAYed transaction “UPDATE t set c= ‘pqr'” is last to commit. But the effect of it is not seen as there is still a row (3, ‘a’) that has ‘a’ instead of ‘pqr’.

| mysql-bin.000003 | 792 | Gtid | 2 | 857 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:4' | | mysql-bin.000003 | 857 | Query | 2 | 925 | BEGIN | | mysql-bin.000003 | 925 | Table_map | 2 | 972 | table_id: 219 (test.t) | | mysql-bin.000003 | 972 | Write_rows | 2 | 1014 | table_id: 219 flags: STMT_END_F existing| | mysql-bin.000003 | 1014 | Xid | 2 | 1045 | COMMIT /* xid=4 */ | | mysql-bin.000003 | 1045 | Gtid | 3 | 1110 | SET @@SESSION.GTID_NEXT= '6706fa1f-e3df-ee18-6621-c4e0bae533bd:5' | | mysql-bin.000003 | 1110 | Query | 3 | 1187 | BEGIN | | mysql-bin.000003 | 1187 | Table_map | 3 | 1234 | table_id: 219 (test.t) | | mysql-bin.000003 | 1234 | Update_rows | 3 | 1324 | table_id: 219 flags: STMT_END_F | | mysql-bin.000003 | 1324 | Xid | 3 | 1355 | COMMIT /* xid=5 */ | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------+ 21 rows in set (0.00 sec)

  • We have used a simple char string, but if there is a constraint here, like c should have X after UPDATE is complete, than the CONSTRAINT will be violated even though the application reports UPDATE as a success.
  • Is it interesting to note what happens on node-1:
    • node-1 applies the local transaction (trx-1) and then gets the replicated write-set from node-2 (trx-2) that has changes only for (1,2,4). Thereby data consistency is not compromised.

BEWARE: Increasing fc_limit can affect SELECT latency

April 22, 2017 - 7:22pm

In this blog post, we’ll look at how increasing the fc_limit can affect SELECT latency.


Recent Percona XtraDB Cluster optimizations have exposed fc_limit contention. It was always there, but was never exposed as the Commit Monitor contention was more significant. As it happens with any optimization, once we solve the bigger contention issues, smaller contention issues start popping up. We have seen this pattern in InnoDB, and Percona XtraDB Cluster is no exception. In fact, it is good because it tells us that we are on the right track.

If you haven’t yet checked the performance blogs, then please visit here and here.

What is FC_LIMIT?

Percona XtraDB Cluster has the concept of Flow Control. If any member of the cluster (not garbd) is unable to match the apply speed with the replicated write-set speed, then the queue builds up. If this queue crosses some threshold (dictated by gcs.fc_limit), then flow control kicks in. Flow control causes members of the cluster to temporary halt/slow-down so that the slower node can catch up.

The user can, of course, disable this by setting wsrep_desync=1 on the slower node, but make sure you understand the effect of doing so. Unless you have a good reason, you should avoid setting it.

mysql> show status like 'wsrep_flow_control_interval'; +-----------------------------+------------+ | Variable_name | Value | +-----------------------------+------------+ | wsrep_flow_control_interval | [ 16, 16 ] | +-----------------------------+------------+ 1 row in set (0.01 sec)

Increasing fc_limit

Until recently, the default fc_limit was 16 (starting with Percona XtraDB Cluster 5.7.17-29.20, the default is 100). This worked until now, since Percona XtraDB Cluster failed to scale and rarely hit the limit of 16. With new optimizations, Percona XtraDB Cluster nodes can process more write-sets in a given time period, and thereby can replicate more write-sets (anywhere in the range of three to ten times). Of course, the replicating/slave nodes are also performing at a higher speed. But depending on the slave threads, it is easy to start hitting this limit.

So what is the solution?

  • Increase fc_limit from 16 to something really big. Say 1600.

Is this correct?

YES and NO.

Why YES?

  • If you don’t care about the freshness of data on the replicated nodes, then increasing the limit to a higher value is not an issue. Say setting it to 10K means that the replicating node is holding 10K write-sets to replicate, and a SELECT fired during this time will not view changes from these 10K write-sets.
  • But if you insist on having fresh data, then Percona XtraDB Cluster has a solution for this (set wsrep_sync_wait=7).
  • Setting wsrep_sync_wait places the SELECT request in a queue that is serviced only after existing replicated write-sets (at the point when the SELECT was fired) are done with. If the queue has 8K write-sets, then SELECT is placed at the 8K+1 position. As the queue progresses, SELECT gets serviced only when all those 8K write-sets are done. This insanely increases SELECT latency and can cause all Monitoring ALARM to go ON.

Why NO?

  • For the reason mentioned above, we feel it is not a good idea to increase the fc_limit beyond some value unless you don’t care about data freshness and in turn don’t care to set wsrep_sync_wait.
  • We did a small experiment with the latest Percona XtraDB Cluster release to understand the effects.

- Started 2 node cluster. - Fired 64-threads workload on node-1 of the cluster. - node-2 is acting as replicating slave without any active workload. - Set wsrep_sync_wait=7 on node-2 to ensure data-freshness. Using default fc_limit (= 16) ----------------------------- mysql> select sum(k) from sbtest1 where id > 5000 and id < 50000; +-------------+ | sum(k) | +-------------+ | 22499552612 | +-------------+ 1 row in set (0.03 sec) Increasing it from 16 -> 1600 ----------------------------- mysql> set global wsrep_provider_options="gcs.fc_limit=1600"; Query OK, 0 rows affected (0.00 sec) mysql> select sum(k) from sbtest1 where id > 5000 and id < 50000; +-------------+ | sum(k) | +-------------+ | 22499552612 | +-------------+ 1 row in set (0.46 sec) That is whopping 15x increase in SELECT latency. Increasing it even further (1600 -> 25000) ------------------------------------------- mysql> set global wsrep_provider_options="gcs.fc_limit=25000"; Query OK, 0 rows affected (0.00 sec) mysql> select sum(k) from sbtest1 where id > 5000 and id < 50000; +-------------+ | sum(k) | +-------------+ | 22499552612 | +-------------+ 1 row in set (7.07 sec)

Note: wsrep_sync_wait=7 will enforce the check for all DMLs (INSERT/UPDATE/DELETE/SELECT). We highlighted the SELECT example, as that is more concerning at first go. But latency for other DMLs also increases for the same reasons as mentioned above.


Let’s conclude with the following observation:

  • Avoid increasing fc_limit to an insanely high value as it can affect SELECT latency (if you are running a SELECT session with wsrep_sync_wait=7 for data freshness).

Better Than Linear Scaling

April 22, 2017 - 3:54pm

In this blog, we’ll look at how to achieve better-than-linear scaling.

Scalability is the capability of a system, network or process to handle a growing amount of work, or its potential to be enlarged to accommodate that growth. For example, we consider a system scalable if it is capable of increasing its total output under an increased load when resources (typically hardware) are added:

It is often accepted as a fact that systems (in particular databases) can’t scale better than linearly. By this I mean when you double resources, the expected performance doubles, at best (and often is less than doubled).  

We can attribute this assumption to Amdahl’s law (, and later to the Universal Scalability Law ( Both these laws prescribe that it is impossible to achieve better than linear scalability. To be totally precise, this is practically correct for single server systems when the added resources are only CPU units.

Multi-nodes systems

However, I think databases systems no longer should be seen as single server systems. MongoDB and Cassandra for a long time have had multi-node auto-sharding capabilities. We are about to see the rise of strongly-consistent SQL based multi-node systems. And even MySQL is frequently deployed with manual sharding on multi-nodes.

The products like Vitess ( proposes auto-sharding for MySQL, and with ProxySQL (which I will use in my experiment) you can setup a basic sharding schema.

I describe multi-nodes setups, because in this environment it is possible to achieve much better than linear scalability. I will show this below.

Why is this important?

Understanding scalability of multi-node systems is important for resource planning, and understanding how much of a potential performance gain we can expect when we add more nodes. This is especially interesting for cloud deployments.

How is it possible?

I’ve written about how the size of available memory (cache) affects the performance. When we add additional nodes to the deployment, effectively we increase not only CPU cores, but also the memory that comes with the node (and we are adding extra IO capacity). So, with increasing node counts, we also increase available memory (and cache). As we can see from these graphs, the effect of extra memory could be non-linear (and actually better than linear). Playing on this fact, we can achieve better-than-linear scaling in a sharded setup. I am going to show the experimental setup of how to achieve this.

Experimental setup

To show the sharded setup we will use ProxySQL in front of N MySQL servers (shards). We also will use sysbench with 60 tables (4 million rows each, uniform distribution).

  • For one shard, this shard contains all 60 tables
  • For two shards, each shard contains 30 tables each
  • For three shards, each shard contains 20 tables each
  • For six shards, each shard contains ten tables each

So schematically, it looks like this:

One shard:

Two shards:

Six shards:

We want to measure how the performance (for both throughput and latency) changes when we go from 1 to 2, to 3, to 4, to 5 and to 6 shards.

For the single shard, I used a Google Cloud instance with eight virtual CPUs and 16GB of RAM, where 10GB is allocated for the innodb_buffer_pool_size.

The database size (for all 60 tables) is about 51GB for the data, and 7GB for indexes.

For this we will use a sysbench read-only uniform workload, and ProxySQL helps to perform query routing. We will use ProxySQL query rules, and set sharding as:

mysql -u admin -padmin -h -P6032 -e "DELETE FROM mysql_query_rules"
for i in {1..60}
hg=$(( $i % $shards + 1))
mysql -u admin -padmin -h -P6032 -e "INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES ($i,1,'root','sbtest$is',$hg,1);"
mysql -u admin -padmin -h -P6032 -e "LOAD MYSQL QUERY RULES TO RUNTIME;"

Command line for sysbench 1.0.4:
sysbench oltp_read_only.lua --mysql-socket=/tmp/proxysql.sock --mysql-user=root --mysql-password=test --tables=60 --table-size=4000000 --threads=60 --report-interval=10 --time=900 --rand-type=pareto run

The results Nodes Throughput Speedup vs. 1 node Latency, ms 1 245 1.00 244.88 2 682 2.78 87.95 3 1659 6.77 36.16 4 2748 11.22 21.83 5 3384 13.81 17.72 6 3514 14.34 17.07

As we can see, the performance improves by a factor much better than just linearly.

With five nodes, the improvement is 13.81 times compared to the single node.

The 6th node does not add much benefit, as at this time data practically fits into memory (with five nodes, the total cache size is 50GB compared to the 51GB data size)

Factors that affects multi-node scaling

How can we model/predict the performance gain? There are multiple factors to take into account: the size of the active working set, the available memory size and (also importantly) the distribution of the access to the working set (with uniform distribution being the best case scenario, and with access to the one with only one row being the opposite corner-case, where speedup is impossible). Also we need to keep network speed in mind: if we come close to using all available network bandwidth, it will be impossible to get significant improvement.


In multi-node, auto-scaling, auto-sharding distributed systems, the traditional scalability models do not provide much help. We need to have a better framework to understand how multiple nodes affect performance.

Percona Monitoring and Management 1.1.3 is Now Available

April 21, 2017 - 3:32pm

Percona announces the release of Percona Monitoring and Management 1.1.3 on April 21, 2017.

For installation instructions, see the Deployment Guide.

This release includes several new graphs in dashboards related to InnoDB and MongoDB operation, as well as smaller fixes and improvements.

New in PMM Server
  • PMM-649: Added the InnoDB Page Splits and InnoDB Page Reorgs graphs to the MySQL InnoDB Metrics Advanced dashboard.
  • Added the following graphs to the MongoDB ReplSet dashboard:
    • Oplog Getmore Time
    • Oplog Operations
    • Oplog Processing Time
    • Oplog Buffered Operations
    • Oplog Buffer Capacity
  • Added descriptions for graphs in the following dashboards:
    • MongoDB Overview
    • MongoDB ReplSet
    • PMM Demo
Changes in PMM Client
  • PMM-491: Improved pmm-admin error messages.
  • PMM-523: Added the --verbose option for pmm-admin add.
  • PMM-592: Added the --force option for pmm-admin stop.
  • PMM-702: Added the db.serverStatus().metrics.repl.executor stats to mongodb_exporter. These new stats will be used for graphs in future releases.
  • PMM-731: Added real-time checks to pmm-admin check-network output.
  • The following commands no longer require connection to PMM Server:
    • pmm-admin start --all
    • pmm-admin stop --all
    • pmm-admin restart --all
    • pmm-admin show-passwords

    NOTE: If you want to start, stop, or restart a specific service, connection to PMM Server is still required.

About Percona Monitoring and Management

Percona Monitoring and Management is an open-source platform for managing and monitoring MySQL and MongoDB performance. Percona developed it in collaboration with experts in the field of managed database services, support and consulting.

PMM is a free and open-source solution that you can run in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.

A live demo of PMM is available at

Please provide your feedback and questions on the PMM forum.

If you would like to report a bug or submit a feature request, use the PMM project in JIRA.

Percona Server for MySQL in Docker Swarm with Secrets

April 21, 2017 - 2:43pm

This quick post demonstrates using Percona Server for MySQL in Docker Swarm with some new authentication provisioning practices.

Some small changes to the startup script for the Percona-Server container image allows us to specify a file that contains password values to set as our root user’s secret. “Why do we need this functionality,” I hear you cry? When we use an environment variable, it’s not terribly hard to locate the value to which someone has set as their database root password. Environment variables are not well suited for sensitive data. We preach against leaving our important passwords in easy to reach places. So moving towards something more secure whilst retaining usability is desirable. I’ll detail the current methods, the problems, and finish off with Docker Secrets – which in my opinion, is the right direction to be heading.

Environment Variables

I’ll elaborate on the main reason why we would want to change from the default given behavior. In the documentation for using the MySQL/Percona and MariaDB containers, we are invited to start containers with an environment variable to control what the instance’s root password is set as upon startup. Let’s demonstrate with the latest official Percona-Server image from Percona’s repository of images on the Docker Hub registry:

moore@chom:~$ docker pull percona/percona-server:latest latest: Pulling from percona/percona-server e12c678537ae: Already exists 65ab4b835640: Pull complete f63269a127d1: Pull complete 757a4fef28b8: Pull complete b0cb547a5105: Pull complete 4214179ba9ea: Pull complete 155dafd2fd9c: Pull complete 848020b1da10: Pull complete 771687fe7e8b: Pull complete Digest: sha256:f3197cac76cccd40c3525891ce16b0e9f6d650ccef76e993ed7a22654dc05b73 Status: Downloaded newer image for percona/percona-server:latest

Then start a container:

moore@chom:~$ docker run -d --name percona-server-1 -e MYSQL_ROOT_PASSWORD='secret' percona/percona-server d08f299a872f1408c142b58bc2ce8e59004acfdb26dca93d71f5e9367b4f2a57 moore@chom:~$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d08f299a872f percona/percona-server "/ " 32 seconds ago Up 31 seconds 3306/tcp percona-server-1

Looks good, eh? Let’s inspect this container a little closer to reveal why this method is flawed:

moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-server-1 MYSQL_ROOT_PASSWORD=secret


We don’t want the root password exposed here, not really. If we wanted to use this method in docker-compose files, we would also be storing passwords inline, which isn’t considered a secure practice.

Environment File

Another approach is to use an environment file. This is simply a file that we can provide docker run or docker-compose in order to instantiate the environment variables within the container. It’s a construct for convenience. So just to illustrate that we have the same problem, the next example uses the mechanism of an environment file for our database container:

moore@chom:~$ echo 'MYSQL_ROOT_PASSWORD=secret' > /tmp/ps.env moore@chom:~$ docker run -d --name percona-server-2 --env-file=/tmp/ps.env percona/percona-server d5105d044673bd5912e0e29c2f56fa37c5f174d9d2a4811ceaba284092837c84 moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-server-2 MYSQL_ROOT_PASSWORD=secret NOTE: shortly after starting this container failed because we didn't provide mysql root password options

While we’re not specifying it in our docker run command or our docker-compose.yml file, the password value remains on our filesystem within the environment file. Again, not ideal.

Password File

With the ability to use a password file it obscures this from the inspect output. Let’s roll through the steps we would use to leverage this new option. With our new Percona-Server image, we’re going to start a container, but first let’s create an arbitrary file containing our desired password:

moore@chom:~$ docker:cloud> echo "secret" > /tmp/mysql_pwd_file

Now start a container where we’re going to bind mount the file, and use our new environment variable to point to it:

moore@chom:~$ docker run -v /tmp/mysql_pwd_file:/tmp/mysqlpwd --name percona-secret -e MYSQL_ROOT_PASSWORD_FILE=/tmp/mysqlpwd percona/percona-server:latest

With the same inspect command, let’s show that there’s no snooping on our password value:

moore@chom:~$ docker inspect --format '{{ index (index .Config.Env) 0}}' percona-secret MYSQL_ROOT_PASSWORD_FILE=/tmp/mysqlpwd

We are revealing the path where our password was read from within the container. For those eagle-eyed readers, this file was just a bind mounted file in the docker run command, and it’s still on the host’s filesystem.

moore@chom:~$ cat /tmp/mysql_pwd_file secret moore@chom:~$ docker exec percona-secret cat /tmp/mysqlpwd secret

Not perfect, because we need to have that file available on all of our Docker hosts, but it works and we’re closer to a more robust solution.

Docker Secrets

The main reason for the new environment variable is to leverage the docker secrets feature. Since Docker version 1.13 (17.03 is now GA), we have the Docker Secrets feature, however it’s only available to the Docker Swarm workflow. If you’re not already working with Docker Swarm mode, I can’t recommend it enough. It’s part of Docker-engine, simple to get started, and intuitive since 1.13 it is compatible with docker-compose files. You don’t need to have a cluster of hardware, it’s entirely valid to use Docker Swarm on a single node. This allows you to test on your local environment with ease.

I won’t waste pixels explaining what’s already well documented in official channels, but in summary: Docker secrets is a new feature that allows us to keep sensitive information out of source code and configuration files. Secrets are stored in the Raft log which is encrypted and replicated throughout the Docker Swarm cluster. The protection and distribution come for free out of the box, which is a fantastic feature if you ask me.

So, let’s create a Docker Secret. Please note that I’ve moved to my Docker Swarm installation for this next part:

moore@chom:~$ docker:cloud> docker info | egrep -i 'swarm|version' Server Version: 17.03.0-ce Swarm: active

Operating as a swarm manager we have the ability to create a new secret to serve as our root user’s password:

moore@chom:~$ docker:cloud> echo "{secret_string}" | docker secret create mysql_root_password - ugd8dx0kae9hbyt4opbolukgi

We can list all of our existing secrets:

moore@chom:~$ docker:cloud> docker secret ls ID NAME CREATED UPDATED ugd8dx0kae9hbyt4opbolukgi mysql_root_password Less than a second ago Less than a second ago

Now our secret has been created, it’s obscured from us. We are unable to see it’s value.

moore@chom:~$ docker secret inspect mysql_root_password [ { "ID": "ugd8dx0kae9hbyt4opbolukgi", "Version": { "Index": 905780 }, "CreatedAt": "2017-04-11T23:33:08.118037434Z", "UpdatedAt": "2017-04-11T23:33:08.118037434Z", "Spec": { "Name": "mysql_root_password" } } ]

Now we can use our secret to set our authentication for the MySQL instance by doing the following:

moore@chom:~$ docker service create --name percona-secret --secret mysql_root_password -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql_root_password percona/percona-server:latest

You can see that instead of docker run, I’ve issued the swarm equivalent docker service create, which is going to start a new Percona-Server container in the scope of my Swarm workflow. I’m also using the –secret option to tell docker to mount my secret in the container, which gets mounted to a file under the path /run/secrets/{secret_name}. The final point here, I’m passing MYSQL_ROOT_PASSWORD_FILE=/path/to/secret as an environment variable to let the startup script know where to find the file with my secret value for the root password. Once the startup routine has completed and the container has started successfully I can connect to my container to test the password was set correctly:

moore@chom:~$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 397bdf9b75f9 percona/percona-server "/ " 46 seconds ago Up 44 seconds 3306/tcp percona-secret.1.9xvbneset9363dr5xv4fqqxua moore@chom:~$ docker exec -ti 397bdf9b75f9 bash mysql@397bdf9b75f9:/$ cat /run/secrets/mysql_root_password {secret_string} mysql@397bdf9b75f9:/$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.17-11 Percona Server (GPL), Release '11', Revision 'f60191c' Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>

The secret can be shared around any container where it’s necessary, simply by telling Docker to use the secret when instantiating a container. For example, if I wanted to start an application container such as a WordPress instance, I can use a secret object to easily share credentials to the data source safely and consistently.

This method is totally viable for other forms of sensitive data. For example, I can generate SSL certificates and use Docker secrets to add them to my containers for encrypted replication or forcing secure logins from remote clients. I’m still thinking of all the possible use cases for this option and no doubt will have some more to share with you in the near future.

Please share your comments, suggestions and corrections in the comments below. Thank you for reading.

Simplified Percona XtraDB Cluster SSL Configuration

April 21, 2017 - 10:09am

In this blog post, we’ll look at a feature that recently added to Percona XtraDB Cluster 5.7.16, that makes it easier to configure Percona XtraDB Cluster SSL for all related communications. It uses mode “encrypt=4”, and configures SSL for both IST/Galera communications and SST communications using the same SSL files. “encrypt=4” is a new encryption mode added in Percona XtraDB Cluster 5.7.16 (we’ll cover it in a later blog post).

If this option is used, this will override all other Galera/SST SSL-related file options. This is to ensure that a consistent configuration is applied. Using this option also means that the Galera/SST communications are using the same keys as client connections. Example

This example shows how to startup a cluster using this option. We will use the default SSL files created by the bootstrap node. Basically, there are two steps:

  1. Set pxc_encrypt_cluster_traffic=ON on all nodes
  2. Ensure that all nodes share the same SSL files
Step 1: Configuration (on all nodes)

We enable the pxc_encrypt_cluster_traffic option in the configuration files on all nodes. The default value of this option is “OFF”, so we enable it here.

[mysqld] pxc_encrypt_cluster_traffic=ON

Step 2: Startup the bootstrap node

After initializing and starting up the bootstrap node, the datadir will contain the necessary data files. Here is some SSL-related log output:

[Note] Auto generated SSL certificates are placed in data directory. [Warning] CA certificate ca.pem is self signed. [Note] Auto generated RSA key files are placed in data directory.

The required files are ca.pem, server-cert.pem and server-key.pem, which are the Certificate Authority (CA) file, the server certificate and the server private key, respectively.

Step 3: Copy the SSL files to all other nodes

Galera views the cluster as a set of homogeneous nodes, so the same configuration is expected on all nodes. Therefore, we have to copy the CA file, the server’s certificate and the server’s private key. By default, MySQL names these: ca.pem, server-cert.pem, and server-key.pem, respectively.

Step 4: Startup the other nodes

This is some log output showing that the SSL certificate files have been found. The other nodes should be using the files that were created on the bootstrap node.

[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. [Note] Skipping generation of SSL certificates as certificate files are present in data directory. [Warning] CA certificate ca.pem is self signed. [Note] Skipping generation of RSA key pair as key files are present in data directory.

This is some log output (with log_error_verbosity=3), showing the SST reporting on the configuration used.

WSREP_SST: [DEBUG] pxc_encrypt_cluster_traffic is enabled, using PXC auto-ssl configuration WSREP_SST: [DEBUG] with encrypt=4 ssl_ca=/my/data//ca.pem ssl_cert=/my/data//server-cert.pem ssl_key=/my/data//server-key.pem


The “ssl-ca”, “ssl-cert”, and “ssl-key” options in the “[mysqld]” section can be used to specify the location of the SSL files. If these are not specified, then the datadir is searched (using the default names of “ca.pem”, “server-cert.pem” and “server-key.pem”).

[mysqld] pxc_encrypt_cluster_traffic=ON ssl-ca=/path/to/ca.pem ssl-cert=/path/to/server-cert.pem ssl-key=/path/to/server-key.pem

If you want to implement this yourself, the equivalent configuration file options are:

[mysqld] wsrep_provider_options=”socket.ssl_key=server-key.pem;socket.ssl_cert=server-cert.pem;socket.ssl_ca=ca.pem” [sst] encrypt=4 ssl-ca=ca.pem ssl-cert=server-cert.pem ssl-key=server-key.pem

How it works
  1. Determine the location of the SSL files
    1. Uses the values if explicitly specified (via the “ssl-ca”, “ssl-cert” and “ssl-key” options in the “[mysqld]” section)
    2. If the SSL file options are not specified, we look in the data directory for files named “ca.pem”, “server-cert.pem” and “server-key.pem” for the CA file, the server certificate, and the server key, respectively.
  2. Modify the configuration
    1. Overrides the values for socket.ssl_ca, socket.ssl_cert, and socket.ssl_key in wsrep_provider_options in the “[mysqld]” section.
    2. Sets “encrypt=4” in the “[sst]” section.
    3. Overrides the values for ssl-ca, ssl-cert and ssl-key in the “[sst]” section.

This is not a dynamic setting, and is only available on startup.

How to Setup and Troubleshoot Percona PAM with LDAP for External Authentication

April 21, 2017 - 9:20am

In this blog, we’ll look at how to setup and troubleshoot the Percona PAM authentication plugin.

We occasionally get requests from our support clients on how to get Percona Server for MySQL to authenticate with an external authentication service via LDAP or Active Directory. However, we normally do not have access to client’s infrastructure to help troubleshoot these cases. To help them effectively, we need to setup a testbed to reproduce their issues and guide them on how to get authentication to work. Fortunately, we only need to install Samba to provide an external authentication service for both LDAP and AD.

In this article, I will show you how to (a) compile and install Samba, (b) create a domain environment with Samba, (c) add users and groups to this domain and (d) get Percona Server to use these accounts for authentication via LDAP. In my follow-up article, I will discuss how to get MySQL to authenticate credentials with Active Directory.

My testbed environment consists of two machines

Samba PDC
OS: CentOS 7
IP Address:
Domain name: EXAMPLE.COM
Firewall: none

Percona Server 5.7 with LDAP authentication
OS: CentOS 7
IP Address:

and have several users and groups:

Domain Groups and Users
Support: jericho, jervin and vishal
DBA: sidd, paul and arunjith
Search: ldap

Compile and Install Samba

We will install an NTP client on the Samba PDC/ machine because time synchronization is a requirement for domain authentication. We will also compile and install Samba from source because the Samba implementation in the official repository doesn’t include the Active Directory Domain Controller role. Hence, samba-tool is not included in the official repository. For our testbed, we need this tool because it makes it easier to provision a domain and manage users and groups. So, for CentOS 7, you can either build from source or use a trusted 3rd party build of Samba (as discussed in Samba’s wiki).

For more information, please read Setting up Samba as an Active Directory Domain Controller as well.

  1. Install, configure, and run the NTP client. Ensure that this client service runs when the server boots up:

[root@samba-10 ~]# yum -y install ntp * * * Installed: ntp.x86_64 0:4.2.6p5-25.el7.centos.1 Dependency Installed: autogen-libopts.x86_64 0:5.18-5.el7 ntpdate.x86_64 0:4.2.6p5-25.el7.centos.1 [root@samba-10 ~]# ntpdate 7 Apr 06:06:07 ntpdate[9788]: step time server offset 0.807640 sec [root@samba-10 ~]# systemctl enable ntpd.service Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/ntpd.service. [root@samba-10 ~]# systemctl start ntpd.service

  1. Install compilers and library dependencies for compiling Samba:

[root@samba-10 ~]# yum -y install gcc perl python-devel gnutls-devel libacl-devel openldap-devel * * * Installed: gcc.x86_64 0:4.8.5-11.el7 gnutls-devel.x86_64 0:3.3.24-1.el7 libacl-devel.x86_64 0:2.2.51-12.el7 openldap-devel.x86_64 0:2.4.40-13.el7 perl.x86_64 4:5.16.3-291.el7 python-devel.x86_64 0:2.7.5-48.el7 Dependency Installed: cpp.x86_64 0:4.8.5-11.el7 cyrus-sasl.x86_64 0:2.1.26-20.el7_2 cyrus-sasl-devel.x86_64 0:2.1.26-20.el7_2 glibc-devel.x86_64 0:2.17-157.el7_3.1 glibc-headers.x86_64 0:2.17-157.el7_3.1 gmp-devel.x86_64 1:6.0.0-12.el7_1 gnutls-c++.x86_64 0:3.3.24-1.el7 gnutls-dane.x86_64 0:3.3.24-1.el7 kernel-headers.x86_64 0:3.10.0-514.10.2.el7 ldns.x86_64 0:1.6.16-10.el7 libattr-devel.x86_64 0:2.4.46-12.el7 libevent.x86_64 0:2.0.21-4.el7 libmpc.x86_64 0:1.0.1-3.el7 libtasn1-devel.x86_64 0:3.8-3.el7 mpfr.x86_64 0:3.1.1-4.el7 nettle-devel.x86_64 0:2.7.1-8.el7 p11-kit-devel.x86_64 0:0.20.7-3.el7 perl-Carp.noarch 0:1.26-244.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-2.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-Pod-Escapes.noarch 1:1.04-291.el7 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-4.el7 perl-Storable.x86_64 0:2.45-3.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-291.el7 perl-macros.x86_64 4:5.16.3-291.el7 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7 unbound-libs.x86_64 0:1.4.20-28.el7 zlib-devel.x86_64 0:1.2.7-17.el7 Complete!

  1. Download, compile and install Samba:

[root@samba-10 ~]# yum -y install wget * * * [root@samba-10 ~]# wget * * * 2017-04-07 06:16:59 (337 KB/s) - 'samba-latest.tar.gz' saved [21097045/21097045] [root@samba-10 ~]# tar xzf samba-latest.tar.gz [root@samba-10 ~]# cd samba-4.6.2/ [root@samba-10 samba-4.6.2]# ./configure --prefix=/opt/samba Checking for program gcc or cc : /usr/bin/gcc Checking for program cpp : /usr/bin/cpp Checking for program ar : /usr/bin/ar Checking for program ranlib : /usr/bin/ranlib * * * Checking compiler for PIE support : yes Checking compiler for full RELRO support : yes Checking if toolchain accepts -fstack-protector : yes 'configure' finished successfully (39.119s) [root@samba-10 samba-4.6.2]# make WAF_MAKE=1 python ./buildtools/bin/waf build Waf: Entering directory `/root/samba-4.6.2/bin' symlink: -> python/ * * * [3773/3775] Linking default/source3/modules/ [3774/3775] Linking default/source3/modules/ [3775/3775] Linking default/source3/modules/ Waf: Leaving directory `/root/samba-4.6.2/bin' 'build' finished successfully (6m58.144s) [root@samba-10 samba-4.6.2]# make install WAF_MAKE=1 python ./buildtools/bin/waf install Waf: Entering directory `/root/samba-4.6.2/bin' * creating /opt/samba/etc * creating /opt/samba/private * * * * installing bin/default/source3/nmbd/nmbd.inst as /opt/samba/sbin/nmbd * installing bin/default/file_server/ as /opt/samba/lib/service/ Waf: Leaving directory `/root/samba-4.6.2/bin' 'install' finished successfully (1m44.377s)

Please take note that when I downloaded Samba, the latest version was 4.6.2. If you have a problem with compiling the latest version of Samba, try using version 4.6.2.

  1. Include executable path of Samba to the PATH variable so we can call samba binaries without specifying its absolute path:

[root@samba-10 samba-4.6.2]# echo "PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin" >> /etc/environment [root@samba-10 samba-4.6.2]# PATH=/opt/samba/sbin:/opt/samba/bin:/usr/sbin:/usr/bin [root@samba-10 samba-4.6.2]# which samba-tool /opt/samba/bin/samba-tool

  1. Setup systemd script for Samba and ensure that this service auto starts on server boot

[root@samba-10 samba-4.6.2]# echo "[Unit] Description=Samba PDC [Service] Type=forking PIDFile=//opt/samba/var/run/ ExecStart=/opt/samba/sbin/samba -D ExecReload=/usr/bin/kill -HUP $MAINPID ExecStop=/usr/bin/kill $MAINPID [Install]" > /etc/systemd/system/samba.service [root@samba-10 samba-4.6.2]# systemctl enable samba.service Created symlink from /etc/systemd/system/ to /etc/systemd/system/samba.service.

  1. Remove existing /etc/krb5.conf, because the existing configuration prevents us from provisioning a new domain.

[root@samba-10 samba-4.6.2]# rm -f /etc/krb5.conf [root@samba-10 samba-4.6.2]# cd [root@samba-10 ~]#

  1. Done.
Create a domain environment with Samba
  1. To setup a domain, all we need to do is to run “samba-tool domain provision” and pass the following details:

Server Role: dc(domain controller)
DNS forwarder IP address:

You will also need to supply the Administrator password. This account is used to join a workstation or server to a domain:

[root@samba-10 ~]# samba-tool domain provision Realm [EXAMPLE.ORG]: EXAMPLE.COM Domain [EXAMPLE]: EXAMPLE Server Role (dc, member, standalone) [dc]: dc DNS backend (SAMBA_INTERNAL, BIND9_FLATFILE, BIND9_DLZ, NONE) [SAMBA_INTERNAL]: SAMBA_INTERNAL DNS forwarder IP address (write 'none' to disable forwarding) []: Administrator password: Retype password: Looking up IPv4 addresses Looking up IPv6 addresses No IPv6 address will be assigned Setting up secrets.ldb Setting up the registry Setting up the privileges database Setting up idmap db Setting up SAM db Setting up sam.ldb partitions and settings Setting up sam.ldb rootDSE Pre-loading the Samba 4 and AD schema Adding DomainDN: DC=example,DC=com Adding configuration container Setting up sam.ldb schema Setting up sam.ldb configuration data Setting up display specifiers Modifying display specifiers Adding users container Modifying users container Adding computers container Modifying computers container Setting up sam.ldb data Setting up well known security principals Setting up sam.ldb users and groups Setting up self join Adding DNS accounts Creating CN=MicrosoftDNS,CN=System,DC=example,DC=com Creating DomainDnsZones and ForestDnsZones partitions Populating DomainDnsZones and ForestDnsZones partitions Setting up sam.ldb rootDSE marking as synchronized Fixing provision GUIDs A Kerberos configuration suitable for Samba AD has been generated at /opt/samba/private/krb5.conf Once the above files are installed, your Samba4 server will be ready to use Server Role: active directory domain controller Hostname: samba-10 NetBIOS Domain: EXAMPLE DNS Domain: DOMAIN SID: S-1-5-21-1337223342-1741564684-602463608

Please take note that if you get the error below, it’s likely due to not removing the existing /etc/krb5.conf before using samba-tool:

ERROR(ldb): uncaught exception - operations error at ../source4/dsdb/samdb/ldb_modules/password_hash.c:2820 File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/", line 176, in _run return*args, **kwargs) File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/", line 471, in run nosync=ldap_backend_nosync, ldap_dryrun_mode=ldap_dryrun_mode) File "/opt/samba/lib64/python2.7/site-packages/samba/provision/", line 2175, in provision skip_sysvolacl=skip_sysvolacl) File "/opt/samba/lib64/python2.7/site-packages/samba/provision/", line 1787, in provision_fill next_rid=next_rid, dc_rid=dc_rid) File "/opt/samba/lib64/python2.7/site-packages/samba/provision/", line 1447, in fill_samdb "KRBTGTPASS_B64": b64encode(krbtgtpass.encode('utf-16-le')) File "/opt/samba/lib64/python2.7/site-packages/samba/provision/", line 55, in setup_add_ldif ldb.add_ldif(data, controls) File "/opt/samba/lib64/python2.7/site-packages/samba/", line 225, in add_ldif self.add(msg, controls)

You could also get an error if you entered a simple password for the Administrator account.

  1. Create a symlink of the generated krb5.conf in /etc. This configuration is used authenticate machines, accounts and services:

[root@samba-10 ~]# ln -s /opt/samba/private/krb5.conf /etc

  1. Start the Samba service:

[root@samba-10 ~]# systemctl start samba.service

  1. Check network ports to see if Samba is running:

[root@samba-10 ~]# yum -y install net-tools * * * [root@samba-10 ~]# netstat -tapn Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0* LISTEN 13296/samba tcp 0 0* LISTEN 13302/samba tcp 0 0* LISTEN 875/sshd tcp 0 0* LISTEN 13296/samba tcp 0 0* LISTEN 1327/master tcp 0 0* LISTEN 13294/samba tcp 0 0* LISTEN 13307/smbd tcp 0 0* LISTEN 13291/samba tcp 0 0* LISTEN 13291/samba tcp 0 0* LISTEN 13294/samba tcp 0 0* LISTEN 13294/samba tcp 0 0* LISTEN 13294/samba tcp 0 0* LISTEN 13291/samba tcp 0 0* LISTEN 13307/smbd

  1. Done.
Add users and groups to this domain

Now that Samba is running we can add users and groups, and assign users to groups with samba-tool.

  1. Add groups by running “samba-tool group add group_name”:

[root@samba-10 ~]# samba-tool group add support Added group support [root@samba-10 ~]# samba-tool group add dba Added group dba [root@samba-10 ~]# samba-tool group add search Added group search

  1. Add users by running “samba-tool user create username”:

[root@samba-10 ~]# samba-tool user create jericho New Password: Retype Password: User 'jericho' created successfully [root@samba-10 ~]# samba-tool user create jervin New Password: Retype Password: User 'jervin' created successfully [root@samba-10 ~]# samba-tool user create vishal New Password: Retype Password: User 'vishal' created successfully [root@samba-10 ~]# samba-tool user create sidd New Password: Retype Password: User 'sidd' created successfully [root@samba-10 ~]# samba-tool user create paul New Password: Retype Password: User 'paul' created successfully [root@samba-10 ~]# samba-tool user create arunjith New Password: Retype Password: User 'arunjith' created successfully [root@samba-10 ~]# samba-tool user create ldap New Password: Retype Password: User 'ldap' created successfully

  1. Add users to their corresponding groups with “samba-tool group addmembers group_name user,user2,usern”:

[root@samba-10 ~]# samba-tool group addmembers support jericho,jervin,vishal Added members to group support [root@samba-10 ~]# samba-tool group addmembers dba sidd,paul,arunjith Added members to group dba [root@samba-10 ~]# samba-tool group addmembers search ldap Added members to group search

  1. Verify that users, groups and memberships exist with commands “samba-tool user list”, “samba-tool group list” and “samba-tool group listmembers group_name”:

[root@samba-10 ~]# samba-tool user list Administrator arunjith jericho jervin krbtgt vishal Guest ldap paul sidd [root@samba-10 ~]# samba-tool group list Allowed RODC Password Replication Group Enterprise Read-Only Domain Controllers Denied RODC Password Replication Group Pre-Windows 2000 Compatible Access Windows Authorization Access Group Certificate Service DCOM Access Network Configuration Operators Terminal Server License Servers Incoming Forest Trust Builders Read-Only Domain Controllers Group Policy Creator Owners Performance Monitor Users Cryptographic Operators Distributed COM Users Performance Log Users Remote Desktop Users Account Operators Event Log Readers RAS and IAS Servers Backup Operators Domain Controllers Server Operators Enterprise Admins Print Operators Administrators Domain Computers Cert Publishers DnsUpdateProxy Domain Admins Domain Guests Schema Admins Domain Users Replicator IIS_IUSRS DnsAdmins Guests Users support search dba [root@samba-10 ~]# samba-tool group listmembers support jervin jericho vishal [root@samba-10 ~]# samba-tool group listmembers dba arunjith sidd paul [root@samba-10 ~]# samba-tool group listmembers search ldap

For more information on using samba-tool, just run samba-tool --help.

  1. Done.
How to get Percona Server to use these accounts for authentication via LDAP

We will be using the machine to offer MySQL service with LDAP authentication via Percona PAM. If you’re not familiar with Percona PAM, please have a look at this before moving forward.

At this point, our Samba service is running with users, groups and memberships added. We can now query Samba via LDAP ports 389 and 636. We will configure the server to do LDAP lookups when searching for users and groups. This is necessary because we use the name service to validate group membership. We will then install Percona Server for MySQL and configure our PAM plugin to use nss-pam-ldapd to authenticate to LDAP. Finally, we will test LDAP authentication on Percona Server for MySQL using a regular user and proxy user.

  1. Install nss-pam-ldapd and nscd. We will use these packages to query LDAP server from our server:

[root@ps-20 ~]# yum -y install nss-pam-ldapd

  1. Configure nss-pam-ldapd by incorporating our Samba’s LDAP settings:

[root@ps-20 ~]# echo "uid nslcd gid ldap pagesize 1000 referrals off idle_timelimit 800 filter passwd (&(objectClass=user)(objectClass=person)(!(objectClass=computer))) map passwd uid sAMAccountName map passwd uidNumber objectSid:S-1-5-21-1337223342-1741564684-602463608 map passwd gidNumber objectSid:S-1-5-21-1337223342-1741564684-602463608 map passwd homeDirectory "/home/$cn" map passwd gecos displayName map passwd loginShell "/bin/bash" filter group (|(objectClass=group)(objectClass=person)) map group gidNumber objectSid:S-1-5-21-1337223342-1741564684-602463608 uri ldaps:// base dc=example,dc=com tls_reqcert never binddn cn=ldap,cn=Users,dc=example,dc=com bindpw MyLdapPasswordDontCopyIt2017" > /etc/nslcd.conf

As you can see above, this config contains LDAP settings, mapping custom LDAP attributes, and LDAP credentials. The value of objectSid was taken from “DOMAIN SID” that was generated when I created a new domain. So, be sure to use the value of “DOMAIN SID” generated on your end. Otherwise, your LDAP queries will not match any record. However, if you’re authenticating from an existing Windows AD server, you can obtain the value of “DOMAIN SID” by running “Get-ADDomain”. Also, you can take a look at this link to get to know more about other configurations for nslcd.conf.

  1. Add LDAP lookup to nsswitch service by editing /etc/nsswitch.conf:

passwd: files sss
shadow: files sss
group: files sss

Replace with:
passwd: files sss ldap
shadow: files sss ldap
group: files sss ldap

  1. Run nslcd in debug mode:

[root@ps-20 ~]# nslcd -d nslcd: DEBUG: add_uri(ldaps:// nslcd: DEBUG: ldap_set_option(LDAP_OPT_X_TLS_REQUIRE_CERT,0) nslcd: version 0.8.13 starting nslcd: DEBUG: unlink() of /var/run/nslcd/socket failed (ignored): No such file or directory nslcd: DEBUG: initgroups("nslcd",55) done nslcd: DEBUG: setgid(55) done nslcd: DEBUG: setuid(65) done nslcd: accepting connections

  1. Test if LDAP lookups work by running “id ” and “getent passwd” on another terminal:

[root@ps-20 ~]# id jervin uid=1107(jervin) gid=1107(jervin) groups=1107(jervin),1103(support) [root@ps-20 ~]# id paul uid=1110(paul) gid=1110(paul) groups=1110(paul),1104(dba) [root@ps-20 ~]# getent passwd root:x:0:0:root:/root:/bin/bash bin:x:1:1:bin:/bin:/sbin/nologin daemon:x:2:2:daemon:/sbin:/sbin/nologin adm:x:3:4:adm:/var/adm:/sbin/nologin lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin sync:x:5:0:sync:/sbin:/bin/sync shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown halt:x:7:0:halt:/sbin:/sbin/halt mail:x:8:12:mail:/var/spool/mail:/sbin/nologin operator:x:11:0:operator:/root:/sbin/nologin games:x:12:100:games:/usr/games:/sbin/nologin ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin nobody:x:99:99:Nobody:/:/sbin/nologin avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin systemd-bus-proxy:x:999:997:systemd Bus Proxy:/:/sbin/nologin systemd-network:x:998:996:systemd Network Management:/:/sbin/nologin dbus:x:81:81:System message bus:/:/sbin/nologin polkitd:x:997:995:User for polkitd:/:/sbin/nologin tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin postfix:x:89:89::/var/spool/postfix:/sbin/nologin sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin user:x:1000:1000:user:/home/user:/bin/bash mysql:x:27:27:Percona Server:/var/lib/mysql:/bin/false nscd:x:28:28:NSCD Daemon:/:/sbin/nologin nslcd:x:65:55:LDAP Client User:/:/sbin/nologin Administrator:*:500:500::/home/Administrator:/bin/bash arunjith:*:1111:1111::/home/arunjith:/bin/bash jericho:*:1106:1106::/home/jericho:/bin/bash jervin:*:1107:1107::/home/jervin:/bin/bash krbtgt:*:502:502::/home/krbtgt:/bin/bash vishal:*:1108:1108::/home/vishal:/bin/bash Guest:*:501:501::/home/Guest:/bin/bash ldap:*:1112:1112::/home/ldap:/bin/bash paul:*:1110:1110::/home/paul:/bin/bash sidd:*:1109:1109::/home/sidd:/bin/bash

If you take a look at the nslcd terminal again, you will see that it’s trying to resolve the user and group identification with LDAP searches:

* * * nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_simple_bind_s("cn=ldap,cn=Users,dc=example,dc=com","***") (uri="ldaps://") nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com nslcd: [7b23c6] <passwd=1107> DEBUG: ldap_result(): end of results (1 total) nslcd: [3c9869] DEBUG: connection from pid=10468 uid=0 gid=0 nslcd: [3c9869] <passwd=1107> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(objectSid=�1�5�0�0�0�0�0�515�0�0�0ae68b44f�c2bce6778dde8... * * * nslcd: [5558ec] <passwd="paul"> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))(sAMAccountName=paul))") nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com nslcd: [5558ec] <passwd="paul"> DEBUG: ldap_result(): end of results (1 total) * * * nslcd: [e2a9e3] <passwd(all)> DEBUG: myldap_search(base="dc=example,dc=com", filter="(&(objectClass=user)(objectClass=person)(!(objectClass=computer)))") nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Administrator,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=arunjith,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jericho,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=jervin,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=krbtgt,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=vishal,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=Guest,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=ldap,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=paul,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): CN=sidd,CN=Users,DC=example,DC=com nslcd: [e2a9e3] <passwd(all)> DEBUG: ldap_result(): end of results (10 total)

Now that we know nslcd is working, shut it down by running “Ctrl-C”.

  1. Run nslcd normally and make sure it starts up on boot:

[root@ps-20 ~]# systemctl start nslcd.service [root@ps-20 ~]# systemctl enable nslcd.service Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/nslcd.service.

  1. Install and run Percona Server for MySQL 5.7 and make sure it runs when the server boots up:

[root@ps-20 ~]# rpm -Uvh Retrieving Preparing... ################################# [100%] Updating / installing... 1:percona-release-0.1-4 ################################# [100%] [root@ps-20 ~]# yum -y install Percona-Server-server-57 * * * [root@ps-20 ~]# mysqld --initialize-insecure --user=mysql [root@ps-20 ~]# systemctl start mysqld.service [root@ps-20 ~]# systemctl enable mysqld.service Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mysqld.service. Created symlink from /etc/systemd/system/ to /usr/lib/systemd/system/mysqld.service.

  1. Login to MySQL and change the root password:

[root@ps-20 ~]# mysql -uroot mysql> SET PASSWORD=PASSWORD('MyNewAndImprovedPassword');

  1. Install the Percona PAM plugin:

mysql> delete from mysql.user where user=''; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN auth_pam SONAME ''; Query OK, 0 rows affected (0.01 sec) mysql> INSTALL PLUGIN auth_pam_compat SONAME ''; Query OK, 0 rows affected (0.00 sec)

  1. Configure Percona PAM to authenticate to LDAP by creating /etc/pam.d/mysqld with this content:

auth required account required

  1. Create a MySQL user that will authenticate via auth_pam:

mysql> CREATE USER jervin@'%' IDENTIFIED WITH auth_pam; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON support.* TO jervin@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

  1. Login as this user and check grants:

[root@ps-20 ~]# mysql -u jervin Password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 22 Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SHOW GRANTS; +-----------------------------------------------------+ | Grants for jervin@% | +-----------------------------------------------------+ | GRANT USAGE ON *.* TO 'jervin'@'%' | | GRANT ALL PRIVILEGES ON `support`.* TO 'jervin'@'%' | +-----------------------------------------------------+ 2 rows in set (0.00 sec)

It works! However, if you have 100 support users who have the same MySQL privileges, creating 100 MySQL users is tedious and can be difficult to maintain. If belonging to a group has certain MySQL privileges, setup proxy users instead to map a user’s privilege to its defined group. We will implement this for both dba and support users in the next step.

For now, delete the user we just created:

mysql> DROP USER jervin@'%'; Query OK, 0 rows affected (0.00 sec)

  1. Create proxy user and proxied accounts:

mysql> CREATE USER ''@'' IDENTIFIED WITH auth_pam as 'mysqld,support=support_users,dba=dba_users'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER support_users@'%' IDENTIFIED BY 'some_password'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER dba_users@'%' IDENTIFIED BY 'some_password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON support.* TO support_users@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO dba_users@'%'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT PROXY ON support_users@'%' TO ''@''; Query OK, 0 rows affected (0.00 sec) mysql> GRANT PROXY ON dba_users@'%' TO ''@''; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

To know more about setting up proxy users, see this article written by Stephane.

  1. Let’s try logging in as “jericho” and “paul” and see if they inherit the privileges of their group.

[root@ps-20 ~]# mysql -ujericho -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 25 Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SELECT user(), current_user(), @@proxy_user; +-------------------+-----------------+--------------+ | user() | current_user() | @@proxy_user | +-------------------+-----------------+--------------+ | jericho@localhost | support_users@% | ''@'' | +-------------------+-----------------+--------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +------------------------------------------------------------+ | Grants for support_users@% | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'support_users'@'%' | | GRANT ALL PRIVILEGES ON `support`.* TO 'support_users'@'%' | +------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> quit Bye [root@ps-20 ~]# mysql -upaul -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 27 Server version: 5.7.17-13 Percona Server (GPL), Release 13, Revision fd33d43 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SELECT user(), current_user(), @@proxy_user; +----------------+----------------+--------------+ | user() | current_user() | @@proxy_user | +----------------+----------------+--------------+ | paul@localhost | dba_users@% | ''@'' | +----------------+----------------+--------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS; +------------------------------------------------+ | Grants for dba_users@% | +------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'dba_users'@'%' | +------------------------------------------------+ 1 row in set (0.00 sec)

As you can see, they did inherit the MySQL privileges of their groups.

  1. Done.

To be honest, setting up Percona PAM with LDAP can be challenging if you add this functionality with existing infrastructure. But hopefully, by setting this up in a lab environment from scratch, and doing some tests, you’ll be confident enough to incorporate this feature in production environments.

Enabling Percona XtraDB Cluster SST Traffic Encryption

April 21, 2017 - 7:48am

In this blog post, we’ll look at enabling Percona XtraDB Cluster SST Traffic Encryption, and some of the changes to the SSL-based encryption of SST traffic in Percona XtraDB Cluster 5.7.16.

Some background

Percona XtraDB Cluster versions prior to 5.7 support encryption methods 0, 1, 2 and 3:

  • encrypt = 0 : (default) No encryption
  • encrypt = 1 : Symmetric encryption using AES-128, user-supplied key
  • encrypt = 2 : SSL-based encryption with a CA and cert files (via socat)
  • encrypt = 3 : SSL-based encryption with cert and key files (via socat)

We are deprecating modes encrypt=1,2,3 in favor of the new mode, encrypt=4. “encrypt=3” is not recommended, since it does not verify the cert being used (it cannot verify since no Certificate Authority (CA) file is provided). “encrypt=2” and “encrypt=3” use a slightly different way of building the SSL files than MySQL does. In order to remove confusion, we’ve deprecated these modes in favor of “encrypt=4”, which can use the MySQL generated SSL files.

New feature: encrypt= 4

The previous SSL methods (encrypt=2 and encrypt=3), are based on socat usage, The certs are not built the same way as the certs created by MySQL (for encryption of client communication with MySQL). To simplify SSL configuration and usage, we added a new encryption method (encrypt=4) so that the SSL files generated by MySQL can now be used for SSL encryption of SST traffic.

For instructions on how to create these files, see


In general, galera views the cluster as homogeneous, so it expects that all nodes are identically configured. This extends to the SSL configuration, so the preferred configuration is that all machines share the same certs/keys. The security implication is that possession of these certs/keys allows a machine to join the cluster and receive all of the data. So proper care must be taken to secure the SSL files.

The mode “encrypt=4” uses the same option names as MySQL, so it reads the SSL information from “ssl-ca”, “ssl-cert”, and “ssl-key” in the “[sst]” section of the configuration file.

Example my.cnf:

[sst] encrypt=4 ssl-ca=/path/to/ca.pem ssl-cert=/path/to/server-cert.pem ssl-key=/path/to/server-key.pem

All three options (ssl-ca, ssl-cert, and ssl-key) must be specified otherwise the SST will return an error.


This is the location of the Certificate Authority (CA) file. Only servers that have certificates generated from this CA file will be allowed to connect if SSL is enabled.


This is the location fo the Certificate file. This is the digital certificate that will be sent to the other side of the SSL connection. The remote server will then verify that this certificate was generated from the Certificate Authority file in use by the remote server.


This is the location of the private key for the certificate specified in ssl-cert.

Percona Live Featured Session with Alibaba Cloud – Flashback: Rolling Back a MySQL/MariaDB Instance, Database or Table to a Previous Snapshot

April 20, 2017 - 2:13pm

Welcome to another post in the series of Percona Live featured session blogs! In these blogs, we’ll highlight some of the session speakers that will be at this year’s Percona Live conference. We’ll also discuss how these sessions can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured session, we’ll meet Staff Engineer Guangzhou Zhang (who focuses on PostgreSQL), Staff Engineer Lixun Peng (who focuses on Replication), Senior Engineer Weixiang Zhai (who focuses on InnoDB) and Senior Engineer Xin Liu (who focuses on MongoDB) who are all from Alibaba Cloud, the cloud computing arm of Alibaba Group.

Alibaba Cloud is holding a session called Flashback: Rolling Back a MySQL/MariaDB Instance, Database or Table to a Previous Snapshot. The talk will discuss how Flashback is currently implemented, what it currently can and can’t do, and what features are in the pipeline for future MariaDB/AliSQL releases.

I had a chance to speak with them about Flashback:

Percona: How did you get into database technology? What do you love about it?

Guangzhou Zhang: Database technology is fundamental to every IT system as it lays the foundation to provide persistency, concurrency and availability. What makes it even more attractive and exciting is in recent years, the “old” database technology has found new directions and innovations in today’s age of cloud computing. There is so much work that can be done fitting open source databases into cloud environments, or even innovating new “cloud native” database architectures in the public cloud.

Lixun: When I was in university, I found database theory very interesting. I decided to be a DBA after graduation. Then I studied lots of Oracle Database books. When I graduated, funnily enough, I became a MySQL DBA, which has meant that I have focused on MySQL-related work until now. MySQL is a great database, but it’s not perfect! I always have optimization requirements to enhance its performance and improve the functionality step by step. I have found it very interesting though and continue to be happy with what it makes possible. And now many of Alibaba Cloud’s users are using my code: this is a great feeling.

Percona: Your talk is called Flashback: Rolling Back a MySQL/MariaDB Instance, Database or Table to a Previous Snapshot. Why would somebody need to rollback a database instance?

Lixun: Anyone can make mistakes, include DBAs. After users mishandle their data, we need to recover from the failure as soon as possible. Then we need a way to recover the data from the correct snapshot, and if possible, do it online and fast. That’s why I have implemented the Flashback feature as it provides the ability to achieve this.

Percona: What are the issues you face rolling back an instance? How does Flashback help?

Lixun: We can, of course, recover data from the last full backup set and incremental binary logs, but if a user’s database is too huge, it could take a while! This is particularly frustrating as it can only be a small amount of the data that needs to be modified, but we still need to recover the whole database.

Flashback allows you to reverse the last misoperation from binary logs. More often than not this will be a small activity, so the speed is much faster than recovery from a full backup. And we don’t need to stop the instance server to do carry this out. That’s very important for the Cloud users.

Percona: What do you want attendees to take away from your session? Why should they attend?

Lixun: I hope the attendees of my session can learn how and why Flashback works, the best way to use it and when they should try to use it.

And Flashback still has some limitations that the users should be aware of. I plan to address these in future versions.

I contributed the Flashback feature to MySQL and MariaDB at the same time. MariaDB 10.2 released it. We are still developing the feature, and I want attendees to know what’s in the roadmap during my session.

Percona: What are you most looking forward to at Percona Live 2017?

Xin Liu: There are two things I’m looking forward to at Percona Live. Firstly, holding technical discussion groups around the subject of our talks or about other open source databases. Also, I‘m interested in other NoSQL-focused database topics, such as HBase, Redis, Cassandra, etc. I also want to learn more about its core storage engine, especially WiredTiger or MongoRocks (which are the MongoDB’s storage engines). Gathering more details, design information or ideas for improvements will bring benefit to for us and our work.

Lixun: The best thing for me is meeting with the best MySQL engineers at the conference. There are very few chances to communicate with the engineers from around the world about the latest technology, and share updates with each other.

Percona: Talk about your team’s other topics . . .

Lixun: The topic proposed by Xin Liu (Multi Active-Active and Disaster Recovery with MongoDB Database Center) demonstrates how we can recover a MongoDB cloud service from a disaster failure, even if we lost whole cluster of a region. Active-Active deployment is the typical way in our production environment, and we developed a system called “Lamda” for replicating asynchronous replication within each region.

The talk from Weixiang Zhai (Scale Read Workload by Sharing Data Files of InnoDB) will introduce how we changed InnoDB so that MySQL can be deployed on shared storage and we can provide the ability to scale out read-only workload.

Guangzhou Zhang (On Building Alibaba’s Public Cloud Database Service for PostgreSQL and MySQL) will talk about the problems we solved while fitting PostgreSQL engines into our public cloud database services. We introduced a lot of enhancements in the database engine to solve disk IO or memory isolation problems. The talk also includes a comparison of PostgreSQL and MySQL covering why and how to deal with them differently within our service.

Register for Percona Live Data Performance Conference 2017, and see Lixun present Flashback: Rolling Back a MySQL/MariaDB Instance, Database or Table to a Previous Snapshot. Use the code FeaturedTalk and receive $100 off the current registration price!

Percona Live Data Performance Conference 2017 is the premier open source event for the data performance ecosystem. It is the place to be for the open source community, as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 24-27, 2017 at the Hyatt Regency Santa Clara and the Santa Clara Convention Center.

Tracking IST Progress in Percona XtraDB Cluster

April 20, 2017 - 1:33pm

In this blog post, we’ll look at how Percona XtraDB Cluster uses IST.


Percona XtraDB Cluster uses the concept of an Incremental State Transfer (IST). When a node of the cluster leaves the cluster for a short period of time, it can rejoin the cluster by getting the delta set of missing changes from any active node in the cluster.

This process of getting the delta set of changes is named as IST in Percona XtraDB Cluster.

Tracking IST Progress

The number of write-sets/changes that the joining node needs to catch up on when rejoining the cluster is dictated by:

  1. The duration the node was not present in the cluster
  2. The workload of the cluster during that time frame

This catch-up process can be time-consuming. Until this process is complete, the rejoining node is not ready to process any active workloads.

We believe that any process that is time-consuming should have a progress monitor attached to it. This is exactly what we have done.

In the latest release of Percona XtraDB Cluster 5.7.17-29.20, we added an IST progress monitor that is exposed through SHOW STATUS. This helps you to monitor the percentage of write-sets which has been applied by the rejoining node.

Let’s see this in a working example:

  • Start a two-node cluster
  • Process some basic workloads, allow cluster replication
  • Shutdown node-2
  • Node-1 then continues to process more workloads (the workload fits the allocated gcache)
  • Restart Node-2, causing it to trigger an IST

mysql> show status like 'wsrep_ist_receive_status'; +--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | wsrep_ist_receive_status | 3% complete, received seqno 1421771 of 1415410-1589676 | +--------------------------+--------------------------------------------------------+ 1 row in set (0.00 sec) .... mysql> show status like 'wsrep_ist_receive_status'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | wsrep_ist_receive_status | 52% complete, received seqno 1506799 of 1415410-1589676 | +--------------------------+---------------------------------------------------------+ 1 row in set (0.00 sec) .... mysql> show status like 'wsrep_ist_receive_status'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | wsrep_ist_receive_status | 97% complete, received seqno 1585923 of 1415410-1589676 | +--------------------------+---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show status like 'wsrep_ist_receive_status'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | wsrep_ist_receive_status | | +--------------------------+-------+ 1 row in set (0.00 sec)

As you can see, the wsrep_ist_receive_status monitoring string indicates the percentage completed, currently received write-set and the range of write-sets applicable to the IST.

Once the IST activity is complete, the variable shows an empty-string.

Closing Comments

I hope you enjoy this newly added feature. Percona Engineering would be happy to hear from you, about more such features that can help you make effective use of Percona XtraDB Cluster. We will try our best to include them in our future plans (based on feasibility).

Note: Special thanks for Kenn Takara and Roel Van de Paar for helping me edit this post.

More Trackable Flow Control for Percona XtraDB Cluster

April 20, 2017 - 10:38am

In this blog post, we’ll discuss trackable flow control in Percona XtraDB Cluster.


Percona XtraDB Cluster has a self-regulating mechanism called Flow Control. This mechanism helps to avoid a situation wherein the weakest/slowest member of the cluster falls significantly behind other members of the cluster.

When a member of a cluster is slow at applying write-sets (while simultaneously continuing to receive write-sets from the cluster group channel), then the incoming/receive queue grows in size. If this queue crosses a set threshold (gcs.fc_limit), the node emits a FLOW_CONTROL message asking other members to slow down or halt processing.

While this happens transparently for the end-user, it’s important for the cluster administrator to know whether a node is using flow-control. If it is, it can affect overall cluster productivity.

Finding if a node is in flow control

FLOW_CONTROL is not a persistent state. A node enters FLOW_CONTROL once the queue size exceeds the set threshold. It is released back again once the queue size falls back below the low-end watermark.

So how can one view the higher and lower watermarks?

Up until now, this was exposed only in the log files. However, starting with Percona XtraDB Cluster 5.7.17-29.20, this is now exposed and available through SHOW STATUS:

mysql> show status like 'wsrep_flow_control_interval'; +-----------------------------+------------+ | Variable_name | Value | +-----------------------------+------------+ | wsrep_flow_control_interval | [ 12, 23 ] | +-----------------------------+------------+ 1 row in set (0.00 sec) mysql> set global wsrep_provider_options="gcs.fc_limit=100"; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'wsrep_flow_control_interval'; +-----------------------------+-------------+ | Variable_name | Value | +-----------------------------+-------------+ | wsrep_flow_control_interval | [ 71, 141 ] | +-----------------------------+-------------+ 1 row in set (0.00 sec) mysql> set global wsrep_provider_options="gcs.fc_factor=0.75"; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'wsrep_flow_control_interval'; +-----------------------------+--------------+ | Variable_name | Value | +-----------------------------+--------------+ | wsrep_flow_control_interval | [ 106, 141 ] | +-----------------------------+--------------+ 1 row in set (0.01 sec)

As you can see, the wsrep_flow_control_interval status variable emits a range representing the lower and higher watermark. A value set of (12, 23) means that if the incoming queue size is greater than 23, then FLOW_CONTROL is enabled. If the size falls below 12, FLOW_CONTROL is released.

Still, this does not show us if the node is using FLOW_CONTROL at any given moment.

To address this, we simultaneously introduced a wsrep_flow_control_status status variable into the same release. This boolean status variable tells the user if the node is in FLOW_CONTROL or not. Once the node is out of flow-control, the variable is switched to OFF, and vice versa to ON when the node is employing flow-control:

show status like '%flow%'; ..... | wsrep_flow_control_sent | 28156 | | wsrep_flow_control_recv | 28156 | | wsrep_flow_control_interval | [ 106, 141 ] | | wsrep_flow_control_status | ON |

Finally, the wsrep_flow_control_sent/recv counter can be used to track FLOW_CONTROL status. This shows an aggregated count of how many times flow-control kicked in. You can clear them using FLUSH STATUS.

mysql> show status like '%flow%'; ..... | wsrep_flow_control_sent | 31491 | | wsrep_flow_control_recv | 31491 | | wsrep_flow_control_interval | [ 106, 141 ] | | wsrep_flow_control_status | OFF |

Takeaway thoughts

We hope you enjoy the new features added to Percona XtraDB Cluster, including flow control tracking!

We like feedback from customers (via the usual Customer Support website), and where possible from the community (via Percona JIRA and Launchpad), on which features they most want to see. We recently re-engineered Percona XtraDB Cluster to have much-increased performance (a request originating from the community), and the new IST Progress Meter and Flow Control Monitoring were introduced to aid in solving customer-facing challenges.

If you have a feature you would like to see, let us know!

Note: Special thanks for Kenn Takara and Roel Van de Paar for helping me edit this post.

How We Made Percona XtraDB Cluster Scale

April 19, 2017 - 2:46pm

In this blog post, we’ll look at the actions and efforts Percona experts took to scale Percona XtraDB Cluster.


When we first started analyzing Percona XtraDB Cluster performance, it was pretty bad. We would see contention even with 16 threads. Performance was even worse with sync binlog=1, although the same pattern was observed even with the binary log disabled. The effect was not only limited to OLTP workloads, as even other workloads (like update-key/non-key) were also affected in a wider sense than OLTP.

That’s when we started analyzing the contention issues and found multiple problems. We will discuss all these problems and the solutions we adapted. But before that, let’s look at the current performance level.

Check this blog post for more details.

The good news is Percona XtraDB Cluster is now optimized to scale well for all scenarios, and the gain is in the range of 3x-10x.

Understanding How MySQL Commits a Transaction

Percona XtraDB Cluster contention is associated mainly with Commit Monitor contention, which comes into the picture during commit time. It is important to understand the context around it.

When a commit is invoked, it proceeds in two phases:

  • Prepare phase: mark the transaction as PREPARE, updating the undo segment to capture the updated state.
    • If bin-log is enabled, redo changes are not persisted immediately. Instead, a batch flush is done during Group Commit Flush stage.
    • If bin-log is disabled, then redo changes are persisted immediately.
  • Commit phase: Mark the transaction commit in memory.
    • If bin-log is enabled, Group Commit optimization kicks in, thereby causing a flush of redo-logs (that persists changes done to db-objects + PREPARE state of transaction) and this action is followed by a flush of the binary logs. Since the binary logs are flushed, redo log capturing of transaction commit doesn’t need to flush immediately (Saving fsync)
    • If bin-log is disabled, redo logs are flushed on completion of the transaction to persist the updated commit state of the transaction.
What is a Monitor in Percona XtraDB Cluster World?

Monitors help maintain transaction ordering. For example, the Commit Monitor ensures that no transaction with a global-seqno greater than the current commit-processing transaction’s global seqno is allowed to proceed.

How Percona XtraDB Cluster Commits a Transaction

Percona XtraDB Cluster follows the existing MySQL semantics of course, but has its own step to commit the transaction in the replication world. There are two important themes:

  1. Apply/Execution of transaction can proceed in parallel
  2. Commit is serialized based on cluster-wide global seqno.

Let’s understand the commit flow with Percona XtraDB Cluster involved (Percona XtraDB Cluster registers wsrep as an additional storage engine for replication).

  • Prepare phase:
    • wsrep prepare: executes two main actions:
      • Replicate the transaction (adding the write-set to group-channel)
      • Entering CommitMonitor. Thereby enforcing ordering of transaction.
    • binlog prepare: nothing significant (for this flow).
    • innobase prepare: mark the transaction in PREPARE state.
      • As discussed above, the persistence of the REDO log depends on if the binlog is enabled/disabled.
  • Commit phase
    • If bin-log is enabled
      • MySQL Group Commit Logic kicks in. The semantics ensure that the order of transaction commit is the same as the order of them getting added to the flush-queue of the group-commit.
    • If bin-log is disabled
      • Normal commit action for all registered storage engines is called with immediate persistence of redo log.
    • Percona XtraDB Cluster then invokes the post_commit hook, thereby releasing the Commit Monitor so that the next transaction can make progress.

With that understanding, let’s look at the problems and solutions:


Commit Monitor is exercised such that the complete commit operation is serialized. This limits the parallelism associated with the prepare-stage. With log-bin enabled, this is still ok since redo logs are flushed at group-commit flush-stage (starting with 5.7). But if log-bin is disabled, then each commit causes an independent redo-log-flush (in turn probable fsync).


Split the replication pre-commit hook into two explicit actions: replicate (add write-set to group-channel) + pre-commit (enter commit-monitor).

The replicate action is performed just like before (as part of storage engine prepare). That will help complete the InnoDB prepare action in parallel (exploring much-needed parallelism in REDO flush with log-bin disabled).

On completion of replication, the pre-commit hook is called. That leads to entering the Commit Monitor for enforcing the commit ordering of the transactions. (Note: Replication action assigns the global seqno. So even if a transaction with a higher global seqno finishes the replication action earlier (due to CPU scheduling) than the transaction with a lower global seqno, it will wait in the pre-commit hook.)

Improved parallelism in the innodb-prepare stage helps accelerate log-bin enabled flow, and the same improved parallelism significantly helps in the log-bin disabled case by reducing redo-flush contention, thereby reducing fsyncs.


MySQL Group Commit already has a concept of ordering transactions based on the order of their addition to the GROUP COMMIT queue (FLUSH STAGE queue to be specific). Commit Monitor enforces the same, making the action redundant but limiting parallelism in MySQL Group Commit Logic (including redo-log flush that is now delayed to the flush stage).

With the existing flow (due to the involvement of Commit Monitor), only one transaction can enter the GROUP COMMIT Queue, thereby limiting optimal use of Group Commit Logic.


Release the Commit Monitor once the transaction is successfully added to flush-stage of group-commit. MySQL will take it from there to maintain the commit ordering. (We call this interim-commit.)

Releasing the Commit Monitor early helps other transactions to make progress and real MySQL Group Commit Leader-Follower Optimization (batch flushing/sync/commit) comes into play.

This also helps ensure batch REDO log flushing.


This problem is specific to when the log-bin is disabled. Percona XtraDB Cluster still generates the log-bin, as it needs it for forming a replication write-set (it just doesn’t persist this log-bin information). If disk space is not a constraint, then I would suggest operating Percona XtraDB Cluster with log-bin enabled.

With log-bin disabled, OPTIMIZATION-1 is still relevant, but OPTIMIZATION-2 isn’t, as there is no group-commit protocol involved. Instead, MySQL ensures that the redo-log (capturing state change of transaction) is persisted before reporting COMMIT as a success. As per the original flow, the Commit Monitor is not released till the commit action is complete.


The transaction is already committed to memory and the state change is captured. This is about persisting the REDO log only (REDO log modification is already captured by mtr_commit). This means we can release the Commit Monitor just before the REDO flush stage kicks in. Correctness is still ensured as the REDO log flush always persists the data sequentially. So even if trx-1 loses its slots before the flush kicks in, and trx-2 is allowed to make progress, trx-2’s REDO log flush ensures that trx-1’s REDO log is also flushed.


With these three main optimizations, and some small tweaks, we have tuned Percona XtraDB Cluster to scale better and made it fast enough for the growing demands of your applications. All of this is available with the recently released Percona XtraDB Cluster 5.7.17-29.20. Give it a try and watch your application scale in a multi-master environment, making Percona XtraDB Cluster the best fit for your HA workloads.

Percona XtraDB Cluster 5.6.35-26.20-3 is now available

April 19, 2017 - 1:40pm

Percona announces the release of Percona XtraDB Cluster 5.6.35-26.20-3 on April 13, 2017. Binaries are available from the downloads section or our software repositories.

Percona XtraDB Cluster 5.6.35-26.20-3 is now the current release, based on the following:

All Percona software is open-source and free. Details of this release can be found in the 5.6.35-26.20-3 milestone on Launchpad.

NOTE: Due to end of life, Percona will stop producing packages for the following distributions after July 31, 2017:

  • Red Hat Enterprise Linux 5 (Tikanga)
  • Ubuntu 12.04 LTS (Precise Pangolin)

You are strongly advised to upgrade to latest stable versions if you want to continue using Percona software.

Fixed Bugs

  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-797: Blocked wsrep_desync toggling while the node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • Fixed several packaging and dependency issues.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Performance improvements in Percona XtraDB Cluster 5.7.17-29.20

April 19, 2017 - 6:50am

In our latest release of Percona XtraDB Cluster, we’ve introduced major performance improvements to the MySQLwrite-set replication layer. In this post, we want to show what these improvements look like.

For the test, we used the sysbench OLTP_RW, UPDATE_KEY and UPDATE_NOKEY workloads with 100 tables, 4mln rows each, which gives about 100GB of datasize. In all the tests we use a three-node setup, connected via a 10GB network, with the sysbench load directed to the one primary node.

In the first chart, we show improvements comparing to the previous version (5.7.16):

The main improvements come from concurrent workloads, under multiple threads.

The previous chart is for cases using enabled binary logs, but in some situations we will have deployments without binary logs enabled (Percona XtraDB Cluster does not require them). The latest release significantly improves performance for this case as well.

Here is a chart showing throughput without binary logs:

Where does Percona XtraDB Cluster place in comparison with similar technologies? To find out, we’ll compare this release with MySQL 5.7.17 Group Replication and with the recently released MariaDB 10.2.5 RC.

For MySQL 5.7.17 Group Replication, I’ve reviewed two cases: “durable” with sync_binlog=1, and “relaxed durability” with sync_binlog=0.

Also for MySQL 5.7.17 Group Replication, we want to review two cases with different flow_control settings. The first setting is flow_control=25000 (the default setting). It provides better performance, but with the drawbacks that non-primary nodes will fall behind significantly and MySQL Group Replication does not provide a way to protect from reading stale data. So with a default flow_control=25000, we risk reading very outdated data. We also tested MySQL Group Replication with flow_control=1000 to minimize stale data on non-primary nodes.

A note on the Flow Control topic: it is worth mentioning that we also changed the flow_control default for Percona XtraDB Cluster. The default value is 100 instead of 16 (as in version 5.7.16).

Comparison chart with sync_binlog=1 (for MySQL Group Replication):

Comparison chart with sync_binlog=0 (for MySQL Group Replication):

So there are couple conclusions we can make out of these charts.

  1. The new version of Percona XtraDB Cluster performs on the level with MySQL Group Replication
  2. flow_control for MySQl Group Replication really makes a difference for performance, and default flow_control=25000 is better (with the risk of a lot of outdated data on non-primary nodes)

The reference our benchmark files and config files are here.

Percona XtraDB Cluster 5.7.17-29.20 is now available

April 19, 2017 - 6:37am

Percona announces the release of Percona XtraDB Cluster 5.7.17-29.20 on April 19, 2017. Binaries are available from the downloads section or our software repositories.

NOTE: You can also run Docker containers from the images in the Docker Hub repository.

Percona XtraDB Cluster 5.7.17-29.20 is now the current release, based on the following:

All Percona software is open-source and free.

Performance Improvements

This release is focused on performance and scalability with increasing workload threads. Tests show up to 10 times increase in performance.

Fixed Bugs

  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • Improved SST and IST log messages for better readability and unification.
  • Excluded the garbd node from flow control calculations.
  • Added extra checks to verify that SSL files (certificate, certificate authority, and key) are compatible before openning connection.
  • Improved parallelism for better scaling with multiple threads.
  • Added validations for DISCARD TABLESPACE and IMPORT TABLESPACE in PXC Strict Mode to prevent data inconsistency.
  • Added the wsrep_flow_control_status variable to indicate if node is in flow control (paused).
  • PXC-766: Added the wsrep_ist_receive_status variable to show progress during an IST.
  • Allowed CREATE TABLE ... AS SELECT (CTAS) statements with temporary tables (CREATE TEMPORARY TABLE ... AS SELECT) in PXC Strict Mode. For more information, see 1666899.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-783: Improved the wsrep stage framework.
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-795: Set --parallel=4 as default option for wsrep_sst_xtrabackup-v2 to run four threads with XtraBackup.
  • PXC-797: Blocked wsrep_desync toggling while node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • PXC-805: Inherited upstream fix to avoid using deprecated variables, such as INFORMATION_SCHEMA.SESSION_VARIABLE. For more information, see 1676401.
  • PXC-811: Changed default values for the following variables:
    • fc_limit from 16 to 100
    • send_window from 4 to 10
    • user_send_window from 2 to 4
  • Moved wsrep settings into a separate configuration file (/etc/my.cnf.d/wsrep.cnf).
  • Fixed mysqladmin shutdown to correctly stop the server on systems using systemd.
  • Fixed several packaging and dependency issues.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Percona XtraBackup 2.4.7 is Now Available

April 18, 2017 - 12:19pm

Percona announces the GA release of Percona XtraBackup 2.4.7 on April 18, 2017. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New features:
  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:
  • Backups were missing *.isl files for general tablespace. Bug fixed #1658692.
  • In 5.7, MySQL changed default checksum algorithm to crc32, while xtrabackup was using innodb. This caused xtrabackup to perform extra checksum calculations which were not needed. Bug fixed #1664405.
  • For system tablespaces consisting of multiple files xtrabackup updated LSN only in first file. This caused MySQL versions lower than 5.7 to fail on startup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

Release notes with all the bugfixes for Percona XtraBackup 2.4.7 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Visit Percona Store

General Inquiries

For general inquiries, please send us your question and someone will contact you.