Beyond SQL Tuning: How Metrics, I/O Settings, and System Parameters Reveal Hidden DB Issues

Background

During recent discussions with domestic database vendors, many DBAs and vendor engineers argued that optimizing SQL statements is sufficient and that digging into low‑level metrics or wait events is unnecessary, especially compared with Oracle.

Oracle Case Study

A client’s Oracle database suffered frequent crashes that sometimes required a full system reboot, causing hours of downtime. Analysis showed a high number of AIOSERVER processes, indicating severe I/O pressure. The database used a regular file system without DIRECTIO, so under heavy load asynchronous I/O spawned many AIOSERVER threads, blocking the system. Two recommendations were made: (1) optimize the application to reduce I/O, and (2) enable DIRECTIO. The client accepted only the first suggestion; after SQL tuning the system became more stable but crashes persisted for months.

System‑Level I/O Tuning

Eventually the client enabled DIRECTIO, which eliminated the crashes completely. While application‑level optimization can lower overall I/O volume, it cannot precisely control I/O bursts; only system‑level settings like DIRECTIO can reliably prevent I/O‑induced failures.

PG‑Compatible Domestic Database Case

In another scenario, a domestic database compatible with PostgreSQL handled many tiny SQL statements per transaction. After two years in production, performance became erratic during peak load: response times fluctuated and the backend’s dirty‑page write rate spiked, causing shared‑buffer contention. Adjusting bgwriter and checkpoint parameters and increasing shared_buffers from the default 25% of physical memory to a larger value resolved the issue.

Why Deeper Metrics Matter

Even though many legacy Oracle environments could be kept healthy through SQL tuning alone, critical systems increasingly require insight from database metrics and wait‑event analysis. Many domestic databases lack accurate metric exposure, forcing DBAs to rely on SQL tricks. Advanced tools such as perf, bpf, and flame‑graph visualizations can uncover hidden bugs, but they are unfamiliar to most DBAs, making such analysis rare and valuable.

Conclusion

For robust database operation, understanding internal metrics, I/O behavior, and system‑level parameters is essential. Vendors should expose precise performance data, and DBAs need to move beyond pure SQL optimization to adopt system‑wide tuning techniques.