PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Using MySQL with PHP mysqli: Connections, Options, Pooling

Note: This article was originally published at Planet PHP on 8 November 2011.
Planet PHP

Opening a database connection is a boring tasks. But do you know how defaults are determined, if values are omitted? Or, did you know there are two flavours of persistent connections in mysqli? Of course you, as a german reader, know it. I blogged about it in 2009 over at phphatesme.com (Nimmer Arger mit den Persistenten Verbindungen von MySQL?) a

Database connections with mysqli

The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

$mysqli = new mysqli("localhost", "root", "", "test"); echo $mysqli-host_info . "\n"; $mysqli = new mysqli("127.0.0.1", "root", "", "test", 3306); echo $mysqli-host_info . "\n";


Localhost via UNIX socket 127.0.0.1 via TCP/IP

Connection parameter defaults

Depending on the connection function used, assorted parameters can be omitted. If a parameter is not given the extension attempts to use defaults values set in the PHP configuration file.

mysqli.default_host=192.168.2.27 mysqli.default_user=root mysqli.default_pw="" mysqli.default_port=3306 mysqli.default_socket=/tmp/mysql.sock

The resulting parameter values are then passed to the client library used by the extension. If the client library detects empty or unset parameters, it may default to library built-in values.

Built-in connection library defaults

If the host value is unset or empty, the client library will default to a Unix socket connection on localhost. If socket is unset or empty and a Unix socket connection is requested, a connection to the default socket on /tmp/mysql.sock is attempted.

On Windows systems the host name . is interpreted by the client library as an attempt to open a Windows named pipe based connection. In this case the socket parameter is interpreted as the pipes name. If not given or empty, the socket (here: pipe name) defaults to \\.\pipe\MySQL.

If neither a Unix domain socket based nor a Windows named pipe based connection is to be bestablished and the port parameter value is unset, the library will default to TCP/IP and port 3306.

The mysqlnd library and the MySQL Client Library (libmysql) implement the same logic for determining defaults.

Connection options

Various connection options are available, for example, to set init commands which are executed upon connect or, for requesting use of a certain charset. Connection options must be set before a network connection is established.

For setting a connection option the connect operation has to be performed in three steps: creating a connection handle with mysqli_init(), setting the requested options using mysqli_options() and establishing the network connection with mysqli_real_connect().

Connection pooling

The mysqli extension supports persistent database connections, which are a special kind of pooled connections. By default every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script. A persistent connection is not. Instead it is put into a pool for later reuse, if a connection to the same server using the same username, password, socket, port and default database is used. Upon reuse connection overhead is saved.

Every PHP process is using its own mysqli connection pool. Depending on the web server deployment model a PHP process may serve one or multiple requests. Therefore, a pooled connection may be used by one or more scripts subsequently.

Persistent connections

If no unused persistent connection for a given combination of host, username, password, socket, port and default database can be found in the connection pool, mysqli opens a new connection. The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links. The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent. Please note, that the web server may spawn many PHP processes.

A common complain about persistent connections is that their state is not reset before reuse. For example,

Truncated by Planet PHP, read more at the original (another 1299 bytes)