What exactly is postgres doing during INSERT/UPDATE ?

From: Joseph S <jks(at)selectacast(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: What exactly is postgres doing during INSERT/UPDATE ?
Date: 2009-08-28 06:56:23
Message-ID: h77v2l$bkf$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

If I run " dd if=/dev/zero bs=1024k of=file count=1000 " iostat shows me:

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 671.50 88.00 113496.00 176 226992

However postgres 8.3.7 doing a bulk data write (a slony slave, doing
inserts and updates) doesn't go nearly as fast:

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 418.41 648.76 7052.74 1304 14176

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 237.50 44.00 3668.00 88 7336

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 221.50 444.00 3832.00 888 7664

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 166.00 248.00 3360.00 496 6720

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 163.00 480.00 3184.00 960 6368

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 102.50 724.00 1736.00 1448 3472

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 295.50 712.00 6004.00 1424 12008

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 109.45 433.83 2260.70 872 4544

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 180.00 640.00 3512.00 1280 7024

top shows the cpu usage of the pg process ranges from zero to never more
than ten percent of a cpu, and that one cpu is always ninety some odd
percent in iowait. So what is postgres doing (with fsync off) that
causes the cpu to spend so much time in iowait?

This is a 64 bit amd linux system with ext3 filesystem. free shows:

total used free shared buffers cached
Mem: 8116992 8085848 31144 0 103016 3098568
-/+ buffers/cache: 4884264 3232728
Swap: 6697296 2035508 4661788

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pierre Frédéric Caillaud 2009-08-28 07:52:33 Re: What exactly is postgres doing during INSERT/UPDATE ?
Previous Message bricklen 2009-08-28 00:19:42 Re: Vacuum duration + hint bits?