Configure SQLAlchemy connection pool for production sized environments

[Dmitry Borodaenko]

Fuel should configure Nova, Neutron, Cinder, and Glance with SQLAlchemy connection pool size parameters (min_pool_size, max_pool_size, overflow etc.) increased from the OpenStack defaults to values suitable to fully utilize a typical server system with 48 hardware threads available (2 CPUS x 12 cores x 2 HT), e.g. 32 connections per service.

MySQL server connection limit (max_connections) should be increased to accomodate all services, e.g. 512 connections per server.

A configuration with all connections utilized simultaneously should be verified to confirm whether file descriptor limit for MySQL should be increased from the default of 1024 as well.

[Matthew Mosesohn (raytrac3r)]

This is related to my work on https://bugs.launchpad.net/fuel/+bug/1270875. I am trying to implement this in a way that scales with the # of CPU cores available to the system and amount of total memory available.

It makes little sense to set up 48 threads on a 1 core VM with 1.5gb memory, so I've been working on using facter facts to get these values correctly sized.

[Pavel Vaylov (pvaylov)]

Hi folks !

What variables do you recommend for following configuration ?

1 controller + 2 compute + 1 storage

Dual Intel E5-2690
128GB RAM
100GB RAID-5 OS volume
12TB RAID-5 VM storage volume
Dual Intel 10Gbps adapter

In nova-all.log on compute nodes a lot off errors like:
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/73634

[Matthew Mosesohn (raytrac3r)]

This bug is nearly completed. Targeting for 4.1.1

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/73634
Committed: https://git.openstack.org/cgit/stackforge/fuel-library/commit/?id=20fb06d44f8f5f6ae160b377b9bdb79f14a089d3
Submitter: Jenkins
Branch: master

commit 20fb06d44f8f5f6ae160b377b9bdb79f14a089d3
Author: Bogdan Dobrelya <email address hidden>
Date: Fri Feb 14 17:11:36 2014 +0200

    Tune sqlalchemy backend settings for OS on scale

    - Tune SQLAlchemy backend settings for Openstack (QueuePool related)
    - Evaluate sqlalchemy backend scaling parameters at osnailyfactor
    - Fix deprecated in I [DEFAULT] usage for database config
    - For Neutron use neutron_config[:database] as well
        * Ensure max_pool_size in [10, 30] limits (10 is config defaults)
        * Ensure max_overflow in [20, 60] limits (20 is config defaults)

    Closes-bug: #1274784
    Partial blueprint: optimize-db-performance-at-scale

    Change-Id: I0e843022f04f53150da52858280034aad6aee987

[Meg McRoberts (dreidellhasa)]

Marked as Resolved Issue in 5.0 Release Notes

[Michael Polenchuk (mpolenchuk)]

This commit has no effect.
Please consider the following fix:

- max_retries => $max_retries,
- max_pool_size => $max_pool_size,
- max_overflow => $max_overflow,
- idle_timeout => $idle_timeout,
+ max_retries => $::osnailyfacter::cluster_ha::max_retries,
+ max_pool_size => $::osnailyfacter::cluster_ha::max_pool_size,
+ max_overflow => $::osnailyfacter::cluster_ha::max_overflow,
+ idle_timeout => $::osnailyfacter::cluster_ha::idle_timeout,

[Bogdan Dobrelya (bogdando)]

This fix was commited for 5.0. @Michael, please elaborate which version of Fuel is affected with this bug again?

[Bogdan Dobrelya (bogdando)]

As it figured out, Michael had reproduced this issue with 5.1.1 so I update this bug status as confirmed for 5.1.2 and incomplete for other releases as it requires investigation.

[Michael Polenchuk (mpolenchuk)]

This bug has been caught up in 5.1+

[Bogdan Dobrelya (bogdando)]

The 6.1 release looks not affected: two-cpu testing environment got configured as
/etc/nova/nova.conf:max_overflow=10
/etc/keystone/keystone.conf:max_overflow=10
/etc/cinder/cinder.conf:max_overflow=10
/etc/glance/glance-registry.conf:sql_max_overflow=10
/etc/glance/glance-cache.conf:sql_max_overflow=10
/etc/glance/glance-api.conf:sql_max_overflow=10

and four-cpu one got all 20

[Bogdan Dobrelya (bogdando)]

