From justinlong@strategicnetwork.org Mon Jul  28 16:17:16 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id D12A3D1C988
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 16:17:16 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 03074-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 13:16:19 -0300 (ADT)
Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242])
	by svr1.postgresql.org (Postfix) with ESMTP id 4EE36D1C9B2
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 13:17:14 -0300 (ADT)
Received: from tarzan ([68.96.211.236]) by lakemtao03.cox.net
	(InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP
	id <20030728161714.ITPY23518.lakemtao03.cox.net@tarzan>
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 12:17:14 -0400
Message-ID: <000f01c35523$f13dee90$ecd36044@tarzan>
From: "Justin Long" <justinlong@strategicnetwork.org>
To: <pgsql-performance@postgresql.org>
Subject: Optimization
Date: Mon, 28 Jul 2003 12:18:55 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_000C_01C35502.69E97930"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=BAYES_30,
	HTML_20_30
X-Spam-Level: 
X-Archive-Number: 200307/440
X-Sequence-Number: 2733

This is a multi-part message in MIME format.

------=_NextPart_000_000C_01C35502.69E97930
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Greetings,

I am trying to understand the various factors used by Postgres to optimize.=
 I presently have a dual-866 Dell server with 1GB of memory. I've done the =
following:

set /proc/sys/kernel/shmmax to 512000000
shared_buffers =3D 32000
sort_mem =3D 32000
max_connections=3D64
fsync=3Dfalse

Can someone tell me what effective_cache_size should be set to? what kind o=
f formula to use for this? (I got the other figures from phpbuilder.com, an=
d modified for 512k memory).=20

The databases I'm using have about 200,000+ news headlines with full-text i=
ndexes (which range upwards of a few million records). They are updated abo=
ut every 5 to 10 minutes, which means I also have to run a vacuum about onc=
e every 2 to 3 hours at least. As I get more updates obviously the efficien=
cy goes down. I'm trying to make the most of this system but don't fully un=
derstand PG's optimization stuff.

Thanks in advance,
Justin Long

------=_NextPart_000_000C_01C35502.69E97930
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Greetings,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I am trying to understand the various fact=
ors used=20
by Postgres to optimize. I presently have a dual-866 Dell server with 1GB o=
f=20
memory. I've done the following:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>set /proc/sys/kernel/shmmax to=20
512000000</FONT></DIV>
<DIV>shared_buffers =3D 32000<BR>sort_mem =3D=20
32000<BR>max_connections=3D64<BR>fsync=3Dfalse<BR></DIV>
<DIV><FONT face=3DArial size=3D2>Can someone tell me what effective_cache_s=
ize=20
should be set to? what kind of formula to use for this? (I got the other fi=
gures=20
from phpbuilder.com, and modified for 512k memory). </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The databases I'm using have about 200,000=
+ news=20
headlines with full-text indexes (which range upwards of a few million reco=
rds).=20
They are updated about every 5 to 10 minutes, which means I also have to ru=
n a=20
vacuum about once every 2 to 3 hours at least. As I get more updates obviou=
sly=20
the efficiency goes down. I'm trying to make the most of this system but do=
n't=20
fully understand PG's optimization stuff.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Justin Long</FONT></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_000C_01C35502.69E97930--




From josh@agliodbs.com Mon Jul  28 18:01:24 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 4E5C1D1C91F
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 18:01:24 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 06925-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 15:00:24 -0300 (ADT)
Received: from davinci.ethosmedia.com (209-128-84-228.bayarea.net
	[209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 4BB1DD1C507
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 15:01:19 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3354339; Mon, 28 Jul 2003 11:01:39 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Justin Long" <justinlong@strategicnetwork.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 11:00:19 -0700
User-Agent: KMail/1.4.3
References: <000f01c35523$f13dee90$ecd36044@tarzan>
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307281100.19084.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/446
X-Sequence-Number: 2739

Justin,

> I am trying to understand the various factors used by Postgres to optimiz=
e.=20
I presently have a dual-866 Dell server with 1GB of memory. I've done the=
=20
following:

Please set the performance articles at:=20
http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From nickf@doxpop.com Mon Jul  28 18:27:21 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 18CE4D1C4E3
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 18:27:21 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 11180-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 15:26:24 -0300 (ADT)
Received: from shire.ontko.com (shire.ontko.com [199.164.165.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 21F97D1C4CB
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 15:27:19 -0300 (ADT)
Received: from nick (bilbo.ontko.com [199.164.165.101])
	by shire.ontko.com (8.12.3/8.12.3/Debian-6.4) with SMTP id
	h6SIRJ0P005164; Mon, 28 Jul 2003 13:27:19 -0500
Reply-To: <nickf@doxpop.com>
From: "Nick Fankhauser - Doxpop" <nickf@doxpop.com>
To: "Justin Long" <justinlong@strategicnetwork.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 13:25:55 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGOEHNHNAA.nickf@doxpop.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/442
X-Sequence-Number: 2735

Justin-

It sounds like you're on a system similar to ours, so I'll pass along the
changes that I made, which seem to have increased performance, and most
importantly, haven't hurt anything. The main difference in our environment
is that we are less Update/Insert intensive than you are- in our
application, 90% of our information (court cases) is static (Closed) and 10%
are frequently being updated (Pending/Active). This means I only vacuum once
a week. I haven't had chance to churn out objective tests yet, but my
subjective judgment is that this set of params works well:

Set SHMMAX and SHMALL in the kernel to 134217728 (128MB)
Set shared_buffers to 8192 (64MB)
Set sort_mem to 16384 (16MB)
Set effective_cache_size to 65536 (1/2 GB)


The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.

The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.

The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)
The PostgreSQL version is 7.3.2

My reasoning was to increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB and boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB. Of the remaining memory, 256MB goes to 16MB sort space
times
a guesstimate of 16 simultaneous sorts at any given time. If I leave about
128 MB for headroom, then 1/2 GB should be left available for the effective
cache size.

I've never been tempted to turn fsync off. That seems like a risky move.

Regards,
     -Nick

---------------------------------------------------------------------
Nick Fankhauser

    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/




From nickf@ontko.com Mon Jul  28 18:29:58 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id D9FE5D1C4EC
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 18:29:58 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 08134-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 15:29:02 -0300 (ADT)
Received: from shire.ontko.com (shire.ontko.com [199.164.165.1])
	by svr1.postgresql.org (Postfix) with ESMTP id D7618D1C4CB
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 15:29:56 -0300 (ADT)
Received: from nick (bilbo.ontko.com [199.164.165.101])
	by shire.ontko.com (8.12.3/8.12.3/Debian-6.4) with SMTP id
	h6SITv0P005192; Mon, 28 Jul 2003 13:29:58 -0500
Reply-To: <nickf@ontko.com>
From: "Nick Fankhauser" <nickf@ontko.com>
To: "Justin Long" <justinlong@strategicnetwork.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 13:28:34 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGCEHOHNAA.nickf@ontko.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/441
X-Sequence-Number: 2734

Justin-

It sounds like you're on a system similar to ours, so I'll pass along the
changes that I made, which seem to have increased performance, and most
importantly, haven't hurt anything. The main difference in our environment
is that we are less Update/Insert intensive than you are- in our
application, 90% of our information (court cases) is static (Closed) and 10%
are frequently being updated (Pending/Active). This means I only vacuum once
a week. I haven't had chance to churn out objective tests yet, but my
subjective judgment is that this set of params works well:

Set SHMMAX and SHMALL in the kernel to 134217728 (128MB)
Set shared_buffers to 8192 (64MB)
Set sort_mem to 16384 (16MB)
Set effective_cache_size to 65536 (1/2 GB)

The Hardware is a dual-processor Athlon 1.2 Ghz box with 1 GB of RAM and the
DB on SCSI RAID drives.

The database size is about 8GB, with the largest table 2.5 GB, and the two
most commonly queried tables at 1 GB each.

The OS is Debian Linux kernel 2.4.x (recompiled custom kernel for dual
processor support)

The PostgreSQL version is 7.3.2

My reasoning was to increase shared_buffers based on anecdotal
recommendations I've seen on this list to 64MB and boost the OS SHMMAX to
twice that value to allow adequate room for other shared memory needs, thus
reserving 128MB off the top. Of the remaining memory, 256MB goes to 16MB
sort space times a guesstimate of 16 simultaneous sorts at any given time.
If I leave about 128 MB for headroom, then 1/2 GB should be left available
for the effective cache size.

I've never been tempted to turn fsync off. That seems like a risky move.

Regards,
   -Nick
---------------------------------------------------------------------
Nick Fankhauser
nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/




From nickf@ontko.com Mon Jul  28 18:40:34 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 2DD32D1C4EC
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 18:40:34 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 09708-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 15:39:38 -0300 (ADT)
Received: from shire.ontko.com (shire.ontko.com [199.164.165.1])
	by svr1.postgresql.org (Postfix) with ESMTP id 313F8D1C4E3
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 15:40:32 -0300 (ADT)
Received: from nick (bilbo.ontko.com [199.164.165.101])
	by shire.ontko.com (8.12.3/8.12.3/Debian-6.4) with SMTP id
	h6SIeX0P005320; Mon, 28 Jul 2003 13:40:33 -0500
Reply-To: <nickf@ontko.com>
From: "Nick Fankhauser" <nickf@ontko.com>
To: "Justin Long" <justinlong@strategicnetwork.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 13:39:09 -0500
Message-ID: <NEBBLAAHGLEEPCGOBHDGOEHOHNAA.nickf@ontko.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0)
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/443
X-Sequence-Number: 2736



>Can someone tell me what effective_cache_size should be set to?

You may be able to intuit this from my last post, but if I understand
correctly, what you should be doing is estimating how much memory is likely
to be "left over" for the OS to do disk caching with after all of the basic
needs of the OS, PostgreSQL & any other applications are taken care of. You
then tell postgresql what to expect in terms of caching resources by putting
this number into effective_cache_size, and this allows the query planner
come up with a strategy that is optimized for the expected cache size.

So the "formula" would be: Figure out how much memory is normally in use
allowing adequate margins, subtract this from your total RAM, and make the
remainder your effective_cache size.


-Nick




From josh@agliodbs.com Mon Jul  28 19:28:46 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 6B45FD1C95D
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 19:28:46 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 11181-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 28 Jul 2003 16:27:50 -0300 (ADT)
Received: from davinci.ethosmedia.com (209-128-84-228.bayarea.net
	[209.128.84.228])
	by svr1.postgresql.org (Postfix) with ESMTP id 57D69D1C50D
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Jul 2003 16:28:44 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3354608; Mon, 28 Jul 2003 12:29:04 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Justin Long" <justinlong@strategicnetwork.org>,
	<pgsql-performance@postgresql.org>
Subject: Re: Optimization
Date: Mon, 28 Jul 2003 12:27:44 -0700
User-Agent: KMail/1.4.3
References: <000f01c35523$f13dee90$ecd36044@tarzan>
In-Reply-To: <000f01c35523$f13dee90$ecd36044@tarzan>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307281227.44072.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/450
X-Sequence-Number: 2743

Justin,

> I am trying to understand the various factors used by Postgres to optimiz=
e.=20
I presently have a dual-866 Dell server with 1GB of memory. I've done the=
=20
following:

see: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
which has articles on .conf files.
(feel free to link these articles at PHPbuilder.com and elsewhere!)

> The databases I'm using have about 200,000+ news headlines with full-text=
=20
indexes (which range upwards of a few million records). They are updated=20
about every 5 to 10 minutes, which means I also have to run a vacuum about=
=20
once every 2 to 3 hours at least. As I get more updates obviously the=20
efficiency goes down. I'm trying to make the most of this system but don't=
=20
fully understand PG's optimization stuff.

Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing ev=
ery=20
10-15 minutes, not every 2-3 hours.   Regular VACUUM does not lock your=20
database.  You will also want to increase your FSM_relations so that VACUUM=
=20
is more effective/efficient; again, see the articles.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From shridhar_daithankar@persistent.co.in Tue Jul  29 06:50:16 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 35244D1C4B8
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 06:50:16 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 52965-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 03:49:18 -0300 (ADT)
Received: from smtp.pspl.co.in (unknown [202.54.11.65])
	by svr1.postgresql.org (Postfix) with ESMTP id 95C6BD1C4B7
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jul 2003 03:50:12 -0300 (ADT)
Received: (from root@localhost)
	by smtp.pspl.co.in (8.12.9/8.12.9) id h6T6mMIO026724
	for <pgsql-performance@postgresql.org>; Tue, 29 Jul 2003 12:18:22 +0530
Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161])
	(authenticated bits=0)
	by persistent.co.in (8.12.9/8.12.9) with ESMTP id h6T6mLRb026696
	for <pgsql-performance@postgresql.org>; Tue, 29 Jul 2003 12:18:22 +0530
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Date: Tue, 29 Jul 2003 12:21:59 +0530
MIME-Version: 1.0
Subject: Re: Optimization
Reply-To: shridhar_daithankar@persistent.co.in
Message-ID: <3F2666E7.7231.44FA68B@localhost>
In-reply-to: <200307281227.44072.josh@agliodbs.com>
References: <000f01c35523$f13dee90$ecd36044@tarzan>
X-mailer: Pegasus Mail for Windows (v4.12a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/456
X-Sequence-Number: 2749

On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every 
> 10-15 minutes, not every 2-3 hours.   Regular VACUUM does not lock your 
> database.  You will also want to increase your FSM_relations so that VACUUM 
> is more effective/efficient; again, see the articles.

There is an auto-vacuum daemon in contrib and if I understand it correctly, it 
is not getting much of a field testing. How about you guys installing it and 
trying it?

Bye
 Shridhar

--
O'Reilly's Law of the Kitchen:	Cleanliness is next to impossible




From blue.dragon@blueyonder.co.uk Tue Jul  29 07:14:18 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 5F20CD1C4CD
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 07:14:18 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 49986-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 04:13:20 -0300 (ADT)
Received: from smtp.blueyonder.co.uk (pc-62-31-245-46-gl.blueyonder.co.uk
	[62.31.245.46])
	by svr1.postgresql.org (Postfix) with ESMTP id 29B19D1C4C8
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jul 2003 04:14:16 -0300 (ADT)
Received: from reddragon.localdomain ([127.0.0.1] helo=localhost)
	by smtp.blueyonder.co.uk with esmtp (Exim 4.10) id 19hOhH-00008q-00
	for pgsql-performance@postgresql.org; Tue, 29 Jul 2003 08:14:59 +0100
Date: Tue, 29 Jul 2003 08:14:59 +0100 (BST)
From: Peter Childs <blue.dragon@blueyonder.co.uk>
X-X-Sender: peter@RedDragon.Childs
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimization
In-Reply-To: <3F2666E7.7231.44FA68B@localhost>
Message-ID: <Pine.LNX.4.44.0307290812420.511-100000@RedDragon.Childs>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/457
X-Sequence-Number: 2750

On Tue, 29 Jul 2003, Shridhar Daithankar wrote:

> On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> > Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every 
> > 10-15 minutes, not every 2-3 hours.   Regular VACUUM does not lock your 
> > database.  You will also want to increase your FSM_relations so that VACUUM 
> > is more effective/efficient; again, see the articles.
> 
> There is an auto-vacuum daemon in contrib and if I understand it correctly, it 
> is not getting much of a field testing. How about you guys installing it and 
> trying it?

	If there is such a daemon, what is it called? As I can't see it. 
Is it part of gborg?

Peter Childs




From shridhar_daithankar@persistent.co.in Tue Jul  29 07:19:45 2003
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 46B3DD1C4C8
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 07:19:45 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 52980-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 29 Jul 2003 04:18:47 -0300 (ADT)
Received: from smtp.pspl.co.in (unknown [202.54.11.65])
	by svr1.postgresql.org (Postfix) with ESMTP id 3DCB4D1C4D0
	for <pgsql-performance@postgresql.org>;
	Tue, 29 Jul 2003 04:19:42 -0300 (ADT)
Received: (from root@localhost)
	by smtp.pspl.co.in (8.12.9/8.12.9) id h6T7HrpW003006
	for <pgsql-performance@postgresql.org>; Tue, 29 Jul 2003 12:47:53 +0530
Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161])
	(authenticated bits=0)
	by persistent.co.in (8.12.9/8.12.9) with ESMTP id h6T7HqRZ002993
	for <pgsql-performance@postgresql.org>; Tue, 29 Jul 2003 12:47:52 +0530
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org
Date: Tue, 29 Jul 2003 12:51:30 +0530
MIME-Version: 1.0
Subject: Re: Optimization
Reply-To: shridhar_daithankar@persistent.co.in
Message-ID: <3F266DD2.30197.46AAB7A@localhost>
In-reply-to: <Pine.LNX.4.44.0307290812420.511-100000@RedDragon.Childs>
References: <3F2666E7.7231.44FA68B@localhost>
X-mailer: Pegasus Mail for Windows (v4.12a)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/458
X-Sequence-Number: 2751

