DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Tracking Long Transactions in GBase 8s with onstat

#gbase #database #数据库

Long-running transactions can block the release of logical logs and even cause GBase 8s to enter a Blocked:LONGTX state. The built-in onstat utility provides a quick way to identify the offending transaction and extract its SQL for optimization. Here's a step-by-step walkthrough for a gbase database.

1. Check the Global Database Status

Under normal conditions, onstat - returns something like:

On-Line -- Up 35 days 16:51:16 -- 3920896 Kbytes
Enter fullscreen mode Exit fullscreen mode

When a long transaction is detected, the status line changes to On-Line (LONGTX) and a Blocked:LONGTX warning appears:

On-Line (LONGTX) -- Up 35 days 16:41:40 -- 3920896 Kbytes
Blocked:LONGTX
Enter fullscreen mode Exit fullscreen mode

This is the first sign that something is holding a transaction open too long.

2. Inspect Transactions and Spot a Rollback

Run onstat -x to list active transactions. Look at the flags column — a R in the third position means the transaction is rolling back, which is a strong indicator of a long transaction.

onstat -x
Enter fullscreen mode Exit fullscreen mode

Example output:

On-Line (LONGTX) -- Up 35 days 16:41:56 -- 3920896 Kbytes
Blocked:LONGTX
Transactions

address          flags   userthread   locks   begin_logpos current logpos isol   rb_time retrys coord
1cf0a6748        A-R--   1cd55c618    642073  119403       119405       0x1aa91e4 DIRTY  0
Enter fullscreen mode Exit fullscreen mode

The flag A-R-- shows the rollback (R) is underway.

3. Map the Userthread to a Session ID

Take the userthread value (e.g., 1cd55c618) and grep onstat -u to find the corresponding session ID.

onstat -u | grep 1cd55c618
Enter fullscreen mode Exit fullscreen mode

Sample result:

address          flags   sessid   user      tty  wait  tout  locks   nreads  nwrites
1cd55c618        --RPX-- 1880841  gbasedbt -    0     0     642073  256446  323049
Enter fullscreen mode Exit fullscreen mode

The sessid is 1880841 — the session responsible for the long transaction.

4. Capture the Problem SQL and Optimize

Use onstat -g ses with the session ID to inspect the session details, which include the currently executing SQL statement.

onstat -g ses 1880841
Enter fullscreen mode Exit fullscreen mode

Examine the SQL, then optimize accordingly — add indexes, break large transactions into smaller batches, or rewrite the query.

With these four simple commands, you can quickly identify and address long transactions in your GBASE environment, keeping logical logs free and your gbase database running smoothly.

Top comments (0)