Benchmarking and Optimizing Disk I/O Performance in Oracle and PostgreSQL(Part B)

Benchmarking and Optimizing Disk I/O Performance in Oracle and PostgreSQL

Part B:

Alireza Kamrani
05/March/2025

Note: All parameters value in this topic is not a preferred value for you environment and must tune based on your workload correctly.

Database performance heavily depends on efficient disk I/O.
Oracle ASM and PostgreSQL handle storage differently, requiring distinct benchmarking and optimization strategies. This guide covers performance testing using tools like dd, iostat, sar, database reports, and configuration tuning for log buffer adjustments and WAL synchronization.

Oracle ASM Disk I/O Benchmarking and Optimization

1. Key Considerations for Oracle ASM

• ASM Manages Storage: Oracle ASM abstracts physical storage into disk groups.

• Redo Log Write Performance: I/O bottlenecks often affect redo log writes.

• Testing Requires Direct I/O: Bypass OS-level buffering to simulate real database behavior.

2. Benchmarking Oracle ASM Disk Performance

Using dd to Simulate Redo Log Writes

dd if=/dev/zero of=/dev/asmdisk1_test bs=128k oflag=sync count=5000

• bs=128k: Matches Oracle redo log block size.

• oflag=sync: Ensures direct writes, bypassing OS cache.

Monitoring Disk Performance with iostat

iostat -dx 5

• Check %util: If near 100%, the disk is saturated.

• Monitor await: High values indicate I/O latency.


Analyzing Historical Disk Performance with sar

sar -d 10 5

• Identifies trends and periods of high disk activity.

Reviewing Oracle Reports (AWR, ASH, OEM)

• AWR Reports: Analyze log file sync wait events.

• ASH Reports: Identify slow sessions causing I/O bottlenecks.

• OEM Performance Hub: Provides real-time storage I/O analysis.

3. Advanced ASM Optimization Strategies

Parallel Testing in RAC Environments

dd if=/dev/zero of=/dev/asmdisk1_test bs=128k oflag=sync count=5000 &

dd if=/dev/zero of=/dev/asmdisk2_test bs=128k oflag=sync count=5000 &

dd if=/dev/zero of=/dev/asmdisk3_test bs=128k oflag=sync count=5000 &

• Identifies storage controller bottlenecks.

• Helps evaluate ASM striping effectiveness.

Log Buffer and I/O Performance Tuning

Troubleshooting Redo Log Buffer Issues in Oracle

The Redo Log Buffer is a crucial memory structure in Oracle that temporarily holds redo entries before writing them to redo log files. If issues arise, such as contention or performance degradation, follow these steps to diagnose the root cause:

1. Identify Symptoms of Redo Log Buffer Issues

• High waits on ‘log buffer space’: Indicates that the redo log buffer is too small, causing processes to wait before writing redo entries.

• High waits on ‘log file sync’: Suggests slow LGWR performance or disk I/O issues.

• High redo entries generated but slow writing: May indicate excessive redo generation or inefficient LGWR operations.

2. Diagnose Using Oracle Views and Wait Events

Check Log Buffer Space Waits

Run the following query to see if sessions are waiting on log buffer space:

SELECT event, total_waits, time_waited, average_wait FROM v$system_event WHERE event IN ('log buffer space', 'log file sync');

• High log buffer space waits → Consider increasing LOG_BUFFER.

• High log file sync waits → Investigate log writer (LGWR) performance or redo log file I/O.

Check Redo Statistics

SELECT name, value FROM v$sysstat WHERE name IN ('redo buffer allocation retries', 'redo log space requests');

• High redo buffer allocation retries → Indicates redo log buffer contention.

• High redo log space requests → Suggests that LGWR is not fast enough.



Check LGWR Activity

SELECT pid, program, status, state, wait_event FROM v$process WHERE program LIKE 'ora%LGWR%';

• If LGWR is waiting on disk I/O, check redo log file performance.

3. Recommended Value for LOG_BUFFER

Default Value Calculation

Oracle automatically sets LOG_BUFFER at startup based on SGA size, typically:

However, in high-throughput environments, increasing LOG_BUFFER beyond 64 MB is rarely beneficial(maybe need  more on some special database), as LGWR flushes frequently to avoid excessive waits.

When to Increase LOG_BUFFER?

• If redo buffer allocation retries is continuously increasing.

• If log buffer space waits are high.

• If heavy redo generation occurs, such as in DML-intensive workloads (batch processing, large inserts, updates).

When NOT to Increase LOG_BUFFER?

• If log file sync waits are the main issue → Focus on redo log file performance.

• If LGWR is slow due to disk I/O → Use faster disks (SSD, ASM, or redo log groups with fewer members).

4. How to Change LOG_BUFFER?

This parameter is not dynamically adjustable; you must set it in init.ora or spfile and restart the database:

Using init.ora

LOG_BUFFER = 16777216 # Example: 16MB

Using spfile (requires restart)

ALTER SYSTEM SET LOG_BUFFER = 16777216 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;


5. Alternative Solutions to Redo Buffer Issues

If increasing LOG_BUFFER does not help, consider:
Tune redo logs (increase log file size, use faster disks).
Reduce redo generation (use NOLOGGING where possible).
Use direct path inserts (APPEND hint).

Optimize commit strategy (batch commits instead of frequent commits).
Enable redo transport optimizations in RAC environments.

• Adjust db_writer_processes to parallelize database writes. ALTER SYSTEM SET DB_WRITER_PROCESSES = 4 SCOPE=SPFILE; --adding CPU cores automatically can increase this parameter.

• Optimize ASM Disk Group Configuration

• Use high-performance SSDs for redo logs.

• Balance disk groups to avoid single-disk saturation.

Using SLOB for Realistic I/O Workload Testing

• Generates realistic Oracle workloads.

• Measures IOPS, latency, and storage responsiveness.

• Guide: SLOB Benchmark

PostgreSQL Disk I/O Benchmarking and Optimization

1. Key Considerations for PostgreSQL

• Uses File-Based Storage: Unlike ASM, PostgreSQL manages data at the file system level.

• Write-Ahead Logging (WAL) Impacts Performance: Disk I/O tuning focuses on WAL optimization.

• OS and File System Caching Affects Performance: PostgreSQL relies on OS-level caching and related to effective_cache_size.


2. Benchmarking PostgreSQL Disk Performance

Using dd to Simulate WAL Writes

dd if=/dev/zero of=/var/lib/postgresql/pg_wal/testfile bs=128k oflag=sync count=5000

• Tests disk write speed for WAL transactions.

Monitoring Disk Performance with iostat

iostat -dx 5

• Identifies disk bottlenecks impacting WAL writes.

Tracking WAL Write Performance with pg_stat_bgwriter

SELECT * FROM pg_stat_bgwriter;

• Monitors WAL writes, buffer flushes, and checkpoints.


Historical I/O Analysis with sar

sar -d 10 5

• Tracks disk utilization trends over time.

3. WAL Optimization Strategies

Adjusting PostgreSQL Log Buffer Size

Increasing shared_buffers and WAL buffers reduces I/O overhead--if you are not sure for value of wal_buffer you can use automatically setup based on wal section size.

ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET wal_buffers = '64MB';

• shared_buffers: PostgreSQL’s main buffer cache, reducing disk reads.

• wal_buffers: Buffers WAL writes before flushing to disk.
• If wal_buffers = -1, PostgreSQL sets it to 1/32nd (3.125%) of shared_buffers, but at least 64 KB and at most 16 MB.If shared_buffers is large, wal_buffers is automatically increased.
• Increasing Wal_Segment_Size:

Tuning WAL Segment Size in PostgreSQL

In PostgreSQL, WAL (Write-Ahead Logging) segments store redo logs, ensuring data durability and crash recovery. By default, the WAL segment size is 16MB, but in high-write environments, increasing it can reduce I/O overhead and improve performance.

1. When to Increase WAL Segment Size?

High Checkpoint Frequency

• If checkpoints are triggered too often due to WAL segment exhaustion (max_wal_size reached).

• Check checkpoints_timed and checkpoints_req in pg_stat_bgwriter: SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;

• If checkpoints_req is high, PostgreSQL is doing forced checkpoints, causing I/O spikes.

WAL Archiving and Replication Optimization

• If streaming replication or logical replication is used and WAL segments are generated too quickly.

• Large WAL sizes reduce the number of files needed, improving replication efficiency.

Bulk Insert or Update Workloads

• If running large batch operations, a bigger WAL segment reduces WAL flush frequency, improving performance.

Frequent ‘WAL Full’ Errors or Lagging Replicas

