Geo SCADA 2023 March Update ODBC Memory leak?

[RobbieF]

Hi everyone,

Many years ago, I wrote a client application in .NET 4.8 (C#) that uses GeoSCADAs ODBC driver to extract Historian data.

The customer has recently upgraded to GeoSCADA 2023 (6.86.8863.1) from ClearSCADA 2017R2 and I am noticing after the upgrade memory usage of both GeoSCADA and client application ballooning which is causing server issues. The same issue occurs with GeoSCADA 2022.

I have attached a very simple sample project (TestGSODBC.zip) to demonstrate the issue.  Note if running the project you will need to set the password and change the historic id and time stamps to something in appropriate in program.cs.
Also attached is the projects program.cs file renamed to program.txt to allow it to be attached.
When running you will see both the app and Geoscada's memory usage increase in task manager.

Is there something that I am doing wrong or is GeoSCADA misconfigured or is there a problem with the ODBC driver?  I think just opening and closing the connection also has this issue.

Any help would be greatly appreciated.

Robbie

[RobbieF]

Just wanted to add to the above.  

At the moment I open and close the connection as required (its multithreaded) but if I use one static connection that I open once and use it through out memory appears to behave itself.  But is keeping one static connection open best practice for a long running application?

[BevanWeiss]

I've got some services that are floating around that also use the ODBC driver to retrieve data from GeoSCADA Expert 2022+ Databases.  I haven't yet had any reports of them causing any issues (at either Server / Client side).  But then I don't use the DataReader either, I'm more a DataAdapter fan.

If you can narrow it down to a particular release of GeoSCADA Expert then you could more easily raise a support ticket with Schneider to further investigate.  I'm surprised that it would impact on the Server side as well as the Client, I'd expect given how much use the database interface into GeoSCADA Expert gets (with Crystal Reports, plus all the internal usage) that it would have been pretty well shaken out, and would get noticed if it was leaking.

The specific Windows Odbc Client driver would surprise me if such a resource leak slipped under the radar.

There wasn't any other changes made between the GeoSCADA versions?  Like going from v4.5 to v4.8 of the .NET Framework?

It's possible there were changes around Odbc connection pooling etc that might have resulted in connections (even closed ones) still lingering around in the background for a while.  That might add some small overhead to the server also (it would need to store data for each connection, even if it's just lingering).

You could possibly use Wireshark on your client PC (if remote) to see if it's indeed firing up separate ephemeral TCP sockets for each OdbcConnection, and then if they're still staying around for a while (the TCP connection isn't reset).

[RobbieF · Accepted solution]

I think I have managed to sort the memory leak. I'm currently testing the fix in UAT.

Changes made:

1. I have switched to using a system data source with DSN= in the connection string, and enabled connection pooling via the ODBC64 UI.

2. I have Bevan to thank; He mentioned he uses DataAdapters but I believe the DataAdapter uses a data reader under the hood to Fill.  This sent alarm bells ringing that maybe the way I'm setting up the reader and disposing it is incorrect! 

Now I wrap the connection like before in a using statement and do not dispose the odbc command and reader instances.  Instead, I call close on the reader within the connection's using statement.

Fingers crossed!!!

[BevanWeiss]

It looks like you're spot on with DataAdapter using a DataReader behind the scenes

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/populating-a-dataset-from-a-dataadapt...

The Fill method uses the DataReader object implicitly to return the column names and types that are used to create the tables in the DataSet, and the data to populate the rows of the tables in the DataSet.

---

And yep, I'd say that missing .Close() is likely going to make all the difference... Microsoft seem pretty keen on emphasing to do that..

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

Always call the Close method when you have finished using the DataReader object.

If your Command contains output parameters or return values, those values are not available until the DataReader is closed.

While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.

---

A little disappointing that immediately after they make this statement they then show a couple of examples where they also have not explicitely called .Close() on the DbReader instances though.

I personally would have kept the using() statements for the command and reader instances, since they do implement IDisposable (hence have some unmanaged resources that they want to clean up).

I would have had something like... 

---

---

---

using (var Connection = new Connection)

{

  using (var Command = new Command)

  {

    using (var DbReader = new DbReader)

    {

      DbReader.Close();

    }

  }

Connection.Close();  // the disposal does do this... but we can do it too..

}

---