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

From fkater@googlemail.com
Subject Re: Inserting 8MB bytea: just 25% of disk perf used?
Date
Msg-id 20100119105007.GB2200@comppasch2
Whole thread Raw
In response to Re: Inserting 8MB bytea: just 25% of disk perf used?  (Scott Carey <scott@richrelevance.com>)
Responses Re: Inserting 8MB bytea: just 25% of disk perf used?
Re: Inserting 8MB bytea: just 25% of disk perf used?
Re: Inserting 8MB bytea: just 25% of disk perf used?
List 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.

 Felix



pgsql-performance by date:

Previous
From: "fkater@googlemail.com"
Date:
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?
Next
From: "fkater@googlemail.com"
Date:
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?