
I have Postgres 6.5 running on a 4-cpu Linux server with 2GB RAM, however
it does not seem to be taking full advantage of the available memory, even
during large queries. How do I tell Postgres that more memory is available?

Brian

P.S. Yes, I know we should upgrade to 7, but it looks like we're moving
this application to Oracle soon.
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/  AIM: bbaquiran 
Work: +63(2)7182222       Home: +63(2) 9227123

Wed, 26 Jul 2000 01:40:32 GMT
brianb-pggeneral@edsamail.com
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

Wed, 26 Jul 2000 16:01:18 +0200
Fabrice Scemama <fabrice@scemama.org>
Is Pg 7.0.x's Locking Mechanism BROKEN?
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

Wed, 26 Jul 2000 10:46:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is Pg 7.0.x's Locking Mechanism BROKEN? 
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 #



Wed, 26 Jul 2000 21:51:34 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?
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.



Thu, 27 Jul 2000 02:39:54 -0400
frank <f.callaghan@ieee.org>
Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?
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

Thu, 27 Jul 2000 16:23:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Is Pg 7.0.x's Locking Mechanism BROKEN? 
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.



Thu, 27 Jul 2000 22:10:43 -0400
frank <f.callaghan@ieee.org>
Re: Is Pg 7.0.x's Locking Mechanism BROKEN?