GDB Modification of MySQL max_connections Without Restart

1. Background

In the day-to-day operations of MySQL, it's common to encounter issues caused by improper parameter settings. Today, we'll focus on a prevalent problem: When the business peak hits, this can lead to the database connection limit being reached.

Error Message

ERROR 1040 (HY000): Too many connections

‍

Cause of Error

This issue arises due to excessive concurrency exhaustion of the maximum connection limit.

Solution

Reconfigure the value of max_connections.

Pain Points

When the Too many connections error occurs, it's impossible to use the MySQL client for dynamic modification.

While restarting after modifying the configuration parameters can reset max_connections, this isn't feasible in a production environment with ongoing business operations.

2. GDB Tool

Given the aforementioned problems and pain points, let's explore the GDB tool, which allows us to modify MySQL parameters without restarting the service.

Although we won't delve into a detailed introduction of the GDB tool here, it's primarily used as a debugging tool in most scenarios. Today, we'll leverage its capability to modify configuration parameters online.

3.Processing Steps

Let's simulate a scenario where MySQL connections are exhausted, resulting in the Too many connections error, and walk through the resolution steps using the GDB tool.

1. Prepare a database instance with MySQL version 5.7.40.

2. Create an empty database named .

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

‍

3. Set the maximum connections for this instance to 10.

mysql> set global max_connections=10;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%max_conn%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 10    |
+--------------------+-------+
2 rows in set (0.00 sec)

‍

4. Install the GDB tool.

yum install -y gdb

‍

Installation Verification

[root@node1 ~]# gdb
GNU gdb (GDB) Red Hat Enterprise Linux 7.6.1-120.el7
Copyright (C) 2013 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later < http://gnu.org/licenses/gpl.html >
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see: < http://www.gnu.org/software/gdb/bugs/ >.
(gdb)
(gdb) q
[root@node1 ~]#

‍

5. Use sysbench for stress testing to simulate creating 11 tables in the empty database jiangshifeng, each with 20 million rows of data and 15 connection threads.

‍

sysbench /usr/share/sysbench/oltp_common.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --db-driver=mysql --tables=11 --table-size=20000000 --report-interval=15 --threads=15 prepare

‍

6. Now, attempting to connect to MySQL using the client tool will result in the error (Too many connections).

[root@node1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
[root@node1 ~]#

‍

7. Use the GDB tool to set the max_connections parameter.

Method 1

gdb -p $(pidof mysqld) -ex "set max_connections=50" -batch

‍

Method 2

[root@node1 ~]# ps -ef | grep mysql
root       1320      1  0 Mar17 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/DB/mysql --pid-file=/DB/mysql/node1.pid
mysql      1582   1320  9 Mar17 ?        03:33:44 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/DB/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=node1.err --pid-file=/DB/mysql/node1.pid --socket=/tmp/mysql.sock --port=3306

[root@node1 ~]# gdb -p 1582
...
(gdb) p max_connections
$1 = 10
(gdb)
(gdb) set max_connections=50
(gdb) p max_connections
$4 = 50
(gdb)
$5 = 50
(gdb) q
A debugging session is active.

	Inferior 1 [process 1582] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/local/mysql/bin/mysqld, process 1582
[Inferior 1 (process 1582) detached]
[root@node1 sysbench]#

‍

8. Re-login for verification.

[root@node1 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 146
Server version: 5.7.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like '%max_conn%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 50    |
+--------------------+-------+
2 rows in set (0.10 sec)

‍

At this point, the MySQL maximum connection parameter max_connections has been successfully modified. The previous sysbench stress test data continues to run normally, indicating minimal business impact.

Summary

• Although this article only demonstrates modifying the max_connections parameter without restarting the service using GDB, the same approach can be applied to other parameters.
• Despite the successful parameter modification observed in the experiment, there are inherent risks in such operations. It's crucial not to rely solely on these tools for absolute safety.
• Strict adherence to development standards is essential. Database issues are often caused by improper human operations. For instance, when setting the maximum connection number, we should assess the business's peak concurrency and set a value significantly higher than the peak to avoid connection exhaustion.
• Additionally, it's not advisable to set this parameter excessively high. A comprehensive evaluation of physical resources and stress testing is necessary to determine an appropriate value. Otherwise, abnormal deadlocks and continuous new connection requests during connection exhaustion may crash the database.