Well, I can give a measurement on my home PC, a Linux box running Ubuntu 17.10 with a Samsung 960 EVO 512GB NVME disk containing Postgres 10. Using your pgbench init I got for example:
pgbench -c 10 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 0.679 ms
tps = 14730.402329 (including connections establishing)
tps = 14733.000950 (excluding connections establishing)
I will try to run a test on our production system which has a pair of Intel DC P4600 2TB in RAID0 tomorrow.
On Tue, Apr 10, 2018 at 9:58 PM Charles Sprickman <css@xxxxxxxxxxx> wrote:
On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov <skaurus@xxxxxxxxx> wrote:> SSDs are generally slower than spinning at sequential IO and way faster at random.Unreleased yet Seagate HDD boasts 480MB/s sequential read speed [1], and no HDD now can achieve that.Even SATA-3 SSD's could be faster than that for years now (550MB/s are quite typical), and NVME ones could be easily faster than 1GB/s and up to 3GB/s+.I'm curious to know where are you drawing these conclusions from?Yeah, that sequential info sounds weird.I’m only chiming in because I just setup one of those SoHo NAS boxes (Qnap) and it had both SSDs and HDDs installed. This was to be used for video editing, so it’s almost all sequential reads/writes. On 10Gb/s ethernet sequential reads off the cached content on the SSDs was somewhere around 800MB/s. These were non-enterprise SSDs.Charles2018-04-10 22:00 GMT+03:00 Aaron <aaron.werman@xxxxxxxxx>:RDBMS such as pg are beasts that turn random IO requests, traditionally slow in spinning drives, into sequential. WAL is a good example of this.SSDs are generally slower than spinning at sequential IO and way faster at random.Expect therefore for SSD to help if you are random IO bound. (Some cloud vendors offer SSD as a way to get dedicated local io and bandwidth - so sometimes it helps stablize performance vs. virtualized shared io.)A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN/AaronYou don't mention the size of your database. Does it fit in memory? If so your disks aren't going to matter a whole lot outside of potentially being i/o bound on the writes. Otherwise getting your data into SSDs absolutely can have a few multiples of performance impact. The NVME M.2 drives can really pump out the data. Maybe push your WAL onto those (as few motherboards have more than two connectors) and use regular SSDs for your data if you have high write rates.Meanwhile, if you're looking for strong cloud hosting for Postgres but the speed of physical hardware, feel free to contact me as my company does this for some companies who found i/o limits on regular cloud providers to be way too slow for their needs.good luck (and pardon the crass commercial comments!),-- Ben ScherreyOn Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@xxxxxxxxxxxxxx> wrote:One of our four "big iron" (spinning disks) servers went belly up today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to a cloud service at the end of the year, so bad timing on this. We didn't want to buy any more hardware, but now it looks like we have to.I followed the discussions about SSD drives when they were first becoming mainstream; at that time, the Intel devices were king. Can anyone recommend what's a good SSD configuration these days? I don't think we want to buy a new server with spinning disks.We're replacing:8 core (Intel)48GB memory12-drive 7200 RPM 500GBRAID1 (2 disks, OS and WAL log)RAID10 (8 disks, postgres data dir)2 sparesUbuntu 16.04Postgres 9.6The current system peaks at about 7000 TPS from pgbench.Our system is a mix of non-transactional searching (customers) and transactional data loading (us).Thanks!Craig-----------------------------------eMolecules, Inc.---------------------------------Chief Technology Officer
Craig A. James
