On Mon, Jan 15, 2018 at 7:38 AM, Neto pr <netoprbr9@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.
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:
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.
