Re: Memory Leakage Problem - Mailing list pgsql-general

Please keep replies on list, this may help others in the future, and
also, don't top post (i.e. put your responses after my responses...
Thanks)

On Tue, 2005-12-06 at 20:16, Kathy Lo wrote:
> For a back-end database server running Postgresql 8.0.3, it's OK. But,
> this problem seriously affects the performance of my application
> server.
>
> I upgraded my application server from
>
>     Redhat 7.3
>     unixODBC 2.2.4
>     Postgresql 7.2.1 with ODBC driver
>
> to
>
>     Redhat 9.0
>     unixODBC 2.2.11
>     Postgresql 8.0.3
>     psqlodbc-08.01.0101
>     pg_autovacuum runs as background job
>
> Before upgrading, the application server runs perfectly. After
> upgrade, this problem appears.
>
> When the application server receives the request from a client, it
> will access the back-end database server using both simple and complex
> query. Then, it will create a database locally to store the matched
> rows for data processing. After some data processing, it will return
> the result to the requested client. If the client finishes browsing
> the result, it will drop the local database.

OK, there could be a lot of problems here.  Are you actually doing
"create database ..." for each of these things?  I'm not sure that's a
real good idea.  Even create schema, which would be better, strikes me
as not the best way to handle this.

> At the same time, this application server can serve many many clients
> so the application server has many many local databases at the same
> time.

Are you sure that you're better off with databases on your application
server?  You might be better off with either running these temp dbs on
the backend server in the same cluster, or creating a cluster just for
these jobs that is somewhat more conservative in its memory usage.  I
would lean towards doing this all on the backend server in one database
using multiple schemas.

> After running the application server for a few days, the memory of the
> application server nearly used up and start to use the swap memory
> and, as a result, the application server runs very very slow and the
> users complain.

Could you provide us with your evidence that the memory is "used up?"
What is the problem, and what you perceive as the problem, may not be
the same thing.  Is it the output of top / free, and if so, could we see
it, or whatever output is convincing you you're running out of memory?

> I tested the application server without accessing the local database
> (not store matched rows). The testing program running in the
> application server just retrieved rows from the back-end database
> server and then returned to the requested client directly. The memory
> usage of the application server becomes normally and it can run for a
> long time.

Again, what you think is normal, and what normal really are may not be
the same thing.  Evidence.  Please show us the output of top / free or
whatever that is showing this.

> I found this problem after I upgrading the application server.
>
> On 12/7/05, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > On Tue, 2005-12-06 at 03:22, Kathy Lo wrote:
> > > Hi,
> >
> > >
> > > In this program, it will access this database server using simple and
> > > complex (joining tables) SQL Select statement and retrieve the matched
> > > rows. For each access, it will connect the database and disconnect it.
> > >
> > > I found that the memory of the databaser server nearly used up (total 2G
> > RAM).
> > >
> > > After I stop the program, the used memory did not free.
> >
> > Ummmm.  What exactly do you mean?  Can we see the output of top and / or
> > free?  I'm guessing that what Tom said is right, you're just seeing a
> > normal state of how unix does things.
> >
> > If your output of free looks like this:
> >
> > -bash-2.05b$ free
> >       total       used       free     shared    buffers     cached
> > Mem:6096912    6069588      27324          0     260728    5547264
> > -/+ buffers/cache:     261596    5835316
> > Swap:      4192880      16320    4176560
> >
> > Then that's normal.
> >
> > That's the output of free on a machine with 6 gigs that runs a reporting
> > database.  Note that while it shows almost ALL the memory as used, it is
> > being used by the kernel, which is a good thing.  Note that 5547264 or
> > about 90% of memory is being used as kernel cache.  That's a good thing.
> >
> > Note you can also get yourself in trouble with top.  It's not uncommon
> > for someone to see a bunch of postgres processes each eating up 50 or
> > more megs of ram, and panic and think that they're running out of
> > memory, when, in fact, 44 meg for each of those processes is shared, and
> > the real usage per backend is 6 megs or less.
> >
> > Definitely grab yourself a good unix / linux sysadmin guide.  The "in a
> > nutshell" books from O'Reilley (sp?) are a good starting point.
> >
>
>
> --
> Kathy Lo