Tuning: Taking advantage of 2GB RAM

Hello;

we've been using Postgres for nearly 2 years now, and have
followed updgrades, from 6.4.2 to 7.0.2. Our applications
are mainly shell scripts, and CGI. All is done in perl.
We have long, heavy, and simultaneous transactions.

With 7.0.x versions, we've come across a problem, new for us:
very often (2 or 3 times a day), there are on our Pg machine
a dozen of 'update waiting', and our perl scripts are waiting
too to begin a transaction. So many postgres are running, than
no other one can be added; only SELECTs continue to be possible.
We finally have to restart Postgres; but even if we kill
postmaster's process, there still are postgres processes runnings,
and they have to be killed too! Definitely not working.

We've been looking for solutions for a few weeks, and even
decided to move Pg from a satured PIII 650 to a DEC Alpha 500mhz,
where Postgres is the only application to run.
So far, we haven't found any workaround, and the hangings have
been quite nasty for our project.

Yesterday, I moved back our DB to Pg 6.5.3, and yes, now
everything works fine again (it's slower, but it works).
And vacuums don't hang the postmaster any more.

So, I've got 2 questions:
- Am I the only one here to have such problems with 7.0.2?
- Is 7.0.2 broken?

Regards
Fabrice Scemama

Fabrice Scemama <fabrice@scemama.org> writes:

With 7.0.x versions, we've come across a problem, new for us:
very often (2 or 3 times a day), there are on our Pg machine
a dozen of 'update waiting', and our perl scripts are waiting
too to begin a transaction.

Can you list exactly what *all* the backends are doing when this
happens? It might help to run the postmaster with -d2 so that
the postmaster log contains a trace of all queries executed.

- Am I the only one here to have such problems with 7.0.2?

Haven't seen any other such reports.

regards, tom lane

frank wrote:

Thanks Fabrice, that will help a lot.

In my applications the conflict was not a direct table conflict e.g.
USER1 locks Table1 record that references Table2 via foreign key with a
cascade update/delete enforced then
USER2 tried to lock Table2 for update on the referenced record - result both
users locked !

Is this the same scenario in your case ?
perhaps a simple test db could used to resolve if this is the issue !

Looks like a deadlock situation not seen by the deadlock
detection code. Unfortunately I'm not able to reproduce a
lockup with a simple test DB. Could you post a simple
(trans1 does ..., trans2 does ...) sample so we coule
reproduce such a lockup?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Fabrice Scemama wrote:

Hello;

we've been using Postgres for nearly 2 years now, and have
followed updgrades, from 6.4.2 to 7.0.2. Our applications
are mainly shell scripts, and CGI. All is done in perl.
We have long, heavy, and simultaneous transactions.

With 7.0.x versions, we've come across a problem, new for us:
very often (2 or 3 times a day), there are on our Pg machine
a dozen of 'update waiting', and our perl scripts are waiting
too to begin a transaction. So many postgres are running, than
no other one can be added; only SELECTs continue to be possible.
We finally have to restart Postgres; but even if we kill
postmaster's process, there still are postgres processes runnings,
and they have to be killed too! Definitely not working.

We've been looking for solutions for a few weeks, and even
decided to move Pg from a satured PIII 650 to a DEC Alpha 500mhz,
where Postgres is the only application to run.
So far, we haven't found any workaround, and the hangings have
been quite nasty for our project.

Yesterday, I moved back our DB to Pg 6.5.3, and yes, now
everything works fine again (it's slower, but it works).
And vacuums don't hang the postmaster any more.

So, I've got 2 questions:
- Am I the only one here to have such problems with 7.0.2?
- Is 7.0.2 broken?

Regards
Fabrice Scemama

I saw this problem just last night, exactly the same, I couldn't vacuum,

I shutdown the server but there
where still postmasters running - I killed them, restarted but the locks

where still in place but, in my
case even if I selected the affected record psql would hang. The only
way I managed to recover was to
shutdown the server completely - pgdump the database - drop it -
createit - pgload it, now its working again.
I too never had this problem with 6.5.3, this was the second time for me

the first time I put it down to a 'c'
function I was playing with but I was not even logged it this time.
It seem that two people accessed the same record that started the
problem !

Regards,
Frank.

Thanks Fabrice, that will help a lot.

In my applications the conflict was not a direct table conflict e.g.
USER1 locks Table1 record that references Table2 via foreign key with a
cascade update/delete enforced then
USER2 tried to lock Table2 for update on the referenced record - result both
users locked !

Is this the same scenario in your case ?
perhaps a simple test db could used to resolve if this is the issue !

Regards,
Frank.

Frank and Fabrice,

If you aren't having any luck generating a reproducible example of this
problem, you might try recompiling the backend with LOCK_DEBUG defined
in src/include/config.h --- or just do
gmake clean
gmake PROFILE=-DLOCK_DEBUG all
This should produce pretty voluminous quantities of info in the
postmaster's stdout/stderr log. Run the postmaster with -d2 so we
can see the related queries too, and then maybe the log will have enough
info to tell something useful the next time you see it happen.

regards, tom lane

Jan Wieck wrote:

frank wrote:

Thanks Fabrice, that will help a lot.

In my applications the conflict was not a direct table conflict e.g.
USER1 locks Table1 record that references Table2 via foreign key with a
cascade update/delete enforced then
USER2 tried to lock Table2 for update on the referenced record - result both
users locked !

Is this the same scenario in your case ?
perhaps a simple test db could used to resolve if this is the issue !

Looks like a deadlock situation not seen by the deadlock
detection code. Unfortunately I'm not able to reproduce a
lockup with a simple test DB. Could you post a simple
(trans1 does ..., trans2 does ...) sample so we coule
reproduce such a lockup?

Hi Jan,

I shall try to reproduce the lockup with -d2 debug level but, I am not sure this
is the only
lockup problem as it seems far to frequent twice today already and thats in only
4 hours of use :(

Q1. When a system task on a client gets killed how long is it before the database
releases it's record locks ?

Q2. When the Postgres server is shutdown and re started shouldn't all the record
locks have been removed ?

This situation seems to be getting worse, now I am scared to leave the building.

Regards,
Frank.