Subject: Re: Why HDD performance is better than SSD in this case
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Neto pr <netopr9@gmail.com>
Cc: Fabio Pardi <f.pardi@portavita.eu>, pgsql-performance@lists.postgresql.org
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>
Message-ID: <4ae07b23-912b-e3fb-8e9a-2959c2a64f19@catalyst.net.nz>
Date: Fri, 20 Jul 2018 12:33:04 +1200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: <ce163be0-06f7-8cc6-f407-cee62235c561@catalyst.net.nz>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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:
>>
>>
>
>