Using oprofile with the MySQL Server

Monty Taylor from MySQL Consulting gave a Percona Performance Conference talk on using oprofile, the linux kernel profiler, to diagnose MySQL server performance issues.

Installing oprofile requires the oprofile package (yum install oprofile), and optionally /boot/vmlinux for kernel symbols.

I was surprised at how useful and easy it was to get useful info out of oprofile, in his examples regarding memory allocation and type conversion performance problems (typical with newbie PHP applications.)

You can do near real-time mysqld library profiling with commands like:

# opcontrol --start --no-vmlinux
# watch -n 1 "opcontrol --dump && opreport -l mysqld | head -30 ; opcontrol --reset"
# opcontrol -h

Here is an example of a table scan on a MyISAM table on a busy production box, with the terminal display updated once per second:

samples % symbol name
1182 15.4753 _mi_rec_unpack
795 10.4085 my_hash_sort_simple
671 8.7850 my_strnncollsp_simple

Note that was 1,182 calls to _mi_rec_unpack per second!

oprofile does add load to a server, but that should be ok even on a fairly busy server. opcontrol loads a kernel module, so there is a small possibility it could crash the kernel.

Below are two useful IBM Developerworks links to advanced oprofile techniques related to scheduling performance:

IBM Developerworks: Using oprofile to profile L2 data cache misses
IBM Developerworks: OProfile analysis: Branch misprediction

Another powerful profiling tool is Sun’s DTrace. Most MySQL DTrace users are using Apple Mac OS X 10.5, less so Solaris. MySQL 5.4 and MySQL 6.0 include about 60 pre-defined static DTrace probes in the source.

wikipedia: DTrace

This entry was posted in Tech. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.