InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on memory usage. The article on compression internals is just stating:
a configuration with more memory dedicated to the InnoDB buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained.
Which translates to "more is better than less" for me – pretty useless in most scenarios.
So in a non-CPU-bound workload where the buffer pool size is significantly smaller than the database size but "meets the needs" (i.e. has an acceptable hit rate), will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?
The aforementioned compression internals article is elaborating that both, the compressed as well as the uncompressed versions of a page need to be present in memory for the page to be available to the engine, thus potentially increasing memory usage. On the other hand, the eviction algorithm seems to first evict uncompressed pages, leaving the compressed versions in the pool and offering the advantage that more pages might fit into the buffer pool if the system would be able to keep up with decompressing and re-evicting pages.
Best Answer
I addressed this issue 1.5 years ago : innodb_file_format Barracuda
Just from reading the documentation, I was able to figure out mathematically that the smaller you make the key_block_size, the less extra space you need to add to the InnoDB Buffer Pool.
Here is the chart I made up in that earlier post
key_block_size=8
8
is50.00%
of16
50.00%
of8G
is4G
innodb_buffer_pool_size
to12G
(8G
+4G
)key_block_size=4
4
is25.00%
of16
25.00%
of8G
is2G
innodb_buffer_pool_size
to10G
(8G
+2G
)key_block_size=2
2
is12.50%
of16
12.50%
of8G
is1G
innodb_buffer_pool_size
to9G
(8G
+1G
)key_block_size=1
1
is06.25%
of16
06.25%
of8G
is0.5G
(512M
)innodb_buffer_pool_size
to8704M
(8G
(8192M
) +512M
)Given the following
The ideal size for the Buffer Pool in a perfect world would be
From this formula, less is more. The smaller KBS is, the smaller the increase in BPS needs to be. Additoinally, it would appear that the CPU would worker a little harder in response to tighter compression. How ?
I can see the CPU working harder to introduce more compressed pages with tighter compression. This could result in evicting LRU compressed pages more often.
If this is the case, it would appear that making the BPS even bigger than the suggested values from these formulas would give CPU some relief as well as reduce the rate of the eviction of compressed pages from the Buffer Pool.
EXAMPLE: From my first chart, an 8GB Buffer Pool would need to be 12GB for key_block_size=8. Making the Buffer Pool larger than 12GB would relieve the CPU of a lot of this heavy lifting (in the form of evicting uncompressed pages). Perhaps doubling the Buffer Pool Size may be need to be explored.
As for your question
I would say it would hurt, no such much because of the hit rate against the compressed pages, but because of the additional house cleaning in terms of the management of uncompressed pages and the CPU usage required for it. This would lower InnoDB performance no matter how much tuning the rest of the Storage Engine gets.