Thursday, September 19, 2024

PostgreSQL - Setting Parameters for Production

There are several key parameters that should be adjusted for optimal performance, security, and stability. The default settings are usually designed for small systems and need tuning based on your hardware, workload, and specific use cases. Here's a breakdown of important parameters to adjust:

1. Memory Settings
  • shared_buffers
    • Memory the database server uses for shared memory buffers
    • 25% of physical RAM if physical RAM > 1GB
    • Larger settings for shared_buffers usually require a corresponding increase in max_wal_size and setting huge_pages
  • wal_buffers
    • The amount of shared memory used for WAL data that has not yet been written to disk
    • Default value:  3% of shared_buffers, but not less than 64kB nor more than the size of one WAL segment (16MB)
    • The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases
  • work_mem
    • Used for sorting tuples by ORDER BY and DISTINCT operations, and for joining tables by merge-join and hash-join operations.
    • Be careful with high values, as this is allocated per query, potentially consuming a large amount of RAM during heavy workloads
    • Consider with formular "Total RAM * 0.25 / max_connections"
  • temp_buffers
    • Used for temporary buffers within each database session and for storing temporary tables.
    • This setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session
  • maintenance_work_mem
    • Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
    • Larger settings might improve performance for vacuuming and for restoring database dumps.
    • It’s recommended to set this value higher than work_mem; this can improve performance for vacuuming. In general it should be "Total RAM * 0.05"
  • effective_cache_size
    • Set how much RAM is available for caching the data pages, both in the Shared Buffers and in the OS Cache.
    • Used by the PostgreSQL query Optimizer to determine whether indexes fit in RAM. 
    • A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used
    • Set value between 50% and 75% of the available RAM
  • huge_pages
    • Turn of Transparent HugePages.
    • Set huge_page_size = 0 (default value) for using the default huge page size on the system
    • Setting hugepage steps:
      • Set shared_buffer, huge_pages (on) parameter
      • Set shmmax, shmall parameters
        • shmmax = shared_buffer + 1 GB
        • shmall = sum of all shmmax
2. Connection Settings
  • max_connections
    • Determines the maximum number of concurrent connections to the database server.
    • When running a standby server, you must set this parameter to the same or higher value than on the primary server
    • Using PgBouncer or PgPool-II to handle connections.
  • superuser_reserved_connections 
    • Determines the number of connection “slots” that are reserved for connections by PostgreSQL superusers.
    • The default value is three connections.
3. WAL and Checkpoint Settings
  • wal_level
    • Determines how much information is written to the WAL.
    • The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server
  • wal_compression
    • This parameter enables compression of WAL using the specified compression method. When enabled, the PostgreSQL server compresses full page images written to WAL when full_page_writes is on or during a base backup
    • Enabling compression can reduce the WAL volume without increasing the risk of unrecoverable data corruption, but at the cost of some extra CPU spent on the compression during WAL logging and on the decompression during WAL replay
  • max_wal_size
    • Maximum size to let the WAL grow during automatic checkpoints
    • The default is 1 GB.
    • Increasing this parameter can increase the amount of time needed for crash recovery
  • min_wal_size
    • If WAL disk usage stays below the value specified by min_wal_size, old WAL files are recycled for future use at a checkpoint rather than removed.
    • This feature ensures that enough WAL space is reserved to handle spikes in WAL usage, like when running large batch jobs. 
    • Default value: 80 MB
  • checkpoint_timeout
    • The maximum amount of time between two WAL checkpoints.
    • A checkpoint is begun every checkpoint_timeout seconds, or if max_wal_size is about to be exceeded, whichever comes first. The default settings are 5 minutes and 1 GB, respectively.
    • Reducing checkpoint_timeout and/or max_wal_size causes checkpoints to occur more often. This allows faster after-crash recovery, since less work will need to be redone. However, one must balance this against the increased cost of flushing dirty data pages more often
  • checkpoint_completion_target
    • To avoid flooding the I/O system with a burst of page writes, writing dirty buffers during a checkpoint is spread over a period of time.
    • A fraction of the checkpoint interval - is adjusted so that the checkpoint finishes when the given fraction of checkpoint_timeout seconds have elapsed, or before max_wal_size is exceeded, whichever is sooner.
    • If checkpoint_completion_target equal 0.8, PostgreSQL write checkpoints slow enough that the current checkpoint is done when 80% of the estimated time to the start of the next checkpoint has passed.
    • Typically recommended to set it to no higher than 0.9 (the default) since checkpoints include some other activities besides writing dirty buffers
4. Autovacuum Settings
  • autovacuum_max_workers
    • Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time.
    • Increase the number of autovacuum workers for large databases to maintain table health.
  • autovacuum_work_mem
    • Specifies the maximum amount of memory to be used by each autovacuum worker process.
    • It defaults to -1, indicating that the value of maintenance_work_mem should be used instead
  • autovacuum_vacuum_cost_limit
    • Specifies the cost limit value that will be used in automatic VACUUM operations
    • If -1 is specified (which is the default), the regular vacuum_cost_limit value will be used.
    • With big tables, increasing this parameter can help the autovacuum process handle more rows at once, reducing the time it takes to complete
5. Logging Settings
  • log_min_duration_statement
    • Log queries that take longer than a certain number of milliseconds to help identify slow queries.
    • When using this option together with log_statement, the text of statements that are logged
  • log_statement
    • Controls which SQL statements are logged.
    • Valid values are none (off), ddl, mod, and all (all statements)
    • mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM.
  • log_checkpoints
    • The default is on
  • log_connections & log_disconnections 
    • Turn ON on production.
6. Disk and I/O Settings
  • random_page_cost
    • Estimate of the cost of a non-sequentially-fetched disk page.
    • This value can be overridden for tables and indexes in a particular tablespace by setting the tablespace parameter.
    • The default value for random_page_cost is 4.0. This suggests that random I/O is 4 times more expensive than sequential I/O, based on the assumption that moving the disk’s read/write head to non-adjacent locations takes more time compared to reading data stored sequentially. Low or High value will effect the Query Planner.
    • Based on types on disk (HDD, SSD,...), RAM, Database size, we set this parameter low or high.
  • seq_page_cost
    • Sets the planner's estimate of the cost of a disk page fetch that is part of a series of sequential fetches.
    • If using a system with faster storage (like SSDs) or if most data is cached in memory, you might want to reduce both seq_page_cost and random_page_cost to reflect the lower cost of I/O
  • effective_io_concurrency
    • Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.
    • SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds.
Ref:
at
Labels: