Skip navigation links
9.1.1 Enabling Query Analyzer by Changing the MySQL Client Application »
Section Navigation      [Toggle]

9.1. Enabling Query Analyzer

[+/-]

9.1.1. Enabling Query Analyzer by Changing the MySQL Client Application
9.1.2. Enabling Query Analyzer by Changing MySQL Server

There are three different ways of enabling query analyzer:

  • Change your MySQL client application to talk to the Proxy port you configured during installation. This requires changing your MySQL client application code, and may require that you stop and restart your MySQL client application , but does not require any changes to your MySQL server. For more information, see Section 9.1.1, “Enabling Query Analyzer by Changing the MySQL Client Application”.

  • Change your MySQL server to listen on a different port, and configure the Agent/proxy to listen on the original MySQL server port. This does not require any changes to your MySQL client application , but will require shutting down and restarting your MySQL server, which may affect your cache and performance. For more information, see Section 9.1.2, “Enabling Query Analyzer by Changing MySQL Server”.

  • Use IP tables to redirect the network packets to the agent/proxy.

Important

MySQL Query Analyzer is designed to gather query performance information from a variety of sources. Query Analyzer uses a new agent plug-in to proxy your queries and collect performance data that is then transmitted to the Enterprise Monitor. This is a new role for the Agent: it is no longer just monitoring, it is now optionally between your MySQL client application and the mysql server.

Depending upon your system load, it is possible to overload the proxy or have the proxy/agent consume system resources needed by mysql itself. In particular, the memory needed by the MySQL Agent for basic monitoring is fairly small and consistent and depends on the number of rules you have enabled. However, when the Query Analyzer is enabled, the Agent can use significantly more memory to monitor and analyze whatever queries you direct through it. In this case, the amount of memory used depends on the number of unique normalized queries, example queries and example EXPLAINs being processed plus the network bandwidth required to send this query performance data to the Service Manager. In general, the amount of memory used for the Query Analyzer is well-bounded, but under heavy load or, in some cases under older versions of linux, RAM usage by Query Analyzer may be too high for your environment and load.

Therefore we advise you to use this release of Query Analyzer extensively in development, test and stage environments under load for an extended period of time before considering usage in a production environment. For all deployments:

  1. Carefully monitor the Agent's resource consumption using the new graph Memory Usage - Agent graphs available on the Graph tab. You can also add an SMTP or SNMP notification to the new Heat Chart rule MySQL Agent Memory Usage Excessive.

  2. If the amount of memory consumed is too high, consider sampling queries during nonpeak hours or monitoring only a subset of queries on this system.

If you experience any problems with Query Analyzer, we're interested in working with you closely and quickly to resolve them. Please open a Support issue right away. We're already working hard on optimizing Agent/proxy RAM usage and are planning a series of rapid releases to quickly distribute these and other improvements to you.

Note that you must have enabled Query Analyzer within the agent/proxy during installation. If you did not enable Query Analyzer during the installation of the agent/proxy, check the following elements within the main mysql-monitor-agent.ini configuration file:

  • Add the proxy plugin to the plugins parameter: 

    plugins=proxy,agent

  • Ensure that the quan.lua items file is enabled in the agent-item-files configuration property: 

    agent-item-files = share/mysql-proxy/items/quan.lua,share/mysql-proxy/items/items-mysql-monitor.xml

  • Check and set the proxy-address, proxy-backend-addresses, and proxy-lua-script settings are configured: 

    proxy-address=:6446
proxy-backend-addresses = 127.0.0.1:3306
proxy-lua-script        = share/mysql-proxy/quan.lua

For more information on these configuration options, see Section 2.3.6.1, “MySQL Enterprise Monitor Agent (mysql-monitor-agent.ini) Configuration”.

Note

The Query Analyzer functionality may show as being enabled on a server, even though the modules within MySQL Enterprise Monitor Agent may not have been enabled.

You may also need to make some additional changes to the security configuration on your server to ensure that queries are correctly reported to MySQL Enterprise Service Manager:

  • You must ensure that each user configured within your MySQL client application that connects through the agent/proxy and is required to report query analyzer information is allowed to connect to the server from the host on which the agent/proxy is running. When the user connects to the agent/proxy, and the agent/proxy connects to the server the host of the agent/proxy will be used as the identifying client host name during the connection.

    Warning

    Because the proxy is providing the immediate connectivity to the MySQL server, authentication must use the proxy hostname, not the client hostname.

    To update your user credentials, you need to use the GRANT statement. For example: 

    mysql> GRANT SELECT,UPDATE,INSERT on database.* to 'user'@'localhost' IDENTIFIED BY 'password';

  • The MySQL client application user must have SELECT privileges on the mysql.inventory table. This table contains the server UUID and is required to report the query analyzer data to the MySQL Enterprise Service Manager. To enable this, use the GRANT option: 

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