# of DB connections in OpenStack services 2


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.

db connections

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


Leave a comment

Your email address will not be published. Required fields are marked *

2 thoughts on “# of DB connections in OpenStack services

  • Arne Wiebalck

    Thanks for this post Gorka!

    Concerning the math for the allowed connections on a DB backend: with N API servers the 440 from your example needs to be multiplied by N, right? Equally, in the case of Cinder, all scheduler services and the volume servers will also add connections (scheduler: 1 per service, volume: 1 per backend, I think), which should be taken into account when setting allowed_connections on the DB, no?

    Cheers,
    Arne

    • geguileo Post author

      Hi Arne,

      Yes, that is correct, the example of 440 was only for 1 server with 8 workers and with those configuration values.

      In the case of Cinder Scheduler we only have 1 process, so it will be max_pool_size + max_overflow connections, and for Cinder Volume with N different backends we’ll have N * (max_pool_size + max_overflow), since we fork one process per backend and the connection pool is not shared between different processes.

      Cheers,
      Gorka.