A MySQL Enterprise Monitor Query Analyzer Walkthrough

The Query Analyzer (QUAN) is one of the flagship features of the MySQL Enterprise Monitor (MEM). I wanted to take a few minutes to walkthrough a complete simulated example in order to demonstrate just how incredibly useful it is for becoming aware of problems, identifying the cause, and in finally fixing the issue.

First, I created a simple MEM test environment:

[root@mylab matt]# cat createsimpletestenv.sh 

#!/bin/sh

echo -n "Stopping any default mysqld instance..."

/etc/init.d/mysqld stop 

echo " done."

echo -n "Setting up fresh mysqld setup..."

cp -R /var/lib/mysqlfresh /var/lib/mysql

chown -R mysql:mysql /var/lib/mysql*

echo " done."

echo -n "Starting fresh default mysqld instance..."

/etc/init.d/mysqld start

echo " done."

echo 

echo "### Beggining MEM install: `date`"

echo

echo -n "Installing monitor..."

/root/mysqlmonitor.bin --mode "unattended" --unattendedmodeui "minimal" \
--installdir "/opt/mysql/enterprise/monitor" \

--adminuser "manager" --adminpassword "manager"

echo " done."

echo -n "Installing agent in 'phone home' mode..."

# Install Agent in "phone home" mode, to enable auto monitoring

/root/mysqlmonitoragent.bin --mode "unattended" --unattendedmodeui "minimal" \
--mysqlhost "localhost" --mysqlport 3306 --managerhost "localhost" \
--mysqluser "root" --mysqlpassword "root" --managerport 18443 \

--agentuser "agent" --agentpassword "agent" --generaluser "matt" \
--generalpassword "matt" --limiteduser "sid" \

--limitedpassword "sid" --agent_autocreate 1 --mysqlconnectiongroup "Production"

/etc/init.d/mysql-monitor-agent start > /dev/null 2>&1

echo " done."

echo

echo "### Ending MEM install: `date`"

echo

Then I created some sample data in my new MySQL 5.6 instance using the sakila and employee sample databases:

[root@mylab matt]# cat loadtestdata.sh 

#!/bin/sh

echo -n "Loading the sakila database ... "

mysql < /opt/mysql/sakila-db/sakila-schema.sql

mysql < /opt/mysql/sakila-db/sakila-data.sql

echo "done."

echo -n "Loading the employees database ... "

cd /opt/mysql/employees_db

mysql < employees.sql

echo "done."

Then I started monitoring the new MySQL instance within MEM:

Then I created a simple script to execute a slow query on our sample data:

[root@mylab matt]# cat runslowquery.sh 

#!/bin/sh

ITERATIONS=$1

if test -z $ITERATIONS || [ "$ITERATIONS" -le 0 ]; then

  ITERATIONS=10

fi

mysqlslap --port=3306 -c 50 -i ${ITERATIONS} --create-schema=test \

-q "select * from employees.employees, employees.salaries where employees.emp_no = salaries.emp_no order by salary desc limit 10"

exit 0

Then I adjusted the CPU usage advisor a bit so as to ensure that I can also get a simulated machine level (rather than MySQL instance level) critical alert about high CPU usage (just as an FYI):

Now let's finally simulate the situation where a developer adds a new query to the application; one that is in dire need of some performance tuning:

[root@mylab matt]# ./runslowquery.sh 50

Benchmark

Average number of seconds to run all queries: 66.574 seconds

Minimum number of seconds to run all queries: 59.725 seconds

Maximum number of seconds to run all queries: 78.115 seconds

Number of clients running queries: 50

Average number of queries per client: 1

Then let's come back to MEM to see the critical alerts for this MySQL instance:

The most serious issue is that we're currently experiencing a query pileup. This is because we cannot execute this query as fast as it's coming in. For example, the developer may have added a new query that's executed each time a user logs in and it's examining 2 billion rows. So now we know right away that there's likely N problematic queries. And when we look at the Query Analyzer (note: no additional setup is needed for MySQL 5.6 and later, as it uses Performance Schema for the QUAN data) for this instance, the problematic query immediately jumps out at us as it's at the top of our list and it has a QRTi score of 0:

