Re: New to PostgreSQL, performance considerations - Mailing list pgsql-performance

From Ron
Subject Re: New to PostgreSQL, performance considerations
Date
Msg-id E1Gut3B-0005lH-WA@elasmtp-galgo.atl.sa.earthlink.net
Whole thread Raw
In response to Re: New to PostgreSQL, performance considerations  (Greg Smith <gsmith@gregsmith.com>)
List pgsql-performance
At 10:00 AM 12/14/2006, Greg Smith wrote:
>On Wed, 13 Dec 2006, Ron wrote:
>
>>The slowest results, Michael's, are on the system with what appears
>>to be the slowest CPU of the bunch; and the ranking of the rest of
>>the results seem to similarly depend on relative CPU
>>performance.  This is not what one would naively expect when
>>benching a IO intensive app like a DBMS.
>
>pgbench with 3000 total transactions and fsync off is barely doing
>I/O to disk; it's writing a bunch of data to the filesystem cache
>and ending the benchmark before the data even makes it to the hard
>drive.  This is why his results become completely different as soon
>as the number of transactions increases. With little or no actual
>disk writes, you should expect results to be ranked by CPU speed.
I of course agree with you in the general sense.  OTOH, I'm fairly
sure the exact point where this cross-over occurs is dependent on the
components and configuration of the system involved.

(Nor do I want to dismiss this scenario as irrelevant or
unimportant.  There are plenty of RW situations where this takes
place or where the primary goal of a tuning effort is to make it take
place.  Multi-GB BB RAID caches anyone?)

In addition, let's keep in mind that we all know that overall system
performance is limited by whatever component hits its limits
first.  Local pg performance has been known to be limited by any of :
CPUs, memory subsystems, or physical IO subsystems.  Intuitively, one
tends to expect only the later to be a limiting factor in the vast
majority of DBMS tasks.  pg has a history of regularly surprising
such intuition in many cases.
IMO, this makes good bench marking tools and procedures more
important  to have.

(If nothing else, knowing what component is likely to be the
bottleneck in system "X" made of components "x1, x2, x3, ...." for
task "Y" is valuable lore for the pg community to have as preexisting
data when first asked any given question on this list! )

One plausible positive effect of tuning like that Daniel advocates is
to "move" the level of system activity where the physical IO
subsystem becomes the limiting factor on overall system performance.

We are not going to know definitively if such an effect exists, or to
what degree, or how to achieve it, if we don't have appropriately
rigorous and reproducible experiments (and documentation of them) in
place to test for it.

  So it seem to make sense that the community should have a
discussion about the proper bench marking of pg and to get some
results based on said.

Ron Peacetree


pgsql-performance by date:

Previous
From: Michael Stone
Date:
Subject: Re: New to PostgreSQL, performance considerations
Next
From: Tom Lane
Date:
Subject: Re: EXPLAIN ANALYZE on 8.2