From: johnnnnnn <john@phaedrusdeinus.org>
To: pgsql-performance@postgresql.org
Subject: Re: Tuning scenarios (was Changing the default configuration)
Date: Fri, 14 Feb 2003 10:33:14 -0600
Message-ID: <20030214163314.GK11017@performics.com> (raw)
In-Reply-To: <20030214114843.GB1847@filer>
References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local>
<26004.1044980414@sss.pgh.pa.us>
<3E492E06.2030702@postgresql.org>
<200302110918.48614.josh@agliodbs.com>
<26582.1044984365@sss.pgh.pa.us>
<3E493415.9090004@postgresql.org>
<26850.1044985975@sss.pgh.pa.us>
<20030214053124.GQ1833@filer>
<4590.1045202674@sss.pgh.pa.us>
<20030214114843.GB1847@filer>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: john@phaedrusdeinus.org
Subject: Re: Tuning scenarios (was Changing the default configuration)
In-Reply-To: <20030214163314.GK11017@performics.com>
References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local> <26004.1044980414@sss.pgh.pa.us> <3E492E06.2030702@postgresql.org> <200302110918.48614.josh@agliodbs.com> <26582.1044984365@sss.pgh.pa.us> <3E493415.9090004@postgresql.org> <26850.1044985975@sss.pgh.pa.us> <20030214053124.GQ1833@filer> <4590.1045202674@sss.pgh.pa.us> <20030214114843.GB1847@filer> <20030214163314.GK11017@performics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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