From there we can get every possible statistic about the query. We can see when the query was first introduced--a new application was added this morning that's using this DB instance!?--how many rows it's examining each time, if a temp table is used, if a sort file on disk is used, what the join order was, etc. We can also see a sample query and the EXPLAIN output for the problematic query. With all of this information we can then determine exactly how to try and improve the problematic query.  

As you can see, it's extremely easy to identify a problematic query with the Query Analyzer. And since it also provides you with every possible detail about the query, it makes determining how we might improve the query very easy as well.

I hope that this was helpful! Please let me know if you have any thoughts or comments. And as always, Thank you for using MySQL!

P.S. If you're not already using MySQL Enterprise, then I encourage you to try it out today! You can get started right now using a 30 day trial license. 

Setting up a MySQL Enterprise Monitor 3 Test Environment

I wanted to quickly walk through my automated test environment setup for MySQL Enterprise Monitor (MEM). In doing so, I hope to help demonstrate how MEM 3 can easily be managed within an automated environment--whether you're using puppet, chef, cfengine, ansible, salt, $TheNextCoolDevOpsTool, or your own custom shell scripts like I am.

Here's how I setup my test environment:

1. I copy a "fresh" data directory into N locations.

shell# cat createtestenv.sh 

#!/bin/sh

echo -n "Setting up fresh mysqld_multi setup..."

cp -R /var/lib/mysqlfresh /var/lib/mysql

cp -R /var/lib/mysqlfresh /var/lib/mysql2

cp -R /var/lib/mysqlfresh /var/lib/mysql3

chown -R mysql:mysql /var/lib/mysql*

echo " done."

echo -n "Starting fresh mysqld instances..."

/etc/init.d/mysql start

echo " done."

2. I install the MEM service manager.

echo 

echo "### Beggining MEM install: `date`"

echo

echo -n "Installing monitor..."

/root/mysqlmonitor.bin --mode "unattended" --unattendedmodeui "minimal" \
--installdir "/opt/mysql/enterprise/monitor" --adminuser "manager" \
--adminpassword "manager"

echo " done."

3. I install the Agent in one of two ways.

#echo -n "Installing agent in host-only mode..."

# Install Agent in host only mode

#/root/mysqlmonitoragent.bin --mode "unattended" --unattendedmodeui "minimal" \
#--agent_installtype "standalone" --managerhost "localhost" --managerport 18443 \
#--agentuser "agent" --agentpassword "agent"

echo -n "Installing agent in 'phone home' mode..."

# Install Agent in "phone home" mode, to enable auto monitoring

/root/mysqlmonitoragent.bin --mode "unattended" --unattendedmodeui "minimal" \
--mysqlhost "localhost" --mysqlport 3306 --managerhost "localhost" \
--mysqluser "root" --mysqlpassword "root" --managerport 18443 --agentuser "agent" \
--agentpassword "agent" --generaluser "matt" --generalpassword "matt" \
--limiteduser "sid" --limitedpassword "sid" --agent_autocreate 1 \
--mysqlconnectiongroup "Production"

/etc/init.d/mysql-monitor-agent start > /dev/null 2>&1

echo " done."

echo

echo "### Ending MEM install: `date`"

echo

This demonstrates how you can use the unattended installation methods to easily script the installation and setup of the MEM Service Manager and the Agent. You can also then configure the Service Manager so that it will automatically begin monitoring any of the Agents that "phone home" (my own likely annoying terminology). You would do that using the MySQL Process Discovery Advisor. You can see a screenshot of where that's done below:

Using these methods you can have a MEM Agent installed and set up on any new machine that gets created and have it automatically reach out to the Service Manager. Then the Service Manager can begin monitoring that Agent and the associated MySQL instance using the default credentials specified. 

I hope that this is helpful! Several people have asked me about how I set things up for my own testing and demos, so I wanted to share. 

Thank you for using MySQL!