Hello.
> Based on these results I think 32 will be a good default for
> checkpoint_flush_after? There's a few cases where 64 showed to be
> beneficial, and some where 32 is better. I've seen 64 perform a bit
> better in some cases here, but the differences were not too big.
Yes, these many runs show that 32 is basically as good or better than 64.
I'll do some runs with 16/48 to have some more data.
> I gather that you didn't play with
> backend_flush_after/bgwriter_flush_after, i.e. you left them at their
> default values? Especially backend_flush_after can have a significant
> positive and negative performance impact.
Indeed, non reported configuration options have their default values.
There were also minor changes in the default options for logging (prefix,
checkpoint, ...), but nothing significant, and always the same for all
runs.
>> [...] Ubuntu 12.04 LTS (precise)
>
> That's with 12.04's standard kernel?
Yes.
>> checkpoint_flush_after = { none, 0, 32, 64 }
>
> Did you re-initdb between the runs?
Yes, all runs are from scratch (initdb, pgbench -i, some warmup...).
> I've seen massively varying performance differences due to autovacuum
> triggered analyzes. It's not completely deterministic when those run,
> and on bigger scale clusters analyze can take ages, while holding a
> snapshot.
Yes, I agree that probably the performance changes on long vs short runs
(andres00c vs andres00b) is due to autovacuum.
--
Fabien.