CPU load problem on a Php/MySQL linksite

is it coding or SpiderBots or cpanel?

[ozzyman]

I got a CPU load problem on my Php/MySQL based Link site. Site gets 6000-10.000 unique visitor and 120.000-180.000 page hits. Its not too many traffic I think. After my hosting (Shared) complained about CPU load 2 months ago I reduced MySQL queries and remove some sections on site. And indexed some table columns in my databas. But its not worked. Then I used a Cache system and included cached files on the site. Queries decreased 80%. But thats not decreased CPU load too. I have learnt "ob_start()" function uses CPU to compress file. I was using it for a long time and I removed it. Guess what,that's not worked too.

I wonder that how can I be sure what's making this CPU load. Which part of site or which code, function? In the logs MySQL skip-locking has most of the (CPU) resources .
I heard some debug tool, benchmark tests.
Is there a way to know whats going on in the code?
What tool (script) do you recommend?

On my site outgoing links open in a new window and every link has a frame to count hits, commenting and voting. So every click makes a database connection to UPDATE hit value.
I think this can make that CPU load problem.
But I tried to write these hit values to text file and import it to database time to time with cronjobs.
But nothing has changed. Same old heavy site.

Now I'm going to try write whole database to the static pages. Like this forum every heavy traffic site uses this in there days. But I dont have that huge traffic I think. In this case I dont really understand this:
If I write 100.000 pages from database in every 10 minutes isn't that a big cpu and HDD load too? What is the catch?

I read in a forum search engine bots makes a really heavy traffic. I use mod_rewrite (Search Engine Friendly urls) on my site to spiders visit whole site. And I see that I got very frequent Google,Yahoo,MSN and other bot visits. But everyone wants that huh? Should I ban these bots with robots text or meta tags?

I dont understand how can 10.000 visitors make that problem. I see that in Cpanel up to %700 cpu load with a red sign. Shared hostings say they allow "10%-20%" CPU usage maximum. But my server's Root admin says 99% of system resources are free on Linux command line! So which one is true? But cpanel resets the server after some time with this cpu load. So hosting providers dont want my site...

Please help me to get rid off this problem. Especially I want to know what is my site's problem? I tried 3 hosting provider and same problem comes up. Do I need a dedicated host for this site? (!)

[coopster]

Welcome to WebmasterWorld, ozzyman.

If you're traffic is high most shared providers would probably prompt you to move to a dedicated server -- they make money off having many small sites on a single box and they want to retain those customers. Without root access to the server you probably aren't going to get to monitor too much of the server load, etc. type of statistics and will have to take their (hopefully) honest word for it.

If your site performs the way you like and you are creating that kind of hit-traffic, maybe it's time to make money from your site ;) A dedicated server probably isn't a bad option and should pay itself off quite quickly.

[ergophobe]

If you want to find out what's eating up CPU time, you need to run a profiler like xdebug. You'll get tons of "CacheGrind" output and it will tell you how much time you are spending in any given function and help you identify bottlenecks. You'll need to profile your queries separately, but if there's a query that's taking a long time, the profiler should tell you which function it's in and then you cna optimize from there.