Personal blog of Yzmir Ramirez

I’ll be presenting at MySQL Meetup in Raleigh,NC February 21,2012. The talk with be about Optimizing MySQL Configuration which I believe is a great topic for my first talk at this meetup group as it covers something every MySQL user has to deal with, also being something both beginner and advanced MySQL Users can learn a lot from. I’ll also bring High Performance MySQL 2nd edition for a drawing as well as exclusive discount codes for MySQL Conference and Expo 2012 in Santa Clara. See you there.

We’ve released an updated version of the MySQL Configuration Wizard we announced at the end of last year. If you don’t remember that announcement, here’s the short version: this is a tool to help you generate my.cnf files based on your server’s hardware and other characteristics.

We’ve gotten really good feedback on this tool, including this nice mention on Stack Exchange:

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf’s were put together by highly trained mysql certified dba’s.

So what’s changed in the new version of the Configuration Wizard? Quite a few things. We’ve rolled out the first iteration of the account and profile features. Now you get a homepage with your configuration files, so you can manage them and return to them anytime you like.

From this page (click on the image for a fullsize view) you can do things like sharing configuration files and emailing them to yourself. The new release also adds features like downloading the configuration files so you don’t have to copy-paste them.

If you share a configuration file, then the URL can be loaded by anyone, even if they’re not logged in. It’s kind of like sending someone a link to a pastebin or something like that. Screenshot:

Another new feature is something I’ve wanted for a long time: the ability to generate a more strict, safer configuration file. There’s a new page in the Wizard that lets you set a lot of sanity/safety options to prevent common problems MySQL users run into because of too-permissive MySQL behaviors. These are the kinds of things that Drizzle fixes — and should be fixed by default in MySQL — but never will be because they might break applications that rely on the default behaviors. If you’re building an application from the ground up, now you can prevent bad things from getting a nose under the tent. Here’s a screenshot:

In addition to these things, we have added a number of other features you might not notice, which I won’t spend much time on — they’re things like an integrated feedback form at the left of the page and so on.

What’s next? Well, next I think we’re going to turn our attention to adding new tools, rather than improving this one. I have a list of tools that people have requested or suggested: a SQL formatter, a visual EXPLAIN tool, a configuration advisor, a query analysis tool, a way to register a server’s essential characteristics and then get advice when there’s a new release that might be beneficial for you, and so on. I have selected the next priorities, but I don’t want to spoil the surprise or promise something if it turns out to be harder than I think it will be. What ideas do you have? Let me know by leaving your feedback in the comments.

We hope this suite of free browser-based tools helps you become a more productive MySQL user and administrator!

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.

With Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is stored in the InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our example will be very straightforward: we’ll delete some rows from a table in order to recover the table later.

Most of these limitations are solved on Percona Server . More info about this in the conclusion section of this post. This post will be focus on how to recover a single tablespace using stock MySQL server.

First, you must meet certain prerequisites to be able to restore a ibd tablespace:

• The ibd file must be from a consistent backup with all insert buffer entries merged and have no uncommitted transactions in order to not be dependent of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server shutdown.
• You must not drop, truncate or alter the schema of the table after the backup has been taken.
• The variable innodb_file_per_table must be enabled.

Then, our first step is to get a consistent backup.

First we need to copy all the data to an output directory:

The –export option is the magic trick that will help us to get a consistent backup with complete independent ibd files without shutting down the service. In the second step the use of –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers.

# innobackupex --defaults-file=/etc/my.cnf --export /tmp/

Then apply the logs to get a consistent backup:

# innobackupex --defaults-file=/etc/my.cnf --apply-log --export /tmp/2012-01-22_14-13-20/

Now we’re going to delete some data from one table. In this case we’re going to delete the salary information from the user 10008:

mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

mysql> DELETE FROM salaries WHERE emp_no=10008;

The next step is where we are going to save a lot of time and some headaches Instead of recovering all the InnoDB data we are going to recover only the “salaries” table:

• Discard the tablespace of the salaries table:


mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries DISCARD TABLESPACE;

• Copy the salaries.ibd files from the backup to the database data directory:

# cp /tmp/2012-01-22_14-13-20/employees/salaries.ibd /var/lib/mysql/data/employees/

• Import the new tablespace:


mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;
mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

The salary history from the user is back again!

Conclusion:

As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are some prerequisites to comply.

Percona Server relaxes a lot of limitations and is able to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was
“exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table_from_xtrabackup=1 should be enabled for such advanced import process to work. You can read more about this feature in Percona Server Documentation

In the next blog post I’ll explain how to do recovery using Percona Data Recovery toolkit.

I am pleased to announce the schedule for Percona Live: MySQL Conference And Expo 2012 is now published. This is truly great selection of talks with something for MySQL Developers, DBAs, Managers, people just starting to use MySQL as well as looking for advanced topics. We have talks about running MySQL on extremely large scale in a Web as well as running MySQL In the Enterprise Environments. Some speakers have spent over decade pushing MySQL to its limits, others have in depth experience working on MySQL Code.

We have many talks which are covering Oracle MySQL, and forks such as MariaDB, Drizzle and Percona Server are well covered too. You will also have a chance to learn about commercial MySQL alternatives such as Clustrix and SchoonerSQL from our sponsors.

At the same time this is the conference for MySQL Community. We’re talking about other database systems only as it comes to migration to MySQL and about NoSQL technologies such as Memcached,Redis,Sphinx which are commonly used to supplement MySQL.

The space was very tight this year and competition was very tough. We had over 300 proposals for approximately 60 slots. As results committee had to make a lot of very tough choices and many great talks could not be accommodated.

We have a great Conference Committee this year who has done a great job getting the schedule together. I can’t thank them enough !

See you in April in Santa Clara for MySQL Conference and lets make this event an amazing success !

I spent last week at linux.conf.au in Ballarat, Victoria (that’s the Victoria in Australia, not wherever else there may be one) which is only a pleasant two hour drive from my home town of Melbourne (Australia, not Florida). I sent an email internally to our experts detailing bits of the conference that may interest them – and I thought that it may also interest our wider readers who are interested in all levels of the software stack.

For those that don’t know: linux.conf.au is one of (if not the) most awesome technical conference in the free software space. It consistently attracts a wide variety of very knowledgable speakers and a large number of attendees.

Every year it is put together by a (different) set of volunteers, and this means it also tours around the country (and sometimes even New Zealand). This year it was in Ballarat – a regional city a couple of hours drive out of Melbourne. One of the great things about LCA is that you are not always at the same hotel, in the same city stuck with the same two restaurants.

This year had a bit of an increased focus on privacy, security and basic freedoms and human rights. This is no doubt a reaction to the increased attacks on freedom of speech and the internet that have been going on in recent months.

That being said, there were a huge number of great talks on a variety of topics – everything from filesystem performance to open hardware, to repurposing existing hardware to upcoming challenges for the kernel to howto be a better sysadmin. In fact… for those who weren’t there and spend any of their life helping people admin machines – go and watch those talks.

linux.conf.au (for me) is one of the cannot-miss events in the year. It’s an opportunity to learn things that directly apply to my work, may apply in the future and most certainly will never apply but are rather cool anyway.

All the video from the conference are already up! This is an amazing effort from the (volunteer) AV team. I’ve included links to a selection of talks below that I especially think are worth watching:

Watch no matter what:

• Keynote – Karen Sandler
  This keynote was amazing. Go watch it. The organisers did a truly excellent job at selecting keynotes this year.
• Keynote – Jacob Appelbaum
  This is best described as a tour of internet freedom, the attacks on it and a tour of the modern surveillance state.
• UEFI and Linux: The future is here and it’s awful
  You will be depressed at some point in this talk – the news is not great for the future of even being able to easily boot free software on machines.
• Paul Fenwick’s Keynote
  A good quick introduction to hacking your brain. I’m sure many of you (like me) are interested in ways to hack our brains and our bodies to better serve us. This talk is merely an introduction. I also suggest you check out Anki if you want to improve your ability to remember things.
• Torturing OpenSSL
  This was certainly one of the most amazing talks I saw. A whole new interesting way of attacking SSL. Vary CPU voltage, extract private SSL keys! Wheee!
• The Kernel Report – Jonathan Corbet
  You can skip this only if you read every single mail on LKML, run your own analysis on the kernel source tree and publish an (at least) weekly publication on Linux.
  This is one of the few (err… only) talks that is repeatedly accepted into linux.conf.au. Why? Because Jon manages to compress a whole year of activity in the Linux world int oa single session that is incredibly informative.
• I Can’t Believe This Is Butter! A tour of BTRFS
  This is going to be the default filesystem in a number of places over the next few years, time to start learning! While it’s unlikely to be suitable for database workloads anywhere in the near future, I suspect we’ll see BTRFS as the root filesystem and XFS as the filesystem for the database server in the not too distant future.
• Mistakes Were Made
  This session explores a number of rather indispensable things for those in operations – but also leaks over into development. Learning from our mistakes can only make us better at doing our jobs.
• Hack Everything: repurposing everyday devices
  This was great fun – reuse existing hardware to do awesome things!

Talks that could be quite interesting for you, depending on your interests:

• XFS: Recent and Future Adventures
  This talk covered a lot of the recent improvements in XFS. Especially interesting for those with metadata heavy workloads.
• Bloat: How and Why UNIX Grew Up (and Out) – Rusty Russell,Matt Evans
  This is a pretty neat examination of where bloat comes from taking V6 UNIX utilities as the baseline. It would be interesting to have a similar study on the database servers we all love and use.
• Migrating to PHP 5.4
  Probably quite useful if you’re dealing with PHP (although I wasn’t in this session).
• Finding vulnerabilities in PHP code (via static code analysis)
  This one is for our web developers. It’s only a 20minute talk, so you should really all go and watch it. Various static analysis tools are part of the normal toolkit for C and C++ development these days, it’s great to see people working on them for other languages.

Talks I shall be watching the videos of as I was in another talk at the time:

• Smashing a square peg into a round hole
• Codec2: Open Source Speech Codec
• Desktop Home Hacks
• This Old Code, or Renovating Dusty Old Open Source For Fun and Profit
  This is especially interesting as I both know Greg and have talked to him about this work *and* because of my experience with the Drizzle project – we certainly have encountered more than our fair share of dusty old code during our time working on Drizzle.
• Mentoring: we’re Doing It Wrong

My Talk:

• Multi-tenancy, multi-master, sharding, scaling and analytics with Drizzle
  An update as to what we’ve been working on getting going in Drizzle.

All the videos are going up at:

• http://www.youtube.com/user/linuxconfau2012/videos

On the 25th of January at 10 am PST, I will present a webinar on preventing MySQL emergencies titled “Preventing Downtime in Production MySQL Servers”. The material I will present is based on in-depth research done by Percona across many production servers.  We analyzed more than 150 emergency cases and categorized our findings to help you learn ways to avoid production downtimes. Join us to learn more about why emergencies happen (it may be different than what you think) and what you can do to avoid them.

