About connection to database

[josejachuf]

Hi

my pool_size = 10

I always did the deployments using FreeBSD + PostgreSQL + Gunicorn + Supervisord and I never had the problem with the connections.

In this implementation, Linux and Docker were used and yesterday had more than 200 connections to the open server.

I understand that the pool_size guarantees a minimum pool of available connections and that as you increase concurrent requests more connections are created. I understand that the closure of the connections is automatic. How do you handle these Emmett (Pydal)?

The keep_alive_timeout parameter makes the time indicated kill the connection and create a new one?

How I said before I had been worried about the connections before, but in this implementation they seem to grow in quantity.

Jose

[gi0baro]

First of all, differently from pyDAL, Emmett uses its own connection manager, which in case of pooled connection is the PooledConnectionManager (https://github.com/emmett-framework/emmett/blob/master/emmett/orm/connection.py#L188). This is mainly because, even if Emmett's ORM is synchronous, the connection establishment when serving the application is asynchronous.

Also, the connection manager in Emmett ORM's is designed to fill the maximum size of the pool during first connection, for a couple of reasons:

• make the flow less blocking
• simplify the code and make the number of open connections more predictable

The connection manager then will close all the connections in the pool during the interpreter shutdown.

The keep_alive_timeout is the time (in seconds) after which a single connection of the pool should be considered as stale, and thus will be closed and removed from the pool (and theoretically a new connection will be established and will take place of the previous one in the pool). A couple of things to mind here:

• this timeout it IS NOT the TCP keepalive timeout, which is handled by both sides OSs
• the connection might actually kept open more than the timeout, since the evaluation of the open time is made during the connection opening

I personally set this to 3600 (1 hour) in order to ensure fresh connections and avoid consuming too much resources over a single connection during time.

Regarding your question, you have to mind the pool size is per process, so in order to calculate the number of effectively opened connections using docker, I should multiply:

• the pool size
• the number of workers
• the number of containers

So, for example, let's say I have a docker image when I serve my app using 2 workers and I spawn 4 containers for that, and I configured a pool size of 10 connections, then I will have:

10 * 2 * 4 = 80 connections

[josejachuf]

Thanks @gi0baro

[josejachuf]

Hi @gi0baro

This is evident:

The connection manager then will close all the connections in the pool during the interpreter shutdown.

What happens in the scenario you describe:

10 * 2 * 4 = 80 connections

If demand grows at a time and we have 150 open connections, but then low demand. Will they close themselves and will be the only 80 open?

Jose

[gi0baro]

@josejachuf I'm not sure I got the question. How do you reach 150 connections given the maximum I stated is 80?

In general, the only way to definitely close the connections in pool is the shutdown of the server process.

[josejachuf]

It was my mistake, I kept thinking about the Doc in Pydal [1] that says that the number of connections goes from the Pool_Size and the maximum number of concurrent requests.

The explanation was very clear. Thanks very much

[1] http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Connection-pooling