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

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