GBase 8a支持用户级磁盘配额限制,通过设置用户的磁盘使用上限,限制所设置用户创建表的sys_tablespace 和 metadata 空间总和。超过配额后会报错The disk space of User 'XXXXX' has exceeded the limit value.
目录导航
参考
基于资源管控的,一组用户的磁盘控制,请参考 GBase 8a资源管控资源管理的介绍
提醒
- 空间配额限制是计算节点级的,不是整个集群的,所以要以节点空间评估。任何一个节点超过限制就会报错
- 当前版本(8.6.2.43.R33、9.5.2.43)尚未将元数据(metadata)计算在内,只有数据目录,而global hash索引是保存在元数据里的。不统计 gbase、gclusterdb、gctempdb 库各表的磁盘占用,
- 用户级磁盘限额只统计用户创建的 express 引擎表。不统计其它引擎的库表。
- 磁盘限额在 sql 执行开始时进行检查是否超额,执行过程中磁盘使用超额不检查不报错。
- 检查磁盘限额的 sql ,都是会增加占用磁盘大小的,对于简单的 select、delete、drop、shrink 等不会增加磁盘大小的 sql 不做超额检查。
环境
4个计算节点的集群。
集群版本:9.5.3.18.123926
操作系统:redhat 7.3 虚拟机
[gbase@gbase_rh7_003 ~]$ gcadmin
CLUSTER STATE: ACTIVE
VIRTUAL CLUSTER MODE: NORMAL
====================================
| GBASE GCWARE CLUSTER INFORMATION |
====================================
| NodeName | IpAddress | gcware |
------------------------------------
| gcware1 | 10.0.2.103 | OPEN |
------------------------------------
====================================================
| GBASE COORDINATOR CLUSTER INFORMATION |
====================================================
| NodeName | IpAddress | gcluster | DataState |
----------------------------------------------------
| coordinator1 | 10.0.2.103 | OPEN | 0 |
----------------------------------------------------
=========================================================================================================
| GBASE DATA CLUSTER INFORMATION |
=========================================================================================================
| NodeName | IpAddress | DistributionId | gnode | syncserver | DataState |
---------------------------------------------------------------------------------------------------------
| node1 | 10.0.2.103 | 2 | OPEN | OPEN | 0 |
---------------------------------------------------------------------------------------------------------
| node2 | 10.0.2.104 | 2 | OPEN | OPEN | 0 |
---------------------------------------------------------------------------------------------------------
| node3 | 10.0.2.105 | 2 | OPEN | OPEN | 0 |
---------------------------------------------------------------------------------------------------------
| node4 | 10.0.2.106 | 2 | OPEN | OPEN | 0 |
---------------------------------------------------------------------------------------------------------
[gbase@gbase_rh7_003 ~]$
用户配额限制
注意:配额只对该用户【创建】的表有效。
设置配额
通过create user时,或者grant时,给用户设置配额。
gbase> create user testq identified by 'testq' limit_storage_size=10K;
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
gbase> grant all on testdb.* to testq;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
修改配额
通过grant 方法可以修改配额。
gbase> grant all on testdb.* to testq limit_storage_size=10K;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
查看当前配额使用情况
查看元数据表
配额信息是定时统计到一个元数据表里。
select * from information_schema.gnodes_user_diskspace_usage where user='XXXXXX'
其中
- NODE_NAME 节点名字
- User 用户
- user_limit_storage_size 限制数额
- user_storage_size 当前使用数量(字节)
注意:使用量,是具体文件的大小,不包括目录的,所以对目录du的结果,与目录内所有文件的大小累加结果,是有差异的。
配额刷新定时参数
是由_gbase_storage_flush_interval控制,默认60,单位秒。
从项目实际情况考虑,强烈建议刷新时间提高,比如15分钟,1小时。太频繁的刷新,特别是该用户表非常多时,会额外浪费很多次磁盘的IO。
我们为了测试,将参数改成10秒。
gbase> show variables like '%_gbase_storage_flush_interval%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| _gbase_storage_flush_interval | 60 |
+-------------------------------+-------+
1 row in set (Elapsed: 00:00:00.01)
gbase> set global _gbase_storage_flush_interval=10;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
强制刷新配额信息
gbase> refresh user storage usage;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
测试样例
如下以testq用户,配额10K为样例,测试的结果。
测试表
就一个int的单列。用测试配额的用户testq登录,创建新表。
[gbase@gbase_rh7_003 ~]$ gccli -utestq -ptestq
GBase client 9.5.3.18.123926. Copyright (c) 2004-2020, GBase. All Rights Reserved.
gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> create table testq_1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
添加数据
gbase> insert into testq_1 values(1),(2),(3),(4);
Query OK, 4 rows affected (Elapsed: 00:00:00.09)
Records: 4 Duplicates: 0 Warnings: 0
gbase> select * from information_schema.gnodes_user_diskspace_usage where user='testq'\G
*************************** 1. row ***************************
NODE_NAME: node1
User: testq
user_limit_storage_size: 10K
user_storage_size: 38
*************************** 2. row ***************************
NODE_NAME: node2
User: testq
user_limit_storage_size: 10K
user_storage_size: 38
*************************** 3. row ***************************
NODE_NAME: node3
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
*************************** 4. row ***************************
NODE_NAME: node4
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
4 rows in set (Elapsed: 00:00:00.00)
gbase> insert into testq_1 select id+power(2,2) from testq_1;
Query OK, 4 rows affected (Elapsed: 00:00:00.09)
Records: 4 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,3) from testq_1;
Query OK, 8 rows affected (Elapsed: 00:00:00.08)
Records: 8 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,4) from testq_1;
Query OK, 16 rows affected (Elapsed: 00:00:00.08)
Records: 16 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,5) from testq_1;
Query OK, 32 rows affected (Elapsed: 00:00:00.08)
Records: 32 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,6) from testq_1;
Query OK, 64 rows affected (Elapsed: 00:00:00.08)
Records: 64 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,7) from testq_1;
Query OK, 128 rows affected (Elapsed: 00:00:00.08)
Records: 128 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,8) from testq_1;
Query OK, 256 rows affected (Elapsed: 00:00:00.08)
Records: 256 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,9) from testq_1;
Query OK, 512 rows affected (Elapsed: 00:00:00.08)
Records: 512 Duplicates: 0 Warnings: 0
gbase> insert into testq_1 select id+power(2,10) from testq_1;
Query OK, 1024 rows affected (Elapsed: 00:00:00.08)
Records: 1024 Duplicates: 0 Warnings: 0
查看已经使用的配额
从各个节点看,其总和已经超过了10K。后面继续增加数据。
gbase> select * from information_schema.gnodes_user_diskspace_usage where user='testq'\G
*************************** 1. row ***************************
NODE_NAME: node1
User: testq
user_limit_storage_size: 10K
user_storage_size: 6212
*************************** 2. row ***************************
NODE_NAME: node2
User: testq
user_limit_storage_size: 10K
user_storage_size: 6212
*************************** 3. row ***************************
NODE_NAME: node3
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
*************************** 4. row ***************************
NODE_NAME: node4
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
4 rows in set (Elapsed: 00:00:00.01)
测试配额限制点
如下测试可以看到,可以再次增加数据,也就是配额并不是所有节点的总量。
再次增加后,单个节点的数量(12356)已经超过配额(10K)了。
gbase> insert into testq_1 select id+power(2,11) from testq_1;
Query OK, 2048 rows affected (Elapsed: 00:00:00.09)
Records: 2048 Duplicates: 0 Warnings: 0
gbase> select * from information_schema.gnodes_user_diskspace_usage where user='testq'\G
*************************** 1. row ***************************
NODE_NAME: node1
User: testq
user_limit_storage_size: 10K
user_storage_size: 12356
*************************** 2. row ***************************
NODE_NAME: node2
User: testq
user_limit_storage_size: 10K
user_storage_size: 12356
*************************** 3. row ***************************
NODE_NAME: node3
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
*************************** 4. row ***************************
NODE_NAME: node4
User: testq
user_limit_storage_size: 10K
user_storage_size: 0
4 rows in set (Elapsed: 00:00:00.01)
测试超过配额
超过配额后,再次增加数据,将直接报错:The disk space of User 'testq' has exceeded the limit value.(Usage: 12356, Limit: 10240)
gbase> insert into testq_1 select id+power(2,12) from testq_1;
ERROR 1708 (HY000): [10.0.2.103:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-700) Gbase general error: (gns_host: 10.0.2.103) The disk space of User 'testq' has exceeded the limit value.(Usage: 12356, Limit: 10240)
SQL: SELECT /*10.0.2.103_142_22_2020-12-10_15:03:56*/ /*+ TID('196838') */ (`vcname000001.testdb.testq_1`.`id` + pow(2, 12)) AS `id+power(2,12)` FROM `testdb`.`testq_1_n1` `vcname000001.testdb.testq_1` target into server (HOST '10.0.2.103,10.0.2.104', PORT 5050, USER 'testq', PASSWORD '', DATABAS
gbase>
其它
无配额用户影响
测试发现,无配额用户,并不会定时统计占用量。
gbase> select * from information_schema.gnodes_user_diskspace_usage where user='gbase'\G
*************************** 1. row ***************************
NODE_NAME: node1
User: gbase
user_limit_storage_size:
user_storage_size: 0
*************************** 2. row ***************************
NODE_NAME: node2
User: gbase
user_limit_storage_size:
user_storage_size: 0
*************************** 3. row ***************************
NODE_NAME: node3
User: gbase
user_limit_storage_size:
user_storage_size: 0
*************************** 4. row ***************************
NODE_NAME: node4
User: gbase
user_limit_storage_size:
user_storage_size: 0
4 rows in set (Elapsed: 00:00:00.00)