Lots of things can make CPU usage go up. A lot of visitors will inevabtly make useage go up. The kinds of things that often drive usage beyone allowable limits can be things like sloopy scripts, and resource instensive scripts.
For instance phpbb is a great forum for a small community, but if it starts growing a lot, all of those fancy things like showing how many people are on-line and the link can take up a lot of processing power. Sometimes people also have problmes with wordpress sites that are getting a lot of visitors because the pages a crated dynamicly - every time someone views a page the server has to gather the info and compile the page before shipping it out - that can eat up resources like crazy.
I think an adverage user won’t have any problems with CPU usage. It’s when you start talking about visitors in the thousands per day that it can become a problem.