Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nu vot, WWWXXXAPC, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text, WWWXXXDOCO, WWWXXXAPC., XXXCNZZZ, ss4000 recovery array

Links

XODOX
Impressum

#1: confused of buffers and memory settings

Posted on 2008-04-23 12:16:50 by koenig

Hi,

we're quite new to Postgres and we're going to start using Postgres8.3
on Opensuse10.3 64 bit. We have a dedicated dbserver with 8GB RAM, and
now I'm not sure how to deal with the memory/buffer settings. The db
will be accessed heavily (~20 requests/sec.) with a read/write ration of
50:50, yes, a lot of write activity.

I thought of setting "shared_buffers" to 750000 (~6GB) but how depends
this on the kernel buffer setting in /etc/sysctl.conf (what is the
interaction between these two settings?).
I know the variable "shmmax" can be defined, but currently there's no
such entry.

The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers" is
also not clear. The maintenance_work_mem influences the size of the WAL
logs..?!?

What else are "top performance related" options for the usage scenario I
described earlier ?

any help appreciated....GERD...

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

Report this message

#2: Re: confused of buffers and memory settings

Posted on 2008-04-23 13:11:17 by Guillaume Lelarge

Hi,

Gerd K=F6nig a =E9crit :
> we're quite new to Postgres and we're going to start using Postgres8.3=20
> on Opensuse10.3 64 bit. We have a dedicated dbserver with 8GB RAM, and=20
> now I'm not sure how to deal with the memory/buffer settings. The db=20
> will be accessed heavily (~20 requests/sec.) with a read/write ration o=
f=20
> 50:50, yes, a lot of write activity.
>=20
> I thought of setting "shared_buffers" to 750000 (~6GB) but how depends=20
> this on the kernel buffer setting in /etc/sysctl.conf (what is the=20
> interaction between these two settings?).
> I know the variable "shmmax" can be defined, but currently there's no=20
> such entry.
>=20

shared_buffers should be set to 1/4 of your available RAM. It's a start=20
value, you can tweak it after that, but you would probably be better=20
advised to use 2GB to begin with.

> The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers" is=
=20
> also not clear. The maintenance_work_mem influences the size of the WAL=
=20
> logs..?!?
>=20

work_mem is used for sort and group operations (for example, ORDER BY).=20
It's not part of the shared_buffers memory and it will be used by every=20
postgres process. So you better have small values, something between 1=20
MB and 64 MB.

maintenance_work_mem is used for maintenance operations (VACUUM, CREATE=20
INDEX and another one I don't remember now). You can use bigger values=20
because you won't have many operations of this kind that will happen at=20
the same time. Probably 256 MB would be a good start value.

> What else are "top performance related" options for the usage scenario =
I=20
> described earlier ?
>=20

Checkpoint and WAL settings are important ones. FSM size is another one.

Regards.


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#3: Re: confused of buffers and memory settings

Posted on 2008-04-23 13:19:23 by Michael Monnerie

--nextPart8976748.1uacCiSEDj
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On Mittwoch, 23. April 2008 Gerd K=F6nig wrote:
> The db will be accessed heavily (~20 requests/sec.)=20
> with a read/write ration of 50:50, yes, a lot of write activity.

Not really heavy, I would say, but maybe your transactions are very big=20
and produce a lot of I/O.

> I thought of setting "shared_buffers" to 750000 (~6GB) but how
> depends this on the kernel buffer setting in /etc/sysctl.conf (what
> is the interaction between these two settings?).
> I know the variable "shmmax" can be defined, but currently there's no
> such entry.

# Shared Mem Maximum example:
kernel.shmmax =3D 950123456
# do not allow memory overcommit to prevent database crashes
vm.overcommit_memory=3D2

> The meaning of "work_mem" / "maintenance_work_mem" and "wal_buffers"
> is also not clear. The maintenance_work_mem influences the size of
> the WAL logs..?!?
> What else are "top performance related" options for the usage
> scenario I described earlier ?

http://www.postgresql.org/docs/8.3/interactive/runtime-confi g-resource.html

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4

--nextPart8976748.1uacCiSEDj
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIDxs8zhSR9xwSCbQRAvRhAJ9u8ZrEgqimg1Br4LFWq9AoxxTw3ACg 90S+
c2FkKu5kNQdhAXvq34C9Wuc=
=Qwtm
-----END PGP SIGNATURE-----

--nextPart8976748.1uacCiSEDj--

Report this message

#4: Re: confused of buffers and memory settings

Posted on 2008-04-23 14:24:43 by Dimitri Fontaine

--nextPart4221083.SMCMcAZESu
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Le mercredi 23 avril 2008, Gerd K=F6nig a =E9crit=A0:
> we're quite new to Postgres and we're going to start using Postgres8.3
[...]
> What else are "top performance related" options for the usage scenario I
> described earlier ?

Did you read this document already?
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute .htm

=2D-=20
dim

--nextPart4221083.SMCMcAZESu
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBIDyqQlBXRlnbh1bkRApplAKCt0eeBnn7LjUIb4kX7coTsd0WQNQCe Lwxj
2ruF7sNHg8SNKWtpTPfss/Q=
=vxTJ
-----END PGP SIGNATURE-----

--nextPart4221083.SMCMcAZESu--

Report this message