Reduce the use of SQLAlchemy connection pooling

When the internal VIP is moved in the event of a failure of the active
controller, OpenStack services can become unresponsive as they try to
talk with MariaDB using connections from the SQLAlchemy pool.

It has been argued that OpenStack doesn't really need to use connection
pooling with MariaDB [1]. This commit reduces the use of connection
pooling via two configuration options:

- max_pool_size is set to 1 to allow only a single connection in the
  pool (it is not possible to disable connection pooling entirely via
  oslo.db, and max_pool_size = 0 means unlimited pool size)
- lower connection_recycle_time from the default of one hour to 10
  seconds, which means the single connection in the pool will be
  recreated regularly

These settings have shown better reactivity of the system in the event
of a failover.

[1] http://lists.openstack.org/pipermail/openstack-dev/2015-April/061808.html

Change-Id: Ib6a62d4428db9b95569314084090472870417f3d
Closes-Bug: #1896635
(cherry picked from commit c81772024c)

ansible/group_vars/all.yml

@@ -85,6 +85,8 @@ external_ntp_servers:
 database_address: "{{ kolla_internal_fqdn }}"
 database_user: "root"
 database_port: "3306"
+database_connection_recycle_time: 10
+database_max_pool_size: 1
 
 
 ####################

ansible/roles/aodh/templates/aodh.conf.j2

@@ -12,6 +12,8 @@ host = {{ api_interface_address }}
 
 [database]
 connection = mysql+pymysql://{{ aodh_database_user }}:{{ aodh_database_password }}@{{ aodh_database_address }}/{{ aodh_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [keystone_authtoken]
 memcache_security_strategy = ENCRYPT

ansible/roles/blazar/templates/blazar.conf.j2

