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.
[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 :
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.
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.
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
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.