Appendix F MySQL Enterprise Monitor Frequently Asked Questions

Note

MySQL Enterprise subscription, MySQL Enterprise Monitor, MySQL Replication Monitor, and MySQL Query Analyzer are only available to commercial customers. To learn more, see: http://www.mysql.com/products/.

FAQ Categories

General Usage

Questions

Questions and Answers

F.1: My MySQL Enterprise Service Manager is behind a firewall but it cannot communicate with the Oracle support web site. I normally use a proxy service to access external web sites. How do I configure the proxy settings for MySQL Enterprise Monitor User Interface?

To configure a proxy service, edit the apache-tomcat/conf/catalina.properties file within the MySQL Enterprise Service Manager installation directory. Change the http.proxyHost and http.proxyPort properties:

http.proxyHost=proxy.example.com
http.proxyPort=8080

Restart the MySQL Enterprise Service Manager for the change to take effect:

shell> mysqlmonitorctl.sh restart

F.2: How do I change the name of a server?

Go to the Manage Servers panel within Settings and click Rename server.

Renaming the server in this way will override all other server naming, including changes to the agent configuration.

F.3: I have started a Data Migration of my old data for a server to MySQL Enterprise Service Manager 2.0, but I have noticed that the performance of the monitor server has degraded significantly. Can I stop the migration?

You can stop the migration of your historical data at any time. Go to the Manage Servers display of the Settings panel and click Stop next to each server that is being migrated. You can restart the migration at any point.

F.4: I have set the graphs to update every 5 minutes, and the page refresh to occur every minute. The page is refreshing correctly, but the graphs do not seem to update.

The graph refresh and page refresh are two different parameters. The graphs are updated according to their refresh period, regardless of the refresh period set for the main display page.

F.5: During query analysis, I am unable to obtain EXAMPLE or EXPLAIN information when examining the detail of the analyzed query within the Query Analyzer panel.

You must explicitly enable the EXAMPLE and EXPLAIN query functionality. Make sure that you have enabled both panels. See Section 3.2.3, “Query Analyzer Settings”.

F.6: I have enabled EXPLAIN queries for Query Analyzer, but no queries with the EXPLAIN data are showing up in the display.

Query Analyzer only obtains EXPLAIN information when the MySQL Enterprise Monitor Agent identifies a long running query. If none of your queries exceed the defined threshold, then the EXPLAIN information is not obtain and provided to the Query Analyze for display.

To change the query duration at which an EXPLAIN is triggered, you must edit the share/mysql-proxy/quan.lua file within the MySQL Enterprise Monitor Agent directory on each server. You need to change the value configured in the auto_explain_min_exec_time_us. The default is 500ms:

---
-- configuration
--
-- SET GLOBAL analyze_query.auto_filter = 0
if not proxy.global.config.quan then
        proxy.global.config.quan = {
                analyze_queries  = true,   -- track all queries
                query_cutoff     = 160,    -- only show the first 160 chars of the query
                num_worst_queries = 5,
                auto_explain     = true,
                auto_explain_min_exec_time_us = 500 * 1000
        }
end

The value is expressed in microseconds, which is why the value must be multiplied by 1000. To reduce this value to 100ms, modify this line:

auto_explain_min_exec_time_us = 100 * 1000

These changes take effect without restarting MySQL Enterprise Monitor Agent.

F.7: Does Query Analyzer work with all versions of MySQL and the MySQL Client Libraries?

The MySQL Proxy component, and Query Analyzer, require that clients connecting through MySQL Enterprise Monitor Agent are using MySQL 5.0 or later. Clients that use the libmysqlclient library provided with MySQL 4.1 or earlier do not work with MySQL Enterprise Monitor Agent.

F.8: Why do some rules appear to have a Severity of Unknown?

Due to timing issues, certain rules such as 32-Bit Binary Running on 64-Bit AMD Or Intel System and Key Buffer Size Greater Than 4 GB do not evaluate correctly due to timing issues. This is a known issue that is expected to be resolved in future versions of MySQL Enterprise Monitor.

F.9: Can I run MySQL Enterprise Service Manager on machine with other applications running?

You can, but Oracle recommends running your MySQL Enterprise Service Manager on a dedicated machine, especially when monitoring many agents.

F.10: Why does the file apache-tomcat/logs/tomcat.log show error messages saying This is very likely to create a memory leak.? Is that anything to be concerned about?

This message is sometimes produced by underlying components of the web stack on web application reload or shutdown, and is not a cause for concern. It is not practical to shut off these spurious messages within Tomcat.

