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
Best Answer
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_prefixcompatibility (large indexes) and compression. Dynamic, on MySQL 8.0 is now the default file format.