Observing Domestic Databases: Slow SQL and Connection-Pool Monitoring for DM and KingbaseES

New database, new troubleshooting toolkit

After moving workloads to domestic databases like DM and KingbaseES, the first thing a DBA notices is that the old habits are gone: slow-query logs, performance_schema, pg_stat_statements—either they don't exist or they look nothing like before. When an incident hits, you're stuck guessing from vague built-in logs. The first job of an observability system is to fold these domestic databases into unified monitoring, sitting next to the hosts and applications in the same stack, instead of leaving them as a black-box island.

Which metrics to collect

Whatever the underlying engine, build a baseline with these common metrics:

• Connections: current vs. max. Approaching the ceiling is a warning sign, usually pointing at pool misconfiguration or a leak—worth its own alert;
• Active / long-running transactions: long transactions delay lock release and are the most common performance killer; alert when they cross a threshold like 30 seconds;
• Buffer hit ratio: for read-heavy workloads, a sudden drop usually means insufficient memory or full-table scans;
• Lock waits: deadlock count and lock wait duration map directly to the "stuck" symptom, and paired with slow SQL they reveal cause and effect;
• Disk and archive: archived-log backlog will eventually fill the disk—the most easily overlooked hardware-level failure;
• Process CPU / IO: the database process's own CPU and disk IO, which alongside host metrics tells you whether it's the database's fault or the machine's.

Observe connects to both engines through JDBC/standard collectors, pulling these metrics into one dashboard and aligning them on the same timeline as hosts and applications for easy cross-referencing. You don't need the full list on day one—collect connections, long transactions, and lock waits steadily for a week, plot the curves, and only then talk about alert thresholds.

Capturing slow SQL

Both DM and KingbaseES keep slow-SQL views, but the column names and how you enable them differ. For DM, turn on slow-statement recording with a threshold first:

-- DM: enable slow statement logging, 200ms threshold
SP_SET_PARA_VALUE(1, 'MONITOR_SQL_EXEC', 1);
ALTER SYSTEM SET 'SQL_TRACE_MASK' = 3;

The collector then polls the slow-SQL view on a schedule and reports execution count, average latency, rows scanned, and a digest of the SQL text. Two things matter here: mask the SQL text—replace parameters with ? so sensitive data never lands in your logs—and aggregate by template so the same statement with different parameters counts as one entry, which surfaces high-frequency slow queries instead of a thousand one-off variations.

How to deploy the collector

Run the collector next to the database host or on the application side, connecting with a read-only account whose privileges stop at querying system views. Don't use a DBA account for collection against a production database—one mistake there is an incident. The default collection interval is 30 seconds; low-volume environments can stretch to 60 seconds to save overhead. Pull the slow-SQL view once a minute—trends tell you more than any single value. Xinchuang environments usually mean an isolated intranet with on-premises deployment, so the collector has to install offline and connect directly, with no dependency on the public internet.

Pitfalls we hit

1. Character sets: DM mixes GB18030 and UTF-8 by default. Tell the collector explicitly which charset to use, or Chinese comments and field values come back garbled and stop matching in search.
2. Driver versions: older DM JDBC drivers handle connection pooling poorly—under high concurrency connections never get released. Use the latest official driver and enable connection-leak detection.
3. Timing semantics: "execution time" means different things across versions—some include queue time, some don't. Confirm the semantics before you build a baseline, or your alert thresholds will drift.
4. Licensing and privileges: some monitoring views need separate system privileges or an enterprise license, and the open-source or standard edition may not expose them. Run through with a test account before going live, so you don't discover on launch day that the data isn't there.

One final suggestion: during migration, watch three numbers above all—slow-SQL count, long-transaction count, and lock waits. They best reflect whether the business is absorbing the new engine's performance profile. Add alert rules only once the baseline stabilizes, or you'll be chasing thresholds instead of real regressions.