Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 039F49FA4BB for ; Thu, 8 Mar 2007 08:23:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 92069-08 for ; Thu, 8 Mar 2007 08:23:46 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.225]) by postgresql.org (Postfix) with ESMTP id C6D129FA43B for ; Thu, 8 Mar 2007 08:23:50 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so483891wxd for ; Thu, 08 Mar 2007 04:23:49 -0800 (PST) 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=t5Sd5VpKQRFEp8iBa0QnP1qqS/hShuMqew61AST/jIbEST3hd1XSdhQwYO+vHVGIP19Nk5niPBvMW5Wicr92YsKTOJ1Vz+GZ8QbnzKTWww6eLvm3zevQ15FeZspj8LsDMBuxJGui+cEw1ZA+tIjLHZ25RcD8iF9GratsrbCozRQ= 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=h2H87w7MYVYuZ83PTePDjRlEIiXQH8iphNlxlfrA0Kyp4b6YFwzaYmSQuVBhanpDbnLSsuUdJgHd/MiZu9YnLkAhdlLLCc769a6gAcwLZoEvqKiA+CQLQoJlTCvUHUxFLWTzqfJtFoysV890Q5HtskcIBYWvg1pYBNvNkQIhF6A= Received: by 10.70.35.1 with SMTP id i1mr597099wxi.1173356629648; Thu, 08 Mar 2007 04:23:49 -0800 (PST) Received: by 10.70.130.7 with HTTP; Thu, 8 Mar 2007 04:23:49 -0800 (PST) Message-ID: <3cf983d0703080423x7d2e57d4w52d883f6812d3031@mail.gmail.com> Date: Thu, 8 Mar 2007 09:23:49 -0300 From: "Rodrigo Madera" To: "Alex Deucher" Subject: Re: strange performance regression between 7.4 and 8.1 Cc: Ron , "PostgreSQL Performance" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_424_816158.1173356629528" References: <2D7248A6-38DB-4FE2-A570-6476D5985E85@event-s.net> <26FE4DE3-7896-4A8F-B26A-4FCDA1890078@event-s.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/164 X-Sequence-Number: 23569 ------=_Part_424_816158.1173356629528 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I would just like to note here that this is an example of inefficient strategy. We could all agree (up to a certain economical point) that Alex saved the most expensive one thousand dollars of his life. I don't know the financial status nor the size of your organization, but I'm sure that you have selected the path that has cost you more. In the future, an investment on memory for a (let's say) rather small database should be your first attempt. Yours, Rodrigo Madera On 3/6/07, Alex Deucher wrote: > > On 3/6/07, Ron wrote: > > At 10:25 AM 3/6/2007, Alex Deucher wrote: > > >On 3/5/07, Guido Neitzer wrote: > > >>On 05.03.2007, at 19:56, Alex Deucher wrote: > > >> > > >> > Yes, I started setting that up this afternoon. I'm going to test > that > > >> > tomorrow and post the results. > > >> > > >>Good - that may or may not give some insight in the actual > > >>bottleneck. You never know but it seems to be one of the easiest to > > >>find out ... > > > > > >Well, the SAN appears to be the limiting factor. I set up the DB on > > >the local scsi discs (software RAID 1) and performance is excellent > > >(better than the old server). Thanks for everyone's help. > > > > > >Alex > > > > What kind of SAN is it and how many + what kind of HDs are in it? > > Assuming the answers are reasonable... > > > > It's a Hitachi WMS/Tagmastore. 105 hitachi SATA drives; 4 Gbps FC. > > > Profile the table IO pattern your workload generates and start > > allocating RAID sets to tables or groups of tables based on IO pattern. > > > > For any table or group of tables that has a significant level of > > write IO, say >= ~25% of the IO mix, try RAID 5 or 6 first, but be > > prepared to go RAID 10 if performance is not acceptable. > > > > Right now it's designed for max capacity: big RAID 5 groups. I expect > I'll probably need RAID 10 for decent performance. > > > Don't believe any of the standard "lore" regarding what tables to put > > where or what tables to give dedicated spindles to. > > Profile, benchmark, and only then start allocating dedicated resources. > > For instance, I've seen situations where putting pg_xlog on its own > > spindles was !not! the right thing to do. > > > > Right. Thanks for the advice. I'll post my results when I get around > to testing some new SAN configurations. > > Alex > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ------=_Part_424_816158.1173356629528 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I would just like to note here that this is an example of inefficient strategy.

We could all agree (up to a certain economical point) that Alex saved the most expensive one thousand dollars of his life.

I don't know the financial status nor the size of your organization, but I'm sure that you have selected the path that has cost you more.

In the future, an investment on memory for a (let's say) rather small database should be your first attempt.

Yours,
Rodrigo Madera

On 3/6/07, Alex Deucher <alexdeucher@gmail.com> wrote:
On 3/6/07, Ron <rjpeace@earthlink.net> wrote:
> At 10:25 AM 3/6/2007, Alex Deucher wrote:
> >On 3/5/07, Guido Neitzer < lists@event-s.net> wrote:
> >>On 05.03.2007, at 19:56, Alex Deucher wrote:
> >>
> >> > Yes, I started setting that up this afternoon.  I'm going to test that
> >> > tomorrow and post the results.
> >>
> >>Good - that may or may not give some insight in the actual
> >>bottleneck. You never know but it seems to be one of the easiest to
> >>find out ...
> >
> >Well, the SAN appears to be the limiting factor.  I set up the DB on
> >the local scsi discs (software RAID 1) and performance is excellent
> >(better than the old server).  Thanks for everyone's help.
> >
> >Alex
>
> What kind of SAN is it and how many + what kind of HDs are in it?
> Assuming the answers are reasonable...
>

It's a Hitachi WMS/Tagmastore.  105 hitachi SATA drives; 4 Gbps FC.

> Profile the table IO pattern your workload generates and start
> allocating RAID sets to tables or groups of tables based on IO pattern.
>
> For any table or group of tables that has a significant level of
> write IO, say >= ~25% of the IO mix, try RAID 5 or 6 first, but be
> prepared to go RAID 10 if performance is not acceptable.
>

Right now it's designed for max capacity: big RAID 5 groups.  I expect
I'll probably need RAID 10 for decent performance.

> Don't believe any of the standard "lore" regarding what tables to put
> where or what tables to give dedicated spindles to.
> Profile, benchmark, and only then start allocating dedicated resources.
> For instance, I've seen situations where putting pg_xlog on its own
> spindles was !not! the right thing to do.
>

Right.  Thanks for the advice.  I'll post my results when I get around
to testing some new SAN configurations.

Alex

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

------=_Part_424_816158.1173356629528--