Thread: setting pg memory consumption on windows

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

Hello,

I am running Postgres 8.3.0 on RHEL 4, and trying to insert a value into an xml data type:

create table test_xml (name character varying(10), iteminfo xml)

insert into test_xml values ('bob', 'xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance')

I get the following error message:

ERROR:  unsupported XML feature

DETAIL:  This functionality requires the server to be built with libxml support.

HINT:  You need to rebuild PostgreSQL using --with-libxml.

Is there a way to “switch on” the libxml option after a db cluster has been instantiated? Or do I really need to rebuild postgres with the --with-libxml parameter?

Thank you,

Sincerely,

Kasia