QA team, please investigate either this bug affects 5.0, 5.1, 6.0 releases or not.

[Bogdan Dobrelya (bogdando)]

branch: stable/6.0
Review: https://review.openstack.org/151161

branch: stable/5.1
Review: https://review.openstack.org/151162

branch: stable/5.0
Review: https://review.openstack.org/151165

6.1 patch https://review.openstack.org/148522

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/6.1
Review: https://review.openstack.org/194961

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/194961
Committed: https://git.openstack.org/cgit/stackforge/fuel-docs/commit/?id=0e26e7d7cc153d179ec34985645dd23cdd239ddb
Submitter: Jenkins
Branch: stable/6.1

commit 5cc5f0c643aebecaf3bf4580535a3ea7c3334a6c
Author: Mike Scherbakov <email address hidden>
Date: Tue Jun 23 13:43:35 2015 -0700

    Removed streamlined patching backend pieces

    Change-Id: I955e76ccdbd12a9145f4e9b689f80bdf9fcaf929

commit 563c4b5c78ebfcb1f4f91047c2919f6270f9a1d4
Author: Mike Scherbakov <email address hidden>
Date: Tue Jun 23 13:30:30 2015 -0700

    Removed outdated patching guide

    Change-Id: I76180c277789ade9c5ebedd19fe2092847c0b7d9

commit 8d120c14bec1ab41d448683ad146a3053a57c4ee
Author: Irina Povolotskaya <email address hidden>
Date: Tue Jun 23 19:59:11 2015 +0300

    Add dual hypervisor ref arch into 6.1 docs

    Change-Id: I900c24c9de878eafadbfc995aa879b7f55737fac

commit feebd1592d3305b64bbdfd0bc5fe108190aef120
Author: OlgaGusarenko <email address hidden>
Date: Tue Jun 23 18:38:17 2015 +0300

    [OPs guide] Running Ceilometer section edits

    1. conf file extract is updated
    2. note is updated

    Closes-bug: 1467817
    Change-Id: I0217e164108e0ba6c1397045a5e57d13ff429223

commit 44a93f9dead7511a3461ec35248dbb689c81eafd
Author: OlgaGusarenko <email address hidden>
Date: Tue Jun 23 18:04:40 2015 +0300

    [RN6_1] Final changes

    1. capitalization
    2. 2014.2 to 2014.2.2
    3. general improvements

    Change-Id: I45057e90c90550559f66bc67ccdf97a559fd9000

commit bb41389cae58084285688853281516b659686422
Author: evkonstantinov <email address hidden>
Date: Tue Jun 23 16:45:35 2015 +0300

    Update patching decription

    Update patching description with
    the standard Linux commands.

    Change-Id: Ia1a8346639c468fdfce15a11d2430bf3a4731244

commit bf3018fae3f2e564413d33aba6cdebf8868f0b4e
Author: OlgaGusarenko <email address hidden>
Date: Tue Jun 23 15:55:49 2015 +0300

    [RN6_1] Clean up

    1. Rearranges sections
    2. Improves RST
    3. Changes titles order

    Change-Id: I6110bf515667d3d6ba08ad35ff5d593dbc96641e

commit 1c7e4457808e8f2d6c56fdf31252170972e444b9
Author: Maria Zlatkova <email address hidden>
Date: Tue Jun 23 15:26:28 2015 +0300

    Replaces VBOX screenshots

    This patch:
    - replaces VBOX screenshots
    - changes the link for Download Mirantis VirtualBox scripts
     to https://docs.mirantis.com/openstack/fuel/fuel-master/#downloads

    Change-Id: I58dede960c5c3355d39b07ff44b757403f6af02c
    Closes-Bug: #1467872

commit 0a568bf53fc0e25d1d692d5d74b4a7b4d983bbcc
Author: evkonstantinov <email address hidden>
Date: Tue Jun 23 14:01:55 2015 +0300

    6.1 --separate repos

    change wording and add links to the
    separate repos feature.

    Change-Id: Ib5d0778a0d8f1534f79ed2f553574cb69a3150b0

commit 95a188b21cbdd064d92696b7920e6a0105fe0c56
Author: Maria Zlatkova <email address hidden>
Date: Tue Jun 23 12:07:28 2015 +0300

    Corrects the output 'pcs status'

    Changes the example outputs to appropriate ones.

    Change-Id: Ib6d83...