A Look Inside MySQL Enterprise Monitor


MySQL Enterprise Monitor was highlighted during “MySQL Sunday” at the Oracle Open World conference this past month. Given the ease of implementation and helpful GUI tools, MySQL
Enterprise Monitor
provides almost instant benefits for any MySQL shop.

MySQL Enterprise Monitor Overview

Basically, if you want an extra set of eyes that never get tired and that
alleviate common pain points for DBAs and developers, MySQL Enterprise Monitor
is the tool for you. As a DBA, I use this tool to pinpoint problems in the
system and to extend limited access to our Developers so they can see how MySQL
is running on their own. This makes it very easy to deploy advanced MySQL
features more efficiently to build, maintain and scale our on-line
applications. When you purchase MySQL support, you can expect to gain full
access to the following:

  • MySQL Enterprise Server software, the most reliable,
    secure and up-to date version of the world’s most popular open source
    database
  • MySQL Enterprise Monitor that continuously monitors your
    database and proactively advises you on how to implement MySQL best
    practices
  • MySQL 24×7 Production Support with fast response times to
    assist you in the development, deployment and management of MySQL
    applications

MySQL Enterprise is available in four tiers (Basic, Silver, Gold, Platinum).
The Enterprise Dashboard is available at all levels, just with different levels
of monitoring and features.

Enterprise Monitor Key Features

  • Increase transparency in your environment for all your
    MySQL servers
  • Monitor over 600 MySQL and operating system variables
  • Get a look inside MySQL with their 30+ graphs
  • MySQL Query Analyzer
  • See what your replication topologies look like through
    auto detection and grouping
  • Use MySQL Replication Monitor to see how your replicated
    servers are doing
  • Customize the MySQL Enterprise Monitor for your specific
    needs or specific needs of a cluster
  • Get notified of issues before they become costly outages
    using threshold driven alerts
  • LDAP integration
  • MySQL support can gain access to packaged, server specific
    information on MySQL, OS and query diagnostic to stream line Support
    issues

Monitor Server Setup

To demonstrate how easy it is to implement MySQL Enterprise Monitor I have
an example below that I ran on my sandbox.

[[email protected] chris]# ./mysqlmonitor-2.2.0.1709-linux-x86_64-installer.bin
Language Selection
Please select the installation language
[1] English - English
[2] Japanese - ?--???
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the setup wizard for the MySQL Enterprise Monitor
You will need a product key to complete installation. When the Monitor first 
starts, provide your existing MySQL Enterprise subscription or Trial credentials 
to obtain a key. Need an account? Go to http://www.mysql.com/trials/ to register 
for a trial subscription.
----------------------------------------------------------------------------
Please specify the directory where the MySQL Enterprise Monitor will be installed
Installation directory [/opt/mysql/enterprise/monitor]:
----------------------------------------------------------------------------
Tomcat Server Options
Please specify the following parameters for the bundled Tomcat Server
Tomcat Server Port [18080]:
Tomcat Shutdown Port [18005]:
Tomcat SSL Port [18443]:
Is SSL support required? [y/N]:
----------------------------------------------------------------------------
Repository Configuration
Please specify the following parameters for the bundled MySQL server
Repository Username [service_manager]:
Password :
Re-enter :
Bundled MySQL Database Port [13306]:
----------------------------------------------------------------------------
Configuration Report
Note:
The settings you specified will be saved here:
/opt/mysql/enterprise/monitor/configuration_report.txt
IMPORTANT: This configuration report includes passwords stored in plain text; it
is intended to help you install and configure your agents. We strongly advise
you to secure or delete this text file immediately after installation
Press [Enter] to continue :
----------------------------------------------------------------------------
Setup is now ready to install MySQL Enterprise Monitor on your computer.
Do you want to continue? [Y/n]: Y
----------------------------------------------------------------------------
Please wait while Setup installs MySQL Enterprise Monitor on your computer.
 Installing
 0% ______________ 50% ______________ 100%
 #########################################
----------------------------------------------------------------------------
Completed installing files
Setup has completed installing the MySQL Enterprise Monitor files on your
computer
Uninstalling the MySQL Enterprise Monitor files can be done by invoking:
/opt/mysql/enterprise/monitor/uninstall
To complete the installation, launch the MySQL Enterprise Dashboard and complete
the initial setup and product activation information. Refer to the readme file
for additional information and a list of known issues.
Press [Enter] to continue :
----------------------------------------------------------------------------
Setup has finished installing MySQL Enterprise Monitor on your computer.
View Readme File [Y/n]: n
Info: To configure the MySQL Enterprise Monitor please visit the following page:
http://localhost:18080
Press [Enter] to continue :

MySQL Enterprise Dashboard

Agent Setup

The best part of the agent installation is that you only need to run one
command. Granted, there are a lot of variables you must declare; however, after
you have declared all the variables you can automate agent installations system
wide with ease.

Unattended Installation Example

./mysqlmonitoragent-2.2.2.1729-linux-glibc2.3-x86-64bit-installer.bin
–mode unattended –installdir
/opt/mysql/enterprise/agent –mysqlconnmethod
socket –mysqlsocket /tmp/mysql.sock –mysqlport 3306 –mysqluser mysql_user
–mysqlpassword bigsecret –enableproxy 1 –proxyport 6446 –managerhost
192.168.1.101 –managerport 18080 –managersslport 18443 –agentuser agent
–agentpassword agent_bigsecret

After running the agent installations in a test environment, I can now login
to my Enterprise Dashboard and check out my servers. Please note that I blurred
out the names of the servers in the image below.

Switching the Application Around

In the graph below you will notice a change in the overall write activity on
one of my servers. This is an example of switching a system from using REPLACE
to INSERT … ON DUPLICATE UPDATE. After this change, the
server was much happier.

example of switching a system from using INSERT … ON DUPLICATE UPDATE

In this example, I was running a query that was NOT indexed for some time. I
found the problematic query through the Query Analyzer. As you can see below,
after the index was added our load decreased significantly. This allowed us to
expand the use of this server and when the managers see the pretty graphs they
are always impressed.

after the index was added our load decreased significantly

Query Analyzer – Overview

The Query Analyzer is one of the most useful tools I have ever used for SQL
optimization. The MySQL Query Analyzer helps both developers and DBAs improve
application performance by monitoring query performance and accurately
pinpointing SQL code that is causing a slow down. I use it to identify long
running queries, queries that return warnings or errors, optimize queries
during development and monitor applications in production environments. In the past,
it took a lot of time to analyze, interpret and test all the queries in an
application. With the MySQL Query Analyzer, I have literally diagnosed a
mal-formed query problem in seconds. Just as a side note, this mal-formed query
caused a consistent 2-point increase in overall server load and didn’t return
any results.

Query Analyzer Key Benefits

  • You no longer need MySQL Proxy – use the MySQL Connector
    Plug-ins
  • Filter for specific query problems like full table scans,
    bad indexes, long running queries, queries that return warnings and or
    errors
  • GUI interface makes everything easy to read
  • Continuously monitor queries from application concept to
    inception
  • Quickly identify problem queries that impact the
    throughput of your applications
  • View query details using visual explain and seeing example
    queries
  • Fine-tune the problematic SQL code to regain peak
    performance
  • Get query information on a specific time by highlighting
    your graphs
    • Clickable query activity graphs for analyzing queries
      during the usage spikes in key systems resources
  • SQL warning and error counts for each query
  • SQL Execution Time for each query along with Avg. time of
    all
  • Aggregated view into query execution counts, run time,
    result sets across all MySQL servers with no dependence on MySQL logs or
    SHOW PROCESSLIST
  • Searchable and sortable queries by query type, content,
    server, database, date/time, interval range, and "when first
    seen"
  • Historical and real-time analysis of all queries across
    all servers

MySQL Enterprise Dashboard

Closing Thoughts

MySQL Enterprise Dashboard has a great set of tools and is easy to
implement. If you are looking to save time and have a one-stop-solution for
monitoring MySQL this tool is for you. I think the Query Analyzer alone is
worth the cost of support! If you are unsure if MySQL Enterprise Monitor is
right for you then you should try it before you buy it.

»


See All Articles by Columnist

Chris Schneider

Chris Schneider
Certified MySQL professional, actively involved in the MySQL community for the better end of a decade. I have designed, implemented and maintained small to large scale MySQL installations while training and mentoring teams of DBAs. This includes building architectures from the ground up and improving on those that are currently in place while emphasizing scalability, performance and ease of use. Most of the work I am involved with now is in the Web 2.0 arena. I also run a community site, EverythingMySQL.com, that promotes collaboration within the open source community, specifically, MySQL.

Latest Articles