From: Andy Colson <andy@squeakycode.net>
To: Richard Shaw <richard@aggress.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Rather large LA
Date: Mon, 05 Sep 2011 11:47:15 -0500
Message-ID: <4E64FD13.3030600@squeakycode.net> (raw)
In-Reply-To: <97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
<4E64D0FE.6020704@squeakycode.net>
<97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, richard@aggress.net
Subject: Re: Rather large LA
In-Reply-To: <4E64FD13.3030600@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox