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:
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.