Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Schema Space Control

Updated on 2022-07-29 GMT+08:00

Context

The GaussDB(DWS) storage resource management manages and controls schema space. On one hand, the space of a single instance can be managed and controlled, preventing the database from being read-only and the disk from being full. On the other hand, space management and control are decoupled from users and queues, enabling users without permissions to manage and control space easier.

You can specify the storage space during schema creation to manage storage. This feature is only applicable to the storage of permanent tables (PERM SPACE). Schema storage space management supports storage management of table data. When a schema has a space limit, if the total table data in the schema exceeds the space limit during service execution, a service error occurs.

Prerequisites

  • enable_perm_space has been set to on, indicating that storage space control is enabled.
  • use_workload_manager has been set to on, indicating that load management control is enabled.

Procedure

  1. Create a schema named schema1 and set the permanent tablespace limit to 100 GB:

    1
    CREATE SCHEMA schema1 WITH PERM SPACE '100G';
    

  2. Change the permanent tablespace limit of schema1 to unlimited:

    1
    ALTER SCHEMA schema1 WITH PERM SPACE 'unlimited';
    

  3. Check whether the permanent tablespace limit has been set for schema1:

    1
    2
    3
    4
    5
    SELECT * FROM PG_NAMESPACE WHERE NSPNAME = 'schema1';
     nspname | nspowner | nsptimeline | nspacl | permspace | usedspace
    ---------+----------+-------------+--------+-----------+-----------
     schema1 |       10 |           0 |        |        -1 |         0
    (1 row)
    

NOTE:
  • The schema space information is the storage space information of a single instance. The CN and DN instances are calculated separately and do not affect each other. In this way, job skew can be prevented.
  • If the space used by a job exceeds the schema space limit, the job will be canceled and the information "out of schema's perm space limit" will be recorded in the log file in the pg_log directory.
  • When the max_query_retry_times value of unlogged table is greater than 0, that is, the SQL retry function is enabled, the table is considered as a common table and the used space is recorded in the permanent tablespace of the schema (PERM SPACE). When the value of max_query_retry_times is 0, that is, the SQL retry function is disabled, the table is considered as a temporary table and the used space is not recorded in the schema permanent tablespace. If max_query_retry_times is switched during the use of an unlogged table, the result of determining whether the unlogged table is a temporary or common table will be different before and after the switchover. In addition, when data is added to or deleted from the corresponding table, the permanent data space in the corresponding schema changes abnormally, and the value is different from the actual physical space. If a primary/standby switchover occurs during the use of an unlogged table, data in the unlogged table will be lost. As a result, the amount of lost data in the corresponding schema space is different from that in the actual physical space.
  • In the PG_TOTAL_SCHEMA_INFO view, there may be a deviation (about ±5%) between the used schema space and the actually used disk space. You can run the select pgxc_wlm_readjust_schema_space() function to correct the deviation. The correction function cannot be re-entered. Otherwise, the result is incorrect. If data is inserted into the table during the function correction, the statistics result may be incorrect.
  • CUDESC, CUDESC_INDEX, DELTA, CUDESC_PART and CUDESC_PART_INDEX in column-based tables are auxiliary tables. The space of these auxiliary tables is recorded in the CSTORE schema space. The storage size of the CBTREE index in the column-based table is recorded to the schema in the primary table queue. But the storage size of the PSORT index is still recorded to the CSTORE schema space. The storage sizes of TOAST table and TOAST are recorded in the PG_TOAST schema space.
  • The storage size of the table in tablespace is recorded in the schema where the table is located.
  • Schema space control ignores template0 and template1 and does not control schemas in these two template libraries.
  • In the scale-in or scale-out scenario, after data re-distribution, the schema space statistics will be inaccurate. You need to invoke the calibration function to recalibrate them.
  • In the scenario where the distribution column of a table is modified, the schema space statistics will be inaccurate. You need to invoke the calibration function to recalibrate them.
  • When VACUUM FULL or REINDEX is executed on the table, if a primary/standby switchover occurs due to a primary DN fault, the schema space statistics will be inaccurate. In this case, the calibration function needs to be invoked to calibrate the table again.
  • A temporary schema is created during temporary table creation. The name of the temporary schema is similar to pg_temp_* and pg_toast_temp_*. The space of the temporary schema is not limited, and the temporary schema information is not displayed in the view of PG_TOTAL_SCHEMA_INFO.
  • For an upgrade from a version earlier than GaussDB(DWS) 8.1.0.3 to 8.1.0.3 or later, the perm space information collected in PG_TOTAL_SCHEMA_INFO does not include the space used before the upgrade. After the upgrade, you can run the select pgxc_wlm_readjust_schema_space() function to collect statistics on the space.
  • GaussDB(DWS) provides the PGXC_TOTAL_SCHEMA_INFO and PGXC_TOTAL_SCHEMA_INFO_ANALYZE views for querying the schema space information of the cluster. The PGXC_TOTAL_SCHEMA_INFO view provides the schema space information of all instances in the cluster. The PGXC_TOTAL_SCHEMA_INFO_ANALYZE view summarizes and analyzes the schema space information of all instances in the cluster, and provides the total value, average value, skew ratio, maximum value, minimum value, maximum instance name, and minimum instance name of the cluster. In a logical cluster, the preceding two views provide the schema space information and analysis results of all logical clusters. If you focus only on a specific logical cluster, you can use the PGXC_WLM_GET_SCHEMA_SPACE('lccluster1') and PGXC_WLM_ANALYZE_SCHEMA_SPACE('lccluster1') functions provided by GaussDB(DWS) to obtain the schema space information of the logical cluster lccluster1.

Viewing Schema Space Information

select * from pg_namespace;
 nspname            | nspowner | nsptimeline |         nspacl          | permspace | usedspace
--------------------+----------+-------------+-------------------------+-----------+-----------
 public             |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 dbms_redact        |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 schema1            |       10 |           0 |                         |        -1 |         0
 dbms_random        |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 pg_toast           |       10 |           0 |                         |        -1 |   2326528
 testt              |    28726 |           0 |                         |        -1 |      8192
 dbms_om            |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 dbms_job           |       10 |           0 | {lys1=UC/lys1,=UC/lys1} |        -1 |         0
 sys                |       10 |           0 |                         |        -1 |         0
 information_schema |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |    294912
 utl_file           |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 utl_raw            |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 dbms_output        |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 dbms_sql           |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 dbms_lob           |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |         0
 cstore             |       10 |           0 |                         |        -1 |    696320
 user1              |    16390 |           0 |                         |  22405120 |      8192
 pg_catalog         |       10 |           0 | {lys1=UC/lys1,=U/lys1}  |        -1 |  24903680
(18 rows)

select * from pg_total_schema_info;
 schemaid |     schemaname     | databaseid | databasename | usedspace | permspace
----------+--------------------+------------+--------------+-----------+-----------
       11 | pg_catalog         |      16389 | test         |         0 |        -1
       99 | pg_toast           |      16389 | test         |         0 |        -1
      100 | cstore             |      16389 | test         |         0 |        -1
     2200 | public             |      15095 | gaussdb     |         0 |        -1
     2200 | public             |      16389 | test         |         0 |        -1
     4230 | dbms_redact        |      15095 | gaussdb     |         0 |        -1
     3987 | dbms_om            |      15095 | gaussdb     |         0 |        -1
     3988 | dbms_job           |      15095 | gaussdb     |         0 |        -1
     3987 | dbms_om            |      16389 | test         |         0 |        -1
     3988 | dbms_job           |      16389 | test         |         0 |        -1
     4230 | dbms_redact        |      16389 | test         |         0 |        -1
    11693 | sys                |      15095 | gaussdb     |         0 |        -1
    28714 | test1              |      15095 | gaussdb     |         0 |        -1
    28715 | test3              |      15095 | gaussdb     |         0 |        -1
    28728 | redisuser          |      15095 | gaussdb     |         0 |        -1
    28730 | testt              |      15095 | gaussdb     |      8192 |        -1
    28766 | redisuser1         |      15095 | gaussdb     |         0 |        -1
    28798 | schema1            |      15095 | gaussdb     |         0 |        -1
    11693 | sys                |      16389 | test         |         0 |        -1
    14147 | information_schema |      15095 | gaussdb     |    294912 |        -1
    14490 | utl_file           |      15095 | gaussdb     |         0 |        -1
    14515 | utl_raw            |      15095 | gaussdb     |         0 |        -1
    14508 | dbms_output        |      15095 | gaussdb     |         0 |        -1
    14512 | dbms_random        |      15095 | gaussdb     |         0 |        -1
    14520 | dbms_sql           |      15095 | gaussdb     |         0 |        -1
    14547 | dbms_lob           |      15095 | gaussdb     |         0 |        -1
    14147 | information_schema |      16389 | test         |         0 |        -1
    14490 | utl_file           |      16389 | test         |         0 |        -1
    14508 | dbms_output        |      16389 | test         |         0 |        -1
    14515 | utl_raw            |      16389 | test         |         0 |        -1
    14512 | dbms_random        |      16389 | test         |         0 |        -1
    14520 | dbms_sql           |      16389 | test         |         0 |        -1
    14547 | dbms_lob           |      16389 | test         |         0 |        -1
       11 | pg_catalog         |      15095 | gaussdb     |  22405120 |  22405120
       99 | pg_toast           |      15095 | gaussdb     |   2326528 |        -1
      100 | cstore             |      15095 | gaussdb     |    696320 |        -1
    16392 | user1              |      15095 | gaussdb     |      8192 |        -1
(37 rows)

select * from  pgxc_total_schema_info;
     schemaname     | schemaid | databasename | databaseid |   nodename   |  nodegroup   | usedspace | permspace
--------------------+----------+--------------+------------+--------------+--------------+-----------+-----------
 pg_catalog         |       11 | test1        |      16384 | datanode1    | installation |   9469952 |        -1
 public             |     2200 | gaussdb     |      15253 | datanode1    | installation |  25280512 |        -1
 pg_toast           |       99 | test1        |      16384 | datanode1    | installation |   1859584 |        -1
 cstore             |      100 | test1        |      16384 | datanode1    | installation |         0 |        -1
 data_redis         |    18106 | gaussdb     |      15253 | datanode1    | installation |    655360 |        -1
 data_redis         |    18116 | test1        |      16384 | datanode1    | installation |         0 |        -1
 public             |     2200 | test1        |      16384 | datanode1    | installation |     16384 |        -1
 dbms_om            |     3987 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_job           |     3988 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_om            |     3987 | test1        |      16384 | datanode1    | installation |         0 |        -1
 dbms_job           |     3988 | test1        |      16384 | datanode1    | installation |         0 |        -1
 sys                |    11693 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 sys                |    11693 | test1        |      16384 | datanode1    | installation |         0 |        -1
 utl_file           |    14644 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 utl_raw            |    14669 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_sql           |    14674 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_output        |    14662 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_random        |    14666 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 dbms_lob           |    14701 | gaussdb     |      15253 | datanode1    | installation |         0 |        -1
 information_schema |    14300 | gaussdb     |      15253 | datanode1    | installation |    294912 |        -1
 information_schema |    14300 | test1        |      16384 | datanode1    | installation |    294912 |        -1
 utl_file           |    14644 | test1        |      16384 | datanode1    | installation |         0 |        -1
 dbms_output        |    14662 | test1        |      16384 | datanode1    | installation |         0 |        -1
 dbms_random        |    14666 | test1        |      16384 | datanode1    | installation |         0 |        -1
 utl_raw            |    14669 | test1        |      16384 | datanode1    | installation |         0 |        -1
 dbms_sql           |    14674 | test1        |      16384 | datanode1    | installation |         0 |        -1
 dbms_lob           |    14701 | test1        |      16384 | datanode1    | installation |         0 |        -1
 pg_catalog         |       11 | gaussdb     |      15253 | datanode1    | installation |  13017088 |        -1
 redisuser          |    16387 | gaussdb     |      15253 | datanode1    | installation |    630784 |        -1
 pg_toast           |       99 | gaussdb     |      15253 | datanode1    | installation |   3080192 |        -1
 cstore             |      100 | gaussdb     |      15253 | datanode1    | installation |   2408448 |        -1
 pg_catalog         |       11 | test1        |      16384 | datanode2    | installation |   9469952 |        -1
 public             |     2200 | gaussdb     |      15253 | datanode2    | installation |  25214976 |        -1
 pg_toast           |       99 | test1        |      16384 | datanode2    | installation |   1859584 |        -1
 cstore             |      100 | test1        |      16384 | datanode2    | installation |         0 |        -1
 data_redis         |    18106 | gaussdb     |      15253 | datanode2    | installation |    655360 |        -1
 data_redis         |    18116 | test1        |      16384 | datanode2    | installation |         0 |        -1
 public             |     2200 | test1        |      16384 | datanode2    | installation |     16384 |        -1
 dbms_om            |     3987 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_job           |     3988 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_om            |     3987 | test1        |      16384 | datanode2    | installation |         0 |        -1
 dbms_job           |     3988 | test1        |      16384 | datanode2    | installation |         0 |        -1
 sys                |    11693 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 sys                |    11693 | test1        |      16384 | datanode2    | installation |         0 |        -1
 utl_file           |    14644 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 utl_raw            |    14669 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_sql           |    14674 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_output        |    14662 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_random        |    14666 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 dbms_lob           |    14701 | gaussdb     |      15253 | datanode2    | installation |         0 |        -1
 information_schema |    14300 | gaussdb     |      15253 | datanode2    | installation |    294912 |        -1
 information_schema |    14300 | test1        |      16384 | datanode2    | installation |    294912 |        -1
 utl_file           |    14644 | test1        |      16384 | datanode2    | installation |         0 |        -1
 dbms_output        |    14662 | test1        |      16384 | datanode2    | installation |         0 |        -1
 dbms_random        |    14666 | test1        |      16384 | datanode2    | installation |         0 |        -1

select * from  pgxc_total_schema_info_analyze;
     schemaname     | databasename |  nodegroup   | total_value | avg_value | skew_percent |                  extend_info
--------------------+--------------+--------------+-------------+-----------+--------------+-----------------------------------------------
 pg_catalog         | test1        | installation |    56819712 |   9469952 |            0 | min:9469952 datanode1,max:9469952 datanode1
 public             | gaussdb     | installation |   150495232 |  25082538 |            0 | min:24903680 datanode6,max:25280512 datanode1
 pg_toast           | test1        | installation |    11157504 |   1859584 |            0 | min:1859584 datanode1,max:1859584 datanode1
 cstore             | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 data_redis         | gaussdb     | installation |     1966080 |    327680 |           50 | min:0 datanode4,max:655360 datanode1
 data_redis         | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 public             | test1        | installation |       98304 |     16384 |            0 | min:16384 datanode1,max:16384 datanode1
 dbms_om            | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_job           | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_om            | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_job           | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 sys                | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 sys                | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 utl_file           | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 utl_raw            | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_sql           | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_output        | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_random        | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_lob           | gaussdb     | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 information_schema | gaussdb     | installation |     1769472 |    294912 |            0 | min:294912 datanode1,max:294912 datanode1
 information_schema | test1        | installation |     1769472 |    294912 |            0 | min:294912 datanode1,max:294912 datanode1
 utl_file           | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_output        | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_random        | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 utl_raw            | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_sql           | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 dbms_lob           | test1        | installation |           0 |         0 |            0 | min:0 datanode1,max:0 datanode1
 pg_catalog         | gaussdb     | installation |    75210752 |  12535125 |            3 | min:12091392 datanode4,max:13017088 datanode1
 redisuser          | gaussdb     | installation |     1884160 |    314026 |           50 | min:16384 datanode4,max:630784 datanode1
 pg_toast           | gaussdb     | installation |    17154048 |   2859008 |            7 | min:2637824 datanode4,max:3080192 datanode1
 cstore             | gaussdb     | installation |    15294464 |   2549077 |            5 | min:2408448 datanode1,max:2703360 datanode6
(31 rows)

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback