Dear NTPT 2018-01-15 16:54 GMT-08:00 NTPT <n...@seznam.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 <mmonc...@gmail.com> > Komu: Neto pr <netopr...@gmail.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 <netopr...@gmail.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 > >
