Dne 3.4.2011 02:45, Tom Lane napsal(a):
> Tomas Vondra <tv@fuzzy.cz> writes:
>> I've been playing with various wal_level settings, mainly to find out
>> what is the overhead of hot standby, and I've noticed a strange thing.
>> In some cases the wal_level=minimal produces signigicantly more xlog
>> data than wal_level=archive (and hot_standby).
>> ...
>> Which means the pgbench init takes about 142kB with 'minimal' level and
>> about 163MB with 'archive' level (which is expected).
>
>> But the actual pgbench run produces much more xlog data with minimal wal
>> level compared to archive level. With minimal level it produces about
>> 90MB and with archive level it produces just about 5MB.
>
>> I've check the docs and the only possible explanation I've noticed when
>> reading the docs is that while wal_level=minimal allows to skip logging
>> of some bulk operations (e.g. COPY, which is exactly what pgbench does
>> when initializing the DB), it may need to log more data later (when
>> actually running the bench).
>
> I wonder whether the discrepancy relates to having to produce
> full-page-image WAL entries during the first touch of a page during the
> pgbench run, versus having already done so in initialization. If you
> force a checkpoint after the init step, do the results change?
Yes, a forced CHECKPOINT results in a much more xlog data in case of the
archive level. Without the checkpoint there was about 5MB, now there is
about about 90MB (a fet kB more than with the minimal wal level).
Hmmm, I'm wondering which of these two cases is more apropriate when
comparing wal levels - with the checkpoint or without it? I believe the
one with checkpoint, as checkpoints happen all the time anyway.
regards
Tomas