Connection pooling vs persist connection mysqli

Question

I've read that mysqli introduced connection pooling that was not available in mysql. http://php.net/manual/en/mysqli.quickstart.connections.php

I've read this question that explains the difference between connection pooling and persist connections.

In the mysqli documentation for Persistent connection they have written that:

If a unused persistent connection for a given combination of host, username, password, socket, port and default database can not be found in the connection pool, then mysqli opens a new connection.

So, does mysqli use connection pooling for persistent connections?

If yes, then what is difference between connection pooling and persistent connections in mysqli?

If no, then how does mysqli perform a lookup for a connection object for a persistent connection?

Answer 1

in libmysql18, it looks for any existing default mysql sock/port and tests it with the credentials. if it can't be found, the c++ function clone() is called, creating the new connection object(Resource). in certain cases this can result in a stack overflow. i'm not familiar with how it works in mysqlnd(native driver). maybe someone else is. if you want more information about how it works, install a version of facebooks HHVM-debug package and attempt to pass a connection via global in multiple nested functions. this can trigger a stack overflow under HHVM, which will show you every function called prior to the exception up to that point.

Answer 2

Actually terms connection pooling and persistent connection refer to the same thing in case of mysqli in PHP.

Persistent connection in this case refers to MySQL connection open from PHP script which stays open after the script has finished executing, to be used again in some later executions.

Connection pooling means that there is a pool of persistent connections maintained by PHP. One idle connection from this pool is given to PHP script which wants to connect to MySQL and returned to pool when script finishes.

You might wonder why do we need the pool of MySQL connections at all, why don't we use just one persistent connection for all of the scripts?

There are two reasons for this:

• PHP creates a pool of MySQL connections based on host/port/username/password used. If one script wants to connect to MySQL with some host/port/username/password combination, PHP searches for idle persistent connection which has the same values. If it's not found, then a new persistent connection is created with this host/port/username/password combination. So we need at least as many different persistent connection as there are different host/port/username/password values used by all of the scripts.
• You cannot execute two SQL commands on one MySQL connection at the same time. This can happen when two PHP scripts are executing simultaneously. When two scripts want to communicate with MySQL at the same time, two persistent MySQL connections are created. Number of persistent connections in pool is equal to last number of maximum parallel PHP scripts executed, or equal to upper limit set in php.ini.

---

Important notice:

MySQL connection pools (and any other connection pools) can exist only if PHP is executing as a web server plugin. Pools do not work when it is working in fast-cgi mode or in any other way when PHP executable terminates after script execution.

Edit: MySQL connection pooling can be used in fast-cgi mode of PHP if web server is configured to reuse one PHP fast-cgi process for multiple requests. If PHP fast-cgi process is configured to exit after serving one request then all of it's MySQL connections are closed.