Recent

Author Topic: Sqlite3 Memory Leak  (Read 8419 times)

jdp

  • Full Member
  • ***
  • Posts: 144
Sqlite3 Memory Leak
« on: July 23, 2016, 12:34:11 am »
Hi all. I hope somebody can help me. I have a time on a form that fires every 20 seconds to update a totals table in a sqlite3 db.
The memory will go up with about 4 bytes every time. I am releasing the memory.  What am I doing wrong for this to happen ?

Regards


Code: Pascal  [Select][+][-]
  1.  
  2. //Create the DB Connection
  3. function CreateConnection:TSQLite3Connection;
  4. begin
  5.   result := TSQLite3Connection.Create(nil);
  6.   result.HostName := '';
  7.   result.DatabaseName := 'Solar.db';
  8.   result.UserName := '';
  9.   result.Password := '';
  10. end;
  11.  
  12. procedure update_daily_totals();
  13. var
  14.    //Declare the DB Vars
  15.   DBConnection : TSQLite3Connection;
  16.   DBTransaction : TSQLTransaction;
  17.   DBQuery : TSQLQuery;
  18.  
  19. begin
  20.  
  21.  try
  22.  
  23.     //Create a connection
  24.     DBConnection := CreateConnection;
  25.     //Create a transaction
  26.     DBTransaction := TSQLTransaction.Create(DBConnection);
  27.     //Point to the database instance
  28.     DBTransaction.DataBase := DBConnection;
  29.  
  30.     //Open the DB
  31.     DBConnection.Connected := True ;
  32.     //Create a query to return data
  33.     DBQuery := TSQLQuery.Create(DBConnection);
  34.     //Point the query to DB and Transaction
  35.     DBQuery.DataBase := DBConnection;
  36.     DBQuery.Transaction := DBTransaction;
  37.  
  38.     //Insert some data (Primary Key is the date and On Conflict Replace has bee set
  39.     DBQuery.SQL.Text := 'INSERT INTO DAILY_TOTALS (RECORD_DATE,GRID_KWH, KWH_CONSUMED, SOLAR_KWH_PRODUCED, MAX_PV_WATTS, MAX_LOAD_WATTS) values (:P1,:P2,:P3,:P4,:P5,:P6)';
  40.  
  41.     //Set the parameters
  42.     DBQuery.Params.ParamByName('P1').AsDate := Date;
  43.     DBQuery.Params.ParamByName('P2').AsFloat := GridTotalDayExportCount;
  44.     DBQuery.Params.ParamByName('P3').AsFloat := GridTotalDayCount;
  45.     DBQuery.Params.ParamByName('P4').AsFloat := SolarTotalDayCount;
  46.     DBQuery.Params.ParamByName('P5').AsFloat := 0;
  47.     DBQuery.Params.ParamByName('P6').AsFloat := 0;
  48.  
  49.  
  50.     //Execute
  51.     DBQuery.ExecSQL;
  52.     //Commit the record
  53.     DBTransaction.Commit;
  54.     //Close the query
  55.     DBQuery.Close;
  56.     DBTransaction.CloseDataSets;
  57.     DBConnection.Connected := False;
  58.  
  59.     finally
  60.      //Release the memory
  61.      DBTransaction.Free;
  62.      DBQuery.Free ;
  63.      DBConnection.Free ;
  64.     end;
  65.  
  66. end;
  67.  

Leledumbo

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Sqlite3 Memory Leak
« Reply #1 on: July 23, 2016, 04:09:19 am »
You can't really know unless you use some leak tracer. So activate -gh along with -gl first, then run your program until it terminates. The result should tell you if there are really memory leaks and their respective stacktrace.

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #2 on: July 23, 2016, 06:00:19 am »
Hi. I did do a trace and it shows that all blocks are freed. Yet the memory is growing.

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #3 on: July 23, 2016, 06:59:07 am »
Ok this is very strange. If I take this code on its own and place it in a form and I call the code from a timer every sekond then I have no memory leak. But if I use the same code in my bigger app I do. So it seems that some componants or library is interfering with this code.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Sqlite3 Memory Leak
« Reply #4 on: July 23, 2016, 09:31:02 am »
So your memory leak is somewhere else.

