Re: Why HDD performance is better than SSD in this case

[Nicolas Charles <nicolas.charles@xxxxxxxxxxxxx>]

Le 18/07/2018 à 03:16, Neto pr a écrit :

2018-07-17 22:13 GMT-03:00 Neto pr <netopr9@xxxxxxxxx>:

2018-07-17 20:04 GMT-03:00 Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx>:

Ok, so dropping the cache is good.

How are you ensuring that you have one test setup on the HDDs and one on the
SSDs? i.e do you have 2 postgres instances? or are you using one instance
with tablespaces to locate the relevant tables? If the 2nd case then you
will get pollution of shared_buffers if you don't restart between the HHD
and SSD tests. If you have 2 instances then you need to carefully check the
parameters are set the same (and probably shut the HDD instance down when
testing the SSD etc).

Dear  Mark
To ensure that the test is honest and has the same configuration the
O.S. and also DBMS, my O.S. is installed on the SSD and DBMS as well.
I have an instance only of DBMS and two database.
- a database called tpch40gnorhdd with tablespace on the HDD disk.
- a database called tpch40gnorssd with tablespace on the SSD disk.
See below:

postgres=# \l
                List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |
Access privileges
---------------+----------+----------+-------------+-------------+-----------------------
 postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres          +
               |          |          |             |             |
postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres          +
               |          |          |             |             |
postgres=CTc/postgres
 tpch40gnorhdd | user1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 tpch40gnorssd | user1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

postgres=#

After 7 query execution in a database tpch40gnorhdd I restart the DBMS
(/etc/init.d/pg101norssd restart and drop cache of the O.S.) and go to
execution test with the database tpch40gnorssd.
You think in this case there is pollution of shared_buffers?
Why do you think having O.S. on SSD is bad? Do you could explain better?

Best regards
[]`s Neto

+1 information about EVO SSD Samsung:

 Model: 850 Evo 500 GB SATA III 6Gb/s -
http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/

As stated on his ML on january, Samsung 850 Evo is not a particularly fast SSD - especially it's not really consistent in term of performance ( see https://www.anandtech.com/show/8747/samsung-ssd-850-evo-review/5 and https://www.anandtech.com/bench/product/1913 ). This is not a product for professional usage, and you should not expect great performance from it - as reported by these benchmark, you can have a 34ms latency in very intensive usage:
ATSB - The Destroyer (99th Percentile Write Latency) 99th Percentile Latency in Microseconds - Lower is Better 34923

Even average write latency of the Samsung 850 Evo is 3,3 ms in intensive workload

Why are you using this type of SSD for your benchmark ? What do you plan to achieve ?

I can see a couple of things in your setup that might pessimize the SDD
case:
- you have OS on the SSD - if you tests make the system swap then this will
wreck the SSD result
- you have RAID 0 SSD...some of the cheaper ones slow down when you do this.
maybe test with a single SSD

regards
Mark

On 18/07/18 01:04, Neto pr wrote (note snippage):

(echo 3> / proc / sys / vm / drop_caches;

discs:
- 2 units of Samsung Evo SSD 500 GB (mounted on ZERO RAID)
- 2 SATA 7500 Krpm HDD units - 1TB (mounted on ZERO RAID)

- The Operating System and the Postgresql DBMS are installed on the SSD
disk.