MySQL 8.4 Release Notes
25.6.17.47 NDBinfo memory_per_fragment 表
memory_per_fragment 表提供了关于单个片段内存使用情况的信息。请参阅本节后面的Notes以了解如何使用该表来确定 NDB 表的内存使用情况。
memory_per_fragment 表包含以下列：
-
fq_名片段名称
-
parent_fq_名片段的父名称
-
type字典对象类型（
Object::Type，在 NDB API 中），用于该片段；其中之一是System table、User table、Unique hash index、Hash index、Unique ordered index、Ordered index、Hash index trigger、Subscription trigger、Read only constraint、Index trigger、Reorganize trigger、Tablespace、Log file group、Data file、Undo file、Hash map、Foreign key definition、Foreign key parent trigger、Foreign key child trigger或Schema transaction。您也可以通过执行
TABLEndbinfo.dict_obj_types在mysql 客户端中获得该列表。 -
table_id对应表的 ID
-
node_id对应节点的 ID
-
block_instanceNDB kernel块实例ID；您可以使用这个数字从
threadblocks表中获取关于特定线程的信息。 -
fragment_num片段ID（数字）
-
fixed_elem_alloc_bytes固定大小元素分配的字节数
-
fixed_elem_free_bytes固定大小元素页面中的剩余字节
-
fixed_elem_size_bytes每个固定大小元素的字节数
-
fixed_elem_count固定大小元素的数量
-
fixed_elem_free_count固定大小元素的空闲行数
-
var_elem_alloc_bytes可变大小元素分配的字节数
-
var_elem_free_bytes可变大小元素页面中的剩余字节
-
var_elem_count可变大小元素的数量
-
hash_index_alloc_bytes哈希索引分配的字节数
memory_per_fragment 表包含系统中每个表片段副本和索引片段副本的一行记录；这意味着，例如，当NoOfReplicas=2时，每个片段通常有两个副本。这是 true 的，只要所有数据节点都在运行并连接到集群中；对于缺少的数据节点，没有对应的片段副本记录。
memory_per_fragment 表的列可以根据它们的功能或目的进行分组如下：
-
关键列：
fq_name，type，table_id，node_id，block_instance和fragment_num -
关系列：
parent_fq_name -
固定大小存储列：
fixed_elem_alloc_bytes，fixed_elem_free_bytes，fixed_elem_size_bytes，fixed_elem_count和fixed_elem_free_count -
可变大小存储列：
var_elem_alloc_bytes，var_elem_free_bytes和var_elem_count -
哈希索引列：
hash_index_alloc_bytes
可以使用parent_fq_name和fq_name列来识别与表相关联的索引。其他ndbinfo表中也提供了类似的架构对象层次结构信息。
表和索引碎片副本在32KB页面中分配DataMemory。这些内存页面将按照以下方式管理：
-
固定大小页：这些存储在给定碎片中的行的固定大小部分。每一行都有一个固定大小部分。
-
可变大小页：这些存储在碎片中的行的可变大小部分。每一行都有一个或多个可变大小、一个或多个动态列（或两者）都有一个可变大小部分。
-
哈希索引页：这些分配为8KB子页面，并存储主键哈希索引结构。
每一行在NDB表中都有一个固定大小部分，包括行头和一个或多个固定大小列。该行可能还包含一个或多个可变大小部分引用、一个或多个磁盘部分引用，或者两者。每一行也都有一个主键哈希索引条目（对应于NDB表中的隐藏主键）。
从前面的信息中可以看到，每个表碎片和索引碎片一起分配的DataMemory数量计算如下所示：
DataMemory =
(number_of_fixed_pages + number_of_var_pages) * 32KB
+ number_of_hash_pages * 8KB由于fixed_elem_alloc_bytes和var_elem_alloc_bytes总是32768字节的倍数，我们可以进一步确定number_of_fixed_pages= fixed_elem_alloc_bytes / 32768number_of_var_pages= var_elem_alloc_bytes / 32768hash_index_alloc_bytes总是8192字节的倍数，所以number_of_hash_pages= hash_index_alloc_bytes / 8192
固定大小的页面具有内部头和固定大小的插槽，每个插槽可以包含一个行的固定大小部分。给定行的固定大小部分的大小是架构相关的，并由fixed_elem_size_bytes列提供；每页固定大小插槽的数量可以通过计算总插槽数和总页面数来确定，如下所示：
fixed_slots = fixed_elem_count + fixed_elem_free_count
fixed_pages = fixed_elem_alloc_bytes / 32768
slots_per_page = total_slots / total_pagesfixed_elem_count实际上是给定表片段的行计数，因为每行都有1个固定元素;fixed_elem_free_count是分配页面上的总免费固定大小插槽数;fixed_elem_free_bytes等于fixed_elem_free_count * fixed_elem_size_bytes。
片段可以有任意数量的固定大小页面；当最后一行在固定大小页面上被删除时，该页面将被释放到DataMemory页面池中。固定大小页面可以碎片化，分配的页面数超过实际使用的固定大小插槽数。你可以通过比较所需页面数和已分配页面数来检查是否存在这种情况，如下所示：
fixed_pages_required = 1 + (fixed_elem_count / slots_per_page)
fixed_page_utilization = fixed_pages_required / fixed_pages变长页面具有内部头部，使用剩余空间存储一个或多个变长行部分；存储的部分数量取决于架构和实际数据。由于不所有架构或行都有变长部分，因此var_elem_count可能小于fixed_elem_count。所有变长页面中的总可用空间由var_elem_free_bytes列显示；由于这个空间可能spread在多个页面上，因此不能一定用于存储特定大小的条目。每个变长页面都将根据变长行部分的大小变化进行重新组织，如果某个行部分增长到超过其所在页面，可以被移动到不同的页面。
可以按照以下方式计算变长页面使用率：
var_page_used_bytes = var_elem_alloc_bytes - var_elem_free_bytes
var_page_utilisation = var_page_used_bytes / var_elem_alloc_bytes
avg_row_var_part_size = var_page_used_bytes / fixed_elem_count我们可以按照以下方式获得平均变长部分大小per行：
avg_row_var_part_size = var_page_used_bytes / fixed_elem_count次要唯一索引内部实现为独立表，具有以下架构：
-
Primary key: 基础表中的索引列。
-
Values: 基础表中的主键列。
这些表像通常一样被分布和分片。这意味着它们的副本使用固定、变长和哈希索引页面，就像任何其他NDB表一样。
辅助索引是碎片化的，并且在同一个方式中分布于基础表中。有序索引碎片是T-树结构，它们维护了一个平衡的树，包含了按隐含的索引列顺序排列的行引用。由于树包含的是引用，而不是实际数据，因此T-树存储成本不取决于索引列的大小或数量，而是取决于表中的行数。树结构使用固定大小的节点结构，每个节点可能包含多个行引用；所需的节点数量取决于表中的行数和必要的树结构来表示顺序。在memory_per_fragment表中，我们可以看到有序索引只分配固定大小的页面，因此通常情况下，来自该表的相关列如下所示：
-
fixed_elem_alloc_bytes: 等于固定大小页面的数量乘以32768。 -
fixed_elem_count: 使用中的T-树节点数量。 -
fixed_elem_size_bytes: 每个T-树节点的字节大小。 -
fixed_elem_free_count: 分配页面中可用的T-树节点槽数量。 -
fixed_elem_free_bytes: 等于fixed_elem_free_count * fixed_elem_size_bytes。
如果页面中的自由空间碎片化，页面将被.defragment。可以使用OPTIMIZE TABLE语句来.defragment表的变长页面；这将移动行变长部分之间的页面，以释放一些完整页面以供重用。
以下示例中，我们创建了一个简单的表，其中有三个整数列，一个具有主键、一个具有唯一索引和一个无索引的列，以及一个无索引的VARCHAR列，如下所示：
mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.06 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (
-> c1 BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> c2 INT,
-> c3 INT UNIQUE,
-> ) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.27 sec)在创建表后，我们插入50,000行包含随机数据的行；生成和插入这些行的确切方法无实际影响，我们将其留作用户练习。
Getting general information about fragments and memory usage
这个查询显示每个片段的内存使用信息：
mysql> SELECT
-> fq_name, node_id, block_instance, fragment_num, fixed_elem_alloc_bytes,
-> fixed_elem_free_bytes, fixed_elem_size_bytes, fixed_elem_count,
-> fixed_elem_free_count, var_elem_alloc_bytes, var_elem_free_bytes,
-> var_elem_count
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = "test/def/t1"\G
*************************** 1. row ***************************
fq_name: test/def/t1
node_id: 5
block_instance: 1
fragment_num: 0
fixed_elem_alloc_bytes: 1114112
fixed_elem_free_bytes: 11836
fixed_elem_size_bytes: 44
fixed_elem_count: 24925
fixed_elem_free_count: 269
var_elem_alloc_bytes: 1245184
var_elem_free_bytes: 32552
var_elem_count: 24925
*************************** 2. row ***************************
fq_name: test/def/t1
node_id: 5
block_instance: 1
fragment_num: 1
fixed_elem_alloc_bytes: 1114112
fixed_elem_free_bytes: 5236
fixed_elem_size_bytes: 44
fixed_elem_count: 25075
fixed_elem_free_count: 119
var_elem_alloc_bytes: 1277952
var_elem_free_bytes: 54232
var_elem_count: 25075
*************************** 3. row ***************************
fq_name: test/def/t1
node_id: 6
block_instance: 1
fragment_num: 0
fixed_elem_alloc_bytes: 1114112
fixed_elem_free_bytes: 11836
fixed_elem_size_bytes: 44
fixed_elem_count: 24925
fixed_elem_free_count: 269
var_elem_alloc_bytes: 1245184
var_elem_free_bytes: 32552
var_elem_count: 24925
*************************** 4. row ***************************
fq_name: test/def/t1
node_id: 6
block_instance: 1
fragment_num: 1
fixed_elem_alloc_bytes: 1114112
fixed_elem_free_bytes: 5236
fixed_elem_size_bytes: 44
fixed_elem_count: 25075
fixed_elem_free_count: 119
var_elem_alloc_bytes: 1277952
var_elem_free_bytes: 54232
var_elem_count: 25075
4 rows in set (0.12 sec)Finding a table and its indexes
这个查询可以用来找到特定的表及其索引：
mysql> SELECT fq_name
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+
| fq_name |
+----------------------+
| test/def/t1 |
| sys/def/13/PRIMARY |
| sys/def/13/c3 |
| sys/def/13/c3$unique |
+----------------------+
4 rows in set (0.13 sec)
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.00 sec)Finding the memory allocated by schema elements
这个查询显示每个架构元素所分配的内存（在所有副本中）：
mysql> SELECT
-> fq_name AS Name,
-> SUM(fixed_elem_alloc_bytes) AS Fixed,
-> SUM(var_elem_alloc_bytes) AS Var,
-> SUM(hash_index_alloc_bytes) AS Hash,
-> SUM(fixed_elem_alloc_bytes+var_elem_alloc_bytes+hash_index_alloc_bytes) AS Total
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+---------+---------+---------+----------+
| Name | Fixed | Var | Hash | Total |
+----------------------+---------+---------+---------+----------+
| test/def/t1 | 4456448 | 5046272 | 1425408 | 10928128 |
| sys/def/13/PRIMARY | 1966080 | 0 | 0 | 1966080 |
| sys/def/13/c3 | 1441792 | 0 | 0 | 1441792 |
| sys/def/13/c3$unique | 3276800 | 0 | 1425408 | 4702208 |
+----------------------+---------+---------+---------+----------+
4 rows in set (0.11 sec)Finding the memory allocated for a table and all indexes
使用以下查询可以获得表和所有索引的总内存分配（在所有副本中）：
mysql> SELECT
-> SUM(fixed_elem_alloc_bytes) AS Fixed,
-> SUM(var_elem_alloc_bytes) AS Var,
-> SUM(hash_index_alloc_bytes) AS Hash,
-> SUM(fixed_elem_alloc_bytes+var_elem_alloc_bytes+hash_index_alloc_bytes) AS Total
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+----------+---------+---------+----------+
| Fixed | Var | Hash | Total |
+----------+---------+---------+----------+
| 11141120 | 5046272 | 2850816 | 19038208 |
+----------+---------+---------+----------+
1 row in set (0.12 sec)这是前一个查询的简化版本，显示了表的总内存使用：
mysql> SELECT
-> SUM(fixed_elem_alloc_bytes+var_elem_alloc_bytes+hash_index_alloc_bytes) AS Total
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+----------+
| Total |
+----------+
| 19038208 |
+----------+
1 row in set (0.12 sec)Finding the memory allocated per row
以下查询显示每个行所分配的总内存（在所有副本中）：
mysql> SELECT
-> SUM(fixed_elem_alloc_bytes+var_elem_alloc_bytes+hash_index_alloc_bytes)
-> /
-> SUM(fixed_elem_count) AS Total_alloc_per_row
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1';
+---------------------+
| Total_alloc_per_row |
+---------------------+
| 109.2813 |
+---------------------+
1 row in set (0.12 sec)Finding the total memory in use per row
要获得每个行的总内存使用（在所有副本中），我们需要将总内存使用除以行数，这是基本表的fixed_elem_count的值，如下所示：
mysql> SELECT
-> SUM(
-> (fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> + (var_elem_alloc_bytes - var_elem_free_bytes)
-> + hash_index_alloc_bytes
-> )
-> /
-> SUM(fixed_elem_count)
-> AS total_in_use_per_row
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1';
+----------------------+
| total_in_use_per_row |
+----------------------+
| 107.2042 |
+----------------------+
1 row in set (0.12 sec)Finding the memory allocated per element
可以使用以下查询找到每个架构元素所分配的总内存（在所有副本中）：
mysql> SELECT
-> fq_name AS Name,
-> SUM(fixed_elem_alloc_bytes) AS Fixed,
-> SUM(var_elem_alloc_bytes) AS Var,
-> SUM(hash_index_alloc_bytes) AS Hash,
-> SUM(fixed_elem_alloc_bytes + var_elem_alloc_bytes + hash_index_alloc_bytes)
-> AS Total_alloc
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+---------+---------+---------+-------------+
| Name | Fixed | Var | Hash | Total_alloc |
+----------------------+---------+---------+---------+-------------+
| test/def/t1 | 4456448 | 5046272 | 1425408 | 10928128 |
| sys/def/13/PRIMARY | 1966080 | 0 | 0 | 1966080 |
| sys/def/13/c3 | 1441792 | 0 | 0 | 1441792 |
| sys/def/13/c3$unique | 3276800 | 0 | 1425408 | 4702208 |
+----------------------+---------+---------+---------+-------------+
4 rows in set (0.11 sec)Finding the average memory allocated per row, by element
要获得每个架构元素对每行的平均内存分配（在所有副本中），我们使用子查询来获取基本表的固定元素计数，每次都可以获取平均值，因为索引的fixed_elem_count不一定与基本表相同，如下所示：
mysql> SELECT
-> fq_name AS Name,
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Table_rows,
->
-> SUM(fixed_elem_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Avg_fixed_alloc,
->
-> SUM(var_elem_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') as Avg_var_alloc,
->
-> SUM(hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') as Avg_hash_alloc,
->
-> SUM(fixed_elem_alloc_bytes+var_elem_alloc_bytes+hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') as Avg_total_alloc
->
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' or parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+------------+-----------------+---------------+----------------+-----------------+
| Name | Table_rows | Avg_fixed_alloc | Avg_var_alloc | Avg_hash_alloc | Avg_total_alloc |
+----------------------+------------+-----------------+---------------+----------------+-----------------+
| test/def/t1 | 100000 | 44.5645 | 50.4627 | 14.2541 | 109.2813 |
| sys/def/13/PRIMARY | 100000 | 19.6608 | 0.0000 | 0.0000 | 19.6608 |
| sys/def/13/c3 | 100000 | 14.4179 | 0.0000 | 0.0000 | 14.4179 |
| sys/def/13/c3$unique | 100000 | 32.7680 | 0.0000 | 14.2541 | 47.0221 |
+----------------------+------------+-----------------+---------------+----------------+-----------------+
4 rows in set (0.70 sec)Finding the average memory allocated per row
每个行的平均内存分配（在所有副本中）：
mysql> SELECT
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Table_rows,
->
-> SUM(fixed_elem_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Avg_fixed_alloc,
->
-> SUM(var_elem_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Avg_var_alloc,
->
-> SUM(hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Avg_hash_alloc,
->
-> SUM(fixed_elem_alloc_bytes + var_elem_alloc_bytes + hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS Avg_total_alloc
->
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+------------+-----------------+---------------+----------------+-----------------+
| Table_rows | Avg_fixed_alloc | Avg_var_alloc | Avg_hash_alloc | Avg_total_alloc |
+------------+-----------------+---------------+----------------+-----------------+
| 100000 | 111.4112 | 50.4627 | 28.5082 | 190.3821 |
+------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.71 sec)Finding the average memory allocated per row for a table
要获取整个表对每行的平均内存分配（在所有副本中），我们可以使用以下查询：
mysql> SELECT
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS table_rows,
->
-> SUM(fixed_elem_alloc_bytes + var_elem_alloc_bytes + hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_total_alloc
->
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+------------+-----------------+
| table_rows | avg_total_alloc |
+------------+-----------------+
| 100000 | 190.3821 |
+------------+-----------------+
1 row in set (0.33 sec)Finding the memory in use by each schema element
要获得架构元素在所有副本中的内存使用，我们需要将每个元素之间的分配和免费内存之差相加，如下所示：
mysql> SELECT
-> fq_name AS Name,
-> SUM(fixed_elem_alloc_bytes - fixed_elem_free_bytes) AS fixed_inuse,
-> SUM(var_elem_alloc_bytes-var_elem_free_bytes) AS var_inuse,
-> SUM(hash_index_alloc_bytes) AS hash_memory,
-> SUM( (fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> + (var_elem_alloc_bytes - var_elem_free_bytes)
-> + hash_index_alloc_bytes) AS total_alloc
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+-------------+-----------+---------+-------------+
| fq_name | fixed_inuse | var_inuse | hash | total_alloc |
+----------------------+-------------+-----------+---------+-------------+
| test/def/t1 | 4422304 | 4872704 | 1425408 | 10720416 |
| sys/def/13/PRIMARY | 1950848 | 0 | 0 | 1950848 |
| sys/def/13/c3 | 1428736 | 0 | 0 | 1428736 |
| sys/def/13/c3$unique | 3212800 | 0 | 1425408 | 4638208 |
+----------------------+-------------+-----------+---------+-------------+
4 rows in set (0.13 sec)Finding the average memory in use by each schema element
获取所有副本中每个架构元素的平均内存使用量的查询：
mysql> SELECT
-> fq_name AS Name,
->
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS table_rows,
->
-> SUM(fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_fixed_inuse,
->
-> SUM(var_elem_alloc_bytes - var_elem_free_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_var_inuse,
->
-> SUM(hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_hash,
->
-> SUM(
-> (fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> + (var_elem_alloc_bytes - var_elem_free_bytes) + hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_total_inuse
->
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1'
-> GROUP BY fq_name;
+----------------------+------------+-----------------+---------------+----------+-----------------+
| Name | table_rows | avg_fixed_inuse | avg_var_inuse | avg_hash | avg_total_inuse |
+----------------------+------------+-----------------+---------------+----------+-----------------+
| test/def/t1 | 100000 | 44.2230 | 48.7270 | 14.2541 | 107.2042 |
| sys/def/13/PRIMARY | 100000 | 19.5085 | 0.0000 | 0.0000 | 19.5085 |
| sys/def/13/c3 | 100000 | 14.2874 | 0.0000 | 0.0000 | 14.2874 |
| sys/def/13/c3$unique | 100000 | 32.1280 | 0.0000 | 14.2541 | 46.3821 |
+----------------------+------------+-----------------+---------------+----------+-----------------+
4 rows in set (0.72 sec)Finding the average memory in use per row, by element
获取所有副本中每行、每个元素的平均内存使用量的查询：
mysql> SELECT
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS table_rows,
->
-> SUM(fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_fixed_inuse,
->
-> SUM(var_elem_alloc_bytes - var_elem_free_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_var_inuse,
->
-> SUM(hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_hash,
->
-> SUM(
-> (fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> + (var_elem_alloc_bytes - var_elem_free_bytes)
-> + hash_index_alloc_bytes)
-> /
-> ( SELECT SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_total_inuse
->
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+------------+-----------------+---------------+----------+-----------------+
| table_rows | avg_fixed_inuse | avg_var_inuse | avg_hash | avg_total_inuse |
+------------+-----------------+---------------+----------+-----------------+
| 100000 | 110.1469 | 48.7270 | 28.5082 | 187.3821 |
+------------+-----------------+---------------+----------+-----------------+
1 row in set (0.68 sec)Finding the total average memory in use per row
获取每行的总体平均内存使用量的查询：
mysql> SELECT
-> SUM(
-> (fixed_elem_alloc_bytes - fixed_elem_free_bytes)
-> + (var_elem_alloc_bytes - var_elem_free_bytes)
-> + hash_index_alloc_bytes)
-> /
-> ( SELECT
-> SUM(fixed_elem_count)
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name='test/def/t1') AS avg_total_in_use
-> FROM ndbinfo.memory_per_fragment
-> WHERE fq_name = 'test/def/t1' OR parent_fq_name='test/def/t1';
+------------------+
| avg_total_in_use |
+------------------+
| 187.3821 |
+------------------+
1 row in set (0.24 sec)