What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard data warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

Checking the Disk Usage
Fault Scenarios
Scenario 1: Clearing Data When the Disk Usage Is High
Scenario 2: Disk Skew and Clearing Skew Tables
Scenario 3: Cluster Read-only.
Space Management and Control
Subscribing to Disk Space Alarms

Show all

Help Center/ GaussDB(DWS)/ Troubleshooting/ Cluster Performance/ Solution to High Disk Usage and Cluster Read-Only

Solution to High Disk Usage and Cluster Read-Only

Updated on 2025-01-06 GMT+08:00

View PDF

Checking the Disk Usage

GaussDB(DWS) disk space is a high-value resource for users. It is closely related to cluster availability. Therefore, you need to pay closely monitor the available disk space and respond promptly when necessary. Note that in this context, "disks" specifically refers to data disks.

To check the disk space, perform the following steps:

Log in to the GaussDB(DWS) console. Click Clusters on the left navigation pane. In the cluster list, click Monitoring on the right of the row that contains the desired cluster. The Monitoring page is displayed.
Choose Monitoring > Node Monitoring. On the displayed page, choose the Disks tab. You can click on the right to sort the records based on disk usage.

To identify a data disk, check the disk capacity. If the disk capacity equals to the purchased capacity, the disk is a data disk.

Fault Scenarios

Scenario 1: High Disk Usage: The usage of all disks or more than half of the disks in the current cluster is greater than or equal to 70%.
Scenario 2: Disk Skew: The difference between the highest usage and the lowest usage is greater than or equal to 10%.
Scenario 3: Cluster Read-only. (The current read-only threshold is that the usage of a single data disk is greater than or equal to 90%.)

During routine processing, the database administrator can use the Space Management and Control to identify and block abnormal workloads to avoid the preceding scenarios.

NOTICE:

Read-only is a self-protection mechanism of the GaussDB(DWS) system. It prevents GaussDB(DWS) instance startup failures caused by 100% disk usage.
In the preceding scenarios, the DMS sends alarm notifications. (The alarm threshold is 80%, which can be configured. For details, see Alarm Rules.)
In scenario 1, you can use the alarm subscription function to receive SMS messages or emails when the disk usage exceeds 70% or 75%. This allows you to clear data in advance. For details, see Subscribing to Disk Space Alarms.

Scenario 1: Clearing Data When the Disk Usage Is High

Periodically delete dirty data based on the query results. The method varies according to the cluster version.

8.1.3 and later versions: Use the Intelligent O&M function on the management console to automatically clear dirty data.

Log in to the GaussDB(DWS) management console.
Click the name of the target cluster.
In the navigation pane, choose Intelligent O&M.
Click the O&M Plan tab. Click Add O&M Task.
The Add O&M Task page is displayed.
- Select Vacuum for O&M Task.
- Set Scheduling Mode to Auto. GaussDB(DWS) automatically scans tables that require VACUUM operation.
- Select System catalogs or User tables for Autovacuum.
  - If there are a large number of UPDATE and DELETE operations, select the User tables.
  - If there are a large number of CREATE and DELETE operations, select System catalogs.
Click Next: Configure Schedule to configure the schedule and Vacuum type. You are advised to select Periodic for Task Type. The GaussDB(DWS) automatically executes VACUUM in your selected time windows.

NOTE:

For automatic Vacuum O&M tasks, the system uses the VACUUM FULL operation to process user tables. VACUUM FULL holds a level 8 lock, which blocks other transactions. Other transactions will be in lock waiting during VACUUM FULL execution. After 20 minutes, a timeout error is reported. Therefore, do not perform other transactions in the configured time window.
After confirming that the information is correct, click Next to complete the configuration.

8.1.2 and earlier versions: Run the VACUUM FULL command to clear data.
NOTICE:
1. VACUUM FULL locks a table. During VACUUM FULL, all accesses to the table are blocked and wait until VACUUM FULL ends. Please schedule properly to prevent table locking from affecting services.
2. VACUUM FULL is used to extract valid data from the current table and delete dirty data. This operation temporarily occupies extra space (the space will be released after the data is deleted). Therefore, the space increases and then decreases during VACUUM FULL, calculate the space required by VACUUM FULL in advance. (Extra space = Table size x (1 – Dirty page rate))

Connect to the database, run the following SQL statement to query large tables whose dirty page rate exceeds 30%, and sort the tables by size in descending order:

      
         SELECT schemaname AS schema, relname AS table_name, n_live_tup AS analyze_count, pg_size_pretty(pg_table_size(relid)) as table_size, dirty_page_rate
