blob: 2f57fef2d3cbd8828ccc26946cbe365c3740b6ec (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
### Understand the alert
The `mysql_connections` alert indicates the percentage of used client connections compared to the maximum configured connections. When you receive this alert, it means your MySQL or MariaDB server is reaching its connection limit, which could lead to performance issues or failed connections for clients.
### Troubleshoot the alert
1. **Check the current connection usage**
Use the following command to see the current used and total connections:
```
mysql -u root -p -e "SHOW STATUS LIKE 'max_used_connections'; SHOW VARIABLES LIKE 'max_connections';"
```
This will display the maximum number of connections used since the server was started and the maximum allowed number of connections (`max_connections`).
2. **Monitor connections over time**
You can monitor the connection usage over time using the following command:
```
watch -n 1 "mysql -u root -p -e 'SHOW STATUS LIKE \"Threads_connected\";'"
```
This will update the number of currently connected threads every second.
3. **Identify connection-consuming processes**
If connection usage is high, check which processes or clients are using connections:
```
mysql -u root -p -e "SHOW PROCESSLIST;"
```
This gives you an overview of the currently connected clients, their states, and queries being executed.
4. **Optimize client connections**
Analyze the processes using connections and ensure they close their connections properly when done, utilize connection pooling, and reduce the number of connections where possible.
5. **Increase the connection limit (if necessary)**
If you need to increase the `max_connections` value, follow these steps:
- Log into MySQL from the terminal as shown in the troubleshooting section:
```
mysql -u root -p
```
- Check the current limit:
```
show variables like "max_connections";
```
- Set a new limit temporarily:
```
set global max_connections = "LIMIT";
```
Replace "LIMIT" with the desired new limit.
- To set the limit permanently, locate the `my.cnf` file (typically under `/etc`, but it may vary depending on your installation) and append `max_connections = LIMIT` under the `[mysqld]` section.
Replace "LIMIT" with the desired new limit, then restart the MySQL/MariaDB service.
### Useful resources
1. [How to Increase Max Connections in MySQL](https://ubiq.co/database-blog/how-to-increase-max-connections-in-mysql/)
2. [MySQL 5.7 Reference Manual: SHOW STATUS Syntax](https://dev.mysql.com/doc/refman/5.7/en/show-status.html)
3. [MySQL 5.7 Reference Manual: SHOW PROCESSLIST Syntax](https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html)
4. [MySQL 5.7 Reference Manual: mysqld – The MySQL Server](https://dev.mysql.com/doc/refman/5.7/en/mysqld.html)
|
