The method and function of workerman writing mysql connection pool

First of all, you must understand why connection pooling is used and what problems the connection pool can solve for you

The main function of the connection pool:

1. Reduce the The data server establishes a three-way handshake for the TCP connection and closes the connection with the overhead of four handshakes, thereby reducing the load on the client and the mysql server, shortening the request response time

2. Reduce the number of concurrent connections to the database, which solves the problem of application server overload. The problem of too many connections in the database caused by too many

If it is to solve problem 1

, the database connection pool in Workerman is not the most efficient method, but it is asking for trouble. way of doing. Since PHP is a single process and single thread, using PHP to implement a database connection pool definitely requires a separate process, which will involve inter-process communication, making the original process of direct communication with mysql become a process with the connection pool and then again MySQL communication increases the load on the application side.

The most efficient way to solve problem 1 is to establish a database single instance (such as the DB class provided by Workerman) for each business process and implement a long connection to the database, so that all requests of each process use its own For a database long connection, there is only one TCP handshake and disconnection wave overhead in the entire process life cycle, and the application communicates directly with mysql. There is no intermediate layer of inter-process IPC communication like a connection pool, and the performance is the highest among them.

If it is for question 2

First check how many application servers you have and how many concurrent connections each server has to mysql. If you only have 10 application servers, each server has 50 processes, and each process has 1 database connection, then there will only be a total of 10*50=500 concurrent connections to the mysql server (not active connections), and 500 concurrent connections for mysql It's a piece of cake. In order to solve problem 2, there is no need to use a connection pool.

If you have 1,000 application servers, then a connection pool is necessary, but this connection pool cannot be a connection pool running on the local application server, because there are 1,000 connection pools for 1,000 application servers. Even if each connection pool only opens 10 connections, the number of connections in the database will be easily filled. So don't expect that opening a connection pool implemented by several task processes on the current server will solve this problem.

In a cluster of 1,000 application servers, setting up several processes on each server to implement connection pooling is also an unreliable method. The real way to solve problem 2 is to establish an independent database connection pool server or cluster to globally manage all database links.

To sum up,

If you are implementing PHP's mysql connection pool solely for question 1, then the database singleton is a simpler and more efficient approach than the so-called connection pool.

If it is to realize question 2, then the business must have a certain scale. If you really want to use Workerman to create a separate connection pool cluster, the following is a simple method, create some task processes, each The process creates a database connection. After the task process receives the SQL request, it sends it to the mysql server. After the mysql server returns, the task process sends the result to the SQL initiator.

The connection pool code is similar to the following. If it is a connection pool cluster composed of multiple servers, it is best to add a lvs in front:

// task worker，使用Text协议
$task_worker = new Worker('Text://0.0.0.0:1234');
$task_worker->count = 64;
$task_worker->name = 'MysqlTask';
$task_worker->onMessage = function($connection, $sql)
{
     // 执行sql.... 得到结果，这里省略....
     $sql_result = your_mysql_query($sql);
     // 发送结果
     $connection->send(json_encode($sql_result));
};

Call in workererman:

use \Workerman\Connection\AsyncTcpConnection;

// 与远程连接池服务建立异步链接，ip为远程连接池服务的ip，如果是集群就是lvs的ip
$sql_connection = new AsyncTcpConnection('Text://ip:1234');
// 发送sql
$sql_connection->send("SELECT ... FROM .....");
// 异步获得sql结果
$sql_connection->onMessage = function($sql_connection, $sql_result)
{
     // 这里只是打印结果
     var_dump(json_decode($task_result));
};
// 执行异步链接
$sql_connection->connect();

More For workerman knowledge, please pay attention to the workerman tutorial column on the PHP Chinese website.

The above is the detailed content of The method and function of workerman writing mysql connection pool. For more information, please follow other related articles on the PHP Chinese website!