Planet MySQL

Releasing ProxySQL 1.3.4

I am happy to announce the release of the latest stable release of ProxySQL 1.3.4 on 18 February 2017.

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license!
It can be downloaded here, and freely usable and accessible according to GPL license.

ProxySQL 1.3.4 is bug fixes release based on ProxySQL 1.3.3: no new features were introduced.

Release notes for ProxySQL 1.3.4 are available online

A special thanks to all the people that reports bugs: this makes each version of ProxySQL better than the previous one.
Please report any bugs or feature requests on github issue tracker

Releasing ProxySQL 1.3.4

I am happy to announce the release of the latest stable release of ProxySQL 1.3.4 on 18 February 2017.

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license!
It can be downloaded here, and freely usable and accessible according to GPL license.

ProxySQL 1.3.4 is bug fixes release based on ProxySQL 1.3.3: no new features were introduced.

Release notes for ProxySQL 1.3.4 are available online

A special thanks to all the people that reports bugs: this makes each version of ProxySQL better than the previous one.
Please report any bugs or feature requests on github issue tracker

MySQL Group Replication… synchronous or asynchronous replication ?

After some feedback we received from early adopters or discussions during events like FOSDEM, I realized that there is some misconception about the type of replication that MySQL Group Replication is using. And even experts can be confused as Vadim’s blog post illustrated it.

So, is MySQL Group Replication asynchronous or synchronous ?? … in fact it depends !

The short answer is that GR is asynchronous. The confusion here can be explained by the comparison with Galera that claims to be synchronous or virtually synchronous depending where and who claims it (Synchronous multi-master replication library, synchronous replicationscalable synchronous replication solutionenables applications requiring synchronous replication of data, …) . But GR and Galera are not more synchronous than the other.

The more detailed answer is that it depends what do you call “replication”. In fact for years, in the MySQL world, replication defined the process of writing (or changing or deleting) data to a master and the appearance of that data on the slave. The full process is what we called replication. The fact of writing data on a master, adding that change in the binary log, sending it on the relay log of a slave and the slave applying that change… So “replication” is in fact 5 different steps:

  1. locally applying
  2. generating a binlog event
  3. sending the binlog event to the slave(s)
  4. adding the binlog event on the relay log
  5. applying the binlog event from the relay log

And indeed, in MySQL Group Replication and in Galera (even if binlog and relay log files are mostly replace by the galera cache), only the step #3 is synchronous… and in fact this step is the streaming of the binlog event (write set) to the slave(s)… the replication of the data to the other nodes.

So yes the process of sending (replicating, streaming) the data to the other nodes is synchronous. But the applying of these changes is still completely asynchronous.

For example if you create a large transaction (which is not recommended neither in InnoDB, Galera and Group Replication) that modifies a huge amount of records, when the transaction is committed, a huge binlog event is created and streamed everywhere. As soon as the other nodes of the cluster/group acknowledge the reception of the binlog event, the node where the transaction was created returns “success” to the client and the data on that particular node is ready. Meanwhile all the other nodes need to process the huge binlog and make all the necessary data modification…. and this can take a lot of time. So yes, if you try to read the data that is part of that huge transaction on another node than the one where the write was done… the data won’t be there immediately. Bigger is the transaction longer you will have to wait for your data to be applied on the slave(s).

Let’s check with some pictures to try to make this more clear, considering the vertical axis is Time, :

We have a MySQL Group Replication cluster of 3 nodes and we start a transaction on node1we add some statements to our transaction…

we commit the transaction and  binary log events are generatedthose binlog events are streamed/delivered synchronously to the other nodes and as soon as everybody (*) ack the reception of the binlog events, each node starts certifying them as soon as they can… but independentlycertification can start as son as the transaction is receivedwhen certification is done, on the writer, there is no need to wait for anything else from the other nodes and the commit result is sent back to the clientevery other nodes consume from the apply queue the changes and start to apply them locally. This is again an asynchronous process like it was for certificationyou can see that the transaction is committed on every node at different time

If you perform a lot of large transactions and you want to avoid inconsistent reads, with MySQL Group Replication, you need to wait by yourself and check if there is still some transaction to apply in the queue or verify the last GTID executed to know if the data you modified is present or not where you try to read it. By default this is the same with Galera. However, Galera implemented sync_wait that force the client to wait (until a timeout) for all the transaction in the apply queue to be executed before the current one.

The only synchronous replication solution for the moment is still MySQL Cluster, aka NDB.

 

(*) on Group Replication majority is enough.

MySQL Bug 72804 Workaround: “BINLOG statement can no longer be used to apply query events”

In this blog post, we’ll look at a workaround for MySQL bug 72804.

Recently I worked on a ticket where a customer performed a point-in-time recovery PITR using a large set of binary logs. Normally we handle this by applying the last backup, then re-applying all binary logs created since the last backup. In the middle of the procedure, their new server crashed. We identified the binary log position and tried to restart the PITR from there. However, using the option

--start-position, the restore failed with the error “The BINLOG statement of type Table_map was not preceded by a format description BINLOG statement.” This is a known bug and is reported as MySQL Bug #72804: “BINLOG statement can no longer be used to apply Query events.”

I created a small test to demonstrate a workaround that we implemented (and worked).

First, I ran a large import process that created several binary logs. I used a small value in 

max_binlog_size and tested using the database “employees” (a standard database used for testing).Then I dropped the database.mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.33 sec) mysql> drop database employees; Query OK, 8 rows affected (1.25 sec)

To demonstrate the recovery process, I joined all the binary log files into one SQL file and started an import.

sveta@Thinkie:~/build/ps-5.7/mysql-test$ ../bin/mysqlbinlog var/mysqld.1/data/master.000001 var/mysqld.1/data/master.000002 var/mysqld.1/data/master.000003 var/mysqld.1/data/master.000004 var/mysqld.1/data/master.000005 > binlogs.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$ GENERATE_ERROR.sh binlogs.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysql < binlogs.sql ERROR 1064 (42000) at line 9020: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inserting error

I intentionally generated a syntax error in the resulting file with the help of the GENERATE_ERROR.sh script (which just inserts a bogus SQL statement in a random row). The error message clearly showed where the import stopped: line 9020. I then created a file that cropped out the part that had already been imported (lines 1- 9020), and tried to import this new file.

sveta@Thinkie:~/build/ps-5.7/mysql-test$ tail -n +9021 binlogs.sql >binlogs_rest.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysql < binlogs_rest.sql ERROR 1609 (HY000) at line 134: The BINLOG statement of type `Table_map` was not preceded by a format description BINLOG statement.

Again, the import failed with exactly the same error as the customer. The reason for this error is that the BINLOG statement – which applies changes from the binary log – expects that the format description event gets run in the same session as the binary log import, but before it. The format description existed initially at the start of the import that failed at line 9020. The later import (from line 9021 on) doesn’t contain this format statement.

Fortunately, this format is the same for the same version! We can simply take it from the beginning the SQL log file (or the original binary file) and put into the file created after the crash without lines 1-9020.

With MySQL versions 5.6 and 5.7, this event is located in the first 11 rows:

sveta@Thinkie:~/build/ps-5.7/mysql-test$ head -n 11 binlogs.sql | cat -n 1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 3 DELIMITER /*!*/; 4 # at 4 5 #170128 17:58:11 server id 1 end_log_pos 123 CRC32 0xccda074a Start: binlog v 4, server v 5.7.16-9-debug-log created 170128 17:58:11 at startup 6 ROLLBACK/*!*/; 7 BINLOG ' 8 g7GMWA8BAAAAdwAAAHsAAAAAAAQANS43LjE2LTktZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA 9 AAAAAAAAAAAAAAAAAACDsYxYEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA 10 AUoH2sw= 11 '/*!*/;

The first six rows are meta information, and rows 6-11 are the format event itself. The only thing we need to export into our resulting file is these 11 lines:

sveta@Thinkie:~/build/ps-5.7/mysql-test$ head -n 11 binlogs.sql > binlogs_rest_with_format.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$ cat binlogs_rest.sql >> binlogs_rest_with_format.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysql < binlogs_rest_with_format.sql sveta@Thinkie:~/build/ps-5.7/mysql-test$

After this, the import succeeded!

Sysadmin 101: Troubleshooting

I typically keep this blog strictly technical, keeping observations, opinions and the like to a minimum. But this, and the next few posts will be about basics and fundamentals for starting out in system administration/SRE/system engineer/sysops/devops-ops (whatever you want to call yourself) roles more generally.
Bear with me!

“My web site is slow”

I just picked the type of issue for this article at random, this can be applied to pretty much any sysadmin related troubleshooting. It’s not about showing off the cleverest oneliners to find the most information. It’s also not an exhaustive, step-by-step “flowchart” with the word “profit” in the last box. It’s about general approach, by means of a few examples.
The example scenarios are solely for illustrative purposes. They sometimes have a basis in assumptions that doesn’t apply to all cases all of the time, and I’m positive many readers will go “oh, but I think you will find…” at some point.
But that would be missing the point.

Having worked in support, or within a support organization for over a decade, there is one thing that strikes me time and time again and that made me write this;
The instinctive reaction many techs have when facing a problem, is to start throwing potential solutions at it.

“My website is slow”

  • I’m going to try upping MaxClients/MaxRequestWorkers/worker_connections
  • I’m going to try to increase innodb_buffer_pool_size/effective_cache_size
  • I’m going to try to enable mod_gzip (true story, sadly)

“I saw this issue once, and then it was because X. So I’m going to try to fix X again, it might work”.

This wastes a lot of time, and leads you down a wild goose chase. In the dark. Wearing greased mittens.
InnoDB’s buffer pool may well be at 100% utilization, but that’s just because there are remnants of a large one-off report someone ran a while back in there. If there are no evictions, you’ve just wasted time.

Quick side-bar before we start

At this point, I should mention that while it’s equally applicable to many roles, I’m writing this from a general support system adminstrator’s point of view. In a mature, in-house organization or when working with larger, fully managed or “enterprise” customers, you’ll typically have everything instrumented, measured, graphed, thresheld (not even word) and alerted on. Then your approach will often be rather different. We’re going in blind here.

If you don’t have that sort of thing at your disposal;

Clarify and First look

Establish what the issue actually is. “Slow” can take many forms. Is it time to first byte? That’s a whole different class of problem from poor Javascript loading and pulling down 15 MB of static assets on each page load. Is it slow, or just slower than it usually is? Two very different plans of attack!

Make sure you know what the issue reported/experienced actually is before you go off and do something. Finding the source of the problem is often difficult enough, without also having to find the problem itself.
That is the sysadmin equivalent of bringing a knife to a gunfight.

Low hanging fruit / gimmies

You are allowed to look for a few usual suspects when you first log in to a suspect server. In fact, you should! I tend to fire off a smattering of commands whenever I log in to a server to just very quickly check a few things; Are we swapping (free/vmstat), are the disks busy (top/iostat/iotop), are we dropping packets (netstat/proc/net/dev), is there an undue amount of connections in an undue state (netstat), is something hogging the CPUs (top), is someone else on this server (w/who), any eye-catching messages in syslog and dmesg?

There’s little point to carrying on if you have 2000 messages from your RAID controller about how unhappy it is with its write-through cache.

This doesn’t have to take more than half a minute. If nothing catches your eye – continue.

Reproduce

If there indeed is a problem somewhere, and there’s no low hanging fruit to be found;

Take all steps you can to try and reproduce the problem. When you can reproduce, you can observe. When you can observe, you can solve. Ask the person reporting the issue what exact steps to take to reproduce the issue if it isn’t already obvious or covered by the first section.

Now, for issues caused by solar flares and clients running exclusively on OS/2, it’s not always feasible to reproduce. But your first port of call should be to at least try! In the very beginning, all you know is “X thinks their website is slow”. For all you know at that point, they could be tethered to their GPRS mobile phone and applying Windows updates. Delving any deeper than we already have at that point is, again, a waste of time.

Attempt to reproduce!

Check the log!

It saddens me that I felt the need to include this. But I’ve seen escalations that ended mere minutes after someone ran tail /var/log/.. Most *NIX tools these days are pretty good at logging. Anything blatantly wrong will manifest itself quite prominently in most application logs. Check it.

Narrow down

If there are no obvious issues, but you can reproduce the reported problem, great. So, you know the website is slow. Now you’ve narrowed things down to: Browser rendering/bug, application code, DNS infrastructure, router, firewall, NICs (all eight+ involved), ethernet cables, load balancer, database, caching layer, session storage, web server software, application server, RAM, CPU, RAID card, disks.
Add a smattering of other potential culprits depending on the set-up. It could be the SAN, too. And don’t forget about the hardware WAF! And.. you get my point.

If the issue is time-to-first-byte you’ll of course start applying known fixes to the webserver, that’s the one responding slowly and what you know the most about, right? Wrong!
You go back to trying to reproduce the issue. Only this time, you try to eliminate as many potential sources of issues as possible.

You can eliminate the vast majority of potential culprits very easily: Can you reproduce the issue locally from the server(s)? Congratulations, you’ve just saved yourself having to try your fixes for BGP routing.
If you can’t, try from another machine on the same network. If you can - at least you can move the firewall down your list of suspects, (but do keep a suspicious eye on that switch!)

Are all connections slow? Just because the server is a web server, doesn’t mean you shouldn’t try to reproduce with another type of service. netcat is very useful in these scenarios (but chances are your SSH connection would have been lagging this whole time, as a clue)! If that’s also slow, you at least know you’ve most likely got a networking problem and can disregard the entire web stack and all its components. Start from the top again with this knowledge (do not collect $200). Work your way from the inside-out!

Even if you can reproduce locally - there’s still a whole lot of “stuff” left. Let’s remove a few more variables. Can you reproduce it with a flat-file? If i_am_a_1kb_file.html is slow, you know it’s not your DB, caching layer or anything beyond the OS and the webserver itself.
Can you reproduce with an interpreted/executed hello_world.(py|php|js|rb..) file? If you can, you’ve narrowed things down considerably, and you can focus on just a handful of things. If hello_world is served instantly, you’ve still learned a lot! You know there aren’t any blatant resource constraints, any full queues or stuck IPC calls anywhere. So it’s something the application is doing or something it’s communicating with.

Are all pages slow? Or just the ones loading the “Live scores feed” from a third party?

What this boils down to is; What’s the smallest amount of “stuff” that you can involve, and still reproduce the issue?

Our example is a slow web site, but this is equally applicable to almost any issue. Mail delivery? Can you deliver locally? To yourself? To <common provider here>? Test with small, plaintext messages. Work your way up to the 2MB campaign blast. STARTTLS and no STARTTLS. Work your way from the inside-out.

Each one of these steps takes mere seconds each, far quicker than implementing most “potential” fixes.

Observe / isolate

By now, you may already have stumbled across the problem by virtue of being unable to reproduce when you removed a particular component.

But if you haven’t, or you still don’t know why; Once you’ve found a way to reproduce the issue with the smallest amount of “stuff” (technical term) between you and the issue, it’s time to start isolating and observing.

Bear in mind that many services can be ran in the foreground, and/or have debugging enabled. For certain classes of issues, it is often hugely helpful to do this.

