Re: SSD options, small database, ZFS - Mailing list pgsql-performance

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
thatit 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
from16GB 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
postgresbox.  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
lookat 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
shouldbe much faster. For instance, the huge increase in RAM may trigger a less efficient query-plan. Or the disks
abideby 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
assumptionthat 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
plannerthat 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
wantfor L2ARC. Given your database size, 4x160GB SSD (in "raid10") or 2x 300GB should yield plenty of space. So given
thesame choice, I wouldn't bother with a set of large capacity sata disks and ZIL/L2ARC-SSD's, I'd just go with 4x160GB
or2x300GB 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
seewhat 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).
Buthonestly, 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
towardswrites.  But each evening we kick off a number of jobs to pre-generate stats for more complex queries...  If the
jobcould 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