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

2018-01-15 16:18 GMT-08:00 Fernando Hevia <fhevia@gmail.com>:
>
>
> 2018-01-15 20:25 GMT-03:00 Neto pr <netopr9@gmail.com>:
>>
>> 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
>
>
> 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.

Ok.

Can you help me to analyze the output of the command: dd if=/dev/sdX
of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
optimal_io_size
I put a heavy query running in the DBMS and ran the time sudo command
...  three times for each environment (SAS HDD and SATA SSD), see
below that the SSD had 412,325 and 120 MB/s
The HDD SAS had 183,176 and 183 MB/s ... strange that in the end the
SAS HDD can execute the query faster ... does it have something else
to analyze in the output below?

-------============  SAS HDD 320 Gb 12 Gb/s ==========--------------
root@deb:/etc#  time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K
skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 188.01 s, 183 MB/s

real    3m8.473s
user    0m0.076s
sys     0m23.628s
root@deb:/etc#  time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K
skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 195.582 s, 176 MB/s

real    3m16.304s
user    0m0.056s
sys     0m19.632s
root@deb:/etc#  time sudo dd if=/dev/sda2 of=/dev/null bs=1M count=32K
skip=$((128*$RANDOM/32)) # set bs to optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 187.822 s, 183 MB/s

real    3m8.457s
user    0m0.032s
sys     0m20.668s
root@deb:/etc#

-------============ SATA SSD 500 Gb 6 Gb/s =========----------------
root@hp2ml110deb:/etc/postgresql/10# time sudo dd if=/dev/sdb
of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
optimal_io_size

32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 83.4281 s, 412 MB/s

real    1m23.693s
user    0m0.056s
sys     0m19.300s

root@hp2ml110deb:/etc/postgresql/10# time sudo dd if=/dev/sdb
of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 105.88 s, 325 MB/s

real    1m46.301s
user    0m0.020s
sys     0m14.676s

root@hp2ml110deb:/etc/postgresql/10# time sudo dd if=/dev/sdb
of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to
optimal_io_size
32768+0 records in
32768+0 records out
34359738368 bytes (34 GB) copied, 285.959 s, 120 MB/s

real    4m46.283s
user    0m0.036s
sys     0m15.444s

------------------------------------- END -----------------------------


>