FROM PGXC_GET_STAT_ALL_TABLES
WHERE schemaName NOT IN ('pg_toast', 'pg_catalog', 'information_schema', 'cstore', 'pmk')
AND dirty_page_rate > 30
ORDER BY table_size DESC, dirty_page_rate DESC;

Check whether any command output is displayed.
- If yes, run the commands in 3 for a table larger than 10 GB.
- If no, no further action is required.
Run the VACUUM FULL command to clear the top 5 tables with the most dirty pages. If the maximum disk usage is greater than 70%, clear the tables one by one.
1

VACUUM FULL ANALYZE schema.table_name;
If there is no table with a high dirty page rate and the disk usage is close to or exceeds 75%, expand the node or disk capacity of the cluster based on the following data warehouse types to prevent cluster read-only.
- Cloud data warehouse + SSD cloud disk: Expand the disk capacity by referring Disk Capacity Expansion of an EVS Cluster.
- Cloud data warehouse+local SSD disks, or old standard data warehouse (disk capacity expansion is not supported): Contact technical support for Scaling Out a Cluster.

Scenario 2: Disk Skew and Clearing Skew Tables

If the skew rate of a table on a single DN is greater than or equal to 5%, you are advised to reselect a distribution key for the table and redistribute data.

NOTICE:

Impacts of skewed tables: Skew tables may cause severe skew in operator computing or spilling. As a result, some DNs may be overloaded, and the advantages of GaussDB(DWS) distributed computing cannot be fully utilized, affecting system performance. Besides, they are easily to cause disk space exhaustion on a single DN.
In 8.1.3 and later versions, tables are created in polling (RoundRobin) mode by default (see RoundRobin). If you are not familiar with distribution keys, you can use the ROUNDROBIN keyword to create tables to simplify the service development (see Application Scenarios of Polling Tables and Hash Tables).

Connect to the database and run the following SQL statement to query the skew tables:

      
       
         
         WITH skew AS
(
        SELECT
                schemaname,
                tablename,
                pg_catalog.sum(dnsize) AS totalsize,
                pg_catalog.avg(dnsize) AS avgsize,
                pg_catalog.max(dnsize) AS maxsize,
                pg_catalog.min(dnsize) AS minsize,
                (pg_catalog.max(dnsize) - pg_catalog.min(dnsize)) AS skewsize,
                pg_catalog.stddev(dnsize) AS skewstddev
        FROM pg_catalog.pg_class c
        INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        INNER JOIN pg_catalog.gs_table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname
        INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype IN('H', 'N')
        GROUP BY schemaname,tablename
)
SELECT
        schemaname,
        tablename,
        totalsize,
        avgsize::numeric(1000),
        (maxsize/totalsize)::numeric(4,3)  AS maxratio,
        (minsize/totalsize)::numeric(4,3)  AS minratio,
        skewsize,
        (skewsize/avgsize)::numeric(4,3)  AS skewratio,
        skewstddev::numeric(1000)
FROM skew
WHERE totalsize > 0;

        

      
     

For details about how to query skew tables, see section Data Skew Causes Slow SQL Statement Execution and Operations Fail on Large Tables.

Select another distribution column for the table with severe skew based on the table size and skew rate. In 8.1.0 and later versions, use the ALTER TABLE syntax to change the distribution column. For other versions, see How Do I Change Distribution Columns.

Scenario 3: Cluster Read-only.

When the usage of a single disk in the cluster exceeds 90%, the system automatically triggers read-only for the cluster. In this case, if you continue to execute write services (DML or DDL), an error message similar to "ERROR: cannot execute xxx in a read-only transaction." is displayed.

Read-only is a mechanism of GaussDB(DWS) to protect user data. It prevents instance startup failures caused by 100% disk usage.

After the cluster is read-only, you need to perform operations such as DROP and TRUNCATE to delete unnecessary data as soon as possible to reduce the space usage to less than 80%. Then handle tables mentioned in scenarios 1 and 2 to prevent the disk usage increase caused by VACUUM FULL.

If the cluster version is 8.1.3 or later, perform the following steps:

When a cluster is in read-only status, stop the write tasks to prevent data loss caused by disk space exhaustion.

Use the client to connect to the database, disable read-only through START TRANSACTION, and run the DROP/TRUNCATE TABLE command to clear unnecessary data to ensure that the disk usage is less than 80%.

Data clearing method 1:

       
          START TRANSACTION READ WRITE;
drop/truncate table table_name;
COMMIT;

Data clearing method 2:

       
          START TRANSACTION;
