Re: Why HDD performance is better than SSD in this case
От | Mark Kirkwood |
---|---|
Тема | Re: Why HDD performance is better than SSD in this case |
Дата | |
Msg-id | db4016fe-5103-7a27-37ff-545beb825fd5@catalyst.net.nz обсуждение исходный текст |
Ответ на | Re: Why HDD performance is better than SSD in this case (Nicolas Charles <nicolas.charles@normation.com>) |
Список | pgsql-performance |
Yeah, A +1 to telling us the model. In particular the later EVOs use TLC nand with a small SLC cache... and when you exhaust the SLC cache the performance can be worse than a HDD... On 18/07/18 01:44, Nicolas Charles wrote: > 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 > > > Nicolas > > Nicolas CHARLES > Le 17/07/2018 à 15:19, Neto pr a écrit : >> 2018-07-17 10:04 GMT-03:00 Neto pr <netopr9@gmail.com>: >>> 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@portavita.eu>: >>>> 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 >>>>> > >
В списке pgsql-performance по дате отправления:
Предыдущее
От: Mark KirkwoodДата:
Сообщение: Re: Why HDD performance is better than SSD in this case