dcsimg

A Look Inside MySQL Enterprise Monitor

October 5, 2010

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 24x7 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.

[root@sandbox 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers