On Jan 18, 2010, at 3:20 AM, fkater@googlemail.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
theproblem. I'm assuming its doing Gzip, and at the default compression level, which on random data will be in the
15MB/secrange. I don't know if TOAST will do compression at a lower compression level. Is your data typically random
orincompressible? 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
canget 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance