Jean-Jerome SchmidtClusterControl Template for Zabbix (18.2.2015, 05:53 UTC)

We’re delighted to announce a ClusterControl Template for Zabbix, so Zabbix users can now get information about the status of their database clusters, backups and alarms. We have previously published integrations with other monitoring systems including Nagios and PagerDuty.

This template is built using the ClusterControl REST API to retrieve monitoring data. Thus, you need to have a ClusterControl API token and URL configured in the template’s configuration file. This simplifies the initial configuration, and allows users to extend the current monitoring data.

If you are running ClusterControl to manage and monitor your database servers/clusters, you don’t have to integrate each of the monitored hosts into Zabbix. Zabbix will be able to connect to ClusterControl and retrieve monitoring data for the database servers. Alerting can now also be integrated into the Zabbix infrastructure.


Monitoring Data


At the time of writing, the ClusterControl template returns the following monitoring data from multiple clusters:

  • Database cluster’s status - either active, failed, degraded and unknown
  • Backups status - Trigger an event if it finds at least one error on all backups
  • ClusterControl alarms (critical/warning) - Report the number of ClusterControl alarms based on severity

If you configured multiple cluster IDs in the Zabbix agent user parameter file, the template will report the statuses from all the clusters, and only return the worse event from any of the clusters. For example, if cluster 1 is up while cluster 2 fails, the template will trigger a failure event from cluster 2. 


Example Deployment


Consider the following example setup:

We have a Galera Cluster for MySQL, as well as a standalone MySQL server, managed by ClusterControl. The Zabbix agent will be installed on the ClusterControl node, and it uses some reporting scripts to talk to ClusterControl to retrieve monitoring data. The Zabbix server will use a ClusterControl template to talk to the Zabbix agent. 


Zabbix Agent

1. Installation instructions can be found on our GitHub repository page. To get the template, just clone the s9s-admin repository:

$ git clone


2. Create a template directory for ClusterControl under /var/lib/zabbix and copy the scripts directory into it:

$ mkdir -p /var/lib/zabbix/clustercontrol
$ cp -Rf ~/s9s-admin/plugins/zabbix/agent/scripts /var/lib/zabbix/clustercontrol


3. Copy the ClusterControl template user paramater file into /etc/zabbix/zabbix.agent.d/:

$ cp -f ~/s9s-admin/plugins/zabbix/agent/userparameter_clustercontrol.conf /etc/zabbix/zabbix.agent.d/


4. This template uses ClusterControl API to collect stats. Configure the value of ClusterControl API URL and token inside /var/lib/zabbix/clustercontrol/scripts/clustercontrol.conf, similar to example below:


Truncated by Planet PHP, read more at the original (another 5471 bytes)

Peter ZaitsevPercona XtraBackup 2.2.9 is now available (17.2.2015, 18:22 UTC)

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.2.9 on February 17, 2015. Downloads are available from our download site or Percona Software 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.

Bugs Fixed:

  • Percona XtraBackup was vulnerable to MITM attack which could allow exfiltration of MySQL configuration information via --version-check option. This vulnerability was logged as CVE 2015-1027. Bug fixed #1408375.
  • xtrabackup_galera_info file isn’t overwritten during the Galera auto-recovery. Bug fixed #1418584.
  • Percona XtraBackup man pages are now included with binary packages. Bug fixed #1156209.
  • Percona XtraBackup now sets the maximum supported session value for lock_wait_timeout variable to prevent unnecessary timeouts when the global value is changed from the default. Bug fixed #1410339.
  • New option --backup-locks, enabled by default, has been implemented to control if backup locks will be used even if they are supported by the server. To disable backup locks innobackupex should be run with innobackupex --no-backup-locks option. Bug fixed #1418820.

Release notes with all the bugfixes for Percona XtraBackup 2.2.9 are available in our online documentation. Bugs can be reported on the launchpad bug tracker. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

The post Percona XtraBackup 2.2.9 is now available appeared first on MySQL Performance Blog.

Peter Zaitsev‘Indexing’ JSON documents for efficient MySQL queries over JSON data (17.2.2015, 16:45 UTC)

MySQL meets NoSQL with JSON UDF

I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely possible to compile and use the UDF with earlier versions of MySQL because the UDF interface has not changed for a long time. However, the UDF should still be considered alpha/preview level of quality and should not be used in production yet! For this example I am using Percona Server 5.6 with the UDF.

That being said, the proof-of-concept that I’m about to present here uses only one JSON function (JSON_EXTRACT) and it has worked well enough in my testing to present my idea here. The JSON functions will probably be GA sometime soon anyway, and this is a useful test of the JSON_EXTRACT function.

The UDF let you parse, search and manipulate JSON data inside of MySQL, bringing MySQL closer to the capabilities of a document store.

Since I am using Percona Server 5.6, I needed to compile and install the UDF. Here are the steps I took to compile the plugin:

  1. $ cd mysql-json-udfs-0.3.3-labs-json-udfs-src
  2. $ cmake -DMYSQL_DIR=/usr/local/mysql .
  3. $ sudo make install
  4. $ sudo cp *.so /usr/local/mysql/lib/plugin

JSON UDF are great, but what’s the problem

The JSON functions work very well for manipulating individual JSON objects, but like all other functions, using JSON_EXTRACT in the WHERE clause will result in a full table scan. This means the functions are virtually useless for searching through large volumes of JSON data.  If you want to use MySQL as a document store, this is going to limit the usefulness in the extreme as the ability to extract key/value pairs from JSON documents is powerful, but without indexing it can’t scale well.

What can be done to index JSON in MySQL for efficient access?

The JSON UDF provides a JSON_EXTRACT function which can pull data out of a JSON document. There are two ways we can use this function to “index” the JSON data.

  1. Add extra columns to the table (or use a separate table, or tables) containing the JSON and populate the columns using JSON_EXTRACT in a trigger. The downside is that this slows down inserts and modifications of the documents significantly.
  2. Use Flexviews materialized views to maintain an index table separately and asynchronously. The upside is that insertion/modification speed is not affected, but there is slight delay before index is populated. This is similar to eventual consistency in a document store.

Writing triggers is an exercise I’ll leave up to the user. The rest of this post will discuss using Flexviews materialized views to create a JSON index.

What is Flexviews?

Flexviews can create ‘incrementally refreshable’ materialized views. This means that the views are able to be refreshed efficiently using changes captured by FlexCDC, the change data capture tool that ships with Flexviews. Since the view can be refreshed fast, it is possible to refresh it frequently and have a low latency index, but not one perfectly in sync with the base table at all times.

The materialized view is a real table that is indexed to provide fast access. Flexviews includes a SQL_API, or a set of stored procedures for defining and maintaining materialized views.

See this set of slides for an overview of Flexviews:

Demo/POC using materialized view as an index

The first step to creating an incrementally refreshable materialized view with Flexviews, is to create a materialized view change log on all of the tables used in the view. The CREATE_MVLOG($schema, $table) function creates the log and FlexCDC will immediately being to collect changes into it.

mysql> call flexviews.create_mvlog('ssb_json','json');
Query OK, 1 row affected (0.01 sec)

Next, the materialized view name, and refresh type must be registered with the CREATE($schema, $mvname, $refreshtype) function:

mysql> call flexviews.create('ssb_json','json_idx','INCREMENTAL');
Query OK, 0 rows affected (0.00 sec)
-- save the just generated identifier for the view.  You can use GET_ID($schema,$mvname) later.
mysql> set @mvid := last_insert_id();
Query OK, 0 rows affected (0.00 sec)

Now one or more tables have to be added to the view using the ADD_TABLE($mvid, $schema, $table, $alias,$joinclause) function. This example will use only one table, but Flexviews supports joins too.

mysql> call flexviews.add_table(@mvid, 'ssb_json','json','json',null);
Query OK, 1 row af

Truncated by Planet PHP, read more at the original (another 18073 bytes)

Oli SennhauserCreating Event Handlers with MySQL Enterprise Monitor (17.2.2015, 12:57 UTC)

MySQL Enterprise Monitor (MEM) has by default no Event Handlers created and activated. These Event Handlers you have to define yourself according to your needs.

In this article we discuss how to create MySQL Enterprise Monitor Event Handlers with MEM v.3.0.18. For other (older) versions the steps may vary...

Task: Event Handler for maximum Connections reached

We would like to be notified by MySQL Enterprise Monitor when the number of connections is near to max_connections.

For this we search first which Advisors are available at all: Configuration -> Advisors -> Availability.

Here we can see that we have an Advisor called Maximum Connection Limit Nearing Or Reached which is scheduled for every 5 minutes and has thresholds at 75, 85, 95 and 100%:

Now we know which Advisor should create and Event. As a next step we have to create and Event which should be triggered: Configuration -> Event Handling -> Create Event Handler.

Here we can create and Event with all its needed configuration: Events -> All -> server.

If we look at the Events we can even see the detailed description and how the values for the Event are collected:

Task: Event Handler for used disk space

For this Event Handler we need the Advisor Filesystem Free Space under Operating System:

In this advisor we can configure the Threshold as well:

In the Event Handler we can define which Assets shall be monitored. For example the mountpoint: /.

