12.1 Using the MySQL Enterprise Plugin for Connector/PHP

The MySQL Enterprise Plugin for Connector/PHP enables you to use the Query Analyzer to monitor MySQL queries from PHP applications, such as PHP-enabled web pages. The Query Analyzer enables you to locate and analyze queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.

The PHP query data is routed through the MySQL Enterprise Monitor Aggregator. The Aggregator receives query information from the PHP plugin, aggregates and computes statistics, and sends this data to the MySQL Enterprise Service Manager, where it is displayed by the Query Analyzer. You must have the MySQL Enterprise Monitor Aggregator enabled and running to use Query Analyzer with PHP applications.

Figure 12.1 Plugin for PHP and Aggregator Architecture

Plugin for PHP and Aggregator Architecture

Important

The PHP Connector is the only connector which requires the MySQL Enterprise Monitor Aggregator to aggregate queries and transmit them to the MySQL Enterprise Service Manager. The other Connectors can be configured to do this without the MySQL Enterprise Monitor Aggregator.

Prerequisites

The MySQL Enterprise Plugin for Connector/PHP requires PHP 5.3.2 or above, with the MySQL native driver, mysqlnd, installed. This is the recommended configuration. If your PHP installation was not configured with the mysqlnd enabled, you must rebuild and install PHP from source using at least one of the following options:

  • --with-mysqli=mysqlnd

  • --with-pdo-mysql=mysqlnd

  • --with-mysql=mysqlnd

The preceding options are supplied to the configure command, depending on which extension you are using (mysql, mysqli or PDO_MYSQL). If you use more than one extension, provide multiple options. Specifying any of the options listed rebuilds PHP with mysqlnd support. You also must enable the PHP JSON module.

The MySQL client application user, that makes PHP connections in your PHP code, must have SELECT privileges on the mysql.inventory table. This table contains the server UUID required to report the Query Analyzer data to the MySQL Enterprise Service Manager. Use the GRANT statement. For example:

mysql> GRANT SELECT on mysql.inventory to 'user'@'localhost' IDENTIFIED BY 'password';

Installation

The plugin is provided as a regular PHP module (PHP extension), and installation follows those PHP standard procedures as described on http://php.net/install.pecl.

Download the MySQL Enterprise Plugin for Connector/PHP, then use the following step-by-step instructions to install and configure the MySQL Enterprise Plugin for Connector/PHP extension.

  1. Locate your php.ini configuration file. If you do not know the location, you can view information about your PHP installation by creating a script containing:

    
    <?php phpinfo(); ?>
    
    

    Place the script within a directory configured for providing PHP web pages. Now load the page in your web browser to see a list of configuration and other information about your PHP installation.

    Check the output for Loaded Configuration File. If the value is (none), refer to the Configuration File (php.ini) Path and create a file called php.ini in there. If a Scan this dir for additional .ini files option is listed you can also create a file using any name you like, ending .ini, in that directory to set configuration options.

  2. Identify whether or not your PHP build was built thread safe by checking the Thread Safety value in the output from the phpinfo() test. If your PHP build is thread safe, you need mysqlenterprise_ts.so on Linux, Unix, and OS X, or php_mysqlenterprise_ts.dll on Microsoft Windows. If not, use mysqlenterprise.so on Linux, Unix, and OS X, or php_mysqlenterprise.dll on Microsoft Windows.

  3. Add an entry for the MySQL Enterprise Plugin for Connector/PHP module. The following example uses the full path:

    extension=/path/to/mysqlenterprise.so
    

    Alternatively, add the file to the directory defined by the extension_dir configuration option, and specify the filename:

    extension=mysqlenterprise.so
    
    Note

    If mysqlnd is loaded as a shared library (mysqlnd.so), then it must be loaded before mysqlenterprise.so or errors such as "PHP Warning: PHP Startup: Unable to load dynamic library '/mysqlenterprise.so' - /mysqlenterprise.so: undefined symbol: mysqlnd_plugin_register in Unknown on line 0" will be emitted by PHP. Either:

    • If php.ini is used to load the PHP extensions, then list it first. For example:

      extension=mysqlnd.so
      extension=mysqlenterprise.so
      
    • If individual ini files are used to load the PHP extensions, then note that the ini files are loaded alphabetically, so adjust accordingly so that mysqlnd.so is loaded first. For example, /etc/php.d/ might contain:

      mysqlnd.ini
      mysqlzz_enterprise.ini
      
  4. Users of Debian-based systems, such as Ubuntu, are encouraged to use the php5enmod command to enable extensions. For example:

    		  $ php5enmod /path/to/mysqlenterprise.so
    

    php5enmod creates a symlink from the usual conf.d directory that points to where the real files are located in mods-available, and prefixes it with a priority number.

  5. Restart your Web server application to reload PHP and the configured extensions.

  6. Reload the phpinfo() page, and inspect the listing for the mysqlenterprise module.

