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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate