Skip site navigation (1) Skip section navigation (2)

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>, "Matthew Wakeling" <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-16 23:23:30
Message-ID: op.u6ne9gowcke6l8@soyouz (view raw or flat)
Thread:
Lists: 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.

In response to

Responses

pgsql-performance by date

Next:From: Eduardo PiombinoDate: 2010-01-17 21:21:30
Subject: Re: a heavy duty operation on an "unused" table kills my server
Previous:From: Greg SmithDate: 2010-01-16 17:47:10
Subject: Re: a heavy duty operation on an "unused" table kills my server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group