F.11: Why does monitoring a MySQL instance with FEDERATED tables cause extra connections, and decreased performance?

When the agent starts, it executes a discovery process that performs a number of INFORMATION_SCHEMA queries that gather table information for rules. These INFORMATION_SCHEMA queries can be costly on instances with many tables, particularly with large numbers of FEDERATED tables to another instance, as each table has a new session opened for it on the target machine.

If you do not want these statements to execute on agent startup, any statement relating to the INFORMATION_SCHEMA can be removed from the agent's share/mysql-proxy/items/items-mysql-monitor.xml file. However, this will cause some of the rules provided with your subscription to not work, as they rely on this information.

MySQL Monitor

Questions

Questions and Answers

F.1: What is MySQL Enterprise Monitor?

Included as part of a MySQL Enterprise subscription, the MySQL Enterprise Monitor is a distributed, web-based application that helps customers reduce downtime, tighten security and increase throughput of their MySQL servers by telling them about problems in their database applications before they occur. It is downloadable from the Oracle Software Delivery Cloud web site and is deployed within the safety of the customer data center.

F.2: What are the features and related benefits of the MySQL Enterprise Monitor?

The MySQL Enterprise Monitor is like having a "Virtual DBA Assistant" at your side to recommend best practices to eliminate security vulnerabilities, improve replication, and optimize performance. For the complete features and benefits, visit the http://www.mysql.com/products/enterprise/monitor-features.html.

F.3: What are the immediate benefits of implementing the MySQL Enterprise Monitor?

Often MySQL installations are implemented with default settings that may not be best suited for specific applications or usage patterns. The MySQL Advisors go to work immediately in these environments to identify potential problems and proactively notify and advise DBAs on key MySQL settings that can be tuned to improve availability, tighten security, and increase the throughput of their existing MySQL servers

F.4: What are the long-term benefits of the MySQL Enterprise Monitor?

Over time, the task of managing even medium-scale MySQL server farms becomes exponentially more complicated, especially as the load of users, connections, application queries, and objects on each MySQL server increases. The Enterprise Monitor continually monitors the dynamic security, performance, replication and schema relevant metrics of all MySQL servers, so as the number of MySQL continues to grow, DBAs are kept up to date on potential problems and proactive measures that can be implemented to ensure each server continues to operate at the highest levels of security, performance and reliability.

F.5: How is the MySQL Enterprise Monitor installed and deployed?

The Enterprise Monitor is powered by a distributed web application that is installed and deployed within the confines of the corporate firewall.

F.6: How is the Enterprise Monitor web application architected?

The Enterprise Monitor web application comprises three components:

F.7: What makes MySQL Enterprise unique?

Of the products on the market that monitor MySQL, SQL code and OS specific metrics, the MySQL Enterprise Monitor is the only solution that is built and supported by the engineers at MySQL. Unlike other solutions that report on raw MySQL and OS level metrics, the MySQL Enterprise Monitor is designed to optimize the use of MySQL by proactively monitoring MySQL instances and providing notifications and 'MySQL DBA expertise in a box' advice on corrective measures DBAs can take before problems occur.

F.8: What versions of MySQL are supported by the MySQL Enterprise Monitor?

The MySQL Enterprise Monitor supports MySQL versions 5.1 and above.

F.9: What operating system platforms are supported by the MySQL Enterprise Monitor?

The Enterprise Monitor Service Manager is fully supported on most current versions of Linux, Windows and Windows Server Editions, Solaris and Mac OS X. The Monitor Agent supports any platform supported by the MySQL Enterprise server. For the complete list of MySQL Enterprise supported operating systems and CPUs, visit the http://www.mysql.com/support/supportedplatforms/database.html.

F.10: What are the MySQL Enterprise Advisors and Advisor Rules?

The MySQL Enterprise Advisors are a set of best practice guidelines for the optimal use of MySQL. Advisors are spread across database specific disciplines and comprise a set of MySQL Advisor Rules that proactively monitor all MySQL servers and report on database application problems before they occur. Each Advisor Rule provides a detailed overview of the problem it is designed to identify, advise on how to correct the problem, specify commands to implement the recommended fix and links to additional resources for additional research into the issue at hand.

F.11: How are subscribers notified about the availability of new or updated MySQL Enterprise Monitor, MySQL Enterprise Advisors and Advisor Rules?

Customers will receive notifications of new and updated MySQL Enterprise Monitor and Advisors as they become available via the MySQL Enterprise Software Update Service. Notifications will be generated and sent based on the customer profile and the MySQL Enterprise subscription level.

MySQL Query Analyzer

Questions

