IBM Cloud Docs
Managing MySQL Connections

Managing MySQL Connections

Connections to your IBM Cloud® Databases for MySQL deployment use resources, so consider how many connections you need when tuning your deployment's performance. MySQL uses a max_connections setting to limit the number of connections (and resources that are used by connections) to prevent runaway connection behavior from overwhelming your deployment's resources.

You can check the value of max_connections with your admin user and mysql. Check the max_connections with your admin user using a command like:

ibmclouddb=> SHOW max_connections;
 max_connections
-----------------
 200
(1 row)

How to calculate the MySQL max_connections Variable

max_connections is a configuration parameter in MySQL that determines the maximum number of concurrent connections that can be established with the database server.

MySQL max_connections basic formula

The basic formula for calculating max_connections is:

Available RAM = Global Buffers + (Thread Buffers x `max_connections`)

To retrieve a list of buffers and their values, use a command like:

SHOW VARIABLES LIKE '%buffer%';

MySQL Connection Limits

At provision, Databases for MySQL sets the maximum number of connections to your MySQL database to 200. Raise this value by Changing the MySQL Configuration.

Leave some connections available, as a number of them are reserved internally to maintain the state and integrity of your database.

Limit the number of simultaneous connections for any nonadmin account. For example, setting max_user_connections=3 restricts the user account to a maximum of three simultaneous connections.

Exceeding the connection limit for your deployment will negatively affect the health of your database and cause it to be unreachable by your applications. After the connection limit is reached, any attempts at starting a new connection result in an error.

FATAL: remaining connection slots are reserved for
non-replication superuser connections

Access information about connections to your deployment with the admin user, mysql, and SHOW GLOBAL STATUS.

mysql> SHOW GLOBAL STATUS;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 0          |
| Aborted_connects                  | 0          |
| Bytes_received                    | 155372598  |
| Bytes_sent                        | 1176560426 |
...
| Connections                       | 30023      |
| Created_tmp_disk_tables           | 0          |
| Created_tmp_files                 | 3          |
| Created_tmp_tables                | 2          |
...
| Threads_created                   | 217        |
| Threads_running                   | 88         |
| Uptime                            | 1389872    |
+-----------------------------------+------------+

To determine where the connections are going, break down the connections by database with the help of the threads_connected variable, using a command like:

mysql> show status where `variable_name` = 'Threads_connected';

To further investigate your connections, use the SHOW PROCESSLIST command:

SHOW [FULL] PROCESSLIST;

Ending MySQL Connections

Each connection to mysqld, the MySQL Server, runs in a separate thread and can be stopped with a processlist_id statement, using a command like:

KILL [CONNECTION | QUERY] processlist_id
  • KILL CONNECTION ends the connection that is associated with the processlist_id, after stopping any statement that the connection is running.
  • KILL QUERY ends the statement the connection is running, but leaves the connection itself intact.

For more information, see the MySQL Reference Manual KILL Statement.

End MySQL Connections

If your deployment reaches the connection limit or you are having trouble connecting to your deployment and suspect that a high number of connections is a problem, disconnect (or end) all of the connections to your deployment.

In the UI, on the Settings tab, there is a button to End Connections to your deployment. Use caution, as it disrupts anything that is connected to your deployment.

In the CLI, end connections to the deployment using a command like:

ibmcloud cdb deployment-kill-connections <deployment name or CRN>

You can also use the Cloud Databases API to perform the end all connections operation.

MySQL Connection Pooling

One way to prevent exceeding the connection limit and ensure that connections from your applications are being handled efficiently is through connection pooling.

MySQL Connectors enable you to connect and run MySQL statements from another language or environment, including ODBC, Java (JDBC), C++, Python, PHP, Perl, Ruby, and native C and embedded MySQL instances.

For example, connection pooling with MySQL Connector/J can increase performance while reducing overall usage. MySQL Connector/Python also allows for optimization by using the mysql.connector.pooling module.