DEV Community

GBASE Database
GBASE Database

Posted on

GBase 8c TRUNCATE Operation Hang Analysis

#database

In the GBase 8c database, the TRUNCATE operation is used to quickly delete all data in a table while preserving the table structure. Normally, the TRUNCATE operation executes swiftly, but in certain scenarios, it may get stuck. This article explores the background of the GBase 8c TRUNCATE hang issue to help readers understand and resolve such problems.

Possible Causes of the Issue:

  1. Lock Waits:

    If other transactions are accessing the table during the TRUNCATE operation, it may lead to lock waits. In this case, check if there are any long-running uncommitted transactions or if a deadlock exists.

  2. System Resource Contention:

    When system resources (such as CPU, memory, disk I/O, etc.) are under heavy contention, the TRUNCATE operation may not get sufficient resources to execute, leading to a hang. In this scenario, check the system resource usage, optimize resource allocation, or consider scaling up.

  3. Network Issues:

    GBase 8c requires communication between different nodes in the database cluster. If there are network issues, the TRUNCATE operation may fail to execute correctly. Ensure the network is stable and functioning properly.

  4. Improper Database Parameter Settings:

    Certain parameters in GBase 8c can affect the execution of the TRUNCATE operation, such as concurrency settings, transaction isolation levels, etc. If these parameters are misconfigured, the TRUNCATE operation may hang. Review and adjust the database parameters accordingly.

  5. Storage Engine Issues:

    GBase 8c supports multiple storage engines, and different engines may handle the TRUNCATE operation differently. If the storage engine in use has a bug or performance issue, the TRUNCATE operation may hang. In this case, try switching storage engines or upgrading the engine version.

Example Scenario: Simulating a Truncate Hang

Session 1: Manually Begin a Transaction and Query Table t1 Without Committing 

begin;
select * from t1;
Enter fullscreen mode Exit fullscreen mode

Example output:

id | c1
---+---
 1 | 1
(1 row)
Enter fullscreen mode Exit fullscreen mode

Session 2: Execute TRUNCATE on Table t1 and Observe the Hang 

truncate table t1;
Enter fullscreen mode Exit fullscreen mode

Query lock information:

select * from pgxc_relation_locks where relname = 't1';
Enter fullscreen mode Exit fullscreen mode

Example output:

Image description

Theoretical Analysis of the Two Sessions:

  • Session 1:

    • mode: AccessShareLock - The SELECT command applies an AccessShareLock on the table, which persists until the transaction ends.
    • granted: hold lock - Lock is held.

  • Session 2:

    • mode: AccessExclusiveLock - The ACCESS EXCLUSIVE lock conflicts with all other locks, typically requested by DDL statements.
    • granted: acquire lock - Lock is requested.

Query Lock Information: 

select * from pgxc_locks;
Enter fullscreen mode Exit fullscreen mode

Example output:

Image description

The query results clearly show that Session 2 is blocked by Session 1. The block_query indicates the blocking query is the SELECT statement from Session 1, and block_pid is the PID of Session 1.

Resolution:

To resolve the hang, terminate Session 1 by executing the following command:

select pg_terminate_backend(140290346104576);
Enter fullscreen mode Exit fullscreen mode

Conclusion:

The TRUNCATE hang issue in GBase 8c can be caused by various factors, and it requires careful analysis and resolution based on the specific circumstances. To troubleshoot such issues, database logs, system resource monitoring, and other relevant information should be utilized to identify the root cause and apply the necessary fixes. Additionally, optimizing database configurations and adjusting system resource allocations can enhance the performance and stability of the GBase database.

Top comments (0)