32

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
David542
  • 104,438
  • 178
  • 489
  • 842

2 Answers2

29

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.

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • 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? – David542 Jul 31 '12 at 20:55
  • 1
    Your buffer pool is 128M. Your InnoDBSize is 270M. So, raise `innodb_buffer_pool_size` to 300M. – RolandoMySQLDBA Jul 31 '12 at 20:59
  • I redid the query to do MB instead of Bytes. – RolandoMySQLDBA Jul 31 '12 at 21:00
  • Is that correct? For InnoDB it returns the size of the whole table (data+index) why? – Tomáš Fejfar Apr 26 '13 at 10:22
  • @TomášFejfar Yes it is correct because InnoDB caches data pages and index pages. Therefore, you have to express `data_length+index_length`. – RolandoMySQLDBA Apr 26 '13 at 22:18
  • Ouch, that's a lot of data :( – Tomáš Fejfar Apr 27 '13 at 15:02
  • 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? – KJ7LNW Jul 05 '23 at 18:30
  • @KJ7LNW 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. – RolandoMySQLDBA Jul 05 '23 at 19:03
3

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]

Michael Robinson
  • 29,278
  • 12
  • 104
  • 130
  • 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? – David542 Jul 31 '12 at 20:47