This document describes performance tuning parameters that can improve AlloyDB Omni performance on a VM. These suggested tuning parameters are based on the results of running a set of workloads that exhibit different read and write patterns, caching behaviors, and index access patterns.
Most default PostgreSQL parameter values remain unchanged, because the changes are limited to parameters that benefit the majority of applications. The suggested values of these parameters should generally improve performance on modern hardware.
Database parameter | AlloyDB Omni default | Suggested setting |
---|---|---|
random_page_cost |
4.0 | 1.1 |
temp_buffers |
8 MB | 128 MB |
max_wal_size |
1 GB | 20 GB |
min_wal_size |
80 MB | 10 GB |
default_toast_compression |
pglz | lz4 |
maintenance_work_mem |
64 MB | 1 GB for smaller machines 2 GB for larger machines |
max_worker_processes |
64 | max(64, # vCPUs) |
max_parallel_workers |
8 | max(8, # vCPUs) |
max_parallel_workers_per_gather |
2 | max(2, # vCPUs / 2) |
work_mem |
4 MB | 128 to 512 MB, depending on workload and the amount of memory available |
effective_cache_size |
40% * DRAM | 80% * DRAM |