Re: Tuning scenarios (was Changing the default configuration)

Kevin,

> I think we might be able to come up with some reasonable answers to
> that, as long as we don't expect too much out of the resulting
> benchmark.  The right people to ask are probably the people who are
> actually running production databases.
>
> Anyone wanna chime in here with some opinions and perspectives?

<grin> I thought you'd *never* ask.

(for background: I'm a consultant, and I administrate 6 postgresql databases
for 5 different clients)

First off, one can't do a useful performance test on the sort of random data
which can be generated by  a script.  The only really useful tests come from
testing on a copy of the user's own database(s), or on a real database of
some sort.

For new installations, we'd need to make a copy of a public domain or OSS
database as a part of our performance testing tool.  This database would need
at least 10 tables, some of them quite large, with FK relationships.

Second, there are five kinds of query tests relevant to performmance:

A) Rapid-fire simple select queries.
B) Large complex select queries, combining at least 2 of: aggregates,
sub-selects, unions, unindexed text searches, and outer joins.
C) Rapid-fire small (<10 rows) update/insert/delete queries.
D) Large update queries (> 10,000 rows, possibly in more than one table)
E) Long-running PL procedures.

Testing on these five types of operations give an all-around test of server
performance.   Fortunately, for many installations, not all tests are
relevant; in fact, for many, only 2 of the 5 above are relevant.  For
example, for a PHP-Nuke installation, you'd only need to test on A and C.  As
another example, an OLAP reporting server would only need to test on  B.

Unfortunately, for any real production server, you need to test all the
different operations concurrently at the appropriate multi-user level.
Meaning that for one of my servers (a company-wide calendaring tool) I'd need
to run tests on A, B, C, and E all simultaneously ... for that matter, A and
C by themselves would require multiple connections.

So, once again, if we're talking about a testing database, we would need
twenty examples of A and C, ten of each of B and D, and at least 3 of E that
we could run.  For real production databases, the user could supply "pools"
of the 5 types of operations from their real query base.

Thirdly, we're up against the problem that there are several factors which can
have a much more profound effect on database performance than *any* amount of
tuning postgresql.conf, even given a particular hardware platform.   In my
experience, these factors include (in no particular order):
    1) Location of the pg_xlog for heavy-update databases.
    2) Location of other files on multi-disk systems
    3) Choice of RAID and controller for RAID systems.
    4) Filesystem choice and parameters
    5) VACUUM/FULL/ANALYZE/REINDEX frequency and strategy
    6) Database schema design
    7) Indexing
Thus the user would have to be somehow informed that they need to examine all
of the above, possibly before running the tuning utility.

Therefore, any tuning utility would have to:
1) Inform the user about the other factors affecting performance and notify
them that they have to deal with these.

2) Ask the user for all of the following data:
    a) How much RAM does your system have?
    b) How many concurrent users, typically?
    c) How often do you run VACUUM/FULL/ANALYZE?
    d) Which of the Five Basic Operations does your database perform frequently?
         (this question could be reduced to "what kind of database do you have?"
        web site database = A and C
        reporting database = A and B
        transaction processing = A, C, D and possibly E   etc.)
    e) For each of the 5 operations, how many times per minute is it run?
    f) Do you care about crash recovery?  (if not, we can turn off fsync)
    g) (for users testing on their own database) Please make a copy of your
database, and provide 5 pools of operation examples.

3) The the script would need to draw random operations from the pool, with
operation type randomly drawn weighted by relative frequency for that type of
operation.    Each operation would be timed and scores kept per type of
operation.

4) Based on the above scores, the tuning tool could adjust the following
parameters:
    For A) shared_buffers
    For B) shared_buffers and sort_mem (and Tom's new JOIN COLLAPSE settings)
    For C) and D) wal settings and FSM settings
    For E) shared_buffers, wal, and FSM

5) Then run 3) again.

The problem is that the above process becomes insurmountably complex when we
are testing for several types of operations simultaneously.  For example, if
operation D is slow, we might dramatically increase FSM, but that could take
away memory needed for op. B, making op. B run slower.   So if we're running
concurrently, we could could find the adjustments made for each type of
operation contradictory, and the script would be more likely to end up in an
endless loop than at a balance.   If we don't run the different types of
operations simultaneously, then it's not a good test; the optimal settings
for op. B, for example, may make ops. A and C slow down and vice-versa.

So we'd actually need to run an optimization for each type of desired
operation seperately, and then compare settings, adjust to a balance
(weighted according to the expected relative frequency), and re-test
concurrently.   Aieee!

Personally, I think this is a project in and of itself.  GBorg, anyone?

--
Josh Berkus
Aglio Database Solutions
San Francisco