Local disks can only be monitored, if a local MySQL Enterprise Monitor Agent is installed. An agent-less MySQL Enterprise Monitor cannot monitor local disk resources...

Have fun using the MySQL Enterprise Monitor. If you need any help in installing or configuring MEM do not hesitate to contact us.

All these functions are also implemented in the FromDual Performance Monitor for MySQL. If you want to relay on Open Source technology only you should consider our Performance Monitor.

Kristian NielsenUnderstanding skew factors in Simplex/Improved Perlin Noise (14.2.2015, 14:56 UTC)

[Here is a PDF version for readers whose browser does not understand MathML.]

The Simplex Noise (or Improved Perlin Noise) algorithm uses a somewhat mysterious "skew factor" of 3-12. I did not find any really satisfactory explanation for this factor in the descriptions of Simplex Noise that I read. But I managed to work it out nevertheless, which I thought was a fun exercise and worth a quick write-up.

Simplex noise is constructed by assigning random values to each point in a simplex grid. The simplex grid is a tiling of the plane using rhombuses, each rhombus consisting of two equilateral triangles. See the figure on the right.

Given a point (x,y) (expressed in normal rectangular coordinates), we first transform the coordinates into (u,v) expressed in the simplex grid. Then we take the integer parts of u and v to find the corners of the containing equilateral triangle, and take the random values assigned to these corners. The "noise value" of the original point (x,y) is then some suitable interpolation of these values with respect to the distance from (x,y) to each corner.

The implementation of this algorithm is explained in detail in several places. The code to transform into and back out of the simplex grid might look like this:

final double F2 = 0.5*(Math.sqrt(3.0)-1.0);
double s = (xin+yin)*F2;
int u = fastfloor(xin+s);
int v = fastfloor(yin+s);
final double G2 = -(3.0-Math.sqrt(3.0))/6.0;
double t = (u+v)*G2;
double X0 = u+t;
double Y0 = v+t;
So the question is, where do these funny factors F2 and G2 come from?

To understand this, let us first consider the general form of the transformation from simplex coordinates (u,v) in the grid spanned by u→ and v→ to the rectangular coordinates (x,y). It is

where u→ = (a,c) and u→ = (b,d). So this requires 4 multiplications in the general case.

However, we can freely choose which simplex grid to use! So we can try to choose one that reduces the computational work needed.

First, we can choose the orientation of the grid so that u→ and v→ are symmetric around the diagonal x=y. Then a=d and b=c, so we can write the transformation as

x= (a-b) u+b (u+v)
y= (a-b) v+b (u+v)
Second, we can choose the scale of the grid so that (a-b)=1, and then we get simply
where t=b(u+v). This simpler form requires only a single multiplication.

This is exactly the form we see in the above code snippet, with G2 being the name for the constant b. We can work out from this that the vectors that span the grid used by the code are

u→ = (1-3-

Truncated by Planet PHP, read more at the original (another 1575 bytes)

Peter ZaitsevPercona XtraDB Cluster 5.6: a tale of 2 GTIDs (13.2.2015, 14:27 UTC)

Say you have a cluster with 3 nodes using Percona XtraDB Cluster (PXC) 5.6 and one asynchronous replica connected to node1. If asynchronous replication is using GTIDs, moving the replica so that it is connected to node2 is trivial, right? Actually replication can easily break for reasons that may not be obvious at first sight.


Let’s assume we have the following setup with 3 PXC nodes and one asynchronous replica:

Regarding MySQL GTIDs, a Galera cluster behaves like a distributed master: transactions coming from any node will use the same auto-generated uuid. This auto-generated uuid is related to the Galera uuid, it’s neither ABC, nor DEF, nor GHI.

Transactions executed for instance on node1 but not replicated to all nodes with Galera replication will generate a GTID using the uuid of the node (ABC). This can happen for writes on MyISAM tables if wsrep_replicate_myisam is not enabled.

Such local transactions bring the same potential issues as errant transactions do for a regular master-slave setup using GTID-based replication: if node3 has a local transaction, when you connect replica1 to it, replication may break instantly.

So do not assume that moving replica1 from node2 to node3 is a safe operation if you don’t check errant transactions on node3 first.

And if you find errant transactions that you don’t want to get replicated to replica1, there is only one good fix: insert a corresponding empty transaction on replica1.


Both kinds of GTIDs are using the same format: <source_id:trx_number>.

For Galera, <source_id> is generated when the cluster is bootstrapped. This <source_id> is shared by all nodes.

For MySQL, <source_id> is the server uuid. So it is easy to identify from which server a transaction originates.

Knowing the Galera GTID of a transaction will give you no clue about the corresponding MySQL GTID of the same transaction, and vice versa. You should simply consider them as separate identifiers.

MySQL GTID generation when writing to the cluster

What can be surprising is that writing to node1 will generate a MySQL GTID where <source_id> is not the server uuid:

node1> select @@server_uuid;
| @@server_uuid                        |
| 03c236a0-f860-11e3-9b80-9cebe8067a3f |
node1> select @@global.gtid_executed;
| @@global.gtid_executed                   |
| b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1   |

Even more surprising is that if you write to node2, you will see a single GTID set as if both transactions had been executed on the same server:

node2> select @@global.gtid_executed;
| @@global.gtid_executed                   |
| b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:2   |

Actually this is reasonable: the cluster acts as a distributed master regarding MySQL replication, so it makes sense that all nodes share the same <source_id>.

And by the way, if you are puzzled about how this ‘anonymous’ <source_id> is generated, look at this:

mysql> show global status like 'wsrep_local_state_uuid';
| Variable_name          | Value                                |
| wsrep_local_state_uuid | 4e0c0cc5-f876-11e3-bc0c-07c8c1ed0e15 |
node1> select @@global.gtid_executed;
| @@global.gtid_executed                   |
| b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1   |

If you ‘sum’ both <source_id>, you will get ffffffff-ffff-ffff-ffff-ffffffffffff.

How can local transactions show up?

Now the question is: given that any transaction

Truncated by Planet PHP, read more at the original (another 2641 bytes)

Open QueryLKML: Live patching for 3.20 (12.2.2015, 06:09 UTC)

Building on the original kSplice idea and combining the efforts of the work done at Red Hat and SuSE, common infrastructure is now ready to be put into the Linux 3.20 mainline kernel – Red Hat and SuSE have already committed to using this.

I still reckon it’s freaky trickery, but heck – it works, and it’s great for server environments that have no redundancy (I prefer to fix that issue!) and can’t afford any downtime.

Peter ZaitsevOnline GTID rollout now available in Percona Server 5.6 (10.2.2015, 08:00 UTC)

Global Transaction IDs (GTIDs) are one of my favorite features of MySQL 5.6. The main limitation is that you must stop all the servers at the same time to allow GTID-replication. Not everyone can afford to take a downtime so this requirement has been a showstopper for many people. Starting with Percona Server 5.6.22-72.0 enabling GTID replication can be done without almost no downtime. Let’s see how to do it.

Implementation of the Facebook patch

Finding a solution to migrate to GTIDs with no downtime is not a new idea, and several companies have already developed their own patch. The 2 best known implementations are the one from Facebook and the one from

Both options have pros and cons, and we finally chose to port the Facebook patch and add a new setting (gtid_deployment_step).

Performing the migration

Let’s assume we have a master-slaves setup with 4 servers A, B, C and D. A is the master:

The 1st step is to take each slave out of rotation, one at a time, and set gtid_mode = ON and gtid_deployment_step = ON (and also log_bin, log_slave_updates and enforce_gtid_consistency).

gtid_deployment_step = ON means that a server will not generate GTIDs when it executes writes, but it will record a GTID in its binary log if it gets an event from the replication stream tagged with a GTID.

The 2nd step is to promote one of the slaves to become the new master (for instance C) and to disable gtid_deployment_step. It is a regular slave promotion so you should do it the same way you deal with planned slave promotions (for instance using MHA or your own scripts). Our patch doesn’t help you do this promotion.

At this point replication will break on the old master as it has gtid_mode = OFF and gtid_deployment_step = OFF.

Don’t forget that you need to use CHANGE MASTER TO MASTER_AUTO_POSITION = 1 to enable GTID-based replication.

The 3rd step is to restart the old master to set gtid_mode = ON. Replication will resume automatically, but don’t forget to set MASTER_AUTO_POSITION = 1.

The final step is to disable gtid_deployment_step on all slaves. This can be done dynamically:

mysql> SET GLOBAL gtid_deployment_step = OFF;

and you should remove the setting from the my.cnf file so that it is not set again when the server is restarted.

Optionally, you can promote the old master back to its original role.

That’s it, GTID replication is now available without having restarted all servers at the same time!


At some point during the migration, a slave promotion is needed. And at this point, you are still using position-based replication. The patch will not help you with this promotion so use your regular failover scripts. If you have no scripts to deal with that kind of situation, make sure you know how to proceed.

Also be aware that this patch provides a way to migrate to GTIDs with no downtime, but not a way to migrate away from GTIDs with no downtime. So test carefully and make sure you understand all the new stuff that comes with GTIDs, like

Truncated by Planet PHP, read more at the original (another 1139 bytes)

Shlomi NoachMySQL Community Awards 2015: Call for Nominations! (6.2.2015, 19:37 UTC)

The 2015 MySQL Community Awards event will take place, as usual, in Santa Clara, during the Percona Live MySQL Conference & Expo, April 2015.

The MySQL Community Awards is a community based initiative. The idea is to publicly recognize contributors to the MySQL ecosystem. The entire process of discussing, voting and awarding is controlled by an independent group of community members, typically based of past winners or their representatives, as well as known contributors.

It is a self-appointed, self-declared, self-making-up-the-rules-as-it-goes committee. It is also very aware of the importance of the community; a no-nonsense, non-political, adhering to tradition, self criticizing committee.

The Call for Nominations is open. We are seeking the community’s assistance in nominating candidates in the following categories:

MySQL Community Awards: Community Contributor of the year 2015

This is a personal award; a winner would a person who has made contribution to the MySQL ecosystem. This could be via development, advocating, blogging, speaking, supporting, etc. All things go.

MySQL Community Awards: Application of the year 2015

An application, project, product etc. which supports the MySQL ecosystem by either contributing code, complementing its behaviour, supporting its use, etc. This could range from a one man open source project to a large scale social service.

MySQL Community Awards: Corporate Contributor of the year 2015

A company who made contribution to the MySQL ecosystem. This might be a corporate which released major open source code; one that advocates for MySQL; one that help out community members by... anything.

For a list of previous winners, please see MySQL Hall of Fame.

Process of nomination and voting

Anyone can nominate anyone. When nominating, please make sure to provide a brief explanation on why the candidate is eligible to get the award. Make a good case!

The committee will review all nominations and vote; it typically takes two rounds of votes to pick the winners, and a lot of discussion.

There will be up to three winners in each category.

Methods of nomination:

  • Send en email to
  • Comment to this post
  • Assuming you can provide a reasonable description in 140 characters, tweet your nomination at #MySQLAwards.

Please submit your nominations no later than Saturday, February 28, 2015.

The committee

Members of the committee are: Baron Schwartz, Colin Charles, Domas Mituzas, Fredric Descamps, Geoffrey Anderson, Giuseppe Maxia, Marc Delisle, Mark Leith, Philip Stoev, Ronald Bradford, Santiago Lertora. A couple additional members pending.

Jeremy Cole and myself (Shlomi Noach) are acting as co-secretaries; we will be non-voting (except for breaking ties).

The committee communicates throughout the nomination and voting process to exchange views and opinions.

The awards

Awards are traditionally (as in this year) donated by some party whose identity remains secret. Thank you, kind sponsor!


This is a community effort; we ask for your support in spreading the word and of course in nominating candidates. Thanks!

Peter ZaitsevPercona Server 5.6.22-72.0 is now available (6.2.2015, 17:04 UTC)

Percona ServerPercona is glad to announce the release of Percona Server 5.6.22-72.0 on February 6, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.22, including all the bug fixes in it, Percona Server 5.6.22-72.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – and this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release can be found in the 5.6.22-72.0 milestone on Launchpad.

New Features:

  • Percona Server is now able to log the query’s response times into separate READ and WRITE INFORMATION_SCHEMA tables. Two new INFORMATION_SCHEMA tables QUERY_RESPONSE_TIME_READ and QUERY_RESPONSE_TIME_WRITE have been implemented for READ and WRITE queries correspondingly.
  • Percona Server now supports Online GTID deployment. This enables GTID to be deployed on existing replication setups without making the master read-only and stopping all the slaves. This feature was ported from the Facebook branch.
  • New ps_tokudb_admin script has been implemented to make the TokuDB storage engine installation easier.

Bugs Fixed:

  • SET STATEMENT ... FOR would crash the server if it could not execute the due to: 1) if the was Read-Write in a Read-Only transaction (bug #1387951), 2) if the needed to re-open an already open temporary table and would fail to do so (bug #1412423), 3) if the needed to commit implicitly the ongoing transaction and the implicit commit would fail (bug #1418049).
  • TokuDB storage engine would fail to load after the upgrade on CentOS 5 and 6. Bug fixed #1413956.
  • Fixed a potential low-probability crash in XtraDB linear read-ahead code. Bug fixed #1417953.
  • Setting the max_statement_time per query had no effect. Bug fixed #1376934.

Other bugs fixed: #1407941, and #1415843 (upstream #75642)

Release notes for Percona Server 5.6.22-72.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.22-72.0 is now available appeared first on MySQL Performance Blog.

LinksRSS 0.92   RDF 1.
Atom Feed