If you want to get some high level statistics on the buffer pool in MySQL / MariaDB you can use the INNODB_BUFFER_POOL_STATS table in the information_schema database.

If you need a little more detail then the INNODB_BUFFER_PAGE table is a good place to start. This view contains details about each page in the buffer pool. So we can use this to get database level details all the way down to individual indexes. Here’s a few queries to get you started…

Buffer pool consumption by database

SELECT bp.POOL_ID,
		NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name,
		(COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name
ORDER BY buffer_pool_consumption_gb DESC;

The ‘NULL’ database here will consist of various INNODB internal structures, for example the UNDO LOG. You can inspect these pages individually with..

SELECT *
FROM `INNODB_BUFFER_PAGE` bp
WHERE bp.TABLE_NAME IS NULL;

Buffer pool consumption by database/table & page type

SELECT bp.POOL_ID, NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name, bp.PAGE_TYPE, bp.TABLE_NAME, (COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name, bp.PAGE_TYPE, bp.TABLE_NAME
ORDER BY bp.POOL_ID, db_name, bp.PAGE_TYPE, bp.TABLE_NAME, buffer_pool_consumption_gb DESC;

Buffer pool consumption by index

SELECT bp.POOL_ID,
		NULLIF(SUBSTRING(bp.TABLE_NAME, 1, LOCATE(".", bp.TABLE_NAME) - 1), '') AS db_name,
		bp.TABLE_NAME,
		bp.INDEX_NAME, (COUNT(*) * 16) / 1024 / 1024 AS buffer_pool_consumption_gb
FROM `INNODB_BUFFER_PAGE` bp
GROUP BY bp.POOL_ID, db_name, bp.TABLE_NAME, bp.INDEX_NAME
ORDER BY bp.POOL_ID, db_name, bp.PAGE_TYPE, buffer_pool_consumption_gb DESC;