Главная » Ошибки » Resolve «Too Many Connections» error when connecting to Amazon Aurora MySQL instance

Resolve «Too Many Connections» error when connecting to Amazon Aurora MySQL instance

Short description

If the client encounters a «Too Many Connections» error when you try to connect to an Amazon Aurora MySQL DB cluster or instance, this means that all available connections are in use by other clients. This is defined by the max_connections parameter.

You might see any of the following symptoms:

  • The DatabaseConnections metric in Amazon CloudWatch is close to or equal to the max_connections value for your Aurora MySQL DB instance.
  • The value for the max_connections parameter is higher than the available memory provisioned by the DB instance class for connections. Check for signs like a low FreeableMemory metric value in CloudWatch.
  • You get an ERROR 1040(): Too many connections error in the MySQL error log.

You can reach a max_connections value for the following reasons:

  • Sudden or gradual increase in the number of client/application connections to the DB instance. This has the following causes:
    • Increase in workload leading to increased connections.
    • Table/row level locking leading to an increase in the client/application connection.
  • Client/application not closing connections properly after the end of the operation.
  • Higher value for connection timeout parameters like wait_timeout and/or interactive_timeout that can lead to an increase in sleeping connections.

Before you resolve the max connection error, first view all threads that are currently running on your DB instance. Then, enable logging on your DB instance.

Show threads currently running on Aurora MySQL DB instance

The SHOW FULL PROCESSLIST command shows which threads are currently running on your DB instance. Log in to your DB instance, and then run the following query:

SHOW FULL PROCESSLISTG

You can also run the following query to get the same result set:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

Note: You must grant your user account the administration privilege for the MySQL PROCESS server to see all the threads running on a MySQL DB instance. Otherwise, SHOW PROCESSLIST shows only the threads associated with the MySQL account that you’re using. For more information, see the MySQL documentation for Privileges provided by MySQL.

Note: The SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST statements can negatively affect performance because they require a mutex.

Enable logging on Aurora MySQL DB instance

Enable logging on your Aurora MySQL DB instance by enabling general_log, slow_query_log or advanced auditing log parameters.

Change max_connections

The max_connections variable can be changed in two places:

Update the my.cnf file, so that the new value is used if the MySQL/MariaDB server is restarted.

Use the SET GLOBAL command to update the value on the running MySQL/MariaDB server. In this case, there is no need to restart MySQL/MariaDB, so you do not have to allow for any downtime of your database.

Display the number of connections in MySQL

To check the current number of max_connections log in to the MySQL/MariaDB command line client with the following command:

mysql -u root -p

Now, use the command:

SHOW variables;

This will output a list of all of the variables which are set for MySQL/MariaDB. Scroll up through the list to find the value for max_connections.

Update my.cnf file

Open the file /etc/my.cnf for editing with the command:

sudo nano /etc/my.cnf

Directly beneath the first line, you’ll see the following entry:

[mysqld]

Add a line to the entry:

max_connections=[desired new maximum number]

For example, to set max_connections to 200, the first two lines of your configuration file should look like this:

[mysqld] max_connections=200

Save and exit the file.

Choose a new maximum number of connections

The system variable max_connections determines the number of connections which MySQL/MariaDB will accept. The default value is 151 connections, which allows 150 normal connections plus one connection from the SUPER account. SUPER is a MySQL privilege that grants admin rights to the user.

The first thing to decide is what new maximum value you want to set for max_connections. There are several considerations to take into account when increasing the number of MySQL/MariaDB connections. The maximum number which can be supported by the system will depend on:

  • The amount of available RAM
  • How much RAM each connection takes (simple queries will require less RAM than more labor-intensive connections).
  • The acceptable response time.

According to the MySQL documentation, most Linux systems should be able to support 500 to 1,000 connections without difficulty.

HiDrive Cloud Storage with IONOS!

Based in Europe, HiDrive secures your data in the cloud so you can easily access it from any device!


Highly secure
Shared access
Available anywhere
Continue

What is the Maximum Value for max_connections on Linux?

The upper limit for how large you can configure max_connections is largely determined by the operating system in addition to the limit of 100,000 that MySQL sets. Linux has a limit called max open files, this is defined “per login” and says the maximum number of files a process can open. The default is 1024 (which you can see using ulimit -n).

Filed Under: mysql

What is MariaDB?

MariaDB is a popular database that the original developers made of MySQL. It is a community-developed, commercially supported fork of MySQL. Additionally, it intended to remain free and open-source software under the GNU General Public License. Finally, MariaDB focuses much of the development work on achieving feature parity with MySQL.

YouTube player

Changing the max_connections parameter (Temporarily)

To change the value of the system variable max_connections, the –max_connections option can be used. To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p
mysql> SET GLOBAL max_connections = 512;Note: It’s not necessary to have a client which is currently connected to the server disconnect to run this SQL statement as root. The server permits one connection more than the value of the max_connections. One connection is reserved for the root user or any other accounts that has SUPER privilege.

When the MySQL server daemon (mysqld) is restarted the above value will set back to the default value of 151. To make changes permanent use the below method.

How to show the maximum number of connections?

You can use the ‘SHOW variables’ SQL command to determine the max connections on your database.

SHOW VARIABLES LIKE ‘max_connections’;

Result:

What is the source of the database connections?

You can use the ‘SHOW processlist’  SQL command to show you which threads are currently running.

