And perhaps more interesting:
Re-running query 9 against the (single) HDD setup *but* with pgsql_tmp
symlinked to the 2x SSD RAID0: 15 minutes
I'm thinking that you have inadvertently configured your HDD test in
this way (you get 9 minutes because you have 2x HDDs). Essentially most
of the time taken for this query is in writing and reading files for
sorting/hashing, so where pgsql_tmp is located hugely influences the
overall time.
regards
Mark
On 20/07/18 12:33, Mark Kirkwood wrote:
FWIW:
re-running query 9 using the SSD setup as 2x crucial M550 RAID0: 10
minutes.
On 20/07/18 11:30, Mark Kirkwood wrote:
One more thought on this:
Query 9 does a lot pf sorting to disk - so there will be writes for
that and all the reads for the table scans. Thus the location of your
instance's pgsql_tmp directory(s) will significantly influence results.
I'm wondering if in your HDD test the pgsql_tmp on the *SSD's* is
being used. This would make the HDDs look faster (obviously - as they
only need to do reads now). You can check this with iostat while the
HDD test is being run, there should be *no* activity on the SSDs...if
there is you have just found one reason for the results being quicker
than it should be.
FWIW: I had a play with this: ran two version 10.4 instances, one on
a single 7200 rpm HDD, one on a (ahem slow) Intel 600p NVME. Running
query 9 on the scale 40 databases I get:
- SSD 30 minutes
- HDD 70 minutes
No I'm running these on an a Intel i7 3.4 Ghz 16 GB RAM setup. Also
both postgres instances have default config apart from random_page_cost.
Comparing my results with yours - the SSD one is consistent...if I
had two SSDs in RAID0 I might halve the time (I might try this).
However my HDD result is not at all like yours (mine makes more sense
to be fair...would expect HDD to be slower in general).
Cheers (thanks for an interesting puzzle)!
Mark
On 18/07/18 13:13, Neto pr wrote:
Dear Mark
To ensure that the test is honest and has the same configuration the
O.S. and also DBMS, my O.S. is installed on the SSD and DBMS as well.
I have an instance only of DBMS and two database.
- a database called tpch40gnorhdd with tablespace on the HDD disk.
- a database called tpch40gnorssd with tablespace on the SSD disk.
See below:
