Adjust DB2 keepalive settings for a Python Cloud Foundry app on IBM Cloud

0
votes

I have a working Python Cloud Foundry app on Bluemix / IBM Cloud that connects to, and otherwise works well with a DB2 on Cloud instance on Bluemix / IBM Cloud.

However, after long intervals (I haven't been able to measure the time), the connection to DB2 closes, and my queries fail. I could modify my code to check for this, but it would be great to be able to adjust the TCP keepalive settings. Something along the lines of this.

Would greatly appreciate any pointers. I'm not sure how one adjusts the client-side settings on a Python Cloud Foundry app.

Cheers.

2
pythondb2ibm-cloudcloud-foundrytcp-keepalive
Are you using explicit-transactions or autocommit? If you are using autocommit (i.e. not using transactions) , are you using pconnect persistent-connections? - mao
It seems like you're seeing a pooled connection that is getting timed out fail on re-use. Hmm.. Keepalive won't help those cases. Question is what is causing the connection to be closed? Db2 on Cloud does not have any idle connection thresholds. Perhaps a firewall some were in between? Also, are you using the free tier for Db2 on Cloud? That will help diagnose since there's different limitations on the free plan. - SilentSteel
@mao I changed my code yesterday to start using ibm_db.pconnect() instead of ibm_db.connect(). Hopefully that'll help. I use explicit transactions. I'll keep you posted as I monitor the app. - Deven
@SilentSteel Yes, I'm indeed using the free tier for DB2 on Cloud. I also bound the DB2 instance to my Cloud Foundry app once I noticed this timeout behavior. Not sure if that makes a difference. - Deven
I believe the pconnect is not suitable if you use explicit transactions, check documentation. - mao

2 Answers

1
votes

I tried reproducing this using a Python Flask Cloud Foundry app and a Db2 on Cloud Lite plan instance. My connections seem to stay alive for hours. It might be the python application you are running that has some kind of timeout.

1
votes

I could not find a satisfactory resolution. Like @jackic23 mentioned, there may be other factors at play. Few things:

  • The problem is hard to replicate
  • The app works fine on localhost, but not when deployed
  • I do have other simultaneous CRUD operations happening, which may be potentially conflicting somehow. There could be a race condition that somehow never happens on localhost.
  • My flask app is deployed using gunicorn, which was killing the worker thread after 30 seconds, so the DB connection terminated mid-query. I adjusted the timeout to 75 seconds, but then the query started returning in under 1 s.

At this point, I have switched to an Enterprise DB2 plan, and the app is working fine. To @jackic23's point, there may still be something else going on here (in my app's code) that may eventually need to be figured out.

For now, I'm moving on. Thank you @jackic23 for looking into this!