search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.88k stars 561 forks source link

Possible memory leak when using connection pooling with SQL Server #1302

Closed sbfrancies-onyx closed 7 months ago

sbfrancies-onyx commented 7 months ago

Environment

Issue

Scenario

Our application runs as a web app with a background service that responds to Azure Service Bus events. When a message it receive the service processes and may perform several reads and writes to the database depending on the message type and content. The number of messages received is on the order of ~20,000 a day.

Expected behaviour

Memory usage remains relatively constant during use of the app, varying slightly depending on the number of messages received in a given timeframe.

Observed behaviour

Memory usage slowly increases for hours before levelling off eventually (we had to upgrade to a higher service plan).

Workaround

By setting the following at the beginning of the application the problem does not occur and the memory usage stabilises in around one hour.

pyodbc.pooling = False

We are however concerned about the performance implications of turning off pooling. We also tried upgrading to pyodbc version 5.0.1 but it made no difference. Is it possible to manually configure/manage the connection pool?

gordthompson commented 7 months ago

What version of unixODBC is being used?

v-chojas commented 7 months ago

You can find out by running odbcinst -j command.

sbfrancies-onyx commented 7 months ago

Sorry for the delay:

unixODBC 2.3.11

gordthompson commented 7 months ago

unixODBC version 2.3.12 was released 2023-08-08 and one of the fixes mentioned is

• Fix iconv handle leak with pooling

so you could try upgrading to that version and see if it helps.

sbfrancies-onyx commented 7 months ago

Thanks for the suggestion. We're on debian stable and the 2.3.12 package is only available in the testing repo. I will try to import it into stable (not recommended I think) and do some testing.

sbfrancies-onyx commented 7 months ago

Have run everything for a few hours now, We still see the same unexpected pattern of increasing memory use with unixODBC version 2.3.12 and pooling turned on.

gordthompson commented 7 months ago

I just noticed this commit:

https://github.com/lurcher/unixODBC/commit/9b31cd228d01e768a7ad9126d5da8860f1ed72a9

unwind the previous change, Python adnd pooling just won't work, so don't use it

sbfrancies-onyx commented 7 months ago

Thank you for all the help, apologies for this but it turns out our memory issue was unrelated to the DB after all. Thank you for your advice which allowed us to identify the actual issue.