I have a web server application in which I am maintaining a session pool - when a query is required, it is either assigned a session from the pool or (if no available sessions) a new session is created for it. New sessions are then added to the pool.

Once the session has been used, it is marked as available for reuse.

The session are created with:

   EnterCriticalSection(SessionNameSection);
   try
     LastSessionValue:=LastSessionValue+1;
     localSession := TEDBSession.Create(nil);
     with localSession do
     begin
       SessionName:='AccountSession'+IntToStr(LastSessionValue);
       Loginpassword := 'EDBDefault';
       LoginUser := 'Administrator';
     end;
     res := TSessionObject.Create;
     res.session := localSession;
     res.bAvailable := false;

     LocalDatabase:=TEDBDatabase.Create(nil);
     LocalDatabase.SessionName:=LocalSession.SessionName;
     LocalDatabase.DatabaseName:='WebQI';
     LocalDatabase.Database :='WebQI';
     LocalDatabase.Connected:=True;

     res.Database := localDatabase;
     codesite.send('created session and database');
     sessionPool.slSessions.AddObject(localSession.SessionName,res);
   finally
     LeaveCriticalSection(SessionNameSection);
   end;
 end;

The TEDBdatabase.create(nil) uses 40-60MB each time.

I assume this is a consequence of the database it is connecting to. While the db is growing, it is not huge.

Once there are ~20 sessions, I start to run out of RAM.

What can I do to either
-use less RAM for each session/database, or
-require fewer session/database connections?

thanks, bryn

Bryn,

<< The TEDBdatabase.create(nil) uses 40-60MB each time. >>

There's no way that the TEDBDatabase.Create uses that much memory.  Are you
sure that it isn't the Connected:=True line instead ?

In general, that much memory usage per session is definitely excessive.  If
you want to email me the database catalog and table files, I can check it
out here and see if there's anything I can find that is out of the ordinary.

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes, sorry it is on connected := true

I will send the db files

Bryn,

<< Yes, sorry it is on connected := true

I will send the db files >>

The main issue is the size of the catalog, which is 1.2MB by itself.  One of
our largest customers with a very complex database structure only has a
catalog with a size of around 512KB.

The reason for the large catalog is that you appear to be using the metadata
as a row-based mechanism for storing multiple copies of the same table
(ProjectXXX where XXX  is a number between 1 and 999).  I would strongly
suggest that you look at a different design.  EDB assumes that the metadata
for a database will be as succinct as possible and not change very often,
and you're going against both of those principles.

I would suggest a single table with a standard structure to represent all
projects, with a BLOB/CLOB column to represent any customizations to that
project in terms of additional columns, etc.  They key here is do *not*
constantly make copies of the same table.  You'll just end up bloating the
catalog and causing the memory consumption to skyrocket.

--
Tim Young
Elevate Software
www.elevatesoft.com

>The reason for the large catalog is that you appear to be using the metadata
>as a row-based mechanism for storing multiple copies of the same table
>(ProjectXXX where XXX  is a number between 1 and 999).  

What do you mean by 'using the metadata'? I'm not doing any lookup of metadata.

I'm not making copies of the same table. Each subject has an id_project to indicate which project table it links to, but I don't understand why that causes a problem in the catalog. The program logic generates the queries, not any information in the db itself.

>I would suggest a single table with a standard structure to represent all
>projects, with a BLOB/CLOB column to represent any customizations to that
>project in terms of additional columns, etc.  

That wouldn't be practical as they are too different.

>They key here is do *not*
>constantly make copies of the same table.  You'll just end up bloating the
>catalog and causing the memory consumption to skyrocket.

I don't see how I am making multiple copies.

Bryn,

<< What do you mean by 'using the metadata'? I'm not doing any lookup of
metadata. >>

You've got a set of tables with a naming convention of Project_X where X is
any number between 1 and 999, and you do this in several ways, so the number
of tables is very large, and artificially so.  Furthermore, many of these
tables have hundreds of columns in them that are built in a similar manner.
The result is thousands of columns, 9734 to be exact.  With 140 tables,
that's an average of 69 columns per table.  There's also 7390 indexes in
all, or an average of 52 indexes per table.  The indexes are probably the
biggest issue, but the number of columns and their sizes are excessive also.
Many of the columns in these tables are defined as VARCHAR(100) and
VARCHAR(200), which should really be defined as CLOB.  One of your tables
has a row size of 13392 bytes (!).

Basically, any time that you start naming things with a numeric extension,
you need to consider a different design because you're using the database
metadata in a way that it isn't intended to be used.  If you need flexible
column layouts, then you should keep the base columns down to a specific set
of common columns, and then use BLOBs or something similar to accommodate
the dynamic columns.

<< I'm not making copies of the same table. Each subject has an id_project
to indicate which project table it links to, but I don't understand why that
causes a problem in the catalog. The program logic generates the queries,
not any information in the db itself. >>

None of this "causes a problem" per se, and EDB really doesn't care one way
or the other.  However, your complaint was that each session is taking up
too much memory, and the reason is the excessive amount of metadata that EDB
has to keep track of for your database.  I'm simply telling you what you
need to do to resolve this issue, which is cut down on the number of columns
and indexes per table.

--
Tim Young
Elevate Software
www.elevatesoft.com

cool, I thought you were implying there was something else going on.

Apart from querying the information schema, are there any other tools for analysing the db metrics?

Bryn,

<< cool, I thought you were implying there was something else going on. >>

The more I look at this, the more I think that your memory consumption may
be more down to the large row sizes due to the long VARCHAR() columns than
the duplicated tables.  The number of indexes probably doesn't help much,
either, but the row sizes may caused the memory consumption per session to
balloon.  Switching these columns to CLOB columns will give you a true
VARCHAR(), and if you need to index them still, you can add COMPUTED columns
that extract a portion of the CLOB column and index them instead.  COMPUTED
columns are an in-memory, virtual column, so they are ideal for this type of
setup.

<< Apart from querying the information schema, are there any other tools for
analysing the db metrics? >>

That's usually the best way, especially to view the row sizes for a set of
tables.

--
Tim Young
Elevate Software
www.elevatesoft.com