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: Scott Carey <scott(at)richrelevance(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Date: 2010-01-19 10:50:07
Message-ID: 20100119105007.GB2200@comppasch2 (view raw or flat)
Lists: pgsql-performance
Scott Carey:

> You are CPU bound.
> 30% of 4 cores is greater than 25%.  25% is one core fully
> used.

I have measured the cores separately. Some of them reached
30%. I am not CPU bound here.

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

Hm. I use 'bytea' (see original posting) and SET STORAGE
EXTERNAL for this column which switches of the compression
AFAIK. I was doing this to measure the raw performance and
may not be able to use compression later in real scenario.

BTW: In the initial tests I used 200 blocks of 4 MB bytea
which is the real scenario; later on I was using 10 times
80MB each just to reduce the number of INSERT commands and
to make it easier to find the performance problem.

> Is your data typically random or incompressible?  If it is
> compressible then your test should be changed to reflect
> that.

Unfortunatelly I can't say much yet. Of course, in case
compression makes sense and fits the CPU performance I will
use it.

> If I am wrong, you are I/O bound

Yes. This is the first half of what we found out now.

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

Yes, I am using this tool.

However, the deeper question is (sounds ridiculous): Why am
I I/O bound *this much* here. To recall: The write
performance in pg is about 20-25% of the worst case serial
write performance of the disk (and only about 8-10% of the
best disk perf) even though pg_xlog (WAL) is moved to
another disk, only 10 simple INSERT commands, a simple table
of 5 columns (4 unused, one bytea) and one index for OID, no
compression since STORAGE EXTERNAL, ntfs tweaks (noatime
etc), ...

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

Done already (see earlier posting). I am not CPU bound.
Speed was the same.

Thank You for the detailed reply.


In response to


pgsql-performance by date

Next:From: fkater@googlemail.comDate: 2010-01-19 10:55:26
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Previous:From: fkater@googlemail.comDate: 2010-01-19 10:16:07
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?

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