Re: why SSD is slower than HDD SAS 15K ?

Dear Merlin
2018-01-15 11:16 GMT-08:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

> On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9(at)gmail(dot)com> wrote:
> > Hello 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 two servers HP Intel Xeon 2.8GHz/4-core - Memory 8GB. O.S.
> > Debian8, using EXT4 filesystem.
> >
> > Server 1
> > - HDD SAS 15 Krpm - 320 GB (Location where O.S. Debian and Postgresql are
> > installed).
> >
> > Server 2
> > - Samsung Evo SSD 500 GB (Location where Postgresql is Installed)
> > - HDD Sata 7500 Krpm - 1TB (Location where O.S Debian is installed)
> >
> > My DBMS parameters presents in postgresql.conf is default, but in SSD I
> have
> > changed random_page_cost = 1.0.
> >
> > I do not understand, because running on an HDD SAS a query used half the
> > time. I explain better, in HDD spends on average 12 minutes the query
> > execution and on SSD spent 26 minutes.
> > I think maybe the execution plan is using more write operations, and so
> the
> > HDD SAS 15Krpm has been faster.
> > I checked that the temporary tablespace pg_default is on the SSD in
> server
> > 2, because when running show temp_tablespaces in psql returns empty,
> will be
> > in the default directory, where I installed the DBMS in:
> > /media/ssd500gb/opt/pgv101norssd/data.
> >
> > Anyway, I always thought that an SSD would be equal or faster, but in the
> > case and four more cases we have here, it lost a lot for the HDDs.
>
> Generally for reading data, yes, but you changed the query plan also.
> To get to the bottom of this let's get SSD performance numbers for
> both plans and HDD performance numbers for both plans. You're trying
> to measure device performance about are probably measuring the
> relative efficiencies of the generated plans.
>
>
Very good tip. I discovered that my SAS HDD drive has a transfer rate of
12Gb/s versus 6Gb/s of the SSD. Because of that reason the difference in
performance occurred. See below:

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

HDD: HPE 300GB 12G SAS Part-Number: 737261-B21
https://h20195.www2.hpe.com/v2/GetPDF.aspx%2Fc04111744.pdf

I intend to do my experiment, between HDD and SSD, abandon the SAS HDD and
use a SATA HDD, to compare with the SATA SSD.
I will use your strategy to put the OS and DBMS on the same disk, when it
is SSD and separate on the HDD.
Best Regards
Neto

> merlin
>