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:
* 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)
* 754 s (14.5 MB/s)
> CREATE TABLE test (LIKE yourtable);
> INSERT INTO test SELECT * FROM yourtable;
> This does the same thing but also writes WAL.
> I wonder what results you'll get.
Setting: like (1), and 'test' table removed first
Speed: 752 s (so, the same since pg_xlog sym'linked)
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.
In response to
pgsql-performance by date
|Next:||From: firstname.lastname@example.org||Date: 2010-01-18 13:08:38|
|Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?|
|Previous:||From: Tom Lane||Date: 2010-01-17 23:14:38|
|Subject: Re: a heavy duty operation on an "unused" table kills my server |