Infrastructure at your Service

Grégory Steulet

Saving time and increasing service availability with MySQL Enterprise Monitor 2.3

Nowadays, MySQL Databases are encapsulated into many mission critical software solutions. Lots of companies host one or many MySQL databases in their data center, sometimes even without knowing it except when the MySQL service is not available anymore. In order to increase the service availability, it is mandatory to have a monitoring solution. Regardless of if you are using MySQL Server, MySQL replication or cluster, the Oracle/MySQL monitoring solution is called MySQL Enterprise Monitor.

MySQL Enterprise Monitor 2.3

MySQL Enterprise Monitor 2.3 provides a “health view” of MySQL servers by monitoring them continuously. MySQL Enterprise Monitor does not only alert database administrators by SNMP or SMTP when errors occur, but also really helps them in their daily business: It provides real-time advice regarding performances, security vulnerabilities and availability based on best practices settings. With MySQL Query Analyzer, MySQL Enterprise Monitor provides all necessary tools to improve application performance. Besides monitoring, MySQL Enterprise Monitor provides a graphical overview of key system resources such as network IO, disk IO, and CPU usage. All this statistical information is stored in the MySQL Enterprise Monitor repository, making it easy to consult historical data and perform trend analysis.

mysqlenterprisedashboard_tumb

How does MySQL Enterprise Monitor work?

Like several monitoring tools such as Oracle Grid Control, MySQL Enterprise Monitor works with a repository, with agents installed on monitored servers, and a web interface. The schema of a typical MySQL Enterprise setup is shown below:

The core system, called MySQL Enterprise Manager, is built with a Tomcat web server. It is in charge of collecting data from MySQL Enterprise Agents. This collected data is stored in the MySQL Enterprise Monitor repository which is nothing more than a MySQL database. The web interface, MySQL Enterprise Dashboard, is realized with JSP pages. The installation of MySQL Enterprise Edition is a straightforward process thanks to a nice and clear installation utility and a good documentation guide. However, having an easy to maintain and efficient setup needs the application of best practices and deeper knowledge.

MySQL Monitor main features:

The MySQL Monitor web interface is provided by MySQL Enterprise Dashboard. MySQL Enterprise Dashboard provides a nice view of the current server status and historical events, and allows setting up the monitoring behavior. Here are the main features of MySQL Enterprise Monitor:

  • Ability to monitor MySQL Database running MySQL 4.1 or higher
  • Global overview through a nice web interface
  • Proactive monitoring
  • Alerting tool on three levels (Critical alerts, warnings, info) through SMTP and SNMP
  • Historical Data Collection and Query Analyzer Data kept up to 24 months
  • MySQL Query Analysis with MySQL Query Analyzer
  • MySQL Cluster and MySQL replication performance monitoring

MySQL Agents setup

MySQL Agent is in charge of monitoring MySQL Server. This task includes checking the server accessibility but also more detailed information such as the load of the MySQL Server, variables status, operating system information and finally communicating all this information to the MySQL Enterprise Service Manager. In case of disrupted communication between these two components, MySQL Enterprise Agent is able to store data locally and send it back once the communication is re-established.

MySQL Agent can also act as proxy service; the main purpose of this architecture is to analyze queries. In this context, MySQL Agent gets queries from applications and forwards them to the MySQL Server. An agent can act as proxy server for only one server. That is why it is mandatory to setup several agents in order to do query analyses of several MySQL Server on the same physical server.

MySQL Enterprise Agent configuration is described in a single ini file as shown below:

[mysql-proxy]
# Common Parameters
 plugins=proxy,agent
 keepalive = true
log-file = mysql-mysqld4-agent.log
 pid-file=/u00/app/mysql/product/agent-2.3.1/mysql-mysqld4-agent.pid
# Agent Parameters
 agent-mgmt-hostname = http://localhost:18080/heartbeat
 agent-mgmt-username = agent
 agent-mgmt-password = manager
 mysqld-instance-dir= etc/mysqld4
 agent-item-files = share/mysql-monitor-agent/items/quan.lua,share/mysql-monitor-agent/items/items-mysql-monitor.xml,share/mysql-monitor-agent/items/items-mysql-cluster.xml,share/mysql-monitor-agent/items/custom.xml
 agent-uuid = 7f5475a8-1694-448e-b100-302451a9ca5d