Here’s also where your traditional armory comes into play. strace, lsof, netstat, GDB, iotop, valgrind, language profilers (cProfile, xdebug, ruby-prof…). Those types of tools.

Once you’ve come this far, you rarely end up having to break out profilers or debugers though.

strace is often a very good place to start.
You might notice that the application is stuck on a particular read() call on a socket file descriptor connected to port 3306 somewhere. You’ll know what to do.
Move on to MySQL and start from the top again. Low hanging fruit: “Waiting_for * lock”, deadlocks, max_connections.. Move on to: All queries? Only writes? Only certain tables? Only certain storage engines?…

You might notice that there’s a connect() to an external API resource that takes five seconds to complete, or even times out. You’ll know what to do.

You might notice that there are 1000 calls to fstat() and open() on the same couple of files as part of a circular dependency somewhere. You’ll know what to do.

It might not be any of those particular things, but I promise you, you’ll notice something.

If you’re only going to take one thing from this section, let it be; learn to use strace! Really learn it, read the whole man page. Don’t even skip the HISTORY section. man each syscall you don’t already know what it does. 98% of troubleshooting sessions ends with strace.

Percona Blog Poll Results: What Programming Languages Are You Using for Backend Development?

In this blog we’ll look at the results from Percona’s blog poll on what programming languages you’re using for backend development.

Late last year we started a poll on what backend programming languages are being used by the open source community. The three components of the backend – server, application, and database – are what makes a website or application work. Below are the results of Percona’s poll on backend programming languages in use by the community:

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

One of the best-known and earliest web service stacks is the LAMP stack, which spelled out refers to Linux, Apache, MySQL and PHP/Perl/Python. We can see that this early model is still popular when it comes to the backend.

PHP still remains a very common choice for a backend programming language, with Python moving up the list as well. Perl seems to be fading in popularity, despite being used a lot in the MySQL world.

Java is also showing signs of strength, demonstrating the strides MySQL is making in enterprise applications. We can also see JavaScript is increasingly getting used not only as a front-end programming language, but also as back-end language with the Node.JS framework.

Finally, Go is a language to look out for. Go is an open source programming language created by Google. It first appeared in 2009, and is already more popular than Perl or Ruby according to this poll.

Thanks to the community for participating in our poll. You can take our latest poll on what database engine are you using to store time series data here. 

Making MaxScale Dynamic: New in MariaDB MaxScale 2.1

Making MaxScale Dynamic: New in MariaDB MaxScale 2.1 markusmakela Thu, 02/16/2017 - 12:43


The beta version of the upcoming MaxScale 2.1 has been released. This new release takes MaxScale to the next level by introducing functionality that makes it possible to configure parts of MaxScale’s configuration at runtime.

How Has MaxScale Changed?

One of the big new features in 2.1 is the ability to add, remove and alter the server definitions at runtime. By decoupling the servers from the services that use them by making the server list dynamic, MaxScale can be defined as a more abstract service instead of a concrete service comprised of a pre-defined set of servers. This opens up new use cases for MaxScale, such as template configurations, that make the use and maintenance of MaxScale a lot easier.


The logical extension to being able to add new servers is the ability to add new monitors. After all, the monitors are just an abstraction of a cluster of servers. What this gives us is the capability to radically change the cluster layout by bringing in a whole new cluster of servers. With this, it is possible to do a runtime migration from one cluster to another by simply adding a new monitor, then adding the servers that it monitors and finally swapping the old cluster to the new one.


While services are a somewhat of a more static concept, the network ports that they listen on aren’t. For this reason, we added the ability to add new listeners for services. What this means is that you can extend the set of ports that a service listens on at runtime. One example of how this could be used is to connect a new appliance which uses a different port to MaxScale. This removes some of the burden on the developer of the appliance and offloads it to MaxScale.


How to Use It?


A practical use case for the new features in 2.1 can be demonstrated by one of the tests that are in our testing framework. It starts MaxScale with a minimal configuration similar to the following.

[maxscale] threads=4 [rwsplit-service] type=service router=readwritesplit user=maxskysql passwd=skysql [CLI] type=service router=cli [CLI Listener] type=listener service=CLI protocol=maxscaled socket=default


Next it defines a monitor.

maxadmin create monitor cluster-monitor mysqlmon maxadmin alter monitor cluster-monitor user=maxuser password=maxpwd monitor_interval=1000 maxadmin restart monitor cluster-monitor


Since the monitors require some configuration before they can be used, they are created in a stopped state. This allows the user to configure the credentials that the monitor will use when it connects to the servers in the cluster. Once those are set, the monitor is started and then the test proceeds to create a listener for the rwsplit-service on port 4006.

maxadmin create listener rwsplit-service rwsplit-listener 0.0.0.0 4006

The listeners require no extra configuration and are started immediately. The next step is the definition of the two servers that the service will use.

maxadmin create server db-serv-west 172.0.10.2 3306 maxadmin create server db-serv-east 172.0.10.4 3306

Once the servers are defined, they need to be linked to the monitor so that it will start monitoring the status of the servers and also to the service that will use them.

maxadmin add server db-serv-west cluster-monitor rwsplit-service maxadmin add server db-serv-east cluster-monitor rwsplit-service

After the servers have been added to the service and the monitor, the system is ready for use. At this point, our test performs various health checks and then proceeds to scale the service down.

Dynamic Scaling

When the demand on the database cluster lowers, servers can be shut down as they are no longer needed. When demand grows, new servers can be started to handle the increased load. Combined with on-demand, cloud-based database infrastructure, MaxScale can handle changing traffic loads.

Scaling up on demand is simply done by creating new servers and adding them to the monitors and services that use them. Once the demand for the service lowers, the servers can be scaled down by removing them from the service.

All of the changes done at runtime are persisted in a way that will survive a restart of MaxScale. This makes it a reliable way to adapt MaxScale to its environment.

Summary

The new features in 2.1 make MaxScale a dynamic part of the database infrastructure. The support of live changes in clusters makes MaxScale the ideal companion for your application whether you’re using an on-premise, cloud-based or a hybrid database setup.
The changes to the configuration in MaxScale 2.1 are backwards compatible with earlier versions of MaxScale which makes taking it into use is easy. Going forward, our goal is to make MaxScale easier to use for the DBAs by providing new ways to manage MaxScale while at the same time taking the modular ideology of MaxScale to the limits by introducing new and interesting modules.

There's More


The 2.1 release of MaxScale is packed with new features designed to supercharge your cluster. One example is the newly added concept of module commands that allow the modules in MaxScale to expand their capabilities beyond that of the module type. Remember to read the MariaDB blog as this new functionality will be explored in a follow-up blog post that focuses on the new module command capabilities and takes a practical look at the dbfwfilter firewall filter module and how it implements these.

The beta version of the upcoming MaxScale 2.1 has been released. This new release takes MaxScale to the next level by introducing functionality that makes it possible to configure parts of MaxScale’s configuration at runtime.

Login or Register to post comments

Improving the Performance of MariaDB MaxScale

Improving the Performance of MariaDB MaxScale Johan Thu, 02/16/2017 - 12:34

Performance has been a central goal of MariaDB MaxScale’s architecture from the start. Core elements in the drive for performance is the use of asynchronous I/O and Linux’ epoll. With a fixed set of worker threads, whose amount is selected to correspond to the number of available cores, each thread should either be working or be idle, but never be waiting for I/O. This should provide good performance that scales well with the number of available CPUs. However, benchmarking revealed that was not entirely the case. The performance of MaxScale did not continuously increase as more worker threads were added, but after a certain point the performance would actually start to decrease.

