setting pg memory consumption on windows

--_000_232B5217AD58584C87019E8933556D11014695D992redmx2esric om_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hello Postgres Admins,

I am having a bit of trouble setting postgres memory parameters on a window=
s 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 star=
t. 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 loa=
d the data and postgres complained about running out of max_fsm_pages and c=
heckpoint_segments when trying to create a primary key.

I found that to figure out the value for the max_fsm_pages parameter I sho=
uld 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 r=
ounded 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 o=
nly 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 im=
plies an assumption that each of these parameters would be utilized by a di=
fferent background process, which may be erroneous.

Does anyone have any recommendations or corrections?

Thank you,
Kasia

--_000_232B5217AD58584C87019E8933556D11014695D992redmx2esric om_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:st=3D"" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hello Postgres Admins,<o:p></o:p>

<o:p> </o:p>

I am having a bit of trouble setting postgres memory
parameters on a windows machine. I figured out the parameters based on post=
gres
documentation as well as the whack a mole slide deck, but the postgres proc=
ess
will not start. Event log keeps indicating the following reason for the
postgres process : FATAL: could not create shar=
ed
memory segment<o:p></o:p>

I can not find any documentation specific to windowsR=
30;<o:p></o:p>

<o:p> </o:p>

Environment:<o:p></o:p>

2008 Windows server, 64bit<o:p></o:p>

20 gigs of ram (I am assuming 2gigs for os, 3 gigs for o=
ther
apps that are running on the machine, which leaves me with 15gigs of memory=
I
could give to Postgres)<o:p></o:p>

<o:p> </o:p>

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. =
<o:p></o:p>

<o:p> </o:p>

I found that to figure out the value for the max_f=
sm_pages
parameter I should run vacuumdb –v which came back with a value=
of 599,584
as opposed to the default 204800<o:p></o:p>

<o:p> </o:p>

I set the parameters with the following values, which
failed:<o:p></o:p>

Effective_cache_size – 8 gigs
– rec: 50%-66% of total ram<o:p></o:p>

Shared_buffers – 4 gigs – =
rec:
25%-33% of total ram<o:p></o:p>

Work_mem – 128MB – rec: fr=
om
whack a mole presentation<o:p></o:p>

Maintenance_work_memory – 256MB
– rec: from whack a mole presentation<o:p></o:p>

Checkpoint_segmetns – 256MB R=
11;
rec: from whack a mole presentation<o:p></o:p>

Max_fsm_pages – 600,000 – =
rec:
from running the vacuumdb command, which I rounded up<o:p></o:p></fo=
nt>

<o:p> </o:p>

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 th=
at
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 b=
e
erroneous. <o:p></o:p>

<o:p> </o:p>

Does anyone have any recommendations o=
r
corrections? <o:p></o:p>

<o:p> </o:p>

Thank you,<o:p></o:p></p=
>

Kasia <o:p></o:p>

<o:p> </o:p>

</div>

</body>

</html>

--_000_232B5217AD58584C87019E8933556D11014695D992redmx2esric om_--

Kasia Tuszynska [ Mo, 14 September 2009 22:15 ] [ ID #2015666 ]

switching on libxml

--_000_232B5217AD58584C87019E8933556D11014695E34Eredmx2esric om_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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=3Dhttp://www.w3.org/2001/XML=
Schema-instance')

I get the following error message:

ERROR: unsupported XML feature
DETAIL: This functionality requires the server to be built with libxml sup=
port.
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-libx=
ml parameter?

Thank you,
Sincerely,
Kasia

--_000_232B5217AD58584C87019E8933556D11014695E34Eredmx2esric om_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:st=3D"" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hello,<o:p></o:p>

I am running Postgres 8.3.0 on RHEL 4,=
and
trying to insert a value into an xml data type:<o:p></o:p></p=
>

<o:p> </o:p>

create table test_xml (name character
varying(10), iteminfo xml)<o:p></o:p>

insert into test_xml values ('bob',
'xmlns:xsi=3Dhttp://www.w3.org/2001/XMLSchema-instance')<o:p></o:p><=
/font>

<o:p> </o:p>

I get the following error message:<o:p=
></o:p>

<o:p> </o:p>

ERROR: unsupported XML feature<o=
:p></o:p>

DETAIL: This functionality requi=
res
the server to be built with libxml support.<o:p></o:p>

HINT: You need to rebuild Postgr=
eSQL
using --with-libxml.<o:p></o:p>

<o:p> </o:p>

Is there a way to “switch onR=
21; the
libxml option after a db cluster has been instantiated? Or do I really need=
to
rebuild postgres with the --with-libxml parameter? <o:p></o:p></font=
>

<o:p> </o:p>

Thank you,<o:p></o:p></p=
>

Sincerely,<o:p></o:p></p=
>

Kasia <o:p></o:p>

</div>

</body>

</html>

--_000_232B5217AD58584C87019E8933556D11014695E34Eredmx2esric om_--

Kasia Tuszynska [ Di, 15 September 2009 21:40 ] [ ID #2015845 ]

Re: switching on libxml

Kasia Tuszynska <ktuszynska [at] esri.com> writes:
> 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?

You don't need to re-initdb the database. You do need to rebuild the
executables with the correct configure options.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Tom Lane [ Di, 15 September 2009 22:34 ] [ ID #2015846 ]

Re: setting pg memory consumption on windows

Kasia Tuszynska wrote on 14.09.2009 22:15:
> 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 segme=
nt*
>
> I can not find any documentation specific to windows=85
>
You are not mentioning which PG version you are running. But with 8.4.1 a=
bug that lead to "could not reattach to shared memory" errors was fixed.=
I don't know if this is the same what you are seeing.

http://www.postgresql.org/docs/current/static/release-8-4-1. html

Thomas

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Thomas Kellerer [ Di, 15 September 2009 23:05 ] [ ID #2015847 ]