Thread: Intel SSD
Hi, I consider buying one of these Intel SSDs for my Database (MLC). I have a few questions regarding this, maybe some of you already have some experience with that. - which files should not be put onto the SSD (pg_clog ? pg_xlog ? ) to reduce wear (which may be an issue with the MLC variant) - is there really a significant performance gain for random reads on a 30GB Database ? -are there guides for tuning pg on SSD available ? (I guess some of the Planner Cost Constants should be quite differend on an SSD to achive optimal performance, as random_page_cost and seq_page_cost may be quite identical ) currently I have a lot of small random reads/writes causing heavy iowait. thanks /sebastian
Sebastian Böhm wrote: > I consider buying one of these Intel SSDs for my Database (MLC). > ..... > currently I have a lot of small random reads/writes causing heavy iowait. how many r/s and w/s in `iostat -x 5` while your database is humming are you seeing now? Thats the single key performance factor in disk bound database IO. Caches can reduce the r/s requirements but the writes have to go to disk if you want data integrity. ok, a raid controller with battery back cache configured for writeback can accelerate the writes by a lot too. a desktop 7200 rpm SATA drive will saturate at under 100 IO ops/sec max with the typical 8kbyte random blocks of a database, while a 15k server drive can sustain 200 io/sec, and a raid 1+0 can hit several times more (I've seen sustained 200/s on each of 4 15k spindles) while still using normal hard disks. Those intel SSDs are supposed to be good for 1000 or so IOP/sec if I remember correctly... (of course, in a mirror, you have to half the aggregated writes for the true number of useful random writes)
On Tue, Feb 10, 2009 at 11:55 PM, John R Pierce <pierce@hogranch.com> wrote: > Sebastian Böhm wrote: >> >> I consider buying one of these Intel SSDs for my Database (MLC). >> ..... >> currently I have a lot of small random reads/writes causing heavy iowait. > > how many r/s and w/s in `iostat -x 5` while your database is humming are you > seeing now? Thats the single key performance factor in disk bound > database IO. Caches can reduce the r/s requirements but the writes have to > go to disk if you want data integrity. ok, a raid controller with battery > back cache configured for writeback can accelerate the writes by a lot too. > > a desktop 7200 rpm SATA drive will saturate at under 100 IO ops/sec max with > the typical 8kbyte random blocks of a database, while a 15k server drive can > sustain 200 io/sec, and a raid 1+0 can hit several times more (I've seen > sustained 200/s on each of 4 15k spindles) while still using normal hard > disks. Those intel SSDs are supposed to be good for 1000 or so IOP/sec if > I remember correctly... (of course, in a mirror, you have to half the > aggregated writes for the true number of useful random writes) Also keep in mind that a fast battery backed caching RAID controller can get several thousand transactions per second on a large number of spindles by re-ordering writes and such. I can get anywhere from 1800 to 3400 tps on 12 15k disks in RAID-10 (i.e. 6 disk RAID-0 performance) and that's sustained over a very long test, usually 30 minutes to a few hours with pgbench. The size of the test set and server config parameters make for the variations from 1800 to 3400. I think the real advantage to the SSDs is that they can get this kind of performance, or even more from a rather small software RAID-10 of 4 disks. Instead of spending $800 on a RAID controller, use it towards buying more SSDs. As the price of SSDs falls much faster than the cost of high end RAID controllers, they'll reach a point where any old 4x2.5" server can walk right into your data center and take over the job of a much larger $10,000+ server. I'd really like to see how 16 or more of them in a big RAID-10 both on a RAID controller and maybe in jbod mode ran. Anyone at some big corporation got some time to benchmark the next great thing? I'd take a day off just to come watch that.
On Tue, Feb 10, 2009 at 9:24 PM, Sebastian Böhm <seb@exse.net> wrote: > I consider buying one of these Intel SSDs for my Database (MLC). > > I have a few questions regarding this, maybe some of you already have some > experience with that. > > - which files should not be put onto the SSD (pg_clog ? pg_xlog ? ) to > reduce wear (which may be an issue with the MLC variant) just shooting from the hip here, but with the MLC drive I'd leave xlog on the hard drive and put everything else on the SSD. > - is there really a significant performance gain for random reads on a 30GB > Database ? Yes. read latency is measured in microseconds not milliseconds. > -are there guides for tuning pg on SSD available ? (I guess some of the > Planner Cost Constants should be quite differend on an SSD to achive optimal > performance, as random_page_cost and seq_page_cost may be quite identical ) > currently I have a lot of small random reads/writes causing heavy iowait. writes could still continue to be a problem. You are taking a bit of a gamble going with the MLC drive...lifespan could be an issue and random writes are not significantly improved vs. traditional hard drive. merlin