mysql连接数优化 SetMaxIdleConns 最大空闲连接数 query sleep状态

 

 

最大空闲连接数
Go\src\database\sql\sql.go

SetMaxIdleConns sets the maximum number of connections in the idle
connection pool.

If MaxOpenConns is greater than 0 but less than the new MaxIdleConns,
then the new MaxIdleConns will be reduced to match the MaxOpenConns limit.

If n <= 0, no idle connections are retained.

The default max idle connections is currently 2. This may change in
a future release

 

SetMaxOpenConns sets the maximum number of open connections to the database.

If MaxIdleConns is greater than 0 and the new MaxOpenConns is less than
MaxIdleConns, then MaxIdleConns will be reduced to match the new
MaxOpenConns limit.

If n <= 0, then there is no limit on the number of open connections.
The default is 0 (unlimited).

 

 

 

d, e := sql.Open(DBDriverName, DBDataSourceName)
if e != nil {
panic(e)
}
e = d.Ping()
if e != nil {
panic(e)
}
d.SetConnMaxIdleTime(60 * time.Minute)
d.SetMaxIdleConns(16)
d.SetMaxOpenConns(32)

u@u5:~/exe$ ss -atp | grep mysql | grep MyAPP |nl
1 ESTAB 0 0 10.123.1.45:47254 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=23))
2 ESTAB 0 0 10.123.1.45:47512 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=29))
3 ESTAB 0 130 10.123.1.45:47374 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=31))
4 ESTAB 0 0 10.123.1.45:46020 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=42))
5 ESTAB 0 0 10.123.1.45:46162 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=44))
6 ESTAB 0 0 10.123.1.45:45256 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=19))
7 ESTAB 0 0 10.123.1.45:45944 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=38))
8 ESTAB 0 0 10.123.1.45:45400 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=34))
9 ESTAB 0 0 10.123.1.45:49110 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=7))
10 ESTAB 0 0 10.123.1.45:49116 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=11))
11 ESTAB 0 0 10.123.1.45:46018 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=41))
12 ESTAB 0 0 10.123.1.45:45916 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=28))
13 ESTAB 0 0 10.123.1.45:45262 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=27))
14 ESTAB 0 0 10.123.1.45:49134 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=14))
15 ESTAB 0 0 10.123.1.45:47232 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=21))
16 ESTAB 0 0 10.123.1.45:47234 10.123.1.45:mysql users:(("MyAPP",pid=6719,fd=22))
u@u5:~/exe$

State Recv-Q Send-Q Local Address:Port Peer Address:Port

 

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show processlist;
+----------+-----------------+----------------------+------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+----------------------+------+---------+---------+------------------------+------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1614134 | Waiting on empty queue | NULL |
| 12871463 | root | localhost | db1 | Query | 0 | starting | show processlist |
| 12873174 | root | localhost:43654 | db1 | Sleep | 0 | | NULL |
| 12873176 | root | localhost:43658 | db1 | Sleep | 0 | | NULL |
| 12873183 | root | localhost:43694 | db1 | Sleep | 0 | | NULL |
| 12873185 | root | localhost:43698 | db1 | Sleep | 0 | | NULL |
| 12873186 | root | localhost:43700 | db1 | Sleep | 0 | | NULL |
| 12873188 | root | localhost:43704 | db1 | Sleep | 0 | | NULL |
| 12873190 | root | localhost:43708 | db1 | Sleep | 0 | | NULL |
| 12873191 | root | localhost:43710 | db1 | Sleep | 0 | | NULL |
| 12873192 | root | localhost:43712 | db1 | Sleep | 0 | | NULL |
| 12873194 | root | localhost:43716 | db1 | Sleep | 0 | | NULL |
| 12873198 | root | localhost:43722 | db1 | Sleep | 0 | | NULL |
| 12873199 | root | localhost:43726 | db1 | Sleep | 0 | | NULL |
| 12873200 | root | localhost:43728 | db1 | Sleep | 0 | | NULL |
| 12873202 | root | localhost:43732 | db1 | Sleep | 0 | | NULL |
| 12873205 | root | localhost:43738 | db1 | Sleep | 0 | | NULL |
| 12873206 | root | localhost:43740 | db1 | Sleep | 0 | | NULL |
+----------+-----------------+----------------------+------+---------+---------+------------------------+------------------+
46 rows in set (0.00 sec)

root@server:/usr/local/db1/APP# ss -atp | grep mysql |grep APP|nl
1 ESTAB 0 0 127.0.0.1:43704 127.0.0.1:mysql users:(("APP",pid=32495,fd=52))
2 ESTAB 0 0 127.0.0.1:43694 127.0.0.1:mysql users:(("APP",pid=32495,fd=39))
3 ESTAB 0 0 127.0.0.1:43710 127.0.0.1:mysql users:(("APP",pid=32495,fd=56))
4 ESTAB 0 0 127.0.0.1:43698 127.0.0.1:mysql users:(("APP",pid=32495,fd=28))
5 ESTAB 0 0 127.0.0.1:43700 127.0.0.1:mysql users:(("APP",pid=32495,fd=30))
6 ESTAB 0 0 127.0.0.1:43658 127.0.0.1:mysql users:(("APP",pid=32495,fd=20))
7 ESTAB 0 0 127.0.0.1:43728 127.0.0.1:mysql users:(("APP",pid=32495,fd=65))
8 ESTAB 0 0 127.0.0.1:43712 127.0.0.1:mysql users:(("APP",pid=32495,fd=57))
9 ESTAB 0 0 127.0.0.1:43654 127.0.0.1:mysql users:(("APP",pid=32495,fd=9))
10 ESTAB 0 0 127.0.0.1:43740 127.0.0.1:mysql users:(("APP",pid=32495,fd=72))
11 ESTAB 0 0 127.0.0.1:43738 127.0.0.1:mysql users:(("APP",pid=32495,fd=71))
12 ESTAB 0 0 127.0.0.1:43716 127.0.0.1:mysql users:(("APP",pid=32495,fd=59))
13 ESTAB 0 0 127.0.0.1:43722 127.0.0.1:mysql users:(("APP",pid=32495,fd=63))
14 ESTAB 0 0 127.0.0.1:43708 127.0.0.1:mysql users:(("APP",pid=32495,fd=55))
15 ESTAB 0 0 127.0.0.1:43726 127.0.0.1:mysql users:(("APP",pid=32495,fd=53))
16 ESTAB 0 0 127.0.0.1:43732 127.0.0.1:mysql users:(("APP",pid=32495,fd=67))
root@server:/usr/local/db1/APP#

 

 

mysql> show processlist;
+----------+-----------------+----------------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+----------------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 1620883 | Waiting on empty queue | NULL |
| 12871463 | root | localhost | db1 | Query | 0 | starting | show processlist |
| 12873174 | root | localhost:43654 | db1 | Sleep | 0 | | NULL |
| 12873176 | root | localhost:43658 | db1 | Sleep | 0 | | NULL |
| 12873183 | root | localhost:43694 | db1 | Query | 0 | query end | INSERT INTO `report_status` (`OnlineTime`, `IPV4`, `TaskType`, `CinemaNO`) VALUES (NOW(), "10.12 |
| 12873185 | root | localhost:43698 | db1 | Sleep | 0 | | NULL |
| 12873186 | root | localhost:43700 | db1 | Sleep | 0 | | NULL |
| 12873188 | root | localhost:43704 | db1 | Sleep | 0 | | NULL |
| 12873190 | root | localhost:43708 | db1 | Sleep | 0 | | NULL |
| 12873191 | root | localhost:43710 | db1 | Sleep | 0 | | NULL |
| 12873192 | root | localhost:43712 | db1 | Sleep | 0 | | NULL |
| 12873194 | root | localhost:43716 | db1 | Sleep | 0 | | NULL |
| 12873198 | root | localhost:43722 | db1 | Sleep | 0 | | NULL |
| 12873199 | root | localhost:43726 | db1 | Sleep | 0 | | NULL |
| 12873200 | root | localhost:43728 | db1 | Sleep | 0 | | NULL |
| 12873202 | root | localhost:43732 | db1 | Sleep | 0 | | NULL |
| 12873205 | root | localhost:43738 | db1 | Sleep | 0 | | NULL |
| 12873206 | root | localhost:43740 | db1 | Query | 0 | query end | INSERT INTO `report_status` (`OnlineTime`, `IPV4`, `TaskType`, `CinemaNO`) VALUES (NOW(), "10.12 |
| 12873268 | root | 112.95.245.252:13798 | db1 | Sleep | 7737 | | NULL |
| 12873293 | root | 112.95.245.252:20403 | db1 | Sleep | 5 | | NULL |

 

 

MySQL :: MySQL 8.0 Reference Manual :: 8.14.2 Thread Command Values https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

Query

Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.

Sleep

The thread is waiting for the client to send a new statement to it.

 MySQL :: MySQL 8.0 Reference Manual :: 8.14.3 General Thread States https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

 

query end

This state occurs after processing a query but before the freeing items state.

freeing items

The thread has executed a command. This state is usually followed by cleaning up.

end

This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.

For the end state, the following operations could be happening:

Writing an event to the binary log

Freeing memory buffers, including for blobs

executing

The thread has begun executing a statement.

 

 

 

 

 

 

 

golang数据库连接池参数设置 - gitfong - 博客园 https://www.cnblogs.com/gitfong/p/13722204.html