B4J Question Does ConnectionPool work the same with postgres as mysql

J

j_o_h_n

Active Member
Licensed User
I am getting the error below every now and then from my server program. It is running on a windows server vps and the database I am using is postgres rather than mysql.


com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@4d154ccd -- timeout at awaitAvailable()
Click to expand...


When I get this error I have to restart the server program to get it working again.
This is happening during my testing when there is a very light load on the server, a remote app (ie on my computer) makes requests every 30 seconds while I am making the odd request as well from a browser.
The vps is entry level and has only 2GB of RAM and one virtual processor so maybe that might be an issue.

I don't think I am leaving any connections open, For all subs doing database access the pattern i use is



B4X: 
Private Sub etc
     Dim sql1 As SQL = Main.pool.GetConnection
     Dim rs as resultset
     Try
      'do stuff
    Catch
        Log(LastException.message) 
    End Try
    rs.Close
    sql1.Close
End Sub


So I was wondering if there could be an issue with connection pooling not working with postgres so well, the version of postgres I am using is 9.4
 
Sort by date Sort by votes

Erel

B4X founder
Staff member
Licensed User
Longtime User
ConnectionPool isn't tied to any specific database.

Add this to test whether you have a connection leak:
B4X: 
Dim jo As JavaObject = pool
jo.RunMethod("setDebugUnreturnedConnectionStackTraces", Array(True))
jo.RunMethod("setUnreturnedConnectionTimeout", Array(5)) '5 seconds

Make sure to remove when done.

 
Upvote 0
J

j_o_h_n

Active Member
Licensed User

Hi Erel
Thank you.
Just so I understand correctly.

I put this code in 'Sub AppStart', I think?
It will return connections to the pool that were still open after 5 seconds.
Then if my problem does not return this will tell me that I have a problem with my code (not closing connections) that I should fix and then remove this code.
 
Upvote 0
J

j_o_h_n

Active Member
Licensed User
Just for completeness, I did have a memory leak and it was caused by a return statement. Really basic error, sorry.
B4X: 
Private Sub etc
     Dim sql1 As SQL = Main.pool.GetConnection
     Dim rs as resultset
     Try
      'do stuff
      Return
    Catch
        Log(LastException.message)
    End Try
    rs.Close
    sql1.Close
End Sub
 
Upvote 0
J

j_o_h_n

Active Member
Licensed User
Erel said:
Did the settings in post #2 help you find it?
Click to expand...
Hi Erel actually I was going to run a new version with that in but before i did that, I looked in the statistics tab for the postgres server using the pgadmin3 admin program and
there were 15 connections listed for my database, 14 of which had the same 'current query' listed so I guessed that was the cause and sure enough just below that was a return statement.
But thank you very much I learned quite a bit from your post and reading up about setDebugUnreturnedConnectionStackTraces, etc as well as it not being a postgres issue
 
Upvote 0
You must log in or register to reply here.

Similar Threads

C
  • Question
B4J Question Mixing HTML and UDP connections in the same B4J server
Replies
1
Views
704
Erel
A
  • Solved
B4J Question Question about WebApps and Jetty.
Replies
5
Views
1K
Pendrush
P
E
  • Question
B4J Question MySql Connector not working when compiled with B4JPackager11
Replies
3
Views
820
Erel
W
  • Question
B4J Question Reconnect websockets
Replies
2
Views
1K
wl
W
  • Locked
  • Question
B4J Question Websocket Server - Client connections - JVM not releasing memory
2
Replies
37
Views
11K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…