Workerman writes the example code of mysql connection pool

First of all, you need to understand why you use connection pools and what problems they can solve for you.

The main functions of the connection pool are:

1. Reduce the overhead of three-way handshakes to establish a TCP connection with the data server and four-way handshakes to close the connection, thereby reducing the load on the client and MySQL server and shortening the request response time

2. Reduce the number of concurrent connections to the database, that is, solve the problem of too many connections to the database caused by too many application servers

If it is to solve the problem 1

In workerman, database connection pool is not the most efficient method, but rather a troublesome approach. Since PHP is a single-process and single-threaded program, using PHP to implement a database connection pool requires a separate process. This will involve inter-process communication, which changes the original process of communicating directly with MySQL to communication with the connection pool and then to MySQL, increasing the load on the application side.

The most efficient way to solve problem 1 is to create a database singleton for each business process (such as the DB class provided by workerman) to implement database persistent connection. In this way, all requests of each process use its own database persistent connection. The entire process life cycle has only one TCP handshake and disconnection wave overhead. In addition, the application communicates directly with MySQL, without an intermediate layer of inter-process IPC communication like a connection pool. The performance is the highest, without a doubt.

If it is for question 2

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

If you have 1,000 application servers, a connection pool is necessary, but this connection pool cannot be a connection pool running on the local application server, because 1,000 application servers will have 1,000 connection pools. Even if each connection pool only opens 10 connections, the number of database connections will easily be full. So don't expect that opening a few task processes on the current server to implement a connection pool will solve this problem.

For a cluster of 1,000 application servers, it is also an unreliable method to implement a connection pool with several processes on each server. The real solution to problem 2 is to establish an independent database connection pool server or cluster to globally manage all database connections.

In summary,

If you want to implement PHP's MySQL connection pool just for problem 1, then a database singleton is a simpler and more efficient approach than a so-called connection pool.

If the purpose is to achieve question 2, then the business must be of a certain scale. If you really want to use workerman to make a separate connection pool cluster, the following is a simple approach. Create some task processes, each process creates a database connection, and the task process sends the SQL request after receiving 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, using Text protocol $task_worker = new Worker('Text://0.0.0.0:1234');

$task_worker->count = 64;

$task_worker->name = 'MysqlTask';

$task_worker->onMessage = function($connection, $sql)

{

   // Execute sql.... Get the result, omitted here....

   $sql_result = your_mysql_query($sql);

   // Send result $connection->send(json_encode($sql_result));

};

Call in workerman:

use \Workerman\Connection\AsyncTcpConnection;

 

// Establish an asynchronous connection with the remote connection pool service. The IP address is the IP address of the remote connection pool service. If it is a cluster, it is the IP address of the LVS service.

$sql_connection = new AsyncTcpConnection('Text://ip:1234');

// Send sql

$sql_connection->send("SELECT ... FROM .....");

// Get sql results asynchronously $sql_connection->onMessage = function($sql_connection, $sql_result)

{

   // Here we just print the result var_dump(json_decode($task_result));

};

// Execute asynchronous connection $sql_connection->connect();

The above is all the knowledge points about writing MySQL connection pool in workerman introduced this time. If you have any supplements, please contact the editor of 123WORDPRESS.COM.