@@ -40,6 +40,8 @@ memcached_servers = {% for host in groups['memcached'] %}{{ 'api' | kolla_addres
 
 [database]
 connection = mysql+pymysql://{{ blazar_database_user }}:{{ blazar_database_password }}@{{ blazar_database_address }}/{{ blazar_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [physical:host]

ansible/roles/cinder/templates/cinder.conf.j2

@@ -91,6 +91,8 @@ cafile = {{ openstack_cacert }}
 
 [database]
 connection = mysql+pymysql://{{ cinder_database_user }}:{{ cinder_database_password }}@{{ cinder_database_address }}/{{ cinder_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/cloudkitty/templates/cloudkitty.conf.j2

@@ -12,6 +12,8 @@ transport_url = {{ rpc_transport_url }}
 
 [database]
 connection = mysql+pymysql://{{ cloudkitty_database_user }}:{{ cloudkitty_database_password }}@{{ cloudkitty_database_address }}/{{ cloudkitty_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/cyborg/templates/cyborg.conf.j2

@@ -12,6 +12,8 @@ host = {{ api_interface_address }}
 
 [database]
 connection = mysql+pymysql://{{ cyborg_database_user }}:{{ cyborg_database_password }}@{{ cyborg_database_address }}/{{ cyborg_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [keystone_authtoken]
 memcache_security_strategy = ENCRYPT

ansible/roles/freezer/templates/freezer.conf.j2

@@ -55,6 +55,8 @@ driver = sqlalchemy
 
 [database]
 connection = mysql+pymysql://{{ freezer_database_user }}:{{ freezer_database_password }}@{{ freezer_database_address }}/{{ freezer_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 {% endif %}
 

ansible/roles/glance/templates/glance-api.conf.j2

@@ -32,6 +32,8 @@ image_cache_dir = /var/lib/glance/image-cache
 
 [database]
 connection = mysql+pymysql://{{ glance_database_user }}:{{ glance_database_password }}@{{ glance_database_address }}/{{ glance_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/gnocchi/templates/gnocchi.conf.j2

@@ -23,8 +23,9 @@ enable_proxy_headers_parsing = True
 
 [database]
 connection = mysql+pymysql://{{ gnocchi_database_user }}:{{ gnocchi_database_password }}@{{ gnocchi_database_address }}/{{ gnocchi_database_name }}
-max_pool_size = 50
+connection_recycle_time = {{ database_connection_recycle_time }}
 max_overflow = 1000
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if enable_gnocchi_statsd | bool %}

ansible/roles/heat/templates/heat.conf.j2

@@ -39,6 +39,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ heat_database_user }}:{{ heat_database_password }}@{{ heat_database_address }}/{{ heat_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/ironic/templates/ironic-inspector.conf.j2

@@ -52,6 +52,8 @@ policy_file = {{ ironic_policy_file }}
 
 [database]
 connection = mysql+pymysql://{{ ironic_inspector_database_user }}:{{ ironic_inspector_database_password }}@{{ ironic_inspector_database_address }}/{{ ironic_inspector_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [processing]
 ramdisk_logs_dir = /var/log/kolla/ironic-inspector

ansible/roles/ironic/templates/ironic.conf.j2

@@ -49,6 +49,8 @@ automated_clean=false
 
 [database]
 connection = mysql+pymysql://{{ ironic_database_user }}:{{ ironic_database_password }}@{{ ironic_database_address }}/{{ ironic_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if enable_keystone | bool %}

ansible/roles/karbor/templates/karbor.conf.j2

@@ -11,6 +11,8 @@ osapi_karbor_workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ karbor_database_user }}:{{ karbor_database_password }}@{{ karbor_database_address }}/{{ karbor_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [trustee]

ansible/roles/keystone/templates/keystone.conf.j2

@@ -17,6 +17,8 @@ policy_file = {{ keystone_policy_file }}
 
 [database]
 connection = mysql+pymysql://{{ keystone_database_user }}:{{ keystone_database_password }}@{{ keystone_database_address }}/{{ keystone_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if keystone_domain_directory.stat.exists %}

ansible/roles/magnum/templates/magnum.conf.j2

@@ -16,6 +16,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ magnum_database_user }}:{{ magnum_database_password }}@{{ magnum_database_address }}/{{ magnum_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if enable_cinder | bool %}

ansible/roles/manila/templates/manila.conf.j2

@@ -26,6 +26,8 @@ lock_path = /var/lib/manila/tmp
 
 [database]
 connection = mysql+pymysql://{{ manila_database_user }}:{{ manila_database_password }}@{{ manila_database_address }}/{{ manila_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/masakari/templates/masakari.conf.j2

@@ -16,6 +16,8 @@ nova_ca_certificates_file = {{ openstack_cacert }}
 
 [database]
 connection = mysql+pymysql://{{ masakari_database_user }}:{{ masakari_database_password }}@{{ masakari_database_address }}/{{ masakari_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/mistral/templates/mistral.conf.j2

@@ -34,6 +34,8 @@ host = {{ api_interface_address }}
 
 [database]
 connection = mysql+pymysql://{{ mistral_database_user }}:{{ mistral_database_password }}@{{ mistral_database_address }}/{{ mistral_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/monasca/templates/monasca-api/api.conf.j2

@@ -8,6 +8,8 @@ enable_logs_api = True
 [database]
 database = {{ monasca_database_name }}
 connection = mysql+pymysql://{{ monasca_database_user }}:{{ monasca_database_password }}@{{ monasca_database_address | put_address_in_context('url') }}:{{ monasca_database_port }}/{{ monasca_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [influxdb]
 database_name = {{ monasca_influxdb_name }}

ansible/roles/murano/templates/murano.conf.j2

@@ -16,6 +16,8 @@ agent_timeout = {{ murano_agent_timeout }}
 
 [database]
 connection = mysql+pymysql://{{ murano_database_user }}:{{ murano_database_password }}@{{ murano_database_address }}/{{ murano_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/neutron/templates/neutron.conf.j2

@@ -97,6 +97,8 @@ root_helper = sudo neutron-rootwrap /etc/neutron/rootwrap.conf
 
 [database]
 connection = mysql+pymysql://{{ neutron_database_user }}:{{ neutron_database_password }}@{{ neutron_database_address }}/{{ neutron_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/nova-cell/templates/nova.conf.j2

@@ -141,13 +141,16 @@ valid_interfaces = internal
 {% if not service_name.startswith('nova-compute') %}
 [database]
 connection = mysql+pymysql://{{ nova_cell_database_user }}:{{ nova_cell_database_password }}@{{ nova_cell_database_address | put_address_in_context('url') }}:{{ nova_cell_database_port }}/{{ nova_cell_database_name }}
-max_pool_size = 50
+connection_recycle_time = {{ database_connection_recycle_time }}
 max_overflow = 1000
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if service_name == 'nova-cell-bootstrap' or (service_name == 'nova-conductor' and nova_cell_conductor_has_api_database | bool) %}
 [api_database]
 connection = mysql+pymysql://{{ nova_api_database_user }}:{{ nova_api_database_password }}@{{ nova_api_database_address }}/{{ nova_api_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 {% endif %}
 {% endif %}

ansible/roles/nova/templates/nova.conf.j2

@@ -96,12 +96,15 @@ cafile = {{ openstack_cacert }}
 
 [database]
 connection = mysql+pymysql://{{ nova_cell0_database_user }}:{{ nova_cell0_database_password }}@{{ nova_cell0_database_address }}/{{ nova_cell0_database_name }}
-max_pool_size = 50
+connection_recycle_time = {{ database_connection_recycle_time }}
 max_overflow = 1000
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [api_database]
 connection = mysql+pymysql://{{ nova_api_database_user }}:{{ nova_api_database_password }}@{{ nova_api_database_address }}/{{ nova_api_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [cache]

ansible/roles/octavia/templates/octavia.conf.j2

@@ -25,6 +25,8 @@ client_cert = /etc/octavia/certs/client.cert-and-key.pem
 
 [database]
 connection = mysql+pymysql://{{ octavia_database_user }}:{{ octavia_database_password }}@{{ octavia_database_address }}/{{ octavia_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [service_auth]

ansible/roles/panko/templates/panko.conf.j2

@@ -13,6 +13,8 @@ connection = mongodb://{{ panko_database_user }}:{{ panko_database_password }}@{
 metering_connection = mongodb://{{ panko_database_user }}:{{ panko_database_password }}@{{ panko_database_mongodb_address }}/{{ panko_database_name }}
 {% elif panko_database_type == "mysql" %}
 connection = mysql+pymysql://{{ panko_database_user }}:{{ panko_database_password }}@{{ panko_database_mysql_address }}/{{ panko_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 metering_connection = mysql+pymysql://{{ panko_database_user }}:{{ panko_database_password }}@{{ panko_database_mysql_address }}/{{ panko_database_name }}
 {% endif %}
 

ansible/roles/placement/templates/placement.conf.j2

@@ -23,8 +23,9 @@ lock_path = /var/lib/placement/tmp
 
 [placement_database]
 connection = mysql+pymysql://{{ placement_database_user }}:{{ placement_database_password }}@{{ placement_database_address }}/{{ placement_database_name }}
-max_pool_size = 50
+connection_recycle_time = {{ database_connection_recycle_time }}
 max_overflow = 1000
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [cache]

ansible/roles/qinling/templates/qinling.conf.j2

@@ -16,6 +16,8 @@ host = {{ api_interface_address }}
 
 [database]
 connection = mysql+pymysql://{{ qinling_database_user }}:{{ qinling_database_password }}@{{ qinling_database_address }}/{{ qinling_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/rally/templates/rally.conf.j2

