Wednesday Dec 23, 2009

Accessing MySQL Database(s) from StarOffice / OpenOffice.org Suite of Applications

This blog post is organized into two major sections and several sub-sections. The major sections focus on the tasks to be performed at the MySQL server and the \*Office client while the sub-sections talk about the steps to be performed in detail.

To show the examples in this exercise, we will be creating a new MySQL database user with user ID SOUSER. This new user will be granted read-only access to couple of tables in a MySQL database called ISVe. The database can be accessed from any host in the network. ben10.sfbay is the hostname of the MySQL server.

Tasks to be Performed at the MySQL Server

This section is intended only for the MySQL Server Administrators. If you are an end-user, skip ahead to Tasks to be Performed at the Client side.

Create a new MySQL user and grant required privileges.

eg.,

% mysql -u root -p
Enter password: \*\*\*\*\*
Server version: 5.1.25-rc-standard Source distribution
..

mysql> CREATE USER SOUSER IDENTIFIED BY 'SOUSER';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE ISVe;
Database changed

mysql> show tables;
+--------------------------+
| Tables_in_ISVe           |
+--------------------------+
| CustomReport             | 
| CustomSQL                | 
| ISVeOldProjects          | 
| ISVeOrg                  | 
| ISVeProject              | 
| ISVeProjectExecution     | 
| ISVeProjectGoalAlignment | 
| ISVeProjectMiscInfo      | 
| ISVeProjectScoping       | 
| ISVeProjectStatus        | 
| ISVeProjects             | 
| ISVeProjectsVW           | 
| ISVeSearchLog            | 
| LastRefreshed            | 
+--------------------------+
14 rows in set (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeOldProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
| GRANT SELECT ON `ISVe`.`ISVeOldProjects` TO 'SOUSER'@'%'                                              | 
| GRANT SELECT ON `ISVe`.`ISVeProjects` TO 'SOUSER'@'%'                                                 | 
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

Check the database connectivity and the accessibility from a remote location.


% mysql -h ben10.sfbay -D ISVe -u SOUSER -pSOUSER
Server version: 5.1.25-rc-standard Source distribution

mysql> show tables;
+-----------------+
| Tables_in_ISVe  |
+-----------------+
| ISVeOldProjects |
| ISVeProjects    |
+-----------------+
2 rows in set (0.03 sec)

mysql> select count(\*) from ISVeOldProjects;
+----------+
| count(\*) |
+----------+
|     2880 |
+----------+
1 row in set (0.04 sec)

mysql> select count(\*) from ISVeProjects;
+----------+
| count(\*) |
+----------+
|     4967 |
+----------+
1 row in set (0.33 sec)

mysql> delete from ISVeOldProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeOldProjects'

mysql> delete from ISVeProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeProjects'

mysql> quit
Bye


Tasks to be Performed at the Client side (End-User's Workstation)


StarOffice and OpenOffice suite of applications can access the MySQL Server using JDBC or native drivers.

MySQL Connector/J is a platform independent JDBC Type 4 driver that is developed specifically to connect to a MySQL database. Using Connector/J, it is possible to connect to almost any version of MySQL Server from any version of StarOffice or OpenOffice.org

Sun|MySQL recently developed a native MySQL driver to facilitate connecting from StarOffice / OpenOffice.org suite of applications to a MySQL database. The new native driver is called MySQL Connector/OpenOffice.org. However the current version of the MySQL Connector for OO.o is compatible only with OpenOffice 3.1, StarOffice 9.1 or newer and it can connect only to MySQL Server 5.1 or later versions. This native connector is supposed to be faster in comparison with the Java connector.

We will explore both MySQL connectors in this section.

Note:
As an end user, you need not be concerned about the internal workings of these MySQL connectors. You just need to worry about installing and configuring the drivers so the \*Office applications can connect to the MySQL database in a seamless fashion.

I. Connector/J approach

  1. Installation steps for MySQL Connector/J

    Using the following navigation, find the location of the JRE that is being used by StarOffice / OpenOffice.org

    • Launch StarOffice / OpenOffice.org
    • Tools Menu -> Options
    • In the 'Options' window, StarOffice / OpenOffice.org -> Java

    Here is a sample screen capture from a Mac running StarOffice 9.

    In the above example, /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home is the location of the JRE. Here onwards, this location will be referred as JRE_HOME.

    1. Download the connector from the following web page:

              http://dev.mysql.com/downloads/connector/j/

      As of this writing, 5.1.10 is the current version for Connector/J

    2. Extract the driver and the rest of the files from the compressed [downloaded] archive

      eg.,
      
      % gunzip -c mysql-connector-java-5.1.10.tar.gz | tar -xvf -
      
      
    3. Locate the jar file that contains the driver --- mysql-connector-java-5.1.10-bin.jar, and copy it into the <JRE_HOME>/lib/ext directory with 'root' privileges.

      eg.,
      
      % sudo cp mysql-connector-java-5.1.10-bin.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext
      
      % ls -l /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/\*connector\*jar
      /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/mysql-connector-java-5.1.10-bin.jar
      
      
    4. Restart StarOffice / OpenOffice.org

    This concludes the installation of MySQL Connector/J.



    2. Configuration steps for Connector/J
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select JDBC by clicking on "Connect using JDBC (Java Database Connectivity)" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database using JDBC" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections.

      MySQL JDBC driver class text field will be automatically filled with the string com.mysql.jdbc.Driver. Leave that string intact, and click on "Test Class" button to make sure that the relevant class can be loaded with no issues. Unless the driver class is loaded successfully, you will not be able to connect to the MySQL database. In case of unsuccessful class loading, double check the installation steps for MySQL Connector/J.

      Click on "Next >>" button

      Note:
      In the above screenshot, notice that the "Name of the database" was filled with ISVe?zeroDateTimeBehavior=convertToNull (It is not completely visible in the above screen capture, but you just have to believe me). In this example, ISVe is the database name and zeroDateTimeBehavior is the configuration property which was set to a value of convertToNull. Without this configuration property, Connector/J throws an exception when it encounters date values such as 0000-00-00. In such cases, the error message will be something similar to java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date.

      Configuration properties define how Connector/J will make a connection to a MySQL server. The list of Connector/J configuration properties are documented in the following web page:

              http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

      If you have more than one configuration property, you can define all of those properties in the "Name of the database" field. The syntax would be:
          <MySQL_DB_Name>?<Config_Property1=Value>&<Config_Property2=Value>&..&<Config_Propertyn=Value>

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.




    II Connector/OpenOffice.org approach

    MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice suite of applications. Even though it appears to be a native driver, MySQL Connector/OpenOffice.org has no implementation for the MySQL Client Server protocol. It is in reality a proxy on the top of MySQL Connector for C++ aka MySQL Connector/C++.

    Unlike MySQL Connector/J, Connector/OpenOffice.org has no dependency on JRE, and it can easily be installed using the OpenOffice.org Extension Manager. Due to the underlying native code, Connector/OpenOffice.org may outperform Connector/J in performance.

    1. Installation steps for MySQL Connector/OpenOffice.org

    Before installing the connector, make sure that you have OpenOffice.org 3.1 [or later] -OR- StarOffice 9.1 [or later] suite installed, and the version of the MySQL server on which the database is hosted is at least 5.1. If any of these requirements are not met, skip this entire section and check the I. Connector/J approach for the instructions that may work with your current versions of StarOffice / OpenOffice and MySQL server.

    1. Download the connector for your platform from the following location:

              http://extensions.services.openoffice.org/project/mysql_connector
    2. Launch StarOffice / OpenOffice.org

    3. Bring up the "Extension Manager" by clicking on Tools Menu -> Extension Manager ...

    4. Click on "Add" button, then locate the OpenOffice connector that you downloaded in step #1 (see two steps above). Click on "Open" button. The name of the connector will be something similar to mysql-connector-ooo-....oxt.

    5. Choose appropriate response to the question "For whom do you want to install the extension?". In this example, I chose the option "Only for me".

    6. Read the "Extension Software License Agreement" and accept the agreement to install the Connector/OpenOffice.org as an extension to StarOffice / OpenOffice.org

    7. Restart StarOffice / OpenOffice.org to complete the installation.



    2. Configuration steps for MySQL Connector/OpenOffice.org
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select "Connect native" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections. If the MySQL Server is running on the same machine as that of the StarOffice / OpenOffice.org application, you can provide the location of the socket under "Socket" field. If not, leave it blank.

      Click on "Next >>" button

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.


    That is all there is to it in installing and configuring the MySQL connectors for \*Office suite of applications. Now enjoy the flexibility of fetching the data from your favorite office productivity software.

Sunday Jul 05, 2009

Another MySQL Connector/C++ Webinar on July 9th

On May 20th, Ulf Wendel and Andrey Hristov from the MySQL Connectors development team delivered a webinar around MySQL Connector/C++. We have another Connector/C++ webinar scheduled for July 9th (Thursday) at 10:00 AM PT. This time, I will be talking about the Connector/C++ driver for about 40 minutes. I am not involved with the development of Connector/C++ in any way. However I have been playing with, and evangelizing this driver for the past few months - so in a way I'm qualified to talk about the driver and its features. As of now, I have the following topics in the agenda.

\* What is MySQL Connector/C++?
\* High Level Architecture
\* Installation, Dependencies
\* Implemented Classes
    \* Driver, Connection
    \* Statement, PreparedStatement
    \* ResultSet
    \* ResultSetMetaData, ParameterMetaData, DatabaseMetaData
    \* Savepoint
\* Transactions
\* Stored Procedures
\* Exceptions
\* Debug Tracing

It is free for all the registered users. If interested, please check the following web page for the registration and for the WebEx access details.
    Getting the Most Out of the New MySQL Connector/C++

[Updated 07/10/2009]
Presentation material from the Webinar: Let's Explore MySQL Connector/C++
Check the "Developing Database Applications Using MySQL Connector/C++" white paper in pdf or HTML format.

Monday May 18, 2009

Invitation to a Free MySQL Connector /C++ Webinar

This Wednesday (May 20, 2009) Andrey Hristov and Ulf Wendel from the MySQL Connector development team are going to talk about the MySQL Connector for C++ at 10:00 AM PT. Andrey and Ulf are planning to talk about the architecture, API, portability, support for: buffered/unbuffered result sets, prepared statements, stored procedures; and the planned features for Connector/C++ 1.0.6 GA.

Plan to attend if you are a C++ developer working [or planning to work] on MySQL database applications. Anyone can attend this webinar for free, and it may last for about 45 minutes. Register at the following location to receive further instructions on how to join the web conference:

        Register for the new MySQL Connector/C++ webinar

Thursday Apr 23, 2009

Developing MySQL Database Applications With PHP

A four part tutorial that explains the PHP / MySQL extensions - mysql, mysqli, and pdo_mysql - with simple examples is now accessible from Sun Developer Network. While most of the information presented in the tutorial is available elsewhere in bits and pieces, the real value-add is in the introduction of the MySQL native driver for PHP, mysqlnd.

Here is a brief description of all four parts in the series. Check them out, and as always feel free to send the corrections, comments, suggestions, etc., to my Sun mail ID: Giri.Mandalika@Sun.COM

Application developers who are new to the development of MySQL database applications with PHP are the target audience of this tutorial.

Wednesday Apr 15, 2009

Exploring the Features of MySQL Connector/C++

With the introduction of MySQL Connector/C++, now C++ application developers have one additional option to choose from, to connect to MySQL Server 5.1 or later from a C++ application. Admittedly, as of today, there isn't enough documentation with examples to show the capabilities [and gotchas] of MySQL Connector/C++. I tried to fill that gap with the technical article, Developing Database Applications Using MySQL Connector/C++. Hopefully it serves as a starting point for the C++ developers while waiting for the MySQL Connectors documentation team to publish the official documentation on MySQL Developer Zone.

While you are at it, don't forget to check the supplement document, Installing MySQL Connector/C++ from Source, in case if you want to build the driver on your own from the source code.

Feel free to send the corrections, comments, suggestions, etc., to my e-mail ID: Giri.Mandalika@Sun.COM

Thursday Feb 26, 2009

Accessing MySQL Database(s) with JDBC

A new technical article entitled "Using the MySQL Connector/J JDBC Driver With the Java SE Platform", has been posted on java.sun.com at:

        http://java.sun.com/developer/technicalArticles/mysql_java/index.html

This article explains the essential steps involved in accessing/manipulating the data in a MySQL database from a Java application. MySQL Connector/J JDBC driver was used in the example code to show the database connectivity, data manipulation steps. Application developers who are new to Java programming language [but not to MySQL database] are the target audience of this article.

Stay tuned for the next article in this series "Using MySQL with PHP" ..

Wednesday Feb 18, 2009

Sun Blueprint : MySQL in Solaris Containers

While the costs of managing a data center are becoming a major concern with the increased number of under-utilized servers, customers are actively looking for solutions to consolidate their workloads to:

  • improve server utilization
  • improve data center space utilization
  • reduce power and cooling requirements
  • lower capital and operating expenditures
  • reduce carbon footprint, ..

To cater those customers, Sun offers several virtualization technologies such as Logical Domains, Solaris Containers, xVM at free of cost for SPARC and x86/x64 platforms.

In order to help our customers who are planning for the consolidation of their MySQL databases on systems running Solaris 10, we put together a document with a bunch of installation steps and the best practices to run MySQL inside a Solaris Container. Although the document was focused on the Solaris Containers technology, majority of the tuning tips including the ZFS tips are applicable to all MySQL instances running [on Solaris] under different virtualization technologies.

You can access the blueprint document at the following location:

        Running MySQL Database in Solaris Containers

The blueprint document briefly explains the MySQL server & Solaris Containers technology, introduces different options to install MySQL server on Solaris 10, shows the steps involved in installing and running Solaris Zones & MySQL, and finally provides few best practices to run MySQL optimally inside a Solaris Container.

Feel free to leave a comment if you notice any incorrect information, or if you have generic suggestions to improve documents like these.

Acknowledgments

Many thanks to Prashant Srinivasan, John David Duncan and Margaret B. for their help in different phases of this blueprint.

Tuesday Jan 27, 2009

PHP: Memory savings with mysqlnd

mysqlnd may save memory. In the best cases, it may consume only 50% memory as that of libmysql esp. when the client application does not modify the data in the result set after executing a query. Keep in mind that the client must use ext/mysqli and treat the data returned by the query as read-only in order to fully realize mysqlnd's memory gains. If the client application modifies any of the data, mysqlnd behaves just like libmysql.

Let's have a quick look at the memory consumption in both the cases (mysqlnd and libmysql) with an example before delving into the internals. For easy comparison, the sample PHP script does not modify any of the arrays returned from the fetch method. The following example uses DTrace on Sun Solaris to monitor the calls to malloc() and prints the requested bytes of memory on the standard output.

Source code for the script: PHPmysqliClient.php. MySQL table structure and the sample data are shown in the other blog post: Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd.


bash# cat monitormalloc.d

#!/usr/sbin/dtrace -s

pid$1:libc:malloc:entry
{
 	printf("\\t\\tSize : %d Bytes", arg0);
	ustack();
	@malloc[probefunc] = quantize(arg0);
}

CASE 1: ext/mysqli with libmysql

In one terminal window:

bash# /opt/coolstack/php5/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 964 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23bb47
              mysqli.so`0xce11d292

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce23da60
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 20 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21d991
              libmysqlclient.so.16.0.0`0xce21d9ce
              libmysqlclient.so.16.0.0`0xce23da72
              mysqli.so`0xce11dc72

  0  80920			malloc:entry		Size : 17 Bytes
		... elided stack traces for brevity ...
  0  80920			malloc:entry		Size : 152 Bytes
  0  80920			malloc:entry		Size : 16384 Bytes
  0  80920			malloc:entry		Size : 8199 Bytes
  0  80920			malloc:entry		Size : 7 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 18261 Bytes
  0  80920			malloc:entry		Size : 58 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 4088 Bytes
  0  80920			malloc:entry		Size : 120 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 6 Bytes
  0  80920			malloc:entry		Size : 5 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
  0  80920			malloc:entry		Size : 92 Bytes
  0  80920			malloc:entry		Size : 56 Bytes
  0  80920			malloc:entry		Size : 8164 Bytes
              libc.so.1`malloc
              libmysqlclient.so.16.0.0`0xce2173c1
              libmysqlclient.so.16.0.0`0xce21a27b
              libmysqlclient.so.16.0.0`0xce23b8a4
              libmysqlclient.so.16.0.0`0xce23d4fa
              mysqli.so`0xce11fe56

  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x856fb98

\^C

  malloc		
           value  ------------- Distribution ------------- count
               2 |                                         0
               4 |@@@@@                                    4
               8 |                                         0
              16 |@@@@                                     3
              32 |@@@@                                     3
              64 |@@@                                      2
             128 |@                                        1
             256 |                                         0
             512 |@                                        1
            1024 |                                         0
            2048 |@@@@@@@@@@@@@@			   11
            4096 |@@@@                                     3
            8192 |@                                        1
           16384 |@@@                                      2
           32768 |                                         0
           65536 |                                         0
          131072 |                                         0
          262144 |@                                        1
          524288 |                                         0

CASE 2: ext/mysqli with mysqlnd

In one terminal window:

bash# /export/home/php53/bin/php PHPmysqliClient.php
In another terminal window, run monitormalloc.d `pgrep php` and press any key to continue ..

Retrieved 3 row(s).

CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia

In another terminal window:

bash# ./monitormalloc.d `pgrep php`
dtrace: script './monitormalloc.d' matched 1 probe
CPU	ID			FUNCTION:NAME
  0  80920			malloc:entry		Size : 262144 Bytes
              libc.so.1`malloc
              php`0x82f702b
              php`0x82f80ab
              php`0x82f841f
              php`0x82f98c4
              php`0x82c7668
              php`0x83c30ae
              php`0x80c059c

\^C

  malloc		
           value  ------------- Distribution ------------- count
          131072 |                                         0
          262144 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 1
          524288 |                                         0

In the case of ext/mysqli with libmysql, there are more than 25 calls to malloc() accounting to a total size around 367KB, where as in the case of ext/mysqli with mysqlnd, there is only one call to malloc() with a size of 256KB. In other words, mysqlnd is consuming 30% less memory relative to libmysql to do similar database operations (in reality, it is incorrect to treat every byte allocated as the memory consumed unless there exists a corresponding memory mapping -- however for the sake of this discussion, let's just assume that all the allocated bytes are eventually consumed).

The memory savings in the above example are the result of mysqlnd's ability to hold the results only once in the memory. On the other hand, as libmysql is not a part of PHP, some of the results fetched by libmysql will be copied into memory twice. When libmysql fetches the data from the MySQL Server, it puts the data into its own buffers. Then the data gets copied from the libmysql buffers into respective ext/mysqli data structures (often referred as zvals) before it is made available to the PHP clients to consume. So with ext/mysqli and libmysql, there might be two copies of the data in the main memory - one copy inside the libmysql buffers and the other inside zvals. With mysqlnd, there might be only one copy of the data in the memory. mysqlnd also uses buffers but links the zval structures directly to the read buffers, wherever possible. Therefore in majority of the instances, mysqlnd consumes less memory relative to libmysql. In the worst case, it may consume as much memory as that of libmysql. The total memory savings depend on the size of the buffered result set.

Shown below is the simplified behind-the-scenes actions of ext/mysqli with libmysql and ext/mysqli with mysqlnd when mysqli sends a query:

ext/mysqli with libmysql

  1. mysqli sends a query
  2. result set gets fetched into libmysql buffers
  3. mysqli allocates zvals, then new buffers
  4. mysqli copies data from libmysql to its own buffers
  5. mysqli calls mysql_free_result() and deallocates libmysql buffers

ext/mysqli with mysqlnd

  1. mysqli sends a query
  2. result set gets fetched row by row -- every row is a different buffer
  3. mysqlnd creates a result set of zvals pointing to the buffers
  4. mysqli calls mysqlnd_free_result() and deallocates the row buffers

In short, ext/mysqli with libmysql does:

  • one extra allocation for mysqli buffers
  • one extra data copy
  • one extra zval allocation (which can be saved with the zval cache)

when compared to ext/mysqli with mysqlnd.

Related Blog Posts:

  1. MySQL Native Driver for PHP, mysqlnd
  2. Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL AB

Wednesday Jan 21, 2009

Demonstrating the Features of MySQL Native Driver for PHP, mysqlnd

Support for Persistent Connections

ext/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 Collection

mysqlnd 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 Features

The 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, mysqlnd

Acknowledgments
Andrey Hristov & Ulf Wendel, Sun-MySQL AB

Saturday Jan 17, 2009

MySQL Native Driver for PHP, mysqlnd

In order to communicate with the MySQL database server from a PHP application, ext/mysql, ext/mysqli and the PDO MYSQL driver rely on the MySQL client library, libmysql - that has the required implementation for the client-server protocol. The MySQL native driver for PHP (will simply be referred as mysqlnd from this point), is an additional, alternative way to connect from PHP 5 and PHP 6 to the MySQL Server 4.1 or later versions. mysqlnd is a replacement for the MySQL client library, libmysql; and it is tightly integrated into PHP starting with the release of PHP 5.3. That is, from PHP 5.3 onwards the developers can choose between libmysql and mysqlnd when using mysql, mysqli or PDO_MySQL extensions to connect to the MySQL server 4.1 or newer. Due to the tight integration into PHP 5.3 (and later), mysqlnd eliminates the dependency on the MySQL client programming support when the database extension(s) and the database driver are built with the support for mysqlnd.

mysqlnd is not another PHP extension like mysqli nor it has an exposed API to the userland. It is a library that provides almost similar functionality as that of the MySQL client library, libmysql. mysqlnd and libmysql libraries implement the MySQL communication protocol - hence both of those libraries can be used to connect to the MySQL Server.

Since mysqlnd is neither a new extension nor a programming API, but just an alternative to libmysql to connect from PHP to the MySQL Server, there is no need to make changes to the existing PHP scripts. The existing scripts which were running properly with the mysql, mysqli and PDO_MySQL extensions built with libmysql support, continue to run with the exact same behavior even when the mysql, mysqli and PDO_MySQL extensions are built with the mysqlnd support.

From the performance perspective, mysqlnd might be as fast as libmysql; and may even outperform libmysql in some cases. The generic recommendation is to try mysqlnd with your PHP application and to decide based on the performance results.

Some of the advantages of using mysqlnd are listed below:

  • Easy to compile: no linking against libmysql, and no dependency on the MySQL client programming support.
  • may outperform libmysql in certain cases
  • persistent connections for ext/mysqli
  • uses PHP memory management, supports PHP memory limit
  • reduced memory footprint -- keeps every row only once in memory, where as with libmysql you have it twice in memory
  • keeps a long list of performance related statistics for bottle-neck analysis
  • client-side result set cache (still in experimental stage)

Installing PHP with the MySQL native driver, mysqlnd

As most of the pre-packaged PHP binary installations may not have the support for mysqlnd enabled by default, it is recommended to build PHP and the required database extensions with mysqlnd from the source code. The rest of this section focuses on the installation of PHP 5.3 from the source code. Check the php-mysqlnd web page at dev.mysql.com for the installation instructions for the prior versions of PHP with mysqlnd support.

  1. Get the source code for PHP 5.3 or later versions from http://www.php.net or from http://snaps.php.net/.

  2. Make sure that Autoconf 2.13 or later and GNU M4 1.4.7 or later are installed on the machine. Adjust the PATH environment variable to include the path to the autoconf and m4 tools.

    eg.,
    
    bash# ls /usr/local/bin/autoconf ; ls /usr/local/bin/m4
    /usr/local/bin/autoconf
    /usr/local/bin/m4
    
    bash# export PATH=.:/usr/local/bin:$PATH
    
    
  3. On Sun Solaris, create a soft link to gmake in the source directory.

    
    bash# ln -s /usr/bin/gmake make
    
    
  4. Navigate to the source directory and run buildconf.

    eg.,
    
    bash# cd php5.3-200811132130
    bash# ./buildconf --force
    
    
  5. For building PHP 5.3 or later with mysqlnd support on UNIX/Linux systems, you can decide for all three MySQL extensions (ext/mysql, ext/mysqli, PDO_mysql) whether they should be built using mysqlnd or libmysql. When choosing mysqlnd, use "mysqlnd" as path to the mysql client library. If you don't specify "mysqlnd" as library location, by default, PHP tries to use libmysql. It is possible to build one extension with one library, and another extension with another library. For example, you can build mysqli extension with mysqlnd support, and PDO_MYSQL with libmysql.

    The configure option shown below builds all the three extensions with mysqlnd support.

    eg.,
    
    bash# ./configure --prefix=/export/home/php53 --enable-mysqlnd \\
           --with-mysqli=shared,mysqlnd --with-mysql=shared,mysqlnd \\
    	--with-pdo-mysql=shared,mysqlnd --with-zlib=shared [other options]
    
    

    configure script in PHP 6.0 and some builds of PHP 5.3 may not recognize the --enable-mysqlnd option, so check the configure options by running ./configure --help before specifying --enable-mysqlnd in the list of configure options when building PHP.

    On Windows platform, mysqli extension uses the MySQL Native Driver by default in PHP versions 5.3 and newer. Hence you don't need to configure libmysql.dll

  6. On Sun Solaris, pass the -z muldefs option to the linker to pro-actively defend against linker errors like ld: fatal: symbol `<symbol>' is multiply-defined.

    
    bash# export LDFLAGS="-z muldefs"
    
    
  7. Build PHP.

    
    bash# make
    
    
  8. Install PHP in the destination location.

    
    bash# make install
    
    
  9. Enable the required database extensions in php.ini

    eg.,
    
    bash# grep extension php.ini | grep -v \\;
    extension_dir=/export/home/php53/lib/php/extensions/no-debug-non-zts-20071006
    extension="mysql.so"
    extension="mysqli.so"
    extension="pdo_mysql.so"
    
    
  10. Finally verify the new PHP installation by checking the list of PHP modules.

    eg.,
    
    bash# cd /export/home/php53/bin
    
    bash# ./php -m | grep mysql
    mysql
    mysqli
    mysqlnd
    pdo_mysql
    
    bash# ./php -i | grep -i mysql
    ..
    mysql
    MySQL Support => enabled
    Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
    ..
    mysqli
    MysqlI Support => enabled
    Client API library version => mysqlnd 5.0.1-beta - 070402 - $Revision: 321 $
    ..
    mysqlnd
    mysqlnd => enabled
    Version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
    ..
    pdo_mysql
    PDO Driver for MySQL => enabled
    Client API version => mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.18 $
    ..
    
    

Acknowledgments

Andrey Hristov & Ulf Wendel, Sun-MySQL

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today