Mysqli extension, pdo_mysql extension, is officially provided. PHP operating mechanism is the page execution will release all the PHP process in all resources, if there are multiple concurrent access to the local test page http://127.0.0.1/1.php according to the different PHP and Web server, will open the corresponding thread or process to remove the request, the request will release the results. PHP can’t pass data from page to page at the language level, but mysql_pconnect and ATTR in PDO can do this by setting array(pDO :: persistent => true).

$conn = new PDO($dsn, DB_USER, DB_PASSWORD,
    array(PDO::ATTR_PERSISTENT => true)
);
Copy the code

In my opinion, the function of the long connection is to reduce the time of establishing database connection for each page by reusing the long connection under the condition of high load, and the time of establishing mysql connection is on my machine

  • In the case of connnections < 10, the connection time of mysql PDO and mysqli is 0.003ms and 0.14ms

  • When the database connection is close to full, the connection time of mysql PDO and mysqli is 0.13ms

The above samples are estimated in approximate time, which is too small to estimate. It doesn’t take a long time to establish a connection, so why do you need mysql long connections, connection pools, etc. For example, the number of concurrent mysql connections that can be accepted by a server server is around 200, and that of a Web server server is around 700. Then, when a large number of 500 concurrent connections come, the Web server does not reach the full load, and mysql reaches the full load in advance. This can result in unresponsive pages or slow execution of pages that have already established database connections.

Mysql long connection in PHP there are many ways to run PHP, so there are many long connection implementations. Long connections require Web server support, because PHP has no concept of process pooling or connection pooling, and most of the time A PHP application is not an application server itself (swoole, a late starter, is a great PHP application server, but on the C level). In Linux, Apache maintains a process pool. After apache MPM is enabled, Apache maintains a process pool by default. Mysql maintains a process pool after long connection. It is not closed as a SOCet connection, but as a non-releasable object, put into the process pool/thread pool. When a connection is needed, Apache fetchs mysql socket Connnection from the process/thread pool it maintains and can reuse the connection.

Mysql: /home/dengpan/opt; /home/dengpan/opt; HTTPD MPM model here uses worker, HTTPD MPM (Apache for parallel aspects of function, commonly known as multiplex module) actually has perfork, worker, event three kinds. The benefit of MPM is that Apache always has spare or idle child processes (server thread pools) waiting for new requests, so clients don’t have to wait for child processes before requesting services.

What MPM is used, and you need to specify which MPM to compile in Apache, such as work MPM to compile in Apache, such as my most simplified HTTPD compilation parameter is

/configure \ --with-apr=/home/dengpan/opt/ apr-util=/home/dengpan/opt/apr-util-1.5.4 \ -- prefix = / home/dengpan/opt/HTTPD - 2.4.16 \ - with - MPM = workerCopy the code

View HTTPD loaded modules,

Seeing that worker.c is compiled,

The MPM configuration parameter is

<IfModule mpm_worker_module> StartServers 15 MinSpareThreads 75 MaxSpareThreads 250 ThreadsPerChild 10 MaxRequestWorkers  400 MaxConnectionsPerChild 0 </IfModule>Copy the code

Start apache pstree see | – HTTPD – 15 * * [{HTTPD}] [HTTPD – 11], make up 15 a server process, each server up 10 child thread. The minimum number of idle threads to be maintained throughout the MPM is 75 and the maximum number of idle threads is 250. The maximum number of fully loaded worker threads is 400. A shell script is used to print the current number of active mysql connections every 1 second

  • Enter mysql shell and run SHOW STATUS WHERE variable_name = ‘Threads_connected’; Mysql > select * from ‘mysql’; mysql > select * from ‘mysql’

  • Shell directly query, find the/proc/pidof mysqld/fd – follow – type s | wc -l, need root authority, The advantage is that mysql can connect to the shell even when it cannot because of too many connections.

Method 2 is used here, because mysql is loaded back to the machine later. Write a shell like this:

#! /bin/bash while(true) do find /proc/`pidof mysqld`/fd/ -follow -type s | wc -l sleep 1 doneCopy the code

The subsequent execution of the shell continuously output the current connection number, the test can be obtained

  1. If you run PHP on the CLI, the long connection is invalid. As soon as the script on the CLI exits, the connection is released

  2. Apche +mod_php does not enable MPM module, regardless of mysql mysql_pconnect, pdo_mysql long connection, after the page access, mysql connection is released.

  3. Apche + open MPM mod_php module (worker mode), no matter the mysql mysql_pconnect, long pdo_mysql connections, page access, mysql connection + 1, until you reach the largest mysql connections, not increase, But the access page can still reuse the connection to query the corresponding data.

  4. Nginx +php-fpm mysql long connection has no effect.

Apache is able to reuse mysql connections, indicating that Apache must implement some functions and modules for mysql itself, otherwise it would not be possible to save an unknown type of socket pointer. Using LDD,

➜ mysql_persist LDD /home/dengpan/opt/httpd-2.4.16/bin/ HTTPD linux-vdso.so.1 (0x00007ffffcbde000) libpcre.so.1 => 1 (0x00007f8e8d17c000) libaprutil-1 => /home/dengpan/opt/apr-util-1.5.4/lib/libaprutil-1 (0x00007f8e8cf57000) libexpat.so.1 => /usr/lib/libexpat.so.1 (0x00007f8e8cd2d000) libapr-1.so.0 => 0 (0x00007f8e8cafB000) libuuid. So.1 => /usr/lib/libuuid (0x00007f8e8c8f6000) librt.so.1 => /usr/lib/librt.so.1 (0x00007f8e8c6ee000) libcrypt.so.1 => /usr/lib/libcrypt.so.1 (0x00007f8e8c4b6000) libpthread.so.0 => /usr/lib/libpthread.so.0 (0x00007f8e8c299000) libdl.so.2 => /usr/lib/libdl.so.2 (0x00007f8e8c095000) libc.so.6 => /usr/lib/libc.so.6 (0x00007f8e8bcf3000) /lib64/ld-linux-x86-64.so.2 (0x00007f8e8d3ec000)Copy the code

/home/dengpan/opt/apr-util-1.5.4/lib/libaprutil-1.so.0 /home/dengpan/opt/apr-1.5.2/lib/libapr-1.so.0 Segment. Because I’m a native compilation, very convenient to find function entry, / home/dengpan/lot/apache HTTPD/apr – util – 1.5.4 / DBD/apr_dbd_mysql. C the file, Apache mod_DBD supports long connections to all common databases. Nginx does not have the same relationship with php-fpm as PHP does with Apache, so nginx+ php-fPM cannot implement the corresponding long connection. PHP – FPM does not do mysql process/thread pool.

PHP itself connects to mysql quickly, and many of them are in excess of performance. As Apache is gradually replaced by Nginx, PHP’s mysql long connection will only become more and more useless. If you want to create a single instance of mysql, you can create only one instance of mysql connection for each page. Such as the singleton example code below, which is better written in the framework to implement the singleton.

<? PHP /** * Created by PhpStorm. * User: dengpan * Date: 15-7-24 * Time: 2:56 PM */ include "./db.php"; class DB { private static $_instance; private $db; private function __construct() { $this->db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, 'my', 3307); } public static function getInstance() { if (! (self::$_instance instanceof DB)) { self::$_instance = new self(); } return self::$_instance; } private function __clone() { } public function getConn() { return $this->db; } } $conn = DB::getInstance()->getConn();Copy the code

Pay attention and don’t get lost

All right, everybody, that’s all for this article. All the people here are talented. As I said before, there are many technical points in PHP, because there are too many, it is really difficult to write, you will not read too much after writing, so I have compiled it into PDF and document, if necessary

Click on the code: PHP+ “platform”

As long as you can guarantee your salary to rise a step (constantly updated)

I hope the above content can help you. Many PHPer will encounter some problems and bottlenecks when they are advanced, and they have no sense of direction when writing too many business codes. I have sorted out some information, including but not limited to: Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, TP6, Laravel, YII2, Redis, Swoole, Swoft, Kafka, Mysql optimization, shell scripting, Docker, microservices, Nginx, etc