Console App Memory Leak Guidance

Targets Linux
21

Unfortunately yes I already ruled this out earlier today my linux side leak detector code I adapted from Kem. Thank you @Ian_Kennedy as I still will take another look at this after the SQL testing.

22

@Anthony_Dellos well it appears that you were spot on with SQLite leaking memory or in my case the prepared insert and update calls are the cause here on this initial testing.

I commented out the 4 SQL calls I make in the structure above and like we knew the amount objects stay at 292, but this time the memory does NOT appear to be leaking. The memory appears to fluxuate as one would expect and i see a low consumption of 10MB and a high roughly around 17MB – before I watched as this thing could grow past 2GB of memory consumption before I shut it down :slight_smile:

Ok so definirtely SQL Lite. I will post my methods and if you all don’t mind to see if I am screwing something up? I tried to move them to API 2.0 so hopefully I did this correctly. I will update the drawing to be more detailed for the SQL specific calls.

Thank you again @Kem_Tekinay @Ian_Kennedy @Anthony_Dellos !!

2 Likes
23

This is my current prepared statement insert. I removed the sentry.io exception code to make the code more readable. I can’t seem to find anything that would cause a leak with this.

Var rowUuidStr As String = CommonModule.createUuid()
Var SQLInsertStatement As String =  "insert into collectorResultsTable (rowUuid, transactionDateTime, myWorkerId, taskSessionId, authUser, restMethodType, manufacturer, endpointType, epHostname, restUri, taskStartDateTime, isFresh, isComplete) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Var prepInsert As SQLitePreparedStatement = SQLiteDB.Prepare(SQLInsertStatement)

prepInsert.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(11, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(12, SQLitePreparedStatement.SQLITE_TEXT)

prepInsert.Bind(0, rowUuidStr)
prepInsert.Bind(1, transactionDateStr)
prepInsert.Bind(2, myWorkerIdStr)
prepInsert.Bind(3, taskSessionIdStr)
prepInsert.Bind(4, authUserStr)
prepInsert.Bind(5, restMethodTypeStr)
prepInsert.Bind(6, manufacturerStr)
prepInsert.Bind(7, endpointTypeStr)
prepInsert.Bind(8, epHostnameStr)
prepInsert.Bind(9, restUriStr)
prepInsert.Bind(10, taskStartDateTimeStr)
prepInsert.Bind(11, "true")
prepInsert.Bind(12, "false")

// Execute Prepared SQL Statements
prepInsert.ExecuteSQL()

This is my update snippet - Please if you see anything I can modify I always appreciate the help.


Var isCompleteStr as String
If resultStr <> "" Then
  isCompleteStr = DefineEncoding("true", Encodings.UTF8)
End If

Var TableName As String = "collectorResultsTable"
Var SQLStatement As String =  "UPDATE " + TableName + " SET " +_
"taskStopDateTime" + " = '"+ taskStopDateTimeStr + "'," +_
"status" + " = '"+ statusCodeStr + "'," +_
"isComplete" + " = '" + isCompleteStr + "'," +_
"rawResult" + " = '"+ resultStr + "'" +_
" WHERE taskSessionId = '" + taskSessionIdStr + "';"

SQLiteDB.ExecuteSQL("BEGIN TRANSACTION;")
SQLiteDB.ExecuteSQL(SQLStatement)

If SQLiteDB.Error = True Then
  SQLiteDB.RollbackTransaction
  Return False
  
ElseIf SQLiteDB.Error = False Then
  SQLiteDB.CommitTransaction
  Return True
End If
24

This is the way :slight_smile:

This whole exercise was a great benefit as I really was forced deep into understanding how SQL uses memory and how certain PRAGMAs can impact memory allocation :slight_smile:

collectionResultDatabaseClass.sqlIteDb.SQLExecute("PRAGMA journal_mode = WAL;")
collectionResultDatabaseClass.sqlIteDb.SQLExecute("PRAGMA synchronous = normal;")
collectionResultDatabaseClass.sqlIteDb.SQLExecute("PRAGMA temp_store = memory;")

I inadvertently has a PRAGMA setting the cache size to 2GB when I only have a micro server running with 8MB of RAM :slight_smile: The appearance of the runaway memory was SQL server allocating more memory for itself, but I didn’t realize it at the time. For now I am removing the cache size and I will eventually add it back later and make it’s value dynamically user set from the CLI as a passed argument.

image
image1920×1605 638 KB

4 Likes
25

Ah, interesting. I was going to suggest that you try ExecuteSQL rather than SQLExecute. It keeps you from having to do the types and the bindings but still uses a prepared statement. Safer than string concatenation for SQL.

26

The posted code use ExecuteSQL but the binding is still being performed. I think the code can be changed to not use the binding, right?

27

ExecuteSQL(command As String, Optional ParamArray values() As Variant)

No binding of types, no binding of data. call it with the SQL statement and the variables for each data item:

Var rowUuidStr As String = CommonModule.createUuid()
Var SQLInsertStatement As String =  "insert into collectorResultsTable (rowUuid, transactionDateTime, myWorkerId, taskSessionId, authUser, restMethodType, manufacturer, endpointType, epHostname, restUri, taskStartDateTime, isFresh, isComplete) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Var prepInsert As SQLitePreparedStatement = SQLiteDB.Prepare(SQLInsertStatement)

prepInsert.ExecuteSQL( rowUuidStr, transactionDateStr, prepInsert. myWorkerIdStr, taskSessionIdStr, authUserStr, restMethodTypeStr, manufacturerStr, endpointTypeStr, epHostnameStr, restUriStr, taskStartDateTimeStr, "true", "false" )

The types come from the variable types and the order is simply the order of the parameters.

28

Do we need to use prepInsert As SQLitePreparedStatement?

Can’t we just:

Var rowUuidStr As String = CommonModule.createUuid()
Var SQLInsertStatement As String =  "insert into collectorResultsTable (rowUuid, transactionDateTime, myWorkerId, taskSessionId, authUser, restMethodType, manufacturer, endpointType, epHostname, restUri, taskStartDateTime, isFresh, isComplete) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

SQLiteDB.ExecuteSQL( SQLInsertStatement, rowUuidStr, transactionDateStr, prepInsert. myWorkerIdStr, taskSessionIdStr, authUserStr, restMethodTypeStr, manufacturerStr, endpointTypeStr, epHostnameStr, restUriStr, taskStartDateTimeStr, "true", "false" )

Edit: thank you Ian.

1 Like
29

Yes, either is possible. If you are only going to run the statement once it is fine to use the “database.ExecuteSQL” form.

If you are going to run it repeatedly then you are better creating the prepared statement and using the “Statement.ExecuteSQL” form. That way it works out the logic of executing the statement once and performs it repeatedly.

2 Likes
30

Thank you so much guys! I will make those changes and I appreciate you helping with my weak spots! I appreciate it.

@Ian_Kennedy - You are right as I run this insert a ton :slight_smile:

1 Like
31

:slight_smile: Left over code from years past and I didn’t even notice :slight_smile: Thanks!!

32

Hi Mike,

Glad you got it sorted. I’d say I was “half right at best” (half write?, db joke, har har) :smile: but happy if my comment pointed you in the right direction.

Thanks for sharing what you discovered about SQLite pragmas. Very interesting stuff.

33

Yes you certainly helped me Anthony thank you again!

Additionally this site helped me a lot understanding what certain PRAGMA statements do and how it related to SQLite performance tuning.
https://phiresky.github.io/blog/2020/sqlite-performance-tuning/

1 Like