Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1084)
Subject: Re: SSD options, small database, ZFS
From: CSS <css@morefoo.com>
In-Reply-To: <4E9811DE.6030601@tweakers.net>
Date: Thu, 17 Nov 2011 22:44:46 -0500
Content-Transfer-Encoding: quoted-printable
Message-Id: <F60C018B-9DCD-461A-A098-D9657A536698@morefoo.com>
References: <0464974E-CF82-41D7-A266-5BAE3CBCEC51@morefoo.com>
	<4E9811DE.6030601@tweakers.net>
To: pgsql-performance@postgresql.org

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.
>=20
> 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.
>=20
> 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.

>=20
>> -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?
>=20
> 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

>=20
> Good luck with your choices,
> Best regards,
>=20
> Arjen
>=20
> --=20
> Sent via pgsql-performance mailing list =
(pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance