Thread: 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 ---------------------------------
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
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 ---------------------------------
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>
[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
>>> 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
> > 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
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
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 )
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
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 ---------------------------------
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
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 >
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
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