Open Stack

Tagging with oslo as this sounds related to oslo.db.

On 9/5/19 7:37 PM, Albert Braden wrote:
> After more googling it appears that max_pool_size is a maximum limit on 
> the number of connections that can stay open, and max_overflow is a 
> maximum limit on the number of connections that can be temporarily 
> opened when the pool has been consumed. It looks like the defaults are 5 
> and 10 which would keep 5 connections open all the time and allow 10 temp.
> 
> Do I need to set max_pool_size to 0 and max_overflow to the number of 
> connections that I want to allow? Is that a reasonable and correct 
> configuration? Intuitively that doesn't seem right, to have a pool size 
> of 0, but if the "pool" is a group of connections that will remain open 
> until they time out, then maybe 0 is correct?

I don't think so. According to [0] and [1], a pool_size of 0 means 
unlimited. You could probably set it to 1 to minimize the number of 
connections kept open, but then I expect you'll have overhead from 
having to re-open connections frequently.

It sounds like you could use a NullPool to eliminate connection pooling 
entirely, but I don't think we support that in oslo.db. Based on the 
error message you're seeing, I would take a look at 
connection_recycle_time[2]. I seem to recall seeing a comment that the 
recycle time needs to be shorter than any of the timeouts in the path 
between the service and the db (so anything like haproxy or mysql 
itself). Shortening that, or lengthening intervening timeouts, might get 
rid of these disconnection messages.

0: 
https://docs.openstack.org/oslo.db/stein/reference/opts.html#database.max_pool_size
1: 
https://docs.sqlalchemy.org/en/13/core/pooling.html#sqlalchemy.pool.QueuePool.__init__
2: 
https://docs.openstack.org/oslo.db/stein/reference/opts.html#database.connection_recycle_time

> 
> *From:* Albert Braden <Albert.Braden at synopsys.com>
> *Sent:* Wednesday, September 4, 2019 10:19 AM
> *To:* openstack-discuss at lists.openstack.org
> *Cc:* Gaëtan Trellu <gaetan.trellu at incloudus.com>
> *Subject:* RE: Nova causes MySQL timeouts
> 
> We’re not setting max_pool_size nor max_overflow option presently. I 
> googled around and found this document:
> 
> https://docs.openstack.org/keystone/stein/configuration/config-options.html 
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__docs.openstack.org_keystone_stein_configuration_config-2Doptions.html&d=DwMGaQ&c=DPL6_X_6JkXFx7AXWqB0tg&r=XrJBXYlVPpvOXkMqGPz6KucRW_ils95ZMrEmlTflPm8&m=3eF4Bv1HRQW6gl7II12rTTSKj_A9_LDISS6hU0nP-R0&s=0EGWx9qW60G1cxoPFCIv_G1-iXX20jKcC5-AwlCWk8g&e=>
> 
> Document says:
> 
> [api_database]
> 
> connection_recycle_time = 3600               (Integer) Timeout before 
> idle SQL connections are reaped.
> 
> max_overflow = None                                   (Integer) If set, 
> use this value for max_overflow with SQLAlchemy.
> 
> max_pool_size = None                                  (Integer) Maximum 
> number of SQL connections to keep open in a pool.
> 
> [database]
> 
> connection_recycle_time = 3600               (Integer) Timeout before 
> idle SQL connections are reaped.
> 
> min_pool_size = 1                                            (Integer) 
> Minimum number of SQL connections to keep open in a pool.
> 
> max_overflow = 50                                          (Integer) If 
> set, use this value for max_overflow with SQLAlchemy.
> 
> max_pool_size = None                                  (Integer) Maximum 
> number of SQL connections to keep open in a pool.
> 
> If min_pool_size is >0, would that cause at least 1 connection to remain 
> open until it times out? What are the recommended values for these, to 
> allow unused connections to close before they time out? Is 
> “min_pool_size = 0” an acceptable setting?
> 
> My settings are default:
> 
> [api_database]:
> 
> #connection_recycle_time = 3600
> 
> #max_overflow = <None>
> 
> #max_pool_size = <None>
> 
> [database]:
> 
> #connection_recycle_time = 3600
> 
> #min_pool_size = 1
> 
> #max_overflow = 50
> 
> #max_pool_size = 5
> 
> It’s not obvious what max_overflow does. Where can I find a document 
> that explains more about these settings?
> 
> *From:* Gaëtan Trellu <gaetan.trellu at incloudus.com 
> <mailto:gaetan.trellu at incloudus.com>>
> *Sent:* Tuesday, September 3, 2019 1:37 PM
> *To:* Albert Braden <albertb at synopsys.com <mailto:albertb at synopsys.com>>
> *Cc:* openstack-discuss at lists.openstack.org 
> <mailto:openstack-discuss at lists.openstack.org>
> *Subject:* Re: Nova causes MySQL timeouts
> 
> Hi Albert,
> 
> It is a configuration issue, have a look to max_pool_size 
> and max_overflow options under [database] section.
> 
> Keep in mind than more workers you will have more connections will be 
> opened on the database.
> 
> Gaetan (goldyfruit)
> 
> On Sep 3, 2019 4:31 PM, Albert Braden <Albert.Braden at synopsys.com 
> <mailto:Albert.Braden at synopsys.com>> wrote:
> 
>     It looks like nova is keeping mysql connections open until they time
>     out. How are others responding to this issue? Do you just ignore the
>     mysql errors, or is it possible to change configuration so that nova
>     closes and reopens connections before they time out? Or is there a
>     way to stop mysql from logging these aborted connections without
>     hiding real issues?
> 
>     Aborted connection 10726 to db: 'nova' user: 'nova' host: 'asdf'
>     (Got timeout reading communication packets)
>