Personal blog of Yzmir Ramirez

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> 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> ALTER TABLE salaries IMPORT TABLESPACE;

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.

This week, Form and Validator were the most active components on the Symfony2 master branch. First, the CollectionValidator introduced new Optional and Required constraints. Second, the validation of form children was made configurable, meaning that child forms now aren’t validated anymore by default (bc-break change).

Symfony2 development highlights

Master branch:

• 6641f3e, bf59018, 509c7bf, e6e3da5:
  [Validator] added constraints Optional and Required for the CollectionValidator
• 0c70a41, 43293f4:
  [Form] made validation of form children configurable. Set the option cascade_validation to true if you need it
• 0c18324:
  [HttpKernel] added extra information when collecting data from an object if that object implements a __toString()
• 693d1ec:
  [Form] do not render default ID attribute on empty form name
• 15c666b, d2a0c74, 9e55cda:
  added a recover-timeout option to allow recovering messages that have taken too long to send
• 0b7e2e0:
  support for DELETE method in forms
• 0513eb1:
  [Form] pass translation domain to the sub-forms when choice list is expanded

2.0.x branch:

• cacc880:
  [Locale] fixed incomplete Locale data loading (sublocales returned only incomplete results for getDisplayCountries(), getDisplayLanguages() and getDisplayLocales())
• 959614b:
  use reflection to determine the correct path for component validation.xml file
• a1317c3:
  [Locale] fixed breaking tests if ‘intl’ extension is not installed
• 1f733e1:
  updated Serbian translation for validators

Repository summary: 3,787 watchers (#1 in PHP, #26 overall) and 973 forks (#1 in PHP, #11 overall).

New plugins

• asJqueryTreePropelManager: (no description)
• sfCpGrid: is a javascript based backend for Symfony. It loads the recordset from the db via ajax, can sort it on the fly, it checks periodically if there is something new, it allows filtering and search, and to have custom buttons and related actions.

Updated plugins

• sfGearman:
  • allow to namespaces function for mutualized gearmand
• apostrophe:
  • slight change in linkage on aImage placeholders
  • fixed bug with placeholder clicker box that looks for the choose button url in the correct place for both slots in areas and singleton slots
  • smart slideshow passes new ‘clickToSelect => false’ option to placeholder, which prevents a 404 if you click the placeholder area on a smart slideshow
  • factored out the slideshow slot code that relinks the related media items into a separate method so it can be easily called from overrides
• apostropheBlog:
  • slot help text doesn’t need to get output to logged out visitors
  • changed the check to editor privileges

They talked about us

• Create your own framework … on top of the Symfony2 Components (part 8)
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 2)
• Une semaine symfonique #263 – du 9 au 15 janvier 2012
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 3)
• Se publica Symfony 2.0.9
• Create your own framework … on top of the Symfony2 Components (part 9)
• Paris PHPCR meetup
• Symfony 2 – Veritabani Icin Baslangic Verileri Olusturmak – Doctrine Fixtures
• Deploying SF2 Projects on Plesk VPS.
• ??????? ??????????? ????????? ?? ?????? Symfony2. (????? 4)
• Create your own framework … on top of the Symfony2 Components (part 10)
• Les services, théorie et création
• ?????? ????????? Symfony2
• Intégrer facilement un blog à votre application Symfony – WordPress, Drupal, Joomla …
• Sortie de Symfony 2.0.9
• ???????????????? ????????? ? SonataAdminBundle
• Documentação do Symfony 2 em português
• Git and first Symfony2 project in NetBeans
• Symfony: short commands in terminal
• [symfony] javascript???????(use_javascript ? include_javascripts)
• Les services, utilisation poussée
• Drupal8 und Symfony2 gehen gemeinsame Wege!
• Symfony 1.4, 1.3 ?rlapok és ajax validáció
• Symfony2 & Mercurial
• Symfony2, best IDE support?
• Symfony meets APC (Alternative PHP Cache)
• rails 3.1 vs Symfony2 – my first comparison

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.

With the recent release of Percona XtraDB Cluster, I am increasingly being asked about MySQL’s semi-synchronous replication. I find that there are often a number of misconceptions about how semi-synchronous replication really works. I think it is very important to understand what guarantees you actually get with semi-synchronous replication, and what you don’t get.

The first thing to understand is that despite the name, semi-synchronous replication is still asynchronous. Semi-synchronous is actually a pretty bad name, because there is no strong coupling between a commit on the master and a commit on the replicas. To understand why, let’s look at what truly synchronous replication means. In truly synchronous replication, when you commit a transaction, the commit does not complete until all replicas have also committed successfully. In MySQL’s semi-synchronous replication, the commit completes before the transaction is even sent to any of the replicas. Therefore, by definition the transaction cannot have committed on any of the replicas. If there’s any problem after the commit happens on the master, it’s possible that the replicas won’t get the transaction, and even after they do, there’s no guarantee they can apply and commit it successfully themselves (duplicate key error, anyone?). If any of these problems happens, it’s too late–the commit is already permanent on the master, and can’t be rolled back.

What should semi-synchronous replication be called instead? I believe that it should be called delayed-acknowledgment commits, because this is what actually happens. When a transaction commits on the master, the commit proceeds as normal, and the transaction is sent to the replicas as normal, but the client connection to the master is not told that the commit has completed until after at least one replica has acknowledged receiving the transaction.

Another way to look at the same thing is that semi-synchronous replication actually forces the client to be synchronized, not the replicas. The client is forced to wait until the transaction has been sent to one of the replicas, but the commit on the master is not forced to wait at all, nor are replicas forced to do anything. The commit has already happened on the master, so the cat’s out of the bag and there’s no way to force replicas to do anything. As a result, the effect is that the client’s activity is throttled so that it cannot outpace the replica’s ability to fetch updates from the master. Have you seen the bumper sticker that says “don’t drive faster than your Guardian Angel can fly?” That is the effect of this throttling.

Semi-synchronous replication also does not guarantee that your replicas will not become delayed. The client connection is forced to wait until at least one of the replicas has retrieved the transaction, but not until the transaction has actually been applied to the replica. As you probably know, it is perfectly possible to send a very long transaction to the replica in a matter of milliseconds. The replica will take a long time to apply this transaction to its own data, and during that time, it will be delayed relative to the master. However, other transactions can continue committing and sending their changes to the replica, because the process of retrieving changes from the master and applying them run in separate threads on the replica.

Finally, semi-synchronous replication does not provide strong guarantees against data loss. What do I mean by a strong guarantee against data loss? I consider the safety of my data strongly guaranteed when at least one other server must have a copy of the data before it can be committed on the master. However, that is not what happens in semi-synchronous replication. And if there is an error in semi-synchronous replication, such as a crash at the wrong moment, or a timeout, then even the throttling is abandoned, and everything defaults back to the traditional mode of replication.

What does semi-synchronous replication guarantee me then? If there are no errors or timeouts, then the guarantee is essentially that only one transaction per client is likely to be lost if the master crashes.

I do not mean to sound negative, or to send the message that semi-synchronous replication is not useful. It is useful, but if you misunderstand it, you could be relying on a strong guarantee that is not actually provided.

If you want to learn more about this, then I encourage you to read the relevant section of the MySQL manual. But read carefully, for example, the following sentences:

When a commit returns successfully, it is known that the data exists in at least two places (on the master and at least one slave). If the master commits but a crash occurs while the master is waiting for acknowledgment from a slave, it is possible that the transaction may not have reached any slave.

Finally, I would be interested to hear how many people are actually running semi-synchronous replication in production. I have a feeling that very few people are, even though a lot of people seem to have heard about it. What are your experiences?