Planet MariaDB

May 26, 2017

Peter Zaitsev

Percona Server for MongoDB 3.0.15-1.10 is Now Available

Percona Server for MongoDB 3.2

Percona Server for MongoDB 3.0Percona announces the release of Percona Server for MongoDB 3.0.15-1.10 on May 26, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly-scalable, zero-maintenance downtime database supporting the MongoDB v3.0 protocol and drivers. It extends MongoDB with PerconaFT and MongoRocks storage engines, as well as several enterprise-grade features:

NOTE: PerconaFT was deprecated and is not available in later versions. TokuBackup was replaced with Hot Backup for WiredTiger and MongoRocks storage engines.

Percona Server for MongoDB requires no changes to MongoDB applications or code.

This release is based on MongoDB 3.0.15 and includes the following additional change:

Percona Server for MongoDB 3.0.15-1.10 release notes are available in the official documentation.

by Alexey Zhebel at May 26, 2017 05:28 PM

Percona Server for MySQL 5.7.18-15 is Now Available

Percona Server for MySQL 5.7.18-15

Percona Server for MySQL 5.7.18-15Percona announces the GA release of Percona Server for MySQL 5.7.18-15 on May 26, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.7.18, including all the bug fixes in it, Percona Server for MySQL 5.7.18-15 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.18-15 milestone at Launchpad.

Bugs Fixed:
  • The server would crash when querying partitioning table with a single partition. Bug fixed #1657941 (upstream #76418).
  • Running a query on InnoDB table with ngram full-text parser and a LIMIT clause could lead to a server crash. Bug fixed #1679025 (upstream #85835).

The release notes for Percona Server for MySQL 5.7.18-15 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

by Hrvoje Matijakovic at May 26, 2017 05:05 PM

May 25, 2017

Peter Zaitsev

What About ProxySQL and Mirroring?

ProxySQL and Mirroring

In this blog post, we’ll look at how ProxySQL and mirroring go together.

Overview

Let me be clear: I love ProxySQL, and I think it is a great component for expanding architecture flexibility and high availability. But not all that shines is gold! In this post, I want to correctly set some expectations, and avoid selling carbon for gold (carbon has it’s own uses, while gold has others).

First of all, we need to cover the basics of how ProxySQL manages traffic dispatch (I don’t want to call it mirroring, and I’ll explain further below).

ProxySQL receives a connection from the application, and through it we can have a simple SELECT or a more complex transaction. ProxySQL gets each query, passes them to the Query Processor, processes them, identifies if a query is mirrored, duplicates the whole MySQL session ProxySQL internal object and associates it to a mirror queue (which refer to a mirror threads pool). If the pool is free (has an available active slot in the concurrent active threads set) then the query is processed right away. If not, it will stay in the queue. If the queue is full, the query is lost.

Whatever is returned from the query goes to /dev/null, and as such no result set is passed back to the client.

The whole process is not free for a server. If you check the CPU utilization, you will see that the “mirroring” in ProxySQL actually doubles the CPU utilization. This means that the traffic on server A is impacted because of resource contention.

Summarizing, ProxySQL will:

  1. Send the query for execution in different order
  2. Completely ignore any transaction isolation
  3. Have different number of query executed on B with respect to A
  4. Add significant load on the server resources

This point, coupled with the expectations I mention in the reasoning at the end of this article, it is quite clear to me that at the moment we cannot consider ProxySQL as a valid mechanism to duplicate a consistent load from server A to server B.

Personally, I don’t think that the ProxySQL development team (Rene :D) should waste time on fixing this issue, as there are so many other things to cover and improve on in ProxySQL.

After working extensively with ProxySQL, and doing a deep QA on mirroring, I think that either we keep it as basic blind traffic dispatcher. Otherwise, a full re-conceptualization is required. But once we have clarified that, ProxySQL “traffic dispatch” (still don’t want to call it mirroring) remains a very interesting feature that can have useful applications – especially since it is easy to setup.

The following test results should help set the correct expectations.

The tests were simple: load data in a Percona XtraDB Cluster and use ProxySQL to replicate the load on a MySQL master-slave environment.

  • Machines for MySQL/Percona XtraDB Cluster: VM with CentOS 7, 4 CPU 3 GB RAM, attached storage
  • Machine for ProxySQL: VM CentOS 7, 8 CPU 8GB RAM

Why did I choose to give ProxySQL a higher volume of resources? I knew in advance I could need to play a bit with a couple of settings that required more memory and CPU cycles. I wanted to be sure I didn’t get any problems from ProxySQL in relation to CPU and memory.

The application that I was using to add load is a Java application I develop to perform my tests. The app is at https://github.com/Tusamarco/blogs/blob/master/stresstool_base_app.tar.gz, and the whole set I used to do the tests are here:  https://github.com/Tusamarco/blogs/tree/master/proxymirror.

I used four different tables:

+------------------+
| Tables_in_mirror |
+------------------+
| mirtabAUTOINC    |
| mirtabMID        |
| mirtabMIDPart    |
| mirtabMIDUUID    |

Ok so let start. Note that the meaningful tests are the ones below. For the whole set, refer to the whole set package. First setup ProxySQL:

First setup ProxySQL:

delete from mysql_servers where hostgroup_id in (500,501,700,701);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',500,3306,60000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.5',501,3306,100,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.21',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.231',501,3306,20000,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',700,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.7',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.25',701,3306,1,400);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('192.168.0.43',701,3306,1,400);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
delete from mysql_users where username='load_RW';
insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) values ('load_RW','test',1,500,'test',1);
LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;
delete from mysql_query_rules where rule_id=202;
insert into mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,apply) values(202,'load_RW',500,700,1,3,1);
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;

Test 1

The first test is mainly a simple functional test during which I insert records using one single thread in Percona XtraDB Cluster and MySQL. No surprise, here I have 3000 loops and at the end of the test I have 3000 records on both platforms.

To have a baseline we can see that the ProxySQL CPU utilization is quite low:

ProxySQL and Mirroring

At the same time, the number of “questions” against Percona XtraDB Cluster and MySQL very similar:

Percona XtraDB Cluster

ProxySQL and Mirroring

MySQL

ProxySQL and Mirroring

The other two metrics we want to keep an eye on are Mirror_concurrency and Mirror_queue_length. These two refer respectively to mysql-mirror_max_concurrency and mysql-mirror_max_queue_length:

ProxySQL and Mirroring

These two new variables and metrics were introduced in ProxySQL 1.4.0, with the intent to control and manage the load ProxySQL generates internally related to the mirroring feature. In this case, you can see we have a max of three concurrent connections and zero queue entries (all good).

Now that we have a baseline, and that we know at functional level “it works,” let see what happens when increasing the load.

Test 2

The scope of the test was identifying how ProxySQL behaves with a standard configuration and increasing load. It comes up that as soon as ProxySQL has a little bit more load, it starts to lose some queries along the way.

Executing 3000 loops for 40 threads only results in 120,000 rows inserted in all the four tables in Percona XtraDB Cluster. But the table in the secondary (mirrored) platform only has a variable number or inserted rows, between 101,359 and 104,072. This demonstrates consistent loss of data.

After reviewing and comparing the connections running in Percona XtraDB Cluster and the secondary, we can see that (as expected) Percona XtraDB Cluster’s number of connections is scaling and serving the number of incoming requests, while the connections on the secondary are limited by the default value of mysql-mirror_max_concurrency=16.

ProxySQL and Mirroring

Is also interesting to note that the ProxySQL transaction process queue maintains its connection to the Secondary longer than the connection to Percona XtraDB Cluster.

ProxySQL and Mirroring

As we can see above, the queue is an evident bell curve that reaches 6K entries (which is quite below the mysql-mirror_max_queue_length limit (32K)). Yet queries were dropped by ProxySQL, which indicates the queue is not really enough to accommodate the pending work.

ProxySQL and Mirroring

CPU-wise, ProxySQL (as expected) take a few more cycles, but nothing crazy. The overhead for the simple mirroring queue processing can be seen when the main load stops around 12:47.

Another interesting graph to keep an eye on is the one describing the executed commands inside Percona XtraDB Cluster and the secondary:

Percona XtraDB Cluster

ProxySQL and Mirroring

Secondary

ProxySQL and Mirroring

As you can see, the traffic on the secondary was significantly less (669 on average, compared to Percona XtraDB Cluster’s 1.17K). Then it spikes when the main load on the Percona XtraDB Cluster node terminates. In short it is quite clear that ProxySQL is not able to scale following the traffic existing in Percona XtraDB Cluster, and actually loses a significant amount of data on the secondary.

Doubling the load in Test 3 shows the same behavior, with ProxySQL reaches its limit for traffic duplication.

But can this be optimized?

The answer is, of course, yes! This is what the mysql-mirror_max_concurrency is for, so let;’s see what happens if we increase the value from 16 to 100 (just to make it crazy high).

Test 4 (two app node writing)

The first thing that comes to attention is that both Percona XtraDB Cluster and secondary report the same number of rows in the tables (240,000). That is a good first win.

Second, note the number of running connections:

ProxySQL and Mirroring

The graphs are now are much closer, and the queue drops to just a few entries.

Commands executed in Percona XtraDB Cluster:

And commands executed in the secondary:

Average execution reports the same value, and very similar trends.

Finally, what was the CPU cost and effect?

Percona XtraDB Cluster and secondary CPU utilization:

     

As expected, some difference in the CPU usage distribution exists. But the trend is consistent between the two nodes, and the operations.

The ProxySQL CPU utilization is definitely higher than before:

But it’s absolutely manageable, and still reflects the initial distribution.

What about CRUD? So far I’ve only tested the insert operation, but what happen if we run a full CRUD set of tests?

Test 7 (CRUD)

First of all, let’s review the executed commands in Percona XtraDB Cluster:

And the secondary:

While in appearance we have very similar workloads, selects aside the behavior will significantly diverge. This is because in the secondary the different operations are not encapsulated by the transaction. They are executed as they are received. We can see a significant difference in update and delete operations between the two.

Also, the threads in the execution show a different picture between the two platforms:

Percona XtraDB Cluster

Secondary

It appears quite clear that Percona XtraDB Cluster is constantly running more threads and more connections. Nevertheless, both platforms process a similar total number of questions:

Percona XtraDB Cluster

Secondary

Both have an average or around 1.17K/second questions.

This is also another indication of how much the impact of concurrent operation on behavior, with no respect to the isolation or execution order. Below we can clearly see different behavior by reviewing the CPU utilization:

Percona XtraDB Cluster

Secondary

Conclusions

To close this article, I want to go back to the start. We cannot consider the mirror function in ProxySQL as a real mirroring, but more as traffic redirection (check here for more reasoning on mirroring from my side).

Using ProxySQL with this approach is still partially effective in testing the load and the effect it has on a secondary platform. As we know, data consistency is not guaranteed in this scenario, and Selects, Updates and Deletes are affected (given the different data-set and result-set they manage).

The server behaviors change between the original and mirror, if not in the quantity or the quality.

I am convinced that when we need a tool able to test our production load on a different or new platform, we would do better to look to something else. Possibly query Playback, recently reviewed and significantly patched by DropBox (https://github.com/Percona-Lab/query-playback).

In the end, ProxySQL is already a cool tool. If it doesn’t cover mirroring well, I can live with that. I am interested in having it working as it should (and it does in many other functionalities).

Acknowledgments

As usual, to Rene, who worked on fixing and introducing new functionalities associated with mirroring, like queue and concurrency control.

To the Percona team who developed Percona Monitoring and Management (PMM): all the graphs here (except 3) come from PMM (some of them I customized).

by Marco Tusa at May 25, 2017 10:45 PM

MariaDB AB

Using MariaDB MaxScale 2.1 Regex Filter for Migrations

Using MariaDB MaxScale 2.1 Regex Filter for Migrations anderskarlsson4 Thu, 05/25/2017 - 13:13

Migrating applications from one database system to another is sometimes easy and sometimes not. But they are hardly ever effortless. Among the obvious issues are schema and data, migrating from one datatype to another, with slightly different behavior and semantics is one thing and another is migrating the actual data, is it UTF8 and if so how many bytes? What is the collation? What is the required accuracy of numeric types?

And on top of this are things such as triggers, stored procedures and such. Not to mention performance tuning and the optimal way to construct SQL statements.

Speaking of SQL statements, we have application code also. Yes, most databases have some kind of application running on them, often more than one, and these access the database using SQL over some kind of API such as JDBC, ODBC or some proprietary driver. And application code, even simple SQL tends to have one or two database specific constructs in them, and that is what this blog is about.

Before moving on to that though, a few words on MariaDB Server 10.2.6 which is GA since May 23. MariaDB Server 10.2 does contain more than a few things that make migration from other database systems to MariaDB a lot easier. Among these features are:

  • CHECK constraints. The syntax for these has been supported before, but in MariaDB Server 10.2 these are actually implementing proper constraints.
  • DEFAULT values. In MariaDB Server before version 10.2, these were several restrictions around what DEFAULT values could be used, and how, but in 10.2 these are lifted.
  • Multiple triggers per event. In MariaDB Server before 10.2 you could only have one trigger per DML eevent, i.e. several BEFORE INSERT triggers. This has two advantages, one is the obvious one that the database you are migrating from might be supporting multiple triggers per event. Another is that sometimes you want to add a trigger to implement some compatibility with the database system you are migrating from, and this feature makes doing this a lot easier.

With that said, let’s say you have migrated the schema and the procedures and what have you not, and also the data. Then you have replaced that ODBC driver from the existing database system with one from MariaDB, which means we are all set to try the application. And the application falls over on the first SQL statement because it uses some proprietary feature of the database we are migrating from. There are some ways of getting around that, with MariaDB there are two ways that have been used in the past:

  • Use MariaDB compatibility features. As stated above, there are many new compatibility features in MariaDB Server 10.2.6 GA. In addition there are some settings for the SQL_MODE parameter for compatibility, such as the PIPES_AS_CONCAT that ensures that the ANSI SQL concatenation operator, two pipes (||), is interpreted as a MariaDB CONCAT.
  • Develop procedures, functions and user defined functions that mimic procedures and functions in other database system.

There is nothing wrong with the above means of migration, but they don’t cover all aspect of a migration. One more tool that is available now is the new MariaDB MaxScale 2.1.3 GA and there is a plugin that is particularly useful, the Regex one. What this allows us to do is to replace text in the SQL statement so that it matches something that MariaDB Server can work with, and a good example is the Oracle DECODE() function. This function is rather special, in a few ways:

  • It takes a variable number of arguments, from 3 and up.
  • The type of the return value depends on the type of the arguments.

The SQL Standard construct for this is the CASE statement, which has the same attributes as above. We cannot solve the use of the DECODE function by adding a STORED FUNCTION. A UDF (User Defined Function) is possible as this can take any number and type of arguments. Also even though a UDF can only return a predefined type, this is not a big issue as MariaDB is loosely typed, so we can always, for numeric results, return a numeric string. A bigger issue though is that MariaDB already has a DECODE function that does something else.

Also, we would really like to use the CASE function and a way to deal with that is to use the MariaDB MaxScale Regex filter. Let me show you how. To begin with, we need to set up the Regex filter itself, and the way I do it here, I will use multiple filters, one for each of the number of arguments I pass to DECODE. I guess there is some way of doing this in a smarter way, but here I am just showing the principle. Also note that the Regex filter use the PCRE2 regular expressions, not the Posix one. Let’s start with a couple of filter specification for a DECODE with 3 and 4 arguments and define them in our MariaDB MaxScale configuration file:

[DecodeFilter3]
type=filter
module=regexfilter
options=ignorecase
match=DECODE\(([^,)]*),([^,)]*),([^,)]*)\)
replace=CASE $1 WHEN $2 THEN $3 END

[DecodeFilter4]
type=filter
module=regexfilter
options=ignorecase
match=DECODE\(([^,)]*),([^,)]*),([^,)]*),([^,)]*)\)
replace=CASE $1 WHEN $2 THEN $3 ELSE $4 END

As anyone can see, the above really isn’t perfect, things like strings with embedded comas and what have you not will not work, but in the general case, this should work reasonable well, which is not to say you would want to use this in production, but for a test or a proof-of-concept this is good enough. For DECODE with 5, 6 or more arguments, you add these following the pattern above.
Before we show this in action, let me add one more useful filter for the Oracle SYSDATE psedocolumn. In Oracle SQL, SYSDATE is the same as NOW() in MariaDB, so this is a simple replacement, but as SYSDATE is a pseudocolumn and not a function, like NOW(), we cannot write a simple Stored Function to handle it, but using a MaraiaDB MaxScale filter should do the trick, like this:

[sysdate]
type=filter
module=regexfilter
options=ignorecase
match=([^[:alpha:]])SYSDATE
replace=$1NOW()


With this, it is now time to enable these filters, and that is done by adding them to the Service in MariaDB MaxScale which we will use:

[Read-Write Service]
type=service
router=readwritesplit
servers=srv1
user=rwuser
passwd=rwpwd
max_slave_connections=100%
filters=DecodeFilter3|DecodeFilter4|sysdate


Assuming you have your MariaDB MaxScale correctly configured in any other place, let’s see if this works as expected. First, we have to restart MariaDB MaxScale and then when we connect to MariaDB and do a call to DECODE the way it looks like in Oracle and see what is returned:

$ mysql -h moe -P 4008 -u theuser -pthepassword
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 21205
Server version: 10.0.0 2.1.3-maxscale MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB> SELECT DECODE(1, 2, 3, 4) FROM dual;
+------------------------------------+
| CASE 1 WHEN  2 THEN  3 ELSE  4 END |
+------------------------------------+
|                                  4 |
+------------------------------------+
MariaDB> SELECT DECODE('Str1', 'Str1', 'Was str1', 'Not str1') FROM dual;
+----------------------------------------------------------------+
| CASE 'Str1' WHEN  'Str1' THEN  'Was str1' ELSE  'Not str1' END |
+----------------------------------------------------------------+
| Was str1                                                       |
+----------------------------------------------------------------+’
MariaDB> SELECT DECODE('Str1', 'Str2', 'Was str1') FROM dual;
+-----------------------------------------------+
| CASE 'Str1' WHEN  'Str2' THEN  'Was str1' END |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+


As can be seen, the translation from DECODE to a CASE statement seems to work as expected. Let’s also try with SYSDATE

MariaDB> SELECT DECODE('Today', 'Today', SYSDATE, 'Some other day') FROM dual;
+-------------------------------------------------------------------+
| CASE 'Today' WHEN  'Today' THEN  NOW() ELSE  'Some other day' END |
+-------------------------------------------------------------------+
| 2017-05-19 18:51:22                                               |
+-------------------------------------------------------------------+


As we see here, not only does SYSDATE work as expected, we can handle both DECODE and SYSDATE conversions as the filters are piped to each other. Using MariaDB MaxScale with the Regex filter is yet another tool for migrating applications.

Happy SQL’ing
/Karlsson

MariaDB MaxScale includes multiple filters, and one of the most useful, flexible and easiest to use is the regex filter and in this blog we will look at how this can be used to transform SQL statements that aren't 100% compatible with MariaDB.

Login or Register to post comments

by anderskarlsson4 at May 25, 2017 05:13 PM

May 24, 2017

Peter Zaitsev

Percona Software and Roadmap Update with CEO Peter Zaitsev: Q2 2017

Percona Software and Services

This blog post is a summary of the Percona Software and Roadmap Update – Q2 2017 webinar given by Peter Zaitsev on May 4, 2017. This webinar reflects changes and updates since the last update (Q1 2017).

A full recording of this webinar, along with the presentation slide deck, can be found here.

Percona Software

Below are the latest and upcoming features in Percona’s software. All of Percona’s software is 100% free and open source, with no restricted “Enterprise” version. Percona doesn’t restrict users with open core or “open source, eventually” (BSL) licenses.

Percona Server for MySQL 5.7

Latest Improvements

Features About To Be Released 

  • Integration of TokuDB and Performance Schema
  • MyRocks integration in Percona Server
  • Starting to look towards MySQL 8

Percona XtraBackup 2.4

Latest Improvements

Percona Toolkit

Latest Improvements

Percona Server for MongoDB 3.4

Latest Improvements

Percona XtraDB Cluster 5.7

Latest Improvements

Performance Improvement Benchmarks

Below, you can see the benchmarks for improvements to Percona XtraDB Cluster 5.7 performance. You can read about the improvements and benchmark tests in more detail here and here.

Percona Software and Roadmap Update

Percona XtraDB Cluster 5.7 Integrated with ProxySQL 1.3

Percona Monitoring and Management

New in Percona Monitoring and Management

Advanced MariaDB Dashboards in PMM (Links go to PMM Demo)

Percona Q217 Roadmap 4

Improved MongoDB Dashboards in PMM (Links go to PMM Demo)

Percona Q217 Roadmap 7

Percona Q217 Roadmap 9

Percona Q217 Roadmap 10

Check out the PMM Demo

Thanks for tuning in for an update on Percona Software and Roadmap Update – Q2 2017.

New Percona Online Store – Easy to Buy, Pay Monthly

by Dave Avery at May 24, 2017 10:24 PM

May 23, 2017

MariaDB AB

What's New in MariaDB MaxScale 2.1

What's New in MariaDB MaxScale 2.1 Dipti Joshi Tue, 05/23/2017 - 19:44

We are happy to announce the 2.1 GA release of MariaDB MaxScale, the next-generation database proxy for MariaDB.

MariaDB MaxScale 2.1 introduces the following key new capabilities:

Dynamic Configuration

  • Server, monitor and listeners: MaxScale 2.1 supports dynamic configuration of servers, monitors and listeners, which can be added, modified or removed during runtime. A set of new commands were added to maxadmin.

  • Database firewall filter: Rules can now be modified during runtime using the new module commands introduced in this release.

  • Persistent configuration changes: The runtime configuration changes are immediately applied to the running MaxScale as well as persisted using the new hierarchical configuration architecture.

Security

  • Selective data masking: Meet your HIPAA and PCI compliance needs by obfuscating sensitive data using the new masking filter.

  • Result set limiting: Prevent access to large sets of data with a single query by using maxrows filter - securing your database servers against malicious or accidental DoS attack.

  • Secured single sign-on: MariaDB MaxScale now supports LDAP/GSSAPI authentication support.

  • Prepared statement filtering by database firewall: The database firewall filter now applies the filtering rules to prepared statements as well.

  • Function filtering by database firewall: Now the database firewall filter adds a rule to whitelist or blacklist a query based on presence of a function.

  • Secure binlog server: The binlog cache files on MaxScale can now be encrypted. MaxScale binlog server also uses SSL in communication with master and slave.

Query Performance

  • Query cache filter: MariaDB MaxScale 2.1 now allows caching of query results in MaxScale for a configurable timeout. If a query is in cache, MaxScale will return results from cache before going to server to fetch query results. Our internal testing has shown 2.8x performance improvement from 2.0 to 2.1.3 using cache filter.

  • Streaming insert plugin: A new plugin in MariaDB MaxScale 2.1 converts all INSERT statements done inside an explicit transaction into LOAD DATA LOCAL INFILE.

Scalability

  • Aurora Cluster support: MariaDB MaxScale can now be used as a proxy for Amazon Aurora Cluster. Newly added monitor detects read replicas and write node in Aurora Cluster, and supports launchable scripts on monitored events like other monitors.

  • Multi-master for MySQL monitor: Now MariaDB MaxScale can detect complex multi-master replication topologies for MariaDB and MySQL environment.

  • Failover mode for MySQL monitor: For a two-node master-slave cluster, MariaDB MaxScale now allows slave to act as a master in case the original master fails.

  • Read-write splitting with master pinning: MariaDB MaxScale 2.1 introduces a new “Consistent Critical Read Filter.” This filter detects a statement that would modify the database and route all subsequent statements to the master server where data is guaranteed to be in an up-to-date state.

Thanks to community members and especially OutboundEngine who beta tested MariaDB MaxScale 2.1.0 to MariaDB MaxScale 2.1.2 in order for us to reach GA with MariaDB MaxScale 2.1.3.

Links:

Please post your questions in our Knowledge Base or email me at dipti.joshi@mariadb.com.

We are happy to announce the 2.1 GA release of MariaDB MaxScale, the next-generation database proxy for MariaDB.

Login or Register to post comments

by Dipti Joshi at May 23, 2017 11:44 PM

Monty Says

MariaDB 10.2 GA released with several advanced features

MariaDB 10.2.6 GA is now released. It's a release where we have concentrated on adding new advanced features to MariaDB

The most noteworthy ones are:
  • Windows Functions gives you the ability to do advanced calculation over a sliding window.
  • Common table expressions allows you to do more complex SQL statements without having to do explicit temporary tables.
  • We finally have a DEFAULT clause that can take expressions and also CHECK CONSTRAINT.
  • Multiple triggers for the same event. This is important for anyone trying to use tools, like pt-online-schema-change, which requires multiple triggers for the same table.
  • A new storage engine, MyRocks, that gives you high compression of your data without sacrificing speed. It has been developed in cooperation with Facebook and MariaDB to allow you to handle more data with less resources.
  • flashback, a feature that can rollback instances/databases/tables to an old snapshot. The version in MariaDB 10.2 is DML only. In MariaDB 10.3 we will also allow rollback over DML (like DROP TABLE).
  • Compression of events in the binary log.
  • JSON functions added. In 10.2.7 we will also add support for CREATE TABLE ... (a JSON).
A few smaller but still noteworthy new features:
  • Connection setup was made faster by moving creation of THD to a new thread. This, in addition with better thread caching, can give a connection speedup for up to 85 % in some cases.
  • Table cache can automatically partition itself as needed to reduce the contention.
  • NO PAD collations, which means that end space are significant in comparisons.
  • InnoDB is now the default storage engine. Until MariaDB 10.1, MariaDB used the XtraDB storage engine as default. XtraDB in 10.2 is not up to date with the latest features of InnoDB and cannot be used. The main reason for this change is that most of the important features of XtraDB are nowadays implemented in InnoDB . As the MariaDB team is doing a lot more InnoDB development than ever before, we can't anymore manage updating two almost identical engines. The InnoDB version in MariaDB contains the best features of MySQL InnoDB and XtraDB and a lot more. As the InnoDB on disk format is identical to XtraDB's this will not cause any problems when upgrading to MariaDB 10.2
  • The old GPL client library is gone; now MariaDB Server comes with the LGPL Connector/C client library.

There are a lot of other new features, performance enhancements and variables in MariaDB 10.2 for you to explore!