FYI. if your create an object with an owner you should free it yourself. Only objects created with owner nil should be freed by your code. So either create DBTransaction and DBQuery with nil or remove the .Free lines for those.

(It won't cause the memory leak but internally it's very inefficient to free objects if they are "managed" by other objects.)

See the warning at the end of this page
http://delphi.about.com/od/kbcurt/a/dynamiccreation.htm
And
http://delphi.about.com/od/kbcurt/ss/dynamiccreateno.htm
« Last Edit: July 23, 2016, 09:34:09 am by rvk »

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #5 on: July 23, 2016, 11:29:19 am »
Hi rvk. Thanks for the reply.

What I did was to disable all the threads and timers. Then I enabled them one at a time until I saw the memory grow. The memory stands still until I enable the timer that fires this code in the bigger app. Yet if I run that code on its own it does not grow. And the memory profiler shows all blocks are relaesed.

I have tried cmem as well. What I also see is that if I include ssl_openssl in the uses then the debugger will crash. It compiles the app but it wont debug. so can it be that some libraries dont play well together ?

Thaddy

  • Hero Member
  • *****
  • Posts: 14205
  • Probably until I exterminate Putin.
Re: Sqlite3 Memory Leak
« Reply #6 on: July 23, 2016, 12:34:13 pm »
What I also see is that if I include ssl_openssl in the uses then the debugger will crash. It compiles the app but it wont debug. so can it be that some libraries dont play well together ?
Are you compiling for windows 32 bit? That makes a huge difference..... for shared libraries compiled in another language. You need to enable win32 SEH.
What we need is platform, compiler version. Both are really important to give good advice in this case.

Btw. You narrowed the problem already down to the code that gets executed in the timer.
There are two strategies that I would try:
- never allocate and free objects nin the timer code, but simply reset them
- Within the timer code, use freeandnil.
Specialize a type, not a var.

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #7 on: July 23, 2016, 04:25:03 pm »
Thanks Thaddy.

I am developing an app that works with my solar system on the Raspberry Pi.
So the OS is Rasbian Jessie.

Lazarus 1.6 (Date 2016-05-23) FPC 2.6.4 arm-linux-gtk 2

When you say reset the variable are you saying I should declare them as globals and reuse them all the time ?


rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Sqlite3 Memory Leak
« Reply #8 on: July 23, 2016, 04:50:20 pm »
Lazarus 1.6 (Date 2016-05-23) FPC 2.6.4 arm-linux-gtk 2
You didn't install Lazarus 1.6 with FPC 3.0 ??

If you only see the memory leak with the procedure in a timer you could try to create a small test-project with this procedure in a timer. If you still can't simulate it with a small test-project there must be something wrong elsewhere in your code. If you can simulate it with a small test-project then you can zip it and place it here in a post so we can look what's going wrong.

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #9 on: July 23, 2016, 06:07:13 pm »
Hi.

This does not make any scenes at all. If I use this code on its own in a little test app then it works 100%.
But If I call it from a timer or a button in the Inverter app then the memory will go up.

This is the code I am calling now.

Code: Pascal  [Select][+][-]
  1. procedure update_daily_totals();
  2. var
  3.    //Declare the DB Vars
  4.   DBConnection : TSQLite3Connection;
  5.   DBTransaction : TSQLTransaction;
  6.   DBQuery : TSQLQuery;
  7.  
  8. begin
  9.  
  10.  try
  11.  
  12.     //Create a connection
  13.     DBConnection := CreateConnection;
  14.     //Create a transaction
  15.     DBTransaction := TSQLTransaction.Create(nil);
  16.     //Point to the database instance
  17.     DBTransaction.DataBase := DBConnection;
  18.  
  19.     //Open the DB
  20.     DBConnection.Connected := True ;
  21.     //Create a query to return data
  22.     DBQuery := TSQLQuery.Create(nil);
  23.     //Point the query to DB and Transaction
  24.     DBQuery.DataBase := DBConnection;
  25.     DBQuery.Transaction := DBTransaction;
  26.  
  27.     //Insert some data (Primary Key is the date and On Conflict Replace has bee set
  28.     DBQuery.SQL.Text := 'INSERT INTO DAILY_TOTALS (RECORD_DATE,GRID_KWH, KWH_CONSUMED, SOLAR_KWH_PRODUCED, MAX_PV_WATTS, MAX_LOAD_WATTS) values (:P1,:P2,:P3,:P4,:P5,:P6)';
  29.  
  30.     //Set the parameters
  31.     DBQuery.Params.ParamByName('P1').AsDate := Date;
  32.     DBQuery.Params.ParamByName('P2').AsFloat := GridTotalDayExportCount;
  33.     DBQuery.Params.ParamByName('P3').AsFloat := GridTotalDayCount;
  34.     DBQuery.Params.ParamByName('P4').AsFloat := SolarTotalDayCount;
  35.     DBQuery.Params.ParamByName('P5').AsFloat := 0;
  36.     DBQuery.Params.ParamByName('P6').AsFloat := 0;
  37.  
  38.     DBQuery.Params.Clear;
  39.  
  40.     //Execute
  41.     DBQuery.ExecSQL;
  42.     //Commit the record
  43.     DBTransaction.Commit;
  44.     //Close the query
  45.     DBQuery.Params.Clear;
  46.     DBQuery.Close;
  47.     DBConnection.Connected := False;
  48.  
  49.     finally
  50.  
  51.      //Release the memory
  52.      freeandnil(DBTransaction);
  53.      freeandnil(DBQuery);
  54.      freeandnil(DBConnection);
  55.     end;
  56.  
  57. end;
  58.  

wp

  • Hero Member
  • *****
  • Posts: 11858
Re: Sqlite3 Memory Leak
« Reply #10 on: July 23, 2016, 06:32:13 pm »
How do you measure that memory usage is growing? In my opinion the only relevant test is heaptrc. If there are zero unfree'ed memory blocks at program end then your program does not have any memory leak whatever you own measuring routine tells. It depends on how the memory manager works and how the balance is set between speed and heap fragmentation. It is hard to predict from the outside how memory usage will evolve while a program is running. Not every allocation is balanced by an equal amount of released memory. Sometimes the memory manager will decide to allocate more or release less memory than requested - what's not released this time may be released the next time. This is out of your control. You only can make sure that every allocation has a matching release, but you cannot control when the release occurs. Ultimately all blocks marked for release are released at program end, and here heaptrc tells you whether any previously allocated blocks are left.

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: Sqlite3 Memory Leak
« Reply #11 on: July 23, 2016, 09:36:18 pm »
If there are zero unfree'ed memory blocks at program end then your program does not have any memory leak whatever you own measuring routine tells.

That depends on your definition of a memory leak. In the strictest sense (a block without reference in the program outside the heapmanager), it might be.

But if something gets added to some global resource that is freed on shutdown, you get all behaviour of a program with a memleak, without the heaptrc leaks to prove it. 

Examples that I found out to my shame:
- memo's used for logging that due to circumstances got fuller than expected
- in a web framework that we used, some resource was not freed from some list, but that list was freed on shutdown.
- Several cases with queues that were not drained by their consumers as intended (in various circumstances) but all were deallocated happily on shutdown.

Basically having excess references where you don't expect them is the same as a memleak.

jdp

  • Full Member
  • ***
  • Posts: 144
Re: Sqlite3 Memory Leak
« Reply #12 on: July 23, 2016, 09:59:46 pm »
How we check the memory is with htop.
When I start the app this is the memory (first image). You can also see all the threads
Then 20 minutes later you can see how much it grew. Now if I leave the app to run for a day it will grow 5mb per day.
If I don't start the timer that runs the DB code it does not grow at all.







 

TinyPortal © 2005-2018