SET transaction_read_only=off;
drop/truncate table table_name;
COMMIT;

After the clearing is complete, the system automatically cancels the read-only mode.

Check the tables mentioned in scenarios 1 and 2 to see whether there are tables that need to be handled. If no, you are advised to scale out the cluster as soon as possible. Based on the data warehouse type, scale-out is classified into node scale-out and disk scale-out.
- Cloud data warehouse + SSD cloud disk: Expand the disk capacity by referring Disk Capacity Expansion of an EVS Cluster.
- Cloud data warehouse+local SSD disks, or old standard data warehouse (disk capacity expansion is not supported): Contact technical support for Scaling Out a Cluster.

For cluster 8.1.2 and earlier versions, perform the following steps:

When a cluster is in read-only status, stop the write tasks to prevent data loss caused by disk space exhaustion.
Log in to the GaussDB(DWS) console and cancel the read-only state of the cluster.
1. Log in to the GaussDB(DWS) console. Click Clusters. All clusters will be displayed by default.
2. In the Operation column of the target cluster, choose More > Cancel Readonly.
3. In the dialog box that is displayed, click OK to confirm and cancel the read-only status for the cluster.
After the read-only state is canceled, run the DROP/TRUNCATE command to delete unnecessary data. Ensure that the disk usage is less than 80%.
Check the tables mentioned in scenarios 1 and 2 to see whether there are tables that need to be handled. If no, you are advised to scale out the cluster as soon as possible. Based on the data warehouse type, scale-out is classified into node scale-out and disk scale-out.
- Cloud data warehouse + SSD cloud disk: Expand the disk capacity by referring Disk Capacity Expansion of an EVS Cluster.
- Cloud data warehouse+local SSD disks, or old standard data warehouse (disk capacity expansion is not supported): Contact technical support for Scaling Out a Cluster.

Space Management and Control

GaussDB(DWS) supports the sql_use_spacelimit and temp_file_limit parameters for statement disk space control. These parameters prevent disk usage spikes that can cause alarms or read-only mode during service operation. They also help detect services that exchange or import too much data to the database.

Log in to the GaussDB(DWS) console, click Clusters on the left, and click the desired cluster. The cluster details page is displayed.

Click Parameters, search for sql_use_spacelimit and temp_file_limit (see Disk Space) in the search box, and adjust them.

You are advised to set sql_use_spacelimit to 10% of the total capacity. For example, if the purchased space is 100 GB per node, set sql_use_spacelimit to 10 GB.

NOTICE:

After the preceding configuration takes effect, if the space usage of a SQL statement exceeds the value of this parameter, the SQL statements will be terminated. To temporarily disable the function, run the following statement in the session:

          
             SET sql_use_spacelimit=0;

Subscribing to Disk Space Alarms

To reduce customers' O&M pressure, GaussDB(DWS) provides the alarm subscription function. When the disk usage of a cluster is greater than the preset threshold, the system notifies you through SMS messages or emails.

Set an alarm threshold:
1. Log in to the GaussDB(DWS) console, choose Alarms on the left, and click Alarm Rule Management.
2. In the alarm rule list, click Modify on the right of Node Data Disk Usage Exceeds the Threshold.
3. In the cluster list, click the name of the target cluster. The Cluster Information page is displayed. Set the alarm policy as follows:
  - Associated Cluster: all clusters
  - Alarm Policy: Set the threshold of important alarms to 70% and the duration to Last 10 minutes, and set the threshold of urgent alarms to 75% and the duration to Last 10 minutes. Set the parameters as follows:
Create a topic on the Simple Message Notification (SMN) console.
1. Switch to the SMN console and click Create Topic. Set Topic Name and Enterprise Project as follows.
2. After the topic is created, click Add Subscription on the right. Select SMS or Email, and enter the mobile number or email address in the Endpoint text box.
3. The entered mobile number or email address will receive a confirmation SMS message or email. Click OK to complete the subscription.
Add an alarm subscription.
1. Return to the GaussDB(DWS) console, choose Alarms on the left, click Subscriptions, and click Create Subscription.
2. Set Subscription Name to dms_alarm, Alarm Severity to Urgent and Important, and SMN Topic to dms_alarm created in the previous step.
3. Click OK. After the alarm subscription is configured, a notification will be sent when the disk usage exceeds 70% or 75%.

Parent topic: Cluster Performance

Previous topic: Error "abort transaction due to concurrent update" Is Reported During SQL Execution

Next topic: SQL Execution Is Slow with Low Performance and Sometimes Does Not End After a Long Period of Time

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank 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.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot