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

Ok, so you are using 1 instance and tablespaces. Also I see you are
restarting the instance between HDD and SSD tests, so all good there.

The point I made about having the OS on the SSD's means that if these
tests make your system swap, and your swap device is on the SSDs (which
is probably is by default), then swap activity will compete with db
access activity for IOPS on your SSDs and spoil the results of your test
(i.e slow down your SSDs).

You can check this using top, sar or iostat to see *if* you are swapping
during the tests.

Ideally you would design your setup to use 3 separate devices:

- one device (or array) for os, swap, tmp etc

- one device (HDD array) for you 'HDD' tablespace

- one device (SDD array) for your 'SDD' tablespace

regards

Mark

On 18/07/18 13:13, Neto pr wrote:
>
> 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?
>
>