2

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

Improve this question
3
  • innodb_buffer_pool_read_requests (needs a page) vs innodb_buffer_pool_reads (from disk)
    – danblack
    Commented Dec 7, 2022 at 6:03
  • Reading 35GB will clearly thrash a 1.5GB buffer_pool. Doing it every few minutes? Let's discuss Summary Tables to eliminate the need for the big scans. My point is that you may be able to use 1.5G much more efficiently, and not worry so much about "big scans".
    – Rick James
    Commented Dec 7, 2022 at 19:30
  • Or graduate to a column store instead of doing OLAP queries in MySQL. MySQL is an OLTP database, big scans and aggregations are not its strength.
    – Bill Karwin
    Commented Dec 7, 2022 at 19:49

2 Answers 2

Reset to default
2

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

Improve this answer
1

You can actually display the stats from the performance_schema.global_status table

SUGGESTED MySQL SCRIPT

#
# 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 - 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;

#
# 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,' ')
;

SAMPLE OUTPUT

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.