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
