Re: Very slow checkpoints

From: Steven Jones <steven(dot)jones1201(at)outlook(dot)com>
To: didier <did447(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very slow checkpoints
Date: 2015-03-19 05:28:40
Message-ID: COL129-W590158F348C93F28E325FE92010@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

>>
>> Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz
>> avgqu-sz await r_await w_await svctm %util
>> sda 0.00 0.00 0.00 5.00 0.00 2056.00 822.40
>> 0.00 0.00 0.00 0.00 0.00 0.00
>> sdb 0.00 0.00 1055.00 549.00 41166.50 22840.00 79.81
>> 5.28 3.28 4.94 0.10 0.62 100.00
> Your sdb is saturated...

Yes that's what iostat seems to indicate, but it's weird because at the same time it is reporting 100% io utilization I can hit the disk write (seq) at> 250Mbyte/sec:

# sync;time bash -c "(dd if=/dev/sda1 of=bf bs=8k count=500000; sync)"
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 14.8575 s, 276 MB/s

real 0m14.896s
user 0m0.068s
sys 0m10.157s

> Why are checkpointer process and writer process reading at 18 MB/s ?
> I have no experience with zfs but could it be related to COW and
> recordsize? I have no idea if these reads are counted in iotop output
> though.

In general, some random disk write benchmarks and varying block sizes don't have a huge effect.  But for some reason the checkpointing process is just simply writing checkpoints too slowly. In the meantime the COPY is piling up logs while the previous checkpoint is still being written, so the next one starts straight away and no setting is able to split them up.

Regards,
Steve

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jake Magner 2015-03-19 06:23:34 Merge Join chooses very slow index scan
Previous Message Tom Lane 2015-03-18 22:41:30 Re: Fwd: views much slower in 9.3 than 8.4