Thread
-
Tuning: Taking advantage of 2GB RAM
brianb-pggeneral@edsamail.com — 2000-07-26T01:40:32Z
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
-
Is Pg 7.0.x's Locking Mechanism BROKEN?
Fabrice Scemama <fabrice@scemama.org> — 2000-07-26T14:01:18Z
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
-
Re: Is Pg 7.0.x's Locking Mechanism BROKEN?
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-26T14:46:27Z
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
-
Re: Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?
Jan Wieck <janwieck@t-online.de> — 2000-07-26T19:51:34Z
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 # -
Re: [GENERAL] Is Pg 7.0.x's Locking Mechanism BROKEN?
frank <f.callaghan@ieee.org> — 2000-07-27T06:39:54Z
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. -
Re: Is Pg 7.0.x's Locking Mechanism BROKEN?
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-27T20:23:14Z
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
-
Re: Is Pg 7.0.x's Locking Mechanism BROKEN?
frank <f.callaghan@ieee.org> — 2000-07-28T02:10:43Z
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.