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
