2018-07-17 10:04 GMT-03:00 Neto pr <netopr9@xxxxxxxxx>: > Sorry.. I replied in the wrong message before ... > follows my response. > ------------- > > Thanks all, but I still have not figured it out. > This is really strange because the tests were done on the same machine > (I use HP ML110 Proliant 8gb RAM - Xeon 2.8 ghz processor (4 > cores), and POSTGRESQL 10.1. > - Only the mentioned query running at the time of the test. > - I repeated the query 7 times and did not change the results. > - Before running each batch of 7 executions, I discarded the Operating > System cache and restarted DBMS like this: > (echo 3> / proc / sys / vm / drop_caches; > > discs: > - 2 units of Samsung Evo SSD 500 GB (mounted on ZERO RAID) > - 2 SATA 7500 Krpm HDD units - 1TB (mounted on ZERO RAID) > > - The Operating System and the Postgresql DBMS are installed on the SSD disk. > One more information. I used default configuration to Postgresql.conf Only exception is to : random_page_cost on SSD is 1.1 > Best Regards > [ ]`s Neto > > 2018-07-17 1:08 GMT-07:00 Fabio Pardi <f.pardi@xxxxxxxxxxxx>: >> As already mentioned by Robert, please let us know if you made sure that >> nothing was fished from RAM, over the faster test. >> >> In other words, make sure that all caches are dropped between one test >> and another. >> >> Also,to better picture the situation, would be good to know: >> >> - which SSD (brand/model) are you using? >> - which HDD? >> - how are the disks configured? RAID? or not? >> - on which OS? >> - what are the mount options? SSD requires tuning >> - did you make sure that no other query was running at the time of the >> bench? >> - are you making a comparison on the same machine? >> - is it HW or VM? benchs should better run on bare metal to avoid >> results pollution (eg: other VMS on the same hypervisor using the disk, >> host caching and so on) >> - how many times did you run the tests? >> - did you change postgres configuration over tests? >> - can you post postgres config? >> - what about vacuums or maintenance tasks running in the background? >> >> Also, to benchmark disks i would not use a custom query but pgbench. >> >> Be aware: running benchmarks is a science, therefore needs a scientific >> approach :) >> >> regards >> >> fabio pardi >> >> >> >> On 07/17/2018 07:00 AM, Neto pr wrote: >>> Dear, >>> Some of you can help me understand this. >>> >>> This query plan is executed in the query below (query 9 of TPC-H >>> Benchmark, with scale 40, database with approximately 40 gb). >>> >>> The experiment consisted of running the query on a HDD (Raid zero). >>> Then the same query is executed on an SSD (Raid Zero). >>> >>> Why did the HDD (7200 rpm) perform better? >>> HDD - TIME 9 MINUTES >>> SSD - TIME 15 MINUTES >>> >>> As far as I know, the SSD has a reading that is 300 times faster than SSD. >>> >>> --- Execution Plans--- >>> ssd 40g >>> https://explain.depesz.com/s/rHkh >>> >>> hdd 40g >>> https://explain.depesz.com/s/l4sq >>> >>> Query ------------------------------------ >>> >>> select >>> nation, >>> o_year, >>> sum(amount) as sum_profit >>> from >>> ( >>> select >>> n_name as nation, >>> extract(year from o_orderdate) as o_year, >>> l_extendedprice * (1 - l_discount) - ps_supplycost * >>> l_quantity as amount >>> from >>> part, >>> supplier, >>> lineitem, >>> partsupp, >>> orders, >>> nation >>> where >>> s_suppkey = l_suppkey >>> and ps_suppkey = l_suppkey >>> and ps_partkey = l_partkey >>> and p_partkey = l_partkey >>> and o_orderkey = l_orderkey >>> and s_nationkey = n_nationkey >>> and p_name like '%orchid%' >>> ) as profit >>> group by >>> nation, >>> o_year >>> order by >>> nation, >>> o_year desc >>> >>
