web2py - didn't disconect from postgreSQL auth database (TIME_WAIT)

[Krzysztof Jeleń]

Hello

I'm using web2py (Version 2.14.6-stable+timestamp.2016.05.09.19.18.48). Last week I've start getting strange errors on client side ("waiting for available socket") - I'm using Chrome. After investigation I found that DAL (PostgreSQL) object didn't close connection to database after finishing request:

netstat -p -n -o | grep "<DATABASE_PORT_NUMBER>"

return a lot of entries like (~300):

tcp        0      0 127.0.0.1:36062         127.0.0.1:5434          TIME_WAIT   -                    timewait (24.30/0/0)

DAL object is created in models/db.py and then it is passed to Auth constructor. When there is no incoming requests to application after some time (60s) every TIME_WAIT socket is closed and everything looks fine, When requests start comming then number of TIME_WAIT sockets start growing and stay about 300.

Is there a way to call close on underlaying postgresql connection layer?

Is there a way to invoke some code every time processing data is finished for each request (after server proces user quest and before start sending it to browser)?

or maybe that was fixed in latest version

BR

  KJ

[Dave S]

On Monday, May 6, 2019 at 8:29:48 AM UTC-7, Krzysztof Jeleń wrote:

Hello

I'm using web2py (Version 2.14.6-stable+timestamp.2016.05.09.19.18.48).

How long have you been using this version?  What OS and what front end and what version of Python?

 

Last week I've start getting strange errors on client side

What changed on your server?

 

("waiting for available socket") - I'm using Chrome. After investigation I found that DAL (PostgreSQL) object didn't close connection to database after finishing request:

I've seen issues with Chrome talking to a local instance of web2py (on W10)  using Rocket, and Chrome seems to get into a state where something cache-related is driving a lot of requests into Rocket; Usually closing Chrome clears things up (actually, closing one window of Chrome is enough, and sometimes it seems to work to close just one tab -- if I get the right one; Chrome uses multiple processes, so windows are independent, and tabs may be also).  In this environment, I'm also using 2.14.6, though I've been trialing newer versions.

[Krzysztof Jeleń]

I'm using web2py (Version 2.14.6-stable+timestamp.2016.05.09.19.18.48).

How long have you been using this version?  What OS and what front end and what version of Python?

3 years currently, python version is 2.7.5, Centos 7.5.1804, kernel 3.10.0

 

Last week I've start getting strange errors on client side

What changed on your server?

I've increase number of applications connected to this service and then Chrome (user page) start showing "waiting for available socket..." (and page is not loaded). Applications use mostly wget and some of them QNetworkAccessManager (C++ Qt component for requests handling).

 

 

("waiting for available socket") - I'm using Chrome. After investigation I found that DAL (PostgreSQL) object didn't close connection to database after finishing request:

 

I've seen issues with Chrome talking to a local instance of web2py (on W10)  using Rocket, and Chrome seems to get into a state where something cache-related is driving a lot of requests into Rocket; Usually closing Chrome clears things up (actually, closing one window of Chrome is enough, and sometimes it seems to work to close just one tab -- if I get the right one; Chrome uses multiple processes, so windows are independent, and tabs may be also).  In this environment, I'm also using 2.14.6, though I've been trialing newer versions.

Current;y I'm not sure is this only Chrome issue, the problem is that using DAL withput calling explicity DAL close method leave a lot of sockets in wating state on server side. I was able to reduce its number to 30 (from ~300) adding excplicity call to close method and removing DAL connection for some requests which didn't need database (or I use my own db abstraction layer with close mthod called explicity on postgreSQL connection). From that point of view I'm pretty user that this issue is kind of DAL error.

[Dave S]

On Monday, May 13, 2019 at 3:18:35 AM UTC-7, Krzysztof Jeleń wrote:

[...]

 

Current;y I'm not sure is this only Chrome issue, the problem is that using DAL withput calling explicity DAL close method leave a lot of sockets in wating state on server side. I was able to reduce its number to 30 (from ~300) adding excplicity call to close method and removing DAL connection for some requests which didn't need database (or I use my own db abstraction layer with close mthod called explicity on postgreSQL connection). From that point of view I'm pretty user that this issue is kind of DAL error.

 

Considering  how many different db.py files are out in the wild, including many production systems, and that we haven't seen much evidence of this before, I'd be surprised if it was purely a DAL error..

Have you tried wireshark or tcpdump on any of these to see what's happening in terms of handshakes, etc?

/dps