Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 11.7 Performance Tuning Recommendations

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

11.7 Performance Tuning Recommendations

To use DRITA reports for performance tuning, review the top five events in a given report, looking for any event that takes a disproportionately large percentage of resources. In a streamlined system, user I/O will probably make up the largest number of waits. Waits should be evaluated in the context of CPU usage and total time; an event may not be significant if it takes 2 minutes out of a total measurement interval of 2 hours, if the rest of the time is consumed by CPU time. The component of response time (CPU "work" time or other "wait" time) that consumes the highest percentage of overall time should be evaluated.

When evaluating events, watch for:

Event type

Description

Checkpoint waits

Checkpoint waits may indicate that checkpoint parameters need to be adjusted, (checkpoint_segments and checkpoint_timeout).

WAL-related waits

WAL-related waits may indicate wal_buffers are under-sized.

SQL Parse waits

If the number of waits is high, try to use prepared statements.

db file random reads

If high, check that appropriate indexes and statistics exist.

db file random writes

If high, may need to decrease bgwriter_delay.

btree random lock acquires

May indicate indexes are being rebuilt. Schedule index builds during less active time.

Performance reviews should also include careful scrutiny of the hardware, the operating system, the network and the application SQL statements.

Previous PageTable Of ContentsNext Page