Mysql – Trying to fit indexes in memory: partitioning vs compression or neither

MySQL

I have a "transaction" table that grows at about 150k rows-per-day rate. 97% of user aggregate queries refer to the last 3 months of data and must run very efficiently. Trimming older transaction data is not an option as I need to make it available going back 12 years. /Splitting "recent" from "old" transactions into separate tables is an option, but I'm trying to avoid this complication if possible./

Would you recommend table partitioning to help keep indexes in memory? Currently, the table has bigint primary key, so if I go with partitioning by date-range, I'd have to add datetime to the PK, which would double PK's memory footprint — feels kind of counter productive.

Or, perhaps table compression would help better? Specifically, MySQL 5.7 permits page-level compression for InnoDB tables that reside in file-per-table tablespaces (mine do).

Increasing system memory is not an option for a while as I'm pinned to an AWS reserved instance for time being.

Best Answer

Indexes and data are stored in pages. Buffer pool holds the "hottest" pages meaning that if you only access one quarter of your table frequently mostly the "hot" part of your table is going to be the buffer pool because an LRU list manages which page(s) should be evicted at any given time.

Partitioning is a good alternative which also helps data cleanup (simply dropping a partition) but yes as you said it comes with some restriction.

If you have auto increment primary key you can also set up ranges on that. Since you have quite good estimation on how many rows you have inserted / day it should be easy to calculate ranges which translates nicely into date ranges.

Experiment with the setup and see if it does improve the performance.

InnoDB compresses on page level but there are many gotchas there:

1) you should check first if your dataset is compressible enough to have benefit. InnoDB user KEY_BLOCK_SIZE which means if it can compress below this than it will otherwise won't.

2) To minimize disk IO both compressed and uncompressed version of the page is stored in the buffer pool meaning that with assuming at least 50% compressability you sacrifice 33% of your buffer pool on the altar of compression which may become an issue. This can be improved with better compression rates. For example with key_block_size=4 only 20% but that means every page has to be minimum 1:4 compressable.

From http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html:

Thus, at any given time, the buffer pool might contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

A good explanation of the scenario is available in this answer: https://serverfault.com/questions/358444/setting-mysql-innodb-compression-key-block-size

3) Table compression can also lead to serious mutex contention issues. For more details: https://www.percona.com/blog/2011/05/20/innodb-compression-woes/

Therefore I wouldn't go for compression unless you have big varchar columns that you want to have indexed. You can compress either in application level or on the filesystem both has their own benefits and both are much more efficient than InnoDB compression.

Related Solutions

Mysql – InnoDB compression: memory usage

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

You have a DB Server with a 8G Buffer Pool
You ran compression with key_block_size=8
- 8 is 50.00% of 16
- 50.00% of 8G is 4G
- raise innodb_buffer_pool_size to 12G (8G + 4G)
You ran compression with key_block_size=4
- 4 is 25.00% of 16
- 25.00% of 8G is 2G
- raise innodb_buffer_pool_size to 10G (8G + 2G)
You ran compression with key_block_size=2
- 2 is 12.50% of 16
- 12.50% of 8G is 1G
- raise innodb_buffer_pool_size to 9G (8G + 1G)
You ran compression with key_block_size=1
- 1 is 06.25% of 16
- 06.25% of 8G is 0.5G (512M)
- raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

Given the following

BPS = Buffer Pool Size
KBS = key_block_size
BPSFC = Buffer Pool Size For Compression

The ideal size for the Buffer Pool in a perfect world would be

BPSFC = BPS * (16 + KBS) / 16

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 ?

For KBS=8, evicting one uncompressed page leaves room for 02 compressed pages
For KBS=4, evicting one uncompressed page leaves room for 04 compressed pages
For KBS=2, evicting one uncompressed page leaves room for 08 compressed pages
For KBS=1, evicting one uncompressed page leaves room for 16 compressed pages
For KBS=X, evicting one uncompressed page leaves room for 16/X compressed pages

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

will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?

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.

Mysql – InnoDB Index Doesn’t Fit in Memory

SUGGESTION #1

Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings:

innodb_buffer_pool_instances=2 # If your Server is DualCore
innodb_buffer_pool_instances=4 # If your Server is QuadCore
innodb_buffer_pool_instances=6 # If your Server is HexaCore
innodb_buffer_pool_size=90G

I addressed InnoDB and Swapping in one of my older posts (How do you tune MySQL for a heavy InnoDB workload?). I referred to Jeremy Cole's blog The MySQL “swap insanity” problem and the effects of the NUMA architecture. You might need to increase innodb_buffer_pool_size to 60%-75% of RAM. In your case, I would go with 90G (70.3125%).

SUGGESTION #2

For more CPU engagement I recommend these

innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_thread_concurrency=0

I discuss why in my old posts

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?

OTHER SUGGESTIONS

You might more RAM on the DB Server (Setting innodb_buffer_pool_size to 60-75% of RAM)
You might need to tune your queries if you read lots of unnecessary data as indicated by the Innodb_buffer_pool_read_ahead_evicted status variable.