MySQL views the number of connections and status information

MySQL views the number of connections and status information

I. Problem Description

Today, I was asked by my O & M colleagues that the number of connections to two MYSQL databases has exceeded the monitoring threshold (700). Of course, their monitoring (zabbix) has just been set up. It indicates that the value has been adjusted from a very low value of 1.1 points, but the alarm is still reported. So I found out that as Oracle's DBA, I was prepared to become a dual-material DBA, which is a good time to learn. Now let's learn how to query the number of connections in MYSQL.

Ii. Experiment

1. show status to view all the status parameters, including the number of current Connections of Threads_connected and the total number of Connections that Connections tries to connect to (whether or not) the MYSQL server, the maximum number of connections (concurrency) that have been used by the Max_used_connections server after it is started ).

Mysql> show status like '% connect % ';

+ ---------------------- + --------- +

| Variable_name | Value |

+ ---------------------- + --------- +

| Aborted_connects | 163 |

| Connections | 1116123 |

| Max_used_connections | 266 |

| Threads_connected | 208 |

+ ---------------------- + --------- +

4 rows in set (0.00 sec)

Mysql>

2. show processlist: displays the mysql connection currently being executed

Mysql> show processlist;

+ --------- + ---------- + -------------------- + ----------- + --------- + ------- + ------------------ +

| Id | User | Host | db | Command | Time | State | Info |

+ --------- + ---------- + -------------------- + ----------- + --------- + ------- + ------------------ +

| 1105357 | tjuser | 10.10.100.30: 36210 | testdb | Sleep | 1377 | NULL |

| 1112435 | tjuser | 10.10.100.30: 54112 | testdb | Sleep | 1616 | NULL |

| ...... Omitted

| 1116128 | tjuser | 10.10.100.21: 47484 | testdb | Sleep | 1 | NULL |

| 1116129 | tjuser | 10.10.100.21: 47485 | testdb | Sleep | 64 | NULL |

+ --------- + ---------- + -------------------- + ----------- + --------- + ------- + ------------------ +

207 rows in set (0.00 sec)

3. mysqladmin-u-p-h status displays the current mysql status

[Root @ db ~] # Mysqladmin-uroot-p-hlocalhost status

Enter password:

Uptime: 14604445 Threads: 208 Questions: 34034734 Slow queries: 179 Opens: 12553 Flush tables: 3 Open tables: 977 Queries per second avg: 2.330

[Root @ db ~] #

4. mysqladmin-u-p-h extended-status displays other mysql statuses

[Root @ db ~] # Mysqladmin-uroot-p-hlocalhost extended-status

Enter password:

+ ------------------------------------------ + -------------- +

| Variable_name | Value |

+ ------------------------------------------ + -------------- +

| Aborted_client | 53041 |

| Aborted_connects | 163 |

| Connections | 1116157 |

...... Omitted

| Threads_connected | 206 |

| Threads_created | 633 |

| Threads_running | 1 |

| Uptime | 14604661 |

| Uptime_since_flush_status | 14604661 |

+ ------------------------------------------ + -------------- +

[Root @ db ~] #

Iii. Summary

For this query, you only need to know the command "show status" and "show process list" in mysql and the command "mysqladmin. The preceding command can be used to quickly obtain MYSQL database connection parameters and Status values. The number of connections set for the database is 1000. After the threshold value is modified, the database is normal.

It's never too late to be what you might have been.

This article permanently updates the link address: