Re: Feature Request --- was: PostgreSQL Performance Tuning

On Fri, 27 Apr 2007, Josh Berkus wrote:

> Dan,
>
>> Yes, this is the classic problem.  I'm not demanding anyone pick up the
>> ball and jump on this today, tomorrow, etc.. I just think it would be
>> good for those who *could* make a difference to keep those goals in mind
>> when they continue.  If you have the right mindset, this problem will
>> fix itself over time.
>
> Don't I wish. Autotuning is *hard*. It took Oracle 6 years. It took
> Microsoft 3-4 years, and theirs still has major issues last I checked. And
> both of those DBs support less OSes than we do. I think it's going to
> take more than the *right mindset* and my spare time.

I think there are a couple different things here.

1. full autotuning

as you say, this is very hard and needs a lot of info about your
particular database useage.

2. getting defaults that are closer to right then current.

this is much easier. for this nobody is expecting that the values are
right, we're just begging for some tool to get us within an couple orders
of magnatude of what's correct.

the current defaults are appropriate for a single cpu with 10's of MB of
ram and a single drive

nowdays you have people trying to run quick-and-dirty tests on some spare
hardware they have laying around (waiting for another project) that's got
4-8 CPU's with 10's of GB of ram and a couple dozen drives

these people don't know about database tuneing, they can learn, but they
want to see if postgres is even in the ballpark. if the results are close
to acceptable they will ask questions and research the tuneing, but if the
results are orders of magnatude lower then they need to be they'll just
say that postgress is too slow and try another database.

an autodefault script that was written assuming that postgres has the box
to itself would be a wonderful start.

I think the next step would be to be able to tell the script 'only plan on
useing 1/2 of this box'

and beyond that would be the steps that you are thinking of where the
useage pattern is considered.

but when every performance question is answered with "did you change the
defaults? they are way too low for modern hardware, raise them by 2 orders
of magnatude and then we'll start investigating"

David Lang
>From pgsql-performance-owner(at)postgresql(dot)org Sat Apr 28 05:31:42 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
by postgresql.org (Postfix) with ESMTP id 3F1869FB607
for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>; Sat, 28 Apr 2007 05:31:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
with ESMTP id 15723-06 for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>;
Sat, 28 Apr 2007 05:31:38 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.233])
by postgresql.org (Postfix) with ESMTP id 04BFF9FB59A
for <pgsql-performance(at)postgresql(dot)org>; Sat, 28 Apr 2007 05:31:37 -0300 (ADT)
Received: by nz-out-0506.google.com with SMTP id s1so704120nze
for <pgsql-performance(at)postgresql(dot)org>; Sat, 28 Apr 2007 01:31:36 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed;
d=gmail.com; s=beta;
h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
b=BM60fgQwXPcs1GUfLpPBiCFj5cZ3ZqLypaxyNCB6+eG3++YbjB9vQMB0CByg7H9VZbROjDCBQaKYNcPXqTOcIOahC8BGUcaLwzGbnNivSrRw/fSOHvbj27DV+HyFqO4McuCcyAVEiMu53x6NLrW+WYN38IUsBA6Qq09UYTnewnI=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=beta;
h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
b=JEKwm6hM5kTSCrvKvJ2/XNkpHjnI6OimQ6r116Qq03eWF9W//Zk772yoJWl5eAGMOT/ylxOtxiAJPWMyaI2CauOodqtEW/0PqFAzOsG0kS4q1Jbd3xEScSJephvmtWzshiAykiVOg6HoEpzH51kLE5p99hU9jcdQh0FtIAWcqpA=
Received: by 10.115.74.1 with SMTP id b1mr1299635wal.1177749095891;
Sat, 28 Apr 2007 01:31:35 -0700 (PDT)
Received: by 10.114.12.15 with HTTP; Sat, 28 Apr 2007 01:31:35 -0700 (PDT)
Message-ID: <7be3f35d0704280131o6e6d2044n78c207e77b55f6a1(at)mail(dot)gmail(dot)com>
Date: Sat, 28 Apr 2007 10:31:35 +0200
From: "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com>
To: "Carlos Moreno" <moreno_pg(at)mochima(dot)com>
Subject: Re: Feature Request --- was: PostgreSQL Performance Tuning
Cc: "PostgreSQL Performance" <pgsql-performance(at)postgresql(dot)org>
In-Reply-To: <4631FA55(dot)4010406(at)mochima(dot)com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_Part_239023_22416331.1177749095813"
References: <OFC96167D5(dot)6BB63AC2-ON652572C9(dot)003B5263-652572C9(dot)003E4142(at)lntinfotech(dot)com>
<46310247(dot)2050802(at)pinpointresearch(dot)com> <463158DD(dot)3060602(at)mochima(dot)com>
<2679(dot)1177642153(at)sss(dot)pgh(dot)pa(dot)us> <4631FA55(dot)4010406(at)mochima(dot)com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/490
X-Sequence-Number: 24367

------=_Part_239023_22416331.1177749095813
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Carlos,

about your feature proposal: as I learned, nearly all
Perfomance.Configuration can be done by editing the .INI file and making th=
e
Postmaster re-read it.

So, WHY at all should those parameters be guessed at the installation of th=
e
database? Would'nt it be a saver point of time to have some postgresql-tun=
e

utilitiy, which gets run after the installation, maybe every once in a
while. That tool can check vital information like "Databasesize to memory
relation"; and suggest a new postgresql.ini.

That tool needs NO INTEGRATION whatsoever - it can be developed, deployed
totally independend and later only be bundled.

Does my suggestion make more sense now? Or is it still too unrealistic to
> make it work properly/safely?

And as this tool can be tested seperately, does not need a new initdb every
time ... it can be developed more easily.

Maybe there is even a pointy flashy version possible (perhaps even for mone=
y
:) which gives nice graphics and "optimized", like those Windows Optimizers=
.
:) I am sure, some DBAs in BIGCOMPs would be thrilled :)

May that be a possible way?

Harald

--=20
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstra=DFe 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.

------=_Part_239023_22416331.1177749095813
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Carlos,<br><br>about your feature proposal: as I learned, nearly all Perfom=
ance.Configuration can be done by editing the .INI file and making the Post=
master re-read it.<br><br>So, WHY at all should those parameters be guessed=
at the installation of the database? Would&#39;nt it be a saver point of t=
ime to have some&nbsp; postgresql-tune=20
<br>utilitiy, which gets run after the installation, maybe every once in a =
while. That tool can check vital information like &quot;Databasesize to mem=
ory relation&quot;; and suggest a new postgresql.ini.<br><br>That tool need=
s NO INTEGRATION whatsoever - it can be developed, deployed totally indepen=
dend and later only be bundled.=20
<br><br><div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px so=
lid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Does=
my suggestion make more sense now?&nbsp;&nbsp;Or is it still too unrealist=
ic to<br>make it work properly/safely?
</blockquote><div><br></div></div>And as this tool can be tested seperately=
, does not need a new initdb every time ... it can be developed more easily=
. <br><br>Maybe there is even a pointy flashy version possible (perhaps eve=
n for money :) which gives nice graphics and &quot;optimized&quot;, like th=
ose Windows Optimizers. :)&nbsp; I am sure, some DBAs in BIGCOMPs would be =
thrilled :)
<br><br>May that be a possible way?<br><br>Harald<br><br><br>-- <br>GHUM Ha=
rald Massa<br>persuadere et programmare<br>Harald Armin Massa<br>Reinsburgs=
tra=DFe 202b<br>70197 Stuttgart<br>0173/9409607<br>fx 01212-5-13695179 <br>
-<br>Python: the only language with more web frameworks than keywords.

------=_Part_239023_22416331.1177749095813--