PHP
Article

First Look at Yahoo’s MySQL Performance Analyzer

By Younes Rafie

When you deploy an app to production servers, you may experience some database problems as your data grows. Some tools can help you debug and solve your problems, like MysqlTuner, Percona, etc. In this article, we’re going to explore the Yahoo MySql performance analyzer and see what the main features are.

Vector illustration of gauge with pointer in the red, indicating speed

Installation

The analyzer uses SNMP when available, so it’s a good idea to have it installed on your database server.

Because this is a JAVA application, make sure you have JDK installed before proceeding. It requires version 8 to run, but version 7 is fine since it doesn’t use any of version 8’s features. You can read more in the readme file in the repo.

I have a demo application on my Vagrant box, and I will install the analyzer in the root of my application. Let’s start by logging into our machine using ssh and cloning the repository.

vagrant ssh
git clone git@github.com:yahoo/mysql_perf_analyzer.git myperf

To build the application, you need to run the mvn clean package command. After the build process is done, you’ll find a myperfserver-server.zip file under the perfJettyServer/target directory. Move that file to a new directory on your server and extract it. The resulting output contains start_myperf.sh and stop_myperf.sh scripts to manage the server. Let’s use the start script to launch the application. You can read more about the available options in the repo.

Managing Users

After starting the server, open your browser using your machine hostname and the specified port (vaprobash.dev:9092/myperf). You can log in with the default admin account (username: myperf, password: change).

The users management page can be accessed from the top left menu. It allows you to add, edit, delete and confirm users, and you can use it to change the default admin password.

User management page

Dashboard

The main dashboard shows the current status of your databases along with other details like CPU usage, running threads, dead locks, etc.

The second tab on the dashboard shows alerts from the last 24 hours. We will talk more about alerts and how to configure them later.

Configuring DB Groups

To monitor a database or a set of databases, you can create a new database group from the top left menu and select DB Info. After filling the inputs you can visit the dashboard to see your databases status. You can restrict the database access to a certain group of users using the Access Control tab.

Add DB Group

Monitoring Processes

You can do real time monitoring of your system resources by accessing the Top page from the menu. If you ever used the top command on linux, this basically does the same thing for your databases. It shows your memory, CPU usage, DB connections, network transfers, etc. You can start and stop the process at any time using controls at the top of the page.

Top page

You can also use the SNMP program when available, but check the box to enable it before starting the process.

Charting

If you’re a reporting fan and you prefer working with charts to compare your database server performance over time, the charts tab provides a charting tool along with date range selection to help you understand your server performance. You can also compare different server metrics like memory usage, DB connections, etc.

Charts

Exploring Database Meta Data

Although we are used to exploring database meta data from our database management apps like SequelPro, Yahoo performance analyzer provides a simple way to explore database and table meta data, like columns, views, indexes, triggers, etc.

DB Meta

Explaining Queries

Probably the most used feature in performance analysis is Profiling Queries, since it helps a lot in detecting slow queries and explaining them. It also shows data about memory usage, execution time, cache usage, etc.

Explain queries

Alerts

Alerts are an indispensable feature in monitoring your database performance. Basically, you set a rule that defines when your system is (or will be) experiencing problems.
From the User Defined Alerts menu at top of the page, you can create an alert using predefined metrics. As an example, I will use the SNMP._.MEMAVAILREAL metric, which defines the available memory and I will set the alert to trigger if it went below 1000KB.

Alert 1

You can also create alerts based on a database query. We can create an alert to trigger when the list of queued jobs is higher than 100.

Alert 1

After creating your alerts, you need to move to the subscribers tab on the same page and select your DB Group and check alerts that you want to attach.

Now, if you navigate to the Alerts page, you’ll find a list of errors from the last 24 hours, and you can select a date range to see previously occurred errors.

Alerts

Conclusion

This article was a brief introduction to the main features of the Yahoo performance analyzer, and there’s still a lot to discover. The project is still being developed – you can contribute on Github.

A more advanced post covering the analyzer’s usage on a real database is coming soon!

If you have any question or comments, please post them below.

  • http://careersreport.com Mary Buck

    I want to show great work opportunity… three to five hours of work daily… Weekly paycheck… Bonus opportunities…Payscale of $6k to $9k /a month… Just few hours of your free time, any kind of computer, elementary understanding of web&and stable connection is what is required…Get informed more about it by visiting my profile>page

  • http://careersreport.com Edna Davis

    I want to show great work opportunity… three to five hours of@ work daily… Weekly paycheck… Bonus opportunities…Payscale of $6k to $9k /a month… Just few hours of your free time, any kind of computer, elementary understanding of web and stable connection is what is required…Get informed more about it by visiting my profile>page

  • http://CareersReport.com peggy Larson2

    I have to share this great internet freelancing !opportunity… 3 to 5 hours of work /a day… Payment each week… Performance depending bonuses…Payscale of $6k-$9k /month… Just few hours of free time, desktop or laptop, most basic understanding of web and trusted internet connection is what is required…See more on my disqus–page

  • http://careersreport.com lori.fay

    I want to show@ great work opportunity… three to five hours of work daily… Weekly paycheck… Bonus opportunities…Payscale of $6k to $9k /a month… Just few hours of your free time, any kind of computer, elementary understanding of web and stable connection is what is required…Get informed more about it by visiting my profile>page

  • Bruno Seixas

    This really looks like a great tool to use. Will give it a go :) Thanks for sharing the knowledge.

    • younesrafie

      Thanks ;)

  • https://plus.google.com/+FelipeMartinsKazumi Felipe Martins

    I’ll take a look on that. Great post, dude! ;)

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Get the latest in PHP, once a week, for free.