• Check pg_wal_lsn_diff() to monitor WAL growth: SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS bytes_since_restart;

2. How to Increase WAL Segment Size?

Step 1: Check Current WAL Segment Size

Run the following query:

SHOW wal_segment_size;

Default: 16MB


Step 2: Change WAL Segment Size (Requires ReinitDB)

⚠️ This setting is fixed at cluster initialization! You cannot change it dynamically. If you need a larger WAL segment, you must reinitialize the PostgreSQL cluster with a new segment size.

Example: Set WAL Segment Size to 64MB

• Stop PostgreSQL: systemctl stop postgresql

• Reinitialize the PostgreSQL (Destructive Action!)

• Backup your data first!(best for creating a new database)

pg_ctl stop -D /var/lib/postgresql/16/main

rm -rf /var/lib/postgresql/16/main
# Be careful! This deletes the cluster

initdb -D /var/lib/postgresql/16/main --wal-segsize=64

Or on a already working PostgreSQL:

pg_resetwal -D $PGDATA --wal-segsize=64

Size is in MB. Default is 16. Parameter can be set between 1 and 1024.

• Restart PostgreSQL: systemctl start postgresql

3. Adjust Supporting WAL Settings

After increasing the segment size, optimize the following parameters in postgresql.conf:

• Increase max_wal_size and min_wal_size to match new segment size: max_wal_size = 10GB min_wal_size = 1GB

• Tune checkpoint_timeout to reduce I/O spikes: checkpoint_timeout = 30min

• Increase WAL buffers for better performance: wal_buffers = 16MB

4. Verify Changes

After restarting, confirm the WAL segment size:

SHOW wal_segment_size;

Then monitor WAL activity:

SELECT * FROM pg_stat_wal_receiver;


5. When NOT to Increase WAL Segment Size?

❌ If storage is limited (larger segments mean more WAL storage).
❌ If the database has low write throughput (no benefit for mostly read workloads).

Tuning WAL Synchronization (synchronous_commit)

The synchronous_commit setting controls when transactions are considered durable.
Usually ON is the best value and don't need to change.

ALTER SYSTEM SET synchronous_commit = 'off';

• off: Improves performance but risks data loss in crashes.

• local: WAL is written locally but not to replicas immediately.

• remote_apply: Ensures WAL is applied on replicas before committing (safe but slower).

Optimizing Checkpoint Frequency

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET max_wal_size = '2GB';

• Reducing checkpoint frequency lowers I/O spikes.

• Increasing WAL size prevents excessive checkpointing.

4. Benchmarking PostgreSQL Under Load

Using pgbench for Performance Testing

pgbench -i -s 100 postgres
# Initialize test with scale 100
pgbench -c 10 -j 2 -T 60 postgres
# Run for 60 seconds with 10 clients

• Simulates real-world database transactions.

• Measures TPS (transactions per second), latency, and I/O efficiency.

Conclusion

Both Oracle ASM and PostgreSQL require specialized approaches for benchmarking and optimizing disk I/O. Below are the key differences in their storage management, performance concerns, and optimization strategies:

• Storage Management:

• Oracle: Uses ASM Disk Groups for storage abstraction.

• PostgreSQL: Relies on the OS File System for data management.

• Primary I/O Concern:

• Oracle: Redo Log Writes can become a performance bottleneck.

• PostgreSQL: WAL Writes impact transaction durability and performance.

• Benchmark Tools:

• Oracle: dd, AWR, ASH, SLOB.

• PostgreSQL: dd, pg_stat_bgwriter, pgbench.

• Buffer Adjustments:

• Oracle: LOG_BUFFER, DB_WRITER_PROCESSES optimize disk writes.

• PostgreSQL: shared_buffers, wal_buffers help reduce I/O overhead.

• WAL/Redo Optimization:

• Oracle: ASM Striping, SSDs for redo logs improve performance.

• PostgreSQL: synchronous_commit tuning, WAL file optimization reduces latency.

• Best Testing Method:

• Oracle: dd for raw I/O testing, SLOB for real-world workload simulation.

• PostgreSQL: dd for raw I/O testing, pgbench for transaction benchmarking.

By leveraging iostat, sar, and database-native reports, DBAs can diagnose I/O bottlenecks and implement performance tuning techniques to optimize disk throughput in both Oracle and PostgreSQL environments.


Alireza Kamrani