Re: Command-line PHP script CPU usage goes sky-high, stays there--why?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 11-Dec-07, at 2:13 PM, Per Jessen wrote:

René Fournier wrote:

However, the number of socket clients connecting in the past 3-4
months has steadily increased, and this seems to have exposed (if not
created) a strange performance "issue" with PHP 5.2.4, MySQL 5.0.45
and/or Mac OS X Server 10.4.11. (I say "and/or" because I am unsure
where the problem's cause really lies.) Basically, after the script
has been running for a day or so (processing essentially the amount
data that used to take two weeks), the CPU usage of the machine goes
from 30% (normal) to 80-90%.

Have you tried stracing it to see what's really happening when the load
goes that high?

Good advice, since I think there's nothing left for me to do but inspect the MySQL process.

Incidentally, I've made some changes to the script a week ago, which has seemed to improve the situation somewhat. Now, the script has run for nearly 7 days without interruption or high CPU load. Problem solved?.... Again this morning, I noticed CPU went up to 90% and is staying there. (Previously, this would happen after 1-2 days.)

The number of distinct MySQL connections remains low, since the script (which runs in a loop, with a sleep(1) and a timeout on the stream_socket_select()), only creates one MySQL connection in the beginning. All MySQL queries run through that.

The script has run for 6-7 days, during which time it's executed 2.7 million queries (mostly SELECTs) and created 105,198 external, short- lived child processes (each lasts about a second or two, then closes after mysql_close())--I don't think this is an issue.

Memory usage seems okay. By the end of 7 days, the script's memory usage has peaked at 4MB (out of 16MB max). Typically it's around 3MB. MySQL memory usage doesn't seem to be a constraint. I'm using my- huge.cnf, and the nature of the queries is fairly regular. I would say that the database structure is not an issue (though I could be wrong!)--everything is pretty well normalized and indexed. I'm logging slow queries, non-indexed SELECTs, etc.

I'm really not sure what to try next. ps -aux shows MySQL as hogging the CPU, not PHP or Terminal:

PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE
342 mysqld 83.3% 16:13:19 33 125 139 435M 4.75M 439M 539M
385 Terminal 4.7% 5:36:35 22 184 251 20.1M 33.5M 28.8M+ 256M
1190 php 4.3% 3:13:33 1 15 148 6.51M 8.15M 12.1M 89.0M
0 kernel_tas 1.3% 2:02:40 47 2 619 5.00M- 0B 219M- 1.26G-

It's really strange and strangely consistent. The script will run for a few million cycles, whereupon MySQL suddenly uses 50% more CPU. But for what?

I'm looking at tutorials on ktrace and kdump to see what I can learn from MySQL. I wonder if I would have this problem under Linux...

...Rene 

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux