High I/O in PolarDB for PostgreSQL(Compatible with Oracle) is most commonly caused by large SQL scans that exceed the shared buffer cache, or by VACUUM operations with improper parameter settings that consume too much I/O bandwidth. This topic explains how to identify the source of high I/O and resolve each cause.
I/O concepts
Term | Description |
I/O | The read and write operations on disks. |
I/O latency | The latency of an I/O operation. |
Logical I/O | The I/O that is sent by an application to a file system. |
Physical I/O | The I/O that is sent by the file system to disks. |
Sequential I/O | The sequential read and write operations on disks. |
Random I/O | The random access to disks for read and write operations. |
Synchronous write | The results can be returned only after all the data is written to disks. |
Asynchronous write | The results can be returned before data is written to disks. This releases CPU resources. |
Diagnose and resolve high I/O
Large SQL scans exceeding shared_buffer
When SQL statements scan many rows, shared_buffer cannot cache all the required data. PolarDB for PostgreSQL(Compatible with Oracle) reads the data directly from disk, generating large numbers of physical I/Os.
Resolve:
Use Performance Insight to identify the specific SQL statements causing the scans, then optimize them by adding appropriate indexes or rewriting queries to reduce the number of rows scanned.
VACUUM with improper parameter settings
The VACUUM operation triggers high I/Os because the parameter settings related to the VACUUM operation are improper. You can throttle it by adjusting the following parameters:
vacuum_cost_delayvacuum_cost_limit
To update these parameters, see Specify parameters.
What's next
After resolving high I/O, use Performance Insight to verify that I/O levels return to normal.