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

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