Swoole MySQL instance

Introduction to Swoole Asynchronous MySQL

$db = new Swoole\MySQL;
$server = array(
    'host' => '127.0.0.1',
    'user' => 'test',
    'password' => 'test',
    'database' => 'test',
);

$db->connect($server, function ($db, $result) {
    $db->query("show tables", function (Swoole\MySQL $db, $result) {
        if ($result === false) {
            var_dump($db->error, $db->errno);
        } elseif ($result === true) {
            var_dump($db->affected_rows, $db->insert_id);
        } else {
            var_dump($result);
            $db->close();
        }
    });
});

MySQL long connection

MySQL Short Connection Each request to operate the database requires a TCP connection to the MySQL server, which is time-expensive. T CP connections require 3 network communications. T his adds a certain amount of latency and additional IO consumption. The MySQL connection is turned off after the request ends, and 3/4 network traffic occurs.

The close operation does not increase the response delay because the close is automatically communicated by the operating system after theclose and is not available to the application

Long connections avoid the overhead of creating connections per request, saving time and IO consumption. Improved performance of PHP programs.

The disconnect is re-connected

In a cli environment, PHP programs need to run for long periods of time, and the TCP connection between the client and the MySQL server is unstable.

• MySQL-Server automatically disconnects the connection for a certain period of time
• PhP programs encounter idle periods when there is no MySQL query for a long time, MySQL-Server will also cut off the connection to recover resources
• In other cases, kill a connection by performing kill kill on the MySQL server, and the MySQL server restarts

The MySQL connection in the PHP program then fails. I f you still mysql_query, a "MySQL server has gone away" error is reported. T he program does not handle it and directly encounters a fatal error and exits. Therefore, the PHP program needs to be disconnected and re-connected.

Many people have come up mysql_ping a different approach, mysql_query connection detection or timed connection detection each time. T his scheme is not the best. The reason is

• mysql_ping need to actively detect connections, resulting in additional consumption
• Scheduled execution does mysql_ping solve the problem, such as when the mysql_ping has just been performed, the connection is closed

The best scenario is to re-connect the lines. Its principle is:

1. mysql_query return value is detected after execution
2. If mysql_query fails to return, the detection error code is found to be 2006/2013 (both errors indicate a connection failure), and the error is mysql_connect
3. After mysql_connect, re-execute the mysql_query, which is bound to succeed because the connection has been re-established
4. If mysql_query return is successful, the connection is valid, which is a normal call

You can refer swoole_framework code in the code

MySQL Asynchronous

MySQL asynchronous refers to driving MySQL connection events so that non-blocking IOs are programmed. Database operations do not block processes, and the corresponding logic is executed when MySQL-Server returns results.

There are a few things to note:

• Asynchronous MySQL does not save SQL execution time
• A MySQL connection can only execute one SQL at a time, and if asynchronous MySQL exists and then multiple MySQL connections must be created

Asynchronous MySQL does not improve performance in asynchronous callback programs. The biggest benefit of asynchronous is that it can be highly co-called, and if 10,000 requests are made, 10,000 MySQL connections need to be established, which puts a lot of pressure on MySQL-Server.

MySQL allocates resources based on the number of connections, and a connection needs to open a thread. A 1000 connection then requires 1000 threads to be maintained. As the number of threads increases, interthread switching consumes a lot of CPU resources
MySQL short connections do not cause this problem because short connections are released after they are used. MySQL-Server's connection resources are not consumed

Although the application layer code uses asynchronous callbacks to avoid its own blocking, the real bottleneck is actually the database server. Asynchronous MySQL also brings additional programming complexity, so asynchronous MySQL is not recommended unless it is required for a particular scenario.

If you insist on using asynchronous in your program, you must be in the form of an asynchronous MySQL-plus connection pool. After exceeding the required MySQL maximum connection, SQL requests should be queued instead of creating new connections to avoid a large number of complicity requests causing the MySQL server to crash.

MySQL connection pool

Connection pools can effectively reduce the MySQL-Server load. T he principle is that the connection pool uses a pattern of shared resources, such as 100 requests at a time, when not all requests are actually executing SQL queries. W ith 100 requests and 20 MySQL connections, you can meet your needs. When one request completes the database operation and begins a process such as template rendering, the database connection is released to other requests.

Connection pools are valuable only in very large applications. C ommon applications use the MySQL long connection scheme, creating a MySQL connection per php-fpm, with 100 php-fpm processes on each machine. I f there are 10 machines, the number of requests for each machine is 100. I n fact, you only need to create 1000 MySQL connections to meet the demand, the database pressure is not great. Even with 100 machines, hardware-configured storage servers are still affordable.

When hundreds or thousands of application servers are reached, MySQL servers need to maintain a hundred thousand levels of connectivity. T he pressure on the database can be very high. Connection pool technology can come in use and can greatly reduce the number of database connections.

The connection pool implemented by the swoole-based AsyncTask module is the perfect solution, with simple programming and no data synchronization and locking problems. Y ou can even share connection pools with multiple services. T he disadvantage is 1, the flexibility is not as good as multithreaded connection pool, can not dynamically increase or decrease the connection. 2, there is an overhead for inter-process communication.
Node .js/ngx_lua in multi-process mode, it is not possible to develop a true connection pool unless it is swoole_task the same way