@@ -6,6 +6,8 @@ log_dir = /var/log/kolla/rally/
 
 [database]
 connection = mysql+pymysql://{{ rally_database_user }}:{{ rally_database_password }}@{{ rally_database_address }}/{{ rally_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if rally_policy_file is defined %}

ansible/roles/sahara/templates/sahara.conf.j2

@@ -12,6 +12,8 @@ use_rootwrap = True
 
 [database]
 connection = mysql+pymysql://{{ sahara_database_user }}:{{ sahara_database_password }}@{{ sahara_database_address }}/{{ sahara_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [keystone_authtoken]
 auth_url = {{ keystone_admin_url }}

ansible/roles/senlin/templates/senlin.conf.j2

@@ -27,6 +27,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ senlin_database_user }}:{{ senlin_database_password }}@{{ senlin_database_address }}/{{ senlin_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if service_name == 'senlin-engine' %}

ansible/roles/solum/templates/solum.conf.j2

@@ -40,6 +40,8 @@ host = {{ ansible_hostname }}_{{ item }}
 
 [database]
 connection = mysql+pymysql://{{ solum_database_user }}:{{ solum_database_password }}@{{ solum_database_address }}/{{ solum_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/tacker/templates/tacker.conf.j2

@@ -27,6 +27,8 @@ alarm_monitor_driver = ceilometer
 
 [database]
 connection = mysql+pymysql://{{ tacker_database_user }}:{{ tacker_database_password }}@{{ tacker_database_address }}/{{ tacker_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/trove/templates/trove-conductor.conf.j2

@@ -27,6 +27,8 @@ driver = noop
 
 [database]
 connection = mysql+pymysql://{{ trove_database_user }}:{{ trove_database_password }}@{{ trove_database_address }}/{{ trove_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 {% if enable_osprofiler | bool %}

ansible/roles/trove/templates/trove-taskmanager.conf.j2

@@ -37,6 +37,8 @@ username = {{ trove_keystone_user }}
 
 [database]
 connection = mysql+pymysql://{{ trove_database_user }}:{{ trove_database_password }}@{{ trove_database_address }}/{{ trove_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [oslo_messaging_notifications]

ansible/roles/trove/templates/trove.conf.j2

@@ -34,6 +34,8 @@ username = {{ trove_keystone_user }}
 
 [database]
 connection = mysql+pymysql://{{ trove_database_user }}:{{ trove_database_password }}@{{ trove_database_address }}/{{ trove_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 [keystone_authtoken]
 www_authenticate_uri = {{ keystone_internal_url }}

ansible/roles/vitrage/templates/vitrage.conf.j2

@@ -20,6 +20,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ vitrage_database_user }}:{{ vitrage_database_password }}@{{ vitrage_database_address }}/{{ vitrage_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 
 {% if vitrage_datasources %}
 [datasources]

ansible/roles/watcher/templates/watcher.conf.j2

@@ -14,6 +14,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ watcher_database_user }}:{{ watcher_database_password }}@{{ watcher_database_address }}/{{ watcher_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 [keystone_authtoken]

ansible/roles/zun/templates/zun.conf.j2

@@ -23,6 +23,8 @@ workers = {{ openstack_service_workers }}
 
 [database]
 connection = mysql+pymysql://{{ zun_database_user }}:{{ zun_database_password }}@{{ zun_database_address }}/{{ zun_database_name }}
+connection_recycle_time = {{ database_connection_recycle_time }}
+max_pool_size = {{ database_max_pool_size }}
 max_retries = -1
 
 # NOTE(yoctozepto): despite what the docs say, both keystone_auth and

releasenotes/notes/reduce-db-connection-pooling-b44da77eaa390f22.yaml

@@ -0,0 +1,6 @@
+---
+fixes:
+  - |
+    Reduce the use of SQLAlchemy connection pooling, to improve service
+    reliability during a failover of the controller with the internal VIP.
+    `LP#1896635 <https://bugs.launchpad.net/kolla-ansible/+bug/1896635>`__