From: Pierre Frédéric Caillaud <lists@peufeu.com>
To: fkater@googlemail.com <fkater@googlemail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: Thu, 14 Jan 2010 17:08:59 +0100
Message-ID: <op.u6i5s9eicke6l8@soyouz> (raw)
In-Reply-To: <20100114142903.GB2973@comppasch2>
References: <20100114142903.GB2973@comppasch2>
> 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.
Big CPU and slow disk...
You should add another disk just for the WAL -- disks are pretty cheap
these days.
Writing the WAL on a second disk is the first thing to do on a
configuration like yours, if you are limited by writes.
It also reduces the fsync lag a lot since the disk is only doing WAL.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, fkater@googlemail.com
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
In-Reply-To: <op.u6i5s9eicke6l8@soyouz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox