Re: Postgres performance problem

Bill Moran escribió:
> In response to Chris Mair <chris(at)1006(dot)org>:
>
>>> Hi,
>>>
>>> Note: I have already vacumm full. It does not solve the problem.
>
> To jump in here in Chris' defense, regular vacuum is not at all the same
> as vacuum full. Periodic vacuum is _much_ preferable to an occasional
> vacuum full.
>
> The output of vacuum verbose would have useful information ... are you
> exceeding your FSM limits?

I think its ok. There is not warning messages on vacuum verbose in the
last year. (I save all logs)

>
> Try a reindex on the database. There may be some obscure corner
> cases where reindex makes a notable improvement in performance.

I do it all days. (i know it is not necessary all days, but I should
avoid problems)

>
>>> I have a postgres 8.1 database. In the last days I have half traffic
>>> than 4 weeks ago, and resources usage is twice. The resource monitor
>>> graphs also shows hight peaks (usually there is not peaks)
>
> Resource monitor graphs? That statement means nothing to me, therefore
> I don't know if the information they're providing is useful or accurate,
> or even _what_ it is. What, exactly, are these graphs monitoring?

I should have explain that. I use "sar" command, I get 1 minute per 5
minutes, and I show the result in a graph. I have been using this one
year, and use to work very well due traffic / resources usage . In last
days started to show thinks that should not.

By the way, the server is two intel dual processor 4Gb ram. It is only
database server.

>
> You might want to provide your postgresql.conf.
>

I have tested these values. It makes sqls faster. I didn't change it in
the last 10 months, The values I changed are:

max_connections = 500
autovacuum = off
shared_buffers = 24576
work_mem = 3072
maintenance_work_mem = 65536
wal_buffers = 1024
checkpoint_segments = 12
checkpoint_warning = 30
effective_cache_size = 225000
random_page_cost = 2
log_min_duration_statement = 1000

> Have you considered the possibility that the database has simply got more
> records and therefore access takes more IO and CPU?

Not possible. All database is in RAM memory. iostat is quiet. And if
that was the problem, it should be in another way. Not in few weeks with
half traffic. (I also have checked a possible attack, automatic "sing
up" or thinks like that ... nothing found)

By other way, there is not "slow sqls". (ok, there is a few slow sqls
but are known slow sqls)

I saw once a postgres sql server 8.1 that had poor performance with sql
s that involve one table because someone change a column data type and
seems it didn't work well. There is not that kind of changes in my
database for 2 months at least, but ... is there any chance that
database is being corrupted? Maybe dump database , delete it and restore
it again may solve the problem ?

>
>>> The performarce is getting poor with the time.
>>>
>>> Im not able to find the problem, seems there is not slow querys ( I have
>>> log_min_duration_statement = 5000 right now, tomorrow I ll decrease it )
>>>
>>> Server is HP, and seems there is not hardware problems detected.
>>>
>>> Any ideas to debug it?
>> Hi,
>>
>> first of all: let us know the exact version of PG and the OS.
>>
>> If performance is getting worse, there ususally is some bloat
>> envolved. Not vacuuming aggressivly enough, might be the most
>> common cause. Do you autovacuum or vacuum manually?
>> Tell us more...
>>
>>
>> Bye,
>> Chris.
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>>
>>
>>
>>
>
>