CPU load problem on a Php/MySQL linksite

Ozz

I got a CPU load problem on my Php/MySQL based Link site. Site gets 3000-4000 unique visitor and 70.000-100.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. 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? Php or MySQL?
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?

I tried PhpED Debug Profiler tool and it tells this database connection is takes most of execution times:
$connection = @mysql_connect($dbhost, $dbuser, $dbpass)
or die ("Can not connect to database");
$db = @mysql_select_db($dbname, $connection)
or die ("Can not select database.");

I use this database connection on config.inc file. 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.
Is there any difference between using mysql_connect and mysql_pconnect?
Maybe I must use file based counting system which I dont prefer. What do you think?
I dont understand how can 3000 visitors make that problem. Also in Cpanel I see %2 or %3 cpu load but it has a red sign. Shared hostings say they allow "10%-20%" CPU usage. So what's wrong on my condition?

Please help me to get rid off this problem. I tried 3 hosting provider and same problem comes up. Do I need a dedicated host for this site ? (!!!)

tsinka

Hi,

how do the counter/commentingvoting scripts look like, especially the SQL queries. Make sure to have indexes on all neccessary columns in the tables so that MySQL doesn't have to make full table scans (which can be the cause for a high system load).

You can check if all indexes are set up correctly by executing

EXPLAIN <query>

The result will help you optimizing queries and finding appropriate indexes.

Sometimes, just executing

ANALYZE TABLE <table>

on all tables used by a slow query speed up things a lot.

But the most important part is to check the indexes.

If that doesn't help then check if you have e.g. code that parses through large and/or multidimensional arrays.

Besides that, it's hard to guess anything without seeing the code or having more information about the server/cpu/ram/os.... itself.

Thomas