The persistent connection between MySQL and mysqli in the PHP API

A long time ago, I also because of the work of the bug, the PHP MySQL client connection driver Mysqlnd and libmysql between the difference between PHP and MySQL communication that thing, this time again encountered a connection with their things, Mysqli the difference between a persistent link with MySQL. Write this article, used for one months, one is that I am too lazy, the second is the work is also relatively busy. It's only recently to make time for these things. Every time you make a summary, read the source code carefully, understand the meaning, test the validation, to confirm these details. And every step takes a long time, and it can't be interrupted. Once interrupted, it takes a long time to brush up on the context. Also intentionally forced himself to write this summary, to change their inertia.

I and my little friends in the development, testing in full swing of "MySQL server too many connections" error, a little bit, found that the PHP background process to establish a large number of links, but not closed. The server environment is approximately the following php5.3.x, Mysqli API, Mysqlnd Drive. The code situation is this:

// Background process A
/ *
Configuration information
'mysql' => array (
     'driver' => 'mysqli',
// 'driver' => 'pdo',
// 'driver' => 'mysql',
     'host' => '192.168.111.111',
     'user' => 'root',
     'port' => 3306,
     'dbname' => 'dbname',
     'socket' => '',
     'pass' => 'pass',
     'persist' => true, // As mentioned below, this is the configuration of the persistent link
    ),
* /
$ config = Yaf_Registry :: get ('config');
$ driver = Afx_Db_Factory :: DbDriver ($ config ['mysql'] ['driver']); // mysql mysqli
$ driver :: debug ($ config ['debug']); // note here
$ driver-> setConfig ($ config ['mysql']); // note here
Afx_Module :: Instance ()-> setAdapter ($ driver); // Pay attention to where it is uncomfortable, pay attention to where it is.

$ queue = Afx_Queue :: Instance ();
$ combat = new CombatEngine ();
$ Role = new Role (1, true);
$ idle_max = isset ($ config ['idle_max'])? $ config ['idle_max']: 1000;
while (true)
{
    $ data = $ queue-> pop (MTypes :: ECTYPE_COMBAT_QUEUE, 1);
    if (! $ data) {
        usleep (50000); // sleep for 0.05 seconds
         ++ $ idle_count;
        if ($ idle_count> = $ idle_max)
        {
            $ idle_count = 0;
             Afx_Db_Factory :: ping ();
        }
        continue;
    }
    $ idle_count = 0;
    $ Role-> setId ($ data ['attacker'] ['role_id']);
    $ Property = $ Role-> getModule ('Property');
    $ Mounts = $ Role-> getModule ('Mounts');
    // ............
    unset ($ Property, $ Mounts /*.....*/);
}

From this background process code, you can see that "$Property" variables and "$Mounts" variables are frequently created and destroyed. And the GetModule method of the Role object is written like this

// getModule method of ROLE object
class Role extends Afx_Module_Abstract
{
     public function getModule ($ member_class)
     {
         $ property_name = '__m'. ucfirst ($ member_class);
         if (! isset ($ this-> $ property_name))
         {
             $ this-> $ property_name = new $ member_class ($ this);
         }
         return $ this-> $ property_name;
     }
}
// Property class
class Property extends Afx_Module_Abstract
{
     public function __construct ($ mRole)
     {
         $ this-> __ mRole = $ mRole;
     }
}

As you can see, the GetModule method is just a mock up, new object returns, and they all inherit the Afx_module_abstract class. The Afx_module_abstract class has approximately the following code:

