Re: HDD vs SSD without explanation

2018-01-15 20:25 GMT-03:00 Neto pr <netopr9(at)gmail(dot)com>:

> 2018-01-15 17:55 GMT-02:00 Fernando Hevia <fhevia(at)gmail(dot)com>:
> >
> >
> > 2018-01-15 15:32 GMT-03:00 Georg H. <georg-h(at)silentrunner(dot)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
>

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.