Galera connection contention on full base deployment on Xeon E3

[Logan V]

On a full base deployment of all integrated services, database connection contention occurs when the controllers are single proc 4C/8T (ie. Xeon E3) CPUs.

The Galera role uses the computation of 100*CPU_Threads to determine the default value, but OSA requires more connections than this when running with default thread counts, default services on these CPUs. We should probably adjust the default max_connections calculation.

lsn-mc1016_galera_container-be4858d8 | SUCCESS | rc=0 >>
Uptime: 44426 Threads: 41 Questions: 392935 Slow queries: 0 Opens: 194 Flush tables: 1 Open tables: 188 Queries per second avg: 8.844

lsn-mc1022_galera_container-7bb56dee | SUCCESS | rc=0 >>
Uptime: 45736 Threads: 10 Questions: 235134 Slow queries: 0 Opens: 79 Flush tables: 1 Open tables: 73 Queries per second avg: 5.141

lsn-mc1023_galera_container-b9b9bb03 | SUCCESS | rc=0 >>
Uptime: 44337 Threads: 807 Questions: 7666716 Slow queries: 0 Opens: 312 Flush tables: 1 Open tables: 306 Queries per second avg: 172.919

Services deployed are here: https://github.com/continuouspw/openstack-ci-cloud/blob/60e0cc99f97300784dbdc9fb8ffb0e265d9a5296/openstack_deploy/openstack_user_config.yml#L55-L93

https://github.com/continuouspw/openstack-ci-cloud/commit/60e0cc99f97300784dbdc9fb8ffb0e265d9a5296 is being applied to the environment to increase the max connections count, and once that is complete I will reply back with updated data about how many connections are consumed.

[Logan V (loganv)]

Connections leveled off just over 800 on this cloud:

# ansible galera -m command -a "mysqladmin status"
Variable files: "-e @/etc/openstack_deploy/user_pending_upstream.yml -e @/etc/openstack_deploy/user_secrets.yml -e @/etc/openstack_deploy/user_variables.yml "
lsn-mc1022_galera_container-7bb56dee | SUCCESS | rc=0 >>
Uptime: 35750 Threads: 10 Questions: 245422 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 61 Queries per second avg: 6.864

lsn-mc1016_galera_container-be4858d8 | SUCCESS | rc=0 >>
Uptime: 35825 Threads: 37 Questions: 372664 Slow queries: 0 Opens: 203 Flush tables: 1 Open tables: 197 Queries per second avg: 10.402

lsn-mc1023_galera_container-b9b9bb03 | SUCCESS | rc=0 >>
Uptime: 3807 Threads: 842 Questions: 569072 Slow queries: 0 Opens: 218 Flush tables: 1 Open tables: 212 Queries per second avg: 149.480

[Jean-Philippe Evrard (jean-philippe-evrard)]

There are many ways to solve these issues, and some of them were discussed in the bug triage of today.

TL:DR; We'll need to write a spec for improvements on that side.

[Matthew Thode (prometheanfire)]

Not sure if this covers it

https://github.com/openstack/openstack-ansible-os_nova/commit/fcd582731b0fb75d3715c2b871266289e999a5e8

[Dmitriy Rabotyagov (noonedeadpunk)]

Yes, I think it should be covered with mentioned commit and topic https://review.opendev.org/q/topic:db-pooling can be tracked for other related changes.

These changes were released with Xena