2018-07-17 10:55 GMT-03:00 Fabio Pardi <f.pardi@xxxxxxxxxxxx>: > If you have a RAID cache, i would disable it, since we are only focusing > on the disks. Cache can give you inconsistent data (even it looks like > is not the case here). > > Also, we can do a step backward, and exclude postgres from the picture > for the moment. > > try to perform a dd test in reading from disk, and let us know. > > like: > > - create big_enough_file > - empty OS cache > - dd if=big_enough_file of=/dev/null > > and post the results for both disks. > > Also i think it makes not much sense testing on RAID 0. I would start > performing tests on a single disk, bypassing RAID (or, as mentioned, at > least disabling cache). > But in my case, both the 2 SSDs and the 2 HDDs are in RAID ZERO. This way it would not be a valid test ? Because the 2 environments are in RAID ZERO. > The findings should narrow the focus > > > regards, > > fabio pardi > > On 07/17/2018 03:19 PM, Neto pr wrote: >> 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 >>>>> >>>>
