Analysis of connection pool of golng mysql Library
0x1 background
Goang's protocol is useful, but sometimes the bottleneck is not in the language, but in the data sources behind it, such as mysql, redis, etc. When a backend serves many requests, the language can hold, but mysql produces errors, such as too many connections, too many time_wait, and so on. Today we will analyze how to solve this problem.
0x2 Code Example
Check main.go, halokid (start or follow if you can help, please. Thank you.)
0x3 analysis
Only the ini function is executed, and the process checking mysql is shown as (the original mysql is not processed by the process)
Before execution
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 2304 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 1315 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 3 rows in set (0.01 sec)
After execution
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 2284 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 1295 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | | 13 | root | 10.244.1.1:52134 | test | Sleep | 20 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 4 rows in set (0.00 sec)
You can see that after db.Ping() is executed, process has an additional Sleep connection, that is, a connection is put into the connection pool.
Function
db.SetMaxOpenConns(10) db.SetMaxIdleConns(5)
After two sentences, the connection pool has not changed, so the above logic takes effect only when the database processing logic is actually executed.
Execution of concurrent queries
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 4397 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 3408 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | | 19 | root | 10.244.1.1:54823 | test | Sleep | 952 | | NULL | | 20 | root | 10.244.1.1:54824 | test | Sleep | 1104 | | NULL | | 47 | root | 10.244.1.1:57906 | test | Sleep | 0 | | NULL | | 48 | root | 10.244.1.1:57909 | test | Sleep | 0 | | NULL | | 49 | root | 10.244.1.1:57912 | test | Sleep | 0 | | NULL | | 50 | root | 10.244.1.1:57907 | test | Sleep | 0 | | NULL | | 51 | root | 10.244.1.1:57908 | test | Sleep | 0 | | NULL | | 52 | root | 10.244.1.1:57913 | test | Sleep | 0 | | NULL | | 53 | root | 10.244.1.1:57911 | test | Sleep | 0 | | NULL | | 54 | root | 10.244.1.1:57910 | test | Sleep | 0 | | NULL | | 55 | root | 10.244.1.1:57915 | test | Sleep | 0 | | NULL | | 56 | root | 10.244.1.1:57914 | test | Sleep | 0 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 15 rows in set (0.00 sec)
Waiting after execution
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 3931 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 2942 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | | 19 | root | 10.244.1.1:54823 | test | Sleep | 486 | | NULL | | 20 | root | 10.244.1.1:54824 | test | Sleep | 638 | | NULL | | 32 | root | 10.244.1.1:56588 | test | Sleep | 22 | | NULL | | 33 | root | 10.244.1.1:56591 | test | Sleep | 22 | | NULL | | 34 | root | 10.244.1.1:56589 | test | Sleep | 22 | | NULL | | 35 | root | 10.244.1.1:56590 | test | Sleep | 22 | | NULL | | 36 | root | 10.244.1.1:56592 | test | Sleep | 22 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 10 rows in set (0.00 sec)
After the execution of the process
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 3941 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 2952 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | | 19 | root | 10.244.1.1:54823 | test | Sleep | 496 | | NULL | | 20 | root | 10.244.1.1:54824 | test | Sleep | 648 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 5 rows in set (0.00 sec)
We found that the maximum connection was controlled at 10, and that five connections remained after execution.
An important issue here is the expiration time of the connection pool.
In-depth analysis of 0x4 Let's set db. SetConnMax Lifetime (15 * time. Second) to 15 seconds for the lifetime of the connection pool, and we will find that after 15 seconds, the connection pool will be disconnected.
mysql> show processlist; +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ | 4 | event_scheduler | localhost | NULL | Daemon | 4987 | Waiting on empty queue | NULL | | 9 | root | 10.244.1.1:64000 | test | Sleep | 3998 | | NULL | | 10 | root | 10.244.1.1:64022 | test | Query | 0 | starting | show processlist | | 19 | root | 10.244.1.1:54823 | test | Sleep | 1542 | | NULL | | 20 | root | 10.244.1.1:54824 | test | Sleep | 1694 | | NULL | +----+-----------------+------------------+------+---------+------+------------------------+------------------+ 5 rows in set (0.00 sec)
Query the database again after 30 seconds
time.Sleep(30 * time.Second) rows, err := db.Query("select name from users") fmt.Println("err -----", err) defer rows.Close() for rows.Next(){ var name string rows.Scan(&name) fmt.Println("name---", name) }
At this point, the discovery program will relaunch the new db connection
Conclusion:
Connection life cycle of mysql server
Another condition is that our program's connection pool life cycle settings are larger than the mysql server's life cycle settings, at this time there will be a condition, if we reuse the connection pool, will produce There are two solutions to the problem of connection errors:
- You can set the life cycle time in the program to be less than the connection life cycle time of the mysql server.
- A keepalive mechanism for adding programs is to send a connection package server at regular intervals. As for point 2, we can diverge in the future. Generally, if permitted, we can use the first way.
mysql> show variables like 'mysqlx_wait_timeout'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | mysqlx_wait_timeout | 28800 | +---------------------+-------+ 1 row in set (0.00 sec)