Will the connection with max_overflow released after executed in OSLO_DB

[JinRong Cai]

Hi , All,

I am working on the NOVA with sqlalchemy.

And one problem is that I did not find any different with the DB configuration with max_pool_size/max_overflow:

example: /tmp/nova.conf

[database]
connection = postgresql://openstack:openstack@xxxx:5432/nova?application_name=nova
max_pool_size = 0
max_overflow=1
idle_timeout=60

When I use the model_query in the nova api, the session still not closed.

In my understand, the max_overflow means the session was returned to pool and will be closed also.

Here is my samples:

ctxt = context_module.get_admin_context()  ###### each session need one ctxt

@oslo_db_api.wrap_db_retry(max_retries=5, retry_on_deadlock=True)
@sqlalchemy_api.main_context_manager.reader
def get_instance_by_uuid_with_demorator(ctxt,uuid):
    # with sqlalchemy_api.main_context_manager.reader.using(ctxt):
    ctxt.session.query(func.pg_sleep(0.250)).scalar()
    result = sqlalchemy_api.model_query(ctxt,models.Instance,read_deleted="no").options(joinedload_all('security_groups.rules')). \
                options(joinedload('info_cache')). \
                filter(models.Instance.uuid == uuid).\
                first()
    return result

The session managemant was wrapped in the OSLO.DB module,  which is class TransactionContextManager.

My question is:

1. Do I need to invoke session.close() in my application code?

2. What the different between max_pool_size connections and max_overflow connections.

Thanks.

[mike bayer]

On 05/31/2017 06:05 AM, JinRong Cai wrote:
> Hi , All,
>
> I am working on the NOVA with sqlalchemy.
> And one problem is that I did not find any different with the DB
> configuration with max_pool_size/max_overflow:
>
> example: /tmp/nova.conf
>
> [database]
> connection =
> postgresql://openstack:openstack@xxxx:5432/nova?application_name=nova
> max_pool_size = 0
> max_overflow=1
> idle_timeout=60
>
> When I use the model_query in the nova api, the session still not closed.
> In my understand, the max_overflow means the session was returned to
> pool and will be closed also.

the pool settings and "the session is closed" are unrelated to each
other. Sessions only close when something calls .close() on them.

Now, as it happens, I am also the same person that wrote said logic in
oslo.db (though in general, if you have oslo.db questions you should be
posting on openstack-dev). So Nova uses "enginefacade", which is that
decorator you see with "context_manager.reader". That decorator, and
the context it's called within, handles calling session.close() for you.
you should not be calling it.

I think, what you're probably looking at, is TCP connections to the
database and you're thinking that the connections aren't being closed.
But the purpose of the connection pool is specifically to keep a few
connections from the application open, and ready for the next use. So
even when your method is done, Nova is still going to have database
connections opened. you don't need to worry about that.

>
>
> The session managemant was wrapped in the OSLO.DB module, which is
> class TransactionContextManager.
> My question is:
> 1. Do I need to invoke session.close() in my application code?

not when using the oslo.db enginefacade pattern.

> 2. What the different between max_pool_size connections and max_overflow
> connections.

max_pool_size is how many connections the pool will keep open when the
application is not doing anything. max_overflow is if the application
is doing something, and all the pooled connections are in use, then the
application still wants more connections. it will give at most
"max_overflow" additional connections out; when these are "returned" to
the pool, they close out again.

So given pool_size=5 max_overflow=10

[1 2 3 4 5] - these stay opened (however will periodically be replaced)

(6 7 8 9 10 11 12 13 14 15) - these will close as soon as you don't need
them, and reopen when someone needs one again

( ... < blocks until timeout> ... ) - everyone else has to wait for
connections within 1-15






>
> Thanks.
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.