strace is another extremely useful tool. Sometimes it's really nice to be able to see exactly what's happening at the OS/program boundary (what exactly are we reading from the file? from the network?), and strace can do that in a very non-intrusive way - no root reqired, no recompile required.
Seconded. That apache process is chewing cpu, is it spinning in an inf loop internally to userspace? No, strace shows it repeatedly doing backend conn/op/disconnect - aha!
Another trick in this vein is/was to use 'pstack' to pull a C backtrace from a looping process. If you do that every second for 10 secs, you'll probably find where the process is looping. As a bonus, it also acts as a poor-man's-retroactive profiler (process X intermittently takes 30s to handle it's job instead of 2s - watch for one and when it happens, strobe the process with pstack to see what it's up to). Can be used in production, no need to deploy profiling build and run for ever to catch problem, etc.
(And a variant of this trick for interpreted languages is to hook an unused signal (SIGUSR2?) in your language to say "dump me a perl/python/etc backtrace to the log file". Assuming the loop is in your app code this will give you a language-level backtrace if you strobe the process with the signal)
NB1: pstack doesn't do anything which scripted gdb couldn't do, it's just more convenient
NB2: pstack is unavailable on 64-bit arches?
NB3: pstack needs some symbols to work with in the binary, which probably won't be the case with your system apache+mod_xxx