I am happy to see that a lot of the new features have come from the MariadB community! (Note to myself; This list doesn't include all contributors to MariadB 10.2, needs to be update.)

Thanks a lot to everyone that has contributed to MariaDB!

by Michael "Monty" Widenius (noreply@blogger.com) at May 23, 2017 10:35 PM

Peter Zaitsev

How to Save and Load Docker Images to Offline Servers

Docker Images

Docker ImagesIn this post, we’ll see how to make Docker images available to servers that don’t have access to the Internet (i.e., machines where docker pull <image_name> does not work).

As a specific example, we will do this with the latest Percona Monitoring and Management Docker images, since we had requests for this from users and customers. With the following steps, you’ll be able to deploy PMM within your secure network, without access to the Internet. Additionally, the same steps can be used when you need to upgrade the containers’ version in future releases.

There are two ways in which we can do this:

  • the easy way, by using docker save and docker load, or
  • the not-so-easy way, by setting up our own registry

We’ll focus on the first option, since the latter is a bit more convoluted. If you need your own registry, you are probably looking into something else rather than simply avoiding a firewall to pull one image to a server. Check out the Docker online docs in case option two fits your needs better.

As of this writing, 1.1.3 is the latest PMM version, so this is what we’ll use in the example. An image name is comprised of three parts, namely:

  • user_account/ (note the ‘/’ at the end); or empty string (and no ‘/’) for the official Docker repo
  • image_name
  • :tag (note the ‘:’ at the beginning)

The PMM Docker images have the following syntax: percona/pmm-server:1.1.3, but you can change this in the following examples to whatever image name you want, and it will work just the same. Before moving on to the commands needed, let’s imagine that serverA is the machine that has access to the Internet and serverB is the machine behind the firewall.

The steps are simple enough. On serverA, get the image, and save it to a file:

serverA> docker pull percona/pmm-server:1.1.3
1.1.3: Pulling from percona/pmm-server
45a2e645736c: Pull complete
7a3c6f252004: Pull complete
2cc1d8878ff1: Pull complete
6c49ea4e9955: Pull complete
bc4630d3a194: Pull complete
75f0952c00bd: Pull complete
79d583a1689c: Pull complete
5a820193ac79: Pull complete
927a0614b164: Pull complete
Digest: sha256:5310b23066d00be418a7522c957b2da4155a63c3e7b08663327aef075674bc2e
Status: Downloaded newer image for percona/pmm-server:1.1.3
serverA> docker save percona/pmm-server:1.1.3 > ~/pmm-server_1.1.3.tar

Now, all you need to do is move the generated tar file to serverB (by using “scp” or any other means), and execute the following:

serverB> docker load < ~/pmm-server_1.1.3.tar
serverB> docker images
REPOSITORY           TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
percona/pmm-server   1.1.3               acc9af2459a4        3 weeks ago         1.146 GB

Now you’ll be able to use the image as if you had used docker pull percona/pmm-server:1.1.3​:

serverB> docker create ... percona/pmm-server:1.1.3 /bin/true
301a9e89ee95886f497482038aa6601d6cb2e21c0532e1077fa44213ef597f38
serverB> docker run -d ... percona/pmm-server:1.1.3
dbaffa80f62bc0b80239b922bbc746d828fbbeb212a638cfafea92b827141abb
serverB> curl http://localhost | grep "Percona Monitoring and Management"
...
                   <p>Percona Monitoring and Management (PMM) is a free and open-source
solution for managing and monitoring performance on MySQL and MongoDB, and provides
time-based analysis of performance to ensure that your data works as efficiently as
possible.</p>
...

Lastly, let me add the relevant documentation links, so you have them at hand, if needed:

https://www.percona.com/doc/percona-monitoring-and-management/deploy/server/docker.html

https://docs.docker.com/engine/reference/commandline/save/

https://docs.docker.com/engine/reference/commandline/load/

by Agustín at May 23, 2017 10:16 PM

MariaDB AB

What's New in MariaDB Server 10.2

What's New in MariaDB Server 10.2 RalfGebhardt Tue, 05/23/2017 - 17:03

We are happy to announce the general availability (GA) of MariaDB Server 10.2! MariaDB Server 10.2 is the newest major version of MariaDB Server, the fastest growing open source relational database.

MariaDB Server 10.2 is the next evolution after MariaDB Server 10.1. In 10.1 the integration of Galera Cluster as a high availability solution, data-at-rest encryption and other security features like the password validation API have been the key enhancements of MariaDB Server.

Now, with MariaDB Server 10.2.6 GA, new significant enhancements are available for our users and customers, including:

  • SQL enhancements like window functions, common table expressions and JSON functions allow new use cases for MariaDB Server

  • Standard MariaDB Server replication has further optimizations

  • Many area limitations have been removed, which allows easier use and there is no need for limitation handling on the application level

  • MyRocks, a new storage engine developed by Facebook, has been introduced, which will further enrich the use cases for MariaDB Server

Window Functions

Window functions are popular in Business Intelligence (BI) where more complex report generation is needed based on a subset of the data, like country or sales team metrics. Another common use case is where time-series based data should be aggregated based on a time window instead of just a current record, like all rows inside a certain time span.

As analytics is becoming more and more important to end users, window functions deliver a new way of writing performance optimized analytical SQL queries, which are easy to read and maintain, and eliminates the need to write expensive subqueries and self-joins.

Common Table Expressions

Hierarchical and recursive queries are usually implemented using common table expressions (CTEs). They are similar to derived tables in a FROM clause, but by having an identification keyword WITH, the optimizer can produce more efficient query plans. Acting as an automatically created temporary and named result set, which is only valid for the time of the query, it can be used for recursive and hierarchical execution, and also allows for reuse of the temporary dataset. Having a dedicated method also helps to create more expressive and cleaner SQL code.

JSON Functions

JSON (JavaScript Object Notation), a text-based and platform independent data exchange format, is used not only to exchange data, but also as a format to store unstructured data. MariaDB Server 10.2 offers more than 24 JSON functions to allow querying, modification, validation and indexing of JSON formated data, which is stored in a text-based field of a database. As a result, the powerful relational model of MariaDB can be enriched by working with unstructured data, where required.

Through the use of virtual columns, the JSON function, JSON_VALUE and the newest indexing feature of MariaDB Server 10.2 on virtual columns, JSON values will be automatically extracted from the JSON string, stored in a virtual column and indexed providing the fastest access to the JSON string.

Using the JSON function JSON_VALID, the new CHECK CONSTRAINTS in MariaDB Server 10.2 guarantee that only JSON strings of the correct JSON format can be added into a field.

Binary Log Based Rollback

The enhanced mysqlbinlog utility delivered with MariaDB Server 10.2 includes a new point-in-time rollback function, which allows a database or table to revert to an earlier state, and delivers binary log based rollback of already committed data. The tool mysqlbinlog is not directly modifying any data, it is generating an “export file” including the reverted statements of the transactions, logged in a binary log file. The created file can be used with the command line client or other SQL tool to execute the included SQL statements. This way all committed transactions up to a given timestamp will be rolled back.

In the case of addressing logical mistakes like adding, changing or deleting data, so far the only possible way has been to use mysqlbinlog to review transactions and fix the problems manually. However, this often leads to data inconsistency because corrections typically only address the wrong statement, thereby ignoring other data dependencies.

Typically caused by DBA or user error, restoring a huge database can result in a significant outage of service. Rolling back the last transactions using point-in-time roll back takes only the time of the extract, a short review and the execution of the reverted transactions – saving valuable time, resources and service.

 

Start now and learn about the newest evolution of MariaDB Server 10.2

We are happy to announce the general availability (GA) of MariaDB Server 10.2! MariaDB Server 10.2 is the newest major version of MariaDB Server, the fastest growing open source relational database.

Login or Register to post comments

by RalfGebhardt at May 23, 2017 09:03 PM

Say Hello to MariaDB TX

Say Hello to MariaDB TX Shane Johnson Tue, 05/23/2017 - 15:49

We believe an enterprise database solution requires technology, tools and services, and that it should be easy to buy, easy to deploy and easy to manage – providing a great customer and user experience from beginning to end. 

In fact, we've made ease of use a central theme in our roadmap, and with the introduction of MariaDB TX today, we're taking another first step – packaging MariaDB technology, tools and services into a unified offering to help customers succeed with MariaDB infrastructure: MariaDB Server, MariaDB MaxScale and MariaDB Cluster.

In addition to introducing MariaDB TX, we are releasing MariaDB Server 10.2 and MariaDB MaxScale 2.1.

MariaDB-Product Overview 17 May 2017 (1).png

MariaDB Server, MariaDB MaxScale and MariaDB Cluster (Galera Cluster for MariaDB), along with MariaDB connectors and drivers, form the base technology in MariaDB TX. By bringing them together, we are building a modular, integrated platform rather than separate, independent products – making them easier to deploy, easier to use and easier to manage ... together.

In addition, MariaDB TX includes tools for administration (SQLyog for MariaDB), monitoring (Monyog for MariaDB) and backup/restore (MariaDB Backup) as well as notification services (e.g., security alerts). In addition, we're creating and investing in more innovative tools for future release, including MariaDB Replication Manager (MRM). 

And finally, to ensure customer success and satisfaction with MariaDB TX, we provide expert services for everything from database administration to enterprise architecture to migration management. In addition to technical support, customers can choose to extend their team with a remote DBA, a dedicated enterprise architect or migration project manager – resources with expert knowledge and experience.   

A great way to learn more about MariaDB TX 2.0, including the new features in MariaDB Server 10.2 and MariaDB MaxScale 2.1, is to join our upcoming launch webinar

If you want to dive right in, keep reading!

 

MariaDB TX 2.0

Product highlights

  • A comprehensive set of JSON functions in a relational database
  • An SSD optimized storage engine with unrivaled performance and efficiency
  • An advanced database firewall, complete with data masking

With MariaDB Server 10.2 and MaxScale 2.1, MariaDB TX 2.0 raises the completeness, compatibility, performance, scalability, security and disaster recovery of MariaDB – setting a new standard for open source database solutions in the enterprise. 

Completeness and compatibility

First, we wanted to increase SQL completeness and schema compatibility. If you choose MariaDB TX over Oracle Database or Microsoft SQL Server, you should be able to perform similar queries on similar schemas. And while many of these features have long been available in proprietary databases, we're excited to make them available in an open source database. 

Completeness

Compatibility

  • Common table expressions

  • Window functions

  • JSON and GeoJSON functions

  • EXECUTE IMMEDIATE statements

  • Subqueries within views

  • Multiple temp tables per query

  • CHECK constraints with expressions

  • DEFAULT values for BLOB/TEXT

  • DECIMAL columns up to 38 places

  • Multiple triggers per type per table

Performance and scalability

Next, we wanted to increase performance and scalability, focusing on storage, replication, querying and routing. At scale, improving storage efficiency and reducing disk IO not only improves performance, it reduces costs. Today, that means optimizing for SSDs, which is why we're introducing MyRocks, a storage engine developed by Facebook for web-scale use cases.

Storage

Querying

  • MyRocks storage engine

  • InnoDB enhancements

  • InnoDB NUMA interleave

  • Virtual Column indexes

  • Fast connections

  • Optimizer enhancements

  • Query caching

  • Streaming inserts

Replication

Routing

  • Binary Log read throttling

  • Binary Log compression

  • Dynamic server configuration

  • Read-write splitting + master pinning

  • Multi-statement routing + master pinning

Security and disaster recovery

Finally, we wanted to increase security and disaster recovery. In particular, by expanding the capabilities of the database firewall by ensuring sensitive data was protected, prepared statements were examined and denial of service queries were prevented. For disaster recovery, we introduce point-in-time rollback a la Oracle Flashback.

Security

Disaster recovery

  • Per user resource limits

  • Enforced TLS connections

  • Data masking

  • Prepared Statement filtering

  • Result Set limiting

  • Dynamic firewall rule configuration

  • Delayed replication
  • Binary Log based rollback

Resources

We believe an enterprise database solution requires technology, tools and services, and that it should be easy to buy, easy to deploy and easy to manage – providing a great customer and user experience from beginning to end. That's why we're introducing MariaDB TX.

Login or Register to post comments

by Shane Johnson at May 23, 2017 07:49 PM

MariaDB Foundation

MariaDB 10.2.6 Stable now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.2.6 Stable (GA). This is the first stable (GA) release in the MariaDB 10.2 series. See the What is MariaDB 10.2? page for more details on the new features in MariaDB 10.2. Download MariaDB 10.2.6 Release Notes Changelog What is MariaDB 10.2? MariaDB […]

The post MariaDB 10.2.6 Stable now available appeared first on MariaDB.org.

by Daniel Bartholomew at May 23, 2017 02:37 PM

May 22, 2017

Peter Zaitsev

ICP Counters in information_schema.INNODB_METRICS

ICP Counters

ICP CountersIn this blog, we’ll look at ICP counters in the information_schema.INNODB_METRICS. This is part two of the Index Condition Pushdown (ICP) counters blog post series. 

As mentioned in the previous post, in this blog we will look at how to check on ICP counters on MySQL and Percona Server for MySQL. This also applies to MariaDB, since the INNODB_METRICS table is also available for MariaDB (as opposed to the Handler_icp_% counters being MariaDB-specific). We will use the same table and data set as in the previous post.

For simplicity we’ll show the examples on MySQL 5.7.18, but they also apply to the latest Percona Server for MySQL (5.7.18) and MariaDB Server (10.2.5):

mysql [localhost] {msandbox} (test) > SELECT @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.7.18    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t1G
*************************** 1. row ***************************
      Table: t1
Create Table: CREATE TABLE `t1` (
 `f1` int(11) DEFAULT NULL,
 `f2` int(11) DEFAULT NULL,
 `f3` int(11) DEFAULT NULL,
 KEY `idx_f1_f2` (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  3999996 |
+----------+
1 row in set (3.98 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 LIMIT 12;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
|    3 |    1 |    1 |
|    3 |    2 |    1 |
|    3 |    3 |    1 |
|    3 |    4 |    1 |
+------+------+------+
12 rows in set (0.00 sec)

Before proceeding with the examples, let’s see what counters we have available and how to enable and query them. The documentation page is at the following link: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-metrics-table.html.

The first thing to notice is that we are advised to check the validity of the counters for each version where we want to use them. The counters represented in the INNODB_METRICS table are subject to change, so for the most up-to-date list it’s best to query the running MySQL server:

mysql [localhost] {msandbox} (test) > SELECT NAME, SUBSYSTEM, STATUS FROM information_schema.INNODB_METRICS WHERE NAME LIKE '%icp%';
+------------------+-----------+----------+
| NAME             | SUBSYSTEM | STATUS   |
+------------------+-----------+----------+
| icp_attempts     | icp       | disabled |
| icp_no_match     | icp       | disabled |
| icp_out_of_range | icp       | disabled |
| icp_match        | icp       | disabled |
+------------------+-----------+----------+
4 rows in set (0.00 sec)

Looking good! We have all the counters we expected, which are:

  • icp_attempts: the number of rows where ICP was evaluated
  • icp_no_match: the number of rows that did not completely match the pushed WHERE conditions
  • icp_out_of_range: the number of rows that were checked that were not in a valid scanning range
  • icp_match: the number of rows that completely matched the pushed WHERE conditions

This link to the code can be used for reference: https://github.com/mysql/mysql-server/blob/5.7/include/my_icp.h.

After checking which counters we have at our disposal, you need to enable them (they are not enabled by default). For this, we can use the “modules” provided by MySQL to group similar counters for ease of use. This is also explained in detail in the documentation link above, under the “Counter Modules” section. INNODB_METRICS counters are quite inexpensive to maintain, as you can see in this post by Peter Z.

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_enable = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, SUBSYSTEM, STATUS FROM information_schema.INNODB_METRICS WHERE NAME LIKE '%icp%';
+------------------+-----------+---------+
| NAME             | SUBSYSTEM | STATUS  |
+------------------+-----------+---------+
| icp_attempts     | icp       | enabled |
| icp_no_match     | icp       | enabled |
| icp_out_of_range | icp       | enabled |
| icp_match        | icp       | enabled |
+------------------+-----------+---------+
4 rows in set (0.00 sec)

Perfect, we now know what counters we need, and how to enable them. We just need to know how to query them, and we can move on to the examples. However, before rushing into saying that a simple SELECT against the INNODB_METRICS table will do, let’s step back a bit and see what columns we have available that can be of use:

mysql [localhost] {msandbox} (test) > DESCRIBE information_schema.INNODB_METRICS;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| NAME            | varchar(193) | NO   |     |         |       |
| SUBSYSTEM       | varchar(193) | NO   |     |         |       |
| COUNT           | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT       | double       | YES  |     | NULL    |       |
| COUNT_RESET     | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT_RESET | double       | YES  |     | NULL    |       |
| TIME_ENABLED    | datetime     | YES  |     | NULL    |       |
| TIME_DISABLED   | datetime     | YES  |     | NULL    |       |
| TIME_ELAPSED    | bigint(21)   | YES  |     | NULL    |       |
| TIME_RESET      | datetime     | YES  |     | NULL    |       |
| STATUS          | varchar(193) | NO   |     |         |       |
| TYPE            | varchar(193) | NO   |     |         |       |
| COMMENT         | varchar(193) | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

There are two types: %COUNT and %COUNT_RESET. The former counts since the corresponding counters were enabled, and the latter since they were last reset (we have the TIME_% columns to check when any of these were done). This is why in our examples we are going to check the %COUNT_RESET counters, so we can reset them before running each query (as we did with FLUSH STATUS in the previous post).

Without further ado, let’s check how this all works together:

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 AND (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    1 |
+------+------+------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%';
+------------------+-------------+
| NAME             | COUNT_RESET |
+------------------+-------------+
| icp_attempts     |           9 |
| icp_no_match     |           6 |
| icp_out_of_range |           1
| icp_match        |           2 |
+------------------+-------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND (f2 % 4) = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    8 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

If you checked the GitHub link above, you might have noted that the header file only contains three of the counters. This is because icp_attempts is computed as the sum of the rest. As expected, icp_match equals the number of returned rows, which makes sense. icp_no_match should also make sense if we check the amount of rows present without the WHERE conditions on f2.

mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
+------+------+------+
8 rows in set (0.00 sec)

So, 8 – 2 = 6, which is exactly icp_no_match‘s value. Finally, we are left with icp_out_of_range. For each end of range the ICP scan detects, this counter is incremented by one. We only scanned one range in the previous query, so let’s try something more interesting (scanning three ranges):

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE ((f1 < 2) OR (f1 > 4 AND f1 < 6) OR (f1 > 8 AND f1 < 12)) AND (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    5 |    1 |    1 |
|    9 |    1 |    1 |
|   10 |    1 |    1 |
|   11 |    1 |    1 |
+------+------+------+
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%';
+------------------+-------------+
| NAME             | COUNT_RESET |
+------------------+-------------+
| icp_attempts     |          23 |
| icp_no_match     |          15 |
| icp_out_of_range |           3 |
| icp_match        |           5 |
+------------------+-------------+
4 rows in set (0.01 sec)

We have now scanned three ranges on f1, namely: (f1 < 2), (4 < f1 < 6) and (8 < f1 < 12). This is correctly reflected in the corresponding counter. Remember that the MariaDB Handler_icp_attempts status counter we looked at in the previous post does not take into account the out-of-range counts. This means the two “attempts” counters will not be the same!

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp; SET GLOBAL innodb_monitor_reset = dml_reads; FLUSH STATUS;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE ((f1 < 2) OR (f1 > 4 AND f1 < 6) OR (f1 > 8 AND f1 < 12)) AND (f2 % 4) = 1;
...
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp_attempts';
+--------------+-------------+
| NAME         | COUNT_RESET |
+--------------+-------------+
| icp_attempts |          23 |
+--------------+-------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE 'Handler_icp_attempts';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Handler_icp_attempts | 20    |
+----------------------+-------+
1 row in set (0.00 sec)

It can be a bit confusing to have two counters that supposedly measure the same counts yielding different values, so watch this if you use MariaDB.

ICP Counters in PMM

Today you can find an ICP counters graph for MariaDB (Handler_icp_attempts) in PMM 1.1.3.

Additionally, in release 1.1.4 you’ll find graphs for ICP metrics from information_schema.INNODB_METRICS: just look for the INNODB_METRICS-based graph on the InnoDB Metrics dashboard!

I hope you found this blog post series useful! Let me know if you have any questions or comments below.

by Agustín at May 22, 2017 08:53 PM

Webinar May 23, 2017: MongoDB Monitoring and Performance for the Savvy DBA

MongoDB Monitoring

MongoDB MonitoringJoin Percona’s Senior Technical Services Engineer Bimal Kharel on Tuesday, May 23, 2017, as he presents a webinar on MongoDB monitoring called How to Help Your DBA’s Sleep Better at Night at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Are you trying to stay on top of your database before things turn ugly? Between metrics for throughput, database performance, resource utilization, resource saturation, errors (asserts) and many others, how do you know which one needs to be looked at NOW (and which can wait)?

Both DBAs and system admins must stay on top of the systems they manage. But filtering between metrics that need immediate attention and those that should be watched over time is challenging. In this webinar, Bimal narrows down the list of metrics that help you decide whether the on-call DBA gets their recommended eight hours of shuteye, or gets to run on caffeine with no sleep.

Bimal also discusses which graphs relate to each other, with examples from Percona’s Monitoring and Management (PMM) tool, to help you understand how things in MongoDB can impact other areas.

Please register for the webinar here.

MongoDB MonitoringBimal Kharel, Senior Technical Services Engineer, Percona

Bimal is a MongoDB support engineer at Percona. Before Percona he worked as a MongoDB DBA at EA and Charles Schwab. He has been in various roles throughout his career, from graphics to web developer to systems administration. MongoDB was the first database Bimal got into (he used MySQL for some websites but never other relational databases).

by Dave Avery at May 22, 2017 04:54 PM

May 19, 2017

Peter Zaitsev

Percona Toolkit 3.0.3 is Now Available

Percona Server for MongoDB

Percona ToolkitPercona announces the release of Percona Toolkit 3.0.3 on May 19, 2017.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

You download Percona Toolkit packages from the web site or install from official repositories.

This release includes the following changes:

New Features

  • Added the --skip-check-slave-lag option for pt-table-checksum, pt-online-schema-change, and pt-archiverdp.This option can be used to specify a list of servers where to skip checking for slave lag.
  • 1642754: Added support for collecting replication slave information in pt-stalk.
  • PT-111: Added support for collecting information about variables from Performance Schema in pt-stalk. For more information, see 1642753.
  • PT-116: Added the --[no]use-insert-ignore option for pt-online-schema-change to force or prevent using IGNORE on INSERT statements. For more information, see 1545129.

Bug Fixes

  • PT-115: Fixed OptionParser to accept repeatable DSNs.
  • PT-126: Fixed pt-online-schema-change to correctly parse comments. For more information, see 1592072.
  • PT-128: Fixed pt-stalk to include memory usage information. For more information, see 1510809.
  • PT-130: Fixed pt-mext to work with non-empty RSA public key. For more information, see 1587404.
  • PT-132: Fixed pt-online-schema-change to enable --no-drop-new-table when --no-swap-tables and --no-drop-triggers are used.

You can find release details in the release notes. Report bugs in Toolkit’s launchpad bug tracker.

by Alexey Zhebel at May 19, 2017 04:23 PM

May 17, 2017

Peter Zaitsev

MongoDB Authentication and Roles: Creating Your First Personalized Role

MongoDB Authentication and Roles

MongoDB Authentication and RolesIn this blog post, we’ll walk through the native MongoDB authentication and roles, and learn how to create personalized roles. It is a continuation of Securing MongoDB instances.

As said before, MongoDB features a few authentication methods and built-in roles that offer great control of both who is connecting to the database and what they are allowed to do. However, some companies have their own security policies that are often not covered by default roles. This blog post explains not only how to create personalized roles, but also how to grant minimum access to a user.

Authentication Methods

SCRAM-SHA-1 and MONGODB-CR are challenge-response protocols. All the users and passwords are saved encrypted in the MongoDB instance. Challenge-response authentication methods are widely used on the internet in several server-client software. These authentication methods do not send passwords as plain text to the server when the client is starting an authentication. Each new session has a different hash/code, which stops people from getting the password when sniffing the network.

The MONGODB-CR method was deprecated in version 3.0.

The x.509 authentication is an internal authentication that allows instances and clients to communicate to each other. All certificates are signed by the same Certificate Authority and must be valid. All the network traffic is encrypted by a given key, and it is only possible to read data with a valid certificate signed by such key.

MongoDB also offers external authentications such as LDAP and Kerberos. When using LDAP, users can log in to MongoDB using their centralized passwords. The LDAP application is commonly used to manage users and passwords in wide networks. Kerberos is a service that allows users to login only once, and then generates access tickets so that the users are allowed to access other services. Some configuration is necessary to use external authentication.

Built in roles

  • read: collStats,dbHash,dbStats,find,killCursors,listIndexes,listCollections,
  • readWrite: all read privileges + convertToCapped, createCollection,dbStats, dropCollection, createIndex, dropIndex, emptycapped, insert, listIndexes,remove, renameCollectionSameDB, update.
  • readAnyDatabase: allows the user to perform read in any database except the local and the config databases.

And so on…

In this tutorial, we are going to give specific privileges to a user who is allowed to only read the database, although he is allowed to write in a specific collection.

For this tutorial, we are using MongoDB 3.4 with previously configured authentication.

Steps:

  1. Create the database:
    mongo --authenticationDatbase admin -u superAdmin -p
    use percona
    db.foo.insert({x : 1})
    db.foo2.insert({x : 1})
  2. Create a new user:
    > db.createUser({user : 'client_read', pwd : '123', roles : ['read']})
    Successfully added user: { "user" : "client_read", "roles" : [ "read" ] }
  3. Log in with the user that has just been created and check the user access:
    ./mongo localhost/percona -u client_read -p
    MongoDB shell version v3.4.0-rc5
    Enter password:
    db.foo.find()
    { "_id" : ObjectId("586bc2e9cac0bbb93f325d11"), "x" : 1 }
    db.foo2.find().count()
    1
    // If user try to insert documents will receive an error:
    > db.foo.insert({x : 2})
    WriteResult({
                "writeError" : {
                "code" : 13,
                "errmsg" : "not authorized on percona to execute command
                     { insert: "foo", documents: [ { _id: ObjectId('586bc36e7b114fb2517462f3'), x: 2.0 } ], ordered: true }"
                }
    })
  4. Log out and log in again with administrator user to create a new role for this user:
    mongo --authenticationDatabase admin -u superAdmin -p
    db.createRole({
    role : 'write_foo2_Collection',
    privileges : [ {resource : {db : "percona", collection : "foo2"}, actions : ["insert","remove"]}
    ],
    roles : ["read"]
    })
    db.updateUser('client_read', roles : ['write_foo2_Collection'])
  5. Check the new access:
    ./mongo
    db.auth('client_read','123')
    1
    > show collections
    foo
    foo2
    > db.foo.find()
    { "_id" : ObjectId("586bc2e9cac0bbb93f325d11"), "x" : 1 }
    > db.foo2.insert({y : 2})
    WriteResult({ "nInserted" : 1 })
    > db.foo.insert({y : 2}) //does not have permission.
    WriteResult({
          "writeError" : {
                "code" : 13,
                "errmsg" : "not authorized on percona to execute command { insert: "foo", documents: [ { _id: ObjectId('586bc5e26f05b3a5db849359'), y: 2.0 } ], ordered: true }"
                         }
    })
  6. We can also add access to other database resources. Let’s suppose we would like to grant this just created user permission to execute a getLog command. This command is available in the clusterAdmin role, but we do not want to give all this role’s access to him. See https://docs.mongodb.com/v3.0/reference/privilege-actions/#authr.getLog.

    There is a caveat/detail/observation here. If we want to grant cluster privileges to a user, we should create the role in the admin database. Otherwise, the command will fail:

    db.grantPrivilegesToRole(
         "write_foo2_Collection",
               [
                      {resource : {cluster : true}, actions : ["getLog"] }
               ]
    )
    Roles on the 'percona' database cannot be granted privileges that target other databases or the cluster :

  7. We are creating the same role in the admin database. This user only works properly if the admin database is present in a possible restore. Otherwise, the privileges fail:

    use admin
    db.createRole({
         role : 'write_foo2_Collection_getLogs',
         privileges : [
                           {resource : {db : "percona", collection : "foo2"}, actions : ["insert","remove"]},
                           {resource : {cluster : true}, actions : ["getLog"]}],  
         roles : [ {role : "read", db: "percona"}]
    })
    use percona
    db.updateUser( "client_read",
    {
         roles : [
              { role : "write_foo2_Collection_getLogs", db : "admin" }
                    ]
    }
    )

  8. Now the user has the same privileges as before, plus the getLog permission. We can test this user new access with:

    mongo --authenticationDatabase percona -u read_user -p
    db.adminCommand({getLog : 'global'})
    {
              "totalLinesWritten" : 287,
              "log" : [....
    ….
    }

I hope you find this post useful. Please feel free to ping me on twitter @AdamoTonete or @percona and let us know your thoughts.

by Adamo Tonete at May 17, 2017 06:53 PM

May 16, 2017

Peter Zaitsev

Percona Live Open Source Database Conference 2017 Slides and Videos Available

Percona Live

Percona LiveThe slides and videos from the Percona Live Open Source Database Conference 2017 are available for viewing and download. The videos and slides cover the keynotes, breakout sessions and MySQL and MongoDB 101 sessions.

To view slides, go to the Percona Live agenda, and select the talk you want slides for from the schedule, and click through to the talk web page. The slides are available below the talk description. There is also a page with all the slides that is searchable by topic, talk title, speaker, company or keywords.

To view videos, go to the Percona Live 2017 video page. The available videos are searchable by topic, talk title, speaker, company or keywords.

There are a few slides and videos outstanding due to unforeseen circumstances. However, we will upload those as they become available.

Some examples of videos and slide decks from the Percona Live conference:

MongoDB 101: Efficient CRUD Queries in MongoDB
Adamo Tonete, Senior Technical Engineer, Percona
Video: https://www.percona.com/live/17/content/efficient-crud-queries-mongodb
Slides: https://www.percona.com/live/17/sessions/efficient-crud-queries-mongodb

MySQL 101: Choosing a MySQL High Availability Solution
Marcos Albe, Principal Technical Services Engineer, Percona
Video: https://www.percona.com/live/17/content/choosing-mysql-high-availability-solution
Slides: https://www.percona.com/live/17/sessions/choosing-mysql-high-availability-solution

Breakout Session: Using the MySQL Document Store
Mike Zinner, Sr. Software Development Director and Alfredo Kojima, Sr. Software Development Manager, Oracle
Video: https://www.percona.com/live/17/content/using-mysql-document-store
Slides: https://www.percona.com/live/17/sessions/using-mysql-document-store

Keynote: Continuent is Back! But What Does Continuent Do Anyway?
Eero Teerikorpi, Founder and CEO and MC Brown, VP Products, Continuent
Video: https://www.percona.com/live/17/content/continuent-back-what-does-continuent-do-anyway
Slides: https://www.percona.com/live/17/sessions/continuent-back-what-does-continuent-do-anyway

Please let us know if you have any issues. Enjoy the videos!

Percona Live Europe 2017
Percona Live Europe 2017: Dublin, Ireland!

This year’s Percona Live Europe will take place September 25th-27th, 2017, in Dublin, Ireland. Put it on your calendar now! Information on speakers, talks, sponsorship and registration will be available in the coming months.

We have developed multiple sponsorship options to allow participation at a level that best meets your partnering needs. Our goal is to create a significant opportunity for our partners to interact with Percona customers, other partners and community members. Sponsorship opportunities are available for Percona Live Europe 2017.

Download a prospectus here.

We look forward to seeing you there!

by Dave Avery at May 16, 2017 10:13 PM

Jean-Jerome Schmidt

Command Line Aficionados: Introducing s9s for ClusterControl

Easily Integrate ClusterControl With Your Existing DevOps Tools via s9s - Our New Command Line Interface

We’ve heard your call (and, selfishly, our own): please meet s9s - the new command line interface (CLI) for ClusterControl, our all-inclusive open source database management system.

At every conference we’ve attended so far, visitors have been asking us whether there is a command line interface for ClusterControl. And, we’re not afraid to admit, some of us at Severalnines have always wanted to have one as well. So those same colleagues have gone and created the s9s CLI for ClusterControl, which we’re happy to present today.

In fact, Johan Andersson, our CTO, is one of our command line aficionados and he describes the new CLI as follows:

What’s the ClusterControl CLI all about?

The ClusterControl CLI, is an open source project and optional package introduced with ClusterControl version 1.4.1. It is a command line tool to interact, control and manage your entire database infrastructure using ClusterControl. The s9s command line project is open source and is located on GitHub.

The ClusterControl CLI opens a new door for cluster automation where you can easily integrate it with existing deployment automation tools like Ansible, Puppet, Chef or Salt. This allows you to easily integrate scripts from your orchestration tools inside the CLI.

Users who have downloaded ClusterControl can use the CLI for all the ClusterControl features while they’re on the Enterprise trial of ClusterControl. Community users can then use the deployment and monitoring functionalities of ClusterControl. Existing customers can use the CLI to the full extent of ClusterControl.

Usage and Installation

The CLI can be installed by adding the s9s tools repository and using a package manager, as well as be compiled from source. The current installation script to install ClusterControl, install-cc, will automatically install the command line client. The command line client can also be installed on another computer or workstation for remote management. Finally, the CLI requires ClusterControl 1.4.1 or later.

Moreover, all communication between the client and the controller is encrypted and secured using TLS.

The ClusterControl CLI allows you to deploy and manage open source databases and load balancers in a way that is fully integrated and aligned with the ClusterControl core and GUI.

The s9s command line project is open source and located on GitHub: https://github.com/severalnines/s9s-tools

For examples and additional information, e.g, how to setup users and authentication, please visit
https://severalnines.com/docs/components.html#clustercontrol-cli

Before you get started, you need to have ClusterControl version 1.4.1 or later installed, see https://severalnines.com/download-clustercontrol-database-management-system

Some of the things you can do from the CLI in ClusterControl

  • Deploy and manage database clusters
    • MySQL
    • PostgreSQL
    • MongoDB to be added soon
  • Monitor your databases
    • Status of nodes and clusters
    • Cluster properties can be extracted
    • Gives detailed enough information about your clusters
  • Manage your systems and integrate with DevOps tools
    • Create, stop or start clusters
    • Add, remove, or restart nodes in the cluster
    • Create database users (CREATE USER, GRANT privileges to user)
      • Users created in the CLI are traceable through the system
    • Create load balancers (HAProxy, ProxySQL)
    • Create and Restore backups
    • Use maintenance mode
    • Conduct configuration changes of db nodes
    • Integrate with existing deployment automation
      • Ansible, Puppet, Chef or Salt, ...

Actions you take from the CLI will be visible in the ClusterControl Web UI and vice versa.

How to contribute

The CLI project (aka s9s-tools) can be accessed via GitHub. We encourage users to contribute to the project by:

  • Trying out the CLI and give us feedback
  • Letting us know about missing features, wishes, or problems by opening issues on GitHub
  • Contributing patches to the project

To sum things up

The ClusterControl CLI and GUI are fully integrated and synced to allow you to utilize the CLI for deployment and management of your databases and load balancers, whilst using the advanced graphs in the GUI for monitoring and troubleshooting. The CLI offers detailed information about node stats and cluster stats, enabling scripts and other tools to benefit from those.

In our experience, System Administrators and DevOps professionals are the mostly likely to benefit from a CLI for ClusterControl as they are accustomed to using scripts to perform their daily tasks.

Happy command-line-clustering!

by jj at May 16, 2017 01:26 PM

May 15, 2017

Peter Zaitsev

Percona Server for MongoDB 3.2.13-3.3 is Now Available

Percona Server for MongoDB 3.2

Percona Server for MongoDB 3.2Percona announces the release of Percona Server for MongoDB 3.2.13-3.3 on May 15, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open-source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.2 protocol and drivers. It extends MongoDB with MongoRocks, Percona Memory Engine, and PerconaFT storage engine, as well as enterprise-grade features like External Authentication, Audit Logging, Profiling Rate Limiting, and Hot Backup at no extra cost. Percona Server for MongoDB requires no changes to MongoDB applications or code.

NOTE: We deprecated the PerconaFT storage engine. It will not be available in future releases.

This release is based on MongoDB 3.2.13 and includes the following additional changes:

  • #PSMDB-127: Fixed cleanup of deleted documents and indexes for MongoRocks. When you upgrade to this release, deferred compaction may occur and cause database size to decrease significantly.
  • #PSMDB-133: Added the wiredTigerCheckpointSizeMB variable, set to 1000 in the configuration template for WiredTiger. Valid values are 32 to 2048 (2GB), with the latter being default.
  • #PSMDB-138: Implemented SERVER-23418 for MongoRocks.

Percona Server for MongoDB 3.2.13-3.3 release notes are available in the official documentation.

by Alexey Zhebel at May 15, 2017 05:55 PM

May 12, 2017

Peter Zaitsev

Percona Server for MySQL 5.7.18-14 is Now Available

Percona Server for MySQL 5.7.18-14

Percona Server for MySQL 5.7.18-14Percona announces the GA release of Percona Server for MySQL 5.7.18-14 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.7.18, including all the bug fixes in it, Percona Server for MySQL 5.7.18-14 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.18-14 milestone at Launchpad.

New Features:
Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • In-place upgrade from Percona Server 5.6 to 5.7 by using standalone packages would fail if /var/lib/mysql wasn’t defined as the datadir. Bug fixed #1687276.
  • Combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Processing GTIDs in the relay log that were already been executed were causing write/fsync amplification. Bug fixed #1669928 (upstream #85141).
  • Text/BLOB fields were not handling sorting of the empty string consistently between InnoDB and filesort. Bug fixed #1674867 (upstream #81810) by porting a Facebook patch for MySQL.
  • InnoDB adaptive hash index was using a partitioning algorithm which would produce uneven distribution when the server contained many tables with an identical schema. Bug fixed #1679155 (upstream #81814).
  • For plugin variables that are signed numbers, doing a SHOW VARIABLES would always show an unsigned number. Fixed by porting a Facebook patch for MySQL.

Other bugs fixed: #1629250 (upstream #83245), #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1670588 (upstream #84173), #1672389, #1674507, #1675623, #1650294, #1659224, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1677943, #1678692, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, #1687386, #1687432, #1687600, and #1674281.

The release notes for Percona Server for MySQL 5.7.18-14 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

by Hrvoje Matijakovic at May 12, 2017 06:00 PM

Percona Server for MySQL 5.6.36-82.0 is Now Available

Percona Server for MySQL 5.7.18-14

Percona Server for MySQL 5.6.36-82.0Percona announces the release of Percona Server for MySQL 5.6.36-82.0 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

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

New Features:
Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992.
  • tokubackup_slave_info file was created for a master server after taking the backup with Percona TokuBackup. Bug fixed #135.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • The combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug in Percona Server 5.6.28-76.1 caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Creating a compression dictionary with innodb_fake_changes enabled could lead to a server crash. Bug fixed #1629257.

Other bugs fixed: #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1683456, #1670588 (upstream #84173), #1672389, #1674507, #1674867, #1675623, #1650294, #1659224, #1660565, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1678692, #1678792, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, and #1674281.

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

by Hrvoje Matijakovic at May 12, 2017 05:43 PM

Kristian Nielsen

Improving replication with multiple storage engines

New MariaDB/MySQL storage engines such as MyRocks and TokuDB have renewed interest in using engines other than InnoDB. This is great, but also presents new challenges. In this article, I will describe work that I am currently finishing, and which addresses one such challenge.

For example, the left bar in the figure shows what happens to MyRocks replication performance when used with a default install where the replication state table uses InnoDB. The middle bar shows the performance improvement from my patch.

Current MariaDB and MySQL replication uses tables to transactionally record the replication state (eg mysql.gtid_slave_pos). When non-InnoDB storage engines are introduced the question becomes: What engine should be used for the replication table? Any choice will penalise other engines heavily by injecting a cross-engine transaction with every replicated change. Unless all tables can be migrated to the other engine at once, this is an unavoidable problem with current MariaDB / MySQL code.

To solve this I have implemented MDEV-12179, per-engine mysql.gtid_slave_pos tables, which should hopefully be in MariaDB 10.3 soon. This patch makes the server able to use multiple replication state tables, one for each engine used. This way, InnoDB transactions can update the InnoDB replication table, and eg. MyRocks transactions can update the MyRocks table. No cross-engine transactions are needed (unless the application itself uses both InnoDB and MyRocks in a single transaction).

The feature is enabled with the new option --gtid-pos-auto-engines=innodb,rocksdb. The server will automatically create the new replication tables when/if needed, and will read any such tables present at server start to restore the replication state.

Performance test

To test the impact of the new feature, I ran a sysbench write-only load on a master, and measured the time for a slave to apply the full load. The workload is using MyRocks tables, while the default mysql.gtid_slave_pos table is stored in InnoDB. The performance was compared with and without --gtid-pos-auto-engines=innodb,rocksdb. Full details of test options are available following the link at the end of the article.

Replication injects an update into a small table as part of each commit. The performance impact of this will be most noticeable for fast transactions, where the commit overhead is relatively larger. It will be particularly noticeable when durability is enabled (--innodb-flush-log-at-trx-commit=1 and similar). If another storage engine is added into a transaction, extra fsync() calls are needed in the commit step, which can be very expensive.

I tested the performance in two scenarios:

  1. A "worst case" scenario with durability/fsync enabled for binlog, InnoDB, and MyRocks, on hardware with slow fsync.
  2. A "best case" scenario with all durability/fsync disabled.
In the "worst case" we would hope to see substantial improvement due to reducing the number of fsync operations. In the "best case" improvements will be expected to be small, if any, though there may still be some improvement due to avoiding CPU and some I/O overhead from running the commits through two engines.

The figure at the start of the article shows the results from the "worst case". The left bar is the time for the slave to catch up when the replication state table is using the default InnoDB storage engine. The middle bar is the time when using --gtid-pos-auto-engines=innodb,myrocks and the right bar is when the state table is changed to MyRocks (MyRocks-only load).

We see a huge speed penalty from the cross-engine transactions introduced by the InnoDB state table, the slave is twice as slow. However, with the patch, all the performance is recovered compared to MyRocks-only setup.

The test was run on consumer-grade hardware with limited I/O capabilities. I ran a small script to test the speed of fdatasync() (see link at end of article). This SATA-attached SSD can do around 120 fdatasync() calls per second, writing 16 KB blocks at random round-robin among five 1MB data files. In the "worst case" test, the load is completely disk-bound. Thus, the absolute transactions-per-second numbers are low, and the impact of the new feature is very big.

The results of the "best case" is in the following figure. The "best case" workload is CPU-bound, disk utilisation is low. Sysbench write-only does several queries in each transaction, so commit overhead is relatively lower. Still, we see a substantial cost of replication introduced cross-engine, it runs 18% slower than the MyRocks-only case. And again, the patch is able to fully recover the performance.

So I think these are really good results for the new feature. The impact for the user is low - just set a server option, and the server will handle the rest. We could eventually make InnoDB, TokuDB, and MyRocks default for --gtid-pos-auto-engines to make it fully automatic. The actual performance gain will depend completely on the workload, and the absolute numbers from these performance tests mean little, perhaps. But they do show that there should be significant potential gain in many cases, and enourmous gains in some cases.

Conclusions

I hope this feature will help experiments with, and eventual migration to, the new storage engines such as TokuDB and MyRocks. The ability to have good replication performance when different storage engines are used in different transactions (but not within a single transaction) should make it easier to experiment, without committing everything on a server to a new and unknown engine. There might even be use cases for deploying permanently on a mixed-engine setup, with different parts of the data utilising different performance characteristics of each engine.

The present work here is implemented for MariaDB only. However, there is some discussions on porting it to other MySQL variants. While the details of the implementation will differ somewhat, due to code differences in MariaDB replication, I believe a similar approach should work well in the other MySQL variants also. So it is definitely a possibility, if there is interest.

Links:

May 12, 2017 10:01 AM

May 11, 2017

MariaDB AB

MariaDB Analytics Tutorial: 5 steps to get started in 10 minutes

MariaDB Analytics Tutorial: 5 steps to get started in 10 minutes Amy Krishnamohan Thu, 05/11/2017 - 16:40

Looking for an easy way to get started with analytics? MariaDB ColumnStore provides a simple, open and scalable analytics solution. It leverages a pluggable storage engine to handle analytic workloads while keeping the same ANSI SQL interface that is used across the MariaDB portfolio. This blog provides a quick 5-step tutorial to help you get started with MariaDB ColumnStore.

Before you begin, please download the sample dataset, including:

 

Step 1: MariaDB ColumnStore Installation and Configuration

In this step, you will learn how to download and install MariaDB ColumnStore.

Step 2: Create Table and Load Data

MariaDB ColumnStore does not require you to set up index and partitioning. It provides an easy way to create a table and load data without help from DBAs. In addition, when ColumnStore loads data, it uses cpimport which leverages parallel query loading capability. To learn more about cpimport, watch this presentation by our solutions engineer, Anders Karlsson.

Step 3: Create Dimension Table / Cross Engine Join

Leveraging the MariaDB Server interface, we can use "Dimension Tables" from the InnoDB storage engine and join those with the "Fact Table" data in ColumnStore. In this demo, we join a loan stats fact table and dimension table to create a sample quarterly report on loan amount.

Step 4: Window Function

Another benefit of ColumnStore is built-in analytics queries like window functions. Without writing complex code, users can run window functions in SQL to run time series analysis or run averages on a certain dataset. In this example, with one SQL query, you can report on the top ranked delinquent loan amounts in five specific states.

Step 5: Data Visualization: Tableau integration

ColumnStore provides an easy way to connect to third-party BI tools like Tableau using a generic ODBC driver, enabling you to better visualize your data.

CS blog.png

Hope you enjoyed the tutorial! Here are some additional resources to help you along the way:

Looking for an easy way to get started with analytics? MariaDB ColumnStore provides a simple, open and scalable analytics solution. It leverages a pluggable storage engine to handle analytic workloads while keeping the same ANSI SQL interface that is used across the MariaDB portfolio. This blog provides a quick 5-step tutorial to help you get started with MariaDB ColumnStore.

Login or Register to post comments

by Amy Krishnamohan at May 11, 2017 08:40 PM

Peter Zaitsev

MyRocks and LOCK IN SHARE MODE

LOCK IN SHARE MODE

LOCK IN SHARE MODEIn this blog post, we’ll look at MyRocks and the

LOCK IN SHARE MODE
.

Those who attended the March 30th webinar “MyRocks Troubleshooting” might remember our discussion with Yoshinori on 

LOCK IN SHARE MODE
.

I did more tests, and I can confirm that his words are true:

LOCK IN SHARE MODE
 works in MyRocks.

This quick example demonstrates this. The initial setup:

CREATE TABLE t (
id int(11) NOT NULL,
f varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=ROCKSDB;
insert into t values(12345, 'value1'), (54321, 'value2');

In session 1:

session 1> begin;
Query OK, 0 rows affected (0.00 sec)
session 1> select * from t where id=12345 lock in share mode;
+-------+--------+
| id | f |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.01 sec)

In session 2:

session 2> begin;
Query OK, 0 rows affected (0.00 sec)
session 2> update t set f='value3' where id=12345;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction

However, in the webinar I wanted to remind everyone about the differences between

LOCK IN SHARE MODE
  and
FOR UPDATE
. To do so, I added the former to my “session 2” test for the webinar. Once I did, it ignores the lock set in “session 1”. I can update a row and commit:

session 2> select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)
session 2> update t set f='value3' where id=12345;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session 2> commit;
Query OK, 0 rows affected (0.02 sec)

I reported this behavior here, and also at Percona Jira bugs database: MYR-107. In Facebook, this bug is already fixed.

This test clearly demonstrates that it is fixed in Facebook. In “session 1”:

session1> CREATE TABLE `t` (
    -> `id` int(11) NOT NULL,
    -> `f` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=ROCKSDB;
Query OK, 0 rows affected (0.00 sec)
session1> insert into t values(12345, 'value1'), (54321, 'value2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1>  select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)

And now in another session:

session2> begin;
Query OK, 0 rows affected (0.00 sec)
session2> select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)
session2> update t set f='value3' where id=12345;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t.PRIMARY

If you want to test the fix with the Facebook MySQL build, you need to update submodules to download the patch:

git submodule update
.

by Sveta Smirnova at May 11, 2017 08:36 PM

Oli Sennhauser

MySQL Enterprise Backup Incremental Cumulative and Differential Backup

Preparing the MySQL Enterprise Administrator Training I found that the MySQL Enterprise Backup Incremental Backup is not described very well. Thus I tried it out and wrote down this how-to:

Incremental Differential Backup

incremental_backup_diff.png

Full Backup

mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log
grep end_lsn /tape/full/meta/backup_variables.txt
end_lsn=2583666

Incremental Backups

mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2583666 --incremental backup
grep end_lsn /tape/inc1/meta/backup_variables.txt
end_lsn=2586138

mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2586138 --incremental backup
grep end_lsn /tape/inc2/meta/backup_variables.txt
end_lsn=2589328

mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2589328 --incremental backup
grep end_lsn /tape/inc3/meta/backup_variables.txt
end_lsn=2592519

Binary Log Backups

cp /var/lib/binlog/binlog.* /tape/binlog/

Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc1 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --incremental-backup-dir=/tape/inc2 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back

Point-in-Time-Recovery

grep binlog_position /tape/inc3/meta/backup_variables.txt
/tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731

cd /tape/binlog
mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot

Incremental Cumulative Backup

incremental_backup_cum.png

Full Backup

mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log
grep end_lsn /tape/full/meta/backup_variables.txt
end_lsn=2602954

Incremental Backups

mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2602954 --incremental backup

mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2602954 --incremental backup

mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2602954 --incremental backup

Binary Log Backups

cp /home/mysql/database/mysql-5.7/binlog/binlog.* /tape/binlog/

Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup

mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back

Point-in-Time-Recovery

grep binlog_position /tape/*/meta/backup_variables.txt
/tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731

cd /tape/binlog
mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot

I very much dislike that during my restore the backup is modified. So if I do a mistake during restore my backup is gone and I am doomed.

by Shinguz at May 11, 2017 03:20 PM

May 10, 2017

Peter Zaitsev

Percona Server for MySQL 5.5.55-38.8 is Now Available

Percona Server for MySQL 5.7.18-14

Percona Server for MySQL 5.5.55-38.8Percona announces the release of Percona Server for MySQL 5.5.55-38.8 on May 10, 2017. Based on MySQL 5.5.55, including all the bug fixes in it, Percona Server for MySQL 5.5.55-38.8 is now the current stable release in the 5.5 series.

Percona Server for MySQL is open-source and free. You can find release details in the 5.5.55-38.8 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

New Features:
  • Percona Server 5.5 packages are now available for Ubuntu 17.04 (Zesty Zapus).
Bugs Fixed:
  • If a bitmap write I/O errors happened in the background log tracking thread while a FLUSH CHANGED_PAGE_BITMAPS is executing concurrently it could cause a server crash. Bug fixed #1651656.
  • Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992.
  • Queries from the INNODB_CHANGED_PAGES table would needlessly read potentially incomplete bitmap data past the needed LSN range. Bug fixed #1625466.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.

Other bugs fixed: #1688161, #1683456, #1670588 (upstream #84173), #1672389, #1675623, #1660243, #1677156, #1680061, #1680510 (upstream #85838), #1683993, #1684012, #1684025, and #1674281.

Find the release notes for Percona Server for MySQL 5.5.55-38.8 in our online documentation. Report bugs on the launchpad bug tracker.

by Hrvoje Matijakovic at May 10, 2017 05:43 PM

Percona-Lab/mongodb_consistent_backup: 1.0 Release Explained

mongodb_consistent_backup

In this blog post, I will cover the Percona-Lab/mongodb_consistent_backup tool and the improvements in the 1.0.1 release of the tool.

Percona-Lab/mongodb_consistent_backup

mongodb_consistent_backup is a tool for performing cluster consistent backups on MongoDB clusters or single-replica sets. This tool is open source Python code, developed by Percona and published under our Percona-Lab GitHub repository. Percona-Lab is a place for code projects maintained and supported with only best-effort from Percona.

By considering the entire MongoDB cluster’s shards and individual shard members, mongodb_consistent_backup can backup a cluster with one or many shards to a single point in time. Single-point-in-time consistency of cluster backups is critical to data integrity for any “sharded” database technology, and is a topic often overlooked in database deployments.

This topic is explained in detail by David Murphy in this Percona blog: https://www.percona.com/blog/2016/07/25/mongodb-consistent-backups/.

1.0 Release

mongodb_consistent_backup originally was a single replica set backup script internal to Percona, which morphed into a large multi-threaded/concurrent Python project. It was released to the public (Percona-Lab) with some rough edges.

This release focuses on the efficiency and reliability of the existing components, many of the pain-points in extending, deploying and troubleshooting the tool and adding some small features.

New Features: Config File Overhaul

The tool was moved to use a structured, nested YAML config file instead of the messy config implemented in 0.x.

You can see a full example of this new format at this URL: https://github.com/Percona-Lab/mongodb_consistent_backup/blob/master/conf/mongodb-consistent-backup.example.conf

Here’s an example of a very basic config file that’s using 3 x replica-set config servers as “seed hosts” (a new feature in 1.0!), username+password and the optional Nagios NSCA notification method:

production:
  host: csReplSet/config01:27019,config02:27019,config03:27019
  username: mongodb_consistent_password
  password: "correct horse battery staple"
  authdb: admin
  log_dir: /var/log/mongodb_consistent_backup
  backup:
    method: mongodump
    name: production-eu
    location: /var/lib/mongodb_consistent_backup
  archive:
    method: tar
  notify:
    method: nsca
    nsca:
      check_host: mongodb-production-eu
      check_name: "mongodb_consistent_backup"
      server: nagios01.example.com:5667
  upload:
    method: none

New Features: Logging

Overall there is much more logged in this release, both in “regular” mode and “verbose” mode. A highlight for this release is live logging of the output of mongodump, something that was missing from the 0.x versions of the tool.

Now we can see the progress of the backup of each shard/replset in a cluster! Below we can see the backup of csReplset (a config server replica set) dump many collections and complete its backup. After, we can see the replica sets “test1” and “test2” dumping “wikipedia.pages”.

...
[2017-05-05 20:11:05,366] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.settings (1 document)
[2017-05-05 20:11:05,367] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing config.version to
[2017-05-05 20:11:05,372] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.version (1 document)
[2017-05-05 20:11:05,373] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing config.locks to
[2017-05-05 20:11:05,377] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.locks (3 documents)
[2017-05-05 20:11:05,378] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing config.databases to
[2017-05-05 20:11:05,381] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.databases (1 document)
[2017-05-05 20:11:05,383] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing config.tags to
[2017-05-05 20:11:05,385] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.tags (0 documents)
[2017-05-05 20:11:05,387] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing config.changelog to
[2017-05-05 20:11:05,399] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	done dumping config.changelog (112 documents)
[2017-05-05 20:11:05,401] [INFO] [MongodumpThread-7] [MongodumpThread:wait:72] csReplSet/172.17.0.1:27019:	writing captured oplog to
[2017-05-05 20:11:05,578] [INFO] [MongodumpThread-7] [MongodumpThread:run:133] Backup csReplSet/172.17.0.1:27019 completed in 0.71 seconds, 0 oplog changes
[2017-05-05 20:11:08,042] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[........................]  wikipedia.pages  636/35080  (1.8%)
[2017-05-05 20:11:08,071] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[........................]  wikipedia.pages  878/35118  (2.5%)
[2017-05-05 20:11:11,041] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[#.......................]  wikipedia.pages  1853/35080  (5.3%)
[2017-05-05 20:11:11,068] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[#.......................]  wikipedia.pages  2063/35118  (5.9%)
[2017-05-05 20:11:14,043] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[##......................]  wikipedia.pages  2983/35080  (8.5%)
[2017-05-05 20:11:14,075] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[##......................]  wikipedia.pages  3357/35118  (9.6%)
[2017-05-05 20:11:17,040] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[##......................]  wikipedia.pages  4253/35080  (12.1%)
[2017-05-05 20:11:17,070] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[###.....................]  wikipedia.pages  4561/35118  (13.0%)
[2017-05-05 20:11:20,038] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[###.....................]  wikipedia.pages  5180/35080  (14.8%)
[2017-05-05 20:11:20,067] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[###.....................]  wikipedia.pages  5824/35118  (16.6%)
[2017-05-05 20:11:23,050] [INFO] [MongodumpThread-5] [MongodumpThread:wait:72] test1/172.17.0.1:27017:	[####....................]  wikipedia.pages  6216/35080  (17.7%)
[2017-05-05 20:11:23,072] [INFO] [MongodumpThread-6] [MongodumpThread:wait:72] test2/172.17.0.1:28017:	[####....................]  wikipedia.pages  6964/35118  (19.8%)
...

Also, while backup data is gathered the status output from each Oplog tailing thread is now logged every 30 seconds (by default):

...
[2017-05-05 20:12:09,648] [INFO] [TailThread-2] [TailThread:status:60] Oplog tailer test1/172.17.0.1:27017 status: 256 oplog changes, ts: Timestamp(1494020048, 6)
[2017-05-05 20:12:11,033] [INFO] [TailThread-3] [TailThread:status:60] Oplog tailer test2/172.17.0.1:28017 status: 1588 oplog changes, ts: Timestamp(1494020049, 50)
[2017-05-05 20:12:22,804] [INFO] [TailThread-4] [TailThread:status:60] Oplog tailer csReplSet/172.17.0.1:27019 status: 43 oplog changes, ts: Timestamp(1494020062, 1)
...

You can now write log files to disk by setting the ‘log_dir’ config variable or ‘–log-dir’ command-line flag. One log file per backup is written to this directory, with a symlink pointing to the latest log file. The previous backup’s log file is automatically compressed with gzip.

New Features: ZBackup

ZBackup is an open-source de-duplication, compression and (optional) encryption tool for archive-like data (similar to backups). Files that are fed into ZBackup are organized at a block-level into pieces called “bundles”. When more files are fed into ZBackup, it can re-use the bundles when it notices the same blocks are being backed up. This approach provides significant savings on disk space (required for many database backups). To add to the savings, all data in ZBackup is compressed using LZMA compression, which generally compresses better than gzip/deflate or zip. ZBackup also supports an optional AES-128 encryption at rest. You enable it by providing a key file to ZBackup that allows it to encode/decode the data.

mongodb_consistent_backup 1.0.0 now supports ZBackup as a new archiving method!

Below is an example of ZBackup used on a small database (about 1GB) that is constantly growing.

This graph compares the size added on disk for seven backups taken 10-minutes apart using two methods. The first method is mongodb_consistent_backup, with mongodump built-in gzip compression (available via the –gzip flag since 3.2) enabled. By default mongodump gzip is enabled by mongodb_consistent_backup (if it’s available), so this is a good “baseline”. The second method is mongodb_consistent_backup with mongodump gzip compression disabled and ZBackup used as the mongodb_consistent_backup archiving method, a post-backup stage in our tool. Notice each backup in the graph after the first only adds 14-18mb to the disk usage, meaning ZBackup was able to recognize similarities in the data.

To try out ZBackup as an archive method, use one of these methods:

  1. Set the field “method” under the “archive” section of your mongodb_consistent_backup config file to “zbackup” (example):
    production:
      ...
      archive:
         method: zbackup
      ...
  2. Or, add the command-line flag “archive.method=zbackup” to your command line.

This archive method causes mongodb_consistent_backup to create a subdirectory in the backup location named “mongodb-consistent-backup_zbackup” and import completed backups into ZBackup after the backup stage. This directory contains the ZBackup storage files that it needs to operate, and they should not be modified!

Of course, there are trade-offs. ZBackup adds some additional system resource usage and time to the overall backup AND restore process – both importing and exporting data into ZBackup takes some additional time.

By default ZBackup’s restore uses a very small amount of RAM for cache, so increasing the cache with the “–cache-size” flag may improve restore performance. ZBackup uses threading so more CPUs can also improve performance of backups and restores.

New Features: Docker Container

We now offer a Dockerfile for building mongodb_consistent_backup with all dependencies into a Docker container! The goal for the image is to be as “thin” as possible, and so the build merely downloads a prebuilt binary of the tool and installs dependencies. See: https://github.com/Percona-Lab/mongodb_consistent_backup/blob/master/Dockerfile

Some interesting use cases for a Docker-based deployment of the tool come to mind:

  • Running MongoDB backups using ephemeral containers on Apache Mesos or Kubernetes (with persistent volumes or remote upload)
  • Restricting system resources used by mongodb_consistent_backup via Docker/cgroup’s isolation features
  • Simplified deployment or isolated dependencies (e.g., Python, Mongodump, etc.)

Up-to-date images of mongodb_consistent_backup are available at this Dockerhub URL: https://hub.docker.com/r/timvaillancourt/mongodb_consistent_backup/. This image includes mongodb_consistent_backup, gzip-capable mongodump binaries and latest-stable ZBackup binaries.

To run the latest Dockerhub image:

$ docker run -i timvaillancourt/mongodb_consistent_backup:latest <mongodb_consistent_backup-flags here>

To just list the “help” page (all available options):

$ docker run -i timvaillancourt/mongodb_consistent_backup:latest --help
usage: mongodb-consistent-backup [-h] [-c CONFIGPATH]
                                 [-e {production,staging,development}] [-V]
                                 [-v] [-H HOST] [-P PORT] [-u USER]
                                 [-p PASSWORD] [-a AUTHDB] [-n BACKUP.NAME]
                                 [-l BACKUP.LOCATION] [-m {mongodump}]
                                 [-L LOG_DIR] [--lock-file LOCK_FILE]
                                 [--sharding.balancer.wait_secs SHARDING.BALANCER.WAIT_SECS]
                                 [--sharding.balancer.ping_secs SHARDING.BALANCER.PING_SECS]
                                 [--archive.method {tar,zbackup,none}]
                                 [--archive.tar.compression {gzip,none}]
                                 [--archive.tar.threads ARCHIVE.TAR.THREADS]
                                 [--archive.zbackup.binary ARCHIVE.ZBACKUP.BINARY]
                                 [--archive.zbackup.cache_mb ARCHIVE.ZBACKUP.CACHE_MB]
                                 [--archive.zbackup.compression {lzma}]
...
...

An example script for running the container with persistent Docker volumes is available here: https://github.com/Percona-Lab/mongodb_consistent_backup/tree/master/scripts

New Features: Multiple Seed Hosts + Config Servers

mongodb_consistent_backup 1.0 introduces the ability to define a list of multiple “seed” hosts, preventing a potential for a single-point of failure in your backups! If a host in the list is unavailable, it will be skipped.

Multiple hosts should be specified with this replica-set URL format, many hosts separated by commas:

    <replica-set>/<host/ip>:<port>,<host/ip>:<port>,…

Or you can specify a comma-separated list without the replica set name for non-replset nodes (eg: mongos or non-replset config servers):

    <host/ip>:<port>,<host/ip>:<port>,…

Also, the functionality to use cluster Config Servers as seed hosts was added. Before version 1.0 a clustered backup needed to use a single mongos router as a seed host to find all shards and cluster members. Sometimes mongos routers can come and go as you scale, making this design brittle.

With this new functionality, mongodb_consistent_backup can use the Cluster Config Servers to map out the cluster, which are usually three times the fairly-static hosts in an infrastructure. This makes the deployment and operation of the tool a bit simpler and more reliable.

Overall Improvements

As mentioned, a focus in this release was improving the existing code. A major refactoring of the code structure of the project was completed in 1.0, and moves the major “phases” or “stages” in the tool to their own Python sub-modules (e.g., “Backup” and “Archive”) that then auto-load their various “methods” like “mongodump” or “Zbackup”.

The code was broken into these high-level stages:

  1. Backup. The stage that gathers the backup of data. During this stage, Oplog tailing and resolving also occur if the backup is for a cluster. More backup methods are coming soon!
  2. Archive. The stage that archives and optionally compresses the backup data. The new ZBackup method also adds de-duplication and encryption ability to this stage.
  3. Upload. The stage that uploads the resulting data to a remote storage. Currently only AWS S3 is supported with Google Cloud Storage and Rsync being added as we speak.
  4. Notify. The stage that notifies external systems of the success/failure of the backup. Currently, our tool only supports Nagios NSCA, with plans for PagerDuty and others to be added.

Some interesting code enhancements include:

  • Reusing of database connections. This reduces the number of connections on seed hosts.
  • Replication heartbeat time (“operational lag”). This is now considered in replica set lag calculations.
  • Added thread safety for oplog tailing threads. This resolves some issues on extremely-overloaded hosts.

Another focus was efficiency and preventing race conditions. The tool should be much less susceptible to error as a result, although if you see any problems we’d like to hear about them on our GitHub “Issues” page.

Lastly, we encourage the open source community to contribute additional functionality to this tool via our GitHub!

Release Notes:

  • 1.0.0
    • Move to dynamic code “Submodules” and subclassing of repeated components
    • Restructuring of YAML config to nested config
    • Safe start/stopping of oplog tailer threads, additional checking on all thread states
    • File-based logging with gzip of old log
    • Oplog tailer ‘oplogReplay’ performance optimization
    • Fixes to oplog durability to-disk
    • Live mongodump output to stdout in realtime
    • Oplog tailer status logging
    • ZBackup archive method: supporting deduplication, compression and option AES encryption
    • Support for list discovery/seed hosts
    • Support configdb servers as cluster seed hosts
    • Fewer (reused) database connections
    • Database connections to use strong write concern
    • Consider replication operational lag in secondary scoring
    • Backup metadata is written for future functionality and troubleshooting
    • mongodb_consistent_backup.Errors custom exceptions for proper exception handling
    • Python PyPi support added
    • Dockerfile support for running under containers
    • Additional log messages
    • Support for MongoDB 3.4 datatypes
    • Significant reworking of existing code for efficiency, reliability and readability

More about our releases can be seen here: https://github.com/Percona-Lab/mongodb_consistent_backup/releases.

by Tim Vaillancourt at May 10, 2017 04:58 PM

Jean-Jerome Schmidt

Webinar Replay and Q&A: how to deploy and manage ProxySQL, HAProxy and MaxScale

Thanks to everyone who participated in yesterday’s webinar on how to deploy and manage ProxySQL, HAProxy and MaxScale with ClusterControl.

Krzysztof Książek, Senior Support Engineer at Severalnines, discussed support for proxies for MySQL HA setups in ClusterControl: how they differ and what their pros and cons are. And he demonstrated how you can easily deploy and manage HAProxy, MaxScale and ProxySQL from ClusterControl during a live demo.

If you missed the webinar, would like to watch it again or browse through the slides, it’s all available for viewing online now. You’ll also find below a transcript of the Q&A session, which took place at the end of the webinar.

Watch the webinar replay

Webinar Questions & Answers

Q.: I’d like to replace HAProxy by ProxySQL - can I deploy ProxySQL on the same VMs as my current HAProxy ones or do I have to create new VMs? I deploy and manage it all with ClusterControl.

A.: Yes, as long as there is no conflict in ports used by those two proxies, there’s no reason why they couldn’t coexist. By default there is no such conflict, but a user may customize ports when deploying a proxy from ClusterControl, so if you are not sure how HAProxy is configured, it’s better to double-check it.

Q.: Do you know what happened to the admin interface MaxScale 2.0 and why it was removed?

A.: We don’t have detailed knowledge - it’s been deprecated due to security reasons, but what exactly is hidden behind this statement, we don’t know.

Q.: Have you any plans to talk about or support other load balancers in future, such as F5 BigIP, A10 Networks, or Citrix Netscaler? Or do you have any immediate thoughts on them you can share just now?

A.: As of now we don’t have any plans related to these load balancers, but if we get more requests for it, we’ll look into them more.

Q.: How can we sync users across multiple Proxysql instances? Or add existing users automatically to a newly added Proxysql instance?

A.: As of now, it is not possible to do that using ClusterControl - you still can do it manually, accessing ProxySQL through the command line interface. Having said that, we have plans to implement configuration syncing in one of the next ClusterControl releases. For adding users in batches, right now, CLI is the best way - ProxySQL accepts passwords in a form of MySQL password hash, so it’s fairly easy to write a script which will do the import. This is one of the feature requests we got so we will, most likely, implement it at some point. We can’t share any ETA though.

Q.: How does ClusterControl handle configuration changes in ProxySQL?

A.: ClusterControl does not take advantage of multiple configuration levels in ProxySQL - any change introduced via the UI is immediately loaded to runtime configuration.

Q.: Can you describe what the CPU usage is for ProxySQL or MaxScale on read/write split?

A.: Typically you’ll see ProxySQL utilizing less CPU resources compared to MaxScale, but it all depends on your workload and the number of query rules you may have added to ProxySQL.

Watch the webinar replay

by krzysztof at May 10, 2017 01:03 PM

May 09, 2017

Peter Zaitsev

MariaDB Handler_icp_% Counters: What They Are, and How To Use Them

Handler_icp_% counters

Handler_icp_% countersIn this post we’ll see how MariaDB’s Handler_icp_% counters status counters (Handler_icp_attempts and Handler_icp_matches) measure ICP-related work done by the server and storage engine layers, and how to see if our queries are getting any gains by using them.

These counters (as seen in SHOW STATUS output) are MariaDB-specific. In a later post, we will see how we can get this information in MySQL and Percona Server. This investigation spun off from comments in Michael’s post about the new MariaDB dashboard in PMM. Comments are very useful, so keep them coming! 🙂

We can start by checking the corresponding documentation pages:

https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_attempts

Description: Number of times pushed index condition was checked. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See Index Condition Pushdown.

https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_match

Description: Number of times pushed index condition was matched. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See See Index Condition Pushdown.

As we’ll see below, “attempts” counts the number of times the server layer sent a WHERE clause down to the storage engine layer to check if it can be filtered out. “Match”, on the other hand, counts whether an attempt ended up in the row being returned (i.e., if the pushed WHERE clause was a complete match).

Now that we understand what they measure, let’s check how to use them for reviewing our queries. Before moving forward with the examples, here are a couple of points to keey in mind:

  • Even if the attempt was not successful, it doesn’t mean that it is bad. However, a high (attempts – match) number is good in this context, since this is a measure of the rows that were “saved” from being checked in the server layer after getting the complete row from the storage engine. (This is explained more thoroughly below in Øystein Grøvlen’s comment – check it out!) On the other hand, a low number is not bad – it just means that most (or all) attempts ended up being a match.
  • From the documentation links above, it is stated that “the smaller the ratio between attempts to match, the better the filtering.”, which I believe is the contrary.

Back to our examples, then. First, let’s review version, table structure and data set.

mysql [localhost] {msandbox} (test) > SELECT @@version, @@version_comment;
+-----------------+-------------------+
| @@version       | @@version_comment |
+-----------------+-------------------+
| 10.1.20-MariaDB | MariaDB Server    |
+-----------------+-------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` int(11) DEFAULT NULL,
  `f3` int(11) DEFAULT NULL,
  KEY `idx_f1_f2` (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  3999996 |
+----------+
1 row in set (1.75 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 LIMIT 12;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
|    3 |    1 |    1 |
|    3 |    2 |    1 |
|    3 |    3 |    1 |
|    3 |    4 |    1 |
+------+------+------+
12 rows in set (0.00 sec)

It’s trivial, but it will work well for what we intend to show:

mysql [localhost] {msandbox} (test) > FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and f2 < 4;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
+------+------+------+
6 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 8              |
| HANDLER_ICP_MATCH    | 6              |
| ROWS_READ            | 6              |
| ROWS_SENT            | 6              |
+----------------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

In this scenario, the server sent a request to the storage engine to check on eight rows, from which six were a complete match. This is the case where a low attempts - match number is seen. The server scanned the index on the f1 column to decide which rows needed a “request for further check”, then the storage engine checked the WHERE condition on the f2 column with the pushed down (f2 < 4) clause.

Now let’s change the condition on f2:

mysql [localhost] {msandbox} (test) > FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    1 |
+------+------+------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 8              |
| HANDLER_ICP_MATCH    | 2              |
| ROWS_READ            | 2              |
| ROWS_SENT            | 2              |
+----------------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

In this scenario, the server also sent a request for eight rows, of which only two ended up being a match, due to the changed condition on f2. This is the case where a “high” attempts - match number is seen.

Great, we understand how to see the amount of rows sent between the server and storage engine layers. Now let’s move forward with the “how can I make sense of these numbers?” part. We can use the other counters included in the outputs that haven’t been mentioned yet (ROWS_READ and ROWS_SENT) and compare them when running the same queries with ICP disabled (which can be conveniently done with a simple SET):

mysql [localhost] {msandbox} (test) > SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

Let’s run the queries again. For the first query:

mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and f2 < 4;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 0              |
| HANDLER_ICP_MATCH    | 0              |
| ROWS_READ            | 9              |
| ROWS_SENT            | 6              |
+----------------------+----------------+
4 rows in set (0.01 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using where |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

As we can see by the handler counters, ICP is indeed not being used. Now the server is reading nine rows, as opposed to six when using ICP. Moreover, notice how we now see a Using where in the “Extra” column in the EXPLAIN output. This means that we are doing the filtering on the server layer; and we are using the first column of the composite index for the range scan (f1 < 3).

For the second query:

mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 0              |
| HANDLER_ICP_MATCH    | 0              |
| ROWS_READ            | 9              |
| ROWS_SENT            | 2              |
+----------------------+----------------+
4 rows in set (0.01 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using where |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

The server is also reading nine rows (because it’s also using only column f1 from the composite index, and we have the same condition on it), with the difference that it used to read only two while using ICP. We could say that this case was much better (and it was), but as with most of the time the answer to the bigger “how much better” question is “it depends”. As stated in the documentation, it has two factors:

  • How many records are filtered out
  • How expensive it is to read them

So it really depends on the size and kind of data set, and if it’s in memory or not. It’s better to benchmark the queries to have more information (like actual response times), but if it’s not possible we can get some valuable information by taking a look at these counters.

Lastly, I wanted to go back to the “attempts to match ratio” mentioned initially. As we saw in these examples, the first one had a 8:6 (or 4:3) ratio and the second a 8:2 (or 4:1) ratio, and the second one filtered more rows. Given this, I believe that the inverse will hold true: “The bigger the ratio of Handler_icp_attempts to Handler_icp_match, the better the filtering.”

Stay tuned for the next part, where we’ll see how to get this information from MySQL and Percona Server, too!

by Agustín at May 09, 2017 07:39 PM

Percona Server for MongoDB 3.4.4-1.4 is Now Available

Percona Server for MongoDB 3.2

Percona Server for MongoDB 3.4.3-1.3Percona announces the release of Percona Server for MongoDB 3.4.4-1.4 on May 9, 2017. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB is an enhanced, open source, fully compatible, highly-scalable, zero-maintenance downtime database supporting the MongoDB v3.4 protocol and drivers. It extends MongoDB with Percona Memory Engine and MongoRocks storage engine, as well as several enterprise-grade features:

Percona Server for MongoDB requires no changes to MongoDB applications or code.

This release is based on MongoDB 3.4.4 and includes the following additional changes:

  • #PSMDB-122: Added the percona-server-mongodb-enable-auth.sh script to binary tarball.
  • #PSMDB-127: Fixed cleanup of deleted documents and indexes for MongoRocks. When you upgrade to this release, deferred compaction may occur and cause database size to decrease significantly.
  • #PSMDB-133: Added the wiredTigerCheckpointSizeMB variable, set to 1000 in the configration template for WiredTiger. Valid values are 32 to 2048 (2GB), with the latter being default.
  • #PSMDB-138: Implemented SERVER-23418 for MongoRocks.

Percona Server for MongoDB 3.4.4-1.4 release notes are available in the official documentation.

by Alexey Zhebel at May 09, 2017 05:18 PM

MariaDB Foundation

2017 MariaDB Developers (Un)Conference New York Presentations

The 2017 MariaDB Developers (Un)Conference was held on April 9 and 10 in New York, and was kindly hosted by BNY Mellon. Below are a list of the sessions with links to slides/Periscope where available. Day One *Welcoming talk: BNY Mellon and MariaDB (Zak Murad), MariaDB Foundation in 2017 (Otto Kekäläinen) – periscope *What’s new […]

The post 2017 MariaDB Developers (Un)Conference New York Presentations appeared first on MariaDB.org.

by Ian Gilfillan at May 09, 2017 12:11 PM

May 08, 2017

Peter Zaitsev

Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back

Hung MySQL Transaction

In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.

Recently I was helping a customer discover why SELECT queries were running slower and slower until the server restarts (which got things back to normal). It took some time to get from that symptom to a final diagnosis. Please follow me on the journey of chasing this strange MySQL behavior!

Symptoms

Changes in the query response time can mean tons of things. We can check everything from the query plan to the disk performance. However, fixing it with a restart is less common. After looking at “show engine innodb status”, I noticed some strange lines:

Trx read view will not see trx with id >= 41271309593, sees < 41268384363
---TRANSACTION 41271309586, ACTIVE 766132 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
...

There was a total of 940 transactions like this.

Another insight was the InnoDB transaction history length graph from Percona Monitoring and Management (PMM):

Hung MySQL Transaction

History length of 6 million and growing clearly indicates a problem.

Problem localized

There have been a number of blog posts describing a similar problem: Peter stated in a blog post: “InnoDB transaction history often hides dangerous ‘debt’“. As the InnoDB transaction history grows, SELECTs need to scan more and more previous versions of the rows, and performance suffers. That explains the issue: SELECT queries get slower and slower until restart. Peter also filed this bug: Major regression having many row versions.

But why does the InnoDB transaction history start growing? There are 940 transactions in this state: ACTIVE 766132 sec. MySQL’s process list shows those transactions in “Sleep” state. It turns out that those transactions were “lost” or “hung”. As we can also see, each of those transactions holds two lock structures and one undo record, so they are not committed and not rolled-back. They are sitting there doing nothing. In this case, with the default isolation level REPEATABLE-READ, InnoDB can’t purge the undo records (transaction history) for other transactions until these “hung” transactions are finished.

The quick solution is simple: kill those connections and InnoDB will roll back those transactions and purge transaction history. After killing those 940 transactions, the graph looked like this:

Hung MySQL Transaction

However, several questions remain:

  1. What are the queries inside of this lost transaction? Where are they coming from? The problem is that neither MySQL’s process list nor InnoDB’s status shows the queries for this transaction, as it is not running those queries right now (the process list is a snapshot of what is happening inside MySQL right at this moment)
  2. Can we fix it so that the “hung” transactions don’t affect other SELECT queries and don’t cause the growth of transaction history?

Simulation

As it turns out, it is very easy to simulate this issue with sysbench.

Test preparation

To add some load, I’m using sysbench,16 threads (you can open less, it does not really matter here) and a script for a “write-only” load (running for 120 seconds):

conn=" --db-driver=mysql --mysql-host=localhost --mysql-user=user --mysql-password=password --mysql-db=sbtest "
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=InnoDB --oltp-table-size=1000000 $conn prepare
sysbench --num-threads=16 --max-requests=0 --max-time=120 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 $conn
--oltp-test-mode=complex --oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0
--oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=0 run

Simulate a “hung” transaction

While the above sysbench is running, open another connection to MySQL:

use test;
CREATE TABLE `a` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into a values(1);
begin; insert into a values(1); select * from a;

Note: we will need to run the SELECT as a part of this transaction. Do not close the connection.

Watch the history

mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 34324 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 36480 |
+----------------------+-------+
1 row in set (0.01 sec)

We can see it is growing. Now it is time to commit or rollback or even kill our original transaction:

mysql> rollback;
...
mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 793   |
+----------------------+-------+
1 row in set (0.00 sec)

As we can see, it has purged the history length.

Finding the queries from the hung transactions

There are a number of options to find the queries from that “hung” transaction. In older MySQL versions, the only way is to enable the general log (or the slow query log). Starting with MySQL 5.6, we can use the Performance Schema. Here are the steps:

  1. Enable performance_schema if not enabled (it is disabled on RDS / Aurora by default).
  2. Enable events_statements_history:
    mysql> update performance_schema.setup_consumers set ENABLED = 'YES' where NAME='events_statements_history';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  3. Run the query to find all transaction started 10 seconds ago (change the number of seconds to match your workload):
    SELECT ps.id as processlist_id,
                 trx_started, trx_isolation_level,
                 esh.EVENT_ID,
                 esh.TIMER_WAIT,
                 esh.event_name as EVENT_NAME,
                 esh.sql_text as SQL,
                 esh.RETURNED_SQLSTATE, esh.MYSQL_ERRNO, esh.MESSAGE_TEXT, esh.ERRORS, esh.WARNINGS
    FROM information_schema.innodb_trx trx
    JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
    LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
    LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
    WHERE trx.trx_started < CURRENT_TIME - INTERVAL 10 SECOND
      AND ps.USER != 'SYSTEM_USER'
    ORDER BY esh.EVENT_IDG
    ...
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 79
             TIMER_WAIT: 33767000
             EVENT NAME: statement/sql/begin
                    SQL: begin
      RETURNED_SQLSTATE: 00000
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
    *************************** 9. row ***************************
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 80
             TIMER_WAIT: 2643082000
             EVENT NAME: statement/sql/insert
                    SQL: insert into a values(1)
      RETURNED_SQLSTATE: 00000
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
    *************************** 10. row ***************************
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 81
             TIMER_WAIT: 140305000
             EVENT NAME: statement/sql/select
                    SQL: select * from a
      RETURNED_SQLSTATE: NULL
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0

    Now we can see the list of queries from the old transaction (the MySQL query used was taken with modifications from this blog post: Tracking MySQL query history in long running transactions).

At this point, we can chase this issue at the application level and find out why this transaction was not committed. The typical causes:

  • There is a heavy, non-database-related process inside the application code. For example, the application starts a transaction to get a list of images for analysis and then starts an external application to process those images (machine learning or similar), which can take a very long time.
  • The application got an uncaught exception and exited, but the connection to MySQL was not closed for some reason (i.e., returned to the connection pool).

We can also try to configure the timeouts on MySQL or the application so that the connections are closed after “N” minutes.

Changing the transaction isolation level to fix the InnoDB transaction history issue

Now that we know which transaction is holding up the purge process of InnoDB history, we can find this transaction and make changes so it will not “hang”. We can change the transaction isolation level from REPEATABLE READ (default) to READ COMMITTED. In READ COMMITTED, InnoDB does not need to maintain history length when other transactions have committed changes. (More details about different isolation methods and how they affect InnoDB transactions.) That will work in MySQL 5.6 and later. However this doesn’t work in Amazon Aurora (as of now): even with READ COMMITTED isolation level, the history length still grows.

Here is the list of MySQL versions where changing the isolation level fixes the issue

MySQL Version  Transaction isolation  InnoDB History Length
MySQL 5.6 repeatable read history is not purged until “hung” transaction finishes
MySQL 5.6 read committed (fixed) history is purged
Aurora repeatable read history is not purged until “hung” transaction finishes
Aurora read committed history is not purged until “hung” transaction finishes


Summary

Hung transactions can cause the InnoDB history length to grow and (surprisingly, on the first glance) affect the performance of other running select queries. We can use the performance schema to chase the “hung” transaction. Changing the MySQL transaction isolation level can potentially help.

by Alexander Rubin at May 08, 2017 07:09 PM

May 07, 2017

Daniël van Eeden

MySQL and SSL/TLS Performance

In conversations about SSL/TLS people often say that they either don't need TLS because they trust their network or they say it is too slow to be used in production.

With TLS the client and server has to do additional work, so some overhead is expected. But the price of this overhead also gives you something in return: more secure communication and more authentication options (client certificates).

SSL and TLS have existed for quite a long time. First they were only used for online banking and during authentication on web sites. But slowly many websites went to full-on SSL/TLS. And with the introduction of Let's encrypt many small websites are now using SSL/TLS. And many non-HTTP protocols either add encryption or move to a HTTP based protocol.

So TLS performance is very important for day-to-day usage. Many people and companies have put a lot of effort into improving TLS performance. This includes browser vendors, hardware vendors and much more.

But instead of just hoping for good performance: Let's try to measure it with a simple benchmark.

There are multiple pieces of a database connection we have to benchmark:
  1. New connections
  2. Reconnecting
  3. Bulk transfer
 And for all of these there are multiple things we can measure:
  1. Connect and/or transfer time (performance)
  2. CPU usage (efficiency)
  3. Concurrency 
The benchmark code can be found here: https://github.com/dveeden/mysql_go_tls

Let's look at connection performance. In this test I connect a number of times to MySQL  and do a "DO 1". This is on a localhost TCP connection, so it should be fast.


This is the connection time in ms for a single connection.
With 5.6.33 Community Edition, which is YaSSL based we see a very noticable overhead. And with 5.7.17 Community Edition this overhead is much smaller, but still very noticable.

Then MySQL 5.7 with OpenSSL (compiled on Fedora 25) shows another very noticable improvement over YaSSL. This can be explained because in this case the AVX2 and AES-NI CPU features can be used.

Also OpenSSL supports TLS tickets and YaSSL doesn't. This is why the yellow bar is much shorter that the orange bar. This is not yet supported in libmysqlclient, see Bug #76921 for details.

So SSL/TLS can be slow, but doesn't have to be slow.

Note that TLS needs multiple roundtrips. When testing this with netem on Linux I see this with MySQL 5.7.18 (YaSSL) and a 5ms delay:
No TLS goes from 0.5ms to 52ms
TLS goes from 8ms to 85ms

The second thing to measure is bulk performance. This is for large result sets including mysqldump.

With mysqldump from MySQL 5.7 it is easy to do:

$ time mysqldump --ssl-mode=disabled -A > /dev/null

real 0m0.145s
user 0m0.021s
sys 0m0.005s
$ time mysqldump --ssl-mode=required --ssl-cipher=AES128-SHA -A > /dev/null

real 0m0.120s
user 0m0.039s
sys 0m0.007s 
 
If you do this with multiple ciphers and put some data in the database you'll see something like this:
No TLS
4.5s
TLS Default
10.4s
RC4-MD5
7.1s
DES-CBC3-SHA
23.2s
 This is with MySQL 5.6.33 with YaSSL. Note that this is without using modern CPU features etc.

To conclude, there are some steps you can take to improve SSL/TLS performance:
  1. Upgrade to 5.7
  2. Compile MySQL with OpenSSL
  3. Use TLS tickets
  4. Use persistent connections
  5. Try different cipher suits for mysqldump and other places where you transfer larger amounts of data.

by Daniël van Eeden (noreply@blogger.com) at May 07, 2017 01:28 PM

May 05, 2017

Colin Charles

Speaking in May 2017

It was a big April if you’re in the MySQL ecosystem, so am looking forward to other events that have different focus and a different base, so to speak. See you at:

  • rootconf – May 11-12 2017 – Bangalore, India. My first Rootconf was last year, and it was a great event; I look forward to going there again this year, to talk about capacity planning for your databases. If you register with this link you get a 10% discount.
  • Open Source Data Center Conference – May 16-18 2017 – Berlin, Germany. I’ve enjoyed my trips to OSDC in the last few years, and they’re on their last tickets now – so register if you plan to go!

by Colin Charles at May 05, 2017 08:28 AM

May 04, 2017

MariaDB AB

How to create microservices and set-up a microservice architecture with MariaDB, Docker and Go

How to create microservices and set-up a microservice architecture with MariaDB, Docker and Go Bjorge Staijen Thu, 05/04/2017 - 17:34

Intro

In this blogpost we’re going to use the photo-sharing application that has been demoed during MariaDB’s M|17 conference. The sources of the application are available for download on Github. The purpose of this blog post is to demonstrate using a MariaDB database server in your stack when building microservice applications. During this post we’re going to talk about what microservices are, the architecture of the photo-sharing application, bootstrapping the application, and scaling parts of the application. The code demoed in this blogpost could be used as a starting template for building your own microservices. The code is tested against Docker version 17.03, Docker Machine version 0.10.0 and VirtualBox version 5.1.12, and the application has been created and tested on a Mac. Let’s start with talking about what microservices are.

What are Microservices

Creating applications with microservices is an approach that attempts to avoid the problems often found in monolithic applications. It is an approach that makes web-based development more agile and code bases easier to understand. Lots of companies claim to use the microservice approach, but they all have their own definitions and best practises. While there is no mutual agreement on what microservices really are, there are some similarities on how most companies are using microservices. With the microservice approach, developers create the application as a suite of small independent services, each independently deployable, running on a unique process, and communicate with each other using public API’s. The main benefits from this approach are resiliency of services, ease of deployment and independent scaling.

High-Level Architecture of the Application

The photo-sharing application has been created utilizing the microservices architecture. This means that the business logic is divided into multiple smaller services, each responsible for a certain set of tasks and responsible for their own data. The services can communicate by using each other’s API.

We divided the application into the following services: profile-service, photo-service, vote-service, comment-service, authentication-service and web-server. The web-server also functions as Proxy and API gateway. The services are split into five separate services for a couple of reasons. We wanted to make the services small, and the first step in to making them small was by creating a service for each domain. In our application we identified four domains: profiles, votes, comments and photos. This resulted in the first four services: profile, vote, comment and photo-service. The authentication-service is not in this list and was created to separate authentication functionality from profile management. This solution shows its value once authentication traffic starts growing, the container orchestrator can then scale the authentication-service independently. Also the authentication-service can easily be rewritten or replaced when the decision is made to use a third party authentication standard, without touching the profile service.

See figure 1 for a visualization of how the services are placed and how they are connected to each other. Note that not all services are interchanging data. For example the authentication-service doesn’t need to communicate with services like photo-service or vote-service.

 

image4.png

Figure 1. Top level domain of the photo sharing application.

Downloading and starting the application

Now we know a little bit about the architecture of the application it is time to download the code and to bootstrap the application. First we’re going to get the application from Github and after that we’re using Docker Swarm and Docker Stacks to bootstrap the application.

From this point you’ll need a terminal.

Preparing our environment

The photo-sharing application is available on Github and can be downloaded here. After downloading, the first thing we need to do is set-up our environment on top of which the application is running. The sources you’ve just downloaded contain a /scripts directory, and within that directory the create_machines.sh script.

The script is responsible for two things, which are the creation of 5 virtual machines and to configure Docker Swarm. If you can’t run this script on your machine then please follow the instructions in the Docker documentation to create a Swarm on your own machine. In this blogpost we use the following machine names:

  • manager1
  • worker1
  • worker2
  • worker3
  • worker4

If you can run a bash script on your machine then run the following command to setup your environment:

./create_machines.sh

After the machines are created we’re going to deploy a cluster visualizer. For that we need the IP of the cluster manager. Use the following command to retrieve the IP address of the manager:

docker-machine ip manager1

Now replace ‘YOUR_IP’ with the IP from above, and run the following command to deploy the visualizer:

docker-machine ssh manager1 docker run -it -d -p 8080:8080 -e HOST=YOUR_IP -e PORT=8080 -v /var/run/docker.sock:/var/run/docker.sock manomarks/visualizer

After we’ve deployed the visualizer we’re going to deploy an etcd instance on the manager-machine for the service discovery for our database cluster. Again, use the IP address of the manager and then run the following command (replace YOUR_IP in 4 locations):

docker-machine ssh manager1 docker run -d -v /usr/share/ca-certificates/:/etc/ssl/certs -p 4001:4001 -p 2380:2380 -p 2379:2379 \
--name etcd quay.io/coreos/etcd etcd \
-name etcd0 \
-advertise-client-urls http://YOUR_IP:2379,http://YOUR_IP:4001 \
-listen-client-urls http://0.0.0.0:2379,http://0.0.0.0:4001 \
-initial-advertise-peer-urls http://YOUR_IP:2380 \
-listen-peer-urls http://0.0.0.0:2380 \
-initial-cluster-token etcd-cluster-1 \
-initial-cluster etcd0=http://YOUR_IP:2380 \
-initial-cluster-state new

We need to verify that our machines are properly working, and to do that we are going to access the visualizer in our webbrowser.

Use the manager’s IP with port 8080 to access the visualizer, e.g. 192.168.99.100:8080. See figure 2 to see what the visualizer looks like.

image3.png

Figure 2. The manomarks/visualizer without any running services.

Starting the stack

To direct the terminal to the Docker Engine of the Swarm-manager we used the command:

eval $(docker-machine env manager1)

Now the first thing that we need to do is we have to pass the location of the etcd service to our database configuration. To do that we need to edit the docker-compose-stacks.yml file and add the IP and port to the “DISCOVERY_SERVICE” environment variable of the database. Get the IP of the manager with:

docker-machine ip manager1

Use the IP and add it to configuration on line 126. Change it so it looks something like this example:

- "DISCOVERY_SERVICE=192.168.99.100:2379

Now we can deploy our application. Before executing the next command, make sure that you’re in the root folder of the photo-sharing application. Run the following command to start the application:

docker stack deploy --compose-file docker-compose-stacks.yml demo

This command deploys our application and distributes the services across our virtual machines. Deploying of the services might take a few minutes, depending on the speed of your internet connection.

Using the photo-sharing application

To check if the photo-sharing application is running you simply have to go to the IP of any of the machines and use the port of the web application, port 4999. We can use any machine because all nodes in Docker Swarm are participating in an ingress routing mesh. To get the IP of the machine use the command:

docker-machine ip manager1

In our case the web application can be access by using the following address: 192.168.99.100:4999.

image5.png

Figure 3. The User Interface of the photo-sharing application.

Now the application is running you can start using it. The application is a photo-sharing and photo-voting application. An anonymous user can visit the website via the browser and can toggle between three different timelines of photos. The first one is a timeline of uploaded photos (ordered by last uploaded), second one is a timeline of hot photos (based on upvotes), and the last one is a timeline of trending photos. The difference between hot photos and trending photos is that the hot photos timeline is showing photos only from the current day; which means that every day there’s a different hot timeline.

People who visit the website can create a free account. An account gives the user the privilege to upload photos, upvote or downvote a photo and leave a comment on photos. If a visitor does not create an account he or she can still see the pictures but they cannot upload a photo, leave a comment or vote.

The photo-sharing application is written in Go and to connect the application with the MariaDB database we use the by the community developed database driver: go-sql-driver/mysql.

Scaling the application

The next thing we want to do is scale our application. See figure 4 to see the current state of the cluster.

image1.png

Figure 4. Cluster overview before the database is scaled.

To scale our application we use docker service scale, with docker service scale you can scale any service you want. For the purpose of demonstration we’re going to scale the database. Scale the database to three instances with the following command:

docker service scale demo_db=3

After scaling we have a three node Galera Cluster. The database containers takes care of setting up the replication. See figure 5 to see the current state of the cluster.

image2.png

Figure 5. Cluster overview after the database is scaled.

The traffic will automatically be spread over all the instances. The swarm manager uses internal load balancing to distribute requests among other services. Load balancing is done based upon DNS name of the service.

Conclusion

In this blogpost we’ve downloaded the photo-sharing application, created a cluster of machines, run the application on the cluster and we ended with scaling the application. The purpose of this blogpost was to merely demonstrate a way of using MariaDB Galera Cluster with microservices. It was a lot to cover for a blogpost and there is still much more to consider before all of this is ready for production. Many areas have not been touched during this blogpost. Things that you should think about before running production are (and not limited to): security, automatic failover, storing data, backups, disaster recovery, using an API gateway, improve overall code, data integrity, data aggregation and service boundaries.

Feel free to ask questions or provide feedback on this blogpost in the comments. Or if you have any suggestions for my next blog post; I’m happy to learn about that as well.

Related Topics

  1. Martin Fowler's article on Microservices
  2. Chris Richardson's website about Microservices
  3. Christian Posta's blogpost about "The Hardest Part About Microservice: Your Data"
  4. MariaDB's Knowledge Base article on Installing and using MariaDB via Docker
  5. MariaDB's Knowledge Base article on MariaDB Galera Cluster

In this blogpost we’re going to use the photo-sharing application that has been demoed during MariaDB’s M|17 conference. The sources of the application are available for download on Github. The purpose of this blog post is to demonstrate using a MariaDB database server in your stack when building microservice applications. During this post we’re going to talk about what microservices are, the architecture of the photo-sharing application, bootstrapping the application, and scaling parts of the application. The code demoed in this blogpost could be used as a starting template for building your own microservices.

Login or Register to post comments

by Bjorge Staijen at May 04, 2017 09:34 PM

Peter Zaitsev

How much disk space should I allocate for Percona Monitoring and Management?

Percona Monitoring and Management

I heard a frequent question at last week’s Percona Live conference regarding Percona Monitoring and Management (PMM): How much disk space should I allocate for PMM Server?

First, let’s review the three components of Percona Monitoring and Management that consume non-negligible disk space:

  1. Prometheus data source for the time series metrics
  2. Query Analytics (QAN) which uses Percona Server XtraDB (Percona’s enhanced version of the InnoDB storage engine)
  3. Orchestrator, also backed by Percona Server XtraDB

Of these, you’ll find that Prometheus is generally your largest consumer of disk space. Prometheus hits a steady state of disk utilization once you reach the defined storage.local.retention period. If you deploy Percona Monitoring and Management 1.1.3 (the latest stable version), you’ll be using a retention period of 30 days. “Steady state” in this case means you’re not adding or removing nodes frequently, since each node comes with its own 1k-7k metrics to be scraped. Prometheus stores a one-time series per metric scraped, and automatically trims chunks (like InnoDB pages) from the tail of the time series once they exceed the retention period (so the disk requirement per static list of metrics remains “fixed” for the retention period).

However, if you’re in a dynamic environment with nodes being added and removed frequently, or you’re on the extreme end like these guys who re-deploy data centers every day, steady state for Prometheus may remain an elusive goal. The guidance you find below may help you establish at least a minimum disk provisioning threshold.

Percona Monitoring and Management

QAN is based on a web application and uses Percona Server 5.7.17 as it’s datastore. The Percona QAN agent runs one instance per monitored MySQL server, and obtains queries from either the Slow log or Performance Schema. It performs analysis locally to generate a list of unique queries and their corresponding metrics: min, max, avg, med, and p95. There are dimensions based on Tmp table, InnoDB, Query time, Lock time, etc. Check the schema for a full listing, as there are actually 149 columns on this table (show create table pmm.query_class_metricsG). While the table is wide, it isn’t too long: PMM Demo is ~9mil rows and is approximately 1 row per distinct query per minute per host.

Finally, there is Orchestrator. While the disk requirements for Orchestrator are not zero, they are certainly dwarfed by Prometheus and QAN.  As you’ll read below, Percona’s Orchestrator footprint is a measly ~250MB, which is a rounding error. I’d love to hear other experiences with Orchestrator and how large your InnoDB footprint is for a large or active cluster.

For comparison, here is the resource consumption from Percona’s PMM Demo site:

  • ~47k time series
  • 25 hosts, which is on average ~1,900 time series/host, some are +4k
  • 8-day retention for metrics in Prometheus
  • Prometheus data is ~40GB
    • Which should not increase until we add more host, as this isn’t a dynamic Kubernetes environment 🙂
  • QAN db is 6.5GB
    • We don’t currently prune records, so this will continue to grow
    • 90% of space consumed is in query_class_metrics, which is ~9mil rows
    • Our first record is ~September 2016, but only in the past three months
    • This is MySQL QAN only, the MongoDB nodes don’t write anything into QAN (yet… we’re working on QAN for MongoDB and hope to ship this quarter!!)
  • Orchestrator db is ~250MB
    • audit table is 97% of the space consumed, ~2mil rows

So back to the original question: How much space should I allocate for Percona Monitoring and Management Server? The favorite answer at Percona is “It Depends®,” and this case is no different. Using PMM Demo as our basis, 46GB / 25 hosts / 8 days = ~230MB/host/day or ~6.9GB/host/30 day retention period. For those of you running 50 instances in PMM, you should be provisioning ~400GB of disk.

Of course, your environment is likely to be different and directly related to what you do and don’t enable. For example, a fully verbose Percona Server 5.7.17 configuration file like this:

## PMM Enhanced options
long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
query_response_time_stats=ON
performance_schema_instrument='%=on'

with none of the mysqld_exporter features disabled:

--disable-binlogstats
--disable-processlist
--disable-queryexamples
--disable-tablestats
--disable-userstats

can lead to an instance that has +4k metrics and will push you above 230MB/host/day. This is what the top ten metrics and hosts by time series count from the PMM Demo look like:

Percona Monitoring and Management

What does the future hold related to minimizing disk space consumption?

  1. The PMM development team is working on the ability to purge a node’s data without access to the instance
    • Today you need to call pmm-admin purge from the instance – which becomes impossible if you’ve already terminated or decommissioned the instance!
  2. We are following Prometheus’ efforts on the 3rd Gen storage re-write in Prometheus 2.0, where InfluxDB will do more than just indices
  3. Again we are following Prometheus’ efforts on Remote Read / Remote Write so we can provide a longer-term storage model for users seeking > 30 days (another popular topic at PL2017)
    • Allows us to store less granular data (every 5s vs. every 1s)
    • Usage of Graphite, OpenTSDB, and InfluxDB as secondary data stores on the Remote end

I’d love to hear about your own experiences using Percona Monitoring and Management, and specifically the disk requirements you’ve faced! Please share them with us via the comments below, or feel free to drop me a line directly michael.coburn@percona.com. Thanks for reading!

by Michael Coburn at May 04, 2017 06:15 PM

Serge Frezefond

MariaDB Backup released

MariaDB Backup has been released with MariaDB Server 10.1.23. It offers support for #MariaDB Compression and Encryption on Linux and Windows Server.

MariaDB Engineering rocks !

This is different from Oracle MySQL were you have to have a commercial licence for your server to make backup with MySQL Enterprise Backup (MEB) MariaDB Backup is under [...]

by Serge at May 04, 2017 05:23 AM

May 03, 2017

MariaDB AB

MariaDB Backup released with MariaDB Server 10.1.23

MariaDB Backup released with MariaDB Server 10.1.23 RalfGebhardt Wed, 05/03/2017 - 19:49

MariaDB Backup, a new tool for performing physical online backups of InnoDB, Aria and MyISAM tables from MariaDB Server, was released as part of the maintenance release of MariaDB Server 10.1.23. MariaDB Backup is available on Linux and Windows.

MariaDB Server 10.1 introduced MariaDB InnoDB Compression and Data-at-Rest Encryption. For both, we have seen high interest from the users of MariaDB Server. However, existing backup solutions from our ecosystem did not support full backup capability for these features. 

If you are running MariaDB Server on Microsoft Windows, an open source tool for creating physical online backups did not exist.

To address our customers and community users concerns, we decided to provide a backup solution that would support full backup capability for MariaDB Server that include encrypted and compressed data.

We also have added Microsoft Windows Support to MariaDB Backup to provide our users and customers an open source solution for creating physical online backups. 

The most obvious way to do this was to create a solution based on the well known and used backup tool called Percona XtraBackup. We extended it and named the solution MariaDB Backup.

So why have we decided to change the name of the tool and executables? Mainly to avoid confusion, to enable the use of both tools in the environment a DBA has to maintain, to give an option to stay with Percona XtraBackup for servers not using MariaDB Data-at-Rest or InnoDB Compression, while at the same time use MariaDB Backup when Data-at-Rest Encryption or InnoDB Compression is needed. Or one can just use MariaDB Backup as a full backup solution.

MariaDB Backup Releases

First of all, the version of MariaDB Backup provided with MariaDB 10.1.23 is of Alpha maturity. We encourage our community and customers to test this version but do not recommend using it in a production environment. Please add comments to this blog post about successful tests. Relying on bug reports to decide on changing the maturity of a software is not as good as knowing about the successful use/testing of it.

You will not find MariaDB Backup as a separate downloadable product at this time. MariaDB Backup will be provided as a separate package included in new releases of MariaDB Server 10.1, and also in the future with MariaDB Server 10.2. 

MariaDB Backup is tightly connected to the storage engines XtraDB/InnoDB and maintaining MariaDB Backup as part of the Server enables us to get the tool tested against changes in the Server and storage engines.

Using MariaDB Backup

MariaDB Backup is currently based on Percona XtraBackup 2.3.8 and therefore provides the same functionality plus

  • Backup/Restore of Data-at-Rest encrypted XtraDB/InnoDB tables
  • Backup/Restore when InnoDB Compression is used
  • Backup/Restore of Data-at-Rest encrypted Aria tables
  • Using MariaDB Backup for a SST with Galera Cluster, when Data-at-Rest encryption is used
  • Support of Microsoft Windows

Limitations:

  • Backup tool based encryption (gcrypt) is not supported
  • No symlink to innobackupex - make use of the parameter “--innobackupex”

The command to use MariaDB Backup is:

 mariabackup 

To use MariaDB Backup for Galera Cluster SST, the script wsrep_sst_mariabackup.sh is provided and the Galera configuration setting is used:

wsrep_sst_method = mariabackup


Summary

MariaDB Backup enables you to

  • Create Backups when you use the MariaDB Server based compression for table types InnoDB/XtraDB
  • Create Backups of MariaDB Server for Data-at-Rest encrypted InnoDB/XtraDB tables
  • Create MariaDB Galera Cluster SST for Data-at-Rest encrypted tables
  • Create online backups also for MariaDB Server running on Microsoft Windows 

Please consult our Knowledge Base for the latest information about this evolving addition to our product offering.

MariaDB Backup, a new tool for performing physical online backups of InnoDB, Aria and MyISAM tables from MariaDB Server, was released as part of the maintenance release of MariaDB Server 10.1.23. MariaDB Backup is available on Linux and Windows.

Login or Register to post comments

by RalfGebhardt at May 03, 2017 11:49 PM

MariaDB Foundation

MariaDB 10.1.23 and 5.5.56 now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.1.23, MariaDB 5.5.56, and MariaDB Galera Cluster 5.5.56. See the release notes and changelogs for details. Download MariaDB 10.1.23 Release Notes Changelog What is MariaDB 10.1? MariaDB APT and YUM Repository Configuration Generator Download MariaDB 5.5.56 Release Notes Changelog What is MariaDB 5.5? […]

The post MariaDB 10.1.23 and 5.5.56 now available appeared first on MariaDB.org.

by Daniel Bartholomew at May 03, 2017 07:01 PM

Peter Zaitsev

Storing UUID and Generated Columns

Storing UUID

A lot of things have been said about UUID, and storing UUID in an optimized way. Now that we have generated columns, we can store the decomposed information inside the UUID and merge it again with generated columns. This blog post demonstrates this process.

First, I used a simple table with one char field that I called uuid_char to establish a base case. I used this table with and without a primary key:

CREATE TABLE uuid_char (
uuid char(36) CHARACTER SET utf8 NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE uuid_char_pk (
uuid char(36) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I performed the tests on a local VM over MySQL 5.7.17 for 30 seconds, with only two threads, because I wanted to just compare the executions:

sysbench
--oltp-table-size=100000000
--test=/usr/share/doc/sysbench/tests/db/insert_uuid_generated_columns.uuid_char.lua
--oltp-tables-count=4
--num-threads=2
--mysql-user=root
--max-requests=5000000
--report-interval=5
--max-time=30
--mysql-db=generatedcolumn
run

One pair of executions is with the UUID generated by sysbench, which simulates the UUID that comes from the app:

rs = db_query("INSERT INTO uuid_char (uuid) VALUES " .. string.format("('%s')",c_val))

An alternative execution is for when the UUID is generated by the MySQL function uuid():

rs = db_query("INSERT INTO uuid_char (uuid) VALUES (uuid())")

Below we can see the results: 

The inserts are faster without a PK (but only by 5%), and using the uuid() function doesn’t impact performance.

Now, let’s see the alternative method, which is decomposing the UUID. It has four main information sets:

  • Timestamp: this is a number with seven decimals.
  • MAC: the MAC address of the device that creates the UUID
  • Unique value: this value avoids duplicate cases scenarios
  • UUID version: this will always be “1”, as we are going to use version 1. If you are going to use another version, you will need to review the functions that I used.

The structure of the table that we’ll use is:

CREATE TABLE `uuid_generated` (
`timestamp` decimal(18,7) unsigned NOT NULL,
`mac` bigint(20) unsigned NOT NULL,
`temp_uniq` binary(2) NOT NULL,
PRIMARY KEY (`timestamp`,`mac`,`temp_uniq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To understand how a UUID is unwrapped, I used this store procedure (which receives a UUID and inserts it into the table):

CREATE PROCEDURE ins_generated_uuid (uuid char(38))
begin
set @hex_timestamp = concat(substring(uuid, 16, 3), substring(uuid, 10, 4), substring(uuid, 1, 8));
set @timestamp = concat(conv(@hex_timestamp,16,10)div 10000000 - (141427 * 24 * 60 * 60),'.',right(conv(@hex_timestamp,16,10),7));
set @mac = conv(right(uuid,12),16,10);
set @temp_uniq = unhex(substring(uuid,20,4));
insert into uuid_generated (timestamp,mac,temp_uniq) values (@timestamp,@mac,@temp_uniq);
end ;;

Explanation:

  • @hex_timestamp is a temporary variable that collects the timestamp in hexadecimal format from the different sections of the UUID
  • @timestamp transforms the hexadecimal timestamp to a decimal number
  • @mac pulls the last number in the UUID (a MAC) so we can store it in as a bigint
  • @temp_uniq is a value to conserve the uniqueness, which is why we store it as binary and it is at the end of the Primary Key

If I wanted to get the UUID again, I can use these two generated columns:

`hex_timestamp` char(40) GENERATED ALWAYS AS (conv(((`timestamp` * 10000000) + (((141427 * 24) * 60) * 600000000)),10,16)) VIRTUAL,
`uuid_generated` char(38) GENERATED ALWAYS AS (concat(right(`hex_timestamp`,8),'-',substr(`hex_timestamp`,4,4),'-1',left(`hex_timestamp`,3),'-',convert(hex(`temp_uniq`) using utf8),'-',lpad(conv(`mac`,10,16),12,'0'))) VIRTUAL,

We performed tests over five scenarios:

  • Without the generated columns, the insert used data generated dynamically
  • Same as before, but we added a char field that stores the UUID
  • With the char field, and adding the generated column
  • We used the store procedure detailed before to insert the data into the table
  • We also tested the performance using triggers

The difference between the Base and the previous table structure with Primary Keys is very small. So, the new basic structure has no impact on performance.

We see that Base and +Char Field have the same performance. So leaving a char field has no performance impact (it just uses more disk space).

Using generated columns impact performance. This is expected, as the columns are generated to validate the type before the row is inserted.

Finally, the use of triggers and store procedure has the same impact in performance.

These are the three structures to the tables:

CREATE TABLE `uuid_generated` (
`timestamp` decimal(18,7) unsigned NOT NULL,
`mac` bigint(20) unsigned NOT NULL,
`temp_uniq` binary(2) NOT NULL,
PRIMARY KEY (`timestamp`,`mac`,`temp_uniq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `uuid_generated_char` (
`timestamp` decimal(18,7) unsigned NOT NULL,
`mac` bigint(20) unsigned NOT NULL,
`temp_uniq` binary(2) NOT NULL,
`uuid` char(38) DEFAULT NULL,
PRIMARY KEY (`timestamp`,`mac`,`temp_uniq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `uuid_generated_char_plus` (
`timestamp` decimal(18,7) unsigned NOT NULL,
`mac` bigint(20) unsigned NOT NULL,
`temp_uniq` binary(2) NOT NULL,
`uuid` char(38) DEFAULT NULL,
`hex_timestamp` char(40) GENERATED ALWAYS AS (conv(((`timestamp` * 10000000) + (((141427 * 24) * 60) * 600000000)),10,16)) VIRTUAL,
`uuid_generated` char(38) GENERATED ALWAYS AS (concat(right(`hex_timestamp`,8),'-',substr(`hex_timestamp`,4,4),'-1',left(`hex_timestamp`,3),'-',convert(hex(`temp_uniq`) using utf8),'-',lpad(conv(`mac`,10,16),12,'0'))) VIRTUAL,
PRIMARY KEY (`timestamp`,`mac`,`temp_uniq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And this is the trigger:

DROP TRIGGER IF EXISTS ins_generated_uuid;
delimiter ;;
CREATE TRIGGER ins_uuid_generated BEFORE INSERT ON uuid_generated
FOR EACH ROW
begin
set @hex_timestamp = concat(substring(NEW.uuid, 16, 3), substring(NEW.uuid, 10, 4), substring(NEW.uuid, 1, 8));
set NEW.timestamp = concat(conv(@hex_timestamp,16,10)div 10000000 - (141427 * 24 * 60 * 60),'.',right(conv(@hex_timestamp,16,10),7));
set NEW.mac = conv(right(NEW.uuid,12),16,10);
set NEW.temp_uniq = unhex(substring(NEW.uuid,20,4));
end ;;
delimiter ;

Conclusions

Decomposing the UUID is an alternative to storing them in order, but it won’t speed up inserts. It is simpler to execute queries over a range of dates, and look at the row for a particular device, as you will be able to use the MAC (it is recommended to add an index for it). Generated columns give you the possibility to build the UUID back in just one string.

by David Ducos at May 03, 2017 06:15 PM

MariaDB AB

MariaDB User Conference M|17 Recap – Video Recordings Now Available

MariaDB User Conference M|17 Recap – Video Recordings Now Available MariaDB Team Wed, 05/03/2017 - 11:35

Thank you to everyone who joined us at our first-ever MariaDB user conference in New York City on April 11-12. Thank you also to our sponsors for their generous support: Amazon Web Services, Galera Cluster, Qualcomm Datacenter Technologies, Monyog, Intel, Alibaba Cloud, eperi, Navicat, MariaDB Foundation, diginomica, insideBIGDATA and ODBMS.org.

Couldn’t make the event or want to relive your favorite session? Access all the M|17 video recordings.

 

Keynote Highlights

M17.jpg

 

  • “The Open Source Mandate” with MariaDB CEO Michael Howard discussed how the changes in today’s world can only be handled with technology that is a reflection of our times and the strategic benefit of open source.

  • “DBS Bank’s Journey to Open Source” with Joan Tay Kim Choo, DBS Bank’s Executive Director of Technology & Operations, shared their process for migrating from a proprietary database system to MariaDB.

  • “How Chips and Databases Change Everything” with MariaDB Chief Product Officer Roger Bodamer provided a sneak peek into the MariaDB roadmap.

  • “Open Source in a Dangerous World” with RedHat’s Gunnar Kellekson urged caution when considering the various flavors of open source, and provided a guide to help choose the right open source solution while maintaining security and stability.

  • “Everything Old is New Again: the Return of Relational” with MariaDB CTO Monty Widenius and CMO Cate Lochead in a fireside chat, discussed the evolving database landscape and MariaDB as the world’s open source database choice.

Session Highlights

IMG_2371.jpg

  • “Visual Analysis of Health Data at UW IHME” with Andrew Ernst, Assistant Director of Infrastructure at the University of Washington’s Institute for Health Metrics and Evaluation, demonstrated how MariaDB ColumnStore is deployed in the real world to help transform data into policy.

  • “Moving Off Oracle Enterprise and into the Cloud” with Amazon Web Services Technical Evangelist Darin Briskman shared tools and best practices for users to unshackle from legacy databases and migrate to MariaDB in the cloud.

  • “How Alibaba Collaborates with MariaDB” featured senior engineers from Alibaba who explained how the mega cloud company collaborates with MariaDB to enhance features, like sequence, design and code, multi-source replication, flashback and more.

  • “Deep Dive: The Nuts and Bolts in MaxScale, a DB Proxy” provided an in-depth overview of MariaDB MaxScale’s security and scaling capabilities, and featured a hands-on demonstration.

 

Reception

Attendees traveled to New York from all over the globe - from as far away as Asia and Europe, to learn about MariaDB and network with peers.

20170411_171659_LLS.jpg

IMG_2553.jpg

IMG_2451.jpg

 

#MariaDBM17

Thanks to the attendees and our amazing speakers, we were trending on Twitter. Here’s a few of our favorite conference tweets.

@ernstae: Thanks NYC for a great (but short) trip, and shouts out to the great #MariaDBM17 conference! Great work @mariadb!

@JahnelGroup: We had such a great time in #NYC the past couple of days at the #MariaDBM17 #tech conference! #opensource #bigdata #analytics

@Brazingo: #MariaDBM17 3 excellent keynotes - short 30 min and not dragging on - CEO, CPO and user DBS Bank - great content

@diginomica: Alibaba on #opensource and cloud business in China - live from #MariaDBM17 bit.ly/2nJCBYs ->a different scale requires new thinking

 

M|18 to be announced soon

In the next few weeks, we’ll release the dates for our next MariaDB user conference, M|18. Be on the lookout for the announcement!

Thank you to everyone who joined us at our first-ever MariaDB user conference in New York City on April 11-12. Thank you also to our sponsors for their generous support: Amazon Web Services, Galera Cluster, Qualcomm Datacenter Technologies, Monyog, Intel, Alibaba Cloud, eperi, Navicat, MariaDB Foundation, diginomica, insideBIGDATA and ODBMS.org.

Couldn’t make the event or want to relive your favorite session? Access all the M|17 video recordings.

Login or Register to post comments

by MariaDB Team at May 03, 2017 03:35 PM

Jean-Jerome Schmidt

How to deploy and manage HAProxy, MaxScale or ProxySQL with ClusterControl - Webinar May 9th

Join us for our new webinar next week, Tuesday May 9th, with Krzysztof Książek, Senior Support Engineer at Severalnines, who will discuss support for proxies for MySQL HA setups in ClusterControl: how they differ and what their pros and cons are. You’ll also be shown you how you can easily deploy and manage HAProxy, MaxScale and ProxySQL from ClusterControl via a live demo.

Proxies are building blocks of high availability setups for MySQL. They can detect failed nodes and route queries to hosts which are still available. If your master failed and you had to promote one of your slaves, proxies will detect such topology changes and route your traffic accordingly. More advanced proxies can do much more, such as route traffic based on precise query rules, cache queries or mirror them. They can be even used to implement different types of sharding.

Register below to hear it all about it!

Date, Time & Registration

Europe/MEA/APAC

Tuesday, May 9th at 09:00 BST (UK) / 10:00 CEST (Germany, France, Sweden)

Register Now

North America/LatAm

Tuesday, May 9th at 9:00 PST (US) / 12:00 EST (US)

Register Now

Agenda

  • Introduction
  • Why use a proxy layer?
  • Comparison of proxies - the pros & cons
    • HAProxy
    • MaxScale
    • ProxySQL
  • Live demo of proxy support in ClusterControl

Speaker

Krzysztof Książek, Senior Support Engineer at Severalnines, is a MySQL DBA with experience managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard.

We look forward to “seeing” you there and to insightful discussions!

If you have any questions or would like a personalised live demo, please do contact us.

by jj at May 03, 2017 11:02 AM

May 02, 2017

Peter Zaitsev

Percona University in Europe May 9 and May 11

Percona University

Percona University in EuropeIn 2013 we started Percona University, which consists of technology discussion events held in different cities around the world. The next installments of Percona University in Europe are next week when I fly there for Percona University Berlin (May 9) and Percona University Budapest (May 11). Both events are free to attend, and you are very welcome to join us for either of them.

Below are some questions and answers about why you should attend a Percona University session:

What is Percona University? It is a half-day technical educational event, with a wider program when compared to a traditional meetup. Usually, we include about six hours of talks split with a 30-minute coffee break. We encourage people to join us at any point during these talks – we understand that not everyone can take off a half a day from their work or studies.

What is on the agenda for each of the events? Full agendas and registration forms for the Berlin and Budapest events are available at the indicated links.

Does the word “University” mean that we won’t cover any in-depth topics, and these events would only interest college/university students? No, it doesn’t. We designed Percona University presentations for all kinds of “students,” including professionals with years of database industry experience. The word “University” means that this event series is about educating attendees on technical topics (it’s not a sales-oriented event, it’s about educating the community).

Does Percona University cover only Percona technology? We will definitely mention Percona technology, but we will also focus on real-world technical issues and recommend solutions that work (regardless of whether Percona developed them).

Are there other Percona University events coming up besides Berlin and Budapest? We will hold more Percona University events in different locations in the future. Our events newsletter is a good source of information about when and where they will occur. If you want to partner with Percona in organizing a Percona University event, contact our team. You can also check our list of technical webinars to get further educational insights.

These events are free and low-key! We want them to remain easy to organize in any city of the world. They aren’t meant to look like a full conference (like our Percona Live series). Percona University has a different format – it’s purposefully informal, and designed to be perfect for learning and networking. This is an in-person database community gathering, so feel free to come with interesting cases and tricky questions!

I hope to see many of you at Percona University in Europe, Berlin and Budapest editions!

by Peter Zaitsev at May 02, 2017 06:20 PM

Jean-Jerome Schmidt

ProxySQL: All the Severalnines Resources

Load balancers are an essential component in MySQL and MariaDB database high availability; especially when making topology changes transparent to applications and implementing read-write split functionality.

ProxySQL is a lightweight yet complex protocol-aware proxy that sits between the MySQL clients and servers. It is a gate, which basically separates clients from databases, and is therefore an entry point used to access all the database servers.

ProxySQL has an advanced multi-core architecture to handle that large number of connections, multiplexed to potentially hundreds of backend servers squeezing every drop of performance out of your database cluster; all with zero downtime.

Here are our top resources for ProxySQL to get you started with this amazing new technology.

On-Demand Webinars

High Availability in ProxySQL

Joint webinar with ProxySQL’s creator, René Cannaò, where we discuss building a solid, scalable and manageable proxy layer using ProxySQL in order to create a highly available MySQL infrastructure.

Watch the replay!

MySQL & MariaDB Load Balancing with ProxySQL & ClusterControl

We’re delighted to be joined by ProxySQL’s creator, René Cannaò, to tell us more about this new MySQL & MariaDB load balancing proxy and its features. We also show you how you can deploy ProxySQL using ClusterControl.

Watch the replay!

Introducing the Severalnines MySQL© Replication Blueprint

The Severalnines Blueprint for MySQL Replication includes all aspects of a MySQL Replication topology with the ins and outs of deployment, setting up replication, monitoring, upgrades, performing backups and managing high availability using proxies as ProxySQL, MaxScale and HAProxy. This webinar provides an in-depth walk-through of this blueprint and explains how to make best use of it.

Watch the replay!

Videos

Video: Interview #2 with ProxySQL Creator René Cannaò

We sat down at Percona Live 2017 with ProxySQL creator René Cannaò to talk about the new release of ClusterControl, what’s is coming up for ProxySQL, how it is being received by the MySQL Community and how it varies from Maxscale.

Watch the Video!

Video: Interview with ProxySQL Creator René Cannaò

Severalnines sits down with ProxySQL founder and creator René Cannaò to discuss his product and the upcoming webinar MySQL & MariaDB Load Balancing with ProxySQL & ClusterControl.

Watch the Video!

Top Blogs

Announcing ClusterControl 1.4.1 - the ProxySQL Edition

Release of ClusterControl 1.4.1 with key new management features for MySQL and MariaDB load balancing with ProxySQL, along with performance improvements and bug fixes.

Read More!

Using ClusterControl to Deploy and Configure ProxySQL on top of MySQL Replication

This blog post shows you how to deploy and configure ProxySQL from ClusterControl 1.3.3, on top of a master-slave replication setup. ProxySQL is an SQL-aware load balancer. It allows you to automatically do read-write split of your traffic, by sending writes to your writeable master and sending reads to the read-only slaves.

Read More!

Tips and Tricks - How to Shard MySQL with ProxySQL in ClusterControl

Sharding can be a painful exercise. Databases need to be split on multiple servers, data migrated, and applications have to be tweaked to be aware of the shards. However, there are ways to make this less painful. This blog explains how to use ProxySQL to shard a table to another cluster in ClusterControl without changing your application.

Read More!

Sharding MySQL with MySQL Fabric and ProxySQL

This blog post shows you how to set up a sharded MySQL setup based on MySQL Fabric and ProxySQL.

Read More!

How to Setup Read-Write Split in Galera Cluster using ProxySQL

This blog shows you how to setup read-write split in Galera Cluster using ProxySQL. It also covers some limitations that we found at the time of writing.

Read More!

How ProxySQL adds Failover and Query Control to your MySQL Replication Setup

An overview of ProxySQL in a MySQL replication environment, and how ProxySQL adds features like failover and query control to the setup.

Read More!

MySQL Load Balancing with ProxySQL - An Overview

An overview of ProxySQL, an SQL-aware load balancer for MySQL and MariaDB. This blog post explains the concepts behind ProxySQL, and goes through installation and configuration.

Read More!

Whitepapers

Database Sharding with MySQL Fabric

Why do we shard? How does sharding work? What are the different ways I can shard my database? This white paper goes through some of the theory behind sharding. It also discusses three different tools which are designed to help users shard their MySQL databases. And last but not least, it shows you how to set up a sharded MySQL setup based on MySQL Fabric and ProxySQL.

Download the whitepaper

Migrating to MySQL 5.7 - The Database Upgrade Guide

Upgrading to a new major version involves risk, and it is important to plan the whole process carefully. In this whitepaper, we look at the important new changes in MySQL 5.7 and show you how to plan the test process. We then look at how to do a live system upgrade without downtime. For those who want to avoid connection failures during slave restarts and switchover, this document goes even further and shows you how to leverage ProxySQL to achieve a graceful upgrade process.

Download the whitepaper

ClusterControl
Single Console for Your Entire Database Infrastructure
Find out what else is new in ClusterControl

ClusterControl for ProxySQL

Packaged in the latest ClusterControl release, ProxySQL enables MySQL, MariaDB and Percona XtraDB database systems to easily manage intense, high-traffic database applications without losing availability. ClusterControl offers advanced, point-and-click configuration management features for the load balancing technologies we support. We know the issues regularly faced and make it easy to customize and configure the load balancer for your unique application needs.

We know load balancing and support many different technologies. ClusterControl has many things preconfigured to get you started with a couple of clicks. If you run into challenged we also provide resources and on-the-spot support to help ensure your configurations are running at peak performance.

ClusterControl delivers on an array of features to help deploy and manage ProxySQL

  • Advanced Graphical Interface - ClusterControl provides the only GUI on the market for the easy deployment, configuration and management of ProxySQL.
  • Point and Click deployment - With ClusterControl you’re able to apply point and click deployments to MySQL, MySQL replication, MySQL Cluster, Galera Cluster, MariaDB, MariaDB Galera Cluster, and Percona XtraDB technologies, as well the top related load balancers with HAProxy, MaxScale and ProxySQL.
  • Suite of monitoring graphs - With comprehensive reports you have a clear view of data points like connections, queries, data transfer and utilization, and more.
  • Configuration Management - Easily configure and manage your ProxySQL deployments with a simple UI. With ClusterControl you can create servers, reorientate your setup, create users, set rules, manage query routing, and enable variable configurations.

by Severalnines at May 02, 2017 02:42 PM

May 01, 2017

Peter Zaitsev

Webinar Thursday May 4, 2017: Percona Software News and Roadmap Update Q2 2017

Percona Software News

Percona Software NewsCome and listen to Percona CEO Peter Zaitsev on Thursday, May 4, 2017 at 11:00 am (PST) / 2:00 pm (EST) discuss Percona’s software news and roadmap, including Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

During this webinar, Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap.

Peter will also talk about new developments in Percona commercial services, and finish with a Q&A.

You can register for the webinar here.

Peter ZaitsevPeter Zaitsev, CEO of Percona

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 150 professionals in 20 plus countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014 and 2015.

Peter was an early employee at MySQL AB, eventually leading the company’s High-Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University where he majored in Computer Science. Peter is a co-author of High-Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Data Performance Blog. Fortune and DZone also tapped Peter as a contributor, and his recent ebook Practical MySQL Performance Optimization Volume 1 is one of percona.com’s most popular downloads.

by Dave Avery at May 01, 2017 06:34 PM

Federico Razzoli

FOREACH in MySQL/MariaDB stored procedures

One of the annoying limitations of MySQL/MariaDB stored procedures is the lack of a FOREACH construct, which loops on each row returned by a query.

In practice, this forces users to write a lot of code just to tell MySQL how to fetch rows and exit properly. Nesting 2 loops of this kind simply results in unmaintenable code (don’t trust me, just try).

Now, I’m writing a library of views and procedures that I’ll share as open source in the next days, and I decided to finally write my foreach. Well, sort of. It is impossible to use the current stored procedures language to write a flexible foreach, because to loop rows you need a cursor. And cursors are based on a hard-coded query. In this old post I proposed a verbose, ugly, but working solution, but it has a limitation: the number of columns returned by the query must still be fixed. So, I used that technique (based on views) in my procedure, but I had to write different procedures: foreach_1(), foreach_2(), foreach_3(). If you need to read more rows you can modify the code easily, but I think that for an open source library 3 columns is reasonable.

Here I decided to share an experimental prototype. If you have a better idea on how to achieve a similar result, I’ll be glad to trash this hack and use your idea instead.

Also note that for this procedure I used MariaDB 10.2 improved PREPARE statement. If you want to run it on MySQL or older MariaDB versions, you’ll have to make some easy changes and test the procedure.

The code

CREATE PROCEDURE foreach_2(IN in_sql TEXT, IN in_callback_type VARCHAR(9), IN in_callback_body TEXT)
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE p_sql TEXT DEFAULT NULL;
    DECLARE p_eof BOOL DEFAULT FALSE;
    DECLARE v_p1, v_p2 TEXT DEFAULT NULL;
 
    DECLARE crs_foreach CURSOR FOR
    SELECT p1, p2 FROM vw_foreach;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
    BEGIN
        SET p_eof := TRUE;
    END;

    SET in_callback_type := UPPER(in_callback_type);
     IF NOT (in_callback_type IN ('SQL', 'PROCEDURE')) THEN
         SIGNAL SQLSTATE VALUE '45000'
        SET MESSAGE_TEXT = '[foreach_2] Invalid in_callback_type';
    END IF;

    DO GET_LOCK('crs_foreach', 1);

    SET p_sql := CONCAT('CREATE OR REPLACE VIEW vw_foreach AS ', in_sql, ';');
    PREPARE stmt_foreach_2 FROM p_sql;
    EXECUTE stmt_foreach_2;

    OPEN crs_foreach;

    DO RELEASE_LOCK('crs_foreach');
 
    lp_while: WHILE TRUE DO
        FETCH crs_foreach INTO v_p1, v_p2;
 
        IF p_eof THEN
            LEAVE lp_while;
        END IF;

        IF in_callback_type IN ('SQL') THEN
            SET @p1 := v_p1;
            SET @p2 := v_p2;
            PREPARE stmt_foreach_2 FROM in_callback_body;
        ELSE
            PREPARE stmt_foreach_2 FROM CONCAT('CALL `', in_callback_body, '`(', QUOTE(v_p1), ', ', QUOTE(v_p2), ');');
        END IF;
        EXECUTE stmt_foreach_2;
    END WHILE;

    CLOSE crs_foreach;
    DEALLOCATE PREPARE stmt_foreach_2;
END

Now, the usage. There are 2 ways to use this procedure.

Using a callback procedure

First, create a callback procedure which will handle the rows returned by your query:

CREATE PROCEDURE p_echo(IN in_p1 TEXT, IN in_p2 TEXT)
BEGIN
    SELECT CONCAT_WS('.', in_p1, in_p2) AS r;
END

As you can see, the procedure must accept 2 values (foreach_2). The names don’t really matter, but p1 and p2 are clear, at least in this example.

Now, invoke foreach_2():

CALL foreach_2(
    'SELECT TABLE_SCHEMA AS p1, TABLE_NAME AS p2 FROM information_schema.TABLES LIMIT 3;',
    'PROCEDURE',
    'p_echo'
);

We passed the query producing the results we want to loop. Then we told foeach_2() that we want to use a callback procedure, and specified its name.

Using a callback SQL statement

Writing a procedure to perform a simple tasks would be annoying. That’s why I decided to also support callback statements:

CALL foreach_2(
    'SELECT TABLE_SCHEMA AS p1, TABLE_NAME AS p2 FROM information_schema.TABLES LIMIT 3;',
    'SQL',
    'SELECT @p1 AS p1, @p2 AS p2;'
);

As you can see, the callback query can access the values using user variables: @p1 and @p2.

These variables exist at session level, so it is possible that we are overwriting something. But I think there are no reasonable ways to avoid these collisions.

Enjoy!


by Federico at May 01, 2017 09:55 AM

April 28, 2017

Peter Zaitsev

From Percona Live 2017: Thank You, Attendees!

Percona Live 2017

Percona Live 2017From everyone at Percona and Percona Live 2017, we’d like to send a big thank you to all our sponsors, exhibitors, and attendees at this year’s conference.

This year’s conference was an outstanding success! The event brought the open source database community together, with a technical emphasis on the core topics of MySQL, MariaDB, MongoDB, PostgreSQL, AWS, RocksDB, time series, monitoring and other open source database technologies.

We will be posting tutorial and session presentation slides at the Percona Live site, and all of them should be available shortly. 

Highlights This Year:

Thanks to Our Sponsors!

We would like to thank all of our valuable event sponsors, especially our diamond sponsors Continuent and VividCortex – your participation really makes the show happen.

We have developed multiple sponsorship options to allow participation at a level that best meets your partnering needs. Our goal is to create a significant opportunity for our partners to interact with Percona customers, other partners and community members. Sponsorship opportunities are available for Percona Live Europe 2017.

Download a prospectus here.

Percona Live Europe 2017Percona Live Europe 2017: Dublin, Ireland!

This year’s Percona Live Europe will take place September 25th-27th, 2017, in Dublin, Ireland. Put it on your calendar now! Information on speakers, talks, sponsorship and registration will be available in the coming months.

We look forward to seeing you there!

by Dave Avery at April 28, 2017 09:47 PM

MariaDB AB

Get Started with MariaDB on Docker

Get Started with MariaDB on Docker MariaDB Team Fri, 04/28/2017 - 14:27

This week’s webinar with Alvin Richards on getting started with MariaDB on Docker was extremely popular but unfortunately we weren’t able to get to all the audience questions at the end. We sat down with Alvin after the webinar to review the questions and get his responses. Check out the conversation below.

If you missed the webinar or would like to watch it again, you can access the Docker recording and slide deck.

Alvin Richards is Field CTO at MariaDB Corporation where he connects the dots between practitioners, innovators and MariaDB products. In his prior life, Alvin was vice president of product at Aerospike; ran engineering teams at Docker and MongoDB, leading the revolution of microservices and NoSQL; was technical director at NetApp, working to integrate databases and virtual infrastructures with storage; and worked at Oracle on data warehousing products.


Q: What is the fundamental content of an image? Is it the line commands you include in the Dockerfile that make up the image? Do you image from the Dockerfile or are there out-of-the-box images out there?

Alvin: There are many pre-created images out there, take a look at https://hub.docker.com/explore/. The result of building a Dockerfile is an image. A container is instantiated from an image at runtime. So the image contains everything that is needed for execution. Under the covers, each line of the Dockerfile may cause an action of the file-system – for example, to load an O/S package. Each change creates a “layer,” i.e., a set of changes from the previous state of the file-system. Each line in the Dockerfile builds another layer until you get the final image. Each of these layers can be reused by other images.


Q: Is the code for this demo available on GitHub or other source code repository?

Alvin: Yes, https://github.com/alvinr/docker-demo


Q: Does Docker support MariaDB replication? I see you emphasized on clustering and not replication.

Alvin: The Docker image we used in the demo uses Galera Cluster for replication. The entrypoint script that is also in the image will take care of configuration of MariaDB and Galera, and makes sure that the databases are clustered and replicated. The demo showed MariaDB cluster, which is a multi-master solution for high availability. The base Docker image does not configure master/slave replication. This is on the roadmap to deliver to the Docker Store later this year.


Q: How do containers influence database performance if you store data inside a container?

Alvin: At DockerCon last week, Netflix stated that the containers have a < 0.1% performance penalty at runtime. Obviously, your mileage may vary. Storing the data inside the container adds more overhead, it impacts the I/O to the file-system. In the case of a container, this is the ‘union’ file-system (or AUFS) that the image is created from. Using a mounted volume means that you are writing directly to the host's file-system (e.g., EXT4, XFS), so you should have better performance.


Q: Are there best practices for deploying schema changes in dev and production? It appears you have a single file for dev which is fine when starting from scratch, but production will need deltas from its current state. It seems that ideally we'd use the same provisioning approach to both dev and production.

Alvin: There are many tools for managing schema migration. Some are independent and others are built into the language frameworks. The choice is largely personal. For the sake of simplicity of the demo, the same image was used for dev and production.


Q: Can you expose a MariaDB instance over the local network so MySQL Workbench can connect to it?

Alvin: Yes, the MariaDB container can be accessed from outside of the container world. I would have needed to install a MySQL client on my Mac. Call me lazy, but it is simpler for me to run the client in another container!


Q: You mentioned MariaDB Cluster … does that use Galera for clustering?

Alvin: Yes, MariaDB Cluster packages Galera.


Q: How are you handling data persistence? I did not see persistent data volumes in your Dockerfile/docker-compose.yml. If the container goes down, that data will remove itself?

Alvin: Correct, for the demo we stored the data inside the container. The last section in the presentation discussed the merits (and complications) of storing data outside of the container. Presently, this requires using one of several vendor technologies in order to map the storage back into the correct container on restart. This is still a weak area of the Docker ecosystem in my view.


Q: Can you show some way to bootstrap and restart Galera Cluster which is running with Docker?

Alvin: Using service discovery (in our example we used DNS), cluster can be self-formed and maintained. Docker is introducing an event endpoint so that direct manipulation of configuration could be orchestrated (e.g., adding and removing nodes). This is waiting for a pull request to be merged by Docker – see PR #26331.


Q: What level of coding skills are required to stand up a dev and prod box?

Alvin: It’s a simple set of CLI commands – however, testing and validating the operations use cases may require setup and config changes – for example, your tolerance for data loss during a failover event, etc.


Q: Could you reiterate the methods/benefits/downsides for persisting data (volumes, etc.) for transient Docker instances? Do MariaDB DBAs seem to have a preference based on their backup/recovery practices?

Alvin: Storing data outside of the container on a mounted volume means that the data will survive a container crash, stop/start cycle, etc. Using an external volume means that you can then use tools to manage that data volume. For example, if this is an EBS volume, then you could use snapshots for backup, etc. There are several options for backup/restore. The choice will depend on data size and operational needs; it’s so hard to recommend a specific solution. More details here: https://mariadb.com/kb/en/mariadb/backup-and-restore-overview/


Q: In case the data is stored in a different container ... how do we ensure the data is persistent, what do you suggest? Like cluster above data containers, or replica to backup files in other system, outside of containerization ... or any other?

Alvin: If you use a data container for storage, then you will still need a solution for data replication. For example, you could use binlog replication in order to ensure other copies exist, or a “FLUSH TABLES WITH READ LOCK” and then a snapshot. If you are mounting the volume from a storage array, then that array may have the capability to replicate data as well. So there are many choices here.


Q: When you start MariaDB, 1st node needs to start with bootstrap flag on, and others in regular way, so what’s the right way to restart cluster, because I can’t restart the 1st node again with bootstrap flag?

Alvin: The Docker entrypoint script we used relies on doing a DNS dig in order to find the other service instances. Docker creates a virtual IP for the service, then IPs for each instance (or in Docker terms, “task”) for the service. Therefore, the Galera Cluster is always started with a “seed” which gets modified if other tasks are discovered in DNS.

 

This week’s webinar with Alvin Richards on getting started with MariaDB on Docker was extremely popular but unfortunately we weren’t able to get to all the audience questions at the end. We sat down with Alvin after the webinar to review the questions and get his responses. Check it out!

Jay DAVE

Jay DAVE

Sat, 04/29/2017 - 19:38

Docker\MariaDB

I am just curious and want to know why to do clustering\replication for Docker Images?
Docker is used for specific purpose then why to complicate with clustering & replication within it.

Login or Register to post comments

by MariaDB Team at April 28, 2017 06:27 PM

April 27, 2017

Peter Zaitsev

Percona Live 2017: Beringei – Facebook’s Open Source, In-Memory Time Series Database (TSDB)

Beringei

BeringeiSo that is just about a wrap here at Percona Live 2017 – except for the closing comments and prize giveaway. Before we leave, I have one more session to highlight: Facebook’s Beringei.

Beringei is Facebook’s open source, in-memory time series database. Justin Teller, Engineering Manager at Facebook, presented the session. According to Justin, large-scale monitoring systems cannot handle large-scale analysis in real time because the query performance is too slow. After evaluating and rejecting several disk-based and existing in-memory cache solutions, Facebook turned their attention to writing their own in-memory TSDB to power the health and performance monitoring system at Facebook. They presented “Gorilla: A Fast, Scalable, In-Memory Time Series Database (http://www.vldb.org/pvldb/vol8/p1816-teller.pdf)” at VLDB 2015.

In December 2016, they open sourced the majority of that work with Beringei (https://github.com/facebookincubator/beringei). In this talk, Justin started by presenting how Facebook uses this database to serve production monitoring workloads at Facebook, with an overview of how they use it as the basis for a disaster-ready, high-performance distributed system. He closed by presenting some new performance analysis comparing (favorably) Beringei to Prometheus. Prometheus is an open source TSDB whose time series compression was inspired by the Gorilla VLDB paper and has similar compression behavior.

After the talk, Justin was kind enough to speak briefly with me. Check it out:

It’s been a great conference, and we’re looking forward to seeing you all at Percona Live Europe!

by Dave Avery at April 27, 2017 11:20 PM

Percona Live 2017: Hawkular Metrics, An Overview

Hawkular Metrics

Hawkular MetricsThe place is still frantic here at Percona Live 2017 as everybody tries to squeeze in a few more talks before the end of the day. One such talk was given by Red Hat’s Stefan Negrea on Hawkular Metrics.

Hawkular Metrics is a scalable, long-term, high-performance storage engine for metric data. The session was an overview of the project that includes the history of the project, an overview of the Hawkular ecosystem, technical details of the project, developer features and APIs and third party integrations.

Hawkular Metrics is backed by Cassandra for scalability. Hawkular Metrics is used and exposed by Hawkular Services.The API uses JSON to communicate with clients.

Users of Hawkular Metrics include:

  • IoT enthusiasts who need to collect metrics, and possibly trigger alerts
  • Operators who are looking for a solution to store metrics from statsD, collectd, syslog
  • Developers of solutions who need long-term time series database storage
  • Users of ManageIQ who are looking for Middleware management
  • Users of Kubernetes/Heapster who want to store Docker container metrics in a long-term time series database storage, thanks to the Heapster sink for Hawkular.

Stefan was kind enough to speak with me after the talk. Check it out below:

There are more talks today. Check out Thursday’s schedule here. Don’t forget to attend the Closing Remarks and prize give away at 4:00 pm.

by Dave Avery at April 27, 2017 10:23 PM

Percona Live 2017: Lessons Learned While Automating MySQL Deployments in the AWS Cloud

Percona Live 2017

Automating MySQLThe last day of Percona Live 2017 is still going strong, with talks all the way until 4:00 pm (and closing remarks and a prize giveaway on the main stage then). I’m going to a few more sessions today, including one from Stephane Combaudon from Slice Technologies: Lessons learned while automating MySQL deployments in the AWS Cloud.

In this talk, Stephane discussed how automating deployments is a key success factor in the cloud. It is actually a great way to leverage the flexibility of the cloud. But often while automation is not too difficult for application code, it is much harder for databases. When Slice started automating their MySQL servers at Slice, they chose simple and production-proven components: Chef to deploy files, MHA for high availability and Percona XtraBackup for backups. But they faced several problems very quickly:

  • How do you maintain an updated list of MySQL servers in the MHA configuration when servers can be automatically stopped or started?
  • How can you coordinate your servers for them to know that they need to be configured as a master or as a replica?
  • How do you write complex logic with Chef without being trapped with Chef’s two pass model?
  • How can you handle clusters with different MySQL versions, or a single cluster where all members do not use the same MySQL version?
  • How can you get reasonable backup and restore time when the dataset is over 1TB and the backups are stored on S3?

This session discussed the errors Slice made, and the solutions they found while tackling MySQL automation.

Stephane was kind enough to let me speak with him after the talk: check it out below:

There are more talks today. Check out Thursday’s schedule here. Don’t forget to attend the Closing Remarks and prize give away at 4:00 pm.

by Dave Avery at April 27, 2017 07:37 PM

Percona Live 2017: Day Three Keynotes

Percona Live Keynotes

Welcome to the third (and final) day of the Percona Live Open Source Database Conference 2017, and the third (and final) set of Percona Live keynotes! The enthusiasm hasn’t waned here at Percona Live, and we had a full house on Thursday morning!

Day three of the conference kicked off with three keynotes talks, and ended with the Community Awards Ceremony:

Percona Live Keynote AirBnBSpinaltap: Airbnb’s Change Data Capture System

Xinyao Hu (AirBnB)

In this talk, Xinyao introduced Airbnb’s change data change system, Spinaltap. He briefly covered its design, and focused on various use cases inside Airbnb. These use cases covered both online serving production and offline large distributed batch processing.

Percona Live Keynote PerconaHow Percona Contributes to the Open Source Database Ecosystem

Peter Zaitsev (Percona)

Peter Zaitsev, CEO of Percona, discussed the growth and adoption of open source databases, and Percona’s commitment to remaining an unbiased champion of the open source database ecosystem. Percona remains committed to providing open source support and solutions to its customers, users and the community. He also provided updates and highlighted exciting new developments in Percona Server software for MySQL and MongoDB.

Percona Live Keynote BookingMonitoring Booking.com without looking at MySQL

Jean-François Gagné (Booking.com)

Jean-François Gagné presented a fascinating talk about using a metric for observing Booking.com’s system health: bookings per second. It wasn’t a technical deep-dive (not MySQL- or Linux-related) but it is one of the most important metric Booking.com has to detect problems (and customer behavior) on the website. Many things impact this metric, including the time of the day, the day of the week or the season of the year.

Percona Live Keynote Community AwardsCommunity Award Ceremony

Daniel Nichter (Square), Emily Slocombe (SurveyMonkey)

The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporations for their contributions to the MySQL ecosystem. It is a from-the-community, by-the-community and for-the-community effort. Awards are given for Community Contributor, Application, and Corporate Contributor. More information can be found here: http://mysqlawards.org.

This year’s winners were:

  • Community: René Cannaò, Simon Mudd, Shlomi Noach
  • Application: Sysbench, Gh-ost
  • Corporate: GitHub, Percona

Congrats to the winners, the entire open source community, and to all the Percona Live attendees this year. There are still sessions today, check them out.

It’s been a great conference, and we’re looking forward to seeing you all at Percona Live Europe!

by Dave Avery at April 27, 2017 05:58 PM

Percona Live 2017: MySQL Makes Toast

MySQL Makes Toast

MySQL Makes ToastEvery day at Percona Live 2017 brings something new and unusual – and on this particular day, we found out that MySQL makes toast.

A lot of people think that with MySQL and open source software, you can do anything. While many might view this metaphorically, Percona’s Alexander Rubin (Principal Consultant) takes this statement very seriously. He demonstrated on Tuesday at Percona Live that not only is possible to accomplish just about anything with MySQL, but MySQL makes toast!

Originally, Alexander took on this project to provide an open source fix for MySQL Bug#2 (MySQL Doesn’t Make Toast). After some effort, and some ingenuity, he provided a patch for the infamous bug.

(You can find out all the details in his blog post here.)

Read up on how this was accomplished, and check out the pics below of Alexander demonstrating his ingenious method of grilling breakfast bread!

 

MySQL Makes Toast

Alex Prepares to Amaze the Crowd with an Open Source Breakfast

MySQL Makes Toast

The Crowd Gathers for a Tasty MySQL-Born Treat

MySQL Makes Toast

Open Source Breakfast is Tiring, Time for a Rest

Don’t miss any of the fun tomorrow! You can find Thursday’s (4/27) session schedule here.

by Dave Avery at April 27, 2017 03:56 AM

Percona Live 2017: Database Management Made Simple – Amazon RDS

Darin Amazon RDS

Amazon RDSPercona Live 2017 is done for Wednesday, but there was still time to get in one more talk before tonight’s Community Networking Reception – and the last one of the evening was about Amazon RDS.

Darin Briskman, Lead Developer Outreach & Technical Evangelist for Amazon, held two back-to-back sessions on Database management made simple – Amazon RDS. Amazon Relational Database Service (or Amazon RDS) is a distributed relational database service by Amazon Web Services (AWS).

Darin reviewed how Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. He showed how it provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you to focus on your applications and business. This talk provided guidance and tips for optimizing MySQL-compatible workloads on RDS.

Darin was kind enough to speak with me about his talk afterward. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

by Dave Avery at April 27, 2017 03:28 AM

Percona Live 2017: Histograms in MySQL and MariaDB

Histograms

HistogramsThe afternoon at Percona Live 2017 is slipping by quickly, and people are still actively looking for sessions to attend – like the session I just sat in on histograms in MySQL and MariaDB.

Histograms are a type of column statistic that provides more detailed information about data distributions in table columns. A histogram sorts values into buckets.

MariaDB Server has had histograms since MariaDB 10.0. Now, MySQL 8.0 will have them too. This session presented an overview of histogram implementations in MariaDB, MySQL 8.0, and looked at PostgreSQL for comparison. The session covered everything about histograms:

  • Why do query optimizers need histograms
  • What are the costs of collecting and maintaining a histogram in each database
  • How the query optimizers use histogram data
  • What are the strong and weak points of histogram implementation in each database

At the end, Sergei talked a bit about a related development in MariaDB Server: the optimizer will have the capability of using constraints.

Sergei was kind enough to speak briefly with me after his talk on histograms in MySQL and MariaDB. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

by Dave Avery at April 27, 2017 12:00 AM

April 26, 2017

Peter Zaitsev

Percona Live 2017: Deploying MongoDB on Public Clouds

MongoDB on Public Clouds

MongoDB on Public CloudsToday at Percona Live 2017, the afternoon is jam-packed with open source technology lectures filled with community members eager for the latest on the best strategies – including how you should deploy MongoDB on public clouds.

Dharshan Rangegowda (CEO of ScaleGrid) discussed deploying MongoDB on public clouds. ScaleGrid provides a fully managed Database-as-a-Service (DBaaS) solution used today by thousands of developers, startups, and enterprise customers. In this session, Dharshan talked about how public clouds like AWS and Azure have become very popular platforms over the past few years. These public clouds provide a plethora of infrastructure features to help make life easier, He dug into the features/assets that one should be actively leveraging.

On the flip side, there are also a number of potential pitfalls that require attention and might need a workaround. Dharshan reviewed some common architecture patterns you need to have in place to be successful with MongoDB on the public cloud, including high availability, disaster recovery, scaling, performance and others.

After the lecture, Dharshan was kind enough to talk briefly with me about his session. Check it out:

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

by Dave Avery at April 26, 2017 10:27 PM

Percona Live 2017: A Deep-Dive Into What’s New in Amazon Aurora

Percona Live 2017

PostgresPercona Live 2017 is rolling along, and now that everybody got through lunch we’re all recharged and ready for the afternoon. Let’s start it out with Amazon Aurora.

Amazon AuroraOnce of the best-attended sessions was Sailesh Krishnamurthy’s (Senior Engineering Manager at Amazon Web Services) talk on a deep dive into what is new in Amazon Aurora. Amazon Aurora is a fully managed relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. It is purpose-built for the cloud using a new architectural model and distributed systems techniques to provide far higher performance, availability and durability than previously possible using conventional monolithic database architectures.

Amazon Aurora packs a lot of innovations in the engine and storage layers. In this session, Sailesh looked at some of the key innovations behind Amazon Aurora, new improvements to Aurora’s performance, availability and cost-effectiveness and discussed best practices and optimal configurations.

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

by Dave Avery at April 26, 2017 09:34 PM

Percona Live 2017: Day Two Keynotes

Percona Live 2017

Welcome to the second day of the Percona Live Open Source Database Conference 2017, and the second set of Percona Live keynotes! It’s a bit rainy outside today, but that isn’t bothering the Percona Live attendees (we’re all indoors learning about new open source technologies)!

Day two of the conference kicked off with another four keynote talks, all of which discussed issues and technologies that are addressed by open source solutions:

Percona Live KeynotesThe Open Source Database Business Model is Under Siege

Paul Dix (InfluxData)

Paul Dix’s keynote may have ruffled a few feathers, as he looked at possible futures for the open source software model (and community). The traditional open source infrastructure business model relied on the backing company providing either support and professional services or closed source tools to provide additional functionality like production tooling, enhanced security and monitoring. For a time this model was good, and it worked. But will it be feasible in the future to maintain this model, given the nature of how markets are developing? Paul discussed various reasons why he thinks the answer is “no.”

Percona Live KeynotesKeynote Panel (Percona, VividCortex, Continuent)

Richard Hipp (SQLite.org), Baron Schwartz (VividCortex), MC Brown(Continuent), Peter Zaitsev (Percona)

Join Percona and the Diamond sponsors of the conference, VividCortex and Continuent to talk database trends. They provided their thoughts on Paul’s talk (above), time series data and database development, whether SQL databases should continue to develop for “niche” functions and what we should be looking for at Percona Live 2020.

Percona Live KeynotesMySQL 8.0: Powering the next generation of Web, SaaS, Cloud

Tomas Ulin (Oracle)

Tomas Ulin, VP of MySQL Engineering at Oracle, provided this morning’s audience with a big picture overview of MySQL 8.0 development priorities, features that are available, and what is coming in upcoming releases. He covered MySQL 5.7, MySQL 8.0 and InnoDB Cluster. He also discussed MySQL’s roadmap and featured some benchmark performance comparisons.

Percona Live KeynotesThe Future Of Monitoring Is Distributed

Baron Schwartz (VividCortex)

Baron Schwartz of VividCortex gave an insightful lecture on “observability” in the data layer. The world of technology is undergoing a rapid and permanent shift. He pointed out how we all know that we’re moving to the cloud at an accelerating pace. The fundamental change that’s taking place today is that our applications are far more distributed than they’ve ever been, in nearly invisible ways. And that’s not good, because invisible means unmeasurable. He posited that “observability” is the new goal for all the data that is available to businesses, and that businesses should make decisions based on what the data says about what customers want.

All the keynotes today highlighted the many different aspects of the open source database community that come together to solve database challenges. Percona Live runs through Thursday 4/27. Check out tomorrow’s keynotes here, as well as the numerous breakout sessions with top open source database experts.

by Dave Avery at April 26, 2017 07:46 PM

Migrate from TokuMX to Percona Server for MongoDB

This blog post details how to migrate from TokuMX to Percona Server for MongoDB.

As part of our ongoing plans to embrace the MongoDB community, we have increased support and software around MongoDB. Percona’s last release of the TokuMX software platform was on September 15th, 2015.  Percona is announcing a General EOL of TokuMX Support as of May 1st, 2017.

If you are currently a MongoDB Support customer running TokuMX, we highly recommend migrating to Percona Server for MongoDB 3.4.x (and the WiredTiger or MongoRocks storage engine). We would be happy to assist in this migration through our regular Support and Consulting communications channels.

A Brief History of TokuMX

A group of engineers at TokuTek started the TokuMX project. The TokuFT fractal tree engine, now called PerconaFT, was already implemented as the MySQL storage engine plug-in known as TokuDB. The group needed new database technologies where a fractal tree might be of use. MongoDB 2.2 had serious performance limitations and inadequate concurrency and durability, making it a good candidate. The team committed the first work on TokuMX to the source tree in September of 2012. TokuMX v1.0 was released by June 2013.

When TokuMX was first implemented, there was no storage engine interface in the MongoDB code. This meant that the integration of the fractal tree required considerable changes to the base code of MongoDB 2.x. They made very impressive performance gains and implemented a full SQL-style stateful transaction interface. This allowed multi-operation, multi-document, and multi-collection concurrent transactions to remain consistent.

In mid-2014, MongoDB, Inc. began work on a storage engine API in the 2.7 development code base. A few months later, the MongoDB storage engine implementation of TokuFT began as a project known as TokuMXse. This became the basis for the PerconaFT storage engine in Percona Server for MongoDB 3.0. During this time, MongoDB, Inc. also worked closely with WiredTiger to implement their high-speed storage engine which also provided better concurrency and durability. In December of 2014, MongoDB, Inc. acquired WiredTiger. The WiredTiger storage engine was released with MongoDB 3.0.0 a few months later in March 2015.

The reason Percona Server for MongoDB does not implement stateful transactions:

Unfortunately, some core design decisions in MongoDB 3.x made it impractical to implement the stateful transactions that SQL databases and TokuMX have. The MongoDB 3.x storage engine API is very closely tied to a concurrency model known as optimistic concurrency. This is implemented using C++ exceptions which throw and retry a single operation until it doesn’t conflict with another. This strategy has advantages and disadvantages. Developers don’t need to worry about implementing advanced transaction handlers for deadlocks and rollbacks. However, MongoDB doesn’t support transactions that involve multiple operations, documents or collections defined and controlled by the user application. MongoDB can’t implement this without far-reaching modifications to the MongoDB code base.

Without a major redesign of the MongoDB 3.x storage engine layer and the higher level database code, SQL style stateful transactions will not be possible. The MongoDB 3.x design also dramatically reduced the performance of the fractal tree engine which is designed around the traditional ACID model and does not support optimistic concurrency. As a result, we deprecated the fractal tree engine (PerconaFT) and removed it from Percona Server for MongoDB 3.4.

Performing a Migration

There are a few things to consider before beginning migration. Percona Server for MongoDB doesn’t implement a few TokuMX features.

Some TokuMX commands that are not available in PSMDB

  •  Stateful transaction commands
    • beginTransaction
    • rollbackTransaction
    • commitTransaction
  • Bulk loader commands
    • beginLoad
    • commitLoad
    • abortLoad
  • Partition commands
    • addPartition
    • dropPartition
    • getPartitionInfo
    • replAddPartition
    • clonePartitionInfo

If your application depends on these features, migration may require some changes to function correctly.  You will also want to review the upstream MongoDB 2.x to 3.x documentation as well as the custom commands section of the TokuMX documentation to identify differences between the versions.

There are two methods to migrate your data from TokuMX to Percona Server for MongoDB. We’ve documented the methods in a Percona Lab GitHub repository.

Migrate from TokuMX to Percona Server for MongoDB

If you have any questions or concerns about your TokuMX to Percona Server for MongoDB migration, please contact Percona Support.  We are here to help 24x7x365 online or by phone.

by David Bennett at April 26, 2017 06:41 PM

Jean-Jerome Schmidt

Announcing ClusterControl 1.4.1 - the ProxySQL Edition

Today we are pleased to announce the 1.4.1 release of ClusterControl - the all-inclusive database management system that lets you easily deploy, monitor, manage and scale highly available open source databases - and load balancers - in any environment: on-premise or in the cloud.

This release contains key new management features for MySQL and MariaDB load balancing with ProxySQL, along with performance improvements and bug fixes.

Release Highlights

For ProxySQL

  • Support for:
    • MySQL Galera in addition to Replication clusters
    • Active-standby HA setup with Keepalived
    • Use the Query Monitor to view query digests
  • Management features:
    • Manage Query Rules (Query Caching, Query Rewrite)
    • Manage Host Groups (Servers)
    • Manage ProxySQL Database Users
    • Manage ProxySQL System Variables

For Galera Cluster for MySQL & Replication

  • Manage MySQL Galera and Replication clusters with management/public IPs
    • For monitoring connections and data/private IPs for replication traffic
  • Add MySQL Galera nodes or Replication Read Slaves with management and data IPs

Download ClusterControl

View release details and resources

Load balancers are an essential component in MySQL and MariaDB database high availability; especially when making topology changes transparent to applications and implementing read-write split functionality. As we all know, high-traffic database applications draw an enormous amount of queries daily. Which is why DBAs and SysAdmins require reliable technology solutions that can automatically scale to handle those connections while remaining available for still more.

And this is where load balancing technologies such as HAProxy, MaxScale and now ProxySQL come in.

ClusterControl has always come with support for HAProxy, as a generic TCP load balancer. We then added support for MariaDB’s MaxScale, an SQL-aware load balancer.

And today we’re happy to announce management support for ProxySQL, a lightweight yet complex protocol-aware proxy that sits between the MySQL clients and servers, in addition to the deployment and monitoring features for ProxySQL we announced two months ago.

Unlike others, ProxySQL understands MySQL protocol, which allows the implementation of features otherwise impossible to implement. For example, ProxySQL is the only proxy supporting connections multiplexing and query caching.

With that said, the new management features in ClusterControl include the following:

MySQL Galera in addition to Replication clusters

Up until now, ClusterControl enabled users to deploy ProxySQL on MySQL Replication clusters and monitor its performance. The same is now true for Galera Cluster for MySQL, MariaDB Galera Cluster and Percona XtraDB. This also includes active-standby HA setups with Keepalived.

Use the Query Monitor to view query digests

ClusterControl offers unified and comprehensive real-time monitoring of your entire database and server infrastructure. You can easily visualize performance in custom dashboards to establish operational baselines and support capacity planning. And with comprehensive reports for ProxySQL, you have a clear view of data points like connections, queries, data transfer and utilization, and more.

For more information on how monitoring works in ProxySQL, see our blog post on MySQL Load Balancing with ProxySQL - An Overview.

Management features

With ClusterControl, you can now easily configure and manage your ProxySQL deployments with its comprehensive UI. You can create servers, reorientate your setup, create users, set rules, manage query routing, and enable variable configurations. The new management features in ClusterControl for ProxySQL include:

Manage Query Rules (Query Caching, Query Rewrite)

  • View running queries, create rules or cache and rewrite queries on the fly.

Manage Host Groups (Servers) - ProxySQL uses a concept of hostgroups - a group of different backends which serve the same purpose or handle similar type of traffic.

  • Add or remove servers to existing and new host groups.

Manage ProxySQL Database Users

  • Create new DB users or add existing MySQL users to ProxySQL.

Manage ProxySQL System Variables

  • View and change global runtime variables for tweaking your ProxySQL instance.

For more information on how ProxySQL helps with MySQL query cache, query rewrite, and on ProxySQL’s host groups, read our blog on How ProxySQL adds Failover and Query Control to your MySQL Replication Setup.

There are a number of other features and improvements that we have not mentioned here. You can find all details in the ChangeLog.

We encourage you to test this latest release and provide us with your feedback. If you’d like a demo, feel free to request one.

Thank you for your ongoing support, and load balancing!

PS.: For additional tips & tricks, follow our blog: https://severalnines.com/blog/

by Severalnines at April 26, 2017 12:41 PM

Peter Zaitsev

Percona Live 2017: Real-Time Data Loading from MySQL and Oracle into Analytics/Big Data

Real-Time Data Loading

PostgresIt looks like the first day of the Percona Live Open Source Database Conference 2017 is coming to a close. Before we shut it down for today, we’ll look at a presentation on Real-Time Data Loading from MySQL and Oracle into Analytics/Big Data.

In this session, Continuent’s VP of Products MC Brown looked at Tungsten Replicator, which enables real-time and efficient replication of data from your transactional database. He focused on the filtering side, for massaging your data before/during replication.

Continuent is back with their Tungsten Replicator product, after splitting off from VMware. You can learn more about that process from the earlier keynote session today.

During this session, MC covered various solutions used by Continuent’s customers, some of the complex deployment models, and how that information can be modified as part of the load into analytics targets. He also looked into how to replicate to non-transactional environments and how to customize and develop your own appliers and customizations. This included an examination of the applier system and the JavaScript environment for batch applications.

MC was kind enough to speak with me after his session. Check it out below:

Don’t miss any of tomorrow’s talks! You can find Wednesday’s (4/26) session schedule here.

by Dave Avery at April 26, 2017 01:10 AM

April 25, 2017

Peter Zaitsev

Percona Live 2017: Designing your SaaS Database for Scale with Postgres

Postgres

PostgresThe Percona Live Open Source Data Conference 2017 day one is rolling right along, and we’re already in the afternoon sessions. In this blog, we’ll look at Citus Data’s presentation on how to design your SaaS database to scale with Postgres.

If you’re building a SaaS application, you probably already have the notion of tenancy built in your data model. Typically, most information relates to tenants/customers/accounts and your database tables capture this natural relation. With smaller amounts of data, it’s easy to throw more hardware at the problem and scale up your database. As these tables grow, however, you need to think about ways to scale your multi-tenant database across dozens or hundreds of machines. In this talk, Citus Data’s Lukas Fittl and Ozgun Erdogan (CTO) talked about the motivations behind scaling your SaaS (multi-tenant) database and several heuristics they found helpful in deciding when to scale.

They then described three design patterns that are common in scaling SaaS databases:

  1. Create one database per tenant
  2. Create one schema per tenant
  3. Have all tenants share the same table(s).

Next, they highlighted the tradeoffs involved with each design pattern and focused on one pattern that scales to hundreds of thousands of tenants. They also shared an example architecture from the industry that describes this pattern in more detail. Lastly, they talked about key PostgreSQL properties, such as semi-structured data types, that make building multi-tenant applications easy.

After the talk, Lukas and Ozgun were kind enough to speak with me about their session. Check it out below:

Don’t miss any of tomorrow’s session. See Wednesday’s (4/26) full schedule here.

by Dave Avery at April 25, 2017 11:29 PM

Percona Live 2017 – MySQL 8.0: Major New Features

MySQL 8.0

MySQL 8Breakout sessions are in full swing at the Percona Live Open Source Database Conference 2017. Check out what’s new in MySQL 8.0.

We’ve finished with this morning’s sessions, and this morning saw a lot of amazing open source talks. One of the most well-attended was MySQL 8.0: Major New Features given by Geir Høydalsvik, Senior Software Development Director at Oracle. MySQL is the next major version of the MySQL platform from Oracle, the open source community is expectantly waiting for the official release.

In this session, Geir described many of the new features announced for MySQL 8.0. In addition to Data Dictionary, CTEs and Windows functions, the session covered:

  • Move to utf8 (mb4) as MySQL’s default character set
  • Language specific case insensitive collation for 21 languages (utf8)
  • Invisible index
  • Descending indexes
  • Improve usability of UUID and IPV6 manipulations
  • SQL roles
  • SET PERSIST for global variable values
  • Performance Schema, instrumenting data locks
  • Performance Schema, instrumenting error messages
  • Improved cost model with histograms

The presentation ended with some words on scalability, plugin infrastructure, and GIS.

After the talk, Geir was kind enough to speak with me for a minute or so about his talk. Check out the video below to see MySQL’s 8.0 features.

Don’t miss tomorrow’s sessions! You can check out the schedule for tomorrow (4/26) here.

by Dave Avery at April 25, 2017 09:35 PM

Percona Live 2017: Day One Keynotes

Welcome to the first day of the Percona Live Open Source Database Conference 2017, and the first set of Percona Live keynotes!

It’s a beautiful day in Santa Clara, but we don’t know because we’re inside the Hyatt Regency Convention Center listening to various rock stars in the open source database community talk about open source technologies. Day one of the conference kicked off with four keynote talks, all of which discussed issues and technologies that are addressed by open source solutions:

Percona Live KeynotesPercona Welcoming Keynote

Peter Zaitsev (Percona)

Peter Zaitsev, CEO of Percona, welcomed everyone to Percona Live Open Source Database Conference 2017 and discussed the history of Percona Live. Percona Live has grown significantly over the years, changing from just a MySQL conference into an open source database technology conference. This change has mirrored the growth and acceptance of open source technologies in what traditionally were commercial marketplaces.

Percona Live KeynotesContinuent is back! But what does Continuent do Anyway?

Eero Teerikorpi, MC Brown (Continuent)

Eero Teerikorpi from Continuent discussed how Continuent as a company has developed over the years – from startup to acquisition by VMware back to separate entity, with regards to its Tungsten database replication and clustering product. Eero explained how Continuent’s swan logo represents it’s long relationship it has had with its customers, and how its Tungsten product is an outstanding replication solution. Eero asked select Continuent customers to tell everyone about how they use their multi-site/multi-master and advanced replication solutions, and explain how Tungsten helped them solve database replication issues.

Percona Live KeynotesOpen Source Database Ecosystem

Peter Zaitsev (Percona), Colin Charles (Percona), Dmitry Andreev (Yandex), Justin Teller (Facebook), Tal Levy (Elastic Search), Björn Rabenstein (SoundCloud Ltd.), Paul Dix (InfluxData), Michael J. Freedman (TimescaleDB)

For our third keynote, we had a panel of speakers from a variety of open source companies: Yandex, Facebook, Elastic Search, SoundCloud, InfluxData,TimescaleDB and of course Percona. The topic was the explosion of time series data. Percona sees time series databases as a trend of 2017, hence the idea of having quick 5-minute lightning talks from projects that are stellar. With the increasing availability of IoT devices and the data they provide, time series data is more and more important in the database landscape. All of these companies provide people with an interest in capturing, monitoring, and analyzing time series data could use their various solutions for that purpose.

Percona Live KeynotesSQLite: The Most Important Software Component That Many People Have Never Heard Of

Richard Hipp (SQLite.org)

The final keynote this morning was a discussion with the creator of SQLite, one of the most used RDBMS. Rather than use a client-server model, SQLite is embedded in the application. There are more instances of SQLite running today than all other database engines combined. This talk reviewed the features, capabilities, limitations, and usage patterns of SQLite and asks why you are not using SQLite more yourself.

All the keynotes today highlighted the many different aspects of the open source database community that come together to solve database challenges. Percona Live runs through Thursday 4/27. Check out tomorrow’s keynotes here, as well as the numerous breakout sessions with top open source database experts.

by Dave Avery at April 25, 2017 08:44 PM

Percona Live 2017 Tutorials Day

Percona Live 2017 Tutorials

Percona Live 2017 Tutorials RegistrationWelcome 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 ActionPercona Live 2017 Tutorials Sveta Alex

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 2017Percona Live 2017 Tutorials Colin

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 GolbaBarrett 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!

by Dave Avery at April 25, 2017 05:38 AM

April 24, 2017

Peter Zaitsev

Improved wsrep-stages and related instrumentation in Percona XtraDB Cluster

wsrep-stages

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

Introduction

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)

Conclusion

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.

by Krunal Bauskar at April 24, 2017 09:22 PM

Percona XtraDB Cluster: SST tmpdir option

wsrep-stages

SST tmpdirIn 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.

by Kenn Takara at April 24, 2017 08:46 PM

Jean-Jerome Schmidt

Webinar Replay and Q&A: High Availability in ProxySQL for HA MySQL infrastructures

Thanks to everyone who participated in our recent webinar on High Availability in ProxySQL and on how to build a solid, scalable and manageable proxy layer using ProxySQL for highly available MySQL infrastructures.

This second joint webinar with ProxySQL creator René Cannaò saw lots of interest and some nice questions from our audience, which we’re sharing below with this blog post as well as the answers to them.

Building a highly available proxy layer creates additional challenges, such as how to manage multiple proxy instances, how to ensure that their configuration is in sync, Virtual IP and fail-over; and more, which we’ve covered in this webinar with René. And we demonstrated how you can make your ProxySQL highly available when deploying it from ClusterControl (download & try it free).

If you missed the webinar, would like to watch it again or browse through the slides, it is available for viewing online.

Watch the webinar replay

Webinar Questions & Answers

Q.: In a MySQL master/slave pair, I am inclined to deploy ProxySQL instances directly on both master and slave hosts. In an environment of 100s of master/slave pairs, with new hosts being built all the time, I can see this as a good way to combine host / MySQL / ProxySQL master/slave pair deploys via a single Ansible playbook. Do you guys have any thoughts on this?

A.: Our only concern here is that co-locating ProxySQL with database servers can make the debugging of database performance issues harder - the proxy will add overhead for CPU and memory and MySQL may have to compete for those resources.

Additionally, we’re not really sure what you’d like to achieve by deploying ProxySQL on all database servers - where would you like to connect? To one instance or to both? In the first case, you’d have to come up with a solution to handling potentially hundreds of failovers - when a master goes down, you’d have to re-route traffic to the ProxySQL instance on a slave. It adds more complexity than it’s really worth. The second case also creates complexity: instead of connecting to one proxy, the application would have to connect to both.

Co-locating ProxySQL on the application hosts is not that much more complex regarding configuration management than deploying it on database hosts. Yet it makes it so much easier for the application to route traffic - just connect to the local ProxySQL instance over the UNIX socket and that’s all.

Q.: Do you recommend for multiple ProxySQL instances to talk to each other or is it preferable for config changes to rely on each ProxySQL instance detecting the same issue at the same time? For example, would you make ProxySQL01 apply config changes in proxysql_master_switchover.sh to both itself and ProxySQL02 to ensure they stay the same? (I hope this isn't a stupid question... I've not yet succeeded in making this work so I thought maybe I'm missing something!)

A.: This is a very good question indeed. As long as you have scripts which would ensure that the configuration is the same on all of the ProxySQL instances - it should result in more consistent configuration across the whole infrastructure.

Q.: Sometimes I get the following warning 2017-04-04T02:11:43.996225+02:00 Keepalived_vrrp: Process [113479] didn't respond to SIGTERM. and VIP was moved to another server ... I can send you the complete configuration keepalived ... I didn't find a solution as to why I am getting this error/warning.

A.: This may happen from time to time. Timeout results in a failed check which triggers VIP failover. And as to why the monitored process didn't respond to signal in time, that’s really hard to tell. It is possible to increase the number of health-check fails required to trigger a VIP move to minimize the impact of such timeouts.

Q.: What load balancer can we use in front of ProxySQL?

A.: You can use virtually every load balancer out there, including ProxySQL itself - this is actually a topology we’d suggest. It’s better to rely on a single piece of software than to use ProxySQL and then another tool which would be redundant - more steep learning curve, more issues to debug.

Q.: When I started using ProxySQL I had this issue "access denied for MySQL user"; it was random, what is the cause of it?

A.: If it is random and not systematic, it may be worth investigating if it is a bug. We strongly recommend to open an issue on github.

Q.: I have tried ProxySQL and the issue we faced was that after using ProxySQL to split read/write , the connection switched to Master for all reads. How can we prevent the connection?

A.: This is most likely a configuration issue, and there are multiple reasons why this may happen. For example, if transaction_persistent was set to 1 and reads were all within a transaction. Or perhaps the query rules in mysql_query_rules weren’t configured correctly, and all traffic was being sent to the default hostgroup (the master).

Q.: How can Service Discovery help me?

A.: If your infrastructure is constantly changing, tools like etcd, Zookeeper or Consul can help you to track those changes, detect and push configuration changes to proxies. When your database clusters are going up and down, this can simplify configuration management.

Q.: In the discussion on structure, the load balancer scenario was quickly moved on from because of its single point of failure. How about when having a HA load balancer using CNAMES (not IP) for example AWS ElasticLoadBalancer on TCP ports. Would that be a structure that could work well in production?

A.: As long as the load balancer is highly available, this is not a problem, because it’s not a single point of failure. ELB itself is deployed in HA mode, so having a single ELB in front of anything (database servers, a pool of ProxySQL instances) will not introduce a single point of failure.

Q.: Don't any of the silo approaches have a single point of failure in the proxy that is fronting the silo?

A.: Indeed, although it is not a single point of failure - it’s more like multiple points of failure introduced in the infrastructure. If we are talking about huge infrastructure of hundreds or thousands of proxies, a loss of very small subset of application hosts should be acceptable. If we are talking about smaller setups, it should be manageable to have a ProxySQL per application host setup.

Watch the webinar replay

by krzysztof at April 24, 2017 10:06 AM

April 23, 2017

Peter Zaitsev

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

wsrep-stages

dh key too smallIf 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 (https://weakdh.org/), 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

by Kenn Takara at April 23, 2017 04:16 PM

Percona XtraDB Cluster Transaction Replay Anomaly

wsrep-stages

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

Introduction

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

Anomaly

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).

End-result:

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.

by Krunal Bauskar at April 23, 2017 04:05 PM

BEWARE: Increasing fc_limit can affect SELECT latency

SELECT Latency

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

Introduction

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.

Conclusion

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).

by Krunal Bauskar at April 23, 2017 02:22 AM

April 22, 2017

Peter Zaitsev

Better Than Linear Scaling

Scalability

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: https://en.wikipedia.org/wiki/Scalability.

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 (https://en.wikipedia.org/wiki/Amdahl%27s_law), and later to the Universal Scalability Law (http://www.perfdynamics.com/Manifesto/USLscalability.html). 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 (http://vitess.io/) 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:

Scaling

Two shards:

Scaling

Six shards:

Scaling

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 127.0.0.1 -P6032 -e "DELETE FROM mysql_query_rules"
shards=$1
for i in {1..60}
do
hg=$(( $i % $shards + 1))
mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES ($i,1,'root','sbtest$is',$hg,1);"
done
mysql -u admin -padmin -h 127.0.0.1 -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

Scaling
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.

Conclusion

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.

by Vadim Tkachenko at April 22, 2017 10:54 PM

MariaDB Foundation

MariaDB 10.3.0 Alpha, 5.5.55 Stable, and Connector/J 2.0.0 RC now available

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.55 Stable (GA), MariaDB Galera Cluster 5.5.55 Stable (GA) and MariaDB Connector/J 2.0.0 RC. See the release notes and changelogs for details. Download MariaDB 5.5.55 Release Notes Changelog What is MariaDB 5.5? MariaDB APT and YUM Repository Configuration Generator Download MariaDB Galera Cluster […]

The post MariaDB 10.3.0 Alpha, 5.5.55 Stable, and Connector/J 2.0.0 RC now available appeared first on MariaDB.org.

by Daniel Bartholomew at April 22, 2017 04:03 PM

April 21, 2017

Peter Zaitsev

Percona Monitoring and Management 1.1.3 is Now Available

Percona Monitoring and Management

Percona Monitoring and ManagementPercona 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 pmmdemo.percona.com.

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.

by Alexey Zhebel at April 21, 2017 10:32 PM

Percona Server for MySQL in Docker Swarm with Secrets

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           "/entrypoint.sh "   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

*facepalm*

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 "/entrypoint.sh " 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.

by Andrew Moore at April 21, 2017 09:43 PM

Simplified Percona XtraDB Cluster SSL Configuration

wsrep-stages

Percona XtraDB Cluster SSLIn 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

Customization

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.

by Kenn Takara at April 21, 2017 05:09 PM

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

Percona PAM

Percona PAMIn 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: 172.16.0.10
Hostname: samba-10.example.com
Domain name: EXAMPLE.COM
DNS: 8.8.8.8(Google DNS), 8.8.4.4(Google DNS), 172.16.0.10(Samba)
Firewall: none

Percona Server 5.7 with LDAP authentication
OS: CentOS 7
IP Address: 172.16.0.20
Hostname: ps-ldap-20.example.com

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/samba-10.example.com 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 0.centos.pool.ntp.org
 7 Apr 06:06:07 ntpdate[9788]: step time server 202.90.132.242 offset 0.807640 sec
[root@samba-10 ~]# systemctl enable ntpd.service
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service 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 https://www.samba.org/samba/ftp/samba-latest.tar.gz
* * *
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: tevent.py -> python/tevent.py
* * *
[3773/3775] Linking default/source3/modules/libvfs_module_acl_xattr.so
[3774/3775] Linking default/source3/modules/libvfs_module_shadow_copy.so
[3775/3775] Linking default/source3/modules/libvfs_module_dirsort.so
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/libservice_module_s3fs.inst.so as /opt/samba/lib/service/s3fs.so
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
After=syslog.target network.target
[Service]
Type=forking
PIDFile=//opt/samba/var/run/samba.pid
ExecStart=/opt/samba/sbin/samba -D
ExecReload=/usr/bin/kill -HUP $MAINPID
ExecStop=/usr/bin/kill $MAINPID
[Install]
WantedBy=multi-user.target" > /etc/systemd/system/samba.service
[root@samba-10 samba-4.6.2]# systemctl enable samba.service
Created symlink from /etc/systemd/system/multi-user.target.wants/samba.service 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:

Realm: EXAMPLE.COM
Domain: EXAMPLE
Server Role: dc(domain controller)
DNS backend: SAMBA_INTERNAL
DNS forwarder IP address: 8.8.8.8

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) [8.8.8.8]: 8.8.8.8
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:            example.com
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/__init__.py", line 176, in _run
    return self.run(*args, **kwargs)
  File "/opt/samba/lib64/python2.7/site-packages/samba/netcmd/domain.py", line 471, in run
    nosync=ldap_backend_nosync, ldap_dryrun_mode=ldap_dryrun_mode)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 2175, in provision
    skip_sysvolacl=skip_sysvolacl)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1787, in provision_fill
    next_rid=next_rid, dc_rid=dc_rid)
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/__init__.py", line 1447, in fill_samdb
    "KRBTGTPASS_B64": b64encode(krbtgtpass.encode('utf-16-le'))
  File "/opt/samba/lib64/python2.7/site-packages/samba/provision/common.py", line 55, in setup_add_ldif
    ldb.add_ldif(data, controls)
  File "/opt/samba/lib64/python2.7/site-packages/samba/__init__.py", 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 0.0.0.0:464             0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 0.0.0.0:53              0.0.0.0:*               LISTEN      13302/samba
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      875/sshd
tcp        0      0 0.0.0.0:88              0.0.0.0:*               LISTEN      13296/samba
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1327/master
tcp        0      0 0.0.0.0:636             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      13307/smbd
tcp        0      0 0.0.0.0:1024            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:1025            0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:3268            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:3269            0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:389             0.0.0.0:*               LISTEN      13294/samba
tcp        0      0 0.0.0.0:135             0.0.0.0:*               LISTEN      13291/samba
tcp        0      0 0.0.0.0:139             0.0.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 ps-ldap-20.example.com 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://172.16.0.10
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:

Find:
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://172.16.0.10)
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://172.16.0.10")
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/multi-user.target.wants/nslcd.service 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 https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
Retrieving https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-4.noarch.rpm
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/multi-user.target.wants/mysqld.service 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 'auth_pam.so';
Query OK, 0 rows affected (0.01 sec)
mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
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 pam_ldap.so
account required pam_ldap.so

  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.

Conclusion

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.

by Jaime Sicam at April 21, 2017 04:20 PM

Jean-Jerome Schmidt

ClusterControl for Galera Cluster for MySQL

ClusterControl allows you to easily manage your database infrastructure on premise or in the cloud. With in-depth support for technologies like Galera Cluster for MySQL and MariaDB setups, you can truly automate mixed environments for next-level applications.

Since the launch of ClusterControl in 2012, we’ve experienced growth in new industries with customers who are benefiting from the advancements ClusterControl has to offer - in particular when it comes to Galera Cluster for MySQL.

In addition to reaching new highs in ClusterControl demand, this past year we’ve doubled the size of our team allowing us to continue to provide even more improvements to ClusterControl.

Take a look at this infographic for our top Galera Cluster for MySQL resources and information about how ClusterControl works with Galera Cluster.

<<

by Severalnines at April 21, 2017 03:04 PM

Peter Zaitsev

Enabling Percona XtraDB Cluster SST Traffic Encryption

dh key too small

Percona XtraDB Cluster SST Traffic EncryptionIn 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, http://www.dest-unreach.org/socat/doc/socat-openssltunnel.html. 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 https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/encrypt-traffic.html.

Configuration

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.

ssl-ca

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.

ssl-cert

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.

ssl-key

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

by Kenn Takara at April 21, 2017 02:48 PM

April 20, 2017

MariaDB AB

Webinar – Get Started with MariaDB on Docker

Webinar – Get Started with MariaDB on Docker MariaDB Team Thu, 04/20/2017 - 17:52

While containers can be great ephemeral vessels for your applications, your data needs to be able to survive containers coming and going, maintain its availability and reliability, and grow when you need it. 

Join MariaDB Field CTO Alvin Richards on April 27 at 10:00 a.m. PT to get started with MariaDB on Docker. This webinar will cover: 

  • Existing deployment models vs. containers 
  • Items to consider when deploying a database on Docker 
  • Steps to build and scale an application with MariaDB on Docker

Date: April 27, 2017
Time: 10:00 a.m. PT

Register here for the webinar. 

AlvinRichards.jpg

Alvin Richards
Field CTO, MariaDB

Alvin Richards is the Field CTO at MariaDB, the leading high-performance open source relational database, where he connects the dots between practitioners, innovators and MariaDB products. In his prior life, Alvin was vice president of product at Aerospike; ran engineering teams at Docker and MongoDB, leading the revolution of microservices and NoSQL; was technical director at NetApp, working to integrate databases and virtual infrastructures with storage; and worked at Oracle on data warehousing products.

While containers can be great ephemeral vessels for your applications, your data needs to be able to survive containers coming and going, maintain its availability and reliability, and grow when you need it. Join our upcoming webinar to learn how to build your own MariaDB container.

Login or Register to post comments

by MariaDB Team at April 20, 2017 09:52 PM

Peter Zaitsev

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

Percona Live Featured Session

Percona Live Featured SessionWelcome 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.

by Dave Avery at April 20, 2017 09:13 PM