I overclocked my HTC Inspire Android phone using the SetCPU app some months ago and wanted to make sure it worked well before I wrote about it, and it has. SetCPU doesn't just let you give your smartphone a boost in clock rate and performance, you can also use it to save some power by decreasing the clock speed when the phone is idle. If you're already messing around with rooting and custom roms, this is the sort of thing you'll want to check out.
SetCPU is a nifty little app that you can get for free if you sign up for the XDA forums, which is something you should do even if you don't want to overclock your Android phone. Joining is free, and the threads are filled with useful information about various Android ROMS and general tips and tricks. I wouldn't have been able to root and install CyanogenMod on my Inspire without reading extensively at XDA.
You can buy SetCPU for $1.99 on the Android Market or Amazon Appstore if you want to donate to the author or don't want to bother joining the XDA forums for some reason. SetCPU does require root access, but then if you're looking into overclocking your cell phone I suspect you already have root anyway.
SetCPU is simple to set up. It's been a while since I configured it on my phone, but I remember it was pretty hassle free. If you need/want detailed info, go check out the main site or the XDA thread.
On my Inspire I've configured SetCPU to overclock the 1GHz Snapdragon processor to 1.2GHz when the phone is being used interactively, and to decrease the clock rate to about 245 MHz when the phone is idle. This provided a small but measurable increase in performance in benchmarks and in battery life. You could push the clock speed higher, but then you run the risk of damaging your phone or shortening its useful lifespan or shortening the battery life. I don't see any need to go past 1.2GHz on the Inspire.
Since the Inspire is already a decent performer when you're running CyanogenMod 7, there's not a ton to be gained with a small overclock like the one I'm running. I imagine if you pushed the clock rate up to 1.5GHz you'd see more noticeable changes in day to day use. Of course it could be that I've gotten too used to running my phone at 1.2GHz and would really notice the performance difference if I turned off SetCPU and went back to stock clocks, but I'm not going to bother doing that.
For slower devices like the original Motorola Droid, which runs at 600 MHz, or the Nook Color, which runs at 800 MHz or so, SetCPU should be even more useful as a way to improve performance. For people with fast, modern devices the main benefit is the battery savings from underclocking when the phone is idle.
If you've got root on your Android phone then SetCPU is well worth the little bit of your time it'll take to install and configure. Especially if you've got a phone like the HTC Inspire, which has a fairly small battery and could use the battery life savings provided by underclocking the CPU whenever the phone is idle.