Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

Stored Procedure Memory Usage

Discuss Stored Procedure Memory Usage in the Firebird SQL Development forum on Dev Shed.
Stored Procedure Memory Usage Firebird SQL Development forum discussing administration, Firebird SQL syntax, or other Firebird SQL-related topics. Firebird is the evolution of Borland's Interbase product.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 16th, 2004, 12:05 AM
singhjoga singhjoga is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 20 singhjoga User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 13 m 31 sec
Reputation Power: 0
Stored Procedure Memory Usage
Guys I am trying desparately to get some feedback from the development team of firebird. Somehow Yahoogroups is not opening in India for some days. I have no other option but to post my message at all places where possible. I have posted this in the firebird Bug List also, but nobody has responded till now.

This is an extension to my previus post "Firebird 1.51.
sucking all memory" in firebird-support list on yahoogroups.

I am using stored procedures extensibly in my
application. There is a part
in my application (written in C using EMBEDDED SQL)
which executes on an
average 250 update/insert queries. Earlier all these
updates and inserts
were done through Stored Procedures. I was monitoring
the memory usage using
'top' command. Memory usage used to increase by 3MB
every 5 minutes, so in a
fiew hours it used to reach 300MB and then crashing all
my applications.

Now I have removed all insert and update statements
from the stored
procedures and put them into my application. AND there
was amazing
difference in memory increase. In 37 Hours, it reached
only 57MB. I am still
using stored procedures to query the database but not
any updates/inserts.

This application is a server based application where it
should be running
for months without any manual intervention. I can not
afford to have even
57MB increase. Major portion of the application is still
using stored
procedures (doing inserts/updates), but it is very less
used. So can
somebody from the development team comment on my
following queries.

1. Is it a known problem ? If not then should I post it to
the bug list ?

2. Are there any planned future releases, where this
problem is solved ?
Guys I will appriciate, if you can answere this question.
Becuase I chose
firebird only because of it having stored
procedures/views/speed etc over
MySQL. I can not afford to have these problems in the
database. I would
rather spend more time porting my application to some
other database, than
continuing with these problems. My exeperience with
MySQL and Postgres is
very good. I never faced such problems with them. I
was expecting the same
stability from the firebird also.

3. Are there any short term workaround for this problem ?

If required, i can post my test db and related files.

Regards:

Joga Singh
Reply With Quote
  #2  
Old October 17th, 2004, 06:21 AM
mariuz's Avatar
mariuz mariuz is offline
Bug Hunter
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2003
Location: Transylvania (Romania)
Posts: 274 mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level)mariuz User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 20 h 12 m 34 sec
Reputation Power: 9
could you give the database script to test it ?
__________________
My home page: http://www.firebirdsql.org and work place :http://www.reea.net
Reply With Quote
  #3  
Old October 17th, 2004, 05:01 PM
hugo2 hugo2 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2004
Posts: 20 hugo2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 47 m 14 sec
Reputation Power: 0
Quote:
Originally Posted by singhjoga
1. Is it a known problem ? If not then should I post it to
the bug list ?

No, it is not known problem.
And you should NOT post it to bug-list, unless you have created reproducible test case.
Quote:
Originally Posted by singhjoga
2. Are there any planned future releases, where this
problem is solved ?

You mean releases of your application ?
Quote:
Originally Posted by singhjoga
3. Are there any short term workaround for this problem ?

Yes, fix your application.

Do you use any UDFs ?
Do you properly close or unprepare all statements ?
Do you update the same data in single stored procedure or single transaction ?
Reply With Quote
  #4  
Old October 17th, 2004, 05:16 PM
hugo2 hugo2 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2004
Posts: 20 hugo2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 47 m 14 sec
Reputation Power: 0
Quote:
Originally Posted by singhjoga
In 37 Hours, it reached only 57MB.
...
I can not afford to have even 57MB increase.

So, does the memory usage still increase continuously (just slower), or does it stop at some level ?
Reply With Quote
  #5  
Old October 21st, 2004, 07:37 AM
singhjoga singhjoga is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 20 singhjoga User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 13 m 31 sec
Reputation Power: 0
Quote:
Originally Posted by mariuz
could you give the database script to test it ?


Can somebody do this test? There seems to be some memory leak in the
udf 'addDay'(fbudf comes with firebird). If I call the Stored
Procedure given below in a loop, memory usage of firebird (using top
command in linux) crosses 100MB in a few minutes. When I comment the
statement calling 'addDay', there is no memory increase.



JS
Code:
-------------------------------------
SET TERM !!;

CREATE OR ALTER PROCEDURE testget_maxdate RETURNS (retval int)
AS
declare date_in timestamp;
declare ret_in timestamp;
begin
date_in=current_timestamp;
ret_in=addday(date_in,1);
-- ret_in=addday(cast(date_in as date),1);
-- ret_in=addmillisecond(addday(cast(date_in as date),1),-1);
retval=1;
SUSPEND;
end !!

SET TERM ; !!
Reply With Quote
  #6  
Old October 24th, 2004, 09:41 PM
skidder skidder is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 15 skidder User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to skidder
I just tried it on 64-bit 1.5.2.4634.
Yes, it indeed eats memory very quickly.

I'm gonna look at it right now. But it would be better if you posted such a nice testcase to firebird-devel mailing list.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Stored Procedure Memory Usage

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway.