SEO

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/postg...rformance.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

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

---------------------------(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

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
---------------------------------


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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>


---------------------------(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

[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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>>> 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

---------------------------(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

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...5/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

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...5/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 )


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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
---------------------------------


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org