PHP develops asynchronous high-performance MySQL proxy server

The Ysql database assigns a thread to each client connection, so the connection is invaluable. Develop an asynchronous MySQL proxy server, the PHP application server can be long connected to this server, both to reduce the connection pressure of MySQL, but also keep PHP long connection to reduce the Connect/close network overhead.

This server takes into account the setting of the database connection pool size, distinguishes between free and busy, mysqli wire break reconnection, and sets up load protection. Based on Swoole extension development, the IO loop uses epoll, which is fully asynchronous and non-blocking, and can handle a large number of TCP connections.

The logic of the program is to create n MySQL connections at startup, receive SQL from the client, allocate 1 MySQL connections, and send SQL to the database server. Then wait for the database to return the query results. When the database returns results, it is sent to the corresponding client connection.

The core data structure is a 3 php array. Idle_pool is an idle database connection that is moved from Idle_pool to Busy_pool when there is a SQL request. When the database returns results, it is moved from Busy_pool to Idle_pool for use by new requests. If there is no idle database connection when the SQL request arrives, it is automatically added to the Wait_queue. Once a SQL complete operation is completed, the waiting request is automatically processed from the Wait_queue.

This is used for recycling. Because the entire server is asynchronous single-process single-threaded, no locks are required at all. And it's completely asynchronous and very efficient.

Of course, the code in this article, if used in the production environment, but also need to do more protection mechanisms and stress testing. Here we offer a solution to the problem.

Classdbserver{Protected$Pool_size=20;Protected$Idle_pool=Array ();Idle connectionProtected$Busy_pool=Array ();Work connectionsProtected$Wait_queue=Array ();Waiting for a requestProtected$Wait_queue_max=100;The maximum length of the wait queue, after which new requests are rejected/** * @var swoole_server * *Protected$ServfunctionRun () {$Serv=NewSwoole_server ("127.0.0.1",9509);$Serv->SetArray' 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 (); }functionOnStart ($Serv) {$This->serv=$Servfor ($I=0;$I<$This->pool_size;$I++) {$Db=Newmysqli;$Db->Connect' 127.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"; }functionOnsqlready ($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 $thi s->idle_pool[] = $db _res; unset ($this->busy_pool[$db _sock]); Here you can take out a wait 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 queue is not full if (count ($this->wait_queue) < $this->wait_queue_max) {$this->wait_queue[] = Array (' fd ' = $fd, ' sql ' = = $data, ); } else {$this->serv->send ($FD, "request too many, please try again later.");} else {$this->doquery ($FD, $data);}} function Doquery ($FD, $sql) {//Removed from the free pool $db = Array_pop ($this->idle_pool);/** * @var mysqli */$mysqli = $db [' mysqli ']; for ($i = 0; $i &lt ; 2; $i + +) {$result = $mysqli->query ($sql, Mysqli_async), if ($result = = = False) {if ($mysqli->errno = = or $mysqli ->errno = = 2006) {$mysqli->close (); $r = $mysqli->connect (); if ($r = = = True) continue;}} Break } $db [' fd '] = $FD; Join the working pool $this->busy_pool[$db [' db_sock ']] = $db; }} $server = new DBServer (); $server->run ();

Source: http://rango.swoole.com/archives/288

PHP develops asynchronous high-performance MySQL proxy server