When we started working on MariaDB MaxScale 2.1 we decided to investigate what the actual cause for that behaviour was and to make modifications in order to improve the situation. As will be shown below, the modifications that were made have indeed produced good results.

Benchmark

The results below were produced with sysbench OLTP in read-only mode. The variant used performs 1000 point selects per transaction and the focus is on measuring the throughput that the proxy can deliver.

In the benchmark two computers were used, both of which have 16 cores / 32 hyperthreads, 128GB RAM and an SSD drive. The computers are connected with a GBE LAN. On one of the computers there were 4 MariaDB-10.1.13 backends and on the other MariaDB MaxScale and sysbench.

MariaDB MaxScale 2.0

The following figure shows the result for MariaDB MaxScale 2.0. As the basic architecture of MaxScale has been the same from 1.0, the result is applicable to all versions up to 2.0.

In the figure above, on the vertical axis there is the number of queries performed by second and on the horizontal axis there is the number of threads used by sysbench. That number corresponds to the number of clients MariaDB MaxScale will see. In each cluster, the blue bar shows the result when the backends are connected to directly, and the red, yellow and green bars when the connection goes via MaxScale running with 4, 8 and 16 threads respectively.

As long as the number of clients is small, the performance of MariaDB MaxScale is roughly the same irrespective of the number of worker threads. However, already at 32 clients it can be seen that 16 worker threads perform worse than 8 and at 64 clients that is evident. The overall performance of MaxScale is clearly below that of a direct connection.

MariaDB MaxScale 2.1

For MariaDB MaxScale 2.1 we did some rather significant changes regarding how the worker threads are used. Up until version 2.0 all threads were used in a completely symmetric way with respect to all connections, both from client to MaxScale and from MaxScale to the backends, which implied a fair amount of locking inside MaxScale. In version 2.1 a session and all its related connections are pinned to a particular worker thread. That means that there is a need for significantly less locking and that the possibility for races has been reduced.

The following figure shows the result of the same benchmark when run using MariaDB MaxScale 2.1.

Compared with the result of MaxScale 2.0, the performance follows and slightly exceeds the baseline of a direct connection. At 64 clients, 16 worker threads still provide the best performance but at 128 clients 8 worker threads overtake. However, as these figures were obtained with a version very close to the 2.1.0 Beta we are fairly confident that by the time 2.1 GA is released we will be able to address that as well. Based on this benchmark, the performance of 2.1 is roughly 45% better than that of 2.0.

MariaDB MaxScale and ProxySQL

ProxySQL is a MySQL proxy that in some respects is similar to MariaDB MaxScale. Out of curiosity we ran the same benchmark with ProxySQL 1.3.0, using exactly the same environment as when MaxScale was benchmarked. In the following we will show in different figures how MaxScale 2.0, MaxScale 2.1 and ProxySQL 1.3.0 compares, when 4, 8 and 16 threads are used.

With 4 threads, ProxySQL 1.3.0 performs slightly better than MaxScale 2.0 and MaxScale 2.1 performs significantly better than both. The performance of both MaxScale 2.1 and ProxySQL 1.3.0 start to drop at 256 client connections.

With 8 threads, up until 128 client connections the order remains the same, with MaxScale 2.1 at the top. When the number of client connections grows larger than that, MaxScale 2.1 and ProxySQL 1.3.0 are roughly on par.

With 16 threads, up until 64 client connections the order is still the same. When the number of client connections grows larger than that, MaxScale 2.1 and ProxySQL 1.3.0 are roughly on par. The performance of both drop slightly compared with the 8 thread situation.

Too far reaching conclusions should not be drawn from these results alone; it was just one benchmark, MaxScale 2.1 will still evolve before it is released as GA and the version of ProxySQL was not the very latest one.

Summary

In MariaDB MaxScale 2.1 we have made significant changes to the internal architecture and benchmarking shows that the changes were beneficial for the overall performance. MaxScale 2.1 consistently performs better than MaxScale 2.0 and with 8 threads, which in this benchmark is the sweetspot for MaxScale 2.0, the performance has improved by 45%.

If you want to give MaxScale 2.1.0 Beta a go, it can be downloaded at https://downloads.mariadb.com/MaxScale/.

Performance has been a central goal of MariaDB MaxScale’s architecture from the start. Core elements in the drive for performance is the use of asynchronous I/O and Linux’ epoll. With a fixed set of worker threads, whose amount is selected to correspond to the number of available cores, each thread should either be working or be idle, but never be waiting for I/O. This should provide good performance that scales well with the number of available CPUs. However, benchmarking revealed that was not entirely the case. The performance of MaxScale did not continuously increase as more worker threads were added, but after a certain point the performance would actually start to decrease.

When we started working on MariaDB MaxScale 2.1 we decided to investigate what the actual cause for that behaviour was and to make modifications in order to improve the situation.

Login or Register to post comments

Introducing MariaDB MaxScale 2.1.0 Beta

Introducing MariaDB MaxScale 2.1.0 Beta Dipti Joshi Thu, 02/16/2017 - 12:32

We are happy to announce that  MariaDB MaxScale 2.1.0 beta is released today. MariaDB MaxScale is the next generation database proxy for MariaDB.  Beta is an important time in our release and we encourage you to download this release today!

MariaDB MaxScale 2.1 introduces following key new capabilities:

Dynamic Configuration

  • Server, Monitor and Listeners: MaxScale 2.1 supports dynamic configuration of servers, monitors and listeners. Servers, monitors and listeners 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

  • 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.

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

  • Selective Data Masking: Meet your HIPPA 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.

  • 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 database firewall filter adds a rule to whitelist or blacklist a query based on presence of a function.

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 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 statement to the master server where data is guaranteed to be in a up-to-date state

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

  • 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
     

Links: 

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

We are happy to announce that  MariaDB MaxScale 2.1.0 beta is released today. MariaDB MaxScale is the next generation database proxy for MariaDB.  Beta is an important time in our release and we encourage you to download this release today!

Login or Register to post comments

Fun with Bugs #48 - Group Replication Bugs and Missing Features as of MySQL 5.7.17

It seems recent post on Group Replication by Vadim caused an interesting discussion on Facebook. I am NOT going to continue it here, but decided to present some facts, specifically, list of public bug reports and feature requests for Group Replication (mostly "Verified", that is, accepted by Oracle engineers as valid) as of MySQL 5.7.17 (where the feature is promoted as GA), with just few comments to some of the bugs.

The goal is to double check this post when next Oracle MySQL 5.7.x release appears, to find out how much Oracle carews to fix the problems already identified by MySQL Community.

So, here are the bugs and feature requests in MySQL Server: Group Replication category (I've included only those reported for 5.7.17 into this post), as of today:
  • Bug #84315 - "add mysql group replication bootstrap option to init script". Fair enough, we have a way to boottrap a cluster for a node in all implementations of Galera cluster. This feature request is still "Open".
  • Bug #84329 - "Some Class B private address is not permitted automatically". Manual seems to say it should be.
  • Bug #84337 - "Configure mgr with out root user encounter an error". Seems to be a duplicate of Bug #82687, that is, known for quite a some time before GA.
  • Bug #84367 - "START GROUP_REPLICATION should be possible without error". You get error if it was already started. Not a big deal, but some optional clause like IF NOT STARTED may help (in a similar way to DROP TABLE ... IF EXISTS) to code scripts. Still "Open".
  • Bug #84710 - "group replication does not respect localhost IP". may be a problem for some MySQL Sandbox setups in a hope to test group replication.
  • Bug #84727 - "GR: Partitioned Node Should Get Updated Status and not accept writes". Writes on partitioned node are accepted and hang (forever?). As a side note, I think Kenny Gryp deserves a special recognition as an early adopter of Group Replication feature who cared to report many problems noted in the process.
  • Bug #84728 - "Not Possible To Avoid MySQL From Starting When GR Cannot Start". We need this fixed to avoid split brain situations by default.
  • Bug #84729 - "Group Replication: Block Reads On Partitioned Nodes". In Galera reads are blocked by default when node is not considered a member of cluster.
  • Bug #84730 - "Feature: Troubleshoot Group Replication Transaction Rollbacks". You can get a lot of information about conflicts in case of Galera. Sorry that I have to compare, but when discussing the ways of dealing with common problems in cluster environments related to MySQL one can not ignore existing solutions (NDB clusters and Galera clusters), so I just picked up a (somewhat similar) technology that I know and used (a bit). I think readers will do the same, try to base their conclusions on known examples.
  • Bug #84731 - "Group Replication: mysql client connections hang during group replication start". There is no reason to hang more than needed. We should just make sure reads and writes are NOT accepted until the node is a member of cluster and in sync.
  • Bug #84733 - "Cannot Start GR with super_read_only=1". But this setting may be needed to make sure that there is only one master node in cluster, no matter what happens ot them...
  • Bug #84773 - "Flow control slows down throughput while a node join the cluster". Let me quote: "This is done this way by design, that is, request group to slow down while the member is fetching and applying data.
  • Bug #84774 - "Performance drop every 60 seconds". Now this sounds like a performance problem to work on, maybe by adding some tuning options.
  • Bug #84784 - "Group Replication nodes do not rejoin cluster after network connectivity issues". It would be really nice for nodes to try to re-join the cluster in case of short term connectivity issues. Galera nodes do not give up that fast. The bug is still not verified.
  • Bug #84785  - "Prevent Large Transactions in Group Replication". Galera somehow allows to limit transaction size. Not that there were no related bugs, but still options exist.
  • Bug #84792 - "Idle GR Cluster: Member CPU hog". Not yet verified, but it seems in some cases node can use a notable share of CPU time for no clear/good reason.
  • Bug #84794 - "Cannot kill query inside GR". Weel, you can do STOP GROUP_REPLICATION, but then it can be dangerous, because...
  • Bug #84795 - "STOP GROUP_REPLICATION sets super_read_only=off" - the node with stopped replication may allow to change the data...
  • Bug #84798 - "Group Replication can use some verbosity in the error log". Galera cluster nodes are too verbose, one gets kilometers of log records about everything, anything and nothing. Still, better to skip some usual outputs in the logs than get no evidence at all on what was going on...
  • Bug #84900 - "Getting inconsistent result on different nodes". Now, this is really unfortunate (for a "cluster") and somewhat similar problem was reported by Vadim before, see Bug #82481 and was supposed to be fixed. Anyway, the inconsistency is repatable and looks somewhat scary.
  • Bug #84901 - "Can't execute transaction on second node". Best practice is to write on one and only one node (see Bug #83218). An attempt to write on the other node may fail...
Now, make your own conclusions about the maturity of Group Replication in MySQL 5.7.17. I manage to avoid it so far, as I try to keep myself as far from any kinds of "clusters" as possible... Had not worked well with Galera, unfortunately - I have to read its verbose logs on a daily basis.

Video Interview with ProxySQL Creator René Cannaò

In anticipation of this month’s webinar MySQL & MariaDB Load Balancing with ProxySQL & ClusterControl that will happen on February 28th Severalnines sat down with the creator of ProxySQL founder and creator René Cannaò to discuss his revolutionary product, how it’s used, and what he plans to cover in the webinar. Watch the video or read the transcript below of the interview.

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE Transcript of Interview

Hi I’m Forrest and I’m from the Severalnines marketing team and I’m here interviewing René Cannaò from ProxySQL. René thanks for joining me. Let’s start by introducing yourself, where did you come from?

Thank you, Forrest. Without going into too many details, I came from a system administrator background, and as a system administrator I got fascinated by databases, so I then become a DBA. In my past experience I worked as a Support Engineer for MySQL/Sun/Oracle, where I got of experience about MySQL... then remote DBA for PalominoDB and after that working as a MySQL SRE for Dropbox and I founded of ProxySQL.

So What is ProxySQL?

ProxySQL is a lightweight yet complex protocol aware proxy that sits between the MySQL clients and servers. I like to describe it as a gate, in fact the Stargate is the logo, so basically it separates clients from databases, therefore an entry point to access all the databases server.

Why did you create ProxySQL?

That’s a very interesting question, and very important. As a DBA, it was always extremely difficult to control the traffic sent to the database. This was the main reason to create ProxySQL: basically it’s a layer that separates the database from the application (de facto splitting them into two different layers), and because it’s sitting in the middle it’s able to control and manage all the traffic between the two, and also transparently managing failures.

So there are several database load balancers in the market, what differentiates ProxySQL from others?

First, most of the load balancers do not understand MySQL protocol: ProxySQL understands it, and this allows the implementation of features otherwise impossible to implement. Among the few proxies that are able to understand the MySQL protocol, ProxySQL is the only one designed from DBAs for DBAs, therefore it is designed to solve real issues and challenges as a DBA. For example, ProxySQL is the only proxy supporting connections multiplexing and query caching.

Related resources  Using ClusterControl to Deploy and Configure ProxySQL on top of MySQL Replication  Tips and Tricks - How to shard MySQL with ProxySQL in ClusterControl  How to set up read-write split in Galera Cluster using ProxySQL

I noticed on your website that you say that ProxySQL isn’t battle-tested, its WAR-tested. What have you done to put ProxySQL through its paces?

The point is that from the very beginning ProxySQL was architected and designed to behave correctly in extremely demanding and very complex setups with millions of clients connections and thousands of database servers. Other proxies won’t be able to handle this. So, a lot of effort was invested in making sure ProxySQL is resilient in such complex setups. And, of course, no matter how resient it is set up it should not sacrifice performance.

On the 28th of February you will be co-hosting a webinar with Severalnines; with Krzysztof one or our Support Engineers. What are some of the topics you are going to cover at that event?

ProxySQL is built upon new technology data not present in other load balancers, its features and concepts are not always intuitive. Some concepts are extremely original in ProxySQL. For this reason the topics I plan to cover at the event are hostgroups, query rules, connection multiplexing, failures handling, and configuration management. Again, those are all the features and concepts that are only present in ProxySQL.

Excellent, well thank you for joining me, I’m really looking forward to this webinar on the 28th.

Thank you, Forrest.

Tags: proxysqlload balancerMySQLclustercontrolvideo

FOSDEM 2017 is over… this was again a great MySQL event !

FOSDEM 2017 is over, I brought back home the flu…. but hopefully not only !

Indeed this 2017 edition was very rewarding. We started our FOSDEM with a “fringe” : pre-FOSDEM MySQL Day where we highlighted MySQL 8.0 new features and hosted some talks from MySQL friends.

This first edition of the pre-FOSDEM MySQL Day was a great success. We had up to 70 attendees! I would like to thanks all the speakers: Morgan Tocker, Bernt Marius Johnsen, Øystein Grøvlen, Kenny Gryp, Jean-François Gagné, Dag H. Wanvik, Sveta Smirnova, Alkin Tezuysal, Norvald H. Ryeng, Mark Leith and René Cannaó.

I also want to thank Dim0, Flyer and Kenny for their precious help organizing the room.

These are the slides of the sessions presented during the MySQL Day:

MySQL Server Defaults from Morgan Tocker

MySQL 8.0 & Unicode: Why, what & how from Bernt Marius Johnsen

MySQL 8.0: Common Table Expressions from oysteing

SQL window functions for MySQL from Dag H. Wanvik

Using Optimizer Hints to Improve MySQL Query Performance from oysteing

MySQL 8.0: GIS — Are you ready? from Norvald Ryeng

How Booking.com avoids and deals with replication lag from Jean-François Gagné

MySQL Group Replication from Kenny Gryp

Proxysql use case scenarios fosdem17 from Alkin Tezuysal

What you wanted to know about MySQL, but could not find using inernal instrumentation only from Sveta Smirnova

Then on Saturday, we had the MySQL & Friends Devroom at FOSDEM and to be honest, I never saw the room so full for the entire day, even the last session that was at 18.30 was still busy with people standing in the back of the room and in the stairs on the side !


All sessions were streamed live and recorded. You can find the videos on http://ftp.osuosl.org/pub/fosdem/2017/H.1309/

We ended this amazing day with the famous MySQL & Friends Community Dinner where once again the hosts of the day made a great job ! Thank you Melinda, Dim0, Kenny and Flyer.

Thank you also to the sponsors !

 

During the FOSDEM week-end, the MySQL Team was also present at our stand to answer questions and presenting our new features like MySQL 8.0, Group Replication and InnoDB Cluster.

Our Mark Leith has been interviewed by Ken Fallon for Hacker Public Radio and can be listened on https://video.fosdem.org/2017/stands/H.7_MySQL.flac

During the week-end, our MySQL Group Replication engineers were solicited a lot by all community members and it seems many people are evaluating it already ! Thank you for all the feedback we got ! ProxySQL was also a very hot topic, good job René !

This FOSDEM 2017 edition was great, I am already looking forward the next edition even if it will be hard to do better ! See you next year !!

Group Replication: Shipped Too Early

This blog post is my overview of Group Replication technology.

With Oracle clearly entering the “open source high availability solutions” arena with the release of their brand new Group Replication solution, I believe it is time to review the quality of the first GA (production ready) release.

TL;DR: Having examined the technology, it is my conclusion that Oracle seems to have released the GA version of Group Replication too early. While the product is definitely “working prototype” quality, the release seems rushed and unfinished. I found a significant number of issues, and I would personally not recommend it for production use.

It is obvious that Oracle is trying hard to ship technology to compete with Percona XtraDB Cluster, which is probably why they rushed to claim Group Replication GA quality.

If you’re all set to follow along and test Group Replication yourself, simplify the initial setup by using this Docker image. We can review some of the issues you might face together.

For the record, I tested the version based on MySQL 5.7.17 release.

No automatic provisioning

First off, the first thing you’ll find is there is NO way to automatically setup of a new node.

If you need to setup new node or recover an existing node from a fatal failure, you’ll need to manually provision the slave.

Of course, you can clone a slave using Percona XtraBackup or LVM by employing some self-developed scripts. But given the high availability nature of the product, one would expect Group Replication to automatically re-provision any failed node.

Bug: stale reads on nodes

Please see this bug:

One line summary: while any secondary nodes are “catching up” to whatever happened on a first node (it takes time to apply changes on secondary nodes), reads on a secondary node could return stale data (as shown in the bug report).

This behavior brings us back to the traditional asynchronous replication slave behavior (i.e., Group Replication’s predecessor).

It also contradicts the Group Replication documentation, which states: “There is a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time.” (See https://dev.mysql.com/doc/refman/5.7/en/group-replication.html.)

I might also mention here that Percona XtraDB Cluster prevents stale reads (see https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-system-index.html#wsrep_sync_wait).

Bug: nodes become unusable after a big transaction, refusing to execute further transactions

There are two related bugs:

One line summary: after running a big transaction, any secondary nodes become unusable and refuse to perform any further transactions.

Obscure error messages

It is not uncommon to see cryptic error messages while testing Group Replication. For example:

mysql> commit; ERROR 3100 (HY000): Error on observer while running replication hook 'before_commit'.

This is fairly useless and provides little help until I check the mysqld error log. The log provides a little bit more information:

2017-02-09T02:05:36.996776Z 18 [ERROR] Plugin group_replication reported: '[GCS] Gcs_packet's payload is too big. Only the packets smaller than 2113929216 bytes can be compressed.'

Discussion:

The items highlighted above might not seem too bad at first, and you could assume that your workload won’t be affected. However, stale reads and node dysfunctions basically prevent me from running a more comprehensive evaluation.

My recommendation:

If you care about your data, then I recommend not using Group Replication in production. Currently, it looks like it might cause plenty of headaches, and it is easy to get inconsistent results.

For the moment, Group Replication appears an advanced – but broken – traditional MySQL asynchronous replication.

I understand Oracle’s dilemma. Usually people are hesitant to test a product that is not GA. So in order to get feedback from users, Oracle needs to push the product to GA. Oracle must absolutely solve the issues above during future QA cycles.

Kerberos for SQLyog by MariaDB Connector/C

Kerberos for SQLyog by MariaDB Connector/C julienfritsch Wed, 02/15/2017 - 15:06

MariaDB is an open source enterprise database with one of the most active and fastest-growing communities in the world. MariaDB Enterprise delivers the security, high availability and scalability required for mission-critical applications, and the management and monitoring tools today’s enterprises rely on.

SQLyog is included in MariaDB Enterprise and it helps DBAs, developers and database architects save time writing queries visualized with syntax checking, designing visually complex queries, and many other powerful features for visualization, synchronization and management. This 12.4 release introduces ‘read-only’ connections as well as support for the MariaDB auth_gssapi (Kerberos) plugin.

Kerberos is an authentication protocol that works on the basis of 'tickets' to allow nodes communicating over a non-secure network to prove their identity to one another in a secure manner. Typically Kerberos is used within corporate/internal environments for two purposes: security and authentication – replacing the computer local passwords with tickets on a distributed network system, thereby eliminating the risk for transmitted passwords over the network to be intercepted.

The MariaDB auth_gssapi (Kerberos) plugin was available with MariaDB Connector/C 3.0 (currently beta), but based on the high demand for this, we backported it to the MariaDB Connector/C 2.3 which is used in SQLyog.

More information on Connector/C here.

Download SQLyog 12.4 from our Customer Portal here.

Want to learn more, please contact us here.

This blog post was inspired by this page.

SQLyog is included in MariaDB Enterprise and it helps DBAs, developers and database architects save time writing queries visualized with syntax checking, designing visually complex queries, and many other powerful features for visualization, synchronization and management. This 12.4 release introduces ‘read-only’ connections as well as support for the MariaDB auth_gssapi (Kerberos) plugin.

Login or Register to post comments

Docker Images for Percona Server for MySQL Group Replication

In this blog post, we’ll point to a new Docker image for Percona Server for MySQL Group Replication.

Our most recent release of Percona Server for MySQ (Percona Server for MySQL 5.7.17) comes with Group Replication plugins. Unfortunately, since this technology is very new, it requires some fairly complicated steps to setup and get running. To help with that process, I’ve prepare Docker images that simplify its setup procedures.

You can find the image here: https://hub.docker.com/r/perconalab/pgr-57/.

To start the first node (bootstrap the group):

docker run -d -p 3306 --net=clusternet -e MYSQL_ROOT_PASSWORD=passw0rd -e CLUSTER_NAME=cluster1 perconalab/pgr-57

To add nodes into the group after:

docker run -d -p 3306 --net=clusternet -e MYSQL_ROOT_PASSWORD=passw0rd -e CLUSTER_NAME=cluster1 -e CLUSTER_JOIN=CONTAINER_ID_FROM_THE_FIRST_STEP perconalab/pgr-57

You can also get a full script that starts “N” number of nodes, here: https://github.com/Percona-Lab/percona-docker/blob/master/pgr-57/start_node.sh

 

MySQL on Docker: Composing the Stack

Docker 1.13 introduces a long-awaited feature called compose-file support, which allow us to define our containers with a nice simple config file instead of a single long command. If you have a look at our previous “MySQL on Docker” blog posts, we used multiple long command lines to run containers and services. By using compose-file, containers are easily specified for deployment. This reduces the risk for human error as you do not have to remember long commands with multiple parameters.

In this blog post, we’ll show you how to use compose-file by using simple examples around MySQL deployments. We assume you have Docker Engine 1.13 installed on 3 physical hosts and Swarm mode is configured on all hosts.

Introduction to Compose-file

In the Compose-file, you specify everything in YAML format as opposed to trying to remember all the arguments we have to pass to Docker commands. You can define services, networks and volumes here. The definition will be picked up by Docker and it is very much like passing command-line parameters to “docker run|network|volume” command.

As an introduction, we are going to deploy a simple standalone MySQL container. Before you start writing a Compose file, you first need to know the run command. Taken from our first MySQL on Docker blog series, let’s compose the following “docker run” command:

$ docker run --detach \ --name=test-mysql \ --publish 6603:3306 \ --env="MYSQL_ROOT_PASSWORD=mypassword" \ -v /storage/docker/mysql-datadir:/var/lib/mysql \ mysql

The docker-compose command will look for a default file called “docker-compose.yml” in the current directory. So, let’s first create the required directories beforehand:

$ mkdir -p ~/compose-files/mysql/single $ mkdir -p /storage/docker/mysql-datadir $ cd ~/compose-files/mysql/single

In YAML, here is what should be written:

version: '2' services: mysql: image: mysql container_name: test-mysql ports: - 6603:3306 environment: MYSQL_ROOT_PASSWORD: "mypassword" volumes: - /storage/docker/mysql-datadir:/var/lib/mysql

Save the above content into “~/compose-files/mysql/single/docker-compose.yml”. Ensure you are in the current directory ~/compose-files/mysql/single, then fire it up by running the following command:

$ docker-compose up -d WARNING: The Docker Engine you're using is running in swarm mode. Compose does not use swarm mode to deploy services to multiple nodes in a swarm. All containers will be scheduled on the current node. To deploy your application across the swarm, use `docker stack deploy`. Creating test-mysql

Verify if the container is running in detached mode:

[root@docker1 single]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 379d5c15ef44 mysql "docker-entrypoint..." 8 minutes ago Up 8 minutes 0.0.0.0:6603->3306/tcp test-mysql

Congratulations! We have now got a MySQL container running with just a single command.

Deploying a Stack

Compose-file simplifies things, it provides us with a clearer view on how the infrastructure should look like. Let’s create a container stack that consists of a website running on Drupal, using a MySQL instance under a dedicated network and link them together.

Similar to above, let’s take a look at the command line version in the correct order to build this stack:

$ docker volume create mysql_data $ docker network create drupal_mysql_net --driver=bridge $ docker run -d --name=mysql-drupal --restart=always -v mysql_data:/var/lib/mysql --net=drupal_mysql_net -e MYSQL_ROOT_PASSWORD="mypassword" -e MYSQL_DATABASE="drupal" mysql $ docker run -d --name=drupal -p 8080:80 --restart=always -v /var/www/html/modules -v /var/www/html/profiles -v /var/www/html/themes -v /var/www/html/sites --link mysql:mysql --net=drupal_mysql_net drupal

To start composing, let’s first create a directory for our new stack:

$ mkdir -p ~/compose-files/drupal-mysql $ cd ~/compose-files/drupal-mysql

Then, create write content of docker-compose.yml as per below:

version: '2' services: mysql: image: mysql container_name: mysql-drupal environment: MYSQL_ROOT_PASSWORD: "mypassword" MYSQL_DATABASE: "drupal" volumes: - mysql_data:/var/lib/mysql restart: always networks: - drupal_mysql_net drupal: depends_on: - mysql image: drupal container_name: drupal ports: - 8080:80 volumes: - /var/www/html/modules - /var/www/html/profiles - /var/www/html/themes - /var/www/html/sites links: - mysql:mysql restart: always networks: - drupal_mysql_net volumes: mysql_data: networks: drupal_mysql_net: driver: bridge

Fire them up:

$ docker-compose up -d .. Creating network "drupalmysql_drupal_mysql_net" with driver "bridge" Creating volume "drupalmysql_mysql_data" with default driver Pulling drupal (drupal:latest)... .. Creating mysql-drupal Creating drupal

Docker will perform the deployment as follows:

  1. Create network
  2. Create volume
  3. Pull images
  4. Create mysql-drupal (since container “drupal” is dependent on it)
  5. Create the drupal container

At this point, our architecture can be illustrated as follows:

We can then specify ‘mysql’ as the MySQL host in the installation wizard page since both containers are linked together. That’s it. To tear them down, simply run the following command under the same directory:

$ docker-compose down

The corresponding containers will be terminated and removed accordingly. Take note that the docker-compose command is bound to the individual physical host running Docker. In order to run on multiple physical hosts across Swarm, it needs to be treated differently by utilizing “docker stack” command. We’ll explain this in the next section.

Composing a Stack Across Swarm

Firstly, make sure the Docker engine is running on v1.13 and Swarm mode is enabled and in ready state:

$ docker node ls ID HOSTNAME STATUS AVAILABILITY MANAGER STATUS 8n8t3r4fvm8u01yhli9522xi9 * docker1.local Ready Active Reachable o1dfbbnmhn1qayjry32bpl2by docker2.local Ready Active Reachable tng5r9ax0ve855pih1110amv8 docker3.local Ready Active Leader

In order to use the stack feature for Docker Swarm mode, we have to use the Docker Compose version 3 format. We are going to deploy a setup similar to the above, apart from a 3-node Galera setup as the MySQL backend. We already explained in details in this blog post.

Firstly, create a directory for our new stack:

$ mkdir -p ~/compose-files/drupal-galera $ cd ~/compose-files/drupal-galera

Then add the following lines into “docker-compose.yml”:

version: '3' services: galera: deploy: replicas: 3 restart_policy: condition: on-failure delay: 30s max_attempts: 3 window: 60s update_config: parallelism: 1 delay: 10s max_failure_ratio: 0.3 image: severalnines/pxc56 environment: MYSQL_ROOT_PASSWORD: "mypassword" CLUSTER_NAME: "my_galera" XTRABACKUP_PASSWORD: "mypassword" DISCOVERY_SERVICE: '192.168.55.111:2379,192.168.55.112:2379,192.168.55.207:2379' MYSQL_DATABASE: 'drupal' networks: - galera_net drupal: depends_on: - galera deploy: replicas: 1 image: drupal ports: - 8080:80 volumes: - drupal_modules:/var/www/html/modules - drupal_profile:/var/www/html/profiles - drupal_theme:/var/www/html/themes - drupal_sites:/var/www/html/sites networks: - galera_net volumes: drupal_modules: drupal_profile: drupal_theme: drupal_sites: networks: galera_net: driver: overlay

Note that the Galera image that we used (severalnines/pxc56) requires a running etcd cluster installed on each of the Docker physical host. Please refer to this blog post on the prerequisite steps.

One of the important parts in our compose-file is the max_attempts parameter under restart_policy section. We have to specify a hard limit on the number of restarts in case of failure. This will make the deployment process safer because, by default, the Swarm scheduler will never give up in attempting to restart containers. If this happens, the process loop will fill up the physical host’s disk space with unusable containers when the scheduler cannot bring the containers up to the desired state. This is a common approach when handling stateful services like MySQL. It’s better to bring them down altogether rather than make them run in an inconsistent state.

To start them all, just execute the following command in the same directory where docker-compose.yml resides:

$ docker stack deploy --compose-file=docker-compose.yml my_drupal

Verify the stack is created with 2 services (drupal and galera):

$ docker stack ls NAME SERVICES my_drupal 2

We can also list the current tasks in the created stack. The result is a combined version of “docker service ps my_drupal_galera” and “docker service ps my_drupal_drupal” commands:

$ docker stack ps my_drupal ID NAME IMAGE NODE DESIRED STATE CURRENT STATE ERROR PORTS 609jj9ji6rxt my_drupal_galera.1 severalnines/pxc56:latest docker3.local Running Running 7 minutes ago z8mcqzf29lbq my_drupal_drupal.1 drupal:latest docker1.local Running Running 24 minutes ago skblp9mfbbzi my_drupal_galera.2 severalnines/pxc56:latest docker1.local Running Running 10 minutes ago cidn9kb0d62u my_drupal_galera.3 severalnines/pxc56:latest docker2.local Running Running 7 minutes ago

Once we get the CURRENT STATE as RUNNING, we can start the Drupal installation by connecting to any of the Docker host IP address or hostname on port 8080, as in this case we used docker3 (albeit the drupal container is deployed on docker1), http://192.168.55.113:8080/. Proceed with the installation and specify ‘galera’ as the MySQL host and ‘drupal’ as the database name (as defined in the compose-file under MYSQL_DATABASE environment variable):

That’s it. The stack deployment was simplified by using Compose-file. At this point, our architecture is looking something like this:

Lastly, to remove the stack, just run the following command:

$ docker stack rm my_drupal Removing service my_drupal_galera Removing service my_drupal_drupal Removing network my_drupal_galera_net Related resources  MySQL on Docker: Deploy a Homogeneous Galera Cluster with etcd  MySQL on Docker: Introduction to Docker Swarm Mode and Multi-Host Networking  MySQL Docker Containers: Understanding the basics Conclusion

Using compose-file can save you time and reduce the risk for human error, as compared to when working with long command lines. This is a perfect tool for you to master before working with multi-container Docker applications, dealing with multiple deployment environments (e.g dev, test, staging, pre-prod, prod) and handling much more complex services, just like MySQL Galera Cluster. Happy containerizing!

Tags: composedocker composeMySQLgaleradocker swarmkubernetes

Log Buffer #504: A Carnival of the Vanities for DBAs

This edition of Log Buffer covers Oracle, Microsoft SQL Server, and MySQL.

Oracle:

Data Pump or Data Pain Part02 – tablespace/user

Monday Spotlight: It’s About Security – Oracle Exadata SL6

0001_specify_date_format_mask.md copy

OGG Custom Adapters: How to include a unique identifier for every record in custom adapter?

One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join” that makes certain types of merge join much more efficient.

Microsoft SQL Server:

How to import data to Azure SQL Data Warehouse using SSIS

Using Index-Organized Tables in Oracle

Query Performance Tuning – A Methodical Approach

SQL Server Temporal Tables: How-To Recipes

Fix Commvault Backup Failures After an Upgrade

MySQL:

MySQL may return results in non-deterministic order with ‘order by’

Shinguz: MySQL and MariaDB authentication against pam_unix

MySQL in the Cloud – Pros and Cons of Amazon RDS

With 500+ VividCortex Users, Shopify Eliminates High Latency Queries From Redis and MySQL

An Introduction to MariaDB’s Data at Rest Encryption (DARE)

Releasing BSL 1.1

Releasing BSL 1.1 kajarno Tue, 02/14/2017 - 17:29

When we originally released BSL 1.0 together with MariaDB MaxScale 2.0 in August 2016, we were acutely aware that using a license not approved by the OSI equates swearing in the church (or your other favourite place of worship). That’s why we didn’t beat the BSL drum, just the MaxScale drum. We tested the waters, and after a while approached the authorities on Open Source. Specifically, based on earlier relationships from the late 1990s, we asked Bruce Perens to review the BSL. Bruce Perens is the founder of the Open Source Initiative, together with Eric Raymond (The Cathedral and the Bazaar).

Bruce had a number of comments, that have flown into what is now BSL 1.1. From the perspective of MariaDB MaxScale users, nothing really changes. The changes aim at enabling the BSL to be a viable license for other vendors than ourselves, by setting consistent expectations for users and customers, about what the BSL is.

Take the Creative Commons. Great thing, but there’s many varieties of it, so you need to spend time evaluating the fine print. Given that BSL also allows for a parameter section, Bruce saw a need to constrain that parameter section. We concur. Out of three parameters, we managed to put clear constraints on two.

First, the duration of the time window, as defined by the Change Date. “This software will convert to Open Source in ninety years” is a possible BSL 1.0 parameter, but it makes little sense. Now there is an automatic cap at four years. That said, we encourage a shorter window (and for MariaDB MaxScale, the window is between two and three years).

Second, the Open Source license when the time window closes, as defined by the Change License. Even amongst OSI approved Open Source licenses, there are some odd ones. Now we require the Change License to be GPL compatible (you may pick between GPLv2 and GPLv3, or any other license that can be combined properly with GPL software). And you can add additional change licenses as you desire.

Third, the Use Limitation. There we failed at identifying a reasonable least common denominator, as software comes in so many different shapes. Even “non-commercial use” is hard to define. As an example, we want to enable App developers to distribute their free versions under the BSL, where a payment trigger would be getting rid of ads. And what exactly is commercial use? Internal use, where you save a lot of cost, in producing value for your own customers? If the users earn money – directly or indirectly – the developer should be fed.

The outcome: We updated the BSL to accommodate these changes. We straightened the language, with the help of Heather Meeker of O’Melveny & Myers LLP [*]. We updated our MaxScale specific and vendor oriented BSL FAQs. And while our next MariaDB MaxScale releases [**] will use BSL 1.1, the more important part is that other vendors are in a better shape using a license endorsed by Bruce Perens.

[*] Heather is author of Open (Source) For Business: A Practical Guide to Open Source Licensing, and has been a pro-bono counsel to the Mozilla, GNOME, and Python foundations, so who better to ask than her.

[**] To be specific:

  • MariaDB MaxScale 1.4.5 (released 8 Feb 2017) is GPL and the following maintenance release MariaDB MaxScale 1.4.6 (whenever that may be) will remain GPL.
  • MariaDB MaxScale 2.0.4 (also released 8 Feb 2017) is BSL 1.0 but the following maintenance release MariaDB MaxScale 2.0.5 (whenever that may be) will move to BSL 1.1.
  • MariaDB MaxScale 2.1.0 will be released under BSL 1.1 from the start.  

So, our suggestion to our fellow software vendors: Take the BSL. Build your business model around it, over time. During the time window, you’ll pay your developer salaries, and when the window is closed, you’ve contributed to the world of Open Source. It’s authentic, it’s honest, it’s fair for your users, your customers, your developers and your shareholders alike. Go BSL!

When we originally released BSL 1.0 together with MariaDB MaxScale 2.0 in August 2016, we were acutely aware that using a license not approved by the OSI equates swearing in the church (or your other favourite place of worship). That’s why we didn’t beat the BSL drum, just the MaxScale drum. We tested the waters, and after a while approached the authorities on Open Source. Specifically, based on earlier relationships from the late 1990s, we asked Bruce Perens to review the BSL. Bruce Perens is the founder of the Open Source Initiative, together with Eric Raymond (The Cathedral and the Bazaar).

Login or Register to post comments

PHP-SQL-Parser updated with pull requests

I took some time this morning to check out and merge some existing pull requests for PHP-SQL-Parser, the most popular SQL parser for MySQL and PHP.

I'm thinking about adding a Validate($conn,$sql) method to the parser to validate the syntax of the SQL against the given MySQL database.

Pages