Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ffiqI-0007GM-7l for pgsql-performance@arkaria.postgresql.org; Wed, 18 Jul 2018 09:33:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ffiqG-0003bj-Nx for pgsql-performance@arkaria.postgresql.org; Wed, 18 Jul 2018 09:33:52 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ffiqG-0003bb-Cd for pgsql-performance@lists.postgresql.org; Wed, 18 Jul 2018 09:33:52 +0000 Received: from webmail.normation.com ([212.129.19.75] helo=nrm-vir-mail-01.normation.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ffiq6-0000Nt-DT for pgsql-performance@postgresql.org; Wed, 18 Jul 2018 09:33:51 +0000 Received: from [192.168.90.10] (republique-1.normation.com [78.249.200.184]) by nrm-vir-mail-01.normation.com (Postfix) with ESMTPSA id D143D222FE7; Wed, 18 Jul 2018 11:33:39 +0200 (CEST) Subject: Re: Why HDD performance is better than SSD in this case To: Neto pr , Mark Kirkwood , postgres performance list References: <01bbbc88-2a47-f078-ea40-ab2c1acafa1f@portavita.eu> From: Nicolas Charles Message-ID: <20b9e5bd-66ff-f7a0-a0e2-ffb917be2897@normation.com> Date: Wed, 18 Jul 2018 11:33:39 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A6D79CDA4FD4848663D3354C" Content-Language: fr List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A6D79CDA4FD4848663D3354C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Le 18/07/2018 à 03:16, Neto pr a écrit : > 2018-07-17 22:13 GMT-03:00 Neto pr : >> 2018-07-17 20:04 GMT-03:00 Mark Kirkwood : >>> 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. >>>> >>>> --------------A6D79CDA4FD4848663D3354C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Le 18/07/2018 à 03:16, Neto pr a écrit :
2018-07-17 22:13 GMT-03:00 Neto pr <netopr9@gmail.com>:

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

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.




        

      

      

    

    

  


--------------A6D79CDA4FD4848663D3354C--