How to figure out if mysql index fits entirely in memory

Question

Is there a way to determine whether a mysql index fits entirely in available memory? If so, how would I:

• Determine size of mysql indexes
• Determine available memory for the application
• Determine if the indexes fit entirely in memory

Catarrhine

Answer 1

Depends on Storage Engine

MyISAM (Caches Index Pages From .MYI files)

SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB
FROM information_schema.tables WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','performance_schema','mysql');

Subtract that from key_buffer_size. If the answer > 0, then Yes

InnoDB (Caches Data and Index Pages)

SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB
FROM information_schema.tables WHERE engine='InnoDB';

Subtract that from innodb_buffer_pool_size. If the answer > 0, then Yes

I wrote about this in the DBA StackExchange

On a dedicated DB Server, make sure InnoDBSizeMB+IndexSizesMB does not exceed 75% of RAM.

Sessler

Thanks, does the following seem realistic? innodb_buffer_pool_size=134217728 - above-query=283164672 = -148946944. If this is the case, should I increase my innodb_buffer_pool_size, or what would be the next step here? – Catarrhine

Your buffer pool is 128M. Your InnoDBSize is 270M. So, raise innodb_buffer_pool_size to 300M. – Sessler

I redid the query to do MB instead of Bytes. – Sessler

Is that correct? For InnoDB it returns the size of the whole table (data+index) why? – Overgrowth

@TomášFejfar Yes it is correct because InnoDB caches data pages and index pages. Therefore, you have to express data_length+index_length. – Sessler

Ouch, that's a lot of data :( – Overgrowth

The query for InnoDB appears to count data and indexes, but if you only want to know if indexes fit, can you just check SUM(index_length)? Obviously there are data pages cached, so it will depend on how hot your data is to increase that size to cache enough indexes and data pages, so the number needs to be bigger by some percentage depending on workload...but does it seem reasonable just to SUM(index_length) and add to that some application-specific additional room for data? – Demandant

@Demandant The Buffer Pool is designed to accommodate data and index pages. You cannot segregate them and measure them independently since they share one buffer. Even if you plan to use covering indexes extensively, the available Buffer Pool is still fair game for data pages and index pages alike. – Sessler

Answer 2

To find memory available to MySQL, look in my.cnf, likely located at: /etc/mysql/my.cnf

key_buffer_size = 264M

To find size of indexes for a table: SHOW TABLE status FROM [DBNAME]

Gnosis

In my my.cnf file I do not have any options related to memory defined. What is the specific option here that would need to be defined? – Catarrhine