Introduction

One of the biggest challenges in daily database operations is dealing with long-running or even infinite-loop queries. These problematic queries not only consume substantial system resources but can also disrupt the normal execution of other workloads. With version 0.15, GreptimeDB addresses this pain point with robust query management features, including both automatic and manual termination of long-running queries, elevating the database’s availability and maintainability.

Getting Started Quickly

Before we dive into query management, let's assume you have already installed GreptimeDB following the official documentation.

The simplest way to launch GreptimeDB is via Docker:

docker run -p 127.0.0.1:4000-4003:4000-4003 
 -v "$(pwd)/greptimedb:/greptimedb_data" 
 --name greptime --rm 
 greptime/greptimedb:v0.15.1 standalone start 
 --http-addr 0.0.0.0:4000 
 --rpc-bind-addr 0.0.0.0:4001 
 --mysql-addr 0.0.0.0:4002 
 --postgres-addr 0.0.0.0:4003

Real-Time Query Monitoring: Tracking All Running Queries

GreptimeDB v0.15 adds the INFORMATION_SCHEMA.PROCESS_LIST table, empowering DBAs with real-time visibility into the state of all active queries:

USE INFORMATION_SCHEMA;
DESC PROCESS_LIST;

Table schema as follows:

+-----------------+----------------------+------+------+---------+---------------+
| Column          | Type                 | Key  | Null | Default | Semantic Type |
+-----------------+----------------------+------+------+---------+---------------+
| id              | String               |      | NO   |         | FIELD         |
| catalog         | String               |      | NO   |         | FIELD         |
| schemas         | String               |      | NO   |         | FIELD         |
| query           | String               |      | NO   |         | FIELD         |
| client          | String               |      | NO   |         | FIELD         |
| frontend        | String               |      | NO   |         | FIELD         |
| start_timestamp | TimestampMillisecond |      | NO   |         | FIELD         |
| elapsed_time    | DurationMillisecond  |      | NO   |         | FIELD         |
+-----------------+----------------------+------+------+---------+---------------+

Key columns explained

• id: Unique identifier for the query—crucial for terminating queries with the KILL command.
• schemas: Database schema the client was connected to.
• query: The executed SQL statement—helps pinpoint problematic queries and optimize performance.
• client: Client connection info, including address and protocol type. This is useful for tracing the source and method of long-running queries.
• start_timestamp / elapsed_time: Represent when the query started and how long it has been running.

Practical Example: Handling an Infinite Loop Query

Let’s see how these features work using a classic recursive CTE example:

WITH RECURSIVE infinite_loop AS (
    SELECT 1 as n
    UNION ALL
    SELECT n + 1 FROM infinite_loop  -- Recursive call
)
SELECT * FROM infinite_loop;

This query will result in an infinite loop.

Step 1: Execute the Problematic Query

Open a terminal and connect to GreptimeDB via the MySQL client (e.g., mysql -h 127.0.0.1 -P 4002), then run the above query. The session will hang.

（Figure 1: Execute the Problematic Query）

Step 2: Monitor Query Status

Open another terminal and check currently running queries:

SELECT * FROM INFORMATION_SCHEMA.PROCESS_LIST\G;

Example output:

*************************** 1. row ***************************
             id: 172.17.0.2:4001/0
        catalog: greptime
        schemas: public
          query: SELECT * FROM INFORMATION_SCHEMA.PROCESS_LIST
         client: mysql[172.17.0.1:57814]
       frontend: 172.17.0.2:4001
start_timestamp: 2025-07-07 10:58:15.673000
   elapsed_time: 00:00:00.001000
*************************** 2. row ***************************
             id: 172.17.0.2:4001/1
        catalog: greptime
        schemas: public
          query: WITH RECURSIVE infinite_loop AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM infinite_loop) SELECT * FROM infinite_loop
         client: mysql[172.17.0.1:63122]
       frontend: 172.17.0.2:4001
start_timestamp: 2025-07-07 10:57:41.443000
   elapsed_time: 00:00:34.231000

We see two active queries; the one with id = 172.17.0.2:4001/1 has been running for 34 seconds—it’s our problematic infinite loop.

In real-world production settings, developers frequently face issues like inefficient queries without proper indexes, infinite loops due to logic errors, or blocked queries caused by database bugs. Given limited database resources, being able to terminate such queries swiftly is critical to protect system performance.

GreptimeDB v0.15 introduces several mechanisms to handle long-running queries efficiently.

Multiple Mechanisms for Terminating long-running Queries

CTRL + C Query Termination

GreptimeDB v0.15 enhances MySQL client support, allowing you to abort the current running query instantly by pressing CTRL+C. This is especially handy for development and testing scenarios.

（Figure 2: Ctrl+C Interrupting a Query）

Note: PostgreSQL psql client support is under development.

Automatic Query Timeout

With v0.15, you can set an automatic query timeout using the MAX_EXECUTION_TIME variable:

SET MAX_EXECUTION_TIME=5000;

Note: MAX_EXECUTION_TIME is in milliseconds. Here, queries exceeding 5 seconds will be automatically terminated for the current session.

Now, if you re-run the CTE infinite loop query, the system will abort it after 5 seconds and return a timeout error.

（Figure 3: Query Times Out after 5 Seconds）

You can also set this in application connection strings. For example, with Java JDBC or Go MySQL Driver, just add MAX_EXECUTION_TIME=5000 to the connection string:

jdbc:mysql://127.0.0.1:4002?connectionTimeZone=Asia/Shanghai&forceConnectionTimeZoneToSession=true&MAX_EXECUTION_TIME=5000

Manually Terminate a Specific Query

You can precisely terminate any running query using the KILL {query id} statement, where {query id} is taken from the PROCESS_LIST output:

*************************** 2. row ***************************
             id: 172.17.0.2:4001/1
        catalog: greptime
        schemas: public
          query: WITH RECURSIVE infinite_loop AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM infinite_loop) SELECT * FROM infinite_loop
         client: mysql[172.17.0.1:63122]
       frontend: 172.17.0.2:4001
start_timestamp: 2025-07-07 11:15:14.779000
   elapsed_time: 00:00:03.075000

Execute KILL statement to terminate this query:

KILL '172.17.0.2:4001/1';

This method is essential in production environments where you may need to terminate a specific problematic query without affecting others.

Conclusion

By introducing the INFORMATION_SCHEMA.PROCESS_LIST system table, the KILL statement, and the MAX_EXECUTION_TIME parameter, GreptimeDB v0.15 delivers a comprehensive query management solution. It enables real-time query inspection, automatic timeouts, and precise manual termination—crucial capabilities for modern database observability. GreptimeDB’s continued improvements in query management reflect our commitment to user experience and system reliability.

We invite you to try out these new features and encourage you to provide feedback and suggestions through our official channels.