Friday, October 18, 2013

Adjusting defaults for PostgreSQL.conf

For PostgreSQL 9.4, we've been discussing adjusting defaults for postgresql.conf to give users a better out-of-box experience.  Our existing defaults were set at a time when we used SysV memory, and when the average developer laptop or low-end server had 512MB of RAM.   So, a survey.

Here's some things contributors have already adjusted:
  • shared_buffers now goes up to 128MB automatically depending on available RAM reservation (we did this for 9.3)
  • effective_cache_size is automatically set to shared_buffers X 3 (might be X 4 by the time we release)
Now we are discussing increasing the defaults and/or autotuning for work_mem and maintenance_work_mem.  Suggested new defaults are 4X the current: 4MB and 64MB.  These should allow PostgreSQL to run on any machine of 1GB or greater memory a bit better.

However, one thing which came up during this discussion is that many people may be running PostgreSQL on virtual machines which have less than 1GB of RAM available to PostgreSQL.  I don't think they are, but we lack data.  So I put up a survey to see what amounts of RAM our users really have on their smallest machines/VMs.   Please fill out the survey so that we can have reasonable input for development, and if you have comments, please put them below.
Posted by at
Labels: , , ,

10 comments:

  1. James HubbardOctober 18, 2013 at 2:17 PM

    Your survey may not be working correctly. I voted in the 4 - 7 category. My vote didn't show up there. There were only 2 results. One in 1 - 1.9 and the other in 8GB and up.

    ReplyDelete
    Replies
    1. Josh BerkusOctober 18, 2013 at 3:17 PM

      I think you're seeing the effect of server-side caching.

      Delete
    2. Josh BerkusOctober 20, 2013 at 11:20 AM

      confirmed. Mind you, caching is being wonky in this instance, but the web team is on it.

      Delete
    3. Guido StepkenOctober 21, 2013 at 5:32 PM

      With beginning of SSDs doing random lookups at a rate of even 15.000/second one can expect to get at minimum a thousand lookups even without large ram buffers. But therefore PostgreSQL algorithms have slighty to be changed.

      Delete
    4. Guido StepkenOctober 21, 2013 at 5:33 PM

      This comment has been removed by the author.

      Delete
    5. Josh BerkusOctober 21, 2013 at 6:06 PM

      Actually, that's accounted for in the existing tunable variables. However, that does bring up an important point for any autotuning.

      Delete
  2. Software and MotorcyclesOctober 18, 2013 at 5:17 PM

    I don't know the user base, but the smallest Amazon EC2 server (the "micro" instances) have 615MB of RAM, and the smallest server offered by RackSpace have 512MB of RAM.

    ReplyDelete
  3. UnknownOctober 19, 2013 at 9:32 AM

    Check out lowendbox.com and the like for the truly low end. The smallest I've ever noticed is 64M, but usually 128M is lowest. However, the nature of the task likely changes for these.

    ReplyDelete
  4. Bruce MomjianNovember 7, 2013 at 9:31 AM

    I think the effective_cache_size is 4x shared buffers, but might be 3x by the time 9.4 is released. I think you stated this backwards above.

    ReplyDelete

Subscribe to: Post Comments (Atom)