Re: Rather large LA

Поиск
Список
Период
Сортировка
От Andy Colson
Тема Re: Rather large LA
Дата
Msg-id 4E64FD13.3030600@squeakycode.net
обсуждение исходный текст
Ответ на Re: Rather large LA  (Richard Shaw <richard@aggress.net>)
Список pgsql-performance
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




В списке pgsql-performance по дате отправления:

Предыдущее
От: Gerhard Wohlgenannt
Дата:
Сообщение: Re: Sudden drop in DBb performance
Следующее
От: Andres Freund
Дата:
Сообщение: Re: Rather large LA