On 29 Jul 2003 at 8:14, Peter Childs wrote:

> On Tue, 29 Jul 2003, Shridhar Daithankar wrote:
> 
> > On 28 Jul 2003 at 12:27, Josh Berkus wrote:
> > > Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every 
> > > 10-15 minutes, not every 2-3 hours.   Regular VACUUM does not lock your 
> > > database.  You will also want to increase your FSM_relations so that VACUUM 
> > > is more effective/efficient; again, see the articles.
> > 
> > There is an auto-vacuum daemon in contrib and if I understand it correctly, it 
> > is not getting much of a field testing. How about you guys installing it and 
> > trying it?
> 
> 	If there is such a daemon, what is it called? As I can't see it. 
> Is it part of gborg?

It is in sources. See contrib module in postgresql CVS, 7.4 beta if you prefer 
to wait till announement.

It is called as pgavd..

Bye
 Shridhar

--
squatcho, n.:	The button at the top of a baseball cap.		-- "Sniglets", Rich 
Hall & Friends




From croolists@gmail.com Mon Feb  28 09:44:01 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 284211337B49
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 28 Feb 2011 05:44:01 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 06072-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 28 Feb 2011 09:43:54 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qy0-f174.google.com (mail-qy0-f174.google.com
	[209.85.216.174])
	by mail.postgresql.org (Postfix) with ESMTP id AC0B81337B39
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Feb 2011 05:43:53 -0400 (AST)
Received: by qyk7 with SMTP id 7so2094504qyk.19
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Feb 2011 01:43:53 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:date:message-id:subject:from:to
	:content-type; bh=78du+313wJAthLLsjEpNC/GzhQydsa/c6yNnTGCtu/g=;
	b=QnHuAqoxuTH5FsHT+y+k+G4cqS1xwyWrkNyeFIi5Y/Ur6CSTRQZgiis8sxAxQmG/N6
	WGz9/d4v9vNohR7RPvOaFJ9nri7bYU47lNFeO43BjcukQIHwXXzbeocM+CxLW/F9/In3
	rdHCZ/2s8jvkDpBeI1lt1VEXgMKj3DweTKleM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=POVb68EiOMFLG11GRK2HoBw4BXGqns+suJIOYnrNOyjqrn7M7B/hTDfo497vF0thN3
	O4ZKfHnCipYEEV1gxj6aI87hxW04LJ2yfPZNOB0P1WOnMpGM9YHW1gx67azm+e3NjezB
	XAtDqLBFFJ1TnoF5IAo1rzpXipYJtGFlBBV/Q=
MIME-Version: 1.0
Received: by 10.229.97.139 with SMTP id l11mr3914854qcn.250.1298886233186;
	Mon, 28 Feb 2011 01:43:53 -0800 (PST)
Received: by 10.229.183.69 with HTTP; Mon, 28 Feb 2011 01:43:53 -0800 (PST)
Date: Mon, 28 Feb 2011 10:43:53 +0100
Message-ID: <AANLkTimDPhR+71a5Xrh8Jjf-91j2XrPE45ZF8YWyzrVp@mail.gmail.com>
Subject: optimization
From: croolyc <croolists@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.589 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_LOW=-0.7,
	T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201102/523
X-Sequence-Number: 42661

Hi!
can you help me with performance optimization
on my machine I have 8 databases with ca. 1-2GB

processor is:
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E3110  @ 3.00GHz
stepping        : 10
cpu MHz         : 2992.585
cache size      : 6144 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
...
processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E3110  @ 3.00GHz
stepping        : 10
cpu MHz         : 2992.585
cache size      : 6144 KB
physical id     : 0
siblings        : 2
core id         : 1
cpu cores       : 2
...
memory: 8GB (4*2GB ecc ram)

hdd: raid 1 (mdadm) - 2x 500GB, ext4 (mounted for /var, without /var/log)

powered with UPS

my English is not very good and I completely lost track while reading
the user manual
i find something like that
http://samiux.wordpress.com/2009/07/26/howto-performance-tuning-for-postgresql-on-ubuntudebian/
but i,m not so sure, reading mha220 comments, that is safe for me & my server :/

greatings



From Kevin.Grittner@wicourts.gov Mon Feb  28 14:57:16 2011
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id C336E1337B8C
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 28 Feb 2011 10:57:16 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 55303-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 28 Feb 2011 14:57:09 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id 3D5B7133616B
	for <pgsql-performance@postgresql.org>;
	Mon, 28 Feb 2011 10:57:08 -0400 (AST)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Mon, 28 Feb 2011 08:57:08 -0600
Message-Id: <4D6B6362020000250003B0B3@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.1 
Date: Mon, 28 Feb 2011 08:57:06 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "croolyc" <croolists@gmail.com>,<pgsql-performance@postgresql.org>
Subject: Re: optimization
References: <AANLkTimDPhR+71a5Xrh8Jjf-91j2XrPE45ZF8YWyzrVp@mail.gmail.com>
In-Reply-To: <AANLkTimDPhR+71a5Xrh8Jjf-91j2XrPE45ZF8YWyzrVp@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201102/527
X-Sequence-Number: 42665

croolyc <croolists@gmail.com> wrote:
 
> can you help me with performance optimization
 
For overall tuning you could start here:
 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
 
If, after some general tuning, you are having problems with slow
queries, it is best if you pick one and show it with EXPLAIN ANALYZE
output and the schema of the tables involved.
 
http://wiki.postgresql.org/wiki/SlowQueryQuestions
 
-Kevin