Search code examples
windowspostgresqlmemorydatabase-performance

Cannot increase work_mem above 1GB using PostgreSQL 9.3 on Windows Server

I would like to tweak the postgres config for use on a Windows server. Here is my current posgresql.conf file: http://pastebin.com/KpSi2zSd

I would like to increase work_mem and maintenance_work_mem, but if I raise the values above 1GB I get this error when starting the service:

enter image description here

Nothing is added to the log files (at least not in data\pg_log). How can I figure out what is causing the issue (increase logging)? Could the have anything to do with issues management between windows and postgres?

Here are my server specs:

  • Windows Server 2012 R2 Datacenter (64 bit)
  • Intel CPU E5-2670 v2 @ 2.50 GHz
  • 512 GB RAM
  • PostgreSQL 9.3
Solution

  • Under Windows the value for work_mem is limited to 2GB (even on a 64bit system) - there is no workaround as far as I know.

    I don't know why you couldn't set it to 1GB though. Maybe the sum of work_mem and maintenance_work_mem has another limit I am not aware of.

    Setting work_mem that high by default is usually not a good idea. With 512GB RAM and just 10 users this might work, but keep in mind that the amount of work_mem is requested by a statement for every sort, group or hash operation in a single query. So you could have a statement requesting this amount of memory 15 or 20 times.

    You don't need to change this in postgresql.conf - this can be changed dynamically if you know that the following query will benefit from a large work_mem, by running: 

    set session work_mem='2097151';

    If you use a higher number, you'll get an error message telling you the limit:

    ERROR: 2097152 is outside the valid range for parameter "work_mem" (64 .. 2097151)

    Even if Postgres isn't using all the memory, it still benefits from it. Postgres (unlike e.g. Oracle) relies heavily on the filesystem cache rather than doing all the caching itself. Values for shared_buffers beyond roughly 8GB rarely show any benefit.

    What you do need to tell Postgres is how much memory the operating system usually uses for caching, by setting effective_cache_size to the appropriate value. Postgres doesn't use that for caching, but it influences the planner's choice to e.g. prefer an index scan over a seq scan if the index is likely to be in the file system cache.

    You can see the current size of the file system cache in the Windows task manager (or e.g. ProcessExplorer)