I wrote already about poor man’s query profiling techniques last summer. Today I’m going to share poor man’s contention profiling tool, for all these poor souls which do not have Solaris with dtrace or pstack, don’t want to run tcmalloc contention profiler, and simply need some easy hack to see ‘what the heck is going on in my server’. Here it is:
gdb \
-ex "set pagination 0" \
-ex "thread apply all bt" \
--batch -p $(pidof mysqld)
Run few times, and you will have enough samples to start judging. Do note, this may stop the process execution for a second, so do not spam it in too tight loop.
Once you have results it is just a matter of 20-liner script to extract any useful calculations :)
P.S. I’d love to see efficient pstack implementation for 64-bit Linux :)
update: this now lives at http://poormansprofiler.org
domas mituzas 
Domas,
You need to share the 20 lines of Perl/Python/Awk.
Collapse the call stacks into strings with this:
BEGIN { s = “”; }
/Thread/ { print s; s = “”; }
/^\#/ { if (s != “” ) { s = s “,” $4} else { s = $4 } }
END { print s }
And then aggregate the result with: ‘sort | uniq -c’
And the full script
# mpid = mysqld pid
mpid=15739 ; nsamples=1; sleeptime=1;
for x in $( seq 0 $nsamples) ; do
gdb -ex “set pagination 0” -ex “thread apply all bt” –batch -p $mpid
sleep $sleeptime
done | \
awk ‘BEGIN { s = “”; } /Thread/ { print s; s = “”; } /^\#/ { if (s != “” ) { s = s “,” $4} else { s = $4 } } END { print s }’ – | \
sort | uniq -c | sort -r -n -k 1,1
I modified the script to prepend the thread id to each comma-separated stack so that I can only look at traces for specific threads. This works great as I want to know where a thread spends all time (CPU, blocked on mutex, blocked on IO).