MySQL row_format compressed vs dynamic

Question

I've changed "innodb_file_format" from "Antelope" to "Barracuda" bcoz of following reasons.

1. To avoid row size limit
2. To avoid column index size limit

While doing file format change i chosen "row_format" as "dynamic". This is working fine.

But, i would like change "row_format" from "dynamic" to "compressed" for data compression. Could someone tell me

1. Is row_format have relation to COLUMN INDEXES and DATA INSERTS into tables? If yes, which is recommended and why?
2. Will compressed format leads to performance degradation?

Answer 1

Using DYNAMIC or COMPRESSED means that InnoDB stores varchar/text/blob fields that don't fit in the page completely off-page. But other than those columns, which then only count 20 bytes per column, the InnoDB row size limit has not changed; it's still limited to about 8000 bytes per row.

InnoDB only supports indexes of 767 bytes per column. You can raise this 3072 bytes by setting innodb_large_prefix=1 and using either DYNAMIC or COMPRESSED row format.

Using COMPRESSED row format does not make InnoDB support longer indexes.

Regarding performance, this is one of those cases where "it depends." Compression is generally a tradeoff between storage size and CPU load to compress and uncompress. It's true that this takes a bit more CPU to work with compressed data, but you have to keep in mind that database servers are typically waiting for I/O and have CPU resources to spare.

But not always -- if you do complex queries against data that is in the buffer pool, you may be constrained by CPU more than I/O. So it depends on many factors, like how well your data fits in RAM, the type of queries you run and how many queries per second, as well as hardware specs. Too many factors for anyone else to be able to answer for your application on your server. You'll just have to test it.

---

Re your comment:

One possibility is that the index is not fitting in the buffer pool. Performance degrades significantly if an index search needs to load pages and evict pages during every SELECT query. An EXPLAIN analysis can't tell you whether the index fits in the buffer pool.

I don't know how many columns or what data types of the columns in your index, but if you are indexing long varchar columns you should consider using prefix indexes (or decreasing the length of the columns).

You could also get more RAM and increase the size of the buffer pool.

Answer 2

COMPRESSED will compress data. Text will be compressed really well. I have several tables and used DYNAMIC before, moved to COMPRESSED.

I use MySQL 5.7

Table:

• id (int)
• some_other_id (int)
• text (longtext) - utf8mb4_unicode_ci ~500KB/row average
• updated_at (int)
• created_at (int)

It uses 80% less space with COMPRESSED compared to DYNAMIC. Before: 80Gb, after: 16Gb Huge save, while i don't need that data so much.

Other tables were not that dramatic, but it saved ~50% where there are some text fields. E.g. another from 6.4Gb -> 3.1Gb with 1.5M rows.

I haven't changed to COMPRESSED smaller tables that mostly saves Integers/Bit and similar. Those tables are already small in space so no need to use more CPU for them.