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
turnedoff to gauge any real world performance increase, there is battery backup on the raid card providing some level
ofresilience.
>
> 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
canrule 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
addingmore 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?
Ordoes 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
youdont say "OMG, CPU is pegged!" so I assume its not CPU bound, I wonder if there are so many processes fighting for
resourcesthey 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
