Get a handle on your HA at Percona Live London 2014

Liz_Fred_Kenny_Percona Live London

From left: Liz van Dijk, Frédéric Descamps and Kenny Gryp

If you’re following this blog, it’s quite likely you’re already aware of the Percona Live London 2014 conference coming up in just a few days. Just in case, though (you know, if you’re still looking for an excuse to sign up), I wanted to put a spotlight on the tutorial to be delivered by my esteemed colleagues Frédéric Descamps (@lefred) and Kenny Gryp (@gryp), and myself.

The past two years at Percona we’ve been spending a substantial amount of time working with customers taking their first steps into creating Highly Available MySQL environments built on Galera. Percona XtraDB Cluster allows you to get it up and running very fast, but as any weathered “HA” DBA will tell you, building the cluster is only the beginning. (Percona XtraDB Cluster is an open source (free) high-availability and high-scalability solution for MySQL clustering.)

Any cluster technology is likely to introduce a great amount of complexity to your environment, and in our tutorial we want to show you not only how to get started, but also how to avoid many of the operational pitfalls we’ve encountered. Our tutorial, Percona XtraDB Cluster in a nutshell, will be taking place on Monday 3 November and is a full-day (6 hours) session, with an intense hands-on approach.

We’ll be covering a great deal of practical topics, such as:

  • Things to keep in mind when migrating an existing environment over to PXC
  • How to manage and maintain the cluster, keeping it in good shape
  • Load balancing requests across the cluster
  • Considerations for deploying PXC in the cloud

Planning on attending? Be sure to come prepared! Given the hands-on approach of the tutorial, make sure you bring your laptop with enough disk space (~20GB) and processing power to run at least 4 small VirtualBox VM’s.

We look forward to seeing you there!

The post Get a handle on your HA at Percona Live London 2014 appeared first on MySQL Performance Blog.


Looking forward to Oracle OpenWorld 2014; visit us at Booth 2413!

Looking forward to Oracle OpenWorld 2014; visit us at Booth 2413!I’m excited to once again be heading to San Francisco next week for Oracle OpenWorld, and also very pleased to have a booth there this year along with some great speakers from Percona.

The scope of Oracle OpenWorld 2014, which runs Sept. 28-Oct. 2, is enormous and there are several keynotes and sessions I’m looking forward to attending. Especially the talks focused on the areas of:

  • MySQL architecture and application development
  • Cloud and Big Data
  • Database administration and DevOps
  • High-Availability and Replication
  • Performance and Scalability

I also have the honor of speaking on the topic of “Practical MySQL Optimization.” Other sessions led by Percona staff include:

All of us will, at one time or another, be at the Percona booth (Booth 2413), so please stop by and say “hello.” And while you’re there, register to win a new Kindle – the Fire HDX 8.9 Tablet.

We’ll also be raffling off passes to Percona Live London (Nov. 3-4), the annual Percona Live MySQL Conference and Expo 2015 (PLMCE, April 13-16) and the new OpenStack Live 2015 conference, which runs alongside PLMCE April 13-14 – also at the Hyatt Regency Santa Clara & the Santa Clara Convention Center.

Let me know if you’ll be attending and do be sure to stop by our booth and say “hello!” I hope to see you there!





The post Looking forward to Oracle OpenWorld 2014; visit us at Booth 2413! appeared first on Percona Performance Blog.


Using sysbench 0.5 for performing MySQL benchmarks

Given the recent excitement & interest around OpenStack I wanted to make sure I was ready to conduct appropriate evaluations of system performance.  I generally turn to sysbench since it comes with a variety of different tests (accessed via –test= option interface), including:

  • fileio – File I/O test
  • cpu – CPU performance test
  • memory – Memory functions speed test
  • threads – Threads subsystem performance test
  • mutex – Mutex performance test

As you can see, sysbench lets you stress many of the fundamental components of your hardware and infrastructure, such as your disk subsystem, along with your CPUs and memory. An additional option exists that is designed to perform synthetic stress testing of MySQL, and I was surprised when I didn’t see it in the above list on version 0.5, as it used to show up as “oltp – OLTP test”. What happened to –test=oltp ??

This list is from the latest release of sysbench which is 0.5 — you’re only going to be on this version if you build it yourself or if you use the package provided courtesy of Frederic Descamps (thanks lefred!).  If you’re using the version from EPEL, Ubuntu 14.04, or Debian 7 you’re still using version 0.4.12 (check with sysbench –version).  One thing you’ll notice is that the test type of OLTP doesn’t show up anymore.  What gives?  I was scratching my head until I asked on Percona IRC and found out that in 0.5 the standard OLTP test type was replaced with a different syntax, that instead of passing parameters to sysbench you instead reference scripts written in lua.  The advantage here is that now you have an interface in order to write your own specific load tests (provided you know lua, but it isn’t hard).  For those of you looking to run the pre-canned load tests they still exist but you have to have them as part of the RPM install or otherwise copied to your system.

Fortunately if you use the package provided by lefred you’ll find these lua scripts here (this is using Amazon ami as of August 4th, 2014):

[root@pxc-control ~]# ls -l /usr/share/doc/sysbench/tests/db/
total 44
-rw-r--r-- 1 root root 3585 Sep 7 2012 common.lua
-rw-r--r-- 1 root root 340 Sep 7 2012 delete.lua
-rw-r--r-- 1 root root 830 Sep 7 2012 insert.lua
-rw-r--r-- 1 root root 2925 Sep 7 2012 oltp.lua
-rw-r--r-- 1 root root 342 Sep 7 2012 oltp_simple.lua
-rw-r--r-- 1 root root 425 Sep 7 2012 parallel_prepare.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 select.lua
-rw-r--r-- 1 root root 3964 Sep 7 2012 select_random_points.lua
-rw-r--r-- 1 root root 4066 Sep 7 2012 select_random_ranges.lua
-rw-r--r-- 1 root root 343 Sep 7 2012 update_index.lua
-rw-r--r-- 1 root root 552 Sep 7 2012 update_non_index.lua

So the trick (if you want to call it that) is that instead of passing a single word to the –test directive, instead you pass the full path to the lua script.

This is the old way (sysbench 0.4.12 from EPEL repo):

--test=oltp --oltp-test-mode=complex

This is the new way (sysbench 0.5):


Here is an example of a test I’m running through haproxy against a 3-node PXC cluster doing the INSERT-only test type so you can see the full syntax I pass to sysbench:

[root@pxc-control ~]# cat sys_haproxy.sh

And here’s what the insert.lua script looks like:

[root@pxc-control ~]# cat /usr/share/doc/sysbench/tests/db/insert.lua
pathtest = string.match(test, "(.*/)") or ""
dofile(pathtest .. "common.lua")
function thread_init(thread_id)
function event(thread_id)
   local table_name
   local i
   local c_val
   local k_val
   local pad_val
   table_name = "sbtest".. sb_rand_uniform(1, oltp_tables_count)
   if (oltp_auto_inc) then
      i = 0
      i = sb_rand_uniq(1, oltp_table_size)
   k_val = sb_rand(1, oltp_table_size)
   c_val = sb_rand_str([[
   pad_val = sb_rand_str([[
   rs = db_query("INSERT INTO " .. table_name ..  " (id, k, c, pad) VALUES " .. string.format("(%d, %d, '%s', '%s')",i, k_val, c_val, pad_val))

The thing that I like most about sysbench 0.5 (beyond the lua interface, of course!) is that it now comes with a –report-interval option (which I generally set as = 1) so that you get output while the script is running. No more waiting until the end of the test to get feedback! Here’s a sample of sysbench 0.5 in action running the INSERT test through a local haproxy instance and writing to three nodes in a PXC cluster such as OpenStack Trove might do:

[root@pxc-control ~]# ./sys_haproxy.sh run
sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 3
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored
Threads started!
[   1s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1099.28, response time: 9.86ms (95%)
[   2s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 973.02, response time: 10.77ms (95%)
[   3s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1181.01, response time: 6.23ms (95%)
[   4s] threads: 3, tps: 0.00, reads/s: 0.00, writes/s: 1103.00, response time: 6.77ms (95%)

I would also like to call your attention to a blog post by Nilnandan Joshi from Percona’s Support team where he describes a method to build sysbench 0.5 on Debian 7.  Thanks Nil for pointing this out!

I hope that helps others out there who upgrade to sysbench 0.5 and then have questions about where –test=oltp went to. I’d love to hear your own sysbench use cases, and whether anyone else is publishing lua scripts for their own load testing!

The post Using sysbench 0.5 for performing MySQL benchmarks appeared first on MySQL Performance Blog.


MariaDB: Selective binary logs events

In the first post in a series on MariaDB features we find interesting, we begin with selectively skipping replication of binlog events. This feature is available on MariaDB 5.5 and 10.

By default when using MySQL’s standard replication, all events are logged in the binary log and those binary log events are replicated to all slaves (it’s possible to filter out some schema). But with this feature, it’s also possible to bypass some events to be replicated on the slave(s) even if they are written in the binary log. Having those event in the binary logs is always useful for point-in-time recovery.

Indeed, usually when we need to not replicate an event, we set sql_log_bin = 0 and the event is bypassed: neither written into the binlog, neither replicated to slave(s).

So with this new feature, it’s possible to just set a session variable to tag events that will be written into the binary log and bypassed on demand on some slaves.

And it’s really easy to use, on the master you do:

set skip_replication=1;

and on the slave(s) having replicate_events_marked_for_skip='FILTER_ON_MASTER' or 'FILTER_ON_SLAVE' the events skipped on the master won’t be replicated.

The valid values for replicate_events_marked_for_skip are:

  • REPLICATE (default) : skipped events are replicated on the slave
  • FILTER_ON_SLAVE : events so marked will be skipped on the slave and not replicated
  • FILTER_ON_MASTER : the filtering will be done on the master so the slave won’t even receive it and then save network bandwidth

That’s a cool feature but when this can be very useful?

Use case:

For archiving this can be very interesting. Indeed most of the time when people is archiving data, they use something like pt-archiver that deletes the data and copy the removed data on an archive server.

Thanks to this feature, instead of having an archiving server where we copy the deleted data, it’s possible to have a slave where we won’t delete the data. This will be much faster (smarter?) and allows to have an archiving server always up to date. Of course in this case sql_log_bin = 0 would have worked (if we ignore the point-in-time recovery).
But with a Galera Cluster? Yes that’s where this feature is really cool, if we would have used sql_log_bin = 0 on a Galera Cluster node, all other nodes would have ignored the delete and the result would be inconsistency between the nodes.

So if you use an asynchronous slave as an archiving server of a Galera Cluster, this feature is really mandatory.

As illustrated below, you can have a MariaDB Galera Cluster node joining a Percona XtraDB Cluster that will be used to delete historical data using pt-archiver:


pt-archiver is started with --set-vars "skip_replication=1"

The post MariaDB: Selective binary logs events appeared first on MySQL Performance Blog.


Location for InnoDB tablespace in MySQL 5.6.6

There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me ;-) ) : “DATA DIRECTORY” for InnoDB tables.

This is implemented since MySQL 5.6.6 and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table (but it’s in some case with partitioned ones as you will see below). If you do so, the option will be just ignored:

mysql> CREATE TABLE `sales_figures` (
    ->   `region_id` int(11) DEFAULT NULL,
    ->   `sales_date` date DEFAULT NULL,
    ->   `amount` int(11) DEFAULT NULL
    -> DATA DIRECTORY = '/tb1/';
Query OK, 0 rows affected (0.11 sec)
mysql> alter table sales_figures engine=innodb data directory='/tb2/';
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> show warnings;
| Level   | Code | Message                         |
| Warning | 1618 |  option ignored |

You can read more information in the MySQL Manual: Specifying the Location of a Tablespace.

So it’s now possible if for example you use SSD or FusionIO disks to have the large log or archived table to cheaper disks as you won’t require fast random access for those table and then save some expensive diskspace.

The syntax is very simple:

mysql> CREATE TABLE `sales_figures` (
  `region_id` int(11) DEFAULT NULL,
  `sales_date` date DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
mysql> select @@datadir;
| @@datadir       |
| /var/lib/mysql/ |

And in fact if we check on the filesystem:

# ls -lh /var/lib/mysql/fred/
total 20K
-rw-r--r-- 1 mysql mysql 65 May 23 22:30 db.opt
-rw-r--r-- 1 mysql mysql 8.5K May 23 22:30 sales_figures.frm
-rw-r--r-- 1 mysql mysql 31 May 23 22:30 sales_figures.isl

Not the new file .isl (referred as a link to the RemoteDatafile in the source code) that contains the location of the tablespace:

[root@imac2 tmp]# cat /var/lib/mysql/fred/sales_figures.isl

And indeed the tablespace is there:

[root@imac2 tmp]# ls -lh /tmp/tb1/fred/
total 96K
-rw-r--r-- 1 mysql mysql 96K May 23 22:30 sales_figures.ibd

This is really great ! And something even nicer, it finally works with partitioning too (before that was only possible for MyISAM tables):

mysql> CREATE TABLE sales_figures (region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
   PARTITION US_DATA VALUES IN(100,200,300) DATA DIRECTORY = '/tmp/tb1',

[root@imac2 mysql]# ls -l /tmp/tb1/fred/sales_figures#P#US_DATA.ibd
-rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb1/fred/sales_figures#P#US_DATA.ibd

[root@imac2 mysql]# ls -l /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd
-rw-rw—- 1 mysql mysql 98304 May 23 16:19 /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd

So now you can have some partitions on fast disks and some on slower disks. This is great for historical partitioning.

For example you have a table orders partitioned by years as follow:

create table orders (id int, purchased DATE)
  partition by range (YEAR(purchased)) (
     partition pre2012 values less than (2012) DATA DIRECTORY '/hdd/',
     partition pre2013 values less than (2013) DATA DIRECTORY '/hdd/',
     partition pre2014 values less than (2014) DATA DIRECTORY '/hdd/',
     partition current values less than MAXVALUE DATA DIRECTORY '/ssd/'

Only the partition handling the orders for the current year is on SSD.
At the end of the year, you can recreate a new partition and move all the data for 2014 on slower disks:

       partition pre2015 values less than (2015) DATA DIRECTORY '/hdd/',
       partition current values less than MAXVALUE DATA DIRECTORY '/ssd');

Notice that XtraBackup is also aware of these tablespaces on different locations and is able to deal with them.

There is currently only one issue is that with –copy-back, you need to have the full path created for the tablespaces not in the MySQL data directory.

So in the example above I had to create /tmp/tb1/fred and /tmp/tb2/fred before being able to run innobackupex –copy-back
(see bug 1322658).

I hope now that this important feature got some more visibility as it deserves it.

The post Location for InnoDB tablespace in MySQL 5.6.6 appeared first on MySQL Performance Blog.


innotop 1.9.1 released

Screen Shot 2013-07-12 at 10.45.58Lefred and I spent a bit of time making innotop 1.9.1.
We’ve released a new version mainly to include MySQL 5.6 support as well as including some bugs fixed by Baron Schwartz and Frédéric Descamps.

You can download the .tar.gz and rpm’s (new!) at

Bugs fixed:

  • Issue 71: DBI prints to STDERR instead of just throwing an inactive statement error
  • Issue 73: innotop fails to start if perl is not installed under /usr/bin/
  • Issue 74: Use of uninitialized value $obj in regexp compilation at ./innotop-1.9.0/innotop line 6935.
  • Issue 82&83: Some pages broken with MySQL 5.6
  • Issue 70: .spec doesn’t build rpm if someperl modules are missing
  • Issue 84: feature request: Deadlock clearing transactions set sql_log_bin=0

The post innotop 1.9.1 released appeared first on MySQL Performance Blog.


2 new features added to Percona XtraDB Cluster (PXC) since 5.5.31

With the last Percona XtraDB Cluster (PXC) release, two major features were added:

* a new command to bootstrap the cluster was added to the init script
* SST via Xtrabackup now supports Xtrabackup 2.1 features

In this post, I want to explain the benefits of these added features and how to use them.

If you follow the mysqlperformanceblog regularly, you’ve already noticed that there are several ways to start a node and that it’s not always easy to start the node that is supposed to bootstrap the entire cluster.

See :
new undocumented option –wsrep-new-cluster

With the new option, bootstrap-pxc, there is no need to modify my.cnf or add parameters (when supported) to the init script. Now you can simply bootstrap (start the first node that will initiate the cluster) using one of these commands :

/etc/init.d/mysql bootstrap-pxc


service mysql bootstrap-pxc

Much easier, isn’t it ?

Now the second added feature is the support of xbstream from Xtrabackup 2.1.

This is useful as it will help to speed up SST operations as the backup could be performed in parallel, compressed and compacted (without copying the secondary indexes).

To configure this, you just need to add two sections to my.cnf: [sst] and [xtrabackup] like this:


Some example to compare the effect of these parameters.

We have one database with 4 InnoDB tables of 480M, and we test Xtrabackup SST with the following settings:

Blue:Xtrabackup with tar (default)
Red: Xtrabackup with xbstream and 2 threads (like the configuration above)
Yellow: Xtrabackup with xbstream and 4 threads


This quick benchmark was realized on a small dataset with tables not having secondary indexes (no benefits from compact). I will prepare a more detailed benchmark for a future blog post.

But we can see that the new settings helps to “free” the donor faster, but the process on the joiner takes more time.
So it depends on what are your constraints to choose the best options that fit your workload’s requirements.

Remember: to be able to use Xtrabackup as SST you need to add in my.cnf:

Important: to be able to use xbstream with compression, you need to have qpress installed on the system and in the mysql user’s path (like /usr/bin for example).

Also the MySQL datadir must be empty before starting the SST process (see lp:1193240)

If you want to get familiar with these new options, I’ve updated my puppet recipes available on github.

For rebuild-threads, the default is $(grep -c processor /proc/cpuinfo) and for qpress, the decompression is also done in parallel (with number of qpress instances == $(grep -c processor /proc/cpuinfo)).

The post 2 new features added to Percona XtraDB Cluster (PXC) since 5.5.31 appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com