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