阿里云的RDS内存之谜

海鲨数据库架构师 2023-08-16

277

起初公司的生产库RDS 是8核 16GB的 MYSQL 5.7
那么16GB内存应该,或许没细想地认为是全部给MYSQL 使用.也许是大部分内存给MYSQL 使用.

有一天感觉不对劲, 我们的订单保留六个月,超过六个月以前的订单会迁移到历史库. 当然这是人工迁移的,大约没半年一次.不一定很准时去迁移. 目前,就现在8月份存在去年10份到今年8月份全部数据了.按理说应该把去年3个月的数据迁移走.

这是发现大概率地发现慢SQL 查询近期6个月,其中一个月数据非常慢,其实也不很慢,就在慢SQL日志里躺着.拿出来分析下,已经优化过的,再跑一次就非常快. 说明一个道理要读的数据大部分在磁盘上.

不过本来不应该的事情,以前也不会发生的,过去好好的?
这句话经常是开发质疑DBA的常用武器!

近两年来订单量都稳步保持一个水平, 额! 这样就很难找数据积累太多的借口去搪塞上面的质疑!
第二借口是虽然订单保持稳定,可功能增加了不少,更多的SQL要查数据....

无法编下去了. 再多功能SQL也是查近2个月的订单数据,也没有增加太多的新的大表.

那么我们得研究研究数据在磁盘上,说明被挤出去的,只所以被挤出去,那么说明它是冷数据,在MYSQL OLD区里. 数据库应该说无论是ORACLE还是MYSQL 都不会自动去把冷数据给清理出内存里.

自然被挤出去,说明内存不够用.

问了MYSQL 大佬说

show engine innodb status\G;
FREE BUFFERS 为 0 就表示内存不够

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 9951510528
Dictionary memory allocated 10533435
Buffer pool size   589824
Free buffers       8192
Database pages     581632
Old database pages 214544
Modified db pages  40152
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0, flush chunk 0
Pages made young 7287831, not young 2185870604
0.00 youngs/s, 0.00 non-youngs/s
Pages read 25099935, created 12262044, written 43134361
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 581632, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]


复制

可总是 8192

二 RDS 内存分配

RDS实例为云盘版独享规格，默认InnoDB Buffer Pool =
（
RDS
实例规格内存
- RDS
系统预留内存）
* 0.75
。

云盘版独享规格中的RDS系统预留内存计算公式如下（单位为MB）：

MIN(RDS实例规格内存/2,2048)+MAX(RDS实例规格CPU*64, RDS实例规格内存/64)+(RDS实例规格内存/64)+285

MIN(16384/2,2048)+MAX(8*64,16384/64)+( 16384/64)+285

2048+512+256+285=3101MB

(16GB*1024-3101)*0.75=9962MB

为方便您设置，下表中提供了云盘版独享规格对应的默认Buffer Pool和推荐最大Buffer Pool。

实例CPU核数	实例内存大小（单位：MB）	默认Buffer Pool（单位：MB）	推荐最大Buffer Pool（单位：MB）
4	16384	9216	10240
4	32768	21504	22528

16GB中有3101MB 给系统使用,通过公式计算 BUF池应该有9962MB
其它0.25 约3320MB 内存给了链接内存和服务层的内存.

9962MB和9216还差了750MB

show engine innodb status 显示
Total large memory allocated 9951510528

为9490.5MB MYSQL 8 取消了 Total large memory 始终为0.

这3个数那个是真的呢?

阿里还有个公式如下:

根据公式

实际BP size和设定值可能会不同，BP size 始终会圆整为chunk_size * instances的整数倍。我看了下您这个是等于16GB的，所以没有预留内存。也就是说如果RDS云盘实例规格内存 ≥ 16 GB，默认InnoDB Buffer Pool = RDS实例规格内存 * 0.75。得出的值是128的96倍

这句话比较绕,大意是BUFFER POOL 是按128MB进行分配的.同时MYSQL有8个实例.

错了错了应该说是把BUFFER POOL 分成了8个子池.

9962MB 内存要分配给BUF 池,就按128MB 平均分给8个子池,每个池要一样.

128*8=1024MB 那么1024MB的9倍就是9216MB 与9962很近!

虽然多了746MB 可无法公平分给8个子池.那么这内存就给了服务层.

show engine innodb 显示分配了9490MB 与9216MB 多了

Buffer pool size 589824 <==这里才是 9216MB 页*16K

9490-9216=274MB 除了数据字典占用10MB 外其它的听八怪高鹏说是页管理结构占用了.当时分析下确实是,现在没有留下文档. 下图最终BUFPOOL内存递减图

空闲池它是始终保持在128MB附近,据说一但低于128MB MYSQL就把冷数据给挤出内存.

最终16GB内存可怜粑粑只有9088MB

SELECT POOL_ID,POOL_SIZE,FREE_BUFFERS,DATABASE_PAGES,OLD_DATABASE_PAGES 
FROM information_schema.INNODB_BUFFER_POOL_STATS;
复制

通过BUF POOL 状态看每个子池和大小.

另外看 information_schema.innodb_buffer_page 看缓存了什么东西在里面和大小,其中大小SIZE有可能错误,这个各位自己矫正下

1 按数据库看

select 
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else left(TABLE_NAME,instr(TABLE_NAME,'.')-1) end ) AS DATABASES_NAME,IS_OLD,
SUM(PAGE_NUMBER) AS PAGE_NUM,SUM(NUMBER_RECORDS) AS CACHE_RECORDS,ROUND(SUM(DATA_SIZE)/1024/1024,2) AS MEM_SIZE_MB 
from information_schema.innodb_buffer_page
WHERE (TABLE_NAME IS NOT NULL OR INDEX_NAME IS NOT NULL)
GROUP BY DATABASES_NAME,IS_OLD;
复制

2 查看特定库各个表占用

--3 DATABASE TABLE
SELECT DATABASES_NAME,TABLE_NAMES,IS_OLD,SUM(PAGE_NUMBER) AS PAGE_NUM,SUM(NUMBER_RECORDS) AS CACHE_RECORDS,ROUND(SUM(DATA_SIZE)/1024/1024,2) AS MEM_SIZE_MB
FROM (
select TABLE_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else left(TABLE_NAME,instr(TABLE_NAME,'.')-1) end ) AS DATABASES_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME when instr(TABLE_NAME,'/*') = 0 then substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,length(TABLE_NAME)-1) else substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,(instr(TABLE_NAME,'/*')-instr(TABLE_NAME,'.')-1) ) end ) AS TABLE_NAMES ,
IS_OLD,PAGE_NUMBER,NUMBER_RECORDS,DATA_SIZE
from information_schema.innodb_buffer_page
WHERE TABLE_NAME like '%sharkdb%'
) T
GROUP BY DATABASES_NAME,TABLE_NAMES,IS_OLD;
复制

3 查看分区表占用

--5 Partition
SELECT DATABASES_NAME,TABLE_NAMES,Partition_NAME,IS_OLD,SUM(PAGE_NUMBER) AS PAGE_NUM,SUM(NUMBER_RECORDS) AS CACHE_RECORDS,ROUND(SUM(DATA_SIZE)/1024/1024,2) AS MEM_SIZE_MB
FROM (
select TABLE_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else left(TABLE_NAME,instr(TABLE_NAME,'.')-1) end ) AS DATABASES_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME when instr(TABLE_NAME,'/*') = 0 then substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,length(TABLE_NAME)-1) else substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,(instr(TABLE_NAME,'/*')-instr(TABLE_NAME,'.')-1) ) end ) AS TABLE_NAMES ,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else substr(TABLE_NAME,instr(TABLE_NAME,'Partition')+9,(instr(TABLE_NAME,'*/')-instr(TABLE_NAME,'Partition')-9) ) end ) as Partition_NAME,
IS_OLD,PAGE_NUMBER,NUMBER_RECORDS,DATA_SIZE
from information_schema.innodb_buffer_page
WHERE TABLE_NAME like '%sharkdb%'
AND TABLE_NAME like '%Partition%'
) T
GROUP BY DATABASES_NAME,TABLE_NAMES,Partition_NAME,IS_OLD;
复制

4 查看分区某个时间的占用

假设你的分区按时间命名的话

--5 Partition
SELECT DATABASES_NAME,TABLE_NAMES,Partition_NAME,IS_OLD,SUM(PAGE_NUMBER) AS PAGE_NUM,SUM(NUMBER_RECORDS) AS CACHE_RECORDS,ROUND(SUM(DATA_SIZE)/1024/1024,2) AS MEM_SIZE_MB
FROM (
select TABLE_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else left(TABLE_NAME,instr(TABLE_NAME,'.')-1) end ) AS DATABASES_NAME,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME when instr(TABLE_NAME,'/*') = 0 then substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,length(TABLE_NAME)-1) else substr(TABLE_NAME,instr(TABLE_NAME,'.')+1,(instr(TABLE_NAME,'/*')-instr(TABLE_NAME,'.')-1) ) end ) AS TABLE_NAMES ,
(case when instr(TABLE_NAME,'.') = 0 then TABLE_NAME else substr(TABLE_NAME,instr(TABLE_NAME,'Partition')+9,(instr(TABLE_NAME,'*/')-instr(TABLE_NAME,'Partition')-9) ) end ) as Partition_NAME,
IS_OLD,PAGE_NUMBER,NUMBER_RECORDS,DATA_SIZE
from information_schema.innodb_buffer_page
WHERE TABLE_NAME like '%sharkdb%'
AND TABLE_NAME like '%Partition%'
AND TABLE_NAME like '%_2022%'
) T
GROUP BY DATABASES_NAME,TABLE_NAMES,Partition_NAME,IS_OLD;
复制