Hello Postgres Admins,
I am having a bit of trouble setting postgres memory parameters on a windows machine. I figured out the parameters based on postgres documentation as well as the whack a mole slide deck, but the postgres process will not start. Event log keeps indicating the following reason for the postgres process : FATAL: could not create shared memory segment
I can not find any documentation specific to windows…
Environment:
2008 Windows server, 64bit
20 gigs of ram (I am assuming 2gigs for os, 3 gigs for other apps that are running on the machine, which leaves me with 15gigs of memory I could give to Postgres)
I would like to give postgres as much memory as possible as this cluster is hosting very large data, by large I mean that it took over 20 hours to load the data and postgres complained about running out of max_fsm_pages and checkpoint_segments when trying to create a primary key.
I found that to figure out the value for the max_fsm_pages parameter I should run vacuumdb –v which came back with a value of 599,584 as opposed to the default 204800
I set the parameters with the following values, which failed:
Effective_cache_size – 8 gigs – rec: 50%-66% of total ram
Shared_buffers – 4 gigs – rec: 25%-33% of total ram
Work_mem – 128MB – rec: from whack a mole presentation
Maintenance_work_memory – 256MB – rec: from whack a mole presentation
Checkpoint_segmetns – 256MB – rec: from whack a mole presentation
Max_fsm_pages – 600,000 – rec: from running the vacuumdb command, which I rounded up
After that failed, I tried setting shared_buffers and effective_cache_size to 2 gigs each, remembering that on a 32 bit windows system a process can only utilize 2gig address space, but that was a shot in the dark as this is a 64bit machine so the 32bit limitation should not apply, of course that implies an assumption that each of these parameters would be utilized by a different background process, which may be erroneous.
Does anyone have any recommendations or corrections?
Thank you,
Kasia
