Dear Merlin 2018-01-15 11:16 GMT-08:00 Merlin Moncure <mmonc...@gmail.com>:
> 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. > > 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 >
