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.