Monday, November 17, 2014

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






Wednesday, November 12, 2014

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! 

Friday, October 31, 2014

Monitoring MySQL from Oracle Enterprise Manager


I wanted to quickly remind everyone that we recently announced the GA release of Oracle Enterprise Manager for MySQL.




We know that many existing Oracle Database customers are also using MySQL, so we're very excited that you can now manage your entire hardware and software stack, including MySQL, all from within a single tool.

I also wanted to take the opportunity to answer a few common questions that I've seen come up over the last few weeks:

Q: Can I use the new Plugin with Oracle Enterprise Manager 11g (Grid Control)?
A: No. The Plugin requires Oracle Enterprise Manager 12c release 4 or later.

Q: Where can I download the Plugin?
A: The Plugin is available via Self Update within Oracle Enterprise Manager 12c release 4 and later. It's also available via My Oracle Support and the Oracle Software Delivery Cloud. You can find more information about the installation process here.

Q: Can I monitor MySQL Community Edition (GPL licensed) Targets?
A: Yes. There are no technical restrictions or behavioral differences for the Plugin depending on whether the MySQL Target is Community or Enterprise Edition. The licensed use of the Plugin to monitor that Target, however, is only covered by the 30 day trial license or a MySQL Enterprise license. If you have a valid license that covers the MySQL Target being monitored, then it does not matter which Edition of MySQL the Target is running.

Q: Where can I find documentation about the Plugin?
A: You can find it here and here.

We look forward to hearing your feedback on this new addition to MySQL Enterprise! Please let us know if you have any questions, encounter any problems, or have any feature requests. You can also engage in a discussion on our forums. Lastly, feel free to reach out to me directly as well, either here in the comments or via email. Thank you for using MySQL!


Friday, December 13, 2013

Monitoring MySQL in the 21st Century

In todays computing environments, the use of cloud based technologies--both public and private--along with various popular devops automation tools, are essential to effective and efficient IT operations.

MySQL Enterprise Monitor (MEM) 3 integrates very well into these modern operations environments. To demonstrate that point, I've created two short video demos that walk you through some helpful examples.

The first one walks through how you can monitor MySQL instances in the public cloud, using Amazon RDS as an example provider:


The second one walks through some examples of how you can integrate MEM 3 with your private cloud and devops automation tools:


You can find additional video demos, and more information about MEM 3 here.

MySQL Enterprise Monitor is just one part of what you get with a MySQL Enterprise subscription. You can find more information on the MySQL Enterprise subscriptions, and all that it includes, here.

Tuesday, August 13, 2013

See you at MySQL Connect!


The MySQL Connect conference is a great opportunity to engage with the MySQL community, as well as the engineers who work on the MySQL technologies at Oracle. The conference takes place on September 21-23 in San Francisco. There are 84 sessions in total, the full list being available here.

I will be presenting this year too. Sveta and I will be talking about how you can best deal with some of the most common challenges people encounter when creating and managing their MySQL backups in our Making Backups in Extreme Situations session, which is scheduled for 16:00-17:00 on the 21st in the Hilton - Union Square Room 3/4. 

I will also be helping out with various Hands On Labs (HOLs) that are planned, along with helping to man the official Oracle booth in the expo center.

I look forward to seeing many old faces and friends, and to making many new ones. Come and enjoy this great conference along with us!






MySQL Connect is only about 5 weeks away! Have you registered? If not, sign up now to save US$300 over the onsite price. And, remember you have the opportunity this year to add a third day of tutorials to your registration. Check out the Top 10 Reasons to Attend MySQL Connect to learn more.

Monday, June 24, 2013

Improving two-way communication.

You spoke:

  http://bugs.mysql.com/bug.php?id=41706

And we listened:


Now you can quickly and easily note that a bug or feature request affects you.


When you subsequently view the report, you will see that status indicated.



We highly value user input here at MySQL, and we wish to foster a vibrant environment of healthy two-way communication between our users and the internal teams. This is simply an initial move toward that bigger goal.

Our community of users and customers have always helped to drive the direction of MySQL, so we encourage you to continue to provide us with your feedback. I'm happy to announce that now, that's gotten just a little bit easier.


As always, thank you!