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

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