DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=date:from:to:subject:message-id:mime-version:content-type
	:content-disposition:user-agent;
	b=ObKI95qehUbBBsU4QhJSHbqYsGzEVmPNqiz+2SYKlsOaAO3ApyHOgCZrqQBWI0ZjSu
	f7c8ibOVY6zHtVXtvSKlyMPQOYt0x08rbOeiQj8NmrZnHaA/wLPaPrMdzQeCswW2ramg
	GEWkqpCWqiBQiRxQEBGLfCFisCBpuYpdnJw8Q=
Date: Thu, 14 Jan 2010 15:29:03 +0100
From: "fkater@googlemail.com" <fkater@googlemail.com>
To: pgsql-performance@postgresql.org
Subject: Inserting 8MB bytea: just 25% of disk perf used?
Message-ID: <20100114142903.GB2973@comppasch2>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

Hello together,

I need to increase the write performance when inserting
bytea of 8MB. I am using 8.2.4 on windows with libpq.

The test setting is simple:

I write 100x times a byte array (bytea) of 8 MB random data
into a table having a binary column (and oids and 3 other
int columns, oids are indexed). I realized that writing 8 MB
of 0-bytes is optimized away. With random data, the disk
space now is filled with 800MB each run as expected. I use a
transaction around the insert command.

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?


Some more hints what I do:

I use PQexecParams() and the INSERT ... $001 notation to NOT
create a real escapted string from the data additionally but
use a pointer to the 8MB data buffer.

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?

Thank You
 Felix