Re: Tuning scenarios (was Changing the default configuration)

On Fri, Feb 14, 2003 at 03:48:43AM -0800, Kevin Brown wrote:
> That unfortunately doesn't help us a whole lot in figuring out
> defaults that will perform reasonably well under broad conditions,
> unless there's some way to determine a reasonably consistent pattern
> (or set of patterns) amongst a lot of those applications.

When moving to a new DB or DB box, we always run a series of
benchmarks to make sure there aren't any surprises
performance-wise. Our database activity, and thus our benchmarks, are
broken up into roughly three different patterns:

1- Transaction processing: small number of arbitrary small
(single-row) selects intermixed with large numbers of small inserts
and updates.

2- Reporting: large reads joining 6-12 tables, usually involving
calculations and/or aggregation.

3- Application (object retrieval): large numbers of arbitrary,
single-row selects and updates, with smaller numbers of single row
inserts.

We use our own application code to do our benchmarks, so they're not
general enough for your use, but it might be worthwhile to profile
each of those different patterns, or allow DB admins to limit it to a
relevant subset. Other patterns i can think of include logging (large
number of single row inserts, no updates, occasional large, simple
(1-3 table) selects), mining (complicated selects over 10 or more
tables), automated (small inserts/updates, with triggers cascading
everywhere), etc.

The problem becomes dealing with the large amounts of data necessary
to frame all of these patterns. An additional wrinkle is accomodating
both columns with well-distributed data and columns that are top-heavy
or which only have one of a small number of values. Plus indexed vs
unindexed columns.

Or, somewhat orthogonally, you could allow pgbench to take a workload
of different sql statements (with frequencies), and execute those
statements instead of the built-in transaction. Then it would be easy
enough to contribute a library of pattern workloads, or for the DBA to
write one herself.

Just my two cents.

-johnnnnnnnnnn