Resurrecting this long-dormant thread... On Oct 14, 2011, at 6:41 AM, Arjen van der Meijden wrote:
> On 14-10-2011 10:23, CSS wrote: >> -I'm calling our combined databases at 133GB "small", fair >> assumption? -Is there any chance that a server with dual quad core >> xeons, 32GB RAM, and 2 or 4 SSDs (assume mirrored) could be slower >> than the 4 old servers described above? I'm beating those on raw >> cpu, quadrupling the amount of RAM (and consolidating said RAM), >> and going from disks that top out at 4x300 IOPS with SSDs that >> conservatively should provide 2000 IOPS. > > Whether 133GB is small or not probably mostly depends on how much of it is > actually touched during use. But I'd agree that it isn't a terribly large > database, I'd guess a few simple SSDs would be plenty to achieve 2000 IOPs. > For lineair writes, they're still not really faster than normal disks, but if > that's combined with random access (either read or write) you ought to be ok. > We went from 15x 15k sas-disks to 6x ssd several years back in our MySQL-box, > but since we also increased the ram from 16GB to 72GB, the io-load dropped so > much the ssd's are normally only lightly loaded... Thanks for your input on this. It's taken some time, but I do finally have some hardware on hand (http://imgur.com/LEC5I) and as more trickles in over the coming days, I'll be putting together our first SSD-based postgres box. I have much testing to do, and I'm going to have some questions regarding that subject in another thread. > Btw, the 5500 and 5600 Xeons are normally more efficient with a multiple of 6 > ram-modules, so you may want to have a look at 24GB (6x4), 36GB (6x4+6x2) or > 48GB (12x4 or 6x8) RAM. Thanks - I really had a hard time wrapping my head around the rules on populating the banks. If I understand it correctly, this is due to the memory controller moving from the south(?)bridge to being integrated in the CPU. > Given the historical questions on the list, there is always a risk of getting > slower queries with hardware that should be much faster. For instance, the > huge increase in RAM may trigger a less efficient query-plan. Or the disks > abide by the flush-policies more correctly. > Assuming the queries are still getting good plans and there are no such > special differences, I'd agree with the assumption that its a win on every > count. > Or your update to a newer OS and PostgreSQL may trigger some worse query plan > or hardware-usage. That's an interesting point, I'd not even considered that. Is there any sort of simple documentation on the query planner that might cover how things like increased RAM could impact how a query is executed? >> -Should I even be looking at the option of ZFS on SATA or low-end >> SAS drives and ZIL and L2ARC on SSDs? Initially this intrigued me, >> but I can't quite get my head around how the SSD-based ZIL can deal >> with flushing the metadata out when the whole system is under any >> sort of extreme write-heavy load - I mean if the ZIL is absorbing >> 2000 IOPS of metadata writes, at some point it has to get full as >> it's trying to flush this data to much slower spinning drives. > > A fail-safe set-up with SSD's in ZFS assumes at least 3 in total, i.e. a pair > of SSD's for ZIL and as many as you want for L2ARC. Given your database size, > 4x160GB SSD (in "raid10") or 2x 300GB should yield plenty of space. So given > the same choice, I wouldn't bother with a set of large capacity sata disks > and ZIL/L2ARC-SSD's, I'd just go with 4x160GB or 2x300GB SSD's. Well, I've bought 4x160GB, so that's what I'll use. I will still do some tests with two SATA drives plus ZIL, just to see what happens. > >> -Should my standby box be the same configuration or should I look >> at actual spinning disks on that? How rough is replication on the >> underlying storage? Would the total data written on the slave be >> less or equal to the master? > > How bad is it for you if the performance of your database potentially drops a > fair bit when your slave becomes the master? If you have a read-mostly > database, you may not even need SSD's in your master-db (given your amount of > RAM). But honestly, I don't know the answer to this question :) It's complicated - during the day we're mostly looking at very scattered reads and writes, probably a bit biased towards writes. But each evening we kick off a number of jobs to pre-generate stats for more complex queries... If the job could still complete in 6-8 hours, we'd probably be OK, but if it starts clogging up our normal queries during the day, that would be a problem. Thanks again for your input! Charles > > Good luck with your choices, > Best regards, > > Arjen > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
