MySQL :: MySQL 8.3 Reference Manual :: 25.6.17.47 The ndbinfo memory_per

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / The ndbinfo memory_per_fragment Table

25.6.17.47 ndbinfo memory_per_fragment 表

该 memory_per_fragment 表提供了每个片段的内存使用情况信息。请参阅本节中的备注，了解如何使用该表来查找 NDB 表的内存使用情况。

该 memory_per_fragment 表包含以下列：

fq_name

该片段的名称
parent_fq_name

该片段的父名称
type

词典对象类型 (Object::Type，在 NDB API 中) 用于该片段；可以是 System 表、User 表、Unique 哈希索引、哈希索引、Unique 有序索引、有序索引、哈希索引触发器、订阅触发器、只读约束、索引触发器、重组触发器、表空间、日志文件组、数据文件、撤销文件、哈希映射、外键定义、外键父触发器、外键子触发器 或 模式事务。

您也可以通过在 mysql 客户端中执行 TABLE ndbinfo.dict_obj_types 来获取该列表。
table_id

该表的表 ID
node_id

该节点的节点 ID
block_instance

NDB 内核块实例 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 表：备注

该 memory_per_fragment 表包含每个表碎片副本和每个索引碎片副本的一行；这意味着，例如，当 NoOfReplicas=2 时，每个碎片通常有两个碎片副本。这在所有数据节点都在运行并连接到集群时是正确的；对于缺少的数据节点，没有它所承载的碎片副本的行。

可以根据其功能或目的将 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 表中找到。

表和索引碎片副本在 DataMemory 中分配 32KB 页。这些内存页按照以下方式管理：

固定大小页：这些存储给定碎片中的行的固定大小部分。每行都有一个固定大小部分。
可变大小页：这些存储碎片中的行的可变大小部分。每行都有一个或多个可变大小列（或两者）都有一个可变大小部分。
哈希索引页：这些以 8 KB 子页分配，并存储主键哈希索引结构。

每个 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 / 32768 和 number_of_var_pages = var_elem_alloc_bytes / 32768。 hash_index_alloc_bytes 始终是 8192 字节的倍数，因此 number_of_hash_pages = hash_index_alloc_bytes / 8192。

固定大小页具有内部头和固定大小槽，每个槽可以包含一行的固定大小部分。给定行的固定大小部分的大小是 schema 依赖的，并由 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_pages

fixed_elem_count 实际上是给定表碎片的行计数，因为每行都有一个固定大小元素， 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列中显示；因为这个空间可能分布在多个页面上，因此不能一定用于存储特定大小的条目。每个可变大小页面都会根据需要重新组织以适应可变大小行部分的变化大小，因为它们被插入、更新和删除；如果给定的行部分增长太大，无法适应页面，它可以被移到另一个页面。

可变大小页面的利用率可以按以下方式计算：

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

我们可以这样计算每行的平均可变部分大小：

avg_row_var_part_size = var_page_used_bytes / fixed_elem_count

二级唯一索引作为独立表实现，具有以下模式：

主键：基础表中的索引列。
值：基础表中的主键列。

这些表被分布和碎片化，如任何其他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。

如果页面中的空闲空间碎片化，页面将被碎片整理。OPTIMIZE TABLE 可以用于碎片整理表的可变大小页面；这将行可变大小部分移动到页面之间，以便释放一些页面以供重新使用。

memory_per_fragment 表：示例

在以下示例中，我们创建了一个简单的表，其中有三个整数列，其中一个具有主键，一个具有唯一索引，一个没有索引，以及一个没有索引的 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)

PREV HOME UP NEXT