Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Memeory usage |
Mon, Feb 21 2011 1:04 AM | Permanent Link |
Bryn Lewis Intelligent Software and Systems | 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 |
Tue, Feb 22 2011 8:38 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Feb 22 2011 7:03 PM | Permanent Link |
Bryn Lewis Intelligent Software and Systems | Yes, sorry it is on connected := true
I will send the db files |
Wed, Feb 23 2011 3:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Feb 23 2011 8:21 PM | Permanent Link |
Bryn Lewis Intelligent Software and Systems | >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. |
Mon, Feb 28 2011 4:52 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Mar 2 2011 5:20 AM | Permanent Link |
Bryn Lewis Intelligent Software and Systems | 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? |
Fri, Mar 4 2011 10:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |