"slow query log"
SET GLOBAL long_query_time = <seconds>to change dynamically
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.
SHOW STATUSwith some built-in smarts to determine if you have settings that should be adjusted.
There are a few different ways to monitor deadlocks.
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.
Dave Wentzel CONTENT