Support for Persistent Connectionsext/mysqli does not support persistent connections when built with libmysql. However ext/mysqli does support persistent connections when built with mysqlnd. To establish a persistent connection with the MySQL server using ext/mysqli and mysqlnd, prepend the database host with the string "p:" (
p stands for persistent) as shown below.
$host="p:localhost";
$port=3306;
$socket="/tmp/mysql.sock";
$user="root";
$password="admin";
$dbname="test";
$cxn = new mysqli($host, $user, $password, $dbname, $port, $socket)
or die ('Could not connect to the database server' . mysqli_connect_error());
ext/mysql, ext/mysqli and PDO_MySQL support persistent connections when built with mysqlnd.
The new API call mysqli_fetch_all()
mysqlnd extends the ext/mysqli API with one brand new method,
mysqli_fetch_all()
.
mysqli_fetch_all()
fetches all result rows and return the result set as an associative array, a numeric array, or both. The method signature is shown below for both procedural as well as object oriented style of programming.
Procedural style:
mixed mysqli_fetch_all (mysqli_result $result [, int $resulttype])
Object oriented style:
mixed mysqli_result::fetch_all ([int $resulttype])
where:
$result
is a result set identifier returned by
mysqli_query(), mysqli_store_result()
or
mysqli_use_result()
, and
$resulttype
is an optional constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants
MYSQLI_ASSOC, MYSQLI_NUM
, or
MYSQLI_BOTH
. Defaults to
MYSQLI_NUM
.
Because
mysqli_fetch_all()
returns all the rows as an array in a single step, it may consume more memory than some of its counterparts like
mysqli_fetch_array()
.
mysqli_fetch_array()
returns one row at a time from the result set, hence consumes less memory relative to
mysqli_fetch_array()
. Besides, if you need to iterate over the result set, you may need a
foreach()
loop and this approach might be little slower compared to the result set retrieval using
mysqli_fetch_array()
. Hence consider using
mysqli_fetch_all()
only in those situations where the fetched result set will be sent to another layer for post processing. If you have to process the fetched result set in the same layer with the help of iterators, then the benefit of using the
mysqli_fetch_all()
method might be minimal, if there is any.
Statistical Data Collectionmysqlnd collects a lot of statistics which you can use to tune your application. mysqlnd enhances ext/mysqli API with three
mysqli_get_XX_stats()
methods for easy monitoring and to simplify the bottleneck analysis. For example, using a combination of
mysqli_get_XX_stats()
methods, one can easily identify a PHP client script that is opening more database connections than it needs or selecting more rows than it consumes.
Accessing Client Statistics:
To access per process client statistics, simply call
mysqli_get_client_stats()
with no arguments. Similarly to access client statistics per connection, call
mysqli_get_connection_stats()
with the database connection handle as the argument. Both of these methods return an associated array with the name of the statistic parameter as the key and the corresponding data as the value.
Alternatively per process client statistics can be accessed by calling the
phpinfo()
method.
The above methods return statistics like
bytes_sent, bytes_received
to represent the number of bytes sent to and received from the MySQL server,
result_set_queries
to show the number of queries which generated a result set,
buffered_sets, unbuffered_sets
to show the number of buffered and unbuffered result sets for the queries generating a result set but not run as a prepared statement.
rows_fetched_from_server_normal
shows the number of rows that have been fetched from the server using buffered and unbuffered result sets.
rows_buffered_from_client_normal
shows the number of rows fetched from the server and buffered on the client-side, and
rows_skipped_normal
shows the number of rows generated by the server but not read from the client.
Accessing Zval Cache Statistics:
mysqlnd collects statistics from its internal zval cache, that you can access by calling
mysqli_get_cache_stat()
method. This method returns an associative array with the name of the statistic as the key and the corresponding data as the value. The zval cache statistics might be useful to tweak zval cahe related
php.ini
settings for better performance.
Sample PHP Script Demonstrating mysqlnd's FeaturesThe following sample PHP script demonstrates how to:
- establish persistent connections
- use
mysqli_fetch_all()
to fetch and display the result set
- access client, connection and zval cache statistics using
mysqli_get_client_stats(), mysqli_get_connection_stats()
and
mysqli_get_cache_stat()
methods
The code sample in this tutorial try to retrieve the data from the
City
table in the MySQL
test
database. The table structure and the data from the
City
table are shown below by using the
mysql
client. MySQL server is running on the default port 3306.
bash# mysql -u root -p
Enter password: admin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.24-rc-standard Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE test;
Database changed
mysql> DESCRIBE City;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| CityName | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.07 sec)
mysql> SELECT * FROM City;
+--------------------+
| CityName |
+--------------------+
| Hyderabad, India |
| San Francisco, USA |
| Sydney, Australia |
+--------------------+
3 rows in set (0.17 sec)
The main purpose of the following example is only to illustrate the syntactical use of the new features of mysqlnd. The sample code does not represent any real world scenarios.
bash# cat PHPmysqliClientmysqlnd.php
<?php
/* create a persistent connection to the MySQL server */
$cxn = new mysqli("p:localhost", "root", "admin", "test", 3306, "/tmp/mysql.sock")
or die ('Could not connect to the database server' . mysqli_connect_error());
$query = "SELECT * FROM City";
/* execute the query */
if ($cxn->real_query ($query)) {
/* initiate the result set retrieval */
if ($result = $cxn->store_result()) {
/* find the number of rows in the result set */
$nrows = $result->num_rows;
echo "\nRetrieved $nrows row(s).\n\n";
echo "CityName\n--------\n";
$all_rows = $result->fetch_all(MYSQLI_ASSOC);
for($i = 0; $i < count($all_rows); $i++) {
echo $all_rows[$i][CityName] . "\n";
}
}
/* close the result set */
$result->close();
}
echo "\n\nClient Statistics After One Query\n---------------------------------";
$client_stats = mysqli_get_client_stats();
#var_dump($client_stats);
foreach ($client_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n\nStatistics for Connection #1\n----------------------------";
$conn_stats = mysqli_get_connection_stats($cxn);
#var_dump($conn_stats);
foreach ($conn_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n\nCache Statistics After One Query\n--------------------------------";
$cache_stats = mysqli_get_cache_stats();
#var_dump($cache_stats);
foreach ($cache_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n\n=================================\n\n";
echo "\nEstablishing connection #2 to the MySQL server ..\n\n";
/* create a non-persistent connection to the MySQL server */
$cxn2 = new mysqli("localhost", "root", "admin", "mysql", 3306, "/tmp/mysql.sock")
or die ('Could not connect to the database server' . mysqli_connect_error());
$query = "SELECT Host, User FROM user";
/* execute the query */
if ($cxn2->real_query ($query)) {
/* initiate the result set retrieval */
if ($result = $cxn2->store_result()) {
/* find the number of rows in the result set */
$nrows = $result->num_rows;
echo "\nRetrieved $nrows row(s).\n\n";
echo "Host\t\tUser\n----\t\t----\n";
$all_rows = $result->fetch_all(MYSQLI_ASSOC);
for($i = 0; $i < count($all_rows); $i++) {
echo $all_rows[$i][Host] . "\t" . $all_rows[$i][User] . "\n";
}
}
/* close the result set */
$result->close();
}
echo "\n\nClient Statistics After Two Queries\n-----------------------------------";
$client_stats = mysqli_get_client_stats();
#var_dump($client_stats);
foreach ($client_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n\nStatistics for Connection #2\n----------------------------";
$conn_stats = mysqli_get_connection_stats($cxn2);
#var_dump($conn_stats);
foreach ($conn_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n\nCache Statistics After Two Queries\n----------------------------------";
$cache_stats = mysqli_get_cache_stats();
#var_dump($cache_stats);
foreach ($cache_stats as $key=>$value) {
if ($value > 0) {
echo "\n$key : $value";
}
}
echo "\n";
//phpinfo();
/* close the database connections */
$cxn->close();
$cxn2->close();
?>
bash# /export/home/php53/bin/php PHPmysqliClientmysqlnd.php
Retrieved 3 row(s).
CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia
Client Statistics After One Query
---------------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_fetched_from_client_normal_buffered : 3
rows_skipped_normal : 3
copy_on_write_performed : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 1
proto_text_fetched_string : 3
Statistics for Connection #1
----------------------------
bytes_sent : 90
bytes_received : 222
packets_sent : 2
packets_received : 9
protocol_overhead_in : 36
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 54
bytes_received_rset_row_packet : 70
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 1
packets_received_rset_row : 4
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 3
rows_buffered_from_client_normal : 3
rows_skipped_normal : 3
connect_success : 1
active_connections : 1
active_persistent_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 3
Cache Statistics After One Query
--------------------------------
put_misses : 3
get_hits : 3
size : 2000
free_items : 1997
references : 3
=================================
Establishing connection #2 to the MySQL server ..
Retrieved 5 row(s).
Host User
---- ----
127.0.0.1 root
localhost
localhost root
unknown
unknown root
Client Statistics After Two Queries
-----------------------------------
bytes_sent : 190
bytes_received : 501
packets_sent : 4
packets_received : 21
protocol_overhead_in : 84
protocol_overhead_out : 16
bytes_received_ok_packet : 22
bytes_received_eof_packet : 18
bytes_received_rset_header_packet : 10
bytes_received_rset_field_meta_packet : 148
bytes_received_rset_row_packet : 157
packets_sent_command : 2
packets_received_ok : 2
packets_received_eof : 2
packets_received_rset_header : 2
packets_received_rset_field_meta : 3
packets_received_rset_row : 10
result_set_queries : 2
buffered_sets : 2
rows_fetched_from_server_normal : 8
rows_buffered_from_client_normal : 8
rows_fetched_from_client_normal_buffered : 8
rows_skipped_normal : 8
copy_on_write_performed : 13
connect_success : 2
active_connections : 2
active_persistent_connections : 1
explicit_free_result : 2
mem_erealloc_count : 1
mem_efree_count : 2
mem_realloc_count : 4
proto_text_fetched_string : 13
Statistics for Connection #2
----------------------------
bytes_sent : 100
bytes_received : 279
packets_sent : 2
packets_received : 12
protocol_overhead_in : 48
protocol_overhead_out : 8
bytes_received_ok_packet : 11
bytes_received_eof_packet : 9
bytes_received_rset_header_packet : 5
bytes_received_rset_field_meta_packet : 94
bytes_received_rset_row_packet : 87
packets_sent_command : 1
packets_received_ok : 1
packets_received_eof : 1
packets_received_rset_header : 1
packets_received_rset_field_meta : 2
packets_received_rset_row : 6
result_set_queries : 1
buffered_sets : 1
rows_fetched_from_server_normal : 5
rows_buffered_from_client_normal : 5
rows_skipped_normal : 5
connect_success : 1
active_connections : 1
explicit_free_result : 1
proto_text_fetched_string : 10
Cache Statistics After Two Queries
----------------------------------
put_misses : 13
get_hits : 13
size : 2000
free_items : 1987
references : 4
Before concluding, be adviced that some of the experimental functions that are available with ext/mysqli and libmysql are not available with ext/mysqli and mysqlnd. eg.,
mysqli_embedded_*(), mysqli_*rpl*_()
Related Blog Post:
MySQL Native Driver for PHP, mysqlndAcknowledgmentsAndrey Hristov & Ulf Wendel, Sun-MySQL AB
(
Originally posted on blogs.sun.com at:
http://blogs.sun.com/mandalika/entry/demonstrating_the_features_of_mysql)
_______________
Technorati Tags:
PHP |
MySQL |
mysqlnd