3 Using the Query Analyzer
3.1 Providing Query Analyzer Data
3.1.2 Using the MySQL Enterprise Plugin for Connector/PHP
The MySQL Enterprise Plugin for Connector/PHP lets you use the Query Analyzer to monitor MySQL queries from PHP applications, such as PHP-enabled web pages. As described in Section 3.2, “Query Analyzer User Interface”, the Query Analyzer can help you locate 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 aggregator component of MySQL Enterprise Monitor Agent. Because PHP processes are typically short-lived, the Aggregator receives bursts of query information from the PHP plugin, aggregates and computes statistics, and periodically sends this data to the MySQL Enterprise Service Manager, where it is displayed on the Query Analyzer tab. You must have the MySQL Enterprise Agent Aggregator enabled and running to use Query Analyzer with PHP applications. See Section 3.1.4, “Using the MySQL Enterprise Agent Aggregator” for details about setting up the MySQL Enterprise Agent Aggregator.
Table 3.5 MySQL Enterprise Plugin for Connector/PHP Summary Feature C/PHP Uses Aggregator Yes Uses Proxy No Supports EXPLAIN No
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.
Using the MySQL Enterprise Plugin for Connector/PHP
For further information on MySQL Native Driver (mysqlnd), see MySQL Native Driver.
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; it is 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 and Unix, or php_mysqlenterprise_ts.dll on Microsoft Windows. If not, use mysqlenterprise.so on Linux, Unix, or php_mysqlenterprise.dll on Microsoft Windows.
3. Add an entry for the MySQL Enterprise Plugin for Connector/PHP module. You can use the full path:
extension=/path/to/mysqlenterprise.so
Alternatively, put the file into the directory identified by your extension_dir configuration option, and specify just the filename:
extension=mysqlenterprise.so
Note
If mysqlnd is loaded as a shared library (mysqlnd.so), then it must be loaded before mysqlenterprise.so as otherwise an error 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:
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. Restart your Web server application to reload PHP and the configured extensions.
5. Now reload the phpinfo() page that you created earlier, 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.
Historically, the Win32 Apache web server binary from apache.org is compiled with the legacy Microsoft Visual Studio 6 compiler (VC6). MySQL no longer supports VC6, and as a consequence 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, so check your source and be sure that your binaries are both compiled using VC9. Or, instead of loading PHP as a module, you can run PHP in (Fast)CGI mode. See http://windows.fyicenter.com/view.php? ID=68&R=71 for configuration details.
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 3.6 MySQL Enterprise Plugin for Connector/PHP Options
Property Name Property Type Default
aggregator_connect_timeout_sec integer 1 aggregator_connect_timeout_usec integer 0 aggregator_password string aggregator_url string tcp://127.0.0.1:14000 aggregator_user string debug_callback string
Using the MySQL Enterprise Plugin for Connector/PHP
Property Name Property Type Default
disable_backtrace boolean 1
log_file string
quan_enabled boolean 1
The following list describes these options in more detail:
• mysqlenterprise.quan_enabled
Type boolean
Permitted Values
Default 1
A boolean value specifying whether query analysis should be enabled.
PHP Category: PHP_INI_PERDIR
• mysqlenterprise.aggregator_url
Type string
Permitted Values
Default tcp://127.0.0.1:14000
A string specifying the hostname and port for the MySQL Enterprise Agent Aggregator.
PHP Category: PHP_INI_ALL
• mysqlenterprise.aggregator_user
Permitted Values Type string
The user name for communicating with the MySQL Enterprise Agent Aggregator. This is defined in the MySQL Enterprise Monitor dashboard, and is assigned the Agent role.
Note
This will only have an effect if the password
(mysqlenterprise.aggregator_password) is also set.
PHP Category: PHP_INI_SYSTEM
• mysqlenterprise.aggregator_password
Permitted Values Type string
The password for communicating with the MySQL Enterprise Agent Aggregator. This is defined in the MySQL Enterprise Monitor dashboard.
Note
This will only have an effect if the user (mysqlenterprise.aggregator_user) is also set.
PHP Category: PHP_INI_SYSTEM
Type integer
Permitted Values
Default 1
Timeout in seconds when communicating with the MySQL Enterprise Agent Aggregator.
Note
The settings for mysqlenterprise.aggregator_connect_timeout_sec and mysqlenterprise.aggregator_connect_timeout_usec are combined (added together) to determine the timeout.
PHP Category: PHP_INI_SYSTEM
• mysqlenterprise.aggregator_connect_timeout_usec
Type integer
Permitted Values
Default 0
The microseconds part of the timeout when communicating with the MySQL Enterprise Agent Aggregator.
Note
The settings for mysqlenterprise.aggregator_connect_timeout_sec and mysqlenterprise.aggregator_connect_timeout_usec are combined (added together) to determine the timeout.
PHP Category: PHP_INI_SYSTEM
• mysqlenterprise.disable_backtrace
Type boolean
Permitted Values
Default 1
Whether a backtrace will be generated. A backtrace is helpful while locating the source of errors, but it will have a performance impact.
PHP Category: PHP_INI_ALL
• mysqlenterprise.debug_callback
Permitted Values Type string
Name of the callback function to be invoked whenever data is sent to the agent. This callback is defined in the PHP application, and is a PHP function that requires one parameter. This parameter is an array of HTTP requests made to the aggregator.
Note
This should only be set while debugging MySQL Enterprise Monitor, and likely only used when support personal asks for it and defines how you should use this configuration option.
Using the MySQL Enterprise Plugin for Connector/Net
• mysqlenterprise.log_file
Permitted Values Type string
Opens a file to log all the Query Analyzer information sent to MySQL Enterprise Agent Aggregator while monitoring queries.
Note
This should only be enabled while debugging MySQL Enterprise Monitor because it will log every request, which may result in a large log file.
PHP Category: PHP_INI_SYSTEM
For example, you might configure your php.ini file with the following options:
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"
The Aggregator-related options must be set, as MySQL Enterprise Plugin for Connector/PHP cannot function without a connection to the Aggregator.
Note
Restart your web server for the configuration options to take effect. Verify your desired settings are in effect by checking the output of the phpinfo() PHP function.
Usage
After you set up MySQL Enterprise Plugin for Connector/PHP, you monitor the performance of your PHP applications through the Query Analyzer tab, as described in Section 3.2, “Query Analyzer User Interface”.
Troubleshooting
When the MySQL Enterprise Plugin for Connector/PHP connects to a MySQL server, it must be able to retrieve the UUID of that server. If there is a problem, for example if the mysql.inventory table does not exist or the MySQL user does not have privileges to read it, the problem is reported using the standard PHP error reporting mechanism. PHP classifies such a problem as an E_WARNING. Depending on how you have configured PHP error reporting, the associated messages might be displayed as part of the application output, stored in a PHP error log file, or discarded. For instructions to set up an Agent UUID, see Section 5.3.8, “Generating a New UUID”. For specific details about PHP error reporting, see http:// php.net/errorfunc.
Note
In PHP, the error_log PHP directive defines the location of the PHP error log, and its usage requires that the log_errors PHP directive is enabled.