From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Neto pr <netopr9@gmail.com>
Cc: Fabio Pardi <f.pardi@portavita.eu>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why HDD performance is better than SSD in this case
Date: Fri, 20 Jul 2018 12:33:04 +1200
Message-ID: <4ae07b23-912b-e3fb-8e9a-2959c2a64f19@catalyst.net.nz> (raw)
In-Reply-To: <ce163be0-06f7-8cc6-f407-cee62235c561@catalyst.net.nz>
References: <CA+wPC0PsWacZpNX2OfRSSauUg9iNa7tReJU0oTLnM7K8PFFt6A@mail.gmail.com>
<01bbbc88-2a47-f078-ea40-ab2c1acafa1f@portavita.eu>
<CA+wPC0M=e5ZXd1NF1nz6mYfmPgys89vB4A74GmxnnK+KpA4_XA@mail.gmail.com>
<d29dfb84-bb9a-d7c7-74eb-e263eae07901@catalyst.net.nz>
<CA+wPC0PzsKEV48D3ibFpaYUnjbKgeEBrN60W-O0Wh2WaT-ph1A@mail.gmail.com>
<ce163be0-06f7-8cc6-f407-cee62235c561@catalyst.net.nz>
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:
>>
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, netopr9@gmail.com, f.pardi@portavita.eu, pgsql-performance@lists.postgresql.org
Subject: Re: Why HDD performance is better than SSD in this case
In-Reply-To: <4ae07b23-912b-e3fb-8e9a-2959c2a64f19@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox