How to Effectively Monitor MySQL Servers © 2013, SolarWinds Worldwide, LLC. All rights reserved. Share: What Can Affect the Performance of MySQL Servers? As we know, MySQL is one of the most widely used open source relational database management systems (RDBMS) in the world. While MySQL is a great tool for storing, retrieving, and managing data, it is important to pay attention to the various factors that affect the performance of the MySQL server, and be aware of the consequences of not addressing these factors proactively—before they cause application outages. Just like any other database server, MySQL is not impervious to performance problems, utilization, management, and availability issues. To maintain business continuity, you cannot afford the MySQL database server having any downtime. So, what issues might affect your MySQL server that you need to be aware of? • Availability: Because of a possible network outage, device failure, application or server failure, or an operating system fault, your MySQL server can go out of service. • Performance: There are various parameters affecting the performance of your MySQL server. These can be application-based, operating system-based, hypervisor-based, VM-based, etc. You need to monitor them all to be able to maintain consistency in the performance of your MySQL server. What Do You Need to Monitor? With MySQL servers being so heavily utilized for varying workloads and end-user connections, there are many performance metrics that system administrators should stay aware of to avoid application and service downtime. Some of these key heath indicators are: • Database Memory Usage: To keep track of database utilization, you need to monitor the total memory used at any point in time. If you run out of space in your database, users will be unable to update or add information to the database. • Measurement of Read/Write Operation: Do you know how many kilobytes of data are being read out and written in? Monitoring these stats will enable you to measure the workload on your MySQL server. • Usage of Disk and Cache: You also need to be aware of the transactions that utilize the disk and cache resources. • Client Access License (CAL) monitoring will help you keep a close eye on your connection count. Reaching your CAL count can prevent users from connecting to the database server. Exceeding your CAL count could put you at risk of being suspected of engaging in software piracy. • Thread Statistics analyze how many threads are currently running, how many are created, and how many are cached. • Connection Status monitors and keeps track of opened and failed/aborted connections. • Database Availability monitors uptime statistics. • Transaction Logs: Monitoring your databases and transaction logs is important to ensure that you have adequate free space inside the database for new data to be added. If you’ve run out of space for new transaction logs to be created, then no new changes to the database can be made. Share: 2 These are just some of the key database performance statistics that are required to ensure your MySQL server is performing at the expected level. There are other aspects of monitoring required for deeper visibility into the performance of your database servers. You may want to monitor the performance of your operating system, the underlying hardware statistics, and the virtual machines where the MySQL database is running. A fault or failure of any of these components may affect the performance of your database server. Monitoring MySQL Servers using SolarWinds Server & Application Monitor SolarWinds Server & Application Monitor (SAM) delivers comprehensive monitoring, alerting, and reporting capabilities and powerful pre-installed dashboards to help you easily monitor the status and performance of your MySQL database servers. Server & Application Monitor is robust server monitoring software that allows you to measure MySQL server database performance from an end-user's perspective using out-of-the-box user-experience monitors such as the SQL Server User Experience Monitor and ODBC User Experience Monitor. These are flexible and customizable templates that assess the performance of your MySQL server database by retrieving performance data from it, allowing you to pin-point end-user issues with the database. In this document, we’ll learn how to effectively monitor MySQL databases using SolarWinds SAM and the various performance metrics and thresholds that you can use directly out of the box. 1. Deeper Performance Monitoring of MySQL Server To gain deeper insight into performance statistics of your MySQL server, • choose the server node where MySQL application is installed, and • drill down to the Application Details page that displays performance statistics and application metrics of the MySQL application. All the performance metrics are listed here in the form of easy-to-understand charts and graphs, so you can quickly see the availability status for each component which can be further explored to retrieve the actual metrics and monitoring results. If you want to view the monitoring statistics of the various component monitors within the MySQL application, just select the required component monitor and this will open the Application Component Details page. Share: 3 Application Details Page: Application Component Details Page: Share: 4 The statistics are displayed on customizable drag-and-discover charts that you can easily adjust to see the performance of your MySQL server database for a specified duration. All of the dashboards in SAM are fully customizable and you can edit them to retrieve any performance metric and counter as required. By default, SolarWinds SAM uses its out-of-the box ODBC User-Experience Monitor that is applied to the MySQL template to monitor the performance of your MySQL server. The ODBC User-Experience Monitor uses synthetic transactions to test the abilities of an ODBC-accessible database such as MySQL. The monitor connects to the database, performs a query, and then retrieves the data. There are many component monitors listed in this template that are used to retrieve the performance metrics of your MySQL server. You have the flexibility to add, edit, test, and delete component monitors. The various component monitors that are available within the ODBC User-Experience Monitor are listed below. For more details on the component monitors for MySQL servers, please visit this page. Performance Monitoring Metrics in ‘ODBC User-Experience Monitor’ Share: • Total Memory Used (MB) • Transactions that use cache • Kilobytes Received • Joins that perform table scans • Kilobytes Sent • Joins that check for key usage • Created Temporary Disk Tables • Joins that perform full scan • Created Temporary Files • Slow Queries • Created Temporary Tables • Max Used Connections • Opened Table Definitions • Free Memory in Query Cache (MB) • Opened Tables • Queries Registered in Query Cache • Opened Files • Deleted Queries from Cache • Statements Executed • Opened Connections • Key Reads • Aborted Connections • Key Writes • Aborted Clients • Table Locks Immediate • Thread Cache Size • Table Locks Waited • Slow Launch Threads • Threads Cached • Sort Scan • Threads Connected • Sort Rows • Threads Created • Select Scan • Threads Running • Select Full Join • Up Time • Select Range Check • Transactions that use disk 5 Once you have made changes to the component monitors, you can run a test before actually applying it to the node you want to monitor your MySQL server. 2. Monitoring Response Time of SQL Queries With the widespread occurrence of database transactions used to meet business requirements and the resulting influx of data, you need to ensure your database server is highly responsive and that it consistently remains so. SolarWinds Server & Application Monitor provides a unique capability to proactively test the response times for SQL queries. This capability measures how long a query takes to complete and how long the database takes to return the result of the pre-defined SQL query. Database administrators who combine SQL user experience monitoring with database server monitoring will help prevent service outages as a result of database issues. SQL Server User Experience Monitor is another component monitor in SAM that uses synthetic transactions to test the abilities of a SQL Server database. Like the ODBC User Experience Monitor, this also connects to the database, performs a query, and then retrieves data such as Response Time Warning Threshold, Response Time Critical Threshold, etc. 3. Monitoring Fault & Availability of MySQL Server SolarWinds SAM allows you to easily monitor the availability and fault status of the MySQL server, on the Summary tab. Just select the node where MySQL is installed and you can get availability diagnostics via SAM’s intuitive dashboard. SAM also provides visibility into the performance and health of your underlying server hardware. Availability of MySQL Server: Share: 6 Availability of the Node where MySQL Server is Running: 4. Hardware Health Monitoring Using SolarWinds SAM, you can monitor the state of your server hardware and key device sensors including temperature, fan speed, and power supply—and be alerted if they cross pre-defined thresholds. SAM’s LUCID (Logical, Useable, Customizable, Interactive, and Drill-Down) Web-based interface provides at-a-glance insight into the health of your network hardware, so you can quickly identify any potential issues. Some key hardware health statistics monitored by SolarWinds SAM are: • Fan status • Power supply status • Temperature • Disk status • Battery status • Array controller status • Physical memory status • Chassis intrusion status • CPU temperature and/or status Share: 7 Share: 8 5. Monitoring Virtual Machines & Hosts where MySQL is Running It can so happen that your MySQL server is running on a virtual machine (VM) or host, and the failure of the MySQL service is caused because of the failure in performance of the VM or host machine. You need to be able to monitor your entire VMware® virtual infrastructure from the highest to the lowest level: vCenter® → data center → cluster → ESX hosts → individual virtual machines With the built-in Integrated Virtual Infrastructure Monitoring functionality, SolarWinds Server & Application Monitor allows you to track availability and performance metrics of virtual machines including CPU, memory, storage, and network bandwidth utilization. • Automatically discover identify and monitor new virtual machines added to any VMware host server or updated during VMotion™ • Native alerting and reporting capabilities extend seamlessly to your virtual infrastructure SolarWinds SAM is capable of monitoring VMware ESXi/ESX Servers versions 3.5 and higher, as well as Microsoft® Hyper-V®. You can drill down from your data center all the way to the individual VM (which is managed as a node in SAM) to monitor key statistics such as VM CPU consumption, network traffic, memory consumption, and more. You can also leverage SAM’s intuitive interface to see performance metrics on simple drag-and-discover charts and customize VM- and host-specific performance reports. Share: 9 6. Additional Functionality for Comprehensive MySQL Monitoring Alerting & Reporting Using SolarWinds SAM, you can set intelligent MySQL alerts to notify you when the performance metrics meet custom thresholds. Additionally, you can also leverage the advanced reporting functionality to schedule and generate reports showing MySQL server availability and performance history. All reports can be easily modified, and custom reports are easily created. Dedicated MySQL Service Group SolarWinds Server & Application Monitor allows you to create a customizable group to aggregate all your MySQL servers and applications under one roof. Monitoring your MySQL servers is now easy as you can get all your performance and availability diagnostics on the same page with SAM’s Web console. Share: 10 Real-Time Process Explorer The Real-Time Process Explorer (RTPE) allows you to gain real-time insight into your server performance. The advantage of the RTPE is that you don’t need to physically, or remotely, log in to a particular machine and run the Task Manager to retrieve that machine's vital statistics. Information for both monitored and unmonitored processes is displayed directly through SAM. You can now monitor the performance of all your nodes that are running MySQL. Key Features & Benefits of SolarWinds Server & Application Monitor Agentless Application & Server Performance Monitoring • Monitors performance and user experience for virtually any application – Microsoft Exchange, Active Directory®, IIS, any ODBC database, and more • Monitors server hardware faults and operating systems across platforms – Windows®, UNIX®, Linux®, and more • Provides expert guidance on what to monitor, why to monitor it, and optimal thresholds • Includes customizable dashboards and reports showing trends, capacity, and performance • Quickly downloads and deploys in less than an hour, is simple to use, and easy on your budget SolarWinds (NYSE: SWI) provides powerful and affordable IT management software to customers worldwide - from Fortune 500 enterprises to small businesses. The company works to put its users first and remove the obstacles that have become “status quo” in traditional enterprise software. SolarWinds products are downloadable, easy to use and maintain, and provide the power, scale, and flexibility needed to address users’ management priorities. SolarWinds online user community, thwack!, is a gathering-place where tens of thousands of IT pros solve problems, share technology, and participate in product development for all of the company’s products. Learn more today at solarwinds.com. For additional information, please contact SolarWinds at 866.530.8100 or email [email protected]. To locate an international reseller near you, visit http://www.solarwinds.com/partners/reseller_locator.aspx. Share: 11