As I reported via Twitter late last week, we encountered an issue that got some of our mail delivery delayed by about a day and a half. I’ll explain more about what happened as I believe in openness on these matters, and also the experience has educational content for others.

Our mail server doesn’t have direct external interaction, it’s shielded by two relays that handle both the inbound MX and the outbound queue. This setup works remarkably well in terms of exposure to spam and other malicious activity. As previously discussed, it appears that it’s more difficult to make mail server infra more resilient without expending lots more time/effort and infrastructure expenditure. Just because of the way the common tools for mail delivery and imap are built, having two or more of each in a semi-active setup gets quite complex. Complexity is in itself a risk so it has to

Inspired by Baron's earlier post, here is one I hear quite frequently -

"If you enable innodb_file_per_table, each table is it's own .ibd file.  You can then relocate the heavy hit tables to a different location and create symlinks to the original location."

There are a few things wrong with this advice:

Percona XtraBackup 1.5-Beta is now available for download.

This release adds additional functionality to Percona XtraBackup 1.4, the current general availability version of XtraBackup.

This is a beta release.

Functionality Added or Changed

• Support for MySQL 5.5 databases has been implemented. (Yasufumi Kinoshita)
• XtraBackup can now be built from the MySQL 5.1.52, MySQL 5.5.7, or Percona Server 5.1.53-12 code bases (fixes bug #683507). (Alexey Kopytov)
• The program is now distributed as three separate binaries: 
  • xtrabackup - for use with Percona Server with the built-in InnoDB plugin
  • xtrabackup_51 - for use with MySQL 5.0 & 5.1 with built-in InnoDB
  • xtrabackup_55 - for use with MySQL 5.5 (this binary is not provided for the FreeBSD

Percona XtraBackup 1.4 is now available for download.

Version 1.4 fixes problems related to incremental backups. If you do incremental backups, it's strongly recommended that you upgrade to this release.

Functionality Added or Changed

• Incremental backups have changed and now allow the restoration of full backups containing certain rollback transactions that previously caused problems. Please see Preparing the Backups and the --apply-log-only option. (From innobackupex, the --redo-only option should be used). (Yasufumi Kinoshita)

Before I start a story about the data recovery case I worked on yesterday, here's a quick tip - having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this manually, at least once a month. No, seriously - in most of the recovery cases I worked on, customers did have some sort of backup, but it just wasn't working, complete and what not. Someone set it up and never bothered to check if it still works after a while.

Anyway, this post is not really about the backups but rather about few interesting things I learned during last recovery case.

First, some facts about the system and how data was lost:

• MySQL had a dedicated partition on XFS file system

I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30x10=5 hours to restore.  Right?  Wrong.

Mysqldump recovery time is not linear.  Bigger tables, or tables with more indexes will always take more time to restore.

If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:

Backup is 80G
Copy is at 70MB/s.
10G is already complete.
= ((80-10) * 1024)/70/60 = ~17 minutes


I can tell progress with mysqldump by monitoring the rate at which show global status like 'Handler_write'; increases and compare it to my knowledge of about how many rows are in each table.  But progress != a magic number like "17

A couple of weeks ago one of my colleagues and I worked on a data corruption case that reminded me that sometimes people make unsafe assumptions without knowing it. This one involved SAN snapshotting that was unsafe.

In a nutshell, the client used SAN block-level replication to maintain a standby/failover MySQL system, and there was a failover that didn't work; both the primary and fallback machine had identically corrupted data files. After running fsck on the replica, the InnoDB data files were entirely deleted.

When we arrived on the scene, there was a data directory with an 800+ GB data file, which we determined had been restored from a SAN snapshot. Accessing this file caused a number of errors, including warnings about accessing data outside of the partition boundaries. We were eventually able to coax the filesystem into

For a long time long types like BLOB, TEXT were not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs.

An InnoDB table is stored in a clustered index called PRIMARY. It must exist even if a user hasn't defined the primary index. The PRIMARY index pages are identified by 8-bytes number index_id. The highest 4 bytes are always 0, so index_id is often notated as o:<4 bytes number>, e.g. 0:258. The pages are ordered in a B-tree. Primary index is used as a key. Inside a page records are stored in a linked list.

InnoDB page by default is 16k. Obviously if a record is too long, a single page can't store it. If the total record size is less than UNIV_PAGE_SIZE/2 - 200 (this is roughly 7k) then the full record is stored in the page of PRIMARY index. Let's call it internal. In InnoDB sources they have type FIL_PAGE_INDEX*.

A week or so ago at the MySQL conference, I visited one of the backup vendors in the Expo Hall. I started to chat with them about their MySQL backup product. One of the representatives told me that their backup product uses FLUSH TABLES WITH READ LOCK, which he admitted takes a global lock on the whole database server. However, he proudly told me that it only takes a lock for "a couple of milliseconds." This is a harmful misconception that many backup vendors seem to hold dear.

The truth is, this command can take a lock for an indeterminate amount of time. It might complete in milliseconds on a test system in a laboratory, but I have seen it take an extremely long time on production systems, measured in many minutes, or potentially even hours. And during this time, the server will get completely blocked (not just read-only!) To understand why, let's

Dear Community,

The notice is hereby given that next version 1.2 of XtraBackup software is released.

The list of changes in this version includes:
Changelog:

• XtraBackup supports now XtraDB 10
• tar4ibd supports variable page size and fast_checksum of XtraDB
• Supported 32bit platform for Centos 5, Centos 4, Debian lenny and etch, Ubuntu Karmic, Jaunty, Intrepid, Hardy, FreeBSD

Fixeb bugs in this release:

• Bug #550776: "--use-memory" is not used at InnoDB-Plugin based version

The binary packages for as well as source code of the XtraBackup is available on http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.2/.

Debian and

If you ever wondered how big is that or another index in InnoDB ... you had to calculate it yourself by multiplying size of row (which I should add is harder in the case of a VARCHAR - since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes tend to take more space. So we added more detailed index statistics into our xtrabackup utility. The thanks for this feature goes to a well known Social Network who sponsored the development.

We chose to put this into xtrabackup for a couple of reasons - the first is that running statistics on your backup database does not need to hurt production servers, and the second reason is that running statistic on a stopped database is more accurate than with online (although online is also supported, but you may have inexact

We've written about replication slaves lagging behind masters before, but one of the other side effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup.  Let me make up some numbers for the purposes of this example:

• We have 2 Servers in a Master-Slave topology.
• The database size is 100 GB (same tables on each).
• The slave machine barely keeps up with the master (at 90% capacity during peak, 75% during offpeak)
• The peak window is 12 hours, the offpeak window is 12 hours.

Provided that the backup method was raw data files, it shouldn't take much more than 30 minutes to restore 100GB (50MB/s), but to replay one day of binary

Speed of InnoDB recovery is known and quite annoying problem. It was discussed many times, see:

http://bugs.mysql.com/bug.php?id=29847

http://dammit.lt/2008/10/26/innodb-crash-recovery/

This is problem when your InnoDB crashes, it may takes long time to start. Also it affects restoring from backup (both LVM and xtrabackup / innobackup)

In this is simple test, I do crash mysql during in-memory tpcc-mysql benchmark with 16 running threads.
MySQL params are:

So let's take

Dear community,

The release 0.8 of the opensource backup tool for InnoDB and XtraDB is available for download.

Key features:

• New mode of innobackupex --stream=tar4ibd; new command tar4ibd based on libtar-1.2.11
• Experimental option --export is added (see Vadim's post "Impossible - possible, moving InnoDB tables between servers"for details)

tar4ibd is made to be sure that read of InnoDB page is consistent. Before we had some complains what in stream mode some pages are getting corrupted, and we suspect tar can do read of pages in time when they changed. So we patches libtar to make read consistent.

Export is added to support moving

We announce next version of our xtrabackup tool and we consider it stable enough to put label RC on it.

Changelist includes:

- use O_DIRECT by default for handling InnoDB files
- use posix fadvise call to disable OS caching of copying files
- disable recovery of double buffer

Also we added binary builds for FreeBSD 7 64bit platform

You can download current binaries (64bit) RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu, tar.gz for Mac OS / Intel and .tar.gz for FreeBSD 7:
http://www.percona.com/mysql/xtrabackup/0.7/.
By the same link you can find general .tar.gz with binaries which can be run on any modern Linux distribution.
By the same link you can download source code if you do not want to deal with bazaar and Launchpad.

The

Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate to limit rate of IO per second. But it really works when you do local copy.
What about stream backup ? Even you copy just to remote box with
innobackupex --stream=tar | ssh remotebox "tar xfi -"
, read may be so intensive so your mysqld feels impact, slave getting behind, etc...

For this there is a nice small utility - pv .

With pv you run:
innobackupex --stream=tar | pv -q -L10m | ssh remotebox "tar xfi -"

and it will limit channel rate to 10 M per second.

Entry posted by Vadim | No