Hi everyone,
I have few question about checkpoints during create database.
First just extract from log on my test database 9.2.4:
2013-11-12 03:48:31 MSK 1717 @ from [vxid: txid:0] [] LOG: checkpoint starting: immediate force wait
2013-11-12 03:48:31 MSK 1717 @ from [vxid: txid:0] [] LOG: checkpoint complete: wrote 168 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.314 s, sync=0.146 s, total=0.462 s; sync files=104, longest=0.040 s, average=0.001 s
2013-11-12 03:48:32 MSK 1717 @ from [vxid: txid:0] [] LOG: checkpoint starting: immediate force wait
2013-11-12 03:48:32 MSK 1717 @ from [vxid: txid:0] [] LOG: checkpoint complete: wrote 6 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.311 s, sync=0.002 s, total=0.315 s; sync files=6, longest=0.000 s, average=0.000 s
2013-11-12 03:48:32 MSK 13609 postgres@hh_data from [local] [vxid:502/0 txid:0] [CREATE DATABASE] LOG: duration: 1160.409 ms statement: create database _tmp;
So during creating of database two immediate force checkpoints was performed.
Now questions:
1)Why these checkpoints performed at all? I understood why checkpoint performed during drop database (to clean shared buffers from the dropped db data), but why issue checkpoint during create database?
2)Why two checkpoints performed one after one?
3)Is there any good way to perform spread checkpoint during create database (similar to --checkpoint=spread for the pg_basebackup) ?
I'm ready to wait 30 min for create database in that case...
I asking because performing immediate checkpoint on the large heavy loaded database - good recipe for downtime (IO become overloaded to point of the total stall)...
Is there any workaround for this problem?
4)Is idea to add an option for create/drop database syntax to control checkpoint behaviour sounds reasonable?
Kind Regards,
Maksym