Re: Inserting 8MB bytea: just 25% of disk perf used? - Mailing list pgsql-performance

From Pierre Frédéric Caillaud
Subject Re: Inserting 8MB bytea: just 25% of disk perf used?
Date
Msg-id op.u6ne9gowcke6l8@soyouz
Whole thread Raw
In response to Re: Inserting 8MB bytea: just 25% of disk perf used?  ("fkater@googlemail.com" <fkater@googlemail.com>)
Responses Re: Inserting 8MB bytea: just 25% of disk perf used?
List pgsql-performance
> I've changed the setting a bit:
>
> (1) Replaced 7.200 disk by a 10.000 one, still sata though.
>
> (2) Inserting rows only 10x times (instead of 100x times)
> but 80mb each, so having the same amount of 800mb in total.
>
> (3) Changed the WAL path to the system disk (by the
> great 'junction' trick mentioned in the other posting), so
> actually splitting the write access to the "system" disk and
> the fast "data" disk.
>
>
>
> And here is the frustrating result:
>
> 1. None of the 4 CPUs was ever more busy than 30% (never
> less idle than 70%),
>
> 2. while both disks kept being far below the average write
> performance: the "data" disk had 18 peaks of approx. 40 mb
> but in total the average thoughput was 16-18 mb/s.
>
>
> BTW:
>
> * Disabling noatime and similar for ntfs did not change
> things much (thanks though!).
>
> * A short cross check copying 800mb random data file from
> "system" to "data" disk showed a performance of constantly
> 75 mb/s.
>
>
> So, I have no idea what remains as the bottleneck.
>
>  Felix

    Try this :

CREATE TABLE test AS SELECT * FROM yourtable;

    This will test write speed, and TOAST compression speed.
    Then try this:

CREATE TABLE test (LIKE yourtable);
COMMIT;
INSERT INTO test SELECT * FROM yourtable;

    This does the same thing but also writes WAL.
    I wonder what results you'll get.

pgsql-performance by date:

Previous
From: Greg Smith
Date:
Subject: Re: a heavy duty operation on an "unused" table kills my server
Next
From: Eduardo Piombino
Date:
Subject: Re: a heavy duty operation on an "unused" table kills my server