Building a High‑Performance Asynchronous MySQL Proxy with Swoole

MySQL allocates a thread per client, making connections scarce. This article presents an asynchronous MySQL proxy server built with the Swoole extension, allowing PHP application servers to maintain long‑lived connections to the proxy, reducing MySQL load and network overhead.

The server creates a configurable pool of MySQL connections, distinguishes idle and busy connections, handles automatic reconnection, and includes load‑protection mechanisms. It runs on a single‑process, single‑thread event loop using epoll, so no locks are required.

Core data structures are three PHP arrays: $idle_pool (free connections), $busy_pool (in‑use connections), and $wait_queue (queued requests). When a request arrives, a connection is moved from idle to busy; results are returned to the client and the connection returns to idle. If no idle connections exist, the request is queued until one becomes available.

Below is the full implementation.

class DBServer
{
    protected $pool_size = 20;
    protected $idle_pool = array(); // idle connections
    protected $busy_pool = array(); // working connections
    protected $wait_queue = array(); // pending requests
    protected $wait_queue_max = 100; // max queue length

    protected $serv; // swoole_server

    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('Receive', array($this, 'onReceive'));
        $serv->start();
    }

    function onStart($serv)
    {
        $this->serv = $serv;
        for ($i = 0; $i < $this->pool_size; $i++) {
            $db = new mysqli;
            $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 . "]
";
    }

    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
";

        if ($result = $mysqli->reap_async_query()) {
            $ret = var_export($result->fetch_all(MYSQLI_ASSOC), true) . "
";
            $this->serv->send($fd, $ret);
            if (is_object($result)) {
                mysqli_free_result($result);
            }
        } else {
            $this->serv->send($fd, sprintf("MySQLi Error: %s
", mysqli_error($mysqli)));
        }
        // release mysqli object
        $this->idle_pool[] = $db_res;
        unset($this->busy_pool[$db_sock]);

        // handle waiting requests
        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 connections
        if (count($this->idle_pool) == 0) {
            // queue 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)
    {
        // take a connection from idle pool
        $db = array_pop($this->idle_pool);
        $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;
        // add to busy pool
        $this->busy_pool[$db['db_sock']] = $db;
    }
}

$server = new DBServer();
$server->run();

Note that production use requires additional safety checks and stress testing; the code here is intended as a conceptual demonstration.