11

I've been reading about MySQL's file formats Antelope and Barracuda a while ago, and I wonder if I could benefit with having Barracuda and Compression.

My server is currently using Antelope, as it is the default of MySQL.
I've had many times issues with memory due to the large database I have. My database is increasing every day.

It seems Compression is giving benefits to a few people, like:
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

I understand memory and disk space can be lower, but I'm not sure if I understand this (quoted from the article):
"~5% CPU load according to top (from 80-100% mostly waiting for I/O)
0.01 sec average lookup time by primary key (from 1-20 sec before the conversion)"

I thought these two things would NOT improve, because if the data is compressed, the server has to uncompress in order to get the original data again, so doesn't that make sense that CPU usage would increase?

Does that benefit you in read/write intensive applications? Would you recommend me to change to Barracuda and Compression?

Are you aware of any issues of Barracuda?
It seems the answer of the following question points a few issues, but since it's from 2011, I'd say they are fixed by now: https://serverfault.com/questions/258022/mysql-innodb-how-to-switch-to-barracuda-format

1 Answer 1

14

Regarding "Dynamic", the non-compressed Barracuda-only format, very little has changed from compact, mainly on how blobs (and any very dynamic fields) are stored. I have never had any issues with compact vs. dynamic, so I can safely recommend Barracuda's dynamic. Remember that Barracuda also supports old redundant and compact row formats.

The article you are mentioning is probably too old (5.1) and, as Peter Z., Percona's CEO, mentions on the comments it may be a bit misleading. That doesn't mean that compression can't be a huge gain depending on the workloads. However, I would recommend you to try it on versions >= 5.6, as both Facebook and Oracle have done lots of improvements about it.

As more recent reference materials, I would recommend you:

In particular, I like Facebook materials as they are third party (no need for an agenda) and they have one of the largest MySQL deployments in the world. As you can see they have had very successful setups combining SSD technology with compression.

Will it benefit you? That will depend on your workload, working set and setup (IOPS, memory). Depending if you are IO bound, CPU bound or memory bound, compression can affect negatively in some cases, by adding extra CPU, memory requirements (both compressed and uncompressed pages are stored on the InnoDB buffer pool) or generating too many compression failures, augmenting the latency. It also depends on the type of data: compression can help a lot with large text blobs, but it may be useless with already-compressed data.

In my experience, in practice, there are people for which compression was a the holy grail of performance and are very happy with it, but on other cases, we had to revert to uncompressed data as no gain was obtained. While a very heavy writing workload may seem like a bad environment for compression, if in your particular case you are not cpu-bound and memory-bound, but iops-bound it may be none-less helpful.

In general, it is very difficult to predict results, usually you should setup a test environment for benchmarking and then discover why you get better or worse results (and that way you can play with different block sizes, etc.). Barracuda is completely safe. Compression may or not be for you. And you can always experiment with other compression methods like client-side compression of blobs (for example, if you end up CPU-bound) or other 3rd party engines like RocksDB and TokuDB, in which compression is a big priority, as it is focused in performance for larger datasets than InnoDB can handle.

In a nutshell: Main reasons to use Barracuda are BLOB handling, innodb_large_prefix compatibility (large indexes) and compression. Dynamic, on MySQL 8.0 is now the default file format.

5
  • 1
    This is a really AWESOME and clear answer! It makes all the sense and is exactly the type of answer I desired. You are mentioning MySQL 5.6 (which is what I've upgraded to recently) and referring Facebook as an example, as I like, since they usually have to overcome the challenges before everyone else. Unfortunately it won't be easy to test this first, since a test environment won't have the same CPU/IO/RAM load as production, but indeed I'll have to give it a try! Thank you very much for your time.
    – Nuno
    Commented Jul 5, 2014 at 23:15
  • As row format can be chosen at table level, that can give you some flexibility for testing on production (after proper testing on a separate machine). This approach, however, will potentially make debugging and benchmarking more difficult.
    – jynus
    Commented Jul 5, 2014 at 23:17
  • Yup, I could probably try to convert a few tables first (maybe those that are not so large/used). However, for the big ones, that would require a few downtimes, rather than just 1 downtime where I would convert all of them at once. I'll have to see what's the best approach. I don't understand, however, why would it make debugging more difficult. What exactly do you mean here? Thank you very much.
    – Nuno
    Commented Jul 6, 2014 at 0:35
  • 1
    You have tools like pt-online-schema-change percona.com/doc/percona-toolkit/2.2/… to recreate tables in an online fashion. I just mentioned that mixing just some tables may make more difficult to measure cpu/memory/iops changes due to the engine change and differentiate them from normal load changes or due to caching changes when recreating it. It is also difficult to see on a different machine with different hardware, so just good luck!
    – jynus
    Commented Jul 6, 2014 at 1:23
  • On ZFS, it is not absolutely that is not good when using LZ4 on the file system.
    – user23642
    Commented Mar 11, 2015 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.