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

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

From: Scott Carey <scott(at)richrelevance(dot)com>
To: "fkater(at)googlemail(dot)com" <fkater(at)googlemail(dot)com>
Cc: Pierre Frédéric Caillaud <lists(at)peufeu(dot)com>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-18 23:37:12
Message-ID: 92E05651-336E-4721-91F5-339A838A1DEE@richrelevance.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Jan 18, 2010, at 3:20 AM, fkater(at)googlemail(dot)com wrote:

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

You are CPU bound.

30% of 4 cores is greater than 25%.  25% is one core fully used.  The postgres compression of data in TOAST is probably the problem.  I'm assuming its doing Gzip, and at the default compression level, which on random data will be in the 15MB/sec range.  I don't know if TOAST will do compression at a lower compression level.  Is your data typically random or incompressible?  If it is compressible then your test should be changed to reflect that.


If I am wrong, you are I/O bound -- this will show up in windows Performance Monitor as "Disk Time (%)" -- which you can get on a per device or total basis, along with i/o per second (read and/or write) and bytes/sec metrics.

To prove that you are CPU bound, split your test in half, and run the two halves at the same time.  If you are CPU bound, then your bytes/sec performance will go up significantly, along with CPU usage.

If you are I/O bound, it will stay the same or get worse.

-Scott
> 
> 
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


In response to

Responses

pgsql-performance by date

Next:From: Ing. Marcos L. Ortiz ValmasedaDate: 2010-01-19 08:25:28
Subject: Re: renice on an I/O bound box
Previous:From: Kenneth MarshallDate: 2010-01-18 18:57:44
Subject: Re: Bad plan choice nestloop vs. hashjoin

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