We have a mysql box where i do rigorous scans/aggregations every few minutes (~ 35GB of data). Innodb buffer pool is ~ 1.5GB.
Off late i have started noticing a few latent queries, where the 'read innodb pages' metric has started to spike up.
Am speculating innodb buffer pool size is the culprit here. Which metric should i refer to confirm that my buffer pool is choking.
Thanks
I would use either of these two view to understand on how well is your buffer pool busy or not
innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table
pages : The total number of pages allocated for the table.
pages_hashed : The number of hashed pages allocated for the table.
pages_old : The number of old pages allocated for the table.
rows_cached : The number of cached rows for the table.
This view will tell how many pages are cached for your table and how many are old. If old pages increases - then there is lot of change in your DB for that specific table.
You usually do not expect all the pages of the table to in pages_hased - if you see large amount of most of amount of the pages - then your buffer pool is either less or there too many of data read from the tables.
So either way you need to increase.
But if the pages_hashed are not high or close to zero - then buffer pool is not really busy
You can actually display the stats from the performance_schema.global_status table
#
# mysqld Process Stats
#
SELECT
NOW() right_now,
(SELECT DATE_FORMAT(NOW() - INTERVAL variable_value SECOND,'%Y-%m-%d %H:%i:%S')
FROM performance_schema.global_status WHERE variable_name='Uptime') mysqld_running_since,
TRIM(REPLACE(CONCAT(
IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),
IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs '))),
IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),
IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),' ',' '))
mysqld_running_time
FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc
FROM (SELECT dy,hr,FLOOR((sec_aaa - dy86400 - hr3600)/60) mn,sec_aaa sec_aaaa
FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa
FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
FROM (SELECT variable_value sec_a FROM performance_schema.global_status
WHERE variable_name='Uptime') A) AA) AAA) AAAA) B;
InnoDB Buffer Pool Gathering of Stats
SELECT variable_value INTO @HOSTNAME FROM performance_schema.global_variables WHERE variable_name='hostname';
SELECT variable_value INTO @IBP_SIZE FROM performance_schema.global_variables WHERE variable_name='innodb_buffer_pool_size';
SELECT variable_value INTO @IBP_PAGES_DATA FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data';
SELECT variable_value INTO @IBP_PAGES_FREE FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_free';
SELECT variable_value INTO @IBP_PAGES_MISC FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_misc';
SELECT variable_value INTO @IBP_PAGES_TOTAL FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_total';
SELECT variable_value INTO @IBP_PAGE_SIZE FROM performance_schema.global_status WHERE variable_name='Innodb_page_size';
SET @IBP_PCT_DATA = 100.00 * @IBP_PAGES_DATA / @IBP_PAGES_TOTAL;
SET @IBP_PCT_FREE = 100.00 * @IBP_PAGES_FREE / @IBP_PAGES_TOTAL;
SET @IBP_PCT_MISC = 100.00 * @IBP_PAGES_MISC / @IBP_PAGES_TOTAL;
SET @IBP_PCT_FULL = 100.00 * (@IBP_PAGES_TOTAL - @IBP_PAGES_FREE) / @IBP_PAGES_TOTAL;
SET @padding = 27; SET @decimal_places = 5; SET @KB = 1024; SET @MB = POWER(1024,2); SET @GB = POWER(1024,3);
SELECT 'innodb_buffer_pool_size' as 'Option',LPAD(FORMAT(@IBP_SIZE,0),@padding,' ') Value
UNION SELECT 'innodb_buffer_pool_size GB',LPAD(FORMAT(@IBP_SIZE / @GB,3),@padding,' ');
SELECT 'Hostname' Status ,LPAD(@HOSTNAME,@padding,' ') Value
UNION SELECT 'Innodb_page_size' ,LPAD(FORMAT(@IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_data' ,LPAD(FORMAT(@IBP_PAGES_DATA ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_free' ,LPAD(FORMAT(@IBP_PAGES_FREE ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_misc' ,LPAD(FORMAT(@IBP_PAGES_MISC ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_data' ,LPAD(FORMAT(@IBP_PAGES_DATA * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_free' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_misc' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_data GB' ,LPAD(FORMAT(@IBP_PAGES_DATA * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free KB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free MB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free GB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free GB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc KB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc MB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc GB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_total GB' ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Percentage Data' ,LPAD(CONCAT(FORMAT(@IBP_PCT_DATA,2),' %'),@padding,' ')
UNION SELECT 'Percentage Free' ,LPAD(CONCAT(FORMAT(@IBP_PCT_FREE,2),' %'),@padding,' ')
UNION SELECT 'Percentage Misc' ,LPAD(CONCAT(FORMAT(@IBP_PCT_MISC,2),' %'),@padding,' ')
UNION SELECT 'Percentage Used' ,LPAD(CONCAT(FORMAT(@IBP_PCT_FULL,2),' %'),@padding,' ')
;
mysql> #
mysql> # mysqld Process Stats
mysql> #
mysql>
mysql> SELECT
-> NOW() right_now,
-> (SELECT DATE_FORMAT(NOW() - INTERVAL variable_value SECOND,'%Y-%m-%d %H:%i:%S')
-> FROM performance_schema.global_status WHERE variable_name='Uptime') mysqld_running_since,
-> TRIM(REPLACE(CONCAT(
-> IF(dy=0,'',IF(dy=1,'1 day ',CONCAT(dy,' days '))),
-> IF(hr=0,'',IF(hr=1,'1 hr ', CONCAT(hr,' hrs '))),
-> IF(mn=0,'',IF(mn=1,'1 min ',CONCAT(mn,' mins '))),
-> IF(sc=0,'',IF(sc=1,'1 sec ',CONCAT(sc,' secs ')))),' ',' '))
-> mysqld_running_time
-> FROM (SELECT dy,hr,mn,MOD(sec_aaaa,60) sc
-> FROM (SELECT dy,hr,FLOOR((sec_aaa - dy*86400 - hr*3600)/60) mn,sec_aaa sec_aaaa
-> FROM (SELECT dy,FLOOR((sec_aa - (dy*86400))/3600) hr,sec_aa sec_aaa
-> FROM (SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
-> FROM (SELECT variable_value sec_a FROM performance_schema.global_status
-> WHERE variable_name='Uptime') A) AA) AAA) AAAA) B;
+---------------------+----------------------+--------------------------------+
| right_now | mysqld_running_since | mysqld_running_time |
+---------------------+----------------------+--------------------------------+
| 2022-12-07 17:46:20 | 2022-11-17 00:02:28 | 20 days 17 hrs 43 mins 52 secs |
+---------------------+----------------------+--------------------------------+
1 row in set (0.00 sec)
mysql> #
mysql> # InnoDB Buffer Pool Gathering of Stats
mysql> #
mysql>
mysql> SELECT variable_value INTO @HOSTNAME FROM performance_schema.global_variables WHERE variable_name='hostname';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT variable_value INTO @IBP_SIZE FROM performance_schema.global_variables WHERE variable_name='innodb_buffer_pool_size';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT variable_value INTO @IBP_PAGES_DATA FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT variable_value INTO @IBP_PAGES_FREE FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_free';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT variable_value INTO @IBP_PAGES_MISC FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_misc';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT variable_value INTO @IBP_PAGES_TOTAL FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_total';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT variable_value INTO @IBP_PAGE_SIZE FROM performance_schema.global_status WHERE variable_name='Innodb_page_size';
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SET @IBP_PCT_DATA = 100.00 * @IBP_PAGES_DATA / @IBP_PAGES_TOTAL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @IBP_PCT_FREE = 100.00 * @IBP_PAGES_FREE / @IBP_PAGES_TOTAL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @IBP_PCT_MISC = 100.00 * @IBP_PAGES_MISC / @IBP_PAGES_TOTAL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @IBP_PCT_FULL = 100.00 * (@IBP_PAGES_TOTAL - @IBP_PAGES_FREE) / @IBP_PAGES_TOTAL;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SET @padding = 27; SET @decimal_places = 5; SET @KB = 1024; SET @MB = POWER(1024,2); SET @GB = POWER(1024,3);
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT 'innodb_buffer_pool_size' as 'Option',LPAD(FORMAT(@IBP_SIZE,0),@padding,' ') Value
-> UNION SELECT 'innodb_buffer_pool_size GB',LPAD(FORMAT(@IBP_SIZE / @GB,3),@padding,' ');
+----------------------------+-----------------------------+
| Option | Value |
+----------------------------+-----------------------------+
| innodb_buffer_pool_size | 49,928,994,816 |
| innodb_buffer_pool_size GB | 46.500 |
+----------------------------+-----------------------------+
2 rows in set (0.00 sec)
mysql> SELECT 'Hostname' Status ,LPAD(@HOSTNAME,@padding,' ') Value
-> UNION SELECT 'Innodb_page_size' ,LPAD(FORMAT(@IBP_PAGE_SIZE,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_pages_data' ,LPAD(FORMAT(@IBP_PAGES_DATA ,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_pages_free' ,LPAD(FORMAT(@IBP_PAGES_FREE ,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_pages_misc' ,LPAD(FORMAT(@IBP_PAGES_MISC ,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_pages_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_bytes_data' ,LPAD(FORMAT(@IBP_PAGES_DATA * @IBP_PAGE_SIZE,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_bytes_free' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_bytes_misc' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_bytes_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE,0),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_data GB' ,LPAD(FORMAT(@IBP_PAGES_DATA * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_free KB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_free MB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_free GB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_free GB' ,LPAD(FORMAT(@IBP_PAGES_FREE * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_misc KB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_misc MB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_misc GB' ,LPAD(FORMAT(@IBP_PAGES_MISC * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
-> UNION SELECT 'Innodb_buffer_pool_total GB' ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
-> UNION SELECT 'Percentage Data' ,LPAD(CONCAT(FORMAT(@IBP_PCT_DATA,2),' %'),@padding,' ')
-> UNION SELECT 'Percentage Free' ,LPAD(CONCAT(FORMAT(@IBP_PCT_FREE,2),' %'),@padding,' ')
-> UNION SELECT 'Percentage Misc' ,LPAD(CONCAT(FORMAT(@IBP_PCT_MISC,2),' %'),@padding,' ')
-> UNION SELECT 'Percentage Used' ,LPAD(CONCAT(FORMAT(@IBP_PCT_FULL,2),' %'),@padding,' ')
-> ;
+--------------------------------+-----------------------------+
| Status | Value |
+--------------------------------+-----------------------------+
| Hostname | some_hostname |
| Innodb_page_size | 16,384 |
| Innodb_buffer_pool_pages_data | 2,990,170 |
| Innodb_buffer_pool_pages_free | 12,290 |
| Innodb_buffer_pool_pages_misc | 44,592 |
| Innodb_buffer_pool_pages_total | 3,047,052 |
| Innodb_buffer_pool_bytes_data | 48,990,945,280 |
| Innodb_buffer_pool_bytes_free | 201,359,360 |
| Innodb_buffer_pool_bytes_misc | 730,595,328 |
| Innodb_buffer_pool_bytes_total | 49,922,899,968 |
| Innodb_buffer_pool_data GB | 45.62637 |
| Innodb_buffer_pool_free KB | 196,640.00000 |
| Innodb_buffer_pool_free MB | 192.03125 |
| Innodb_buffer_pool_free GB | 0.18753 |
| Innodb_buffer_pool_misc KB | 713,472.00000 |
| Innodb_buffer_pool_misc MB | 696.75000 |
| Innodb_buffer_pool_misc GB | 0.68042 |
| Innodb_buffer_pool_total GB | 46.49432 |
| Percentage Data | 98.13 % |
| Percentage Free | 0.40 % |
| Percentage Misc | 1.46 % |
| Percentage Used | 99.60 % |
+--------------------------------+-----------------------------+
22 rows in set (0.00 sec)
mysql>
For those running older versions of MySQL you can display the stats from the information_schema.global_status table
What's important here is to have the working set of data in the Buffer Pool
You should also make sure the Buffer Pool is writtent to disk and shutdown and startup. Please add these lines to the /etc/my.cnf (or DB Parameter Group is using RDS/Aurora)
[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
You can learn more from my Apr 18, 2014 post : Will redirecting queries to dev/null warm up indexes?