Possible memory leak when using connection pooling with SQL Server
sbfrancies-onyx opened this issue · 8 comments
Environment
- Python: 3.10.8
- pyodbc: 4.0.35
- OS: Linux (Azure App Service)
- DB: SQL Server (Azure SQL)
- driver: ODBC Driver 17 for SQL Server
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?
What version of unixODBC is being used?
You can find out by running odbcinst -j command.
Sorry for the delay:
unixODBC 2.3.11
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.
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.
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.
I just noticed this commit:
unwind the previous change, Python adnd pooling just won't work, so don't use it
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.