Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> This is what I've been able to find on a quick look:
>
> http://archives.postgresql.org/pgsql-hackers/2009-05/msg00678.php
>
> Sounds like Kevin may want to try renaming some of his indices to
> produce intermingling...
Thanks, I'll give that a try. Renaming them is one thing, getting a
new dump is another, though. I probably won't be able to test that
theory until tomorrow night.
Last night's test yielded a couple interesting results.
For one thing, while the "optimized" postgresql.conf was 2.5% faster
than the default file for a single job in one database transaction, it
was 10% *slower* than the default for multi-job restores. I'll check
on that more later, to see what might be helping and what is hurting.
For another thing, with the default settings, the patched version ran
an additional 1% faster than the unpatched; although I don't have
enough samples to have a high degree of confidence it wasn't noise.
I'll run another slew of tests tonight with the existing dump file to
confirm to debunk that result, while I create a new dump file to test
with name intermingling on later nights.
For the record, the "default" postgresql.conf:
port = 5678
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
standard_conforming_strings = on
The "optimized" file adds these:
max_connections = 100
shared_buffers = 256MB
work_mem = 50MB
maintenance_work_mem = 500MB
bgwriter_lru_maxpages = 600
bgwriter_lru_multiplier = 10.0
fsync = off
full_page_writes = off
wal_buffers = 4MB
random_page_cost = 2.0
effective_cache_size = 3GB
logging_collector = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
track_counts = off
autovacuum = off
sql_inheritance = off
I'm sure that there is a wealth of opinion on which of these are
slowing things down, but I'm going to withhold any guesses in favor of
testing them. (They all proved themselves neutral or beneficial in
objective testing for the single-job restores under 8.3.)
-Kevin