GBase 8a 学习笔记 014 —— GBase 8a MPP Cluster 系统表
系统库综述
- 系统数据库在每个gcluster节点、gnode节点上都存在；
- 系统库里的系统表所查询到的信息大多是本地信息；
- Information_schema
- performance_schema
- gbase
- gclusterdb
集群元数据
information_schema库
库内表为系统视图（MEMORY引擎表，只读），在查询时通过检索扫描相关文件获取集群的元数据信息，如库或表的名称、列的数据类型、访问权限、数据加载结果及状态信息、资源信息等；
常用表
- tables
- columns
- views
- processlist
- global_variables
- ……
performance_schema库
与information_schema库相似，库内表为系统视图，区别在于erformance_schema用于监控数据库本地运行时的信息，包括运行状态信息、磁盘、内存使用情况等；
常用表
- disk_usage_info
- cluter_disk_usage_info
- tables
- heap_usage_info
- memory_usage_info
- ……
gbase库
gbase库内的系统表为GsSYS引擎表，存储用户及权限、审计日志、函数/存储过程、UDF及引擎插件等信息；
常用表
- user
- role_edges
- table_distribution
- audit_log
- nodedatamap
- ……
gclusterdb库
gclusterdb库的系统表为express引擎表，主要为dual、数据重分布、kafka-consumer流数据处理、temporary临时表对应的物理映射表等系统表（部分系统表在使用相关功能时才生成）；
常用表
- dual
- rebalancing_status
系统表查询场景示例
INFORMATION_SCHEMA系统库信息查询示例
-- 查询VC虚拟集群
gbase> SELECT * FROM INFORMATION_SCHEMA.vc;
+---------+--------------+---------+
| ID | NAME | DEFAULT |
+---------+--------------+---------+
| vc00001 | vcname000001 | Y |
+---------+--------------+---------+
1 row in set (Elapsed: 00:00:00.00)
-- 查询数据库
gbase> SELECT SCHEMA_NAME AS `Database` FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| mydb |
| test |
+--------------------+
7 rows in set (Elapsed: 00:00:00.00)
-- 查询某个库的所有表和视图
gbase> SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test';
+------------+------------+
| table_name | table_type |
+------------+------------+
| sc | BASE TABLE |
| student | BASE TABLE |
| t | BASE TABLE |
+------------+------------+
3 rows in set (Elapsed: 00:00:00.00)
-- 查询某个表中的列
gbase> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'student' AND table_schema='test';
+-------------+-----------+-------------+----------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
+-------------+-----------+-------------+----------------+
| Sno | varchar | YES | NULL |
| Sname | varchar | YES | NULL |
| Ssex | varchar | YES | NULL |
| Sage | int | YES | NULL |
| Sdept | varchar | YES | NULL |
+-------------+-----------+-------------+----------------+
5 rows in set (Elapsed: 00:00:00.01)
-- 查询存储过程和自定义函数
gbase> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES ;
+------------------------------------------------------------------+------------------------------------------------------------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |
+------------------------------------------------------------------+------------------------------------------------------------------+--------------+
| mydb | New | PROCEDURE |
| mydb | newf | FUNCTION |
+------------------------------------------------------------------+------------------------------------------------------------------+--------------+
2 rows in set (Elapsed: 00:00:00.00)
-- 查询索引信息
gbase> SELECT table_name , INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, index_type FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='mydb';
Empty set (Elapsed: 00:00:00.07)
gbase> SELECT table_name , INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, index_type FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='test';
Empty set (Elapsed: 00:00:00.10)
元数据信息查询
-- 查看系统中VC名的语句
gbase> show vcs;
+---------+--------------+---------+
| id | name | default |
+---------+--------------+---------+
| vc00001 | vcname000001 | Y |
+---------+--------------+---------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看系统中数据库名的语句（根据权限显示）
gbase> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| gbase |
| gctmpdb |
| gclusterdb |
| mydb |
| test |
+--------------------+
7 rows in set (Elapsed: 00:00:00.00)
-- 列出给定数据库的所有非临时表
gbase> show tables from test like 't%';
+---------------------+
| Tables_in_test (t%) |
+---------------------+
| t |
+---------------------+
1 row in set (Elapsed: 00:00:00.01)
-- 查看test库t表的列信息
-- show columns from test.t; 等价于desc test.t;
gbase> show columns from test.t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> desc test.t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
-- 查看test库t表的索引信息
gbase> show index from test.t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
| t | 1 | idx_t_a | 1 | a | NULL | NULL | NULL | NULL | YES | GLOBAL HASH | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-------------+---------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看test的建库语句
gbase> show create table test.t;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE "t" (
"a" int(11) DEFAULT NULL,
"b" varchar(40) DEFAULT NULL,
KEY "idx_t_a" ("a") USING HASH GLOBAL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
-- 查看创建存储过程的语句
gbase> show create procedure New;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| New | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH | CREATE DEFINER="root"@"%" PROCEDURE "New"()
BEGIN
select * from mydb.sales;
END | utf8 | utf8_general_ci | utf8_general_ci |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)
磁盘空间使用信息查询
performance_schema系统库示例
- 查询当前节点gcluster层磁盘空间使用信息
gbase> select * from performance_schema.DISK_USAGE_INFO;
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE | PATH | DIR_SIZE | FILESYTEM | SIZE | USED | AVAIL | PCT |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| 192.168.254.138 | datadir | /opt/gbase/gcluster/userdata/gcluster/ | 7651328 | /dev/mapper/centos-root | 53660876800 | 6192005120 | 47468871680 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gcluster/tmpdata/cache_gcluster | 0 | /dev/mapper/centos-root | 53660876800 | 6192005120 | 47468871680 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gcluster/log/gcluster/ | 6402048 | /dev/mapper/centos-root | 53660876800 | 6192005120 | 47468871680 | 12% |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
3 rows in set (Elapsed: 00:00:00.01)
- 查询集群某节点的gcluster层和gnode层磁盘空间使用信息
-- select * from performance_schema.CLUSTER_DISK_USAGE_INFO where host='vm1';
gbase> select * from performance_schema.CLUSTER_DISK_USAGE_INFO where host='192.168.254.138';
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE | PATH | DIR_SIZE | FILESYTEM | SIZE | USED | AVAIL | PCT |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
| 192.168.254.138 | datadir | /opt/gbase/gnode/userdata/gbase/ | 8466432 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gnode/tmpdata/cache_gbase | 0 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gnode/log/gbase/ | 421888 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
| 192.168.254.138 | datadir | /opt/gbase/gcluster/userdata/gcluster/ | 7651328 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
| 192.168.254.138 | gbase_cache_data | /opt/gbase/gcluster/tmpdata/cache_gcluster | 0 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
| 192.168.254.138 | logdir | /opt/gbase/gcluster/log/gcluster/ | 6402048 | /dev/mapper/centos-root | 53660876800 | 6196035584 | 47464841216 | 12% |
+-----------------+------------------+--------------------------------------------+----------+-------------------------+-------------+------------+-------------+-----+
6 rows in set (Elapsed: 00:00:00.04)
用户信息查询
gbase系统库示例
- 查询数据库中的用户
gbase> select user from gbase.user;
+----------------------------------------------------------------------------------------------------------------------------------+
| user |
+----------------------------------------------------------------------------------------------------------------------------------+
| admin |
| gbase |
| role1 |
| root |
| user1 |
+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (Elapsed: 00:00:00.00)
- 查询数据库中的用户与用户组role的关联关系。
gbase> select * from gbase.role_edges \G
*************************** 1. row ***************************
FROM_HOST: %
FROM_USER: role1
TO_HOST: %
TO_USER: user1
WITH_ADMIN_OPTION: N
1 row in set (Elapsed: 00:00:00.00)
hash分布表和分布键信息查询
gbase系统库示例
如何查看哪些表是复制表，哪些是hash分布表，分布键是哪列?
select dbName,tbName,isReplicate,hash_column from gbase.table_distribution where dbname= '库名' and tbname like '表名';
show create table 表名;
查询分布表数据量
如何判断分布表数据分布没有倾斜？
select * from information_schema.cluster_table_segments where table_schema ='库名' and table_name = '表名'；
-- cluster_table_segments ：记录每个分片的数据占用磁盘空间信息
查询分布表数据量
如何查询表总条数
select count(*) from 表名;
select TABLE_SCHEMA,TABLE_NAME, TABLE_ROWS/2，STORAGE_SIZE/2 from performance_schema.tables where TABLE_SCHEMA='库名' and TABLE_NAME='表名'；
-- TABLE_ROWS：为表所有分片的条数和，当表分片有一个副本时，是表条数的2倍；当表分片有两个副本时，是表条数的3倍；
显示/设置服务器系统变量
- 显示8a集群的系统变量值
SELECT * from information_schema.global_variables where variable_name like 'pattern';
SHOW VARIABLES [LIKE 'pattern'];
- 设置系统变量值
SET [GLOBAL | SESSION] <variablename> = value
-- GLOBAL 全局变量值改变，只对新的session会话起作用。
-- SESSION默认为会话变量，可不写。
-- 开启本节点的trace日志，默认是设置 session 变量。
SHOW VARIABLES like 'gbase_sql_trace%';
SET gbase_sql_trace = on;
系统压缩模式查询
查询8a集群的默认压缩模式信息
SHOW global variables like '%compress%';
SELECT * from information_schema.global_variables where variable_name like '%compress%';
查询当前节点运行信息
查询当前节点正在运行的线程信息
-- 查看正在运行的线程
SHOW [FULL] PROCESSLIST;
select id,user,host,db,command,time,state,info from information_schema.processlist;
管理元数据库线程
-- 终止正在运行的线程（SQL语句)
KILL [CONNECTION | QUERY] thread_id;
-- CONNECTION参数：用于终止指定的thread_id 线程，默认参数。
-- QUERY参数： 中止连接当前执行的语句，但是不中止该连接本身。
-- 暂停正在运行的线程
PAUSE thread_id；
-- 继续已暂停的线程
CONTINUE thread_id；
显示语句错误信息
显示最后一条警告或错误信息
-- 显示由最后一个语句产生的错误，警告和注意信息。
SHOW WARNINGS;
-- 显示由最后一个语句产生的错误信息。
SHOW ERRORS;
[gbase@192 ~]$ gccli -uroot -p
Enter password:
GBase client 9.5.2.39.126761. Copyright (c) 2004-2023, GBase. All Rights Reserved.
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> drop table a;
ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'test.a'
gbase>
gbase> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Error | 1702 | gcluster table error: (GBA-02DD-0017) Unknown table 'test.a' |
+-------+------+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
显示集群节点信息
显示集群节点信息
--如果有SUPER 权限，可以看到所有节点信息。
gbase> SHOW NODES;
+------------+-----------------+-------+--------------+----------------+--------+-----------+
| Id | ip | name | primary part | duplicate part | status | datastate |
+------------+-----------------+-------+--------------+----------------+--------+-----------+
| 2331945152 | 192.168.254.138 | node1 | n1,n4 | n3,n5 | online | 0 |
| 2348722368 | 192.168.254.139 | node2 | n2,n5 | n1,n6 | online | 0 |
| 2365499584 | 192.168.254.140 | node3 | n3,n6 | n2,n4 | online | 0 |
+------------+-----------------+-------+--------------+----------------+--------+-----------+
3 rows in set (Elapsed: 00:00:00.00)
-- 显示集群中客户端正在访问节点的信息。
gbase> SHOW LOCAL NODE;
+------------+-----------------+--------------+--------+
| Id | ip | name | status |
+------------+-----------------+--------------+--------+
| 2331945152 | 192.168.254.138 | coordinator1 | ONLINE |
+------------+-----------------+--------------+--------+
1 row in set (Elapsed: 00:00:00.00)
查询当前系统信息
使用系统函数，查看当前系统信息
- version()：返回当前8a系统版本
- vc()：返回当前VC名字
- database()：返回当前数据库名字
- user()：返回当前用户名
- charset(str)：返回字符串参数使用的字符集
gbase> select version();
+-----------------+
| version() |
+-----------------+
| 9.5.2.39.126761 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select vc();
+---------+
| vc() |
+---------+
| vc00001 |
+---------+
1 row in set (Elapsed: 00:00:00.00)
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select database(),user(),charset('南大通用');
+------------+----------------+-------------------------+
| database() | user() | charset('南大通用') |
+------------+----------------+-------------------------+
| test | root@localhost | utf8 |
+------------+----------------+-------------------------+
1 row in set (Elapsed: 00:00:00.00)
使用 use 切换当前VC/数据库
- 切换到vc1虚拟集群：use vc vc1；
- 切换到test数据库：use test；
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