Questions and Answers

F.1: What is the MySQL Query Analyzer?

The MySQL Query Analyzer allows DBAs, developers and system administrators to improve application performance by collecting, monitoring, and analyzing queries as they run on their MySQL servers. http://www.mysql.com/products/enterprise/query.html

F.2: How is the MySQL Query Analyzer installed and enabled?

The Query Analyzer feature is installed with the Monitor Agent. It is enabled during agent installation and can be toggled between collection and pass-thru modes from the Query Analysis page of the Enterprise Monitor.

F.3: What overhead can I expect when the MySQL Query Analyzer is installed and enabled?

The average overhead when in active collection mode is in the 15-20% range. In pass-thru mode the overhead is minimal, weighing in at 1-5% on most MySQL systems of average load.

F.4: Can I leave the MySQL Query Analyzer enabled at all times?

We have customers who have the Query Analyzer enabled and collecting queries on their development and QA servers so they can tune their code and monitor the fixes as part of the development process. For production systems, Query collection and analysis can easily be toggled on when a slowdown occurs. To avoid collection mode overhead many users are using simple scripts to enable the Query Analyzer to sample queries during non-peak hours, typically during 30 minute windows. They can then view the collected queries using the date/time or interval filter options.

F.5: What are the main features and benefits of the MySQL Query Analyzer?

For the complete features and benefits, visit the http://www.mysql.com/products/enterprise/monitor-features.html

F.6: What are the typical use cases of the MySQL Query Analyzer?

The typical use cases for developers, DBAs and system administrators are:

F.7: How are subscribers notified about updates to the MySQL Query Analyzer application components?

Customers will receive notifications of the MySQL Query Analyzer updates as they become available via the MySQL Enterprise Software Update and Alert Service. Notifications will be generated and sent based on the customer profile and the MySQL Enterprise subscription level.

F.8: What makes the MySQL Query Analyzer unique?

Other products (free, open source and commercial) that provide MySQL query monitoring are dependent on the MySQL Slow Query Log being enabled and available for sampling. While this provides some time savings over the DBA collecting and parsing the Log, the Slow Query Log comes with overhead and does not capture sub millisecond executions. The log data also grows very large very quickly.

The MySQL Query Analyzer collects queries and execution statistics with no dependence on the SQL Query Log, it captures all SQL statements sent to the MySQL server and provides an aggregated view into the most expensive queries in number of executions and total execution time. It is also fully supported as part of the MySQL Enterprise subscription.

F.9: How can I get the MySQL Query Analyzer?

The MySQL Query Analyzer is built into the MySQL Enterprise Monitor.

To experience the MySQL Enterprise Monitor for 30 days, visit the http://www.mysql.com/trials/

F.10: Does Query Analyzer work with MySQL Cluster?

Yes, providing that exact node is monitored with an agent and query analyzer has been enabled for that node. Note that you must be accessing your cluster data through a standard MySQL node for this to work.

F.11: Does Query Analyzer capture queries by the root user?

Yes, Query Analyzer captures all queries by all users providing that the queries are sent through the proxy port configured by the MySQL Enterprise Monitor Agent.

F.12: Does Query Analyzer enable me to monitor the disk reads and writes during a query?

No, that information is not available to the query analyzer.

F.13: Does Query Analyzer handler prepared statements?

At this time, the query analyzer does not track server-side prepared statements. However the default configurations for most client-side libraries for MySQL don't use them, they emulate them client-side, and those will be tracked by the query analyzer.

F.14: How much degradation in performance does mysql-proxy introduce?

At the very least it's equivalent to a network hop in latency. The degradation is directly related to your average query execution time. If your queries execute in microseconds (which can happen if served from query cache) then the degradation will be higher, and noticeable. We've seen some applications that actually do work when they execute queries, the degradation is much less, and in some limited cases because of scheduling, the application actually has better throughput.

F.15: Does the query analyzer look at all queries? Or only queries which would show up in the in the slow-queries log?

The Query Analyzer sees all queries that you route through the agent/proxy that performs the query analysis and aggregate them directly.

F.16: Does the "Rows" area show the rows returned/updated or the rows visited by the query?

Returned/updated. We don't have visibility into how many rows were touched. at an instance level. Some of the graphs we provide will show you when you're queries are touching a lot of rows.

F.17: Do the MySQL clients have to connect to the port of the mysql proxy to enable the QA?

Yes, or you can re-direct them in various ways, by reconfiguring mysqld to listen to some other port, and the proxy to 3306, use iptables redirection, etc. We have some examples in the manual for the product on how to do it (semi)-transparently.