Child pages
  • PostgreSQL Configuration(Tuning)
Skip to end of metadata
Go to start of metadata

This might be interesting: pgtune.  It is supposed to help tune postgresql for the machine it is running on.  I haven't tried it since I'm running Windows which uses EnterpriseDB Tuning-Wizard (which I've just installed and haven't actually tried yet).

I started looking up PostgreSQL tuning documentation.  Here's what I have so far:

  • Tuning Your PostgreSQL Server:
    • shared_buffers - memory for PostgreSQL itself, depending on version the units are different.
    • effective_cache_size - available system memory that the PostgreSQL query planner can use
    • checkpoint_segments - data written to the database are done in WAL (write-ahead log) segements.  Each WAL is 16MB and the default is 3 segments before writing.  Writing is expensive, but storing more can lead to problems as well. Better to increase 30 to avoid the following log message: checkpoints are occurring too frequently but could not avoid if there are more insert/update than the configuration.
    • checkpoint_completion_target - how much of the previous checkpoint data should be written before the next checkpoint is reached
    • default_statistics_target - default is 10, recommend 100; the more stats, the better the optimizer
    • work_mem - used for each sorting operation; losts of sorts = losts of memory so be careful
    • wal_sync_method - advanced users only, operating system dependent (somewhat): a value of open_sync is the most common (default is no good)
    • wal_buffers - a value of 1MB is good for large write-heavy systems
    • constraint_exclusion - turn on if using table partitions, off otherwise
    • random_page_cost - the cost of accessing a random page from the disk; default is 4, fast systems use 2-3, uber-fast (like Dave's) use 1
    • autovacuum - automate the execution of VACUUM and ANALYZE commands.
  • Tuning PostgreSQL for Performance - supplement to above:
    • WAL files: by turning off fsync, you run the risk of loosing all "commited" data since none of it will actually be written to the database (stored in the WAL); however, the write speeds will go through the roof.  If the amount of written data is large and you want to leave on the immediate sync, then move the WAL file over to another disk (RAID might do this automatically)
  • Performance Turning PostgreSQL
    • commit_delay and commit_siblings - tweak to enhance simultaneous commits
  • Checkpoints and the Background Writer
    • Checkpoints are occurring too frequently... generally happens as a result of having lots of updates going into the database. Some claims that it happens during a heavy VACUUM . Consider increasing the configuration parameter "checkpoint_segments".

Other interesting sites:

There's a lot of interesting stuff out there; most of which is over my head.  I'll add more stuff as I find it.

2 Comments

  1. Do you have any estimates for the size of the WAL log on disk?
    We can probably place that onto an SSD similar to what we are doing with the filesystems themselves on opensolaris.

  2. It depends on the configuration.

    Each WAL is 16MB so that if checkpoint _segments is 10, the size of  WAL is 160MB.

    You can look at postgresql.conf for checkpoint_segments.