2018-01-15 20:25 GMT-03:00 Neto pr <netopr9@xxxxxxxxx>:
Guys, sorry for the Top Post, I forgot ....2018-01-15 17:55 GMT-02:00 Fernando Hevia <fhevia@xxxxxxxxx>:
>
>
> 2018-01-15 15:32 GMT-03:00 Georg H. <georg-h@xxxxxxxxxxxxxxx>:
>>
>>
>> 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.
>
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
The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't think you are limited to the interface speed. The 12 Gb/s interface speed advantage kicks in when there are several drives installed and it won't make a diference in a single drive or even a two drive system.
But don't take my word for it. Test your drives throughput with the command Justin suggested so you know exactly what each drive is capable of:
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
While common sense says SSD drive should outperform the mechanical one, your test scenario (large volume sequential reads) evens out the field a lot. Still I would have expected somewhat similar results in the outcome, so yes, it is weird that the SAS drive doubles the SSD performance. That is why I think there must be something else going on during your tests on the SSD server. It can also be that the SSD isn't working properly or you are running an suboptimal OS+server+controller configuration for the drive.
