Simple implementation of MySQL connection pool with Swoole

MySQL Connection pool

In traditional web site development, such as LNMP mode, the Nginx master process receives the request and then assigns it to multiple worker processes, and each worker process then links PHP-FPM's master process, PHP-FPM again according to the current situation to invoke its worker process and then process PHP, if you need MySQL, in connection with MySQL, this time, if there are 1000 requests to call over, you need to establish 1000 connections with MySQL. If the request reaches the level or millions, then the database pressure will be very large. Connection pooling technology can be useful, greatly reducing the number of database connections, but also reduce IO consumption.

Why can I reduce my IO consumption?

MySQL short connection each request operation database needs to establish a TCP connection with the MySQL server, which takes time overhead. The TCP connection requires 3 network traffic. This adds a certain amount of latency and additional IO consumption. The MySQL connection is closed after the request ends, and 3/4 network traffic occurs.

And the connection pool is the long-connected MySQL mode, will keep the connection with MySQL, reuse the connection for MySQL operation, thus saving the establishment of connection and disconnection consumption.

Why can I reduce the number of data connections?

The connection pool is maintained for several long connections, and when a new request arrives, if the connection pool is idle, it is allocated to the connection pool for processing, otherwise the subsequent database connection request is added to the wait queue.

using Swoole to implement a simple MySQL connection pool

<?php$serv = new Swoole_server (' 0.0.0.0 ', 9509); $serv->set (Array (' worker_num ' = =//worker Process Quantity ' Task_wo Rker_num ' +, the number of//task processes is the number of MySQL connections maintained); function my_onreceive ($serv, $FD, $from _id, $data) {echo "received data". $data.    Php_eol;    Taskwait is the delivery of a task, where the SQL statement is passed//and then blocked waiting for SQL to complete, and return the result $result = $serv->taskwait ($data); echo "Task End".    Php_eol;        if ($result!== false) {list ($status, $db _res) = Explode (': ', $result, 2); if ($status = = ' OK ') {//The database operation succeeded, execute the business logic code, this will automatically release the MySQL connection occupies//Send processing results to the client $serv->sen D ($FD, Var_export (unserialize ($db _res), true).        "\ n");        } else {$serv->send ($FD, $db _res);    } return; } else {$serv->send ($FD, "Error.    Task timeout\n "), or//if the return is false, the taskwait wait time-out, you can set the corresponding wait timeout}}function my_ontask ($serv, $task _id, $from _id, $sql) { echo "Start doing task ID:". $task _id.    Php_eol;    static $link = null; Hell:if ($link = = null) {$link = @mysqli_connect ("127.0.0.1", "root", "passwd", "Database");            if (! $link) {$link = null; $serv->finish ("ER:".            Mysqli_error ($link));        Return    }} $result = $link->query ($sql);                if (! $result) {//If the query fails if (In_array (Mysqli_errno ($link), [2013, 2006])) {//Error code 2013, or 2006, reconnect the database, re-execute SQL                $link = null;        Goto HELL; }else{$serv->finish ("ER:".            Mysqli_error ($link));        Return    }} if (Preg_match ("/^select/i", $sql)) {//If it is a select operation, returns an associative array $data = $result->fetch_assoc ();    }else{//otherwise directly returns the result $data = $result; } $serv->finish ("OK:".) Serialize ($data));//Call the Finish method to notify the worker process in the task process, and the posted task is completed//return "OK:". Serialize ($data);}    function My_onfinish ($serv, $task _id, $data) {echo "task completed";//taskwait does not trigger this: echo "Asynctask finish:connect.pid=". Posix_getpid (). Php_eol;} $serv->on (' Receive ', ' my_onreceive '); $servOn (' Task ', ' My_ontask '); $serv->on (' Finish ', ' my_onfinish '); $serv->start ();//Start server 

　　

Simple implementation of MySQL connection pool with Swoole