it depend of ssd type ie different ssd need diferent alignment. . on samsung evo should be partition aligned to 3072 not default 2048 , to start on erase block bounduary . And fs block should be 8kb (as I remember correctly...)
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
On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9@xxxxxxxxx> 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.
