PFC schrieb:
PCI limits you to 133 MB/s (theoretical), actual speed being around 100-110 MB/s.
"Current" PCI 2.1+ implementations allow 533MB/s (32bit) to 1066MB/s (64bit) since 6-7 years ago or so.
For instance here I have a box with PCI, Giga Ethernet and a software RAID5 ; reading from the RAID5 goes to about 110 MB/s (actual disk bandwidth is closer to 250 but it's wasted) ; however when using the giga ethernet to copy a large file over a LAN, disk and ethernet have to share the PCI bus, so throughput falls to 50 MB/s. Crummy, eh ?
Sounds like a slow Giga Ethernet NIC...
Let me repeat this : at the current state of SATA drives, just TWO of them is enough to saturate a PCI bus. I'm speaking desktop SATA drives, not high-end SCSI ! (which is not necessarily faster for pure throughput anyway). Adding more drives will help random reads/writes but do nothing for throughput since the tiny PCI pipe is choking.
In my experience, SATA drives are very slow for typical database work (which is heavy on random writes). They often have very slow access times, bad or missing NCQ implementation (controllers / SANs as well) and while I am not very familiar with the protocol differences, they seem to add a hell of a lot more latency than even old U320 SCSI drives.
Sequential transfer performance is a nice indicator, but not very useful, since most serious RAID arrays will have bottlenecks other than the theoretical cumulated transfer rate of all the drives (from controller cache speed to SCSI bus to fibre channel). Thus, lower sequential transfer rate and lower access times scale much better.
Whether a SAN or just an external enclosure is 12disk enough to substain 5K inserts/updates per second on rows in the 30 to 90bytes territory? At 5K/second inserting/updating 100 Million records would take 5.5 hours. That is fairly reasonable if we can achieve. Faster would be better, but it depends on what it would cost to achieve.
5K/s inserts (with no indexes) are easy with PostgreSQL and typical (current) hardware. We are copying about 175K rows/s with our current server (Quad core Xeon 2.93GHz, lots of RAM, meagre performance SATA SAN with RAID-5 but 2GB writeback cache). Rows are around 570b each on average. Performance is CPU-bound with a typical number of indexes on the table and much lower than 175K/s though, for single row updates we get about 9K/s per thread (=5.6MB/s) and that's 100% CPU-bound on the server - if we had to max this out, we'd thus use several clients in parallel and/or collect inserts in text files and make bulk updates using COPY. The slow SAN isn't a problem now.
Our SATA SAN suffers greatly when reads are interspersed with writes, for that you want more spindles and faster disks.
To the OP I have 1 hearty recommendation: if you are using the RAID-functionality of the 2120, get rid of it. If you can wipe the disks, try using Linux software-RAID (yes, it's an admin's nightmare etc. but should give much better performance even though the 2120's plain SCSI won't be hot either) and then start tuning your PostgreSQL installation (there's much to gain here). Your setup looks decent otherwise for what you are trying to do (but you need a fast CPU) and your cheapest upgrade path would be a decent RAID controller or at least a decent non-RAID SCSI controller for software-RAID (at least 2 ports for 12 disks), although the plain PCI market is dead.
-mjy