Sign up for the webinar now by visiting our webinar page [http://www.percona.com/webinars/2012-01-25-preventing-downtime-in-production-mysql-servers/].

Sometimes you might hear people talk about full backups, and differential backups versus incremental backups. What is the difference?

A full backup is pretty self-explanatory. It makes a copy of all of your MySQL data.

A differential backup, on the other hand, simply records the differences since your last full backup. The advantage of taking a differential backup is usually the space savings. Most databases have a lot of data that does not change from one backup to the next. Not copying this data into your backups can result in significantly smaller backups. In addition, depending on the backup tool used, a differential backup can be less labor-intensive for the server. If a differential backup does not have to scan all of the data to determine what has changed, the differential backup process can be significantly more efficient.

An incremental backup is a modification of a differential backup. An incremental backup records the changes since the last backup of any type, be it a differential or full backup. The advantages of incremental backups are similar to those of differential backups.

Here is an example that might explain this more clearly. Suppose that you take a full backup on Sunday. On Monday, you make a backup of all the changes since Sunday. This is a differential backup.

On Tuesday is when you begin to see the differences between the backup strategies. If you back up the changes since Sunday, then you have made a differential backup. If you back up the changes since Monday, it is an incremental backup.

Why would you choose an incremental versus a differential backup? That is a little bit out of scope for this blog post, because there are a lot of subtleties to consider. However, perhaps the biggest difference is in the way that you would restore a backup. Suppose that you need to restore your database on Friday. If you have taken differential backups all week long, you only need to restore Sunday, and then apply the changes that have happened since Sunday. If you have taken incremental backups, you must restore Sunday’s backup, and then apply changes repeatedly until you reach Friday. This can be more labor intensive, error-prone, and risky. It can also take longer.

Percona XtraBackup is capable of taking incremental backups, and when you specify the base backup as the previous incremental or full backup, you can easily make differential backups.

If you’ve missed Percona Live in Washington,DC and even if you did not you should be happy to know we have now published slides from majority of talks at this conference. Enjoy! This conference had great talks, whenever you’re Beginner or Advanced when it comes to MySQL. Also remember not to miss MySQL Conference in Santa Clara,CA April 10-12 2012.

This is about the second great feature – Multi-Master replication, what you get with Percona XtraDB Cluster.

It is recommended you get familiar with general architecture of the cluster, described on the previous post.

By Multi-Master I mean the ability to write to any node in your cluster and do not worry that eventually you get out-of-sync situation, as it regularly happens with regular MySQL replication if you imprudently write to the wrong server.

This is long-waited feature, I’ve seen growing demand for it for last two years or even more.
Percona XtraDB Cluster provides it, and let’s see how it works.

With our Cluster you can write to any node, and the Cluster guarantees consistency of writes. That is the write is either committed on all nodes or not committed at all.

Let me draw some diagram there. For the simplicity I will use a two-nodes example, but the same logic is applied when you have N nodes.

As you see all queries are executed locally on the node, and only on COMMIT there is special handling.
When you issue COMMIT, your transaction has to pass certification on all nodes. If it does not pass, you
will receive “ERROR” as response on your query. After that transaction is applied on the local node.

That is response time of COMMIT consist of several parts:

• Network roundtrip
• Certification time
• Local applying

Please note that applying the transaction on remote nodes does not affect the response time of COMMIT,
as it happens in the background after the response on certification.

The two important consequences of this architecture.

• First: we can have several appliers working in parallel. This gives us true parallel replication. Slave can have many parallel threads, and you can tune it by variable wsrep_slave_threads
• Second: There might be a small period of time when the slave is out-of-sync from master. This happens because the master may apply event faster than a slave. And if you do read from the slave, you may read data, that has not changes yet. You can see that from diagram. However you can change this behavior by using variable wsrep_causal_reads=ON. In this case the read on the slave will wait until event is applied (this however will increase the response time of the read. This gap between slave and master is the reason why this replication named “virtually synchronous replication”, not real “synchronous replication”

The described behavior of COMMIT also has the second serious implication.
If you run write transactions to two different nodes, the cluster will use an optimistic locking model.
That means a transaction will not check on possible locking conflicts during individual queries, but rather on the COMMIT stage. And you may get ERROR response on COMMIT. I am highlighting this, as this is one of incompatibilities with regular InnoDB, that you may experience. In InnoDB usually DEADLOCK and LOCK TIMEOUT errors happen in response on particular query, but not on COMMIT. Well, if you follow a good practice, you still check errors code after “COMMIT” query, but I saw many applications that do not do that.

So, if you plan to use Multi-Master capabilities of XtraDB Cluster, and run write transactions on several nodes, you may need to make sure you handle response on “COMMIT” query.