I currently have a (AWS) DB.M1.LARGE instance (7.5GB, 2vCPU, 40GiB SSD, MySQL 5.6.34) only 4GB of space are in use with less than 100 databases.
For some reason, i'm experiencing high InnoDB buffer usage, close to 98% and low Freeable Memory, less than 600MB.
The current value of innodb_buffer_pool_size is 5.7GB
After some internet research, i found this query to list all the index grouped and ordered by size.
select table_name as Table_Name, index_name as Index_Name,
count(*) as Page_Count, sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by table_name, index_name
order by Size_in_MB desc;
This is my result for the query, and i don't exactly understand why there are that amount of space use for NULL tables and NULL index, and if this is a problem and the reason why the Freeable Memory is that low.
This are the charts of the last 2 weeks from the RDS console
Update after new innodb_buffer_page query
Based in Bill's suggestion, i run this new query and here are the results:
select page_type, page_state, table_name, index_name,
count(*) as Page_Count, sum(data_size)/1024/1024 as Size_in_MB
from information_schema.innodb_buffer_page
group by page_type, page_state, table_name, index_name
order by Size_in_MB desc;
Top of query:
First part of the query result
Between this two captures, all the page_type are INDEX and all the page_state are FILE_PAGE
Is there a problem? When MySQL is running, it usually has the entire buffer_pool allocated.
5.7 buffer_pool
x.x various other tables and caches
y.y code (OS, MySQL, etc)
0.6 "freeable" memory
---
7.5 Total
The 600MB is under control of the OS, not MySQL. It is likely to be disk blocks that are cached in RAM. If they match what is on disk (ie, not "dirty"), then they are immediately reusable.
The 3.7GB you show in that table may be free blocks under InnoDB's control. If so, they will be reused when you do INSERTs, etc.
(Caveat: Much of this Answer is 'guessing', based on my experience.)