Monitor and Optimize Slow Queries with PMM and EverSQL – Part One

PMM EverSQL optimization integration

A common challenge with continuously deployed applications is that new and modified SQL queries are constantly being introduced to the application. Many companies choose to use a database monitoring system (such as PMM) to identify those slow queries. But identifying slow queries is only the start – what about actually optimizing them?

In this post we’ll demonstrate a new way to both identify and optimize slow queries, by utilizing the recent integration of Percona Monitoring and Management with EverSQL Query Optimizer via Chrome browser extension. This integration allows you to identify slow queries using PMM, and optimize them automatically using EverSQL Query Optimizer.

Optimizing queries with PMM & EverSQL

We’re using PMM to monitor our MySQL instance, which was pre-loaded with the publicly available StackOverflow dataset. PMM is configured to monitor for slow queries from MySQL’s slow log file.

monitor slow queries dashboard on PMM

We’ll begin with a basic example of how EverSQL can provide value for  a simple SELECT statement. In a follow-up blog post we’ll go through a more sophisticated multi-table query to show how response time can be reduced from 20 minutes to milliseconds(!) using EverSQL.

Let’s have a look at one of the slow queries identified by PMM:

PMM EverSQL optimization integration

In this example, the table posts contains two indexes by default (in addition to the primary key). One that contains the column AnswerCount, and the other contains the column CreationDate.

CREATE TABLE `posts` (
 `Id` int(11) NOT NULL,
 `AcceptedAnswerId` int(11) DEFAULT NULL,
 `AnswerCount` int(11) DEFAULT NULL,
 `ClosedDate` datetime DEFAULT NULL,
 `CreationDate` datetime NOT NULL,
 `ViewCount` int(11) NOT NULL,
 KEY `posts_idx_answercount` (`AnswerCount`),
 KEY `posts_idx_creationdate` (`CreationDate`)

As you can see below, EverSQL identifies that a composite index which contains both columns can be more beneficial in this case, and recommends to add an index for posts(AnswerCount, CreationDate).

EverSQL optimization report

After using pt-online-schema-change to apply the schema modification, using PMM we are able to observe that the query execution duration changed from 3m 40s to 83 milliseconds!

execution time improvement with EverSQL


Note that this Extension is available for Chrome from the chrome web store:

EverSQL for Database Monitoring Applications


If you’re looking for an easy way to both monitor for slow queries and quickly optimize them, consider deploying Percona Monitoring and Management and then integrating it with EverSQL’s Chrome extension!

Co-Author: Tomer Shay

Tomer Shay, EverSQL


Tomer Shay is the Founder of EverSQL. He loves being where the challenge is. In the last 12 years, he had the privilege to code a lot and lead teams of developers, while focusing on databases and performance. He enjoys using technology to bring ideas into reality, help people and see them smile.



Quick installation guide for Percona Cloud Tools for MySQL

Here in Percona Support, we’re receiving several requests per day for help with Percona Cloud Tools installation steps.

So I decided to prepare a step-by-step example of the installation process with some comments based on experience.  Percona Cloud Tools is a hosted service providing access to query performance insights for all MySQL uses. After a brief setup, you’ll unlock new information about your database and how to improve your applications. You can sign up here to request access to the free beta, currently under way.

Some notes

  • It’s recommended to do the installation under root.
  • If you’re installing pt-agent as root then .pt-agent.conf should be placed in root $HOME
  • You could became root with “sudo -s” command and in this case your homedir is still unchanged and not homedir of root user.
  • So I would strongly recommend to login as root or to “sudo -i” to become root and check if your HOME and PWD are the same: env | egrep -i ‘home=|pwd=’

Sign Up

Go to the URL and sign up (or log-in): https://cloud.percona.com/


Copy your API key:
On this URL: https://cloud.percona.com/api-key
Or by menu: Agents -> API Key


Percona-Toolkit Download

Download Percona-Toolkit:
For example:

  • DEB: for Debian/Ubuntu like systems
  • RPM: for RedHat, CentOS

Percona-Toolkit Installation

yum install http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.6-1.noarch.rpm

wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/deb/percona-toolkit_2.2.6_all.deb
dpkg -i percona-toolkit_2.2.6_all.deb

pt-agent installation

Run this command:
pt-agent --install --user={mysql username} --password={password} --api-key={API Key copied from web site}

Note: add there your username, password and API Key

pt-agent installation output

You should see this:

pt-agent --install --user={user} --password={password} --api-key={api-key}
Step 1 of 11: Verify the user is root: OK
Step 2 of 11: Check Perl module dependencies: OK
Step 3 of 11: Check for crontab: OK
Step 4 of 11: Verify the API key: OK
Step 5 of 11: Connect to MySQL: OK
Step 6 of 11: Check if MySQL is a slave: NO
Step 7 of 11: Create a MySQL user for the agent: OK
Step 8 of 11: Initialize /etc/percona/agent/my.cnf: OK
Step 9 of 11: Initialize /root/.pt-agent.conf: OK
Step 10 of 11: Create the agent: OK
Step 11 of 11: Run the agent: 2014-01-29T20:12:17 INFO Starting agent
pt-agent has daemonized and is running as PID 13506:
--lib /var/lib/pt-agent
--log /var/log/pt-agent.log
--pid /var/run/pt-agent.pid
These values can change if a different configuration is received.
The agent has been installed and started, but it is not running any services yet. Go to https://cloud.percona.com/agents#node1 to enable services for the agent.

Agent is installed

pt-agent configuration

Goto: https://cloud.percona.com/agents and select your newly created agent

Then enable Service:

  • Agents -> Services -> Query Analytics -> On (Push Off button and configure values)
  • Save




Now wait a few min, check Status Log: Agents -> select agent -> Status Log
You should see there: “Agent OK”


Now wait (~3 min) until pt-agent will add jobs to crontab.
You should see there: “Services OK”


Then run some slow queries and wait (~3-5 min).
If everything is ok then you should see there “Exit: 200″


Now check Query Analytics.


There is a “Help Me” button at the bottom of the page so you can ask for Support if you have any questions and our Support team will gladly help you.



The post Quick installation guide for Percona Cloud Tools for MySQL appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com