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

Dear NTPT

2018-01-15 16:54 GMT-08:00 NTPT <NTPT(at)seznam(dot)cz>:

> I bet this is a ssd partition alignment problem there are erase block size
> of 3mb and this should be taken in account, when You partition ssd drive,
> creating a raid and filesystem etc...
>

That is a good observation. I believe the block size was set by default
when I formatted the drive. I use Debian 64bits version 8, and all disks
are with ext4 file system. What size block do you suggest for SSD and HDD?

Neto

> ---------- Původní e-mail ----------
> Od: Merlin Moncure <mmoncure(at)gmail(dot)com>
> Komu: Neto pr <netoprbr9(at)gmail(dot)com>
> Datum: 15. 1. 2018 20:17:17
> Předmět: Re: why SSD is slower than HDD SAS 15K ?
>
> 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.
>
> merlin
>
>