21.5.14 ndbinfo:NDB 群集信息数据库
ndbinfo
是一个数据库,其中包含特定于 NDB 群集的信息。
该数据库包含许多 table,每个 table 提供有关 NDB 群集节点状态,资源使用情况和操作的不同类型的数据。在接下来的几节中,您将找到有关这些 table 的更多详细信息。
ndbinfo
包含在 MySQL 服务器的 NDB 群集支持中;不需要特殊的编译或配置步骤;这些 table 是由 MySQL 服务器连接到群集时创建的。您可以使用SHOW PLUGINS来验证ndbinfo
支持在给定的 MySQL Server 实例中处于活动状态;如果启用了ndbinfo
支持,则应该在Name
列中看到包含ndbinfo
的行,在Status
列中看到ACTIVE
的行,如下所示(强调的文本):
mysql> SHOW PLUGINS;
+----------------------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbCluster | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbinfo | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndb_transid_mysql_connection_map | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
+----------------------------------+--------+--------------------+---------+---------+
46 rows in set (0.00 sec)
您还可以通过检查SHOW ENGINES的输出来实现这一点,该行的Engine
列中包含ndbinfo
,而Support
列中包含YES
,如下所示(强调的文本):
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: ndbinfo
Support: YES
Comment: NDB Cluster system information storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 10. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)
如果启用了ndbinfo
支持,则可以使用mysql或其他 MySQLClient 端中的 SQL 语句访问ndbinfo
。例如,您可以看到SHOW DATABASES的输出中列出了ndbinfo
,如下所示(强调的文本):
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ndbinfo |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.04 sec)
如果mysqld进程不是使用--ndbcluster选项启动的,则ndbinfo
不可用,并且SHOW DATABASES不会显示。如果mysqld以前已连接到 NDB 群集,但是该群集不可用(由于群集关闭,网络连接丢失等事件),则ndbinfo
及其 table 仍然可见,但是尝试访问任何 table(除blocks
或config_params
)失败,并显示错误 157:从 NDBINFO 到 NDB 的连接失败。
除了blocks
和config_params
table 以外,我们称为ndbinfo
“table”实际上是从内部NDBtable 生成的视图,通常对 MySQL Server 不可见。
所有ndbinfo
table 均为只读,并在查询时按需生成。由于它们中的许多是由数据节点并行生成的,而另一些则特定于给定的 SQL 节点,因此不能保证它们提供一致的快照。
此外,ndbinfo
table 不支持下推连接;因此,即使查询使用WHERE
子句,联接大型ndbinfo
table 也可能需要将大量数据传输到发出请求的 API 节点。
ndbinfo
table 不包括在查询缓存中。 (缺陷#59831)
您可以使用USE语句选择ndbinfo
数据库,然后发出SHOW TABLES语句以获取 table 列 table,就像其他任何数据库一样,如下所示:
mysql> USE ndbinfo;
Database changed
mysql> SHOW TABLES;
+---------------------------------+
| Tables_in_ndbinfo |
+---------------------------------+
| arbitrator_validity_detail |
| arbitrator_validity_summary |
| blocks |
| cluster_locks |
| cluster_operations |
| cluster_transactions |
| config_nodes |
| config_params |
| config_values |
| counters |
| cpustat |
| cpustat_1sec |
| cpustat_20sec |
| cpustat_50ms |
| dict_obj_info |
| dict_obj_types |
| disk_write_speed_aggregate |
| disk_write_speed_aggregate_node |
| disk_write_speed_base |
| diskpagebuffer |
| error_messages |
| locks_per_fragment |
| logbuffers |
| logspaces |
| membership |
| memory_per_fragment |
| memoryusage |
| nodes |
| operations_per_fragment |
| processes |
| resources |
| restart_info |
| server_locks |
| server_operations |
| server_transactions |
| table_distribution_status |
| table_fragments |
| table_info |
| table_replicas |
| tc_time_track_stats |
| threadblocks |
| threads |
| threadstat |
| transporters |
+---------------------------------+
44 rows in set (0.00 sec)
在 NDB 7.5.0(及更高版本)中,所有ndbinfo
table 都使用NDB
存储引擎;但是,如上所述,ndbinfo
条目仍出现在SHOW ENGINES和SHOW PLUGINS的输出中。
config_valuestable 已添加到 NDB 7.5.0 中。
NDB 7.5.2 中添加了cpustat,cpustat_50ms,cpustat_1sec,cpustat_20sec和threadstable。
NDB 7.5.3 中添加了cluster_locks,locks_per_fragment和server_lockstable。
NDB 7.5.4 中添加了dict_obj_info,table_distribution_status,table_fragments,table_info和table_replicastable。
config_nodes和processestable 已添加到 NDB 7.5.7 和 NDB 7.6.2 中。
error_messagestable 已添加到 NDB 7.6.4 中。
您可以像通常期望的那样对这些 table 执行SELECT语句:
mysql> SELECT * FROM memoryusage;
+---------+---------------------+--------+------------+------------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+---------------------+--------+------------+------------+-------------+
| 5 | Data memory | 753664 | 23 | 1073741824 | 32768 |
| 5 | Index memory | 163840 | 20 | 1074003968 | 131104 |
| 5 | Long message buffer | 2304 | 9 | 67108864 | 262144 |
| 6 | Data memory | 753664 | 23 | 1073741824 | 32768 |
| 6 | Index memory | 163840 | 20 | 1074003968 | 131104 |
| 6 | Long message buffer | 2304 | 9 | 67108864 | 262144 |
+---------+---------------------+--------+------------+------------+-------------+
6 rows in set (0.02 sec)
可以进行更复杂的查询,例如使用memoryusagetable 的以下两个SELECT语句:
mysql> SELECT SUM(used) as 'Data Memory Used, All Nodes'
> FROM memoryusage
> WHERE memory_type = 'Data memory';
+-----------------------------+
| Data Memory Used, All Nodes |
+-----------------------------+
| 6460 |
+-----------------------------+
1 row in set (0.37 sec)
mysql> SELECT SUM(max) as 'Total IndexMemory Available'
> FROM memoryusage
> WHERE memory_type = 'Index memory';
+-----------------------------+
| Total IndexMemory Available |
+-----------------------------+
| 25664 |
+-----------------------------+
1 row in set (0.33 sec)
ndbinfo
table 和列的名称区分大小写(ndbinfo
数据库本身的名称也是如此)。这些标识符是小写的。尝试使用错误的字母大写会导致错误,如以下示例所示:
mysql> SELECT * FROM nodes;
+---------+--------+---------+-------------+
| node_id | uptime | status | start_phase |
+---------+--------+---------+-------------+
| 1 | 13602 | STARTED | 0 |
| 2 | 16 | STARTED | 0 |
+---------+--------+---------+-------------+
2 rows in set (0.04 sec)
mysql> SELECT * FROM Nodes;
ERROR 1146 (42S02): Table 'ndbinfo.Nodes' doesn't exist
mysqldump完全忽略ndbinfo
数据库,并将其从任何输出中排除。即使使用--databases或--all-databases选项也是如此。
NDB Cluster 还在INFORMATION_SCHEMA
信息数据库中维护 table,包括FILEStable 和ndb_transid_mysql_connection_maptable,该 table 包含有关 NDB 群集磁盘数据存储所用文件的信息,而ndb_transid_mysql_connection_maptable 显示事务,事务协调器和 NDB Cluster API 节点之间的关系。有关更多信息,请参见 table 或第 21.5.15 节,“ NDB 群集的 INFORMATION_SCHEMAtable”的描述。