|
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 |
