Personal blog of Yzmir Ramirez

Our previous GA release of Percona XtraDB Cluster caused a lot of interest and feedback. I am happy to announce next version Percona XtraDB Cluster 5.5.23, which comes with bug fixes and improvements.

List of changes:

• Fixes merged from upstream (Codership-mysql)
• Support for MyISAM, now changes to MyISAM tables are replicated to other nodes
• Improvements to XtraBackup SST methods, better error handling
• New SST wsrep_sst_method=skip, useful when you start all nodes from the same sources (i.e. backup)
• Ability to pass list of IP addresses for a new node, it will connect to the first available

Binaries are available from downloads area or from our repositories.

For this release we will provides binaries for Ubuntu 12.04, they are coming soon.

If you want to know more how to migrate to XtraDB Cluster, we will be giving a free webinar on June 6th.

This is an General Availability release. We did our best to eliminate bugs and problems during alpha and beta testing release, but this is a software, so bugs are expected. If you encounter them, please report to our bug tracking system.

Links:

• We provide tar.gz and RPM binaries for RedHat (CentOS, Oracle Linux) 5 and 6, and Debian packages.
  Downloads: http://www.percona.com/downloads/Percona-XtraDB-Cluster/
• Documentation
  Codership Wiki
• General Discussion group
• Launchpad project
• Bug reports

I’m happy to announce that version 1.0 of Percona Monitoring Plugins is now available. The Percona Monitoring Plugins are high-quality plugins, templates, and add-ons for Nagios and Cacti, so you can add world-class MySQL monitoring to your existing enterprise open-source monitoring products.

Version 1.0 fixes a variety of bugs that users found in version 0.9.0. Here are the usual links: changelog, documentation, downloads, source code, bug reports, mailing list, and of course, you should purchase a support contract.

If you have recently attended some Percona Live events or if you have checked some slides from Yves Trudeau, you may have heard about Percona Replication Manager (PRM), a new high availability tool for MySQL.

• Percona Live DC 2012
• Percona Live MySQL Conference & Expo 2012

PRM is an OCF Resource Agent for Corosync / Pacemaker that take cares of MySQL and manages the replication.

Corosync allows any number of servers to be part of the cluster using any number of fault-tolerant configurations (active/passive, active/active, N+1, etc.) It also provides messaging between servers within the same cluster.

Pacemaker manages the resources and applications on a node within the cluster.

I have created a Vagrant environment to speed up installation and testing this new solution for HA.

The url of the git repository including the Vagrant file and the puppet recipes is https://github.com/lefred/percona-prm.

You can use the CentOS 6 base box I used in the previous post (Testing Percona XtraDB Cluster with Vagrant).

In the follow “terminalcast” you can see how to deploy the setup and some basic commands to check and manage your cluster:

I’m proud to announce the GA release of version 2.1 of Percona Toolkit. Percona Toolkit is the essential suite of administrative tools for MySQL.

With this release we introduce a new version of pt-online-schema-change, a tool that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table with no interruption.

With this new version of the tool, one of the most painful things anyone experiences with MySQL is significantly alleviated. If you’ve ever delayed a project’s schedule because the release involved an ALTER, which had to be scheduled in the dead of the night on Sunday, and required overtime and time off, you know what I mean. A schema migration is an instant blocker in the critical path of your project plan. No more!

The pt-online-schema-change tool is not new to the toolkit. We developed it some time ago for a client and quietly included it, without knowing how much it would be used. We found that people were using it eagerly even without advertisement. However, it had many limitations and missing features. The new 2.1 version of the tool removes those limitations and makes the tool fully featured and much easier to use. The key is that it is replication-aware and inspects replicas, throttles its operation to avoid causing replication delay, watches for load on the master, and so forth. In this respect it is quite similar to pt-table-checksum, and it has all of the safeguards you might be familiar with from pt-table-checksum.

I will give a free webinar about the new pt-online-schema-change tool and how it enables zero-downtime schema changes on May 2nd.

In addition to pt-online-schema-change, we have completely redesigned and rebuilt the pt-summary and pt-mysql-summary tools. The changes are not so obvious to the user, but the benefit is that in the future we will be able to add features to these tools rapidly, for example, to support new types of RAID controllers.

Percona Toolkit is a hot topic at the upcoming MySQL Conference and there will be many talks about various parts of the toolkit:

• A 3-hour tutorial about the toolkit on Tuesday
• Daniel will talk about using it to verify replication
• Peter will discuss using it to verify upgrades
• I’ll show how to use it to forecast scalability and performance

The new Percona Toolkit release is available for download today. It is also in our YUM and DEB repositories. And you can read the documentation, discuss it on the mailing list, get the source code from Launchpad, and file bug reports on Launchpad.

PS: in addition to Percona Toolkit’s release today, there is also a version 1.0 GA release of the Percona Monitoring Plugins project. It features a variety of bug fixes.

I am excited to announce the availability of the GA release of our new product Percona XtraDB Cluster.

Percona XtraDB Cluster is a High Availability and Scalability solution for MySQL Users and is based on Percona Server 5.5.20.

With this release we make clustering very easy and affordable for everyone. You can convert your existing MySQL replication setup into a cluster in just a couple minutes.

Tomorrow, 4-April-2012, I will be giving a webinar where I will explain what Percona XtraDB Cluster is and how does it work, you still have time to register today.

I expect XtraDB Cluster and Galera will be a big topic on coming MySQL User Conference, both Codership and Percona development team will be there, you will have a chance to ask us any questions.

Related talks:

• Tutorial on PL MySQL Conference Galera Synchronous Multi-Master Replication for MySQL.
• My talk on PL MySQL Conference Percona XtraDB Cluster: New HA solution.

Percona XtraDB Cluster provides:

• Synchronous replication. Transaction either commited on all nodes or none.
• Multi-master replication. You can write to any node.
• Parallel applying events on slave. Real “parallel replication”.
• Automatic node provisioning.
• Data consistency. No more unsyncronised slaves.

Percona XtraDB Cluster is fully compatible with MySQL or Percona Server in the following meaning:

• Data compatibility. Percona XtraDB Cluster works with databases created in MySQL / Percona Server
• Application compatibility. There is no or minimal application changes required to start work with Percona XtraDB Cluster

Percona XtraDB Cluster is based on Percona Server with XtraDB and includes Write Set REPlication patches. It uses the Galera library, version 2.x, a generic Synchronous Multi-Master replication plugin for transactional applications.

The Galera library is developed by Codership Oy company and
Galera 2.x supports such new features as:

• Incremental State Transfer, especially useful for WAN deployments
• RSU, Rolling Schema Update. Schema change does not block operations against table

This is an General Availability release. We did our best to eliminate bugs and problems during alpha and beta testing release, but this is a software, so bugs are expected. If you encounter them, please report to our bug tracking system.

Links:

• We provide tar.gz and RPM binaries for RedHat (CentOS, Oracle Linux) 5 and 6, and Debian packages.
  Downloads: http://www.percona.com/downloads/Percona-XtraDB-Cluster/
• Documentation
  Codership Wiki
• General Discussion group
• Launchpad project
• Bug reports

Previous posts on this topic:

• Percona XtraDB Cluster Feature 1: High Availability
• Percona XtraDB Cluster Feature 2: Multi-Master replication
• Create 3 nodes XtraDB Cluster in 3 minutes
• Benchmarking Galera replication overhead
• Percona Server 5.5.15 + Galera 21.1-beta2
• State of the art: Galera – synchronous replication for InnoDB

Happy clustering!

I’m excited by all of the recent developments surrounding the Percona Live MySQL Conference and Expo! Our own Baron Schwartz will moderate the Diamond Keynote Panel entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of MySQL industry leaders, including: Sundar Raghavan, director product management at Amazon; Paul Mikesell, CEO of Clustrix; a representative from HP; and, a representative from McAfee. The Diamond Sponsor Keynote Panel will take place at 9:30 a.m. on Thursday, April 12th and provide insight into the future of MySQL technology, adoption, and the ecosystem landscape. I am also very pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which recently agreed to participate as a lunch sponsor.

We also recently announced the Birds of a Feather Sessions which will take place on Tuesday at 6:30 p.m. and Wednesday at 9:00 p.m. BOFs enable attendees with the same project or topic interests to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby on Rails”, “MySQL in the Cloud”, and much more.

We also published the list of Lightning Talks, a series of five-minute speaking opportunities during which attendees will propose, explain, exhort, and rant on a variety of MySQL-related topics. The one-hour Lightning Talks session will take place at 6:30 p.m. on Wednesday night during the Community Networking Reception. Topics include “The Five Minute Tour of Drizzle”, “How to Design a Scalable System”, and “How to Become a Rockstar DBA”.

The Percona Live MySQL Conference and Expo promises to be a great event! If you already registered, I look forward to seeing you in Santa Clara next week. If you haven’t registered but plan to attend, register now using discount code PerLiveSC and receive 10% off your registration fees. If you just cannot make it this year, keep your eyes open for recordings of the keynote sessions which we plan to post online following those events.

Percona is glad to announce the release of Percona Server 5.5.21-25.1 on March 30, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.21, including all the bug fixes in it, Percona Server 5.5.21-25.1 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.21-25.1 milestone at Launchpad.

We are doing another release based on MySQL 5.5.21 as we have a couple of important bug fixes that we wish to make available.

Bugs Fixed:

• Fixed a memory corruption regression introduced in 5.5.18-23.0. Bug fixed #915814 (Alexey Kopytov).
• Fixed InnoDB compilation warnings on CentOS 5. Bug fixed #962940 (Laurynas Biveinis).
• Fixed MySQL upstream bug #64160 that was causing issues on upgrade to 5.5.20 and 5.5.21. Bug fixed #966844 (Stewart Smith).

Full release notes are available in the documentation.

Most of the Nagios plugins included with the Percona Monitoring Plugins are purpose-built for specific scenarios that I found in my research to be frequent, severe, or subtle causes of problems in MySQL systems. They are intentionally not generic because I wanted to focus on doing one thing with each plugin, and doing it excellently. This makes the plugins easier to set up and use, and I believe it should help avoid some of the common problems such as spammy alerts.

There is one “generic, flexible” plugin, however, which you can use for fairly arbitrary checks of status counters and variables. This is the pmp-check-mysql-status plugin, which can perform computations on status variables. By default, I recommend using it only for two purposes:

• Check whether the Uptime variable is too small, indicating the server has been restarted
• Check whether Threads_connected is approaching max_connections, which can be inconvenient to solve in some environments

However, there’s no reason not to use the plugin for other purposes if you want. Here are some of the things you could do:

• Compare a counter to a threshold
• Compare a counter to a variable or another counter
• Add, subtract, multiply, and divide counters or variables
• Transform a ratio into a percentage
• Compute the rate of change over time for one or more counters (e.g. queries per second)

The documentation contains examples of how to do all of these things, if you so desire.

I frequently see questions about how to understand the nature of errors in MySQL’s error log. Now, there is a lot of complexity to this — the flowchart would be quite large, as with any nontrivial piece of software. But there is one particular class of errors that is relatively easy to diagnose, if you pay close attention to the error message.

Often an error has a little number in it, as in this example from our forums:

120326 16:56:45 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_21b2_0.MYI'; try to repair it
120326 16:56:45 [ERROR] Got an error from unknown thread, storage/myisam/mi_write.c:223
120326 16:56:45 [ERROR] /usr/sbin/mysqld: Sort aborted: Error writing file '/tmp/MYK74Kpi' (Errcode: 28)

The gem in all of that mess is this bit: (Errcode: 28). Now, MySQL includes a little perror utility to translate this into something a human can understand:

$ perror 28
OS error code  28:  No space left on device

That’s the key to understanding what really happened. MySQL created a temporary table, using the MyISAM storage engine, and when the disk filled up, MyISAM got an error. Little-known secret: MyISAM doesn’t handle a disk-full error gracefully

Sometimes I’ve seen people having trouble with error codes that they don’t believe. Good examples are error 13 (permission denied) or 2 (No such file or directory). The typical reaction is “but it certainly has permission!” or “I can see the file/directory myself, of course it exists!” In cases such as these you need to dig more deeply. The error code is not wrong, I promise you. If it says permission is denied, then permission is denied; there is no bug — that error comes from the operating system. Typical causes are things like SELinux or AppArmor interfering.

So, if you see an error code, try to diagnose with that first. It’s often the easiest route to a solution.

PS: You may wish to get a copy of MySQL Troubleshooting by Sveta Smirnova — I have not read it yet, but I’ve heard really good things about it.

The Percona Live MySQL Conference & Expo is going to be awesome! Great speakers, an A-list of sponsors, countless opportunities to engage with the community, and an enthusiastic crowd of MySQL users ensure this is going to be a great event. The conference features 72 breakout sessions, keynotes by leading industry luminaries, an optional day of 16 tutorial sessions, a bustling exhibit hall, and numerous opportunities to connect with other community members.

I am pleased to announce the conference Birds of a Feather sessions and Lightning Talks. Birds of a Feather sessions will be Tuesday and Wednesday nights following the evening receptions. Lightning Talks will be Wednesday evening in the Exhibit Hall during the Community Networking Reception. Check the conference schedule for more details on times and locations.

The conference room block at the Hyatt is already sold out and the tutorial sessions, which require preregistration, are filling rapidly. Don’t miss out on this great opportunity to connect with the MySQL community at the largest community gathering in the world.

To sweeten the deal and help convince you to register now rather than two weeks from now, we are pleased to make a special, limited time offer. Register between Thursday March 22nd and midnight PDT on Thursday March 29th and you will be entered in a drawing for:

• One of three new Kindle Fires
• One of five copies of the new book High Performance MySQL, 3rd Edition

Don’t miss this special offer – register now at the conference registration website!