Thread: Performance Tuning Article

Performance Tuning Article

From
Frank Wiles
Date:
  Hi Everyone,

  I've put together a short article and posted it online regarding
  performance tuning PostgreSQL in general.  I believe it helps to bring
  together the info in a easy to digest manner. I would appreciate any
  feedback, comments, and especially any technical corrections.

  The article can be found here:

  http://www.revsys.com/writings/postgresql-performance.html

  Thanks!

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: Performance Tuning Article

From
Josh Berkus
Date:
Frank,

>   I've put together a short article and posted it online regarding
>   performance tuning PostgreSQL in general.  I believe it helps to bring
>   together the info in a easy to digest manner. I would appreciate any
>   feedback, comments, and especially any technical corrections.

Looks nice. You should mark the link to the perf tips at Varlena.com as
"PostgreSQL 7.4" and augment it with the current version here:
www.powerpostgresql.com/PerfList
as well as the Annotated .Conf File:
www.powerpostgresql.com/Docs

For my part, I've generally seen that SATA disks still suck for read-write
applications.   I generally rate 1 UltraSCSI = 2 SATA disks for anything but
a 99% read application.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Performance Tuning Article

From
Frank Wiles
Date:
On Wed, 22 Jun 2005 10:16:03 -0700
Josh Berkus <josh@agliodbs.com> wrote:

> Frank,
>
> >   I've put together a short article and posted it online regarding
> >   performance tuning PostgreSQL in general.  I believe it helps to
> >   bring together the info in a easy to digest manner. I would
> >   appreciate any feedback, comments, and especially any technical
> >   corrections.
>
> Looks nice. You should mark the link to the perf tips at Varlena.com
> as  "PostgreSQL 7.4" and augment it with the current version here:
> www.powerpostgresql.com/PerfList
> as well as the Annotated .Conf File:
> www.powerpostgresql.com/Docs

  Thanks! These changes have been incorporated.

> For my part, I've generally seen that SATA disks still suck for
> read-write  applications.   I generally rate 1 UltraSCSI = 2 SATA
> disks for anything but  a 99% read application.

  I'll work this bit of wisdom in later tonight. Thanks again for the
  feedback.

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: Performance Tuning Article

From
Karim Nassar
Date:
On Wed, 2005-06-22 at 09:52 -0500, Frank Wiles wrote:
>  I've put together a short article and posted it online regarding
>   performance tuning PostgreSQL in general.

Nice work! Some minor issues I saw:

* section "Understanding the process", para 5:

"Now that PostgreSQL has a plan of what it believes to be the best way
to retrieve the hardware it is time to actually get it."

Do you mean "retrieve the data" instead of "retrieve the hardware"?


* Perhaps some examples under "Disk Configuration"?


* section "Database Design and Layout", after new table layout:

"Take for example the employee table above. Your probably only display
active employees throughout the majority of the application..."

Do you mean "You're probably only displaying"?


HTH,
--
Karim Nassar <karim.nassar@acm.org>


Re: Performance Tuning Article

From
Tobias Brox
Date:
[Frank Wiles - Wed at 09:52:27AM -0500]
>   I've put together a short article and posted it online regarding
>   performance tuning PostgreSQL in general.  I believe it helps to bring
>   together the info in a easy to digest manner. I would appreciate any
>   feedback, comments, and especially any technical corrections.

I did not read through the whole article, but I already have some comments;

work_mem was formerly sort_mem.  As many of us still use pg7, you should
probably have a note about it.

There are already quite some short articles at the web about this issue, and
that was actually my starting point when I was assigned the task of tweaking
the database performance.  I think diversity is a good thing, some of the
short articles was relatively outdated, others were not very well written.
And also - I still never had the chance to do proper benchmarking of the
impact of my changes in the configuration file, I just chose to trust some
of the advices when I saw almost the same advice repeated in several
articles.

I think we need some comprehensive chapter about this in the manual, with
plenty of pointers - or eventually some separate well-organized pages
telling about all known issues.  It seems to me that many of the standard
tips here are repeating themselves over and over again.

--
Tobias Brox, +86-13521622905
Nordicbet, IT dept

Re: Performance Tuning Article

From
Keith Worthington
Date:

>>>  I've put together a short article and posted it online regarding
>>>  performance tuning PostgreSQL in general.  I believe it helps to
>>>  bring together the info in a easy to digest manner. I would
>>>  appreciate any feedback, comments, and especially any technical
>>>  corrections.
>>
>>Looks nice. You should mark the link to the perf tips at Varlena.com
>>as  "PostgreSQL 7.4" and augment it with the current version here:
>>www.powerpostgresql.com/PerfList
>>as well as the Annotated .Conf File:
>>www.powerpostgresql.com/Docs
>
>
>   Thanks! These changes have been incorporated.
>
>
>>For my part, I've generally seen that SATA disks still suck for
>>read-write  applications.   I generally rate 1 UltraSCSI = 2 SATA
>>disks for anything but  a 99% read application.
>
>
>   I'll work this bit of wisdom in later tonight. Thanks again for the
>   feedback.
>
>  ---------------------------------
>    Frank Wiles <frank@wiles.org>
>    http://www.wiles.org
>  ---------------------------------

Frank,

A couple of things I wish I had been told when I started asking how to
configure a new machine.

Use RAID 10 (striping across mirrored disks)
  or RAID 0+1 (mirror a striped array) for your data.
Use RAID 1 (mirror) for your OS
Use RAID 1 (mirror) for the WAL.

Don't put anything else on the array holding the WAL.

There have been problems with Xeon processors.

--
Kind Regards,
Keith

Re: Performance Tuning Article

From
Radu-Adrian Popescu
Date:
>
> There have been problems with Xeon processors.
>

Can you elaborate on that please ?

Thanks,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243

Attachment

Re: Performance Tuning Article

From
Keith Worthington
Date:
Radu-Adrian Popescu wrote:
>>
>> There have been problems with Xeon processors.
>>
>
> Can you elaborate on that please ?
>
> Thanks,

Not really as I do not understand the issue.

Here is one post from the archives.
http://archives.postgresql.org/pgsql-performance/2005-05/msg00441.php

If you search the archives for xeon sooner or later you will bump into
something relevant.

--
Kind Regards,
Keith

Re: Performance Tuning Article

From
Dave Cramer
Date:
My understanding is that it isn't particularly XEON processors that
is the problem

Any dual processor will exhibit the problem, XEON's with
hyperthreading exacerbate the problem though

and the good news is that it has been fixed in 8.1

Dave
On 23-Jun-05, at 8:16 AM, Keith Worthington wrote:

> Radu-Adrian Popescu wrote:
>
>>>
>>> There have been problems with Xeon processors.
>>>
>>>
>> Can you elaborate on that please ?
>> Thanks,
>>
>
> Not really as I do not understand the issue.
>
> Here is one post from the archives.
> http://archives.postgresql.org/pgsql-performance/2005-05/msg00441.php
>
> If you search the archives for xeon sooner or later you will bump
> into something relevant.
>
> --
> Kind Regards,
> Keith
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>



Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )


Re: Performance Tuning Article

From
Radu-Adrian Popescu
Date:
Dave Cramer wrote:
> My understanding is that it isn't particularly XEON processors that  is
> the problem
>
> Any dual processor will exhibit the problem, XEON's with  hyperthreading
> exacerbate the problem though
>
> and the good news is that it has been fixed in 8.1
>

Where's that ? The only information I have is a message from Tom Lane saying the
buffer manager (or something like that) locking has been redone for 8.0. Any
pointers ?

> Dave

Thanks,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243

Attachment

Re: Performance Tuning Article

From
Frank Wiles
Date:
On Wed, 22 Jun 2005 22:31:29 -0400
Keith Worthington <KeithW@NarrowPathInc.com> wrote:

> Frank,
>
> A couple of things I wish I had been told when I started asking how to
>
> configure a new machine.
>
> Use RAID 10 (striping across mirrored disks)
>   or RAID 0+1 (mirror a striped array) for your data.
> Use RAID 1 (mirror) for your OS
> Use RAID 1 (mirror) for the WAL.
>
> Don't put anything else on the array holding the WAL.
>
> There have been problems with Xeon processors.

  I believe all of these issues are covered in the article, but
  obviously not clearly enough.  I'll work on rewording that section.

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


Re: Performance Tuning Article

From
Michael Stone
Date:
On Wed, Jun 22, 2005 at 10:31:29PM -0400, Keith Worthington wrote:
>Use RAID 10 (striping across mirrored disks)
>  or RAID 0+1 (mirror a striped array) for your data.

yikes! never tell an unsuspecting person to use mirred stripes--that
configuration has lower reliability and performance than striped mirrors
with no redeeming qualities.

Mike Stone

Re: Performance Tuning Article

From
Dave Cramer
Date:
AFAIK, the problem was the buffer manager

Dave
On 23-Jun-05, at 9:46 AM, Radu-Adrian Popescu wrote:

> Dave Cramer wrote:
>
>> My understanding is that it isn't particularly XEON processors
>> that  is the problem
>> Any dual processor will exhibit the problem, XEON's with
>> hyperthreading exacerbate the problem though
>> and the good news is that it has been fixed in 8.1
>>
>
> Where's that ? The only information I have is a message from Tom
> Lane saying the buffer manager (or something like that) locking has
> been redone for 8.0. Any pointers ?
>
>
>> Dave
>>
>
> Thanks,
> --
> Radu-Adrian Popescu
> CSA, DBA, Developer
> Aldrapay MD
> Aldratech Ltd.
> +40213212243
>


Re: Performance Tuning Article

From
Tom Lane
Date:
Dave Cramer <pg@fastcrypt.com> writes:
> AFAIK, the problem was the buffer manager

The buffer manager was the place that seemed to be hit hardest by Xeon's
problems with spinlock contention.  I think we've partially fixed that
issue in 8.1, but as we continue to improve the system's performance,
it's likely to surface as a bottleneck again in other places.

            regards, tom lane

Re: Performance Tuning Article

From
"Dmitri Bichko"
Date:
Hi,

The article seems to dismiss RAID5 a little too quickly.  For many
application types, using fast striped mirrors for the index space and
RAID5 for the data can offer quite good performance (provided a
sufficient number of spindles for the RAID5 - 5 or 6 disks or more).  In
fact, random read (ie most webapps) performance of RAID5 isn't
necessarily worse than that of RAID10, and can in fact be better in some
circumstances.  And, using the cheaper RAID5 might allow you to do that
separation between index and data in the first place.

Just thought I'd mention it,
Dmitri

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Frank Wiles
Sent: Wednesday, June 22, 2005 10:52 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Performance Tuning Article



  Hi Everyone,

  I've put together a short article and posted it online regarding
  performance tuning PostgreSQL in general.  I believe it helps to bring
  together the info in a easy to digest manner. I would appreciate any
  feedback, comments, and especially any technical corrections.

  The article can be found here:

  http://www.revsys.com/writings/postgresql-performance.html

  Thanks!

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://www.wiles.org
 ---------------------------------


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you
received this in error, please contact the sender and delete the
material from any computer