#aggr-mem-baseurl=http://localhost:18080/v2/rest
# Proxy Parameters
 proxy-address=:33004
 proxy-backend-addresses = /u00/app/mysql/admin/mysqld4/socket/mysqld4.sock
 proxy-lua-script = lib/mysql-monitor-agent/lua/quan.lua

MySQL Enterprise Monitor strengths and weaknesses

MySQL Enterprise Monitor is a great tool providing lots of possibilities in terms of monitoring as already described. The main assets are the following:

  • Monitoring MySQL Server from version 4.1 to 5.x, MySQL Query optimizer only for version 5.x
  • Full support of MySQL Cluster and MySQL Replication, including MySQL Query Analyzer functionalities
  • Plenty of monitored metrics including MySQL and operating system metrics
  • Nice web interface providing a quick global overview of services availability
  • Straightforward and intuitive usage

Among the main weaknesses:

  • Only included as part of a MySQL Enterprise subscription (30 days trial)
  • In order to use Query analyzing it is mandatory to go through a MySQL Proxy which can slow down performances
  • If multiple MySQL instances are installed on the same server, it is mandatory to have several agents installed for query analyzing purposes.

I recommend the usage of MySQL Enterprise Monitor for any productive and potentially sensitive MySQL Server. This tool can of course monitor MySQL Service and therefore improve service availability. It can also help developers to improve code through the query analyzer service and therefore save significant amount of time for business activities.

3 Comments

  • Mike says:

    I’m running MySQL 5.5 with MySQL Enterprise Manager 2.3.6

    I was wondering how many use mysql proxy “in the middle” in order to get Query Analyzer functionallity? This is really all I want it for. I’m at a crossroads trying to decide if going forward want to start implimenting mysql agent “in the middle” on all our production databases to get the Query Analyzer functionality. The main drawbacks I’ve found are the following:

    – Upgrading the agent requires downtime
    – Not able to use IP level access control on user accounts. Have to use ‘webuser’@’%’ for example.
    – Not able to see what servers users are logging in from since all logins are from the agent and are displayed as “localhost” in the processlist.
    – If agent crashes, access to database is lost.
    – Increased overhead.

    I’m just not sure it’s worth it!!

    Just wondering what others are doing. Is MySQL Agent used “in the middle” commonly in production databases?

    Thanks!

  • gregory steulet says:

    Hi Mike,

    Thanks for your message, I’m always happy to discuss with MySQL guys.

    Well regarding companies that run mysql proxy in order to get query analyzer functionalities, it is obviously hard for me to provide you a precise answer.

    However what I can advise you, is enabling query analyzer only when you encounter performance problems in order to find the root cause of the issue. Indeed the query analyzer can overload the proxy and therefore consume lot of system resources which are needed by MySQL. It is the same story with the slow query logs. You have to enable them when you are doing tests, or when you are running a new application, when tests have been validated, simply switch it off till you have performance problems. MySQL Query Analyzer is a wonderful tool to investigate problems, when you have no problem do not use it. (Have a look on advices provided on http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem-query-analyzer-enabling.html)

    Regarding the implementation of MySQL proxy in order to have the query analyzer functionality in your production environment: Part of the solution, at least regarding SPOF and downtime, can be provided with a high availability solution.. Something like a master active / master passive replication with a virtual IP provided by corosync and pacemaker. It sounds maybe a bit heavy but such a solution can ensure data high-availability and service high-availability. For your information I’ll make a post on pacemaker/corosync installation in few days. Anyway in a production environment that have availability constraints it is advised to use a high-availability solution.

    Now regarding the IP level access control, I’ve to say that I need to investigate that. Hope that I partially answered to your questions.

    Best regards

    Gregory Steulet

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Grégory Steulet
Grégory Steulet

Chief Financial Officer (CFO) and Partner Manager
Delivery Manager