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

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