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

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