Question about forced immediate checkpoints during create database

[Maxim Boguk <maxim.boguk@xxxxxxxxx>]

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

--

Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.ru/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim.boguk@xxxxxxxxx
МойКруг: http://mboguk.moikrug.ru/

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."