Board index » delphi » Memory Leak Issues

2007-12-17 01:36:46 PM
delphi13
This is a long running issue (>4years) that is becoming disruptrive and
frustrating due to having to restart our server more and more frequently.
Some facts first:
IB version: 7.5 (we upgraded from 6.1 in the hope that would solve the
problem)
DB Size: approx 3-4gb
Concurrent users: < 100 (probably averages 50)
Processing around 100k transactions / month
Server RAM: 2GB
Task Manager reports memory use climbing from around 100mb to about 1Gb in 2
weeks, at which point the DB starts grinding to a halt. I have not be able
to get my head around what to look for using Performance Monitor - hopefully
someone can help with that
We have looked at UDF's but cannot seem to find any one procedure or trigger
that resolves the problem.
The DB needs to be up 24/7, but of greater is whether the system can handle
the strain as the business grows - although I know it should.
Any help REALLY appreciated.
Russell.
 

Russella writes:
 

Russella writes:
slow?
2) What are the OIT, OAT and Next transaction values when IB is slow?
--
Bill Todd (TeamB)
 

Where possible, we try and re-boot the server overnight before it gets to
this stage, to avoid disruption.
Will reporting this data at peak memory use suffice (before reboot)? If so,
that will take another week.
"Bill Todd" <XXXX@XXXXX.COM>writes

Russella writes:
If a udf returns a string the memory must be explicit freed by the
free_it keyword. If that is ommitted IB keeps on allocating memory
without freeing it which can cause memory get exhausted.
--
Gelein van de Voorde (IBConsole team)
"The blossoms on an apple tree are standardized,
and yet they are all different.
That is how we, too, should learn to build."
Alvar Alto
 

Sorry Simon, it actually is 7.5.1.80.
"SimonW" <XXXX@XXXXX.COM>writes

Russella writes:
problem until we see it. That said, yes, post the numbers when the
system has been running as long as possible.
--
Bill Todd (TeamB)
 

I'm not 100% sure - we're using mostly FreeUDF, which I am thinking of
changing to FreeAdhoc, if that is any better memory wise.
I've just used the declarations that were provided, assuming they'd be
correct.
"Gelein van de Voorde" <XXXX@XXXXX.COM>writes

Russella writes:
Interbase.
Gelein van de Voorde (IBConsole team)
"The blossoms on an apple tree are standardized,
and yet they are all different.
That is how we, too, should learn to build."
Alvar Alto
 

Three things stand out. First, the OIT (oldest interesting transaction
is stuck. The difference between the OIT and OAT is 5323. Normally it
should be 1. Did you have a server crash and not run a sweep after the
server was restarted?
Almost 4% of your transactions are being rolled back which is unusual.
Is it possible that you have rolled back a transaction with a very
large number of changes (100,000 or more)? That could also stick the
OIT.
Second, you appear to have one or more long running transactions. The
gap between the OAT and next transaction is 85,285. Look at the
Transactions tab in performance monitor and sort by Elapsed Time. How
long has the oldest transaction been running? If the answer is measured
in hours you need to find the cause and fix it.
Three, only 29 megabytes of memory is being used for the page cache yet
current total memory is 1 gigabyte. What is all of that memory being
used for? That is very unusual. Look at the pie chart on the Memory tab
to find out what is consuming so much memory.
--
Bill Todd (TeamB)
 

Bill, here are the numbers from the 30th, just prior to doing a
shutdown/restart when task manager reported memory usage @ 868mb.
Let me know if you need more info.
Russell.
Active threads 1
Attachments 12
Compiled statements 95
Loaded procedures 60
Loaded tables and views 141
Loaded triggers 150
Memory: Cache buffers 7000
Memory: Cache free waits 2171
Memory: Cache free writes 133657
Memory: Cache latch waits 248979
Memory: Cache precedence 5
Memory: Cache pool 29613056
Memory: Current 1054229744
Memory: Maximum ever allocated 2147483456
Memory: Permanent pool 1426432
Memory: Pools 437
Memory: Sort 1048500
Pages allocated 979615
Page fetches -835419611
Page marks 5051531
Page reads 406304104
Page writes 1764000
Record backouts 18127
Record deletes 322731
Record expunges 316968
Record inserts 575585
Record purges 37624
Record selects 281591260
Record updates 439975
Sweep active? N
Sweep interval 20000
Sweep records
Sweep table
Transactions 11
Transactions: Commits 120036
Transactions: Conflicts 30
Transactions: Deadlocks 1
Transactions: Next 732637
Transactions: Oldest active 647352
Transactions: Oldest interesting 642029
Transactions: Oldest snapshot 647352
Transactions: Prepares 0
Transactions: Rollbacks 4739
Transactions: Waits 40
"Bill Todd" <XXXX@XXXXX.COM>writes

Thaks for the comments Bill.
this difference is 19269. I am unsure why this would be so. Some of the
comments below may shed some light.
not explicitly start, commit or rollback transactions when running select
queries, having always assume that they would take care of themselves. Is
this inappropriate?
transactions as you suggest, it appears as tho a transaction is created at
the time each user has logged on. There are no transactions earlier than
today (other than COLLECTOR which shows 85hours), but they do all go back to
the beginning of the day - up to 5 hours at this point. Is this normal?
user queries) 45mb, followed by CCH (Cache mgr/page buffers) at 29mb. The
total at this stage is showing as a -515375280 - why is this minus? The
Memory Cache Pool (assuming that is what you're quoting above) is still about
29mb and task manager is showing 251mb.
I sincerely hope all this paints some picture for you as to where the
problem may lie, as clearly I am out of my depth.
Thanks again.
Russell.
 

possible to correct the problem.
transactions so you know what is happening and when it happens. What
data access components are you using? The data access components should
be configured to automatically commit the transaction when the SELECT
query is closed.
Transactions should be as short as possible. You can expect to have
performance problems due to the long transactions. The more updates and
deletes you do the worse the performance will be.
never seen that happen. The negative number could be caused by integer
overflow but I cannot see how that is possible.
My suggestions are:
1) Restart the IB server as soon as possible.
2) Run a database sweep while no users are connected.
3) If you cannot do 2 above then run a sweep while users are connected.
4) Monitor the memory usage and see if total memory goes negative. You
might also query the TMP$DATABASE table and see if any of the memory
numbers are negative. You can find information about TMP$DATABASE in
the Language Reference manaual.
5) See if there is any easy way to modify the application to eliminate
the long running transactions.
The most important are for continued investigation is memory usage. You
need to find out if something really does consume a large amount of
memory from time to time.
--
Bill Todd (TeamB)
 

Thanks Bill.
1) I ran the sweep while the db was down.
2) I have discovered that the data access components that we use (SQLDirect)
create some sort of implicit transaction on connection, and then create a
new transaction after every commit/rollback, which explains the long running
transactions.
I'm told that this has been corrected in a later version, but this has
created a new problem, for which I have started a new thread - Open
Transactions.
3) As you say, the memory leak is my most major concern. I will investigate
further as per your instructions, however, I am still at a loss as to where
to look for the cuase. Do you think however, that any of the transaction
issues could be to blame?
Russell.
"Bill Todd" <XXXX@XXXXX.COM>writes