Help Center>
Data Warehouse Service (DWS)>
Developer Guide (Kuala Lumpur Region)>
Resource Load Management>
Schema Space Control
Updated on 2022-08-16 GMT+08:00
Schema Space Control
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
- Create a schema named schema1 and set the permanent tablespace limit to 100 GB:
1
CREATE SCHEMA schema1 WITH PERM SPACE '100G';
- Change the permanent tablespace limit of schema1 to unlimited:
1
ALTER SCHEMA schema1 WITH PERM SPACE 'unlimited';
- 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)
- 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)
Parent topic: Resource Load Management
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
