MySQL Performance Blog

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.

[read more...]

Dear Community,

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

Changelog:

• tar4ibd could not treat over 8GB file, now max 64GB
• prepare-speed-hack is added

Fixed bugs:

• Bug #386179: “InnoDB: Error: space id in fsp header 143088, but in the page header 0″
• Bug #394374: “Can’t compile with MySQL 5.1″
• Bug #394781: “xtrabackup requires write permissions on original ibdata1″
• Bug #395977: “xtrabackup 0.8 crashes during prepare after tar4ibd”
• Bug #396069: “hardcoded XTRABACKUP_VERSION”
• Bug #395407: “innobackupex reports “perl is too old””
• Bug #408966: “Unclear license terms”
• Bug #389360: “Partition files not being copied in innobackupex-1.5.1”
• Bug #394464: “batch scp copy of .frm, .MRG etc files”

The binary packages for RHEL4,5, Debian, FreeBSD, MacOS as well as source code of the XtraBackup is available on http://www.percona.com/mysql/xtrabackup/0.9/.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki.

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona.

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 logs  it would take an additional 20 hours ((12 * 0.9) + (12 * 0.75) = 19h48m).

If you wanted to do something like setup a new slave with a 24-hour old backup, and apply the binary logs continuously until it catches up, you will be waiting almost 5 days until that happens (each day has 24hr-19h48m = 4h12m “free” capacity, 19h48m/4h12m = 4.7 days).

So what are the solutions?
If you are using all InnoDB tables, an XtraBackup incremental backup should be much faster than using binary logs.  You can understand Vadim’s excitement when he announced this feature a few months ago.

If you are using multiple storage engines, then your options are to either try and time delay slaves (to keep them close to up to date), or hope that it’s not often that you need to restore!  Eventually this problem should be lessened by a MySQL Server feature – parallel execution on slaves.  Lets hope that it can get enough testers so that it makes it into a new release very quickly.

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 standard MySQL 5.4.1, it starts:

So you see it takes 1h 12 mins to start.

In XtraDB we made some improvements, and it shows:

1h 06mins. Better, but still not good.

So Yasufumi sat, scratched his head and made new patch for InnoDB. With this patch
mysqld starts:

6 mins 41 secs. 10x improvement. Something to think about.

The patch is not in official XtraDB tree yet, it lives in
http://bazaar.launchpad.net/~percona-dev/percona-xtradb/recovery-speed-hack/
tree.

The patch by itself is quite small, I would ask InnoDB experts to review it:

http://bazaar.launchpad.net/~percona-dev/percona-xtradb/recovery-speed-hack/revision/86

So your comments about patch is welcome! If it is stable it will go to XtraDB and XtraBackup.

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 .ibd tablespaces between servers.

The list of other features in the release includes:

• Support of ARCHIVE tables
• Addded incremental option to innobackupex-1.5.1 script
• Rollback XA PREPAREd transaction automatically at --prepare
• To extend tablespace size, if needed after --prepare
• Data page corruption check is added to local backup mode
• innobackupex can pass --export option
• Bug #389360: innobackupex-1.5.1 accepts scp options

Fixed bugs:

• Bug #388062: Update to innobackupex to include incremental
• fix error handling of tar stream

The binary packages for RHEL4,5, Debian, FreeBSD as well as source code of the XtraBackup is available on http://www.percona.com/mysql/xtrabackup/0.8/.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki.

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona.

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 project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

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.

We announce next beta version of our xtrabackup tool.

Changelist includes:

- set innodb_fast_shutdown=1 on xtrabackup shutdown
- set innodb_write_io_threads=10 by default for fast buffer_pool flushing during recovery
- build xtrabackup with --with-extra-charsets=complex
Bug #357653: innobackupex-1.5.1 --copy-back fails
Bug #358194: 2nd-phase is very slow
Bug #358266: xtrabackup-0.5 dies with SEGV on bad command line arguments
Bug #359341: innobackupex still does not read defaults-file

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu and tar.gz for Mac OS / Intel 64bit there:
http://www.percona.com/mysql/xtrabackup/0.6/.
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 project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

I am happy to announce next build of our backup tool. This version contains several bugfixes and introduces initial implementation of incremental backup.

Incremental backup works in next way. When you do regular backup, at the end of procedure you can see output:

which gives start point 1319:813219999 for further incremental backup. This point is LSN of last checkpoint operations. Now next time when you want only copy changed pages you can do:

and only changed pages (ones with LSN greater than given) will be copied to specified dir. You may have several incremental dir, and apply them one-by-one.

Current version does not allow to copy incremental changes to remote box or to stream, it is only local copy for now, but we are going to change it in next release. Beside putting last checkpoint LSN to output we also store it in xtrabackup_checkpoint file to use it in scripts.
More about incremental you can read on our draft page http://www.percona.com/docs/wiki/percona-xtrabackup:spec:incremental

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also), DEB for Debian/Ubuntu and tar.gz for Mac OS / Intel 64bit there:
http://www.percona.com/mysql/xtrabackup/0.5/.
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 project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona

We added some new features to our backup tool, namely:

• Now we can handle log file taken during backup with size exceeding 4GB, it may happen if backup takes too long and you have a lot of updates on InnoDB tables
• Progress meter during recovery step, now you can see what percentage of log file was handled

Also we did extensive testing and fixed bugs, so now we consider current state as feature complete and with beta-release stability.

You can download current binaries RPM for RHEL4 and RHEL5 (compatible with CentOS also) and DEB for Debian/Ubuntu there
http://www.percona.com/mysql/xtrabackup/0.4/.
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 project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona