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


Hi,

I having the same problem I told here a few weeks before. Database is
using too much resources again.

I do a vacumm full each day, but seems it is not working. I am preparing
an update to postgres 8.2.4 (actually I am using at 8.1.3, and tests for
update will need several days)

Last time I had this problem i solved it stopping website, restarting
database, vacuumm it, run again website. But I guess this is going to
happen again.

I would like to detect and solve the problem. Any ideas to detect it?

Thanks in advance,



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

iD8DBQFG5jbLIo1XmbAXRboRArcpAJ0YvoCT6KWv2fafVAtapu 6nwFmKoACcD0uA
zFTx9Wq+2NSxijIf/R8E5f8=
=u0k5
-----END PGP SIGNATURE-----


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

> Last time I had this problem i solved it stopping website, restarting
> database, vacuumm it, run again website. But I guess this is going to
> happen again.
>
> I would like to detect and solve the problem. Any ideas to detect it?

Do you have very long transactions? Maybe some client that is connected
all the time that is idle in transaction?

/Dennis


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

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

db@zigo.dhs.org escribió:
>> Last time I had this problem i solved it stopping website, restarting
>> database, vacuumm it, run again website. But I guess this is going to
>> happen again.
>>
>> I would like to detect and solve the problem. Any ideas to detect it?
>
> Do you have very long transactions? Maybe some client that is connected
> all the time that is idle in transaction?

There should not be long transactions. I ll keep an eye on Idle transactions

I m detecting it using:

echo 'SELECT current_query FROM pg_stat_activity;' |
/usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l



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

iD8DBQFG5kiRIo1XmbAXRboRAj3sAKCH21zIhvdvPcmVQG71ow iCye96xwCcDPe0
o/aArJF0JjUnTIFd1sMYD+Y=
=6zyY
-----END PGP SIGNATURE-----


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

On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> db@zigo.dhs.org escribi?:
> >> Last time I had this problem i solved it stopping website, restarting
> >> database, vacuumm it, run again website. But I guess this is going to
> >> happen again.
> >>
> >> I would like to detect and solve the problem. Any ideas to detect it?
> >
> > Do you have very long transactions? Maybe some client that is connected
> > all the time that is idle in transaction?
>
> There should not be long transactions. I ll keep an eye on Idle transactions
>
> I m detecting it using:
>
> echo 'SELECT current_query FROM pg_stat_activity;' |
> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l

If you're using VACUUM FULL, you're doing something wrong. Run lazy
vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
autovac parameters in half), and make sure your FSM is big enough
(periodic vacuumdb -av | tail is an easy way to check that).

Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
easy for them to seriously bloat.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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

iD8DBQFG5xw6dO30qud8SkgRAkvJAJ9ye3n/fo/QLWAVgQtJFs1AZ0plUwCghGnr
nkSUMnsN6WRMerovsDu4j5o=
=l6IL
-----END PGP SIGNATURE-----

Decibel! escribió:
> On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> db@zigo.dhs.org escribi?:
>>>> Last time I had this problem i solved it stopping website, restarting
>>>> database, vacuumm it, run again website. But I guess this is going to
>>>> happen again.
>>>>
>>>> I would like to detect and solve the problem. Any ideas to detect it?
>>> Do you have very long transactions? Maybe some client that is connected
>>> all the time that is idle in transaction?
>> There should not be long transactions. I ll keep an eye on Idle transactions
>>
>> I m detecting it using:
>>
>> echo 'SELECT current_query FROM pg_stat_activity;' |
>> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
>
> If you're using VACUUM FULL, you're doing something wrong.

I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
check if vacuum is done properly.(it is)

Run lazy
> vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
> autovac parameters in half), and make sure your FSM is big enough

I checked that there is no warnings about FSM in logs. (also in logs
from vacuum). Is it reliable?

What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
default autovac parameters?

> (periodic vacuumdb -av | tail is an easy way to check that).

I ll keep an eye on it.

>
> Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> easy for them to seriously bloat.

Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
the output averyday and save it into a log, and I can check that it is
done properly.



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> Decibel! escribió:
> > On Tue, Sep 11, 2007 at 09:49:37AM +0200, Ruben Rubio wrote:
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> db@zigo.dhs.org escribi?:
> >>>> Last time I had this problem i solved it stopping website, restarting
> >>>> database, vacuumm it, run again website. But I guess this is going to
> >>>> happen again.
> >>>>
> >>>> I would like to detect and solve the problem. Any ideas to detect it?
> >>> Do you have very long transactions? Maybe some client that is connected
> >>> all the time that is idle in transaction?
> >> There should not be long transactions. I ll keep an eye on Idle transactions
> >>
> >> I m detecting it using:
> >>
> >> echo 'SELECT current_query FROM pg_stat_activity;' |
> >> /usr/local/pgsql/bin/psql vacadb | grep IDLE | wc -l
> >
> > If you're using VACUUM FULL, you're doing something wrong.
>
> I do a VACUUM FULL VERBOSE ANALYZE each day. I save all logs so I can
> check if vacuum is done properly.(it is)

Then, like Jim said, you're doing it wrong. Regular vacuum full is
like rebuiling a piece of equipment every night when all it needs is
the filter changed.

> Run lazy
> > vacuum frequently enough (better yet, autovacuum, but cut all of 8.1's
> > autovac parameters in half), and make sure your FSM is big enough
>
> I checked that there is no warnings about FSM in logs. (also in logs
> from vacuum). Is it reliable?
>
> What do u mean for "cut all of 8.1's autovac parameters in half" Maybe
> default autovac parameters?

Yep. ( I assume)

> > (periodic vacuumdb -av | tail is an easy way to check that).
>
> I ll keep an eye on it.
>
> >
> > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > easy for them to seriously bloat.
>
> Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> the output averyday and save it into a log, and I can check that it is
> done properly.

Then you're vacuum full is wasted. A reindex accomplishes the same
thing, plus shrinks indexes (vacuum full can bloat indexes).

Just run regular vacuums, preferably by autovacuum, and keep an eye on
the vacuum analyze you run each night to see if your fsm is big
enough.

Occasionally vacuum full is absolutely the right answer. Most the
time it's not.

I'm getting more and more motivated to rewrite the vacuum docs. I
think a rewrite from the ground up might be best... I keep seeing
people doing vacuum full on this list and I'm thinking it's as much
because of the way the docs represent vacuum full as anything. Is
that true for you?

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Scott Marlowe wrote:

>I'm getting more and more motivated to rewrite the vacuum docs. I
>think a rewrite from the ground up might be best... I keep seeing
>people doing vacuum full on this list and I'm thinking it's as much
>because of the way the docs represent vacuum full as anything. Is
>that true for you?
>
>
>

It's true for me.

I turned off autovacuum as I was getting occassional hangs, which I
thought were the result of vacuums (and have signifigantly decreased
since I did that), and went nightly vacuum fulls, and vacuum
full/reindex/cluster on the weekends (which I now realize is redundant).

Brian


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

On 9/12/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:



> > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > > easy for them to seriously bloat.
> >
> > Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> > the output averyday and save it into a log, and I can check that it is
> > done properly.
>
> Then you're vacuum full is wasted. A reindex accomplishes the same
> thing, plus shrinks indexes (vacuum full can bloat indexes).


Aren't you mixing up REINDEX and CLUSTER?

Regards

MP

On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>
>
> On 9/12/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On 9/12/07, ruben@rentalia.com <ruben@rentalia.com> wrote:
>
> > > > Try a REINDEX. VACUUM FULL is especially hard on the indexes, and it's
> > > > easy for them to seriously bloat.
> > >
> > > Reindex is done everyday after VACUUM FULL VERBOSE ANALYZE. I save also
> > > the output averyday and save it into a log, and I can check that it is
> > > done properly.
> >
> > Then you're vacuum full is wasted. A reindex accomplishes the same
> > thing, plus shrinks indexes (vacuum full can bloat indexes).
>
> Aren't you mixing up REINDEX and CLUSTER?

I don't think so. reindex (which runs on tables and indexes, so the
name is a bit confusing, I admit) basically was originally a "repair"
operation that rewrote the whole relation and wasn't completely
transaction safe (way back, 7.2 days or so I think). Due to the
issues with vacuum full bloating indexes, and being slowly replaced by
regular vacuum, reindex received some attention to make it transaction
/ crash safe and has kind of take the place of vacuum full in terms of
"how to fix bloated objects".

cluster, otoh, rewrites the table into index order.

Either one does what a vacuum full did / does, but generally does it better.

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

On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote:
> On 9/12/07, Mikko Partio <mpartio@gmail.com> wrote:
>> …
>> Aren't you mixing up REINDEX and CLUSTER?
>
> …
> Either one does what a vacuum full did / does, but generally does
> it better.

On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE
I'd like to ask if CLUSTER is safe to run on a table that is in
active use.

After updating my maintenance scripts from a VACUUM FULL (add me to
the list) to CLUSTER (which improves performance a lot) I noticed I
was getting "could not open relation …" errors in the log while the
scripts ran so I reverted the change. This was on 8.1.9.

Am I hitting a corner case or is it generally not a good idea to
CLUSTER tables which are being queried? I haven't had problems with
the REINDEX / VACUUM FULL combination while CLUSTER / VACUUM ANALYZE
resulted in errors on the first run.

Can the "could not open relation…" error bring down the whole
database server? I'm really interested in using CLUSTER regularly as
it speeds up my system by a factor of two because of more efficient I/O.

Sincerely,

Frank

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