Other MySQL Performance Management Tooling

In my last post, [[Introduction to the MySQL Performance Schema]], I covered the performance_schema which is a relatively new feature of MySQL 5.5.  If you are using an earlier version of MySQL you won't have access to performance_schema.  This post covers your alternatives when you find yourself facing a performance problem as an "accidental MySQL DBA".  

If you have an older release of MySQL how do you do basic performance troubleshooting without the performance_schema tooling?  In this post I'll cover some of the other ways to track down your performance problems when you don't have access to performance_schema.  This post isn't meant to be exhaustive.  It's only meant to be a helpful guide for the "accidental DBA".  

"slow query log"

• aka "the slow log".  
• catches problematic queries solely from a wallclock perspective.  
• In 5.0 this requires a server restart.  Or you can run SET GLOBAL long_query_time = <seconds> to change dynamically
• This is configured in my.conf
  • log-slow-queries = <filename>
  • long_query_time = 2 ##number of seconds.  anything less than this is not logged
  • log-queries-not-using-indexes
  • log-slow-admin-statements
• mysqldumpslow:  will perform an analysis on your log files

If your application uses prepared statements then the slow log is actually more useful, in some cases, than performance_schema.  With prepared statements performance_schema can't reliably capture the actual statement executing in many cases.  Using the two in tandem often works well.  

innotop

Innotop is structured like the Linux top command, implemented specifically for MySQL.  

show processlist

SHOW PROCESSLIST is very similar to select * from sysprocesses in MS SQL Server.  If you create a cron job to snap this data off every 15 seconds or so you can get a good feel for your system over time.  Aggregating the output by the Command column with a script (innotop has this ability built in), or just by inspecting it visually, is a good way to find offending statements.  Look for threads that spend a lot of time in a particular state.  

Other Tools

• You should also consider reading my post on [[Linux Performance Management for the DBA]] if your MySQL instance runs on Linux.  
• Percona performance wizard: https://tools.percona.com/wizard.  While not exactly a tool to diagnose your performance problems, it will give you a list of recommended tuning parameters based on information you provide about your environment.  Perhaps something egregious is misconfigured?
• mysqlreport makes a nice report out of SHOW STATUS with some built-in smarts to determine if you have settings that should be adjusted.  
• The Percona Toolkit is a set of scripts and utilities that looks at the overall health of your MySQL server.  
• SHOW ENGINE INNODB STATUS:  gives you clues on innodb performance.  It also gives you info on the last deadlock seen on an innodb table.  

Monitor Deadlocks

There are a few different ways to monitor deadlocks.  

• https://www.percona.com/blog/2012/09/19/logging-deadlocks-errors/ :  this is a great blogpost on MySQL deadlocks
• /usr/bin/pt-deadlock-logger
• innodb_print_all_deadlocks is a configuration setting which sends all deadlocks to mysqld error log 

This post isn't meant to be definitive...just a quick post to help learn where to find more information for MySQL performance tuning.  Good luck.  

You have just read "[[Other MySQL Performance Management Tooling]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.