Caution

If you are using PHP on Microsoft Windows with the Apache web server (httpd) built from apache.org, note the following:

MySQL no longer supports VC6, the MySQL Enterprise Plugin for Connector/PHP for Microsoft Windows is compiled with the newer VC9 compiler. You can not use PHP as a loaded module with an Apache web server build that uses VC6. Alternative Apache builds exist that use VC9. Check your source and ensure that your binaries are compiled using VC9.

Note that PHP binaries for Microsoft Windows from php.net have compiled in mysqlnd support by default, since PHP 5.3.0.

Configuration

The configuration of the MySQL Enterprise Plugin for Connector/PHP is handled through the standard PHP configuration files, either globally using php.ini, or by using the per-directory options, as detailed in PHP Configuration. The following table shows the available configurable options.

Note

Each PHP configuration option for MySQL Enterprise Monitor is prefixed by mysqlenterprise.

Table 12.1 Connector/PHP Properties

Property Description
aggregator_connect_timeout_sec

Timeout, in seconds, for communications with the MySQL Enterprise Monitor Aggregator.

  • Property type: integer

  • Default value: 1

This property can be combined with the aggregator_connect_timeout_usec property.

aggregator_connect_timeout_usec

Timeout, in microseconds, for communications with the MySQL Enterprise Monitor Aggregator.

  • Property type: integer

  • Default value: 0

This property can be combined with the aggregator_connect_timeout_sec property.

aggregator_user

The Aggregator's username. See Chapter 11, Proxy and Aggregator Installation for more information.

  • Property type: string

aggregator_password The Aggregator's password.

  • Property type: string

aggregator_url

The IP address, or hostname, and port of the Aggregator installation.

  • Property type: string

  • Default value: tcp://127.0.0.1:14000

debug_callback

This property should be used only when debugging your MySQL Enterprise Monitor installation with MySQL Support personnel.

Defines the name of the callback function to invoke when data is sent to the Aggregator. The callback is defined in the PHP application and is a function which requires a single parameter, the array of HTTP requests made to the Aggregator.

disable_backtrace

Defines whether a backtrace is performed. Backtrace is useful for debugging but has a performance impact.

  • Property type: boolean

  • Default value: 1, the backtrace is disabled. To enable the backtrace, set this value to 0.

log_file

Defines the location of a log file which logs all query information sent to the Aggregator.

This should only be used for debugging purposes because every request is logged, resulting in a very large log file.

  • Property type: string

quan_enabled

Defines whether query analysis is enabled.

  • Property type: boolean

  • Default value: 1, query analysis is enabled. To disable query analysis, set this value to 0.


The following is an example of the Aggregator-specific section of the php.ini:

extension = /usr/local/apache/php/lib/php/extensions/mysqlenterprise.so
mysqlenterprise.aggregator_url = tcp://aggregator:14000
mysqlenterprise.quan_enabled = 1
mysqlenterprise.debug_callback = cta_callback
mysqlenterprise.disable_backtrace = 1
mysqlenterprise.aggregator_user = username
mysqlenterprise.aggregator_password = "password"
Note

You must restart your server after setting these properties. Verify the settings are correct by checking the output of phpinfo().