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.

Advertisement