Date: Thu, 14 Jan 2010 10:07:35 -0500
From: Aidan Van Dyk <aidan@highrise.ca>
To: "fkater@googlemail.com" <fkater@googlemail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Message-ID: <20100114150735.GT18076@oak.highrise.ca>
References: <20100114142903.GB2973@comppasch2>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="FoibaoN3dya3u5fy"
Content-Disposition: inline
In-Reply-To: <20100114142903.GB2973@comppasch2>
User-Agent: Mutt/1.5.18 (2008-05-17)


--FoibaoN3dya3u5fy
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

* fkater@googlemail.com <fkater@googlemail.com> [100114 09:29]:
 
> This takes about 50s, so, 800MB/50s = 16MB/s.
> 
> However the harddisk (sata) could write 43 MB/s in the worst
> case! Why is write performance limited to 16 MB/s?
 
> I altered the binary column to STORAGE EXTERNAL.
> 
> Some experiments with postgresql.conf (fsync off,
> shared_buffers=1000MB, checkpoint_segments=256) did not
> change the 50s- much (somtimes 60s sometimes a little less).
> 
> 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk.
> 
> 
> Do you have any further idea why 16MB/s seems to be the
> limit here?

So, your SATA disk can do 43MB/s of sequential writes, but you're example
is doing:
1) Sequential writes to WAL
2) Random writes to your index
3) Sequential writes to table heap
4) Sequential writes to table' toast heap
5) Any other OS-based FS overhead

Now, writes #2,3 and 4 don't happen completely concurrently with your
WAL, some of them are still in postgres buffers, but easily enough to
interrupt the stream of WAL enough to certainly make it believable that
with everything going on on the disk, you can only write WAL at a
*sustained* 16 MB/s

If you're running a whole system on a single SATA which can stream
43MB/s, remember that for *every* other read/write sent do the disk, you
lose up to 1MB/s (12ms seek time, read/write, and back).  And in that
"every other", you have FS metadata updates, any other file writes the
FS flushes, etc...  20 aditional blocks being that are either read or
written to disk are going to completely chop your 43MB/s rate...

a.

-- 
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

--FoibaoN3dya3u5fy
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iD8DBQFLTzM3uVxNPsxNPScRAmizAJ9p5FD9BAFs1lM7CRxMA0gFXhIFMACfZ8V3
b9xNjCZGUJq3mPtQ4Zs5Qxw=
=ANBN
-----END PGP SIGNATURE-----

--FoibaoN3dya3u5fy--