pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Tuning Article
15+ messages / 10 participants
[nested] [flat]
* Performance Tuning Article
@ 2005-06-22 14:52 Frank Wiles <frank@wiles.org>
0 siblings, 3 replies; 15+ messages in thread
From: Frank Wiles @ 2005-06-22 14:52 UTC (permalink / raw)
To: pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-22 17:16 Josh Berkus <josh@agliodbs.com>
parent: Frank Wiles <frank@wiles.org>
2 siblings, 1 reply; 15+ messages in thread
From: Josh Berkus @ 2005-06-22 17:16 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-22 17:42 Frank Wiles <frank@wiles.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 15+ messages in thread
From: Frank Wiles @ 2005-06-22 17:42 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-22 18:05 Karim Nassar <karim.nassar@acm.org>
parent: Frank Wiles <frank@wiles.org>
2 siblings, 0 replies; 15+ messages in thread
From: Karim Nassar @ 2005-06-22 18:05 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 01:06 Tobias Brox <tobias@nordicbet.com>
parent: Frank Wiles <frank@wiles.org>
2 siblings, 0 replies; 15+ messages in thread
From: Tobias Brox @ 2005-06-23 01:06 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
[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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 02:31 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Frank Wiles <frank@wiles.org>
0 siblings, 3 replies; 15+ messages in thread
From: Keith Worthington @ 2005-06-23 02:31 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; +Cc: pgsql-performance
>>> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 09:22 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
2 siblings, 1 reply; 15+ messages in thread
From: Radu-Adrian Popescu @ 2005-06-23 09:22 UTC (permalink / raw)
To: pgsql-performance
>
> 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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.1K) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 12:16 Keith Worthington <KeithW@NarrowPathInc.com>
parent: Radu-Adrian Popescu <radu.popescu@aldratech.com>
0 siblings, 1 reply; 15+ messages in thread
From: Keith Worthington @ 2005-06-23 12:16 UTC (permalink / raw)
To: radu.popescu@aldratech.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 13:36 Dave Cramer <pg@fastcrypt.com>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 15+ messages in thread
From: Dave Cramer @ 2005-06-23 13:36 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: radu.popescu@aldratech.com; pgsql-performance
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 )
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 13:46 Radu-Adrian Popescu <radu.popescu@aldratech.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 15+ messages in thread
From: Radu-Adrian Popescu @ 2005-06-23 13:46 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.1K) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 13:48 Frank Wiles <frank@wiles.org>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
2 siblings, 0 replies; 15+ messages in thread
From: Frank Wiles @ 2005-06-23 13:48 UTC (permalink / raw)
To: KeithW@NarrowPathInc.com; +Cc: pgsql-performance
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
---------------------------------
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 14:44 Michael Stone <mstone+postgres@mathom.us>
parent: Keith Worthington <KeithW@NarrowPathInc.com>
2 siblings, 0 replies; 15+ messages in thread
From: Michael Stone @ 2005-06-23 14:44 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 16:16 Dave Cramer <pg@fastcrypt.com>
parent: Radu-Adrian Popescu <radu.popescu@aldratech.com>
0 siblings, 1 reply; 15+ messages in thread
From: Dave Cramer @ 2005-06-23 16:16 UTC (permalink / raw)
To: radu.popescu@aldratech.com; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-23 17:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2005-06-23 17:15 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: radu.popescu@aldratech.com; pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Performance Tuning Article
@ 2005-06-24 22:02 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 15+ messages in thread
From: Dmitri Bichko @ 2005-06-24 22:02 UTC (permalink / raw)
To: Frank Wiles <frank@wiles.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-06-24 22:02 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-22 14:52 Performance Tuning Article Frank Wiles <frank@wiles.org>
2005-06-22 17:16 ` Josh Berkus <josh@agliodbs.com>
2005-06-22 17:42 ` Frank Wiles <frank@wiles.org>
2005-06-23 02:31 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-06-23 09:22 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2005-06-23 12:16 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-06-23 13:36 ` Dave Cramer <pg@fastcrypt.com>
2005-06-23 13:46 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2005-06-23 16:16 ` Dave Cramer <pg@fastcrypt.com>
2005-06-23 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-23 13:48 ` Frank Wiles <frank@wiles.org>
2005-06-23 14:44 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-22 18:05 ` Karim Nassar <karim.nassar@acm.org>
2005-06-23 01:06 ` Tobias Brox <tobias@nordicbet.com>
2005-06-24 22:02 Re: Performance Tuning Article Dmitri Bichko <dbichko@aveopharma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox