Database Performance Monitoring

For OLTP applications, monitoring the performance of a database server is critical.

Importance of Low DB Transaction Latency

If a OLTP DB like MS SQL Server or Postgres is able to complete transactions quickly, it avoid request queueing in all levels of the application stack and has a cascading positive effect on compute requirements of the entire application stack.

If a DB is tuned to complete transactions quickly, then the outstanding request queue length will be smaller. Fewer "in-flight" transactions are easier to sustain and require less server CPU and RAM. In hyperscaler cloud environments, CPU and RAM are the most expensive resources, and reducing this has a direct impact on infra cost.

DB Server Metrics

The following DB Server metrics must be closely monitored, AWS CloudWatch can be configured to monitor the below parameters of any RDS instance.

Disk Latency

This is the single most important DB metric to monitor. Create a dedicated graph that plots read and write disk latency in milliseconds. Even during peak DB usage, disk latency below 10ms is good, and below 5ms is excellent. Disk latencies in excess of 20ms will heavily impact performance. Disk latency is substantially more manageable with SSD/NVMe drives and is recommended for all high performance OLTP DB applications.

To ensure ACID compliance, databases require data to be committed to disk before acknowledging transactions. Having slow disks has a cascading performance impact on the entire application stack.

Measuring disk latency instead of disk throughput or IOPs is preferred as disk latency is independent of DB activity. Even during idle or peak periods the disk latency should be low.

Disk IOPs

This is the second most important metric. Create a dedicated graph to plot read and write IOPs.

OLTP workloads are typically 75% reads and 25% writes. High percentage of read IOPs is an indicator of table scans and indexes may be required. High writes generally indicate issues with DB maintenance operations (Postgres vacuum DB). High writes are also an indicator of very large tables where indexes do not fit in RAM and require heavy disk IO to update data records and the indexes.

Disk Throughput

Create a dedicated graph to plot disk read and write throughput in MB/s (mega bytes per second).

Disk IOPs and throughput are positively correlated, i.e. higher throughput will produce higher IOPs and vice versa.

Check the same pointers for abnormal disk IOPs.

ℹ️ Performance of HDD, SSD and NVMe

Drive Type Latency IOPs Throughput Read/Write

HDD (7200RPM) 15 milliseconds 200 200 MB/s (sqeuential)

SATA SSD 200 microseconds 30,000 600 MB/s

NVMe PCI Gen4 100 microseconds 1,000,000 8 GB/s

NVMe PCI Gen5 100 microseconds 1,500,000 12 GB/s

Drive Type	Latency	IOPs	Throughput Read/Write
HDD (7200RPM)	15 milliseconds	200	200 MB/s (sqeuential)
SATA SSD	200 microseconds	30,000	600 MB/s
NVMe PCI Gen4	100 microseconds	1,000,000	8 GB/s
NVMe PCI Gen5	100 microseconds	1,500,000	12 GB/s

Server CPU, RAM

Create a separate graph to plot CPU and RAM usage in percentage.

Note that on a 4 CPU server, 25% CPU usage indicates the full use of a single CPU core and 100% indicates the full use of all CPU cores. Note that for RAM usage, track only the in-use RAM by the applications and not kernel buffers and free memory.

On dedicated DB servers optimise the DB configuration using apps like PGTune to ensure 80% of RAM is allocated to the DB application. The operating system will require 1 CPU and 2GB of RAM for proper functioning.

Database Tools

Using DB specific tools (Postgres stat_statements/track_io_timing) to monitor slow queries. Using EXPLAIN ANALYZE to understand the working of the DB query planner and optimizer.
Check DB locks to ensure concurrent transactions are not waiting for locks (Postgres pg_locks).
Using APM tools like DataDog, NewRelic to identify slow queries and provide resolutions.

Abnormal Scenarios

Slow DB performance can also be observed under the following abnormal scenarios:

High CPU usage, with low disk IO: this indicates table scans of small tables that fit entirely in RAM. Identify and add indexes.
Low CPU usage, low disk IO: this indicates that transactions are blocked and waiting for DB locks. On highly concurrent OLTP systems, DB locking is very difficult to resolve. A DB schema change may be required to reduce locking. Brute force methods involve lowering disk latency (faster disk) and increasing server CPU and RAM. Postgres Concurrency Control

TODO

#TODO Language polish, this was written in haste.