Locate stop and trace slow SQL queries - Lindorm

LindormTable's slow query diagnosis lets you locate running queries, stop those harming instance stability, and review historical records — all using SQL.

Prerequisites

Before you begin, make sure that:

Your LindormTable version is 2.6.3 or later for locating and stopping slow queries, or 2.8.2.13 or later for tracing slow queries. To check and upgrade your version, see LindormTable version guide and Minor version update.
Your Lindorm SQL version is 2.6.6 or later. To verify, see SQL version guide.

Locate slow queries

By default, LindormTable uses a universally unique identifier (UUID) to identify each executed statement. Run SHOW PROCESSLIST to see all queries currently running in LindormTable:

SHOW PROCESSLIST;

The ID field in the result set is the key field for identifying queries in subsequent operations such as stopping them.

Note

The result set varies slightly depending on the storage engine. It may also include queries executed through non-SQL methods — those queries have non-UUID IDs.

For the full SHOW PROCESSLIST syntax reference, see SHOW PROCESSLIST.

Stop slow queries

After identifying a slow query from the result set, run KILL QUERY with its UUID to stop it:

KILL QUERY '581f9ab8-68af-4c93-b73a-eb99679ed192';

For the full KILL QUERY syntax reference, see KILL QUERY.

Trace slow queries

LindormTable records slow queries that exceed a configurable time threshold into the lindorm._slow_query_ view. Query this view to review historical slow queries for diagnosis and optimization.

Step 1: Enable slow query recording

ALTER SYSTEM SET SLOW_QUERY_RECORD_ENABLE = true;

For full ALTER SYSTEM usage and the SLOW_QUERY_RECORD_ENABLE parameter, see ALTER SYSTEM.

Step 2: Set the slow query threshold

Set SLOW_QUERY_TIME_MS to define how many milliseconds a query must run before it is recorded as slow. The following example sets the threshold to 10 seconds:

ALTER SYSTEM SET SLOW_QUERY_TIME_MS = 10000;

Important

Setting the threshold too low in performance-sensitive environments can affect overall instance performance.

Step 3: Query the slow query view

Use SELECT to retrieve records from lindorm._slow_query_. The view name is fixed — lindorm is Lindorm's internal database and _slow_query_ is the slow query table name; neither can be changed.

-- Query the first 10 slow queries
SELECT * FROM lindorm._slow_query_ LIMIT 10;

-- Count slow queries recorded after a specific point in time
SELECT COUNT(sql_query_s) AS num FROM lindorm._slow_query_ WHERE query_start_time >= 1680152319000;

Slow query view fields

The following table describes the fields in the lindorm._slow_query_ view.

Field name	Description
`query_start_time`	The time when the query request was initiated, expressed as a UNIX timestamp in milliseconds.
`query_id`	The ID of the query request.
`sql_query_s`	The SQL statement of the query request. Empty if the request was not SQL-based.
`duration_i`	The running time of the query request.
`status_s`	Indicates whether the query request succeeded or failed.
`ip_s`	The IP address that sent the query request. Empty if no IP address is associated.
`server_s`	The node where the query request was executed.
`query_s`	The internal query statement that was executed.

Important

Slow query records are retained for only 1 hour by default. Query the view promptly after a slow query incident.

What's next

Review ALTER SYSTEM to explore other system-level configuration options.
Review SHOW PROCESSLIST to understand the full result set schema for your storage engine.