MySQLCluster数据节点内存空间释放

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;