Copyright 1997-2008 the PHP Documentation Group.
This section of the manual provides an overview of the MySQL Native Driver.
What is it?
MySQL Native Driver is a replacement for the MySQL Client Library (libmysql). MySQL Native Driver is part of the official PHP 5.3 and PHP 6 branches.
The MySQL database extensions MySQL extension,
mysqli
and PDO MYSQL all communicate with the
MySQL server. In the past, this was done by the extension using
the services provided by the MySQL Client Library. The extensions
were compiled against the MySQL Client Library in order to use its
client-server protocol.
With MySQL Native Driver there is now an alternative, as the MySQL database extensions can be compiled to use MySQL Native Driver instead of the MySQL Client Library.
MySQL Native Driver is written in C as a PHP extension.
What it is not
Although MySQL Native Driver is written as a PHP extension, it is
important to note that it does not provide a new API to the PHP
programmer. The programmer APIs for MySQL database connectivity
are provided by the MySQL extension, mysqli
and
PDO MYSQL. These extensions can now use the services of MySQL
Native Driver to communicate with the MySQL Server. Therefore, you
should not think of MySQL Native Driver as an API.
Why use it?
Using the MySQL Native Driver offers a number of advantages over using the MySQL Client Library.
The older MySQL Client Library was written by MySQL AB (now Sun Microsystems, Inc.) and so was released under the MySQL license. This ultimately led to MySQL support being disabled by default in PHP. However, the MySQL Native Driver has been developed as part of the PHP project, and is therefore released under the PHP license. This removes licensing issues that have been problematic in the past.
Also, in the past, you needed to build the MySQL database extensions against a copy of the MySQL Client Library. This typically meant you needed to have MySQL installed on a machine where you were building the PHP source code. Also, when your PHP application was running, the MySQL database extensions would call down to the MySQL Client library file at run time, so the file needed to be installed on your system. With MySQL Native Driver that is no longer the case as it is included as part of the standard distribution. So you do not need MySQL installed in order to build PHP or run PHP database applications.
Because MySQL Native Driver is written as a PHP extension, it is tightly coupled to the workings of PHP. This leads to gains in efficiency, especially when it comes to memory usage, as the driver uses the PHP memory management system. It also supports the PHP memory limit. Using MySQL Native Driver leads to comparable or better performance than using MySQL Client Library, it always ensures the most efficient use of memory. One example of the memory efficiency is the fact that when using the MySQL Client Library, each row is stored in memory twice, whereas with the MySQL Native Driver each row is only stored once in memory.
Special features
MySQL Native Driver also provides some special features not available when the MySQL database extensions use MySQL Client Library. These special features are listed below:
Improved persistent connections
The special function mysqli_fetch_all
Performance statistics calls:
mysqli_get_cache_stats
,
mysqli_get_client_stats
,
mysqli_get_connection_stats
The performance statistics facility can prove to be very useful in identifying performance bottlenecks.
MySQL Native Driver also allows for persistent connections when
used with the mysqli
extension.
SSL Support
Currently, MySQL Native Driver does not support SSL. This means
that mysqli_ssl_set
will generate an error if
called while using MySQL Native Driver.
Installation on Unix
By default the MySQL database extensions are configured to use MySQL Client Library. In order to use the MySQL Native Driver, PHP needs to be built specifying that the MySQL database extensions are compiled with MySQL Native Driver support. This is done through configuration options prior to building the PHP source code.
For example, to build the MySQL extension,
mysqli
and PDO MYSQL using the MySQL Native
Driver, the following command would be given:
Installation on Windows
In the official PHP distributions from 5.3 onwards, MySQL Native Driver is enabled by default, so no additional configuration is required to use it. All MySQL database extensions will use MySQL Native Driver in this case.
Using Persistent Connections
The MySQL database extensions do not support persistent
connections when used with the MySQL Client Library. However, they
do when using MySQL Native Driver. When creating a connection the
hostname is prepended with “p:”. This is demonstrated
by the following code snippet, which creates a new connection
using mysqli
:
Using mysqli_fetch_all
If you are using mysqli
with the MySQL Native
Driver, you have access to the new API call
mysqli_fetch_all
.
As mysqli_fetch_all
returns all the rows as
an array in a single step, it may consume more memory than some
similar functions such as mysqli_fetch_array
,
which only returns one row at a time from the result set. Further,
if you need to iterate over the result set, you will need a
looping construct that will further impact performance. For these
reasons mysqli_fetch_all
should only be used
in those situations where the fetched result set will be sent to
another layer for processing.
Using Statistical Data
MySQL Native Driver contains support for gathering statistics on the communication between the client and the server. The statistics gathered are of three main types:
Client statistics
Connection statistics
Zval cache statistics
If you are using the mysqli
extension, these
statistics can be obtained through three API calls:
mysqli_get_client_stats
mysqli_get_connection_stats
mysqli_get_cache_stats
Accessing Client Statistics
To access client statistics, you need to call
mysqli_get_client_stats
. The function call
does not require any parameters.
The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.
Client statistics can also be accessed by calling the
phpinfo
function.
Accessing Connection Statistics
To access connection statistics call
mysqli_get_connection_stats
. This takes the
database connection handle as the parameter.
The function returns an associative array that contains the name of the statistic as the key and the statistical data as the value.
Accessing Zval Cache Statistics
The MySQL Native Driver also collects statistics from its internal
Zval cache. These statistics can be accessed by calling
mysqli_get_cache_stats
.
The Zval cache statistics obtained may lead to a tweaking of
php.ini
settings related to the Zval cache,
resulting in better performance.
Statistics returned by MySQL Native Driver
The following table shows a list of statistics returned by the
mysqli_get_client_stats
,
mysqli_get_connections_stats
and
mysqli_get_cache_stats
functions.
Statistic |
bytes_sent |
bytes_received |
packets_sent |
packets_received |
protocol_overhead_in |
protocol_overhead_out |
bytes_received_ok_packet |
bytes_received_eof_packet |
bytes_received_rset_header_packet |
bytes_received_rset_field_meta_packet |
bytes_received_rset_row_packet |
bytes_received_prepare_response_packet |
bytes_received_change_user_packet |
packets_sent_command |
packets_received_ok |
packets_received_eof |
packets_received_rset_header |
packets_received_rset_field_meta |
packets_received_rset_row |
packets_received_prepare_response |
packets_received_change_user |
result_set_queries |
non_result_set_queries |
no_index_used |
bad_index_used |
slow_queries |
buffered_sets |
unbuffered_sets |
ps_buffered_sets |
ps_unbuffered_sets |
flushed_normal_sets |
flushed_ps_sets |
ps_prepared_never_executed |
ps_prepared_once_executed |
rows_fetched_from_server_normal |
rows_fetched_from_server_ps |
rows_buffered_from_client_normal |
rows_buffered_from_client_ps |
rows_fetched_from_client_normal_buffered |
rows_fetched_from_client_normal_unbuffered |
rows_fetched_from_client_ps_buffered |
rows_fetched_from_client_ps_unbuffered |
rows_fetched_from_client_ps_cursor |
rows_skipped_normal |
rows_skipped_ps |
copy_on_write_saved |
copy_on_write_performed |
command_buffer_too_small |
connect_success |
connect_failure |
connection_reused |
reconnect |
pconnect_success |
active_connections |
active_persistent_connections |
explicit_close |
implicit_close |
disconnect_close |
in_middle_of_command_close |
explicit_free_result |
implicit_free_result |
explicit_stmt_close |
implicit_stmt_close |
mem_emalloc_count |
mem_emalloc_ammount |
mem_ecalloc_count |
mem_ecalloc_ammount |
mem_erealloc_count |
mem_erealloc_ammount |
mem_efree_count |
mem_malloc_count |
mem_malloc_ammount |
mem_calloc_count |
mem_calloc_ammount |
mem_realloc_count |
mem_realloc_ammount |
mem_free_count |
proto_text_fetched_null |
proto_text_fetched_bit |
proto_text_fetched_tinyint |
proto_text_fetched_short |
proto_text_fetched_int24 |
proto_text_fetched_int |
proto_text_fetched_bigint |
proto_text_fetched_decimal |
proto_text_fetched_float |
proto_text_fetched_double |
proto_text_fetched_date |
proto_text_fetched_year |
proto_text_fetched_time |
proto_text_fetched_datetime |
proto_text_fetched_timestamp |
proto_text_fetched_string |
proto_text_fetched_blob |
proto_text_fetched_enum |
proto_text_fetched_set |
proto_text_fetched_geometry |
proto_text_fetched_other |
proto_binary_fetched_null |
proto_binary_fetched_bit |
proto_binary_fetched_tinyint |
proto_binary_fetched_short |
proto_binary_fetched_int24 |
proto_binary_fetched_int |
proto_binary_fetched_bigint |
proto_binary_fetched_decimal |
proto_binary_fetched_float |
proto_binary_fetched_double |
proto_binary_fetched_date |
proto_binary_fetched_year |
proto_binary_fetched_time |
proto_binary_fetched_datetime |
proto_binary_fetched_timestamp |
proto_binary_fetched_string |
proto_binary_fetched_blob |
proto_binary_fetched_enum |
proto_binary_fetched_set |
proto_binary_fetched_geometry |
proto_binary_fetched_other |
User Comments
Add your own comment.