Develop an asynchronous and high-performance MySQL proxy server _ MySQL based on Swoole extension

Develop an asynchronous High-Performance MySQL proxy server based on Swoole extension

MySQL database allocates a thread for each client connection, so the connection is very valuable. Develop an asynchronous MySQL proxy Server. The PHP application Server can connect to this Server for a long time, which reduces the connection pressure of MYSQL and keeps PHP persistent connections to reduce the network overhead of connect/close.

This Server takes into account the size of the database connection pool, distinguishes between idle and busy, mysqli reconnection, and sets load protection. Based on swoole extended development, io uses epoll cyclically, which is completely asynchronous and non-blocking and can cope with a large number of TCP connections.

The logic of the program is: Create N MySQL connections at startup. after receiving the SQL statement sent from the client, assign one MySQL connection and send the SQL statement to the database server. Then wait for the database to return the query results. When the database returns the result, it is connected to the corresponding client.

The core data structure is three PHP arrays. Idle_pool is an idle database connection. When an SQL request is sent, it is moved from idle_pool to busy_pool. When the database returns results, it moves from busy_pool to idle_pool for new requests. When an SQL request arrives, if there is no idle database connection, it will be automatically added to wait_queue. Once an SQL statement is completed, the waiting request is automatically retrieved from wait_queue for processing.

This applies cyclically. Because the entire server is an asynchronous single-process, single-thread, no locks are required at all. It is completely asynchronous and highly efficient.

Of course, if the code in this article is to be used in the production environment, more protection mechanisms and stress tests are required. Here, we only provide a solution to the problem.

Class DBServer {protected $ pool_size = 20; protected $ idle_pool = array (); // idle connection protected $ busy_pool = array (); // working connection protected $ wait_queue = array (); // waiting request protected $ wait_queue_max = 100; // maximum length of the waiting queue, when this parameter is exceeded, new requests will be rejected/*** @ var swoole_server */protected $ serv; function run () {$ serv = new swoole_server ("127.0.0.1", 9509 ); $ serv-> set (array ('worker _ num' => 1,); $ serv-> on ('workerstart', array ($ this, 'onstart'); // $ serv-> on ('connect ', array ($ this, 'onconnect'); $ serv-> on ('receive ', array ($ this, 'onreceive '); // $ serv-> on ('close', array ($ this, 'onclose ')); $ serv-> start ();} function onStart ($ serv) {$ this-> serv = $ serv; for ($ I = 0; $ I <$ this-> pool_size; $ I ++) {$ db = new mysqli; $ db-> connect ('2017. 0.0.1 ', 'root', 'root', 'test'); $ db_sock = swoole_get_mysqli_sock ($ db); swoole_event_add ($ db_sock, array ($ this, 'onsqlready'); $ this-> idle_pool [] = array ('mysqli' => $ db, 'DB _ sock '=> $ db_sock, 'fd '=> 0,);} echo "Server: start. swoole version is [". SWOOLE_VERSION. "]/n";} function onSQLReady ($ db_sock) {$ db_res = $ this-> busy_pool [$ db_sock]; $ mysqli = $ db_res ['mysqli']; $ fd = $ db_res ['fd ']; echo _ METHOD __. ": client_sock = $ fd | db_sock = $ db_sock/n"; if ($ result = $ mysqli-> reap_async_query ()) {$ ret = var_export ($ result-> fetch_all (MYSQLI_ASSOC), true ). "/n"; $ this-> serv-> send ($ fd, $ ret); if (is_object ($ result) {mysqli_free_result ($ result );}} else {$ this-> serv-> send ($ fd, sprintf ("MySQLi Error: % s/n", mysqli_error ($ mysqli )));} // release mysqli object $ this-> idle_pool [] = $ db_res; unset ($ this-> busy_pool [$ db_sock]); // Here you can retrieve a waiting request if (count ($ this-> wait_queue)> 0) {$ idle_n = count ($ this-> idle_pool ); for ($ I = 0; $ I <$ idle_n; $ I ++) {$ req = array_shift ($ this-> wait_queue ); $ this-> doQuery ($ req ['fd '], $ req [' SQL ']) ;}} function onReceive ($ serv, $ fd, $ from_id, $ data) {// no idle database connection if (count ($ this-> idle_pool) = 0) {// wait for the queue to be less than if (count ($ this-> wait_queue) <$ this-> wait_queue_max) {$ this-> wait_queue [] = array ('fd '=> $ fd,' SQL '=> $ data ,);} else {$ this-> serv-> send ($ fd, "request too handle, Please try again later. ") ;}} else {$ this-> doQuery ($ fd, $ data) ;}} function doQuery ($ fd, $ SQL) {// remove $ db = array_pop ($ this-> idle_pool) from the idle pool;/*** @ var mysqli */$ mysqli = $ db ['mysqli']; for ($ I = 0; $ I <2; $ I ++) {$ result = $ mysqli-> query ($ SQL, MYSQLI_ASYNC ); if ($ result = false) {if ($ mysqli-> errno = 2013 or $ mysqli-> errno = 2006) {$ mysqli-> close (); $ r = $ mysqli-> connect (); if ($ r === true) continue ;}} break ;}$ db ['fd '] = $ fd; // join the work Pool $ this-> busy_pool [$ db ['DB _ sock '] = $ db ;}$ server = new DBServer (); $ server-> run ();