Message-ID: <4E64FD13.3030600@squeakycode.net>
Date: Mon, 05 Sep 2011 11:47:15 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.18) Gecko/20110707 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: Richard Shaw <richard@aggress.net>
CC: pgsql-performance@postgresql.org
Subject: Re: Rather large LA
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
	<4E64D0FE.6020704@squeakycode.net>
	<97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
In-Reply-To: <97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/05/2011 08:57 AM, Richard Shaw wrote:
>
> Hi Andy,
>
> It's not a new issue no, It's a legacy system that is in no way ideal but is also not in a position to be overhauled.  Indexes are correct, tables are up to 25 million rows.
>
> On startup, it hits CPU more than IO, I'll provide some additional stats after I restart it tonight.
>
> Server logs have been reviewed and where possible, slow queries have been fixed.
>
> Autovacuum has been disabled and set to run manually via cron during a quiet period and fsync has recently been turned off to gauge any real world performance increase, there is battery backup on the raid card providing some level of resilience.
>
> Thanks
>
> Richard
>
>

So I'm guessing that setting fsync off did not help your performance problems.  And you say CPU is high, so I think we can rule out disk IO problems.

> possibly installing more RAM as the most used db @ 67GB might appreciate it

That would only be if every row of that 67 gig is being used.  If its history stuff that never get's looked up, then adding more ram wont help because none of that data is being loaded anyway.  Out of that 67 Gig, what is the working size?  (Not really a number you can look up, I'm looking for more of an empirical little/some/lots/most).

pgpool:

max_client_conn = 4096
reserve_pool_size = 800

I've not used pgpool, but these seem really high.  Does that mean pgpool will create 4K connectsions to the backend?  Or does it mean it'll allow 4K connections to pgpool but only 800 connections to the backend.

I wonder...When you startup, if you watch vmstat for a bit, do you have tons of context switches?  If its not IO, and you dont say "OMG, CPU is pegged!" so I assume its not CPU bound, I wonder if there are so many processes fighting for resources they are stepping on each other.

When you get up and running (and its slow), what does this display:

ps ax|grep postgr|wc --lines

That and a minute of 'vmstat 2' would be neet to see as well.

-Andy