GBase 8a MPP Cluster集群数据库系统表的查询


使用root用户登录gbase8a数据库

[gbase@h1 ~]$ gccli -uroot -proot123

GBase client 9.5.2.39.126761. Copyright (c) 2004-2021, GBase.  All Rights Reserved.

gbase> 
1、查看数据库
gbase> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| gbase              |
| gctmpdb            |
| gclusterdb         |
| mydb               |
| test               |
+--------------------+
7 rows in set (Elapsed: 00:00:00.00)
2、查询数据库版本
gbase> select version();
+-----------------+
| version()       |
+-----------------+
| 9.5.2.39.126761 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)
3、查询当前登录用户
gbase> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (Elapsed: 00:00:00.00)
4、切换information_schema数据库(查询数据库信息)
gbase> use information_schema;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
(1)查询数据库中一共有多少个表
--在information_schema数据库中查询
gbase> select count(*) from tables;
+----------+
| count(*) |
+----------+
|      126 |
+----------+
1 row in set (Elapsed: 00:00:00.00)
(2)查询某个数据库下有多少张表
--在information_schema数据库中查询
gbase> select table_name from tables where table_schema = 'mydb';
+------------+
| table_name |
+------------+
| course     |
| sc         |
| sc2        |
| student    |
| student2   |
| v_sc       |
+------------+
6 rows in set (Elapsed: 00:00:00.00)
(3)查询数据库中有多少个视图
--在information_schema数据库中查询
gbase> select table_name from tables where table_type like 'view';
+------------+
| table_name |
+------------+
| v_sc       |
+------------+
1 row in set (Elapsed: 00:00:00.01)
5、切换到performance_schema数据库(查询数据性能资源库信息)
gbase> use performance_schema;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> 
(1)查看系统表
gbase> show tables;
+------------------------------+
| Tables_in_performance_schema |
+------------------------------+
| DISK_USAGE_INFO              | #该表记录本节点所占用磁盘空间大小
| CLUSTER_DISK_USAGE_INFO      | #该表记录集群所有节点所占用磁盘空间大小
| CACHE_USAGE_INFO             |
| CACHE_CELL_STATUS_INFO       |
| HEAP_USAGE_INFO              |
| SESSION_MEMORY_USAGE_INFO    |
| MEMORY_USAGE_INFO            |
| TABLES                       |
| CLUSTER_MONIT_INFO           |
| MONIT_INFO                   |
+------------------------------+
10 rows in set (Elapsed: 00:00:00.00)
(2)查询DISK_USAGE_INFO表
gbase> select * from DISK_USAGE_INFO;
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE         | PATH                                 | DIR_SIZE | FILESYTEM | SIZE        | USED       | AVAIL       | PCT |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| h1   | datadir          | /opt/gcluster/userdata/gcluster/     |  7094272 | rootfs    | 41100877824 | 4701929472 | 36398948352 | 11% |
| h1   | gbase_cache_data | /opt/gcluster/tmpdata/cache_gcluster |        0 | rootfs    | 41100877824 | 4701929472 | 36398948352 | 11% |
| h1   | logdir           | /opt/gcluster/log/gcluster/          |    81920 | rootfs    | 41100877824 | 4701929472 | 36398948352 | 11% |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
3 rows in set (Elapsed: 00:00:00.01)
(3)查询CLUSTER_DISK_USAGE_INFO表
gbase> select * from CLUSTER_DISK_USAGE_INFO;
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE         | PATH                                 | DIR_SIZE | FILESYTEM | SIZE        | USED       | AVAIL       | PCT |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| h1   | datadir          | /opt/gnode/userdata/gbase/           |  6361088 | rootfs    | 41100877824 | 4703055872 | 36397821952 | 11% |
| h1   | gbase_cache_data | /opt/gnode/tmpdata/cache_gbase       |        0 | rootfs    | 41100877824 | 4703055872 | 36397821952 | 11% |
| h1   | logdir           | /opt/gnode/log/gbase/                |   249856 | rootfs    | 41100877824 | 4703055872 | 36397821952 | 11% |
| h2   | datadir          | /opt/gnode/userdata/gbase/           |  6381568 | rootfs    | 41100877824 | 4350029824 | 36750848000 | 11% |
| h2   | gbase_cache_data | /opt/gnode/tmpdata/cache_gbase       |        0 | rootfs    | 41100877824 | 4350029824 | 36750848000 | 11% |
| h2   | logdir           | /opt/gnode/log/gbase/                |   262144 | rootfs    | 41100877824 | 4350029824 | 36750848000 | 11% |
| h3   | datadir          | /opt/gnode/userdata/gbase/           |  6324224 | rootfs    | 41100877824 | 4360601600 | 36740276224 | 11% |
| h3   | gbase_cache_data | /opt/gnode/tmpdata/cache_gbase       |        0 | rootfs    | 41100877824 | 4360601600 | 36740276224 | 11% |
| h3   | logdir           | /opt/gnode/log/gbase/                |   262144 | rootfs    | 41100877824 | 4360601600 | 36740276224 | 11% |
| h1   | datadir          | /opt/gcluster/userdata/gcluster/     |  7094272 | rootfs    | 41100877824 | 4703035392 | 36397842432 | 11% |
| h1   | gbase_cache_data | /opt/gcluster/tmpdata/cache_gcluster |        0 | rootfs    | 41100877824 | 4703035392 | 36397842432 | 11% |
| h1   | logdir           | /opt/gcluster/log/gcluster/          |    81920 | rootfs    | 41100877824 | 4703035392 | 36397842432 | 11% |
| h2   | datadir          | /opt/gcluster/userdata/gcluster/     |  7094272 | rootfs    | 41100877824 | 4350009344 | 36750868480 | 11% |
| h2   | gbase_cache_data | /opt/gcluster/tmpdata/cache_gcluster |        0 | rootfs    | 41100877824 | 4350009344 | 36750868480 | 11% |
| h2   | logdir           | /opt/gcluster/log/gcluster/          |    73728 | rootfs    | 41100877824 | 4350009344 | 36750868480 | 11% |
| h3   | datadir          | /opt/gcluster/userdata/gcluster/     |  7094272 | rootfs    | 41100877824 | 4360577024 | 36740300800 | 11% |
| h3   | gbase_cache_data | /opt/gcluster/tmpdata/cache_gcluster |        0 | rootfs    | 41100877824 | 4360577024 | 36740300800 | 11% |
| h3   | logdir           | /opt/gcluster/log/gcluster/          |    77824 | rootfs    | 41100877824 | 4360577024 | 36740300800 | 11% |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
18 rows in set (Elapsed: 00:00:00.04)
(4)查询某个节点占用磁盘空间大小
gbase> select * from CLUSTER_DISK_USAGE_INFO where host = 'h2';
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| HOST | DIR_TYPE         | PATH                                 | DIR_SIZE | FILESYTEM | SIZE        | USED       | AVAIL       | PCT |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
| h2   | datadir          | /opt/gnode/userdata/gbase/           |  6381568 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
| h2   | gbase_cache_data | /opt/gnode/tmpdata/cache_gbase       |        0 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
| h2   | logdir           | /opt/gnode/log/gbase/                |   262144 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
| h2   | datadir          | /opt/gcluster/userdata/gcluster/     |  7094272 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
| h2   | gbase_cache_data | /opt/gcluster/tmpdata/cache_gcluster |        0 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
| h2   | logdir           | /opt/gcluster/log/gcluster/          |    73728 | rootfs    | 41100877824 | 4349988864 | 36750888960 | 11% |
+------+------------------+--------------------------------------+----------+-----------+-------------+------------+-------------+-----+
6 rows in set (Elapsed: 00:00:00.03)
6、切换到gbase数据库(查询相关系统表)
gbase> use gbase;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
(1)例如查询table开头的系统表
gbase> show tables like 'table%';
+--------------------------+
| Tables_in_gbase (table%) |
+--------------------------+
| table_distribution       | #查询哪些表是hash分布表,那些表是复制表以及随机分布表
| tables_priv              |
+--------------------------+
2 rows in set (Elapsed: 00:00:00.00)
(2)查询table_distribution表结构
gbase> desc table_distribution;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| index_name           | varchar(128)  | NO   | PRI | NULL    |       |
| dbName               | varchar(64)   | NO   |     | NULL    |       |
| tbName               | varchar(64)   | NO   |     | NULL    |       |
| isReplicate          | varchar(3)    | NO   |     | YES     |       |
| hash_column          | varchar(4096) | YES  |     | NULL    |       |
| lmt_storage_size     | bigint(20)    | YES  |     | NULL    |       |
| table_storage_size   | bigint(20)    | YES  |     | NULL    |       |
| is_nocopies          | varchar(3)    | NO   |     | YES     |       |
| data_distribution_id | bigint(8)     | NO   |     | NULL    |       |
| vc_id                | varchar(64)   | NO   | PRI | NULL    |       |
| mirror_vc_id         | varchar(64)   | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+
11 rows in set (Elapsed: 00:00:00.00)
(3)从table_distribution中查询数据库中表的类型
gbase> select dbName,tbName,isReplicate,hash_column from table_distribution;
+------------+--------------------+-------------+-------------+
| dbName     | tbName             | isReplicate | hash_column |
+------------+--------------------+-------------+-------------+
| gclusterdb | rebalancing_status | NO          | index_name  |
| gclusterdb | dual               | YES         | NULL        |
| test       | t                  | NO          | NULL        |
| mydb       | student            | NO          | sno         | #hash分布表,hash列是sno
| mydb       | course             | YES         | NULL        | #复制表
| mydb       | sc                 | NO          | NULL        | #既不是hash分布表也不是复制表的就是随机分布表
| mydb       | sc2                | NO          | sno         |
| mydb       | student2           | NO          | sno         |
+------------+--------------------+-------------+-------------+
8 rows in set (Elapsed: 00:00:00.00)
(4)查看本节点正在运行哪些命令
gbase> show full processlist;
+-----+------+-----------------+---------------------+-------+---------+-------+-----------------------------+-----------------------+
| Id  | Tid  | User            | Host                | db    | Command | Time  | State                       | Info                  |
+-----+------+-----------------+---------------------+-------+---------+-------+-----------------------------+-----------------------+
|   1 | 2773 | event_scheduler | localhost           | NULL  | Daemon  | 12541 | Waiting for next activation | NULL                  |
|   9 | 2793 | gbase           | 192.168.27.21:45048 | NULL  | Sleep   |  3017 |                             | NULL                  |
|  10 | 2797 | gbase           | 192.168.27.22:37414 | NULL  | Sleep   |  3017 |                             | NULL                  |
| 126 | 4420 | root            | 192.168.27.1:8891   | NULL  | Sleep   |  5370 |                             | NULL                  |
| 127 | 4422 | root            | 192.168.27.1:8892   | NULL  | Sleep   |  5147 |                             | NULL                  |
| 137 | 4487 | user1           | 192.168.27.1:8938   | mydb  | Sleep   |  5144 |                             | NULL                  |
| 138 | 4489 | user1           | 192.168.27.1:8939   | mydb  | Sleep   |  5112 |                             | NULL                  |
| 139 | 4491 | user1           | 192.168.27.1:8940   | mydb  | Sleep   |  5112 |                             | NULL                  |
| 157 | 5102 | root            | localhost           | gbase | Query   |     0 | NULL                        | show full processlist |
+-----+------+-----------------+---------------------+-------+---------+-------+-----------------------------+-----------------------+
9 rows in set (Elapsed: 00:00:00.00)
--可以通过id对进程进行kill或暂停等相关操作
7、切换mydb进行测试
gbase> use mydb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
(1)删除一个不存在的表
gbase> drop table a;
ERROR 1702 (HY000): gcluster table error: (GBA-02DD-0017) Unknown table 'mydb.a'
(2)通过show errors;命令查看系统报错信息
gbase> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message                                                      |
+-------+------+--------------------------------------------------------------+
| Error | 1702 | gcluster table error: (GBA-02DD-0017) Unknown table 'mydb.a' |
+-------+------+--------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
8、查看系统变量的值show variables
#比如查看压缩的系统变量值
gbase> show variables like '%compress%';
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| _gbase_delete_filter_compressed | OFF        |
| _gbase_tx_log_compress          | ON         |
| gbase_compress_level            | 0          |
| gbase_compress_method           | No Setting |
| gbase_compression_num_method    | 5          | #数值型的压缩方式为5
| gbase_compression_sampling      | 1          |
| gbase_compression_str_method    | 5          | #字符型的压缩方式为5
| have_compress                   | YES        |
| slave_compressed_protocol       | OFF        |
+---------------------------------+------------+
9 rows in set (Elapsed: 00:00:00.00)
#默认的就是55压缩
9、查看gbase8a个节点信息
(1)查看所有节点信息
gbase> show nodes;
+-----------+---------------+-------+--------------+----------------+--------+-----------+
| Id        | ip            | name  | primary part | duplicate part | status | datastate |
+-----------+---------------+-------+--------------+----------------+--------+-----------+
| 337356992 | 192.168.27.20 | node1 | n1,n4        | n3,n5          | online |         0 |
| 354134208 | 192.168.27.21 | node2 | n2,n5        | n1,n6          | online |         0 |
| 370911424 | 192.168.27.22 | node3 | n3,n6        | n2,n4          | online |         0 |
+-----------+---------------+-------+--------------+----------------+--------+-----------+
3 rows in set (Elapsed: 00:00:00.00)
(2)查看当前节点信息
gbase> show local node;
+-----------+---------------+--------------+--------+
| Id        | ip            | name         | status |
+-----------+---------------+--------------+--------+
| 337356992 | 192.168.27.20 | coordinator1 | ONLINE |
+-----------+---------------+--------------+--------+
1 row in set (Elapsed: 00:00:00.00)
(3)在gccli中使用gcadmin查看节点信息
gbase> system gcadmin;
CLUSTER STATE:         ACTIVE
VIRTUAL CLUSTER MODE:  NORMAL

================================================================
|            GBASE COORDINATOR CLUSTER INFORMATION             |
================================================================
|   NodeName   |   IpAddress   | gcware | gcluster | DataState |
----------------------------------------------------------------
| coordinator1 | 192.168.27.20 |  OPEN  |   OPEN   |     0     |
----------------------------------------------------------------
| coordinator2 | 192.168.27.21 |  OPEN  |   OPEN   |     0     |
----------------------------------------------------------------
| coordinator3 | 192.168.27.22 |  OPEN  |   OPEN   |     0     |
----------------------------------------------------------------
=========================================================================================================
|                                    GBASE DATA CLUSTER INFORMATION                                     |
=========================================================================================================
| NodeName |                IpAddress                 | DistributionId | gnode | syncserver | DataState |
---------------------------------------------------------------------------------------------------------
|  node1   |              192.168.27.20               |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node2   |              192.168.27.21               |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node3   |              192.168.27.22               |       1        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
  • 5
    点赞
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值