2018-07-17 10:44 GMT-03:00 Nicolas Charles <nicolas.charles@xxxxxxxxxxxxx>: > Hi Neto, > > You should list the SSD model also - there are pleinty of Samsung EVO drives > - and they are not professional grade. > > Among the the possible issues, the most likely (from my point of view) are: > > - TRIM command doesn't go through the RAID (which is really likely) - so the > SSD controller think it's full, and keep pushing blocks around to level > wear, causing massive perf degradation - please check this config on you > RAID driver/adapter > > - TRIM is not configured on the OS level for the SSD > > - Partitions is not correctly aligned on the SSD blocks > > > Without so little details on your system, we can only try to guess the real > issues > Thank you Nicolas, for your tips. I believe your assumption is right. This SSD really is not professional, even if Samsung's advertisement says yes. If I have to buy another SSD I will prefer INTEL SSDs. I had a previous problem with it (Sansung EVO) as it lost in performance to a SAS HDD, but however, the SAS HDD was a 12 Gb/s transfer rate and the SSD was 6 Gb/s. But now I tested against an HDD (7200 RPM) that has the same transfer rate as the SSD 6 Gb/sec. and could not lose in performance. Maybe it's the unconfigured trim. Could you give me some help on how I could check if my RAID is configured for this, I use Hardware RAID using HP software (HP Storage Provider on boot). And on Debian 8 Operating System, how could I check the TRIM configuration ? Best []'s Neto > > Nicolas > > Nicolas CHARLES > > Le 17/07/2018 à 15:19, Neto pr a écrit : >> >> 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 >>>>> >
