Message-ID: <4E3BFC66.4040501@peak6.com>
Date: Fri, 5 Aug 2011 09:21:26 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: <pgsql-performance@postgresql.org>, Claire Chang <yenhsiac@yahoo.com>
Subject: Re: Postgres 8.4 memory related parameters
References: <1312489633.71838.YahooMailClassic@web31912.mail.mud.yahoo.com>
	<4E3ABCFB020000250003FAF4@gw.wicourts.gov> <4E3B08D3.606@peak6.com>
	<4E3ACA8C020000250003FB05@gw.wicourts.gov>
	<4E3B13E8.7050002@peak6.com>
	<4E3ACEB4020000250003FB10@gw.wicourts.gov>
	<4E3B15EB.1020603@peak6.com>
	<4E3BB14A020000250003FB41@gw.wicourts.gov>
In-Reply-To: <4E3BB14A020000250003FB41@gw.wicourts.gov>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 08/05/2011 09:00 AM, Kevin Grittner wrote:

> optimal pool size = ((2 * actual core count) + effective spindle
> count)

How does that work? If your database fits in memory, your optimal TPS is 
only constrained by CPU. Any fetches from disk reduce your throughput 
from IO Waits. How do you account for SSDs/PCIe cards which act as an 
effective spindle multiplier?

I've seen Java apps that, through the use of several systems using Java 
Hibernate pool sharing, are not compatible with connection poolers such 
as PGBouncer. As such, they had 50x CPU count and still managed 
12,000TPS because everything in use was cached. Throw a disk seek or two 
in there, and it drops down to 2000 or less. Throw in a PCIe card, and 
pure streams of "disk" reads remain at 12,000TPS.

It just seems a little counter-intuitive. I totally agree that it's not 
optimal to have connections higher than effective threads, but *adding* 
spindles? I'd be more inclined to believe this:

optimal pool size = 3*cores - cores/spindles

Then, as your spindles increase, you're subtracting less and less until 
you reach optimal 3x.

One disk, but on a 4-cpu system?

12 - 4 = 8. So you'd have the classic 2x cores.

On a RAID 1+0 with 4 disk pairs (still 4 cpu)?

12 - 1 = 11.

On a giant SAN with couple dozen disks or a PCIe card that tests an 
order of magnitude faster than a 6-disk RAID?

12 - [small fraction] = 12

It still fits your 3x rule, but seems to actually account for the fact 
disks suck. :p

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email