Re: Inserting 8MB bytea: just 25% of disk perf used?

From: Pierre Frédéric Caillaud <lists(at)peufeu(dot)com>
To: "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-14 16:08:59
Message-ID: op.u6i5s9eicke6l8@soyouz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


> 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.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pierre Frédéric Caillaud 2010-01-14 16:16:11 Re: a heavy duty operation on an "unused" table kills my server
Previous Message Kevin Grittner 2010-01-14 16:03:16 Re: Slow "Select count(*) ..." query on table with 60 Mio. rows