Re: Archive log compression keeping physical log availablein the crash recovery

As suggested, the result I've posted used standard checkpoint_timeout.

With longer checkpoint timeout, overall WAL size tend to decrease. So
I've also run DBT-2. The measurement shows that the resultant WAL size
is reasonably small, while preserving full page writes to WAL,
maintaining the chance of crash recovery as full_page_writes=on case.

I think this is good score and practical for archivelo log recovery with
online backup.

Here's a result:

Database size: 4.13GB (40WH)
Checkpoint setments: 1000
Checkpoint timeout: 20min
Measurement: 60min (run 30min in advance for stabilization)
Total WAL size: 2.98GB
Gzip'ed WAL size: 1.67GB
After physical log removal (proposed patch): 0.38GB
full_page_writes=off: 0.39GB

Database size: 12.35GB (120WH)
Checkpoint setments: 1000
Checkpoint timeout: 20min
Measurement: 60min (run 30min in advance for stabilization)
Total WAL size: 4.20GB
Gzip'ed WAL size: 2.16GB
After physical log removal (proposed patch): 0.32GB
full_page_writes=off: 0.31GB

Database size: 4.13GB (40WH)
Checkpoint setments: 1000
Checkpoint timeout: 60min
Measurement: 60min (run 30min in advance for stabilization)
> maybe we need to run longer to include checkpoint effect
more accurately.
Total WAL size: 2.14GB
Gzip'ed WAL size: 1.22GB
After physical log removal (proposed patch): 0.39GB
full_page_writes=off: 0.38GB

As expected, after phyiscal log records are removed, the resultant
archive log size seems not affected by checkpoint timeout. Rather, the
size seems to depend mainly on the number of transactions.

Zeugswetter Andreas ADI SD wrote:
>> Our evaluation result is as follows:
>> Database size: 2GB
>> WAL size (after 10hours pgbench run): 48.3GB
>> gzipped size: 8.8GB
>> removal of the physical log: 2.36GB
>> fullpage_writes=off log size: 2.42GB
>
>> I'm planning to do archive log size evalutation with other benchmarks
>> such as DBT-2 as well.
>
> Looks promising :-)
>
> Did you use the standard 5 minute checkpoint_timeout?
> Very nice would be a run with checkpoint_timeout increased
> to 30 min, because that is what you would tune if you are concerned
> about fullpage overhead.
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Koichi Suzuki