Thread

1. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-12T15:13:10Z

   Mirko Kaffka <mirko@interface-business.de> writes:
   > We have problems with backend processes that close the channel because of
   > palloc() failures. When an INSERT statement fails, the backend reports an
   > error (e.g. `Cannot insert a duplicate key into a unique index') and
   > allocates a few bytes more memory. The next SQL statement that fails
   > causes the backend to allocate more memory again, etc. until we have no
   > more virtual memory left. Is this a bug?
   
   Yeah, I'd say so --- all the memory used should get freed at transaction
   end, but evidently it isn't happening.
   
   > We are using postgres 6.4.2 on FreeBSD 2.2.8.
   
   I still see it with 6.5-current sources.  Will take a look.
   
   			regards, tom lane
   
   

2. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Jan Wieck <jwieck@debis.com> — 1999-05-12T16:34:16Z

   >
   > Mirko Kaffka <mirko@interface-business.de> writes:
   > > We have problems with backend processes that close the channel because of
   > > palloc() failures. When an INSERT statement fails, the backend reports an
   > > error (e.g. `Cannot insert a duplicate key into a unique index') and
   > > allocates a few bytes more memory. The next SQL statement that fails
   > > causes the backend to allocate more memory again, etc. until we have no
   > > more virtual memory left. Is this a bug?
   >
   > Yeah, I'd say so --- all the memory used should get freed at transaction
   > end, but evidently it isn't happening.
   >
   > > We are using postgres 6.4.2 on FreeBSD 2.2.8.
   >
   > I still see it with 6.5-current sources.  Will take a look.
   
       I  remember  to  have  taken  some  but haven't found all the
       places.  I think there's still something in  tcop  where  the
       querytree list is malloc()'d.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #======================================== jwieck@debis.com (Jan Wieck) #
   
   
   
   

3. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-13T00:33:36Z

   >> Yeah, I'd say so --- all the memory used should get freed at transaction
   >> end, but evidently it isn't happening.
   >> 
   >> I still see it with 6.5-current sources.  Will take a look.
   
   Ah-ha, I think I see it: AtCommit_Memory releases memory in the blank
   portal (by doing EndPortalAllocMode()).  AtAbort_Memory forgets to do so.
   Will commit this fix momentarily.
   
   >     I  remember  to  have  taken  some  but haven't found all the
   >     places.  I think there's still something in  tcop  where  the
   >     querytree list is malloc()'d.
   
   That is a relatively minor leak, compared to leaking *all* memory
   allocated in the failed transaction, which is what it was doing until
   now :-(.  But I think I will fix it anyway ... the code is awfully
   ugly, and it is still a leak.
   
   			regards, tom lane
   
   

4. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Don Baccus <dhogaza@pacifier.com> — 1999-05-13T01:09:10Z

   At 08:33 PM 5/12/99 -0400, Tom Lane wrote:
   
   >That is a relatively minor leak, compared to leaking *all* memory
   >allocated in the failed transaction, which is what it was doing until
   >now :-(.  But I think I will fix it anyway ... the code is awfully
   >ugly, and it is still a leak.
   
   I'm a lurker, a compiler writer who has just begun using
   Postgres as the database engine behind a bird population
   tracking project I'm putting up on the web on my own
   time, on a linux box running AOLServer and, for now at
   least, postgres.
   
   In my researching postgres vs. paying Oracle (which didn't
   seem too bad until I learned about their extra fees for
   web sites and multiple-CPU boxes) vs. mySql etc, the one
   biggest complaint I've run across when talking to people
   running web sites backed by Postgres has been that the
   back end starts dying after weeks ... days ... hours
   depending on the type of site.
   
   On questioning folks, it seemed pretty clear that in 
   some of these cases significant memory leaking was
   causing the system to run out of memory.
   
   And last week I managed to generate long sequences
   of SQL that would eat available memory in about
   15 minutes.  I've been lurking around a couple of
   these postgres lists trying to figure out whether
   or not it was a known problem before making noise
   about it.
   
   So, imagine my pleasure at seeing this short thread
   on the problem and, even better, the solution!
   
   Well, if not the (only) leak, at least one very,
   very serious memory leak.  Just how many kb were
   being leaked for each failed transaction?
   
   I think you may've just slammed a stake through the 
   heart of a very significant bug causing a lot of
   people seemingly unexplainable flakey back-end
   behavior...this fix alone may do a lot to erase
   the impression some have that postgres is not
   reliable enough to support any web site based
   on a large database with lots of transactions.
   
   
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, and other goodies at
     http://donb.photo.net
   
   

5. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-05-13T01:39:56Z

   Don Baccus <dhogaza@pacifier.com> writes:
   > I think you may've just slammed a stake through the 
   > heart of a very significant bug
   
   Thanks for the compliment :-).  You might actually be right;
   this bug could go a long way towards explaining why some people
   find Postgres very reliable and others don't.  The first group's
   apps don't tend to provoke any SQL errors, and/or don't try to
   continue running with the same backend after an error.
   
   > And last week I managed to generate long sequences
   > of SQL that would eat available memory in about
   > 15 minutes.  I've been lurking around a couple of
   > these postgres lists trying to figure out whether
   > or not it was a known problem before making noise
   > about it.
   
   We're aware of a number of memory-leak type problems, although
   most of them are just temporary leakage situations (the memory
   will eventually be freed, if you have enough memory to complete
   the transaction...).  I'm hoping that we can make a serious dent
   in that class of problem for release 6.6.
   
   I believe that all the Postgres developers have a bedrock commitment
   to making the system as stable and bulletproof as we can.  But it
   takes time to root out the subtler bugs.  I got lucky tonight ;-)
   
   > Well, if not the (only) leak, at least one very,
   > very serious memory leak.  Just how many kb were
   > being leaked for each failed transaction?
   
   I was measuring about 4K per cycle for a trivial parsing error,
   like feeding "garbage;" to the backend repeatedly.  It could be
   a *lot* more depending on how much work got done before the error
   was detected.  Worst case you might lose megabytes...
   
   			regards, tom lane
   
   

6. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Don Baccus <dhogaza@pacifier.com> — 1999-05-13T02:03:56Z

   At 09:39 PM 5/12/99 -0400, Tom Lane wrote:
   
   >Thanks for the compliment :-).  You might actually be right;
   >this bug could go a long way towards explaining why some people
   >find Postgres very reliable and others don't.  The first group's
   >apps don't tend to provoke any SQL errors, and/or don't try to
   >continue running with the same backend after an error.
   
   AOLServer, in particular, will keep a backend alive 
   forever unless the site goes idle for (typically)
   some minutes.  In this way, no overhead for backend
   start-up is suffered by a busy site.  AOLServer manages
   the threads associated with particular http connections,
   while the (typically) tcl scripts servicing the connections
   ask for, use, and release database handles (the tcl
   interpreter runs inside the server) .  Each handle
   is a connection to the db backend, and these connections
   get passed around by the server to various threads as
   they're released by tcl "ns_db releasehandle" calls.
   
   So ... ANY permament memory leak by the backend will tear things
   down eventually.  "Randomly", from the sysadmin's point of view.
   
   Don't feel bad, I know of one very busy Oracle site
   that kicks things down once every 24 hrs in the
   dead of night for fear of cumulative leaks or, well,
   any of a number of imaginable db problems :)
   
   >We're aware of a number of memory-leak type problems, although
   >most of them are just temporary leakage situations (the memory
   >will eventually be freed, if you have enough memory to complete
   >the transaction...).
   
   Relatively harmless in the environment I'm describing...
   
   >  I'm hoping that we can make a serious dent
   >in that class of problem for release 6.6.
   
   Still worth getting rid of, though!
   
   >I believe that all the Postgres developers have a bedrock commitment
   >to making the system as stable and bulletproof as we can.
   
   Yes, I've gathered that in my reading of this group over the
   last three days, and in my reading of older posts.
   
   And y'all have fixed that other horrible bug from the
   web service POV: table-level locking.  Ugh.  I'd given up
   on using postgres for my project until I learned that 6.5
   doesn't suffer from this limitation.
   
   >I was measuring about 4K per cycle for a trivial parsing error,
   >like feeding "garbage;" to the backend repeatedly.  It could be
   >a *lot* more depending on how much work got done before the error
   >was detected.  Worst case you might lose megabytes...
   
   Memory's cheap, but not THAT cheap :)
   
   OK, I'll go back to lurking again.  Keep up the good work,
   folks.
   
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, and other goodies at
     http://donb.photo.net
   
   

7. Re: [HACKERS] backend dies suddenly after a lot of error messages

   Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-13T02:44:39Z

   > I was measuring about 4K per cycle for a trivial parsing error,
   > like feeding "garbage;" to the backend repeatedly.  It could be
   > a *lot* more depending on how much work got done before the error
   > was detected.  Worst case you might lose megabytes...
   
   The strange thing is that we don't usually hear about crash/leaks very
   much.  We just started hearing about it more in the past week or so.
   
   
   -- 
     Bruce Momjian                        |  http://www.op.net/~candle
     maillist@candle.pha.pa.us            |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026