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.

About these ads

Tags: , , , , , , , , , , , ,

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: