SEO

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



Hi,

Note: I have already vacumm full. It does not solve the problem.

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)

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?



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG0rh5Io1XmbAXRboRAuaTAJ4tDVHUMN5YRBdWxT//kPAfBvYqRACgvLst
rJF3dmxzWHDOWB8yQwTyvpw=
=2ic9
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> Hi,
>
> Note: I have already vacumm full. It does not solve the problem.
>
> 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)
>
> 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

SO: CentOS release 4.3 (Final) (kernel: 2.6.9-34.0.1.ELsmp)
Postgres: 8.1.3

I had some problems before with autovacuum. So, Each day I crontab execute:

vacuumdb -f -v --analyze
reindex database vacadb

I saw logs (the output of vacuum and reindex) and there is no errors.

If u need more info, I ll be pleased to tell it here ...


Chris Mair escribió:
>> Hi,
>>
>> Note: I have already vacumm full. It does not solve the problem.
>>
>> 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)
>>
>> 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
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

In response to Chris Mair <chris@1006.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?

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

> > 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?

You might want to provide your postgresql.conf.

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

> > 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
>
>
>
>
>
>


--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

************************************************** **************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
************************************************** **************

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Just a random thought/question...
Are you running else on the machine? When you say "resource usage", do
you mean hd space, memory, processor, ???
What are your values in top?
More info...
Cheers
Anton


On 27/08/2007, Bill Moran <wmoran@collaborativefusion.com> wrote:
> In response to Chris Mair <chris@1006.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?
>
> Try a reindex on the database. There may be some obscure corner
> cases where reindex makes a notable improvement in performance.
>
> > > 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?
>
> You might want to provide your postgresql.conf.
>
> Have you considered the possibility that the database has simply got more
> records and therefore access takes more IO and CPU?
>
> > > 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
> >
> >
> >
> >
> >
> >
>
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> ************************************************** **************
> IMPORTANT: This message contains confidential information and is
> intended only for the individual named. If the reader of this
> message is not an intended recipient (or the individual
> responsible for the delivery of this message to an intended
> recipient), please be advised that any re-use, dissemination,
> distribution or copying of this message is prohibited. Please
> notify the sender immediately by e-mail if you have received
> this e-mail by mistake and delete this e-mail from your system.
> E-mail transmission cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses. The
> sender therefore does not accept liability for any errors or
> omissions in the contents of this message, which arise as a
> result of e-mail transmission.
> ************************************************** **************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>


--
echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc
This will help you for 99.9% of your problems ...

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Bill Moran escribió:
> In response to Chris Mair <chris@1006.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
>>
>>
>>
>>
>>
>>
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



Hi ...

Seems its solved. But the problem is not found.

As you may know, I do a vacuum full and a reindex database each day. I
have logs that confirm that its done and I can check that everything was
fine.

So, this morning, I stopped the website, I stopped database, started it
again. (I was around 200 days without restarting), then I vacuum
database and reindex it (Same command as everyday) . Restart again, and
run again the website.

Now seems its working fine. But I really does not know where is the
problem. Seems vacuum its not working fine? Maybe database should need
a restart? I really don't know.

Does someone had a similar problem?

Thanks in advance,
Ruben Rubio


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wD LnIW2SfQCfV+Hj
e8PurQb2TOSYDPW545AJ83c=
=dQgM
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote:
> As you may know, I do a vacuum full and a reindex database each day. I
> have logs that confirm that its done and I can check that everything was
> fine.
>
> So, this morning, I stopped the website, I stopped database, started it
> again. (I was around 200 days without restarting), then I vacuum
> database and reindex it (Same command as everyday) . Restart again, and
> run again the website.
>
> Now seems its working fine. But I really does not know where is the
> problem. Seems vacuum its not working fine? Maybe database should need
> a restart? I really don't know.

No, it sounds to me like you just weren't vacuuming aggressively enough
to keep up with demand.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFG1v8HdO30qud8SkgRAqlVAKDbNKRoyn1QPl0BWdVqst QWQPfrfACgm719
+VaHgg9JkPIw27EULubqM7Y=
=QYaL
-----END PGP SIGNATURE-----

Perhaps you had a long-running transaction open (probably a buggy or
hung application) that was preventing dead rows from being cleaned up.
Restarting PG closed the offending connection and rolled back the
transaction, which allowed vacuum to clean up all the dead rows.

If you're not running regular VACUUMs at all but are instead exclusively
running VACUUM FULL, then I don't think you would see warnings about
running out of fsm enties, which would explain why you did not notice
the bloat. I haven't confirmed that though, so I might be wrong.

-- Mark Lewis

On Thu, 2007-08-30 at 11:50 +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> Hi ...
>
> Seems its solved. But the problem is not found.
>
> As you may know, I do a vacuum full and a reindex database each day. I
> have logs that confirm that its done and I can check that everything was
> fine.
>
> So, this morning, I stopped the website, I stopped database, started it
> again. (I was around 200 days without restarting), then I vacuum
> database and reindex it (Same command as everyday) . Restart again, and
> run again the website.
>
> Now seems its working fine. But I really does not know where is the
> problem. Seems vacuum its not working fine? Maybe database should need
> a restart? I really don't know.
>
> Does someone had a similar problem?
>
> Thanks in advance,
> Ruben Rubio
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFG1pLMIo1XmbAXRboRAqgQAKCkWcZYE8RDppEVI485wD LnIW2SfQCfV+Hj
> e8PurQb2TOSYDPW545AJ83c=
> =dQgM
> -----END PGP SIGNATURE-----
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Decibel! wrote:
> On Thu, Aug 30, 2007 at 11:50:04AM +0200, Ruben Rubio wrote:
>> As you may know, I do a vacuum full and a reindex database each day. I
>> have logs that confirm that its done and I can check that everything was
>> fine.
>>
>> So, this morning, I stopped the website, I stopped database, started it
>> again. (I was around 200 days without restarting), then I vacuum
>> database and reindex it (Same command as everyday) . Restart again, and
>> run again the website.
>>
>> Now seems its working fine. But I really does not know where is the
>> problem. Seems vacuum its not working fine? Maybe database should need
>> a restart? I really don't know.
>
> No, it sounds to me like you just weren't vacuuming aggressively enough
> to keep up with demand.

Actually , I think it sounds like a stray long-lived transaction.

Ruben - vacuum can't recover rows if another transaction might be able
to see them. So, if you have a connection that issues BEGIN and sits
there for 200 days you can end up with a lot of bloat in your database.

Now, there's no way to prove that since you've restarted the
database-server, but keep an eye on it.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings