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

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

From: "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com>
To: Pierre Frédéric Caillaud <lists(at)peufeu(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-18 11:20:59
Message-ID: 20100118112059.GB2913@comppasch2 (view raw or flat)
Thread:
Lists: pgsql-performance
Hello Pierre,

thank You for these useful test commands.

Here is what I did:


Pierre Frédéric Caillaud:

> 	Try this :
> 
> CREATE TABLE test AS SELECT * FROM yourtable;
> 
> 	This will test write speed, and TOAST compression speed.
> 	Then try this:

(1)

Setting:

* pg_xlog sym'linked to another disk (to "system disk")
* having approx 11.1 GB in 'yourtable' on "data disk"
* executed SQL by pgAdmin III (as above, no transaction)

Speed:

* 754 s (14.5 MB/s)


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

(2)

Setting: like (1), and 'test' table removed first
Speed: 752 s (so, the same since pg_xlog sym'linked)


(3)

Setting: like (2), but removed symlink of pg_xlog, so
having it again on "data disk" where big data is

Speed: 801 s (so ~1 minute longer)

BTW: I expected longer duration for scenario (3).



IMHO: As neither the CPUs nor the disk throughput nor the
postgres.exe task's CPU consumption was at its limits: I
wonder what is the problem here. Maybe it is not postgresql
related at all. I'll try to execute these tests on a SSD
and/or Raid system.

 Felix



In response to

Responses

pgsql-performance by date

Next:From: fkater@googlemail.comDate: 2010-01-18 13:08:38
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Previous:From: Tom LaneDate: 2010-01-17 23:14:38
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