Low Freeable memory and many NULL index using buffer pool in MySQL

Question

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.

Index list

This are the charts of the last 2 weeks from the RDS console

RDS monitoring charts

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

Second part of the query result

@bill-karwin I see that threat before, but there is no information about why i have that amount of NULL index's — Ricardo Albear, Jan 18 '19 at 15:03

Yoseph · Answer 1 · 2020-10-26T06:48:19.997

Regarding the NULL table_names, MySQL documents this here: https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-buffer-pool-tables.html

This query provides an approximate count of pages that contain system data by excluding pages where the TABLE_NAME value is either NULL or includes a slash / or period . in the table name, which indicates a user-defined table.

Edit also from Oracle: (this is complicated)

When table_name is NULL, it means that those pages are available for allocation. Are either free, or used by system. But always available when claimed. NULL is used for all buffer pool blocks that are not index pages or free.

Bill Karwin · Answer 2 · 2019-02-15T20:13:26.990

https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-page-table.html says:

TABLE_NAME

The name of the table the page belongs to. This column is applicable only to pages with a PAGE_TYPE value of INDEX.

The same applies to the INDEX_NAME column.

Try this query to get more information:

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;

I see in your screenshot that a lot of buffer pool pages of the type INDEX still show NULL for the table and index names.

I'm not sure what this means. I looked a the MySQL source, and it seems like it could leave those values as NULL if it can't find the index in the InnoDB data dictionary. But I'm not sure how that could happen.

https://github.com/mysql/mysql-server/blob/5.6/storage/innobase/handler/i_s.cc#L5007

Re comment from @C G-K:

Yes, you're right, I tested and I see a difference. I suppose the difference shows the fill ratio of the pages.

select page_type, page_state, table_name, index_name,
  count(*) as Page_Count,
  count(*) * @@innodb_page_size /1024/1024 AS Total_Page_Size_in_MB,
  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

+-------------------+------------+--------------+------------+------------+-----------------------+-------------+
| page_type         | page_state | table_name   | index_name | Page_Count | Total_Page_Size_in_MB | Size_in_MB  |
+-------------------+------------+--------------+------------+------------+-----------------------+-------------+
| INDEX             | FILE_PAGE  | `test`.`foo` | PRIMARY    |       1940 |           30.31250000 | 27.87315369 |

Thanks for your suggestion, i update my answer with the results of the new query — Ricardo Albear, Jan 18 '19 at 15:46
I've run across this advice before and I'm not sure how much sense it makes. Note: `sum(data_size)` is going to sum the underlying data referenced in the actual pages but _not_ the actual data taken up by the pages in memory. The innodb pages are quantized to 16KB, so correct count of the actual in use memory by the innodb_buffer_pool for those pages is to count the number of pages themselves*16384 in bytes. — C G-K, Feb 15 '19 at 13:44

score 0 · Accepted Answer · answered Jan 19 '19 at 22:53

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.)

Low Freeable memory and many NULL index using buffer pool in MySQL

3 Answers3