MySQL Enterprise Backup 3.10: Teasing compression.

Ok, so I wanted to look into the new compression options of MEB 3.10.

And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

So, these tests are solely about backup. I’ll do restore when I get some *more* time.

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp --read-threads=4 --write-threads=4 \
--process-threads=4 --limit-memory=300 backup

empty DB         1.2Gb        2.2Gb     2.6Gb

v3.9          0:14 min            2:37          4:26        6:04

v3.10        0:07                  2:34          4:03        5:52

So, with a bit more adjustments to use as much as possible, without compression, here’s testing 3.10:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --with-timestamp --read-threads=2 --write-threads=2 \
--process-threads=8 --limit-memory=1280 --number-of-buffers=80 backup

This takes 5:20 mins. 2685708 Kb backup size.

Now onto compression.

And I want to double check, let’s check how compression is in v3.9:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --backup-image=full_$BKUP_DATE.img --with-timestamp \
--read-threads=2 --write-threads=2 --process-threads=8 --limit-memory=1280 --number-of-buffers=80 \
--compress backup-to-image

6:31 min    867264 Kb (32% of original size, 68% compression)

Time for v3.10. Same options, just using the mysqlbackup binary in another location, /usr/local/meb310/bin/mysqlbackup:

4:28    Lz4 default compression. 1271720 Kb (47% of original size, 53% compression)

Ok, now let’s use 3.10 properly:

mysqlbackup --user=root --password=oracle --socket=/tmp/mysql5614.sock \
--backup-dir=/home/mysql/MEB/test --backup-image=full_$BKUP_DATE.img --with-timestamp \
--read-threads=2 --write-threads=2 --process-threads=8 --limit-memory=1280 --number-of-buffers=80 \
--compress --compress-level=1 --compress-method=lzma backup-to-image

3:43    469916 Kb (82.6% compression).

changing it slightly, with:

--compress-level=9 --compress-method=lzma

4:35    2512022 Kb (compression 6.33%)

Ok, so now I feel I have something useful, compress-level=1 and lzma for my environment is the best option, with it taking ~83% less disk, and at 1:37 min faster. This obviously will depend on the memory (number-of-buffers) available, and of course the processing power and core’s available. Not to mention that I expect lots of you to have much better IO rates than me, so, now it’s up to you to tease MEB compression…

Try it.. just for me.

This entry was posted in Backup, MEB, MySQL, MySQL Enterprise Backup, MySQL Enterprise Edition, Oracle, Recovery and tagged , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to MySQL Enterprise Backup 3.10: Teasing compression.

  1. Darwin says:

    Hi, do you still have a copy of MEB 3.10 x86_64 for Oracle Linux? If yes can you please give me a copy. Thank you.

    • Hi,
      Sorry for not replying sooner. I’m afraid MEB 3.10 has to be downloaded via, as part of the Enterprise Edition. If you can’t find it (under Patches) just open an SR and support can help you locate it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s