It will, for instance, provide the following details:

  • User – The MySQL user who issued the statement.
  • Host – The hostname of the client issuing the statement.
  • Command – The SQL command that is executed.

The ‘show processlist SQL command:

SHOW processlist

Result

What can cause too many database connections?

Too many database connections can be caused by simultaneous or old connections that are not released in time. However, there are many things that you can do to mitigate this type of issue.

Firstly, check your code. It could be that your code is experiencing a race condition. A race condition occurs normally during parallel program execution. For example, a Bash script that is executed via Cron can enter a race condition. You must therefore ensure your Bash script is not concurrently executed. Click here for an example.

Secondly, check your database. It is good to limit the maximum number of connections to your database from a specific user. Additionally, ensure the time out for connections is suitable to your needs. This error may also be a symptom of slow queries or bottlenecks.

Changing the max_connections parameter (Permanently)

A better method to change max_connections parameter would be to add this option to the options file (my.cnf or my.ini, depending on your system) so that it takes effect next time the server is restarted. Assuming you are using /etc/my.cnf file, add the below line to it.

# vi /etc/my.cnf
max_connections = 512

Now restart the mysqld daemon for the changes to take effect.

For CentOS/RHEL 6:

# service mysqld restart

For CentOS/RHEL 7:

# systemctl restart mysqld

What is MySQL?

MySQL is an open-source relational database management system that can run on UNIX, Linux, Windows, etc. Additionally, MySQL is free and open-source software under the terms of the GNU General Public License.

Resolution

Resolve the max connection error using one of the following methods:

  • Review existing connections and, if possible, terminate them to release connection pressure. For example, start by terminating connections in sleep state.
  • Increase the maximum number of connections to your DB instance.

Terminate existing connections on your DB instance

Terminate user sessions or queries currently running on your DB instance by running the rds_kill and rds_kill_query commands:

CALL mysql.rds_kill(thread-ID);
CALL mysql.rds_kill_query(thread-ID);

Increase the maximum connections to your DB instance

Increase the maximum number of connections to your DB instance using the following methods:

  • Scale the instance up to a DB instance class with more memory. Note: Scaling the DB instance class causes an outage.
  • Set a larger value for the max_connections parameter using a custom instance-level parameter group. Increasing the max_connections parameter doesn’t cause an outage, but if your DB instance is using a default parameter group, then change the parameter group to a custom parameter group. Changing the parameter group causes an outage. For more information, see Working with DB parameter groups.

Note: The maximum number of connections allowed to an Aurora MySQL DB instance is determined by the max_connections parameter in the instance-level parameter group for the DB instance. See the following example:

max_connections = GREATEST({log(DBInstanceClassMemory/805306368)*45},{log(DBInstanceClassMemory/8187281408)*1000})

Check the current value of the max_connections parameter for your DB instance. To do this, check the parameter group attached to your DB instance or by run the following query:

select @@max_connections;

The max_connections parameter has the following specifications:

  • Can be set on both the DB cluster and DB instance parameter group. However, instance-level parameter setting takes effect.
  • Allowed value is an integer in the range of 1-16000.
  • Dynamic in nature (no reboot is required to change this parameter value).

For more information on the default value of max_connections for each DB instance class available to Aurora MySQL, see Maximum connections to an Aurora MySQL DB instance.

Note: Aurora MySQL and Amazon Relational Database Service (Amazon RDS) MySQL DB instances have different amounts of memory overhead. The max_connections value can be different for Aurora MySQL and RDS MySQL DB instances that use the same instance class. The values listed apply only to Aurora MySQL.

What is the maximum number of connections your database will handle?

The maximum number of database connections permitted is calculated based on RAM, disk space, CPU cores, network latency, average database connection size, and more.

For example, MySQL and MariaDB are configured for 151 connections by default. Another example is AWS RDS MySQL, where the default maximum number of connections is the instance type.
For example:

  • t2.micro – max connections: 66
  • t2.small – max connections: 150
  • m3.medium – max connections: 296
  • t2.medium – max connections: 312
  • m3.large – max connections: 609
  • t2.large – max connections: 648
  • m4.large – max connections: 648
  • m3.xlarge – max connections: 1,237
  • r3.large – max connections: 1,258
  • m4.xlarge – max connections: 1,320
  • m2.xlarge – max connections: 1,412
  • m3.2xlarge – max connections: 2,492
  • r3.xlarge – max connections: 2,540

Solution

The maximum number of connections threads allowed for the server is contained in the system variable max_connections. The default value is 151. To see the value to which this variable is set, run the following SQL command:

$ mysql –u root –p
mysql> SHOW VARIABLES LIKE ‘max_connections’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| max_connections | 151 |
+——————+——-+

How to change the maximum number of connections?

There are two ways to increase or decrease the maximum number of connections.

Источники

  • https://aws.amazon.com/ru/premiumsupport/knowledge-center/aurora-mysql-max-connection-errors/
  • https://www.ionos.com/digitalguide/websites/web-development/solve-mysqlmariadb-too-many-connections-error/
  • https://www.thegeekdiary.com/mysql-error-too-many-connections-and-how-to-resolve-it/
  • https://anto.online/guides/determine-the-cause-of-too-many-connections-in-mysql-and-mariadb/
[свернуть]
Решите Вашу проблему!


×
Adblock
detector