MySQL Tutorials: A time to learn at Percona Live 2015

The many hours of intensive tutorials, led by some of the top minds in MySQL, have always been a major draw each year to the Percona Live MySQL Conference and Expo. And 2015’s event will be no exception.

Percona Live 2015 runs April 13-16 in Santa Clara, Calif. and the first day is dedicated to the classroom – so bring your laptops for the combined 45 hours of learning. MySQL tutorials are included with the full-conference pass but a “tutorial-only pass” is also available. Super-saver registration discounts have been extended until Dec. 19. Here’s a look at this year’s tutorials lineup. (There will be a couple more announced in January).

And that’s just on Monday! There will be much more over the four days of the Percona Live MySQL Conference and Expo 2015. I posted a sneak peek of the full Percona Live (initial) roster a couple weeks ago. And remember, super-saver registration discounts have been extended until Dec. 19 so register now – and don’t forgot your laptop (and power cord)!

The post MySQL Tutorials: A time to learn at Percona Live 2015 appeared first on MySQL Performance Blog.


Percona Live London: Top Ten reasons to attend Nov. 3-4

Percona Live London 2014Percona Live London 2014 is fast approaching – November is just around the corner. This year’s conference, November 3-4, will be even bigger and better than last year thanks to the participation of leading MySQL experts the world over (including you!).

The Percona Live London MySQL Conference is a great event for users of any level using any of the major MySQL branches: MySQL, MariaDB or Percona Server. And this year we once again host a star-studded group of keynote speakers from industry-leading companies in the MySQL space.

We’ll also be welcoming leading MySQL practitioners from across the industry (and from all corners of the world) who will speak on topics that matter to you now  – see the full conference schedule here:

Monday starts early with a full day of tutorials and a fun evening at the community dinner.  Attendees will be arriving in true London style on a double-decker bus! Tuesday morning will kick-off with a series of keynotes followed by interactive breakout sessions – wrapping things up at the end of the day with a fun post-conference reception (a great chance to make new friends and reconnect with old ones).

Here’s a sneak peek at some of the must-see events this year:

To recap, here are the Top Ten reasons to attend Percona Live London this November 3-4:

10. Advanced Rate Pricing ends October 5th
9. Hear about the hottest current topics and trends.
8. Network! Meet face-to-face in the “hallway track” and make lasting connections.
7. Learn how to make MySQL work better for you – regardless of your expertise.
6. Have a blast at the community dinner!
5. Discuss your unique challenges with experts and discover options for solving them.
4. Engage with the sponsors at their tabletop exhibits.
3. Listen to top industry leaders describe the future of the MySQL ecosystem
2. Learn what works, and what doesn’t, from leading companies using MySQL
1. And the Number 1 reason to attend Percona Live London 2014: ALL of the above!

I look forward to seeing you in London this November and don’t forgot that Advanced Rate Pricing pricing ends October 5 so be sure to register now!

The post Percona Live London: Top Ten reasons to attend Nov. 3-4 appeared first on MySQL Performance Blog.


Check for MySQL slave lag with Percona Toolkit plugin for Tungsten Replicator

Continuent Tungsten ReplicatorA while back, I made some changes to the plugin interface for pt-online-schema-change which allows custom replication checks to be written. As I was adding this functionality, I also added the --plugin option to pt-table-checksum. This was released in Percona Toolkit 2.2.8.

With these additions, I spent some time writing a plugin that allows Percona Toolkit tools to use Tungsten Replicator to check for slave lag, you can find the code at


The plugin uses the perl JSON::XS module (perl-JSON-XS rpm package,, make sure it’s available or the plugin will not work.


We need to use the --recursion-method=dsns as the Percona Toolkit tools are not able to automatically find the tungsten replicator slaves that are connected to the master database. (I did add a blueprint on launchpad to make this possible

The dsns recursion-method gets the list of slaves from a database table you specify:

CREATE TABLE `percona`.`dsns` (
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)

Here one slave node3 is replicating from the master:

node1 mysql> select * from percona.dsns;
| id | parent_id | dsn     |
|  2 |      NULL | h=node3 |


Currently, it is not possible to specify extra options for the plugin with Percona Toolkit, so some manual editing of the perl file is still necessary to configure it.

So before we can run a checksum, we need to configure the plugin:

# trepctl command to run
my $trepctl="/opt/tungsten/installs/cookbook/tungsten/tungsten-replicator/bin/trepctl";
# what tungsten replicator service to check
my $service="bravo";
# what user does tungsten replicator use to perform the writes?
# See Binlog Format for more information
my $tungstenusername = 'tungsten';

Running A Checksum

Here I did a checksum of a table with pt-table-checksum. During the checksum process, I brought the slave node offline and brought it back online again:

# pt-table-checksum
        -u checksum
        --databases app
Created plugin from /vagrant/
PLUGIN get_slave_lag: Using Tungsten Replicator to check replication lag
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Replica node3 is stopped.  Waiting.
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Replica lag is 125 seconds on node3.  Waiting.
Replica lag is 119 seconds on node3.  Waiting.
Checksumming app.large_table:  22% 00:12 remain
07-03T10:49:54      0      0  2097152       7       0 213.238 app.large_table

I recommend to change the check-interval higher than the default 1 second as running trepctl takes a while. This could slow down the process quite a lot.

Making Schema Changes

The plugin also works with pt-online-schema-change:

# pt-online-schema-change
        -u schemachange
        --alter "add index (column1) "
        --execute D=app,t=large_table
Created plugin from /vagrant/
Found 1 slaves:
Will check slave lag on:
PLUGIN get_slave_lag: Using Tungsten Replicator to check replication lag
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `app`.`large_table`...
Creating new table...
Created new table app._large_table_new OK.
Waiting forever for new table `app`.`_large_table_new` to replicate to node3...
Altering new table...
Altered `app`.`_large_table_new` OK.
2014-07-03T13:02:33 Creating triggers...
2014-07-03T13:02:33 Created triggers OK.
2014-07-03T13:02:33 Copying approximately 8774670 rows...
Copying `app`.`large_table`:  26% 01:21 remain
Copying `app`.`large_table`:  50% 00:59 remain
Replica lag is 12 seconds on node3.  Waiting.
Replica lag is 12 seconds on node3.  Waiting.
Copying `app`.`large_table`:  53% 02:22 remain
Copying `app`.`large_table`:  82% 00:39 remain
2014-07-03T13:06:06 Copied rows OK.
2014-07-03T13:06:06 Swapping tables...
2014-07-03T13:06:06 Swapped original and new tables OK.
2014-07-03T13:06:06 Dropping old table...
2014-07-03T13:06:06 Dropped old table `app`.`_large_table_old` OK.
2014-07-03T13:06:06 Dropping triggers...
2014-07-03T13:06:06 Dropped triggers OK.
Successfully altered `app`.`large_table`.

As you can see, there was some slave lag during the schema changes.

Binlog Format & pt-online-schema-change

pt-online-schema-change uses triggers in order to do the schema changes. Tungsten Replicator has some limitations with different binary log formats and triggers (

In Tungsten Replicator, ROW based binlog events will be converted to SQL statements, which causes triggers to be executed on the slave as well, this does not happen with traditional replication.

Different settings:

  • STATEMENT based binary logging works by default
  • ROW based binary logging works, the plugin recreates the triggers and uses the technique documented at
  • MIXED binary logging does not work, as there is currently no way to determine whether an event was written to the binary log in statement or row based format, so it’s not possible to know if triggers should be run or not. The tool will exit and and error will be returned:
    Error creating --plugin: The master it's binlog_format=MIXED,
    pt-online-schema change does not work well with
    Tungsten Replicator and binlog_format=MIXED.

Be Warned

The binlog_format can be overriden on a per session basis, make sure that this does NOT happen when using pt-online-schema-change.


The documentation on the Continuent website already mentions how you can compare data with pt-table-checksum.

I believe this plugin is a good addition to it. The features in Percona Toolkit that monitor replication lag can now be used with Tungsten Replicator and therefore gives you control on how much replication lag is tolerated while using those tools.

The post Check for MySQL slave lag with Percona Toolkit plugin for Tungsten Replicator appeared first on MySQL Performance Blog.


Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo

The Percona Live MySQL Conference and Expo begins next Monday and runs April 22-25, 2013. Attendees will see great keynotes from leaders in the industry including representatives from Oracle, Amazon Web Services, HP, Continuent, and Percona. They can also participate in thought provoking Birds of a Feather sessions on Tuesday night and the Wednesday night Community Networking Reception will be fun and entertaining with the presentation of the Community Awards and the Lightning Talks.

If you cannot attend the entire Percona Live MySQL Conference but want to take advantage of the keynotes, BOFs, and Community Networking Reception, I’m pleased to offer a limited number of $5 Expo Only passes. Use discount code “KEY” when registering for the Percona Live MySQL Conference. Hurry, though, as only 100 passes are available at this price! This discount is only available for new ticket purchases. The regular price for Expo Only passes is $50 prior to the conference and $100 onsite.

Percona Live MySQL Conference and Expo Logo

I’m personally looking forward to Matt Aslett’s keynote on Thursday morning of the Percona Live MySQL Conference. Matt is the Research Director, Data Management and Analytics for 451 Research. The description for his talk, “The State of the MySQL Ecosystem“, summarizes what we’re all coming to understand:

“It is now over three years since Oracle acquired MySQL along with Sun Microsystems. Fears for the open source database’s survival appear to have been misplaced as Oracle has increased investment in MySQL development. At the same time, a thriving ecosystem of potential alternatives and complementary products has emerged to provide MySQL users with greater choice in terms of both functionality and support. As a result of that choice, we are seeing the increasing independence of the ecosystem of MySQL-related products and services from MySQL itself – both in terms of a commercial product, and also a development project. The continued maturity of vendors such as Percona and SkySQL, as well as the formation of the MariaDB Foundation, has the potential to accelerate that trend. The MySQL ecosystem is far from fragmenting, but 451 Research’s updated survey of database users indicates that the center of gravity has begun to shift towards an increased state of independence.”

Mirroring the growing diversity in the MySQL ecosystem, Percona Live MySQL Conference attendees have an opportunity to hear from a variety of server projects in both the keynotes and breakout sessions including presentations on Oracle MySQL, Percona Server, and MariaDB during the Percona Live MySQL Conference.

Percona Live MySQL Conference Keynotes




Breakout Sessions on Oracle MySQL

Breakout Sessions on Percona Server and Related Projects

Breakout Sessions on MariaDB

The Percona Live MySQL Conference includes a Diamond Sponsor Keynote panel on Wednesday morning on the “Impact of MySQL 5.6 and its Future in the Cloud”. Moderated by me, the panel will include MySQL industry leaders Simone Brunozzi, senior technology evangelist at Amazon Web Services; Robert Hodges, CEO of Continuent; Brian Aker, fellow, HP Cloud Division; and Peter Zaitsev, co-founder and CEO of Percona. The discussion will focus on MySQL 5.6 and how MySQL must evolve if it is to remain competitive in the new world order of the cloud and big data.

If you can join us in Santa Clara for the Percona Live MySQL Conference and Expo, use discount code “Percona15″ to receive 15% off your full conference pass. If you can only make it to the keynotes, BOFs, or Community Networking Reception, use discount code “KEY” for a $5 Expo Only pass. And if you cannot make it this year, watch this blog following the conference and we’ll announce when and where the keynote recordings and breakout session slide decks can be found.

The post Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by