Taste test: Innobackup vs. Xtrabackup

Oct 1, 2009 / By Sheeri Cabral

Tags: ,

Firstly, I have to thank my co-workers Singer Wang and Gerry Narvaja for doing a lot of the work that resulted in this comparison.

After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume.

Xtrabackup:

    • Free

 

    • takes 1.1% longer (2 min during a 3 hour backup)

 

    • uses 1.4% more space (1G more in a 70G backup — this was for uncompressed backups)

 

    • uses 1.115% more cpu overall

 

  • split as 0.12% user, 0.66% nice, 0.025% system, 0.31% more iowait, 0% more steal


InnoDB Hot Backup:

  • Commercial

 

  • slightly fewer resources (see xtrabackup)

 

 

The server has 32G of RAM, 8 Dual-Core AMD Opteron(tm) Processor 8220, with a speed of 2800 Mhz (2.734 Ghz) and a cache size of 1024 Kb, and is also used as a reporting server. We ran Xtrabackup on odd days, and InnoDB hot backup on even days. Both are stable and have responsive developers, and both projects have developers that know the internals of MySQL.

The clients of ours that use InnoDB Hot Backup (including the client this test was run on) already have purchased indefinite licenses for it. For those clients, there is not a big reason to switch to Xtrabackup.

For companies that are not using a hot backup solution yet, we recommend both InnoDB Hot Backup and Xtrabackup, and they end up choosing what best fits their environment. Some companies shun InnoDB Hot Backup because of its price tag. Some companies are wary of Xtrabackup because it has been less than a year since the release was announced, and feel better paying money to a company for their backup solution. I did not put the “feelings” in the lists above, because they are dependent on the environment — I have the same trust in the skill and commitment of the developers of Xtrabackup and the developers of InnoDB Hot Backup. Other such feelings might be “supporting open source” or “using the backup software that is developed by a partner organization.”

We felt it was easier to set up InnoDB Hot Backup than Xtrabackup, but that may be because we have been using InnoDB Hot Backup for years. However, neither setup is too complex — it is not necessary to have a senior-level DBA set up the backups, no matter which software you choose.

17 Responses to “Taste test: Innobackup vs. Xtrabackup”

  • Sheeri Cabral says:

    (I guess the result of the taste test is that they taste the same!)

  • VadimTk says:

    Sheeri,

    Thanks you for test and mentioning XtraBackup.

    If you allow me, I would add two features in XtraBackup which make big difference

    1) Direct copy on remote host
    2) Ability to restore only specified table

    And also you can compare recovery time,
    XtraBackup ( v 0.9+) should be in times faster here.

  • Sheeri Cabral says:

    Vadim — This wasn’t a feature comparison, it was a resource comparison. We specifically had a request to test if there was any difference in resource usage between the two, so I thought I should make the numbers available to everyone.

    I’ll see what we have in terms of recovery time — for this particular client we have automated restoring a production backup to a development server, so we may actually have some log information.

    I’m sure there are plenty of places people can go to for feature lists of your software and for InnoDB Hot Backup; that wasn’t the point of this post.

  • gillian says:

    I do like the streaming ability of Xtrabackup for sure, and wish InnoDB Hot Backup had that built in.

  • Sheeri Cabral says:

    Gillian — this is not a post about the features of Xtrabackup, as I mentioned before.

  • gillian says:

    Sure. Please delete my comment.

  • mjiang says:

    If you copy big files off the system(Linux) on production server, then even with swappness=0 it is still going to flush the cache. xtrabackup has the fix, but I do not think innodb hotbackup has it.

  • Mark Callaghan says:

    The previous comment refers to the use of POSIX fadvise calls by Xtrabackup to avoid wiping the OS buffer cache when copying files as described here — http://www.mysqlperformanceblog.com/2009/05/21/xtrabackup-07-rc. If you also use MyISAM or run things other than MySQL on the same host, this can be important.

    The perpetual license for ibbackup — http://www.innodb.com/products/hot-backup/order/ — means you can use the binary that you have forever and get upgrades for 3 years. The current version of ibbackup doesn’t support the Barracuda file format that is new in the InnoDB 5.1 plugin. Customers who want to do hot backup when using compressed InnoDB tables with Barracuda will have to purchase new licenses for ibbackup if they are beyond the 3 year upgrade limit when support for Barracuda is released in ibbackup.

    Does the binary ABI for RHEL/CentOS allow a static binary to work on both RHEL/CentOS versions 4 and 5? I am curious about how long a binary can be used as OS versions are upgraded. I know Solaris is great about that.

  • Sheeri Cabral says:

    Mark — I don’t know about how many versions the binary will work on, but I guess it depends on what changes between the versions — it’s really an OS thing.

    I do know that I’ve been able to get binaries for different operating systems when companies with perpetual licenses migrate their systems (for free).

  • Sheeri Cabral says:

    We didn’t see any impact other than the 1% or so reported in the blog post. We are using xtrabackup Ver 0.8.1rc Rev 78, so it seems like the lack of OS cache flushing isn’t making a difference (in our case).

    mysql> select count(*), sum(data_length+index_length)/1024/1024/1024 as sizeGb, engine from tables where table_schema not in (‘mysql’,’information_schema’) group by engine;
    +———-+——————+——–+
    | count(*) | sizeGb | engine |
    +———-+——————+——–+
    | 1 | NULL | NULL |
    | 555 | 302.903549194336 | InnoDB |
    | 86 | 20.829088966362 | MyISAM |
    +———-+——————+——–+
    3 rows in set (23.77 sec)

    We’re using plenty of MyISAM tables and InnoDB tables (the NULL table is a view).

  • Mark Callaghan says:

    The use of POSIX fadvise should benefit MyISAM queries, not the backup time. So the difference would be measured in query response time and iostat results measured during the backup window.

  • Mark Callaghan says:

    I am not a Linux kernel hacker nor an expert on the Linux VM, but I can share a bit more background from my peers who submitted the patch to use POSIX fadvise. vmstat showed too much activity for the si and/or so columns when hot backups were done. The theory is that recently read data from the files copied during hot backup pushed other pages out of physical memory and written to the swap space on disk. The fear is that this included pages from the InnoDB buffer cache. So even though O_DIRECT was used, scanning large files had an impact on InnoDB performance. When xtrabackup was changed to use POSIX fadvise, swap activity decreased as listed in vmstat and query response time improved.

  • Sheeri Cabral says:

    Mark, thanx for that. We compared iostat and swap activity and found that xtrabackup was (slightly) more resource intensive for a (slightly) shorter period of time than InnoDB Hot Backup.

    For example, there were about 2,000 more transfers per second using xtrabackup, and the heavy I/O dropped off 20 minutes sooner.

    With swapping, sar reported pswapin/sec as slightly higher for xtrabackup (0.04 vs 0.03) and the swapping dropped off 15 minutes sooner.

    The query response time was actually worse with xtrabackup, but only slightly so. This is likely due to xtrabackup using more resources than InnoDB Hot backup — since it only uses slightly more resources, the query response time is only slightly worse. This is a heavily used server, and the degradation isn’t affecting the user experience (partly because the hot backups are done during a low activity time).

  • si says:

    xtrabackup not working with innodb plugin. with xtrabackup + innodb plugin you can lost you data.

  • Sheeri Cabral says:

    si — you’ll have to file a bug with xtrabackup for that, I can’t confirm that and I don’t want people to think this is true when it may be only on your system, or the bug may already be fixed.

  • Massimo says:

    Hi at all,
    someone know it’s possible to backup stored procedure with innobackupex when I use –database=only_one_db

  • [...] had a chance to test this myself yet, so I’ll trust what I’ve read on pythian.com: Taste test: Innobackup vs. Xtrabackup.Here are the main features available with the current release, from the home page:- Create hot [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>