The other day someone asked me if the SQLAlchemy connections to the DB where per worker or shared among all workers, and what was the number of connections that should be expected from an OpenStack service. Maybe you have also wondered about this at some point, wonder no more, here’s a quick write up summarizing it.

OpenStack services use Oslo DB library for database access, which in turn uses the SQLAlchemy library as the ORM, so we define our connection parameters in the [database] section of the service’s configuration file, and the service will pass them to Oslo DB and it will finally get passed to SQLAlchemy.

First we’ll go over the 2 Oslo DB configuration options that affect the number of connection:

• max_pool_size: Maximum number of SQL connections to keep open.
• max_overflow: The maximum number of sleeping SQL connections.

The way this works is, a service starts with no connections, and as DB connections are required by the queries SQLAlchemy opens them until it reaches max_pool_size connections, at this point, when we have reached this threshold, the number of connections remains and will not go below max_pool_size, even when these connections are not being used.

If more connections than max_pool_size are required we start using the “sleeping connections” until the number of “sleeping connections” reaches max_overflow. At this point all other queries requiring connections will be queued until one is available.

When a “sleeping connection” is freed and returned to the pool they are disconnected and discarded.

So the number of DB connections per process will go from 0 to max_pool_size + max_overflow. It is important to notice I say per process, because when we launch a service that spawns multiple processes -like an API service or a multi-backend Cinder-volume service- you will have an even broader range of connections, from 0 to num_processes * (max_pool_size + max_overflow).

For example, if we leave these configuration options with their defaults, 5 for max_pool_size and 50 for max_overflow, and we run an OpenStack API service with 8 workers –osapi_volume_workers in Cinder and osapi_compute_workers in Nova-, the number of DB connections will go from 0 to 440, resulting from this operation 8 * (5 + 50).

As far as I know min_pool_size, which is another Oslo DB configuration option available, has no effect in the number of DB connections.

It is important to be aware of these numbers, as we may need to adjust the number of allowed connections in the RDBMS server –MySQL and PostgreSQL– to avoid seeing “too many connections” errors in the logs.

Picture: “Flickr Tag Mapping” by Toby is licensed under CC BY-NC-ND 2.0