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
- 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 is structured like the Linux top command, implemented specifically for MySQL.
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.
- 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 STATUSwith 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.
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
- 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.