1、基础知识
重要参数:NoOfReplicas,DataMemory
(1)数据节点物理内存占用:
DataMemory:
什么内容会占用DataMemory分配的内存:Data,Ordered T-Tree indexes
表中每条记录的Ordered T-Tree indexes大小,大约是10byte
DataMemory计算方法:
DataMemory = (data size +(for each table(number of records * ordered indexes * 10)) * NoOfReplicas) /number of data nodes
IndexMemory:
什么内容会占用IndexMemory分配的内存: hash indexes(Hash indexes are used when you createa PRIMARY or UNIQUE constraint on a table)
IndexMemory计算方法:
IndexMemory = (for eachindex( (fragments * 32K) + (rows * 18) ) ) * NoOfReplicas / number of datanodes
事务操作占用内存:MaxNoOfConcurrentOperations: lock,undo操作,管理事物状态(Eachentry for this setting takes about 1KB of space)
MaxNoOfConcurrentOperations =total number of rows changed in a single transaction / number of data nodes
其他占用数据节点内存占用:
直接设置:
DataMemory (default 80 MB)
IndexMemory (default 18 MB)
BackupDataBufferSize (default16 MB)
BackupLogBufferSize (default16 MB)
DiskPageBufferMemory (default64 MB)
ExtraSendBufferMemory (seebelow)
SharedGlobalMemory (default20 MB)
RedoBuffer (default 32 MB)
TotalSendBufferMemory (默认是计算值)
UndoDataBuffer (default 16MB)
UndoIndexBuffer (default 2MB)
TransactionBufferMemory(default 1 MB)
LongMessageBuffer (default 4MB)
驱动占用:
MaxNoOfConcurrentIndexOperations(1 KB)
MaxNoOfConcurrentOperations(1 KB)
MaxNoOfLocalOperations (168bytes)
MaxNoOfLocalScans (a littlemore than 32k KB)
MaxNoOfConcurrentTransactions(1 KB)
MaxNoOfOrderedIndexes (10 KB)
MaxNoOfTables (20 KB)
MaxNoOfUniqueHashIndexes (15KB)
MaxNoOfAttributes (200 bytes)
StringMemory
Job buffers
(2)pages分类fixed sized pages:存储固定长度字段
var sized pages:存储变长字段
(3)memory_per_fragment表结构:
Column Name
Type
Description
fq_name
string
Name of this fragment
parent_fq_name
string
Name of this fragment's parent
type
string
Type of object; see text for possible values
table_id
integer
Table ID for this table
node_id
integer
Node ID for this node
block_instance
integer
Kernel block instance ID
fragment_num
integer
Fragment ID (number)
fixed_elem_alloc_bytes
integer
Number of bytes allocated for fixed-sized elements
fixed_elem_free_bytes
integer
Free bytes remaining in pages allocated to fixed-size elements
fixed_elem_size_bytes
integer
Length of each fixed-size element in bytes
fixed_elem_count
integer
Number of fixed-size elements
fixed_elem_free_rows
decimal
Number of free rows for fixed-size elements
var_elem_alloc_bytes
integer
Number of bytes allocated for variable-size elements
var_elem_free_bytes
integer
Free bytes remaining in pages allocated to variable-size elements
var_elem_count
integer
Number of variable-size elements
hash_index_alloc_bytes
integer
Number of bytes allocated to hash indexes
2、数据节点内存释放方法
内存会释放的情况:
删除表,truncate表,optimize 表(可能),轮询重启数据节点(可能)。
MySQLCluster释放内存碎片额方式:
1)optimize表:
优化var sizedpages碎片。只有完整fixed sized pages空页才能够被释放。
2)backup+restore:
优化fixedsized pages碎片。
3、如何确定数据节点内存碎片(数据库系统中会存在碎片有个前提条件就是数据库系统中存在大量的更新和删除操作)
(1)创建一个视图:
CREATE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost`
SQL SECURITY DEFINER
VIEW `ndbinfo`.`memory_pages_per_fragment`
AS select
`memory_per_fragment`.`fq_name` AS `fq_name`,
`memory_per_fragment`.`table_id` AS `table_id`,
`memory_per_fragment`.`node_id` AS `node_id`,
`memory_per_fragment`.`block_instance` AS `block_instance`,
`memory_per_fragment`.`fragment_num`AS `fragment_num`,
((100 *(`memory_per_fragment`.`fixed_elem_alloc_bytes` -
`memory_per_fragment`.`fixed_elem_free_bytes`))
/`memory_per_fragment`.`fixed_elem_alloc_bytes`) AS
`fixed_util_pct`,
(`memory_per_fragment`.`fixed_elem_alloc_bytes`/ 32768) AS
`fixed_elem_alloc_pages`,
(`memory_per_fragment`.`fixed_elem_free_bytes` / 32768) AS
`fixed_elem_spare_pages`,
((100 *(`memory_per_fragment`.`var_elem_alloc_bytes` -
`memory_per_fragment`.`var_elem_free_bytes`))
/`memory_per_fragment`.`var_elem_alloc_bytes`) AS
`var_util_pct`,
(`memory_per_fragment`.`var_elem_alloc_bytes` / 32768) AS
`var_elem_alloc_pages`,
(`memory_per_fragment`.`var_elem_free_bytes` / 32768) AS
`var_elem_spare_pages`
((`memory_per_fragment`.`var_elem_alloc_bytes` -
`memory_per_fragment`.`var_elem_free_bytes`)
/`memory_per_fragment`.`var_elem_count`) AS
`var_elem_avg_bytes` from `memory_per_fragment`
from`ndbinfo`.`memory_per_fragment`;
(2) 寻找可能的碎片:
--查找每个分片潜在可以释放的fixed page页数
select fq_name,
sum(floor(fixed_elem_spare_pages))
as freeable_fixed_pages from ndbinfo.memory_pages_per_fragment
where floor(fixed_elem_spare_pages) > 0
group by fq_name;
--查找整个系统潜在可以释放的fixed page页数
select sum(floor(fixed_elem_spare_pages))
as freeable_fixed_pages
from ndbinfo.memory_pages_per_fragment
where floor(fixed_elem_spare_pages) > 0;
--查找每个分片潜在可以释放的var page页数(误差较大,但可做参考)
select fq_name,
sum(floor(var_elem_spare_pages))
as freeable_var_pages from ndbinfo.memory_pages_per_fragment
where floor(var_elem_spare_pages) > 2
and
var_util_pct < 90
group by fq_name;
--查找整个系统潜在可以释放的var page页数(误差较大,但可做参考)
select sum(floor(var_elem_spare_pages))
as freeable_var_pages
from ndbinfo.memory_pages_per_fragment
where floor(var_elem_spare_pages) > 2
and
var_util_pct < 90;
(3)每个分片数据量查询
select fq_name,(sum(fixed_elem_alloc_bytes - fixed_elem_free_bytes) + sum(var_elem_alloc_bytes - var_elem_free_bytes))/1024/1024 as data_M
from ndbinfo.memory_per_fragment
where data_M > 0
group by fq_name;