NYPHP Con: “Maximum Velocity MySQL” – Benchmarking and Profiling

Jay Pipes is the Community Relations Manager, North America for MySQL, Inc. This was a three-hour tutorial about ways to fine-tune your MySQL queries.

This is part three of my notes. This post may contain inaccuracies, typos, boo-boos, etc. Jay says his slides will be available on his web site, so be sure to check there as well. If you have questions, direct them to Jay or consult the MySQL documentation.

Benchmarking and Profiling tips

• Give yourself a target
• Record everything
• Isolate the problem (Shut down other programs. Stop network traffic to machine.)
• Get familiar with EXPLAIN as a means to optimize queries
• Optimize within reason. Makes more sense to speed up the 30 second query than the 1 second query.

Benchmarking Toolbox

• SysBench
• mysqlslap (MySQL version 5.1+)
• Apache bench
• Supersmack
• MyBench

Profiling: Diagnosing a running system to identify performance bottlenecks

Possible areas for bottlenecks:

• Identify performance bottlenecks
• SQL Coding and Index Usage
• Memory
• CPU
• I/O (Disk)

Profiling toolbox

• SHOW commands (SHOW PROCESSLIST, etc…)
• EXPLAIN and the Slow Query Log
• MyTop
• List of Linux Power tools (gprof/oprofile and vmstat.ps/top/mpstat/procinfo)
• apd for PHP developers