Re: HDD vs SSD without explanation - Mailing list pgsql-performance

2018-01-15 17:55 GMT-02:00 Fernando Hevia <fhevia@gmail.com>:
>
>
> 2018-01-15 15:32 GMT-03:00 Georg H. <georg-h@silentrunner.de>:
>>
>>
>> Hello Neto
>>
>> Am 14.01.2018 um 21:44 schrieb Neto pr:
>>>
>>> Dear all
>>>
>>> Someone help me analyze the two execution plans below (Explain ANALYZE
>>> used), is the  query 9 of TPC-H benchmark [1].
>>> I'm using a server HP Intel Xeon 2.8GHz/4-core - Memory 8GB HDD SAS 320GB
>>> 15 Krpm AND SSD Sansung EVO 500GB.
>>> My DBMS parameters presents in postgresql.conf is default, but in SSD I
>>> have changed random_page_cost = 1.0.
>>>
>> you are comparing a SAS Drive against a SATA SSD. Their interfaces serve a
>> completely different bandwidth.
>> While a SAS-3 device does 12 Gbit/s  SATA-3 device  is only able to
>> transfer 6 Gbit/s  (a current SAS-4 reaches 22.5 Gbit/s)
>> Do a short research on SAS vs SATA and then use a SAS SSD for comparison
>> :)
>
>
> The query being all read operations both drives should perform somewhat
> similarly. Therefore, either the SAS drive has some special sauce to it
> (a.k.a very fast built-in cache) or there is something else going on these
> systems. Otherwise he shouldn't be stressing the 6 Gbit/s interface limit
> with a single drive, be that the SATA or the SAS drive.
>
> Neto, you have been suggested to provide a number of command outputs to know
> more about your system. Testing the raw read throughput of both your drives
> should be first on your list.
>

Guys, sorry for the Top Post, I forgot ....

Fernando, I think the difference of 6 Gb/s to 12 Gb/s from SAS is what
caused the difference in query execution time.
Because looking at the execution plans and the cost estimate, I did
not see many differences, in methods of access among other things.
Regarding the query, none of them use indexes, since I did a first
test without indexes.
Do you think that if I compare the disk below HDD SAS that has a
transfer rate of 6Gb/s equal to the SSD SATA 6Gb/s, do you think the
SSD would be more agile in this case?

HDD: HP 450GB 6G SAS 15K rpm LFF (3.5-inch) Part-Number: 652615-B21

Neto

Can you reproduce the speed difference using dd ?
time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size