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). 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 >>>> >>>
