Re: HDD vs SSD without explanation
2018-01-15 20:04 GMT-08:00 Mark Kirkwood : > On 16/01/18 13:18, Fernando Hevia wrote: > >> >> >> >> The 6 Gb/s interface is capable of a maximum throughput of around 600 >> Mb/s. None of your drives can achieve that so I don't think you are limited >> to the interface speed. The 12 Gb/s interface speed advantage kicks in when >> there are several drives installed and it won't make a diference in a single >> drive or even a two drive system. >> >> But don't take my word for it. Test your drives throughput with the >> command Justin suggested so you know exactly what each drive is capable of: >> >> Can you reproduce the speed difference using dd ? >> time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K >> skip=$((128*$RANDOM/32)) # set bs to optimal_io_size >> >> >> While common sense says SSD drive should outperform the mechanical one, >> your test scenario (large volume sequential reads) evens out the field a >> lot. Still I would have expected somewhat similar results in the outcome, so >> yes, it is weird that the SAS drive doubles the SSD performance. That is why >> I think there must be something else going on during your tests on the SSD >> server. It can also be that the SSD isn't working properly or you are >> running an suboptimal OS+server+controller configuration for the drive. >> > > I would second the analysis above - unless you see your read MB/s slammed up > against 580-600MB/s contunuously then the interface speed is not the issue. > We have some similar servers that we replaced 12x SAS with 1x SATA 6 GBit/s > (Intel DC S3710) SSD...and the latter way outperforms the original 12 SAS > drives. > > I suspect the problem is the particular SSD you have - I have benchmarked > the 256GB EVO variant and was underwhelmed by the performance. These > (budget) triple cell nand SSD seem to have highly variable read and write > performance (the write is all about when the SLC nand cache gets > full)...read I'm not so sure of - but it could be crappy chipset/firmware > combination. In short I'd recommend *not* using that particular SSD for a > database workload. I'd recommend one of the Intel Datacenter DC range (FWIW > I'm not affiliated with Intel in any way...but their DC stuff works well). > > regards > > Mark Hi Mark In other forums one person said me that on samsung evo should be partition aligned to 3072 not default 2048 , to start on erase block bounduary . And fs block should be 8kb. I am studing this too. Some DBAs have reported in other situations that the SSDs when they are full, are very slow. Mine is 85% full, so maybe that is also influencing. I'm disappointed with this SSD from Samsung, because in theory, the read speed of an SSD should be more than 300 times faster than an HDD and this is not happening. regards Neto
Fwd: Re: Query is slow when run for first time; subsequent execution is fast
Missed to have mailing list in to address.. forwarding now. -- Forwarded message -- From: "Nandakumar M" Date: 15 Jan 2018 12:16 Subject: Re: Query is slow when run for first time; subsequent execution is fast To: "Pavel Stehule" Cc: Hi, On Fri, Jan 12, 2018 at 3:34 PM, Pavel Stehule wrote: > > >> maybe some your indexes and some system tables are bloated. Try you run > VACUUM FULL ANALYZE > Tried this suggestion. Planning time gets reduced slightly but it is still way higher on the first run compared to subsequent runs of the same query. Regards, Nanda
Re: Query is slow when run for first time; subsequent execution is fast
Nandakumar M schrieb am 12.01.2018 um 09:03:
> Even if I close a connection and open a new one and execute the same
> query, the planning time is considerably less than the first time.
> Only when I restart the Postgres server then I face high planning
> time again.
Yes, because the data is cached by Postgres ("shared_buffers") and the
filesystem.
Re: HDD vs SSD without explanation
Le 16/01/2018 à 11:14, Neto pr a écrit : 2018-01-15 20:04 GMT-08:00 Mark Kirkwood : On 16/01/18 13:18, Fernando Hevia wrote: The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't think you are limited to the interface speed. The 12 Gb/s interface speed advantage kicks in when there are several drives installed and it won't make a diference in a single drive or even a two drive system. But don't take my word for it. Test your drives throughput with the command Justin suggested so you know exactly what each drive is capable of: Can you reproduce the speed difference using dd ? time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size While common sense says SSD drive should outperform the mechanical one, your test scenario (large volume sequential reads) evens out the field a lot. Still I would have expected somewhat similar results in the outcome, so yes, it is weird that the SAS drive doubles the SSD performance. That is why I think there must be something else going on during your tests on the SSD server. It can also be that the SSD isn't working properly or you are running an suboptimal OS+server+controller configuration for the drive. I would second the analysis above - unless you see your read MB/s slammed up against 580-600MB/s contunuously then the interface speed is not the issue. We have some similar servers that we replaced 12x SAS with 1x SATA 6 GBit/s (Intel DC S3710) SSD...and the latter way outperforms the original 12 SAS drives. I suspect the problem is the particular SSD you have - I have benchmarked the 256GB EVO variant and was underwhelmed by the performance. These (budget) triple cell nand SSD seem to have highly variable read and write performance (the write is all about when the SLC nand cache gets full)...read I'm not so sure of - but it could be crappy chipset/firmware combination. In short I'd recommend *not* using that particular SSD for a database workload. I'd recommend one of the Intel Datacenter DC range (FWIW I'm not affiliated with Intel in any way...but their DC stuff works well). regards Mark Hi Mark In other forums one person said me that on samsung evo should be partition aligned to 3072 not default 2048 , to start on erase block bounduary . And fs block should be 8kb. I am studing this too. Some DBAs have reported in other situations that the SSDs when they are full, are very slow. Mine is 85% full, so maybe that is also influencing. I'm disappointed with this SSD from Samsung, because in theory, the read speed of an SSD should be more than 300 times faster than an HDD and this is not happening. regards Neto Hi Neto, Unfortunately, Samsung 850 Evo is not a particularly fast SSD - especially it's not really consistent in term of performance ( see https://www.anandtech.com/show/8747/samsung-ssd-850-evo-review/5 and https://www.anandtech.com/bench/product/1913 ). This is not a product for professional usage, and you should not expect great performance from it - as reported by these benchmark, you can have a 34ms latency in very intensive usage: ATSB - The Destroyer (99th Percentile Write Latency)99th Percentile Latency in Microseconds - Lower is Better *34923 *Even average write latency of the Samsung 850 Evo is 3,3 ms in intensive workload, while the HPE 300 GB 12G SAS is reported to have an average of 2.9 ms, and won't suffer from write amplification As long has you stick with a light usage, this SSD will probably be more than capable, but if you want to host a database, you should really look at PRO drives Kind regards Nicolas **
Re: HDD vs SSD without explanation
On 16/01/18 23:14, Neto pr wrote: 2018-01-15 20:04 GMT-08:00 Mark Kirkwood : On 16/01/18 13:18, Fernando Hevia wrote: The 6 Gb/s interface is capable of a maximum throughput of around 600 Mb/s. None of your drives can achieve that so I don't think you are limited to the interface speed. The 12 Gb/s interface speed advantage kicks in when there are several drives installed and it won't make a diference in a single drive or even a two drive system. But don't take my word for it. Test your drives throughput with the command Justin suggested so you know exactly what each drive is capable of: Can you reproduce the speed difference using dd ? time sudo dd if=/dev/sdX of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32)) # set bs to optimal_io_size While common sense says SSD drive should outperform the mechanical one, your test scenario (large volume sequential reads) evens out the field a lot. Still I would have expected somewhat similar results in the outcome, so yes, it is weird that the SAS drive doubles the SSD performance. That is why I think there must be something else going on during your tests on the SSD server. It can also be that the SSD isn't working properly or you are running an suboptimal OS+server+controller configuration for the drive. I would second the analysis above - unless you see your read MB/s slammed up against 580-600MB/s contunuously then the interface speed is not the issue. We have some similar servers that we replaced 12x SAS with 1x SATA 6 GBit/s (Intel DC S3710) SSD...and the latter way outperforms the original 12 SAS drives. I suspect the problem is the particular SSD you have - I have benchmarked the 256GB EVO variant and was underwhelmed by the performance. These (budget) triple cell nand SSD seem to have highly variable read and write performance (the write is all about when the SLC nand cache gets full)...read I'm not so sure of - but it could be crappy chipset/firmware combination. In short I'd recommend *not* using that particular SSD for a database workload. I'd recommend one of the Intel Datacenter DC range (FWIW I'm not affiliated with Intel in any way...but their DC stuff works well). regards Mark Hi Mark In other forums one person said me that on samsung evo should be partition aligned to 3072 not default 2048 , to start on erase block bounduary . And fs block should be 8kb. I am studing this too. Some DBAs have reported in other situations that the SSDs when they are full, are very slow. Mine is 85% full, so maybe that is also influencing. I'm disappointed with this SSD from Samsung, because in theory, the read speed of an SSD should be more than 300 times faster than an HDD and this is not happening. Interesting - I didn't try changing the alignment. However I could get the rated write and read performance on simple benchmarks (provided it was in a PCIe V3 slot)...so figured it was ok with the default aligning. However once more complex workloads were attempted (databases and distributed object store) the performance was disappointing. If the SSD is 85% full that will not help either (also look at the expected lifetime of these EVO's - not that great for a server)! One thing worth trying is messing about with the IO scheduler: if you are using noop, then try deadline (like I said crappy firmware)... Realistically, I'd recommend getting an enterprise/DC SSD (put the EVO in your workstation, it will be quite nice there)! Cheers Mark
Re: Query is slow when run for first time; subsequent execution is fast
On Fri, Jan 12, 2018 at 12:03 AM, Nandakumar M wrote: > Hello Jeff, > > Thanks for the insights. > > >Don't keep closing and reopening connections. > > Even if I close a connection and open a new one and execute the same > query, the planning time is considerably less than the first time. Only > when I restart the Postgres server then I face high planning time again. > Oh. I've not seen that before. But then again I don't often restart my server and then immediately run very large queries with a stringent time deadline. You can try pg_prewarm, on pg_statistic table and its index. But I'd probably just put an entry in my db startup script to run this query immediately after startng the server, and let the query warm the cache itself. Why do you restart your database often enough for this to be an issue? Cheers, Jeff
RE: Query is slow when run for first time; subsequent execution is fast
Hello, FWIW, I do have the same issue. Unfortunately our application is running on a standard laptop/desktop computers, not dedicated servers. Restarting the computer leads to a restart of the database server, which slow down all queries for several minutes. Are you on Windows or Linux? I’m on Windows and wondering if the issue is the same on Linux? BR, Guillaume De : Jeff Janes [mailto:[email protected]] Envoyé : mercredi 17 janvier 2018 06:18 À : Nandakumar M Cc : pgsql-performa. Objet : Re: Query is slow when run for first time; subsequent execution is fast On Fri, Jan 12, 2018 at 12:03 AM, Nandakumar M mailto:[email protected]> > wrote: Hello Jeff, Thanks for the insights. >Don't keep closing and reopening connections. Even if I close a connection and open a new one and execute the same query, the planning time is considerably less than the first time. Only when I restart the Postgres server then I face high planning time again. Oh. I've not seen that before. But then again I don't often restart my server and then immediately run very large queries with a stringent time deadline. You can try pg_prewarm, on pg_statistic table and its index. But I'd probably just put an entry in my db startup script to run this query immediately after startng the server, and let the query warm the cache itself. Why do you restart your database often enough for this to be an issue? Cheers, Jeff smime.p7s Description: S/MIME cryptographic signature This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