Abstract class Afx_module_abstract
{public
    function Setadapter ($_adapter)
    {
        $this->_ adapter = $_adapter;
    }

The key code in class Afx_module_abstract, as in the case of Db, setadapter a method that goes back to "background process a" setadapter the Afx_db_factory::D bdriver ($config The return of MySQL ' [' Driver ']] is passed in as a parameter. Keep looking at the code for the Afx_db_factory class.

Class Afx_db_factory
{
    const DB_MYSQL = ' MYSQL ';
    Const DB_MYSQLI = ' mysqli ';
    Const DB_PDO = ' PDO ';

    public static function Dbdriver ($type = self::D b_mysqli)
    {
        switch ($type)
        {case
            self::D b_mysql:
                $driver = Afx_db_mysql_adapter::instance ();
                break;
            Case self::D b_mysqli:
                $driver = Afx_db_mysqli_adapter::instance ();    Came here to break
                ;
            Case self::D b_pdo:
                $driver = Afx_db_pdo_adapter::instance ();
                break;
            Default: Break
                ;
        }
        return $driver;
    }

A look at it is a factory class, continue to look at the real DB adapter part of the code

class Afx_Db_Mysqli_Adapter implements Afx_Db_Adapter
{
    public static function Instance ()
    {
        if (! self :: $ __ instance instanceof Afx_Db_Mysqli_Adapter)
        {
            self :: $ __ instance = new self (); // Here is the singleton pattern. Why is a new mysql link generated?
        }
        return self :: $ __ instance;
    }

    public function setConfig ($ config)
    {
        $ this-> __ host = $ config ['host'];
        // ...
        $ this-> __ user = $ config ['user'];
        $ this-> __ persist = $ config ['persist'];
        if ($ this-> __ persist == TRUE)
        {
            $ this-> __ host = 'p:'. $ this-> __ host; // Here is done for persistent links, support persistent links
        }
        $ this-> __ config = $ config;
    }

    private function __init ()
    {

        $ this-> __ link = mysqli_init ();
        $ this-> __ link-> set_opt (MYSQLI_OPT_CONNECT_TIMEOUT, $ this-> __ timeout);
        $ this-> __ link-> real_connect ($ this-> __ host, $ this-> __ user, $ this-> __ pass, $ this-> __ dbname, $ this-> __ port, $ this-> __ socket);
        if ($ this-> __ link-> errno == 0)
        {
            $ this-> __ link-> set_charset ($ this-> __ charset);
        } else
        {
            throw new Afx_Db_Exception ($ this-> __ link-> error, $ this-> __ link-> errno);
        }
    }
}

From the above code can see that we have enabled the long link, ah, why the frequent creation of so many links? In order to simulate reproduce this problem, I test in the local development environment, in any case can not reproduce, compared to the environment, my development environment is Windows7, php5.3.x, MySQL, libmysql, inconsistent with the server, The problem is likely to arise on MySQL and Mysqli APIs, or on Libmysql and Mysqlnd. To this end, I carefully opened the PHP source code (5.3.x the latest), and finally Kung Fu, found the reasons for these problems.

// line 907-916 in file ext \ mysql \ php_mysql.c
// mysql_connect, mysql_pconnect both call it, the difference is that the persistent link identity is persistent is false or true
static void php_mysql_do_connect (INTERNAL_FUNCTION_PARAMETERS, int persistent)
{
/ * hash it up * /
Z_TYPE (new_le) = le_plink;
new_le.ptr = mysql;
// Note the code in the following if
if (zend_hash_update (& EG (persistent_list), hashed_details, hashed_details_length + 1, (void *) & new_le, sizeof (zend_rsrc_list_entry), NULL) == FAILURE) {
     free (mysql);
     efree (hashed_details);
     MYSQL_DO_CONNECT_RETURN_FALSE ();
}
MySG (num_persistent) ++;
MySG (num_links) ++;
}

From Mysql_pconnect's code, you can see that when PHP expands the MySQL API to establish a TCP link with MySQL server, it immediately stores the link in Persistent_list, and the next time the link is created, it will start from Persistent_ List to find if there is a link to the IP, PORT, USER, pass, client_flags, existing, and then new.

and PHP mysqli expansion, not only used a persistent_list to store the link, but also used a free_link to store the current idle TCP link. When looking, it also determines whether it exists in the idle Free_link list and exists to use this TCP link. After Mysqli_closez or Rshutdown, the link is pushed to the free_links. (Mysqli will find the same ip,port, USER, pass, dbname, socket as the same logo, unlike MySQL, no client, more dbname with the socket, and IP also includes long connection identification "P")

// File ext \ mysqli \ mysqli_nonapi.c about 172 lines mysqli_common_connect creates a TCP connection (when the mysqli_connect function is called)
do {
    if (zend_ptr_stack_num_elements (& plist-> free_links)) {
        mysql-> mysql = zend_ptr_stack_pop (& plist-> free_links); // Pop out directly, when the next mysqli_connect of the same script is called again, it will not be found

        MyG (num_inactive_persistent)-;
        / * reset variables * /

        #ifndef MYSQLI_NO_CHANGE_USER_ON_PCONNECT
            if (! mysqli_change_user_silent (mysql-> mysql, username, passwd, dbname, passwd_len)) {// (When you see it, you look at it again) Pay attention to this
        #else
            if (! mysql_ping (mysql-> mysql)) {
        #endif
        #ifdef MYSQLI_USE_MYSQLND
            mysqlnd_restart_psession (mysql-> mysql);
        #endif
}
// File ext \ mysqli \ mysqli_api.c lines 585-615
/ * {{{php_mysqli_close * /
void php_mysqli_close (MY_MYSQL * mysql, int close_type, int resource_status TSRMLS_DC)
{
if (resource_status> MYSQLI_STATUS_INITIALIZED) {
MyG (num_links)-;
}

if (! mysql-> persistent) {
mysqli_close (mysql-> mysql, close_type);
} else {
zend_rsrc_list_entry * le;
if (zend_hash_find (& EG (persistent_list), mysql-> hash_key, strlen (mysql-> hash_key) + 1, (void **) & le) == SUCCESS) {
if (Z_TYPE_P (le) == php_le_pmysqli ()) {
mysqli_plist_entry * plist = (mysqli_plist_entry *) le-> ptr;
#if defined (MYSQLI_USE_MYSQLND)
mysqlnd_end_psession (mysql-> mysql);
#endif
zend_ptr_stack_push (& plist-> free_links, mysql-> mysql); // Here is push back here, it can be used again next time

MyG (num_active_persistent)-;
MyG (num_inactive_persistent) ++;
}
}
mysql-> persistent = FALSE;
}
mysql-> mysql = NULL;

php_clear_mysql (mysql);
}
/ *})} * /

Why would mysqli do that? Why can't the same long connection be reused in the same script?
In the C function Mysqli_common_connect saw a mysqli_change_user_silent call, as on the code, mysqli_change_user_silent corresponding to this libmysql mysql_ Change_user or Mysqlnd mysqlnd_change_user_ex, they all call the C API Mysql_change_user to clean up some temporary session variables for the current TCP link, incomplete write-back instructions, lock-table directives, temporary table unlock and so on (these instructions, are the MySQL server itself decided to do, not PHP mysqli to judge the sent SQL instructions and then do the response decision), see the Manual of the Mysqli Extension and persistent Connections. This design is for this new feature, and MySQL extensions do not support this feature.

In terms of the superficial understanding of these codes, can understand mysqli and MySQL, the difference between the persistent link, this problem, perhaps we understand more difficult, I later searched, also found a reason for this cause of the confusion, we look at this case, may understand it is very easy. Mysqli Persistent Connect doesn ' t work respondents were not specific to the mysqli low-level implementation, in fact, this is the reason. The code is as follows:

<?php
$links = Array ();
for ($i = 0; $i < $i + +) {
    $links [] =  mysqli_connect (' p:192.168.1.40 ', ' USER ', ' PWD ', ' DB ', 3306);
} Sleep
(15);

Viewing the process list is the result:

netstat -an  grep 192.168.1.40:3306
tcp        0      0 192.168.1.6:52441       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52454       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52445       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52443       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52446       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52449       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52452       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52442       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52450       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52448       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52440       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52447       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52444       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52451       192.168.1.40:3306       ESTABLISHED
tcp        0      0 192.168.1.6:52453       192.168.1.40:3306       ESTABLISHED

So look at the code, it is much clearer, verify that my understanding is relatively simple, so the change can be seen

for ($ i = 0; $ i <15; $ i ++) {
     $ links [$ i] = mysqli_connect ('p: 192.168.1.40', 'USER', 'PWD', 'DB', 3306);
     var_dump (mysqli_thread_id ($ links [$ i])); // If you are worried about being closed, this is a newly created TCP link, then you can print the thread id to see if it is the same ID, then distinguish
     mysqli_close ($ links [$ i])
}
/ *
The results are as follows:
root @ cnxct: / home / cfc4n # netstat -antp grep 3306grep -v "php-fpm"
tcp 0 0 192.168.61.150:55148 192.168.71.88:3306 ESTABLISHED 5100 / php5
root @ cnxct: / var / www # / usr / bin / php5 4.php
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
int (224218)
* /

If you're worried about being close, this is a new TCP link, then you can print the thread ID and see if it's the same ID. (although I did not reply to this post, but can not prove that I am bad.) Above is the case when CLI mode. In FPM mode, each page request is handled by a single fpm subprocess. This subprocess will be responsible for maintaining the long link between PHP and MySQL server, so when you visit this page several times to verify that the same thread ID is not the same, it may be distributed separately to other FPM processes, resulting in different results. Eventually, however, each FPM will maintain these TCP links separately.

Overall, the difference between mysqli expansion and MySQL expansion is the following

• Persistent link Establishment, mysqli is added "P:" Two characters in front of host, MySQL uses mysql_pconnect function;.
• The persistent link established by mysqli must be mysqli_close after the following code is reused, or after Rshotdown, the next request is reused; the long connection to MySQL can be reused immediately.
• When mysqli establishes a persistent link, it automatically cleans up the last session variable, rolls back the transaction, unlocks the table, releases the lock, and so on; MySQL does not.
• Mysqli determine if the identity of the same persistent link is ip,port, user, pass, dbname, Socket;mysql is IP, PORT, USER, pass, Client_flags

Well, for this reason, the problem we mentioned at the beginning of the article will be solved, we are sure that the first thought is that in a class similar to property, a Mysqli_close method is added to the __destruct destructor, and when it is destroyed, the shutdown function is called. Push the persistent link into the free_links. If you think so, I can only congratulate you, wrong answer, the best solution is not let it create so many times. Colleague Dietoad a solution to the DB adapter most real single example, and, optionally, whether to create a new link. The following code:

// DB FACTORY
class Afx_Db_Factory
{
    const DB_MYSQL = 'mysql';
    const DB_MYSQLI = 'mysqli';
    const DB_PDO = 'pdo';

    static $ drivers = array (
        'mysql' => array (), 'mysqli' => array (), 'pdo' => array ()
    );


    public static function DbDriver ($ type = self :: DB_MYSQLI, $ create = FALSE) // Add $ create parameter
    {
        $ driver = NULL;
        switch ($ type)
        {
            case self :: DB_MYSQL:
                $ driver = Afx_Db_Mysql_Adapter :: Instance ($ create);
                break;
            case self :: DB_MYSQLI:
                $ driver = Afx_Db_Mysqli_Adapter :: Instance ($ create);
                break;
            case self :: DB_PDO:
                $ driver = Afx_Db_Pdo_Adapter :: Instance ($ create);
                break;
            default:
                break;
        }
        self :: $ drivers [$ type] [] = $ driver;
        return $ driver;
    }
}

// mysqli adapter
class Afx_Db_Mysqli_Adapter implements Afx_Db_Adapter
{
    public static function Instance ($ create = FALSE)
    {
        if ($ create)
        {
            return new self (); // Add the judgment of $ create parameter
        }
        if (! self :: $ __ instance instanceof Afx_Db_Mysqli_Adapter)
        {
            self :: $ __ instance = new self ();
        }
        return self :: $ __ instance;
    }
} 

It seems that the development environment is as important as the operating environment, otherwise you will not encounter these problems. But it would be a shame if you hadn't met such an interesting question.