How do I update the maximum number of connections to MariaDB database?

Solution Unverified - Updated

Environment

  • Red Hat Enterprise Linux Openstack Platform 5.
  • Red Hat Enterprise Linux Openstack Platform 6.
  • Red Hat Enterprise Linux Openstack Platform 7.
  • Red Hat Enterprise Linux Openstack Platform 8.
  • Red Hat Enterprise Linux Openstack Platform 9.

Issue

  • OpenStack APIs behind HAProxy respond very slowly or fail and the max_connections value should be updated. How can I increase maximum connections on the MariaDB/Galera database server?

Resolution

First identify correct max_connections limit applicable for your openstack deployment by following details in article How can I determine maximum number of connections required to MariaDB database for an Openstack deployment?

Then there are two ways to set the maximum_connections on the MariaDB servers.

During Deployment By Director


You can configure Openatack Director to set correct max_connections during deployment time by following below steps.

By Updating tuskar plan. Follow below steps.

$ openstack management plan set [tuskar_plan_uuid] -P "Controller-1::MysqlMaxConnections=x"

Replace x with the correct value and [tuskar_plan_uuid] with the appropriate plan's UUID, which you can find with:

$ openstack management plan list

and then select the appropriate tuskar_plan_uuid for use in the openstack management plan set command above.

To increase the max_connections value when deploying with the --templates argument, provide to the deploy command an additional customization environment file containing the following:

parameters:
  MysqlMaxConnections: x

Replace x with the correct value.

Add it to the overcloud deploy command using -e /path/to/custom_environment_file.yaml

After Deployment

If you do not set this during deployment, you can manually set this post deployment. This need to be done on each node.

# mysql
MariaDB [(none)]> set global max_connections=x;
Query OK, 0 rows affected (0.00 sec)

Verify that the changes have taken effect.

MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | x  |
+-----------------+-------+
1 row in set (0.00 sec)

To make the change permanent, edit /etc/my.cnf.d/galera.cnf on all controller nodes and configure below parameter to the correct value.

max_connections = x

The change will now survive a service restart.

Note that setting max_connections to the right value on the database server alone not enough. You should also set this value in haproxy.cfg by following steps in Why do some openstack API commands randomly time out and fail?

Root Cause

The number of connections to the database scaled depending on the number of Controllers and the cores of each Controller. The number of max_connections may not be suitable for the environment, and a higher number may be needed. This value can be determined by the process explained in How can I determine maximum number of connections required to MariaDB database for an Openstack deployment?

Diagnostic Steps

To find the current max_connections for a Controller, execute the following commands

# mysql -e 'show variables like "max_connections";'
# mysql -e 'SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS where VARIABLE_NAME="THREADS_CONNECTED";'
SBR
Product(s)
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.