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