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
Is there a way to determine whether a mysql index fits entirely in available memory? If so, how would I:
Depends on Storage Engine
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
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.