Max Connection Tuning with MariaDB Enterprise Server

Overview

For basic on maximum connections, see "Max Connections".

Compatibility

The following details about maximum connection tuning pertain to:

  • Multi-Node Analytics

  • Replicated Transactions

  • Single Node Analytics

  • Single Node Transactions

Understanding Connection Usage

Database connections are used by clients, utilities, and applications that connect to the database. Automated processes such as monitoring, backups, and replication may also use connections.

The number of concurrent connections must be limited because certain database features trigger the per-connection allocation of memory.

While the MariaDB Enterprise Server max_connections system variable defines the maximum number of live connections, the back_log system variable defines the number of outstanding connections permitted; this limits the pace of new connections.

Since SkySQL users do not have the SUPER privilege, the extra connection (+1) typically reserved for administrative users in on-premises deployments of MariaDB Enterprise Server is not applicable.

Optimizing Connection Usage

Some tricks can be used to optimize connection usage:

  • Connection pooling and application architectural design choices can allow you to concurrently serve many more concurrent clients from a database service.

  • The Replicated Transactions topology separates read/write traffic and read-only traffic using dedicated TCP ports. With this topology, it is possible to exceed the stated single-server max connections limitation. Node failures may reduce total available connections for the SkySQL service.

    • Each connection to the read/write port transits MaxScale's read/write split router. These connections may be routed to the Primary server and some or all Replica servers, based on max_slave_connections default or Configuration Manager setting.

    • Each connection to the read-only port transits MaxScale's read connection router (readconnroute) and will be routed to a single Replica server (since MaxScale's router options are configured as router_options=slave).

  • Though the max_connections system variable in MariaDB Enterprise Server is not customer-tunable through SkySQL Configuration Manager, SkySQL Power Tier customers may contact us to discuss requirements.

Thread Pool

The thread pool can be used to improve performance in environments with a lot of connections. It offers the following benefits:

  • It minimizes context switching by partitioning threads into thread groups.

  • It automatically scales the number of threads up/down, based on need.

  • It re-uses old threads for new connections.

  • It detects when a connection is waiting on resources and ensures that the connection's thread doesn't unnecessarily use CPU.

  • It allows user to treat their connection as high priority, which would allow it to get CPU time earlier.

  • It detects when a connection is stalled, so that a single connection can't monopolize a thread group.

The thread pool can be enabled in the Configuration Manager by setting the following variables:

Memory Usage of Connections

Certain memory allocations occur on a per-connection basis. The instance size defines the amount of memory available. The default configuration for each service and instance size has been optimized to avoid out-of-memory conditions. However, users should ensure that their application does not increase the session value of any per-connection buffers, which could trigger out-of-memory conditions.

The following session system variables can affect per-connection memory usage:

Connection Problems

If your service hits the connection limit, new clients will fail to connect with the ER_CON_COUNT_ERROR error code:

ERROR HY000: Too many connections

Slow connect times to a service may be indicative of application-side issues, client-side TCP settings, DNS lookup times, high server load, or pace of connections which exceed the configured back_log.