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...
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.
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.
-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.
-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 :)
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
