From michel@edmi.de Sat Mar  5 12:26:39 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 55CC353C92
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat,  5 Mar 2005 12:26:39 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 25744-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat,  5 Mar 2005 12:26:37 +0000 (GMT)
Received: from natpreptil.rzone.de (natpreptil.rzone.de [81.169.145.163])
	by svr1.postgresql.org (Postfix) with ESMTP id 2ACD253C91
	for <pgsql-performance@postgresql.org>;
	Sat,  5 Mar 2005 12:26:36 +0000 (GMT)
Received: from mainframe (pD4B9E93C.dip.t-dialin.net [212.185.233.60])
	by post.webmailer.de (8.13.1/8.13.1) with SMTP id j25CQa7t005195
	for <pgsql-performance@postgresql.org>;
	Sat, 5 Mar 2005 13:26:37 +0100 (MET)
Message-ID: <020301c5217e$94643c00$65a8a8c0@mainframe>
From: "Michael Zoephel" <michel@edmi.de>
To: <pgsql-performance@postgresql.org>
Subject: performance problems
Date: Sat, 5 Mar 2005 13:26:37 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0200_01C52186.F5973970"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200503/117
X-Sequence-Number: 10822

This is a multi-part message in MIME format.

------=_NextPart_000_0200_01C52186.F5973970
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

I have performance problems with the postmaster-process. The CPU usage =
is permanently=20
around 90 %.

My DB:

    3,5 GB Size
    largest table ca. 700 000 rows

Most access:

    Finde a row - Update if exist Insert else

Average access:

    10 times a second

    every Query needs ca. 20 ms


All server options are still default !!

Any idea on wich "screw" I can turn ?.


Thanks for suggestions!


Michael Z=F6phel

e-Mail: zoephel@rechnerbetriebsgruppe.de

------=_NextPart_000_0200_01C52186.F5973970
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.2900.2604" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have performance problems with the=20
postmaster-process. The CPU usage is permanently&nbsp;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>around 90 %.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>My DB:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; 3,5 GB =
Size</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; largest table ca. =
700 000=20
rows</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Most access:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; Finde a row - Update =
if exist=20
Insert else</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Average access:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; 10 times a =
second</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; every Query needs =
ca. 20=20
ms</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>All server options are still default=20
!!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Any idea on wich "screw" I can turn =
?.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks for suggestions!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Michael Z=F6phel</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>e-Mail: <A=20
href=3D"mailto:zoephel@rechnerbetriebsgruppe.de">zoephel@rechnerbetriebsg=
ruppe.de</A><BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_0200_01C52186.F5973970--




From matthew@sorbs.net Wed Aug  30 10:30:44 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 397C99FA147
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 07:30:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 13544-07-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 07:30:31 -0300 (ADT)
X-Greylist: delayed 01:00:03.640238 by SQLgrey-
Received: from frankenstein.isux.com (mailbox.isux.com [203.173.10.51])
	by postgresql.org (Postfix) with SMTP id D307B9FA5DC
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 07:30:30 -0300 (ADT)
Received: from [203.173.10.53] by frankenstein.isux.com
	(Sun Java System Messaging Server 6.2-3.04 (built Jul 15 2005))
	with ESMTPA id <0J4T004DQ0T61600@frankenstein.isux.com> for
	pgsql-performance@postgresql.org; Wed, 30 Aug 2006 19:24:46 +1000 (EST)
Date: Wed, 30 Aug 2006 19:29:35 +1000
From: Matthew Sullivan <matthew@sorbs.net>
Subject: performance problems.
To: pgsql-performance@postgresql.org
Message-id: <44F55A7F.40305@sorbs.net>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.4)
	Gecko/20060516 SeaMonkey/1.0.2
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.069 tagged_above=0 required=5 tests=SPF_NEUTRAL
X-Spam-Level: *
X-Archive-Number: 200608/466
X-Sequence-Number: 20651

All,

Got a little bit of a performance problem I hope that can be resolved.

All the files/info I believe you are going to ask for are here:

http://www.au.sorbs.net/~matthew/postgres/30.8.06/

The odd thing was it originally was fast (1-2 seconds) which is all I 
need - the query is a permissions check and I added a permissions 
caching engine to the client code.  However, I was testing part of my 
new interface and added and "expired" some rows in the permissions, and 
authorisation tables (taking the row count to ~15) the performance 
dropped to 86seconds (ish) which is unusable... :-(

Unfortunately I do not have a query plan from before the performance issue.

work_mem has been adjusted from 512 to 8192, 65536 and 1000000 with no 
apparent effect.
random_page_cost has been 4 and 2 - 2 results in 89seconds for the query.

The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons and a 
Compaq RAID 3200 in RAID 5 configuration running across 3 spindles (34G 
total space).

The OS is FreeBSD 5.4-RELEASE-p14
The PG Version is 8.1.3

Solutions/tips greatly appreciated.

Regards,

Mat



From vivek@khera.org Wed Aug  30 14:11:51 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (unknown [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 32D3F9FB30E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 11:11:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 33833-01-7
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 11:11:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180])
	by postgresql.org (Postfix) with ESMTP id 7676D9FB2F0
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 11:10:31 -0300 (ADT)
Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103])
	by yertle.kcilink.com (Postfix) with ESMTP id 6122EB80F;
	Wed, 30 Aug 2006 10:10:29 -0400 (EDT)
In-Reply-To: <44F55A7F.40305@sorbs.net>
References: <44F55A7F.40305@sorbs.net>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-4-398685960;
	protocol="application/pkcs7-signature"
Message-Id: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
Cc: pgsql-performance@postgresql.org
From: Vivek Khera <vivek@khera.org>
Subject: Re: performance problems.
Date: Wed, 30 Aug 2006 10:10:28 -0400
To: Matthew Sullivan <matthew@sorbs.net>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200608/477
X-Sequence-Number: 20662


--Apple-Mail-4-398685960
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed


On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote:

> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons  
> and a Compaq RAID 3200 in RAID 5 configuration running across 3  
> spindles (34G total space).
>
> The OS is FreeBSD 5.4-RELEASE-p14
> The PG Version is 8.1.3

What else does this box do?

I think you should try these settings, which I use on 4GB dual  
Opteron boxes running FreeBSD 6.x dedicated to Postgres only.  Your  
effective_cache_size seems overly optimistic for freebsd.  cranking  
up the shared buffers seems to be one of the best bangs for the buck  
under pg 8.1.  I recently doubled them and nearly tripled my  
performance on a massive write-mostly (insert/update) load.  Unless  
your disk system is *really* slow, random_page_cost should be reduced  
from the default 4.

As you can see, I change *very* little from the default config.


shared_buffers = 70000                  # min 16 or  
max_connections*2, 8KB each
work_mem = 262144                       # min 64, size in KB
maintenance_work_mem = 524288           # min 1024, size in KB

checkpoint_segments = 256
checkpoint_timeout = 900

effective_cache_size = 27462            # `sysctl -n  
vfs.hibufspace` / 8192 (BLKSZ)
random_page_cost = 2

if you're feeling adventurous try these to reduce the checkpoint  
impact on the system:

bgwriter_lru_percent = 2.0
bgwriter_lru_maxpages = 40
bgwriter_all_percent = 0.666
bgwriter_all_maxpages = 40


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                MailerMailer, LLC     Rockville, MD
http://www.MailerMailer.com/                     +1-301-869-4449 x806



--Apple-Mail-4-398685960
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGlDCCAz8w
ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0
ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx
KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA
dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB
MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg
UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA
xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d
yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/
p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow
OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js
MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw
DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A
9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI
Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNNMIICtqADAgECAhA6sDoA4m3lcimf
yUtGSQgmMA0GCSqGSIb3DQEBBAUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u
c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz
dWluZyBDQTAeFw0wNjA1MDIxNDEyNDdaFw0wNzA1MDIxNDEyNDdaMIGKMR8wHQYDVQQDExZUaGF3
dGUgRnJlZW1haWwgTWVtYmVyMR4wHAYJKoZIhvcNAQkBFg92aXZla0BraGVyYS5vcmcxIDAeBgkq
hkiG9w0BCQEWEWtoZXJhQGtjaWxpbmsuY29tMSUwIwYJKoZIhvcNAQkBFhZ2aXZla0BtYWlsZXJt
YWlsZXIuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAq+zHKYq9vZ4Dq1itNqT/
OeJvWDvQ5JdQlRzwsJlvtJBknXsnOaJNabmjnu2s6XFMcP2srBZQ/WPvhsClajLQOcxnarrfB66N
DpMzXTxfYzX6m9TA/fEn64sNn+YnBZL6Dvid9kiAgP9LmqcTOIFdWUya3ZmQS5YTuLB+tkFSL8/h
+inDPN6dcsna8TcM1SAk+3upxOR7kyFM9T3vy25w62Nh1zK7Stp0vUZLU6GzzC1VvHGZHKGticD6
o3uHaMr2LCFjptoIcfZL75LO/UaR9o7smgboW4yJyW5g9ZasyYQUXyvkitfyZuVWATb8ZSHOkjWJ
872Pyf5+HOevVUI0aQIDAQABo1cwVTBFBgNVHREEPjA8gQ92aXZla0BraGVyYS5vcmeBEWtoZXJh
QGtjaWxpbmsuY29tgRZ2aXZla0BtYWlsZXJtYWlsZXIuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZI
hvcNAQEEBQADgYEAYpaFKDj47pTQIUQi28MgtjKophopv4QJjvspXmy0qxd8t/M/zc7HuBy3i/9a
PrXDgKyNBzzlFTasTazAY53ntVpqw9k1NOeHmH6o3j/DBVa49bC6bbWfp9UGOwYChlDR0tngQZyC
MDMZEdYv4zpGfBTku5m1jb8Yz/qYqV4FWB4xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUw
IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy
c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQOrA6AOJt5XIpn8lLRkkIJjAJBgUrDgMCGgUAoIIB
bzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA4MzAxNDEwMjla
MCMGCSqGSIb3DQEJBDEWBBSg596BsNa229CaJajjh/q+pAsvhDCBhQYJKwYBBAGCNxAEMXgwdjBi
MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG
A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJ
CCYwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENv
bnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElz
c3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJCCYwDQYJKoZIhvcNAQEBBQAEggEAdR4kPpLWQ58AZ7FA
wNI8U9RFcrav+TpmpIe4MPVa1LvL5wZqHuOJIDKkeAvC2oP4I6nQG/P7+gNmXOcKB8X+KmVtl2lu
G/7+ISG8pJA1TVt0GIFXiC7IBeZM23/iH8IgmWgu87DykvTxYDlgBw/7KXRrByr7LUPh2SH1SYpc
kyPsPRLQSRXmv6T1SRcj1eZUtxNv+vQt8rel4NGXd6/YhdW/0bDhpSVEBL4NXFQPO8sMipIn8U/N
p2JUeT9feKyd8tL3c7mBwA4w9vdVYCmh5ZMOcDYh8cKkamce0Nh215o9PAYnAY/NT7oP8HjSMisw
RCzFpoeLrg5Cz8p4aTETQAAAAAAAAA==

--Apple-Mail-4-398685960--



From jnasby@pervasive.com Wed Aug  30 16:27:19 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 18F139FA6B1
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 13:27:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 37441-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 16:27:08 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id 48A179FA4A5
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 13:27:00 -0300 (ADT)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 513E45643F; Wed, 30 Aug 2006 11:26:59 -0500 (CDT)
Received: (hashcash-sendmail, from uid 1001); Wed, 30 Aug 2006 11:26:58 -0500
Date: Wed, 30 Aug 2006 11:26:57 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Vivek Khera <vivek@khera.org>
Cc: Matthew Sullivan <matthew@sorbs.net>, pgsql-performance@postgresql.org
Subject: Re: performance problems.
Message-ID: <20060830162657.GD81373@pervasive.com>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Hashcash: 1:20:060830:vivek@khera.org::86xGpwA0dFMl7/fw:00ERS7
X-Hashcash: 
 1:20:060830:matthew@sorbs.net::JyjSeXGmkfOLT2nr:00000000000000000000
	0000000000000000000000000orW
X-Hashcash: 
 1:20:060830:pgsql-performance@postgresql.org::pcorom3hJGvizjc9:00000
	0000000000000000000000003lLT
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200608/480
X-Sequence-Number: 20665

On Wed, Aug 30, 2006 at 10:10:28AM -0400, Vivek Khera wrote:
> effective_cache_size = 27462            # `sysctl -n  
> vfs.hibufspace` / 8192 (BLKSZ)
> random_page_cost = 2

You misunderstand how effective_cache_size is used. It's the *only*
memory factor that plays a role in cost estimator functions. This means
it should include the memory set aside for caching in shared_buffers.

Also, hibufspace is only talking about filesystem buffers in FreeBSD,
which AFAIK has nothing to do with total memory available for caching,
since VM pages are also used to cache data.

Basically, your best bet for setting effective_cache_size is to use the
total memory in the machine, and substract some overhead for the OS and
other processes. I'll typically subtract 1G.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



From xelah-pgsql@xelah.com Wed Aug  30 18:22:44 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D00DF9FB348
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 15:22:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 60667-03-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 15:22:35 -0300 (ADT)
X-Greylist: delayed 06:59:39.938057 by SQLgrey-
Received: from wumpus.mythic-beasts.com (wumpus.mythic-beasts.com
	[212.69.37.9]) by postgresql.org (Postfix) with ESMTP id 1410E9FB333
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 15:22:20 -0300 (ADT)
Received: from sphinx.mythic-beasts.com ([212.69.37.6])
	by wumpus.mythic-beasts.com with esmtp (Exim 4.50)
	id 1GIUhf-0003rm-7O
	for pgsql-performance@postgresql.org; Wed, 30 Aug 2006 19:22:19 +0100
Received: from xelah (helo=localhost)
	by sphinx.mythic-beasts.com with local-esmtp (Exim 4.51)
	id 1GIUhe-0002F8-UG
	for pgsql-performance@postgresql.org; Wed, 30 Aug 2006 19:22:18 +0100
Date: Wed, 30 Aug 2006 19:22:18 +0100 (BST)
From: Alex Hayward <xelah-pgsql@xelah.com>
X-X-Sender: xelah@sphinx.mythic-beasts.com
To: pgsql-performance@postgresql.org
Subject: Re: performance problems.
In-Reply-To: <20060830162657.GD81373@pervasive.com>
Message-ID: <Pine.LNX.4.58.0608301912570.28033@sphinx.mythic-beasts.com>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<20060830162657.GD81373@pervasive.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200608/483
X-Sequence-Number: 20668

On Wed, 30 Aug 2006, Jim C. Nasby wrote:

> On Wed, Aug 30, 2006 at 10:10:28AM -0400, Vivek Khera wrote:
> > effective_cache_size = 27462            # `sysctl -n
> > vfs.hibufspace` / 8192 (BLKSZ)
> > random_page_cost = 2
>
> You misunderstand how effective_cache_size is used. It's the *only*
> memory factor that plays a role in cost estimator functions. This means
> it should include the memory set aside for caching in shared_buffers.
>
> Also, hibufspace is only talking about filesystem buffers in FreeBSD,
> which AFAIK has nothing to do with total memory available for caching,
> since VM pages are also used to cache data.

I believe it's not talking about quantities of buffers at all, but about
kernel virtual address space. It's something like the amount of kernel
virtual address space available for mapping buffer-cache pages in to
kernel memory. It certainly won't tell you (or even approximate) how much
PostgreSQL data is being cached by the OS. Cached PostgreSQL data will
appear in the active, inactive and cached values - and (AFAIK) there isn't
any distinction between file-backed pages and swap-backed pages amongst
those.



From matthew@sorbs.net Wed Aug  30 22:56:48 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 50D759FB316
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 19:56:48 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 52043-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 22:56:37 +0000 (UTC)
X-Greylist: delayed 00:31:21.654698 by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id BFA1A9FB35F
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 19:56:35 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from scorpion.sorbs.net (scorpion.sorbs.net [203.15.51.56])
	by svr4.postgresql.org (Postfix) with ESMTP id 96A5B5AF887
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 22:25:12 +0000 (GMT)
Received: from scorpion.sorbs.net (localhost [127.0.0.1])
	by scorpion.sorbs.net (Postfix) with ESMTP id 1F786A6CA2
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 08:24:59 +1000 (EST)
Received: from mail.sorbs.net (mail.sorbs.net [203.15.51.62])
	by scorpion.sorbs.net (Postfix) with ESMTP id C24B4A6C2D
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 08:24:58 +1000 (EST)
Received: from [10.200.254.98] (oblivion.isux.com [203.173.10.62])
	by nemesis.sorbs.net
	(iPlanet Messaging Server 5.2 HotFix 2.05 (built Mar  3 2005))
	with ESMTPSA id <0J4U00L050XHWS@nemesis.sorbs.net> for
	pgsql-performance@postgresql.org; Thu, 31 Aug 2006 08:24:58 +1000 (EST)
Date: Thu, 31 Aug 2006 08:17:46 +1000
From: Matthew Sullivan <matthew@sorbs.net>
Subject: Re: performance problems.
In-reply-to: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
To: pgsql-performance@postgresql.org
Message-id: <44F60E8A.9090700@sorbs.net>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
X-Accept-Language: en
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.7) Gecko/20050415
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.069 tagged_above=0 required=5 tests=SPF_NEUTRAL
X-Spam-Level: *
X-Archive-Number: 200608/485
X-Sequence-Number: 20670

Vivek Khera wrote:

>
> On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote:
>
>> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons  
>> and a Compaq RAID 3200 in RAID 5 configuration running across 3  
>> spindles (34G total space).
>>
>> The OS is FreeBSD 5.4-RELEASE-p14
>> The PG Version is 8.1.3
>
>
> What else does this box do?

Notihing - it's the developement DB and is dedicated to the development 
website - which has a total number of users of '1' ;-)

> I think you should try these settings, which I use on 4GB dual  
> Opteron boxes running FreeBSD 6.x dedicated to Postgres only.  Your  
> effective_cache_size seems overly optimistic for freebsd.  cranking  
> up the shared buffers seems to be one of the best bangs for the buck  
> under pg 8.1.  I recently doubled them and nearly tripled my  
> performance on a massive write-mostly (insert/update) load.  Unless  
> your disk system is *really* slow, random_page_cost should be reduced  
> from the default 4.

I'll give this a try.

>
> As you can see, I change *very* little from the default config.
>
>
> shared_buffers = 70000                  # min 16 or  
> max_connections*2, 8KB each
> work_mem = 262144                       # min 64, size in KB
> maintenance_work_mem = 524288           # min 1024, size in KB
>
> checkpoint_segments = 256
> checkpoint_timeout = 900
>
> effective_cache_size = 27462            # `sysctl -n  vfs.hibufspace` 
> / 8192 (BLKSZ)
> random_page_cost = 2
>
> if you're feeling adventurous try these to reduce the checkpoint  
> impact on the system:
>
> bgwriter_lru_percent = 2.0
> bgwriter_lru_maxpages = 40
> bgwriter_all_percent = 0.666
> bgwriter_all_maxpages = 40
>
That might have some impact on the production server (which is also 
running PG - but the old DB and RT3) however the new DB is only me in 
devel, so I think that it will not have much of an effect (I'll still 
try it though)

Regards,

Mat



From markir@paradise.net.nz Wed Aug  30 23:38:08 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id E03439FB3A4
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 20:38:08 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 88443-01-5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 20:37:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from linda-3.paradise.net.nz (bm-3a.paradise.net.nz
 [203.96.152.182])
	by postgresql.org (Postfix) with ESMTP id 4D9F99FB37D
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 20:37:20 -0300 (ADT)
Received: from smtp-2.paradise.net.nz
	(tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by
	linda-3.paradise.net.nz (Paradise.net.nz) with ESMTP id
	<0J4U00CSG4A0YB@linda-3.paradise.net.nz> for
	pgsql-performance@postgresql.org; Thu, 31 Aug 2006 11:37:13 +1200 (NZST)
Received: from [192.168.1.11]
	(218-101-29-170.dsl.clear.net.nz [218.101.29.170])	by
	smtp-2.paradise.net.nz (Postfix) with ESMTP id 55CC5323B25;
	Thu, 31 Aug 2006 11:36:26 +1200 (NZST)
Date: Thu, 31 Aug 2006 11:36:11 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: performance problems.
In-reply-to: <44F55A7F.40305@sorbs.net>
To: Matthew Sullivan <matthew@sorbs.net>
Cc: pgsql-performance@postgresql.org
Message-id: <44F620EB.6020206@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Thunderbird 1.5.0.4 (X11/20060704)
References: <44F55A7F.40305@sorbs.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.029 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200608/486
X-Sequence-Number: 20671

Matthew Sullivan wrote:

> 
> The OS is FreeBSD 5.4-RELEASE-p14
> The PG Version is 8.1.3
> 
> Solutions/tips greatly appreciated.
> 

This won't help this particular query, but 6.1-RELEASE will possibly be 
a better performer generally, in particular for your SMP system - e.g. 
the vfs layer is no longer under the Giant lock in the 6.x series, so 
parallel io should be much better!

Cheers

Mark



From pg@fastcrypt.com Thu Aug  31 00:08:28 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 2306A9FB260
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 21:08:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 91039-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 30 Aug 2006 21:08:15 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from net2.micro-automation.com (net2.micro-automation.com
	[64.7.141.29]) by postgresql.org (Postfix) with SMTP id 6D8D79FB2FA
	for <pgsql-performance@postgresql.org>;
	Wed, 30 Aug 2006 20:48:15 -0300 (ADT)
Received: (qmail 25903 invoked from network); 30 Aug 2006 23:48:13 -0000
Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116)
	by net2.micro-automation.com with SMTP; 30 Aug 2006 23:48:13 -0000
In-Reply-To: <BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
Cc: Matthew Sullivan <matthew@sorbs.net>,
 pgsql-performance@postgresql.org
Content-Transfer-Encoding: 7bit
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: performance problems.
Date: Wed, 30 Aug 2006 19:48:12 -0400
To: Vivek Khera <vivek@khera.org>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.035 tagged_above=0 required=5 tests=AWL, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/487
X-Sequence-Number: 20672


On 30-Aug-06, at 10:10 AM, Vivek Khera wrote:

>
> On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote:
>
>> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons  
>> and a Compaq RAID 3200 in RAID 5 configuration running across 3  
>> spindles (34G total space).
>>
>> The OS is FreeBSD 5.4-RELEASE-p14
>> The PG Version is 8.1.3
>
> What else does this box do?
>
> I think you should try these settings, which I use on 4GB dual  
> Opteron boxes running FreeBSD 6.x dedicated to Postgres only.  Your  
> effective_cache_size seems overly optimistic for freebsd.  cranking  
> up the shared buffers seems to be one of the best bangs for the  
> buck under pg 8.1.  I recently doubled them and nearly tripled my  
> performance on a massive write-mostly (insert/update) load.  Unless  
> your disk system is *really* slow, random_page_cost should be  
> reduced from the default 4.
>
Actually unless you have a ram disk you should probably leave  
random_page_cost at 4, shared buffers should be 2x what you have  
here, maintenance work mem is pretty high
effective cache should be much larger 3/4 of 4G or about 360000

Setting work _mem this high should be done with caution. From the  
manual "Note that for a complex query, several sort or hash  
operations might be running in parallel; each one will be allowed to  
use as much memory as this value specifies before it starts to put  
data into temporary files. Also, several running sessions could be  
doing such operations concurrently. So the total memory used could be  
many times the value of work_mem"
> As you can see, I change *very* little from the default config.
>
>
> shared_buffers = 70000                  # min 16 or  
> max_connections*2, 8KB each
> work_mem = 262144                       # min 64, size in KB
> maintenance_work_mem = 524288           # min 1024, size in KB
>
> checkpoint_segments = 256
> checkpoint_timeout = 900
>
> effective_cache_size = 27462            # `sysctl -n  
> vfs.hibufspace` / 8192 (BLKSZ)
> random_page_cost = 2
>
> if you're feeling adventurous try these to reduce the checkpoint  
> impact on the system:
>
> bgwriter_lru_percent = 2.0
> bgwriter_lru_maxpages = 40
> bgwriter_all_percent = 0.666
> bgwriter_all_maxpages = 40
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                MailerMailer, LLC     Rockville, MD
> http://www.MailerMailer.com/                     +1-301-869-4449 x806
>
>




From vivek@khera.org Thu Aug  31 18:11:19 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 30ECB9FB428
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 15:11:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 36199-03-3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 15:11:07 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180])
	by postgresql.org (Postfix) with ESMTP id 272499FB425
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 15:10:58 -0300 (ADT)
Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103])
	by yertle.kcilink.com (Postfix) with ESMTP id 5FB04B814
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 14:10:56 -0400 (EDT)
Mime-Version: 1.0 (Apple Message framework v752.2)
In-Reply-To: <20060830162657.GD81373@pervasive.com>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<20060830162657.GD81373@pervasive.com>
Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-5-499513155;
	protocol="application/pkcs7-signature"
Message-Id: <D8E2BA61-A5A2-433E-ABFF-28F7D440BF20@khera.org>
From: Vivek Khera <vivek@khera.org>
Subject: Re: performance problems.
Date: Thu, 31 Aug 2006 14:10:55 -0400
To: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200608/494
X-Sequence-Number: 20679


--Apple-Mail-5-499513155
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed


On Aug 30, 2006, at 12:26 PM, Jim C. Nasby wrote:

> You misunderstand how effective_cache_size is used. It's the *only*
> memory factor that plays a role in cost estimator functions. This  
> means
> it should include the memory set aside for caching in shared_buffers.
>
> Also, hibufspace is only talking about filesystem buffers in FreeBSD,
> which AFAIK has nothing to do with total memory available for caching,
> since VM pages are also used to cache data.
>

Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com>  
from the October 2003 archives.  (I'd provide a full link to it, but  
the http://archives.postgresql.org/pgsql-performance/ archives are  
botched -- only some posts are on the browsable archive but it is all  
in the raw mailbox download, so that's the only way to get the full  
message.)  It reads in part:

From: Sean Chittenden <sean@chittenden.org>
Date: Sat, 11 Oct 2003 02:23:08 -0700

  [...]
 > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
 >
 > I've used it for my dedicated servers.  Is this calculation correct?

Yes, or it's real close at least.  vfs.hibufspace is the amount of
kernel space that's used for caching IO operations (minus the
necessary space taken for the kernel).  If you're real paranoid, you
could do some kernel profiling and figure out how much of the cache is
actually disk IO and multiply the above by some percentage, say 80%?
I haven't found it necessary to do so yet.  Since hibufspace is all IO
and caching any net activity is kinda pointless and I assume that 100%
of it is used for a disk cache and don't use a multiplier.  The 8192,
however, is the size of a PG page, so, if you tweak PG's page size,
you have to change this constant (*grumbles*).

--END QUOTE--

Given who Sean is, I tend to believe him.  Whether this is still  
valid for FreeBSD 6.x, I'm unable to verify.

> Basically, your best bet for setting effective_cache_size is to use  
> the
> total memory in the machine, and substract some overhead for the OS  
> and
> other processes. I'll typically subtract 1G.

I'll give this a whirl and see if it helps.

Any opinions on using the FreeBSD sysctl kern.ipc.shm_use_phys to  
bypass the VM system for shared pages?


--Apple-Mail-5-499513155
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGlDCCAz8w
ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0
ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx
KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA
dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB
MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg
UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA
xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d
yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/
p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow
OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js
MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw
DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A
9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI
Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNNMIICtqADAgECAhA6sDoA4m3lcimf
yUtGSQgmMA0GCSqGSIb3DQEBBAUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u
c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz
dWluZyBDQTAeFw0wNjA1MDIxNDEyNDdaFw0wNzA1MDIxNDEyNDdaMIGKMR8wHQYDVQQDExZUaGF3
dGUgRnJlZW1haWwgTWVtYmVyMR4wHAYJKoZIhvcNAQkBFg92aXZla0BraGVyYS5vcmcxIDAeBgkq
hkiG9w0BCQEWEWtoZXJhQGtjaWxpbmsuY29tMSUwIwYJKoZIhvcNAQkBFhZ2aXZla0BtYWlsZXJt
YWlsZXIuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAq+zHKYq9vZ4Dq1itNqT/
OeJvWDvQ5JdQlRzwsJlvtJBknXsnOaJNabmjnu2s6XFMcP2srBZQ/WPvhsClajLQOcxnarrfB66N
DpMzXTxfYzX6m9TA/fEn64sNn+YnBZL6Dvid9kiAgP9LmqcTOIFdWUya3ZmQS5YTuLB+tkFSL8/h
+inDPN6dcsna8TcM1SAk+3upxOR7kyFM9T3vy25w62Nh1zK7Stp0vUZLU6GzzC1VvHGZHKGticD6
o3uHaMr2LCFjptoIcfZL75LO/UaR9o7smgboW4yJyW5g9ZasyYQUXyvkitfyZuVWATb8ZSHOkjWJ
872Pyf5+HOevVUI0aQIDAQABo1cwVTBFBgNVHREEPjA8gQ92aXZla0BraGVyYS5vcmeBEWtoZXJh
QGtjaWxpbmsuY29tgRZ2aXZla0BtYWlsZXJtYWlsZXIuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZI
hvcNAQEEBQADgYEAYpaFKDj47pTQIUQi28MgtjKophopv4QJjvspXmy0qxd8t/M/zc7HuBy3i/9a
PrXDgKyNBzzlFTasTazAY53ntVpqw9k1NOeHmH6o3j/DBVa49bC6bbWfp9UGOwYChlDR0tngQZyC
MDMZEdYv4zpGfBTku5m1jb8Yz/qYqV4FWB4xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUw
IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy
c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQOrA6AOJt5XIpn8lLRkkIJjAJBgUrDgMCGgUAoIIB
bzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA4MzExODEwNTZa
MCMGCSqGSIb3DQEJBDEWBBTI79gpiyTXQoWoiDJEgYtjVuKr+jCBhQYJKwYBBAGCNxAEMXgwdjBi
MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG
A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJ
CCYwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENv
bnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElz
c3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJCCYwDQYJKoZIhvcNAQEBBQAEggEAF+Vr+ZD1jdKCM4XJ
cqghLTuaQoZvC9cdYxDKulneeYpmRL+zg0MClzjo/pd2hQokZQYg9wZfxrPOwAUmTTGirdIIf+tn
MkP0yoQL41GC0+XNGzIEL6ukdNZmoZaWNF+YOrGwzxRmnGuShirCoFIqTNo1//IFHXkl7AI2Z4VY
0CE5/l7y0v1VJJBZudp1Jn8E56HmhTVSbDoCrIUtoYmKERAwUZUoFlqQ/s74MOlAaPWq10Ba/oA1
UeMy/Oe88A0ljCUxmUtHP0WEkTn/UMJwMnwFZJINNYIdW8sP6hw9niBKBuxIv2s6/ad5PoHFi5H6
dJRnkC36i4rpz+2o+y9DFQAAAAAAAA==

--Apple-Mail-5-499513155--



From vivek@khera.org Thu Aug  31 18:15:36 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 6FAD09FB413
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 15:15:36 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 23124-07-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 18:15:27 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180])
	by postgresql.org (Postfix) with ESMTP id 29B9C9FB42B
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 15:15:16 -0300 (ADT)
Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103])
	by yertle.kcilink.com (Postfix) with ESMTP id 8C1D5B814
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 14:15:15 -0400 (EDT)
Mime-Version: 1.0 (Apple Message framework v752.2)
In-Reply-To: <BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-6-499772001;
	protocol="application/pkcs7-signature"
Message-Id: <A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
From: Vivek Khera <vivek@khera.org>
Subject: Re: performance problems.
Date: Thu, 31 Aug 2006 14:15:14 -0400
To: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200608/497
X-Sequence-Number: 20682


--Apple-Mail-6-499772001
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed


On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote:

> Actually unless you have a ram disk you should probably leave  
> random_page_cost at 4, shared buffers should be 2x what you have  
> here, maintenance work mem is pretty high
> effective cache should be much larger 3/4 of 4G or about 360000
>

I've been pondering bumping up SHM settings more, but it is a very  
big imposition to have to restart the production server to do so.   
This weekend being a long weekend might be a good opportunity to try  
it, though...

As for maintenence mem, when you have HUGE tables, you want to give a  
lot of memory to vacuum.  With 4GB of RAM giving it 512MB is not an  
issue.

The effective cache size is the big issue with FreeBSD.  There are  
opposing claims of how much memory it will use for cache, and throw  
in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the VM  
system entirely, and who knows what's going on.

> Setting work _mem this high should be done with caution. From the  
> manual "Note that for a complex query, several sort or hash  
> operations might be running in parallel; each one will be allowed  
> to use as much memory as this value specifies before it starts to  
> put data into temporary files. Also, several running sessions could  
> be doing such operations concurrently. So the total memory used  
> could be many times the value of work_mem"

Again, with boat-loads of RAM why not let the queries use it?  We  
only have a handful of connections at a time so that's not eating up  
much memory...


--Apple-Mail-6-499772001
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGlDCCAz8w
ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0
ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx
KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA
dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB
MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg
UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA
xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d
yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/
p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow
OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js
MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw
DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A
9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI
Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNNMIICtqADAgECAhA6sDoA4m3lcimf
yUtGSQgmMA0GCSqGSIb3DQEBBAUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u
c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz
dWluZyBDQTAeFw0wNjA1MDIxNDEyNDdaFw0wNzA1MDIxNDEyNDdaMIGKMR8wHQYDVQQDExZUaGF3
dGUgRnJlZW1haWwgTWVtYmVyMR4wHAYJKoZIhvcNAQkBFg92aXZla0BraGVyYS5vcmcxIDAeBgkq
hkiG9w0BCQEWEWtoZXJhQGtjaWxpbmsuY29tMSUwIwYJKoZIhvcNAQkBFhZ2aXZla0BtYWlsZXJt
YWlsZXIuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAq+zHKYq9vZ4Dq1itNqT/
OeJvWDvQ5JdQlRzwsJlvtJBknXsnOaJNabmjnu2s6XFMcP2srBZQ/WPvhsClajLQOcxnarrfB66N
DpMzXTxfYzX6m9TA/fEn64sNn+YnBZL6Dvid9kiAgP9LmqcTOIFdWUya3ZmQS5YTuLB+tkFSL8/h
+inDPN6dcsna8TcM1SAk+3upxOR7kyFM9T3vy25w62Nh1zK7Stp0vUZLU6GzzC1VvHGZHKGticD6
o3uHaMr2LCFjptoIcfZL75LO/UaR9o7smgboW4yJyW5g9ZasyYQUXyvkitfyZuVWATb8ZSHOkjWJ
872Pyf5+HOevVUI0aQIDAQABo1cwVTBFBgNVHREEPjA8gQ92aXZla0BraGVyYS5vcmeBEWtoZXJh
QGtjaWxpbmsuY29tgRZ2aXZla0BtYWlsZXJtYWlsZXIuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZI
hvcNAQEEBQADgYEAYpaFKDj47pTQIUQi28MgtjKophopv4QJjvspXmy0qxd8t/M/zc7HuBy3i/9a
PrXDgKyNBzzlFTasTazAY53ntVpqw9k1NOeHmH6o3j/DBVa49bC6bbWfp9UGOwYChlDR0tngQZyC
MDMZEdYv4zpGfBTku5m1jb8Yz/qYqV4FWB4xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUw
IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy
c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQOrA6AOJt5XIpn8lLRkkIJjAJBgUrDgMCGgUAoIIB
bzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA4MzExODE1MTVa
MCMGCSqGSIb3DQEJBDEWBBRfFrvtyEfuq0tH8J91yGS9cBkGEDCBhQYJKwYBBAGCNxAEMXgwdjBi
MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG
A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJ
CCYwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENv
bnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElz
c3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJCCYwDQYJKoZIhvcNAQEBBQAEggEAbrKEoKDj8mG2qPU1
AXinVSigOYx4XNqzzTQNHZqbaedp199yDpEMmXNglh7IY13UuuDrI9vzAOxt0f6Dl/pIg/K38p3q
ZkfsrZ7/k2Md6FOFsIUMOWxaOSwhK+WXeB3CR1tcIMW7LlUXHtwlpPRruSwcMTUpabhqo+YjE0jY
5cNs2Pm4NYAzmqdwHdFdmebmVQFMl0V4zI4PRUaHx4oD5xDPctOtOaLZLm+3uqPqiqVtl2bqid+k
XfQ5Kq7Jt2JWAz2/qcUsmVMFpAck3nOHqQLAzD9Xfuzps9LVV4pvC10Xw/1fwp5lRr+z6bi4DbI2
JcSMLpZhHOuLIYcJqDUMSgAAAAAAAA==

--Apple-Mail-6-499772001--



From pg@fastcrypt.com Thu Aug  31 18:28:41 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 1318E9FA418
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 15:28:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 37526-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 15:28:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from net2.micro-automation.com (net2.micro-automation.com
	[64.7.141.29]) by postgresql.org (Postfix) with SMTP id 1A9629FB3D6
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 15:28:33 -0300 (ADT)
Received: (qmail 5557 invoked from network); 31 Aug 2006 18:28:33 -0000
Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116)
	by net2.micro-automation.com with SMTP; 31 Aug 2006 18:28:33 -0000
In-Reply-To: <A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<BC2892F0-2DF0-4DB4-85A7-C80832181FBD@fastcrypt.com>
	<A313FD0D-0D27-4022-9C8F-6A0EE7C3E7AC@khera.org>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <43D1B021-7BDB-4CC4-BB7A-A6DFDAEF1537@fastcrypt.com>
Cc: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Dave Cramer <pg@fastcrypt.com>
Subject: Re: performance problems.
Date: Thu, 31 Aug 2006 14:28:31 -0400
To: Vivek Khera <vivek@khera.org>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.043 tagged_above=0 required=5 tests=AWL, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/499
X-Sequence-Number: 20684


On 31-Aug-06, at 2:15 PM, Vivek Khera wrote:

>
> On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote:
>
>> Actually unless you have a ram disk you should probably leave  
>> random_page_cost at 4, shared buffers should be 2x what you have  
>> here, maintenance work mem is pretty high
>> effective cache should be much larger 3/4 of 4G or about 360000
>>
>
> I've been pondering bumping up SHM settings more, but it is a very  
> big imposition to have to restart the production server to do so.   
> This weekend being a long weekend might be a good opportunity to  
> try it, though...
>
> As for maintenence mem, when you have HUGE tables, you want to give  
> a lot of memory to vacuum.  With 4GB of RAM giving it 512MB is not  
> an issue.
>
> The effective cache size is the big issue with FreeBSD.  There are  
> opposing claims of how much memory it will use for cache, and throw  
> in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the  
> VM system entirely, and who knows what's going on.

Yes, I have to admit, the setting I proposed works well for linux,  
but may not for bsd.
>
>> Setting work _mem this high should be done with caution. From the  
>> manual "Note that for a complex query, several sort or hash  
>> operations might be running in parallel; each one will be allowed  
>> to use as much memory as this value specifies before it starts to  
>> put data into temporary files. Also, several running sessions  
>> could be doing such operations concurrently. So the total memory  
>> used could be many times the value of work_mem"
>
> Again, with boat-loads of RAM why not let the queries use it?  We  
> only have a handful of connections at a time so that's not eating  
> up much memory...
>
As long as you are aware of the ramifications....




From tgl@sss.pgh.pa.us Thu Aug  31 19:08:14 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 0C5DE9FB3F6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 16:08:14 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 73785-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 19:08:06 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 0BB699FB3ED
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 16:08:04 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k7VJ83tv022782;
	Thu, 31 Aug 2006 15:08:03 -0400 (EDT)
To: Vivek Khera <vivek@khera.org>
cc: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
Subject: Re: performance problems. 
In-reply-to: <D8E2BA61-A5A2-433E-ABFF-28F7D440BF20@khera.org> 
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<20060830162657.GD81373@pervasive.com>
	<D8E2BA61-A5A2-433E-ABFF-28F7D440BF20@khera.org>
Comments: In-reply-to Vivek Khera <vivek@khera.org>
	message dated "Thu, 31 Aug 2006 14:10:55 -0400"
Date: Thu, 31 Aug 2006 15:08:03 -0400
Message-ID: <22781.1157051283@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200608/500
X-Sequence-Number: 20685

Vivek Khera <vivek@khera.org> writes:
> Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com>  
> from the October 2003 archives.  (I'd provide a full link to it, but  
> the http://archives.postgresql.org/pgsql-performance/ archives are  
> botched --

Still?  I found it easily enough with a search for 'hibufspace':
http://archives.postgresql.org/pgsql-performance/2003-10/msg00383.php

			regards, tom lane



From vivek@khera.org Thu Aug  31 19:49:04 2006
X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 102F09FB448
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 16:49:04 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 74597-07-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 31 Aug 2006 16:48:49 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from yertle.kcilink.com (yertle.kcilink.com [65.205.34.180])
	by postgresql.org (Postfix) with ESMTP id 8B9959FB46F
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 16:40:40 -0300 (ADT)
Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103])
	by yertle.kcilink.com (Postfix) with ESMTP id 15D81B80F
	for <pgsql-performance@postgresql.org>;
	Thu, 31 Aug 2006 15:40:40 -0400 (EDT)
Mime-Version: 1.0 (Apple Message framework v752.2)
In-Reply-To: <22781.1157051283@sss.pgh.pa.us>
References: <44F55A7F.40305@sorbs.net>
	<BA0105C5-8551-49A6-B131-00D814DDFDA0@khera.org>
	<20060830162657.GD81373@pervasive.com>
	<D8E2BA61-A5A2-433E-ABFF-28F7D440BF20@khera.org>
	<22781.1157051283@sss.pgh.pa.us>
Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-8-504896495;
	protocol="application/pkcs7-signature"
Message-Id: <70E4013D-8F10-4FD6-B09A-64FF95B7F155@khera.org>
From: Vivek Khera <vivek@khera.org>
Subject: Re: performance problems. 
Date: Thu, 31 Aug 2006 15:40:39 -0400
To: "Pgsql-Performance ((((E-mail))))" <pgsql-performance@postgresql.org>
X-Mailer: Apple Mail (2.752.2)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200608/501
X-Sequence-Number: 20686


--Apple-Mail-8-504896495
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed


On Aug 31, 2006, at 3:08 PM, Tom Lane wrote:

> Vivek Khera <vivek@khera.org> writes:
>> Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com>
>> from the October 2003 archives.  (I'd provide a full link to it, but
>> the http://archives.postgresql.org/pgsql-performance/ archives are
>> botched --
>
> Still?  I found it easily enough with a search for 'hibufspace':
> http://archives.postgresql.org/pgsql-performance/2003-10/msg00383.php
>
> 			regards, tom lane

go to "view by thread" or "view by date" for October 2003.  Or August  
2003.  Most messages are missing.
--Apple-Mail-8-504896495
Content-Transfer-Encoding: base64
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Disposition: attachment;
	filename=smime.p7s

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGlDCCAz8w
ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0
ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx
KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA
dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB
MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg
UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA
xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d
yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/
p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow
OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js
MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw
DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A
9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI
Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNNMIICtqADAgECAhA6sDoA4m3lcimf
yUtGSQgmMA0GCSqGSIb3DQEBBAUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u
c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz
dWluZyBDQTAeFw0wNjA1MDIxNDEyNDdaFw0wNzA1MDIxNDEyNDdaMIGKMR8wHQYDVQQDExZUaGF3
dGUgRnJlZW1haWwgTWVtYmVyMR4wHAYJKoZIhvcNAQkBFg92aXZla0BraGVyYS5vcmcxIDAeBgkq
hkiG9w0BCQEWEWtoZXJhQGtjaWxpbmsuY29tMSUwIwYJKoZIhvcNAQkBFhZ2aXZla0BtYWlsZXJt
YWlsZXIuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAq+zHKYq9vZ4Dq1itNqT/
OeJvWDvQ5JdQlRzwsJlvtJBknXsnOaJNabmjnu2s6XFMcP2srBZQ/WPvhsClajLQOcxnarrfB66N
DpMzXTxfYzX6m9TA/fEn64sNn+YnBZL6Dvid9kiAgP9LmqcTOIFdWUya3ZmQS5YTuLB+tkFSL8/h
+inDPN6dcsna8TcM1SAk+3upxOR7kyFM9T3vy25w62Nh1zK7Stp0vUZLU6GzzC1VvHGZHKGticD6
o3uHaMr2LCFjptoIcfZL75LO/UaR9o7smgboW4yJyW5g9ZasyYQUXyvkitfyZuVWATb8ZSHOkjWJ
872Pyf5+HOevVUI0aQIDAQABo1cwVTBFBgNVHREEPjA8gQ92aXZla0BraGVyYS5vcmeBEWtoZXJh
QGtjaWxpbmsuY29tgRZ2aXZla0BtYWlsZXJtYWlsZXIuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZI
hvcNAQEEBQADgYEAYpaFKDj47pTQIUQi28MgtjKophopv4QJjvspXmy0qxd8t/M/zc7HuBy3i/9a
PrXDgKyNBzzlFTasTazAY53ntVpqw9k1NOeHmH6o3j/DBVa49bC6bbWfp9UGOwYChlDR0tngQZyC
MDMZEdYv4zpGfBTku5m1jb8Yz/qYqV4FWB4xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUw
IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy
c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQOrA6AOJt5XIpn8lLRkkIJjAJBgUrDgMCGgUAoIIB
bzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA4MzExOTQwMzla
MCMGCSqGSIb3DQEJBDEWBBT23fw15kC1kgoLx4MwIqMeYai4BjCBhQYJKwYBBAGCNxAEMXgwdjBi
MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG
A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJ
CCYwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENv
bnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElz
c3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJCCYwDQYJKoZIhvcNAQEBBQAEggEAjsAxLVFNv054w6H+
Jrcyi5Hj7iC2CbscfhkamdlQeXKgIiBE4FqpAixIj7rEqwMWMm0ABhAo3TKZZhU2BGRZ7GXVjsf6
ox/QSnkLqu+DfdoBAreDdnwSbrHDMENZM8NxZe9y1CjZhIZdeEzLJOOfvTcz7f9/Ammn5ZY6L/Uf
Ki/FADn26R5ftMR611H52BJhHMWsnk6vPx0PluUiwfCPTE2RSTLRPNSiD6Khi3Zi4wZ2pwDBBcFJ
ayBS6dYAGCRAo0rk6TSZlYlmInT8OswFXWauQ4KA630EXRYsCDqWZR8NOVElrL7zNsWqHMvBzP/q
kH/b3Z1OnqwLtYGDVg0SAQAAAAAAAA==

--Apple-Mail-8-504896495--