Using the Agent Proxy

There are three different ways of feeding Query Analyzer using the proxy component of the MySQL Enterprise Monitor Agent:

• 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 the MySQL server. For more information, see Section 3.1.5.1, “Enabling Query Analyzer by Changing the MySQL Client Application”.

• Change your MySQL server to listen on a different port, and configure the proxy to listen on the original MySQL server port. This does not require any changes to your MySQL client application, but requires shutting down and restarting the MySQL server, which might affect its cache and cause a temporary performance drop. For more information, see Section 3.1.5.2, “Enabling Query Analyzer by Changing MySQL Server”.

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

Example 3.1 Routing with Proxy and IP tables on Linux

This example assumes that default ports are used, it's on localhost, the proxy is installed on to the database box itself, and that it will not work on an intermediary server.

Using the Agent Proxy

sudo iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 6446

Disabling (with -D):

sudo iptables -t nat -D PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 6446

Important

MySQL Query Analyzer gathers 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 could 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.

Note: zone-specific memory usage is not available for 32-bit Solaris operating systems.

If you experience any problems with Query Analyzer, we are interested in working with you closely and quickly to resolve them. Please open a service request right away. We are working hard on optimizing Agent/proxy RAM usage.

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

• Add the proxy keyword to the plugins parameter. For example:

plugins=proxy,agent

agent-item-files = share/mysql-monitor-agent/items/quan.lua,share/mysql-monitor-agent/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-monitor-agent/quan.lua

For more information on these configuration options, see Section C.2.1, “MySQL Enterprise Monitor Agent Configurable Options”.

You might 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:

• Check the authorization for each user ID used in the connect strings for MySQL client applications that connect through the proxy. Each such user ID must be allowed to connect to the MySQL server from the host on which the agent is running. When the application connects to the server with the proxy as an intermediary, the client host name is the host name of the system running the agent.

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, 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; 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';

3.1.5.1 Enabling Query Analyzer by Changing the MySQL Client Application

Generally, changing your MySQL client application is the easiest and recommended method. For example, given a typical structure like the one in the following figure, you would modify the client application so that it communicated to the proxy rather than directly with the MySQL server,

Using the Agent Proxy

The following figure shows an example of the structure when communicating through the proxy:

Figure 3.6 MySQL Enterprise Monitor User Interface: Query Analyzer Agent/Monitor Topology

To enable Query Analyzer within your MySQL client application:

1. Make sure that the MySQL Enterprise Service Manager and your MySQL Enterprise Monitor Agent are configured and running.

2. Confirm the configuration of your agent by examining the contents of the etc/mysql-monitor- agent.ini file within your installed Agent directory.

Queries are sent to the host specified in the proxy-backend-addresses parameter, and the agent listens for connections to be redirected to the server on the host name and port configured in the

proxy-address parameter.

3. Now modify your MySQL client application to communicate with the address specified in the proxy- address parameter.

Alternatively, if you do not want to modify your application directly, you can use iptables or firewall rules to redirect queries from the original host/port combination to the agent's port.

Because connections to the MySQL server come from the proxy, not the original host, the user

credentials must be have a suitable GRANT statement for connections from localhost, or the host on which the agent and its proxy component run. The user name and password information are passed directly through the proxy from the client to the server.

4. Confirm that your MySQL client application still operates normally. There should be no difference between communicating directly with the MySQL server and communicating via the proxy.

Note

If you are using the mysql client to connect to the proxy and your backend servers, make sure that you are communicating with the proxy over the right port. By default, if you specify localhost as the host name, then mysql connects using the local Unix domain socket, rather than the TCP/IP socket.

You can enforce mysql to use the right port either by explicitly requesting the protocol type, or by using the IP address rather than localhost. For example, both of these command lines start the client using the right protocol:

Note

It is recommended that you use one agent and associated proxy per MySQL server instance. A single proxy is not able to forward queries to multiple MySQL server backends.

3.1.5.2 Enabling Query Analyzer by Changing MySQL Server

When enabling Query Analyzer by changing the MySQL Server, shut down your server, edit the MySQL configuration file, and then restart MySQL. Also, change your Agent configuration so that the proxy listens on the original MySQL TCP/IP port. To use this method, follow these steps:

1. Edit the /etc/my.cnf or other MySQL configuration file and change or add the port setting from its current value (default 3306), to another value. For example:

port = 3307

2. Shut down your MySQL Server.

3. Start your MySQL Server and confirm that is running.

4. Edit your MySQL Enterprise Monitor Agent configuration so that the proxy listens for connections on the original MySQL port:

proxy-address=:3306

proxy-backend-addresses = 127.0.0.1:3307

5. Stop and restart MySQL Enterprise Monitor Agent.

You can now connect to your MySQL server through the MySQL Enterprise Agent Proxy Service by connecting on the original port:

shell> mysql --host=127.0.0.1