PHP Script timing out and MySql using almost all the CPU and RAM questions

[keith smith]

Hi Everyone,

I do support for an online store.  

Last night we were trying to run a report that was taking forever.  It is a lot of data so I expected it to timeout.  The owner says he has successfully run the report before.  I shelled in and found MySql was using 98.3% of the CPU and I think 4.7% of Memory.

I restarted MySql and the load went down to nothing. 

While I was in there I noticed we have 513764k of RAM, and we were using almost all of it and we were using some swap as well.

Here is what I just pulled off the system.  

top - 11:12:54 up 229 days, 20:08,  3 users,  load average: 0.21, 0.16, 0.07
Tasks:  80 total,   2 running,  78 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.3%sy,  0.0%ni, 95.7%id,  0.0%wa,  3.0%hi,  0.0%si,  0.0%st
Mem:    513764k total,   506652k used,     7112k free,     4904k buffers
Swap:  3723784k total,    32276k used,  3691508k free,   311520k cached


This raises a number of questions:

1) What could cause the MySql server to start using so much CPU?  After a restart we ran the report again and the CPU usage was much less.  The report still timed out.

2) I'm wondering if more RAM would make the system more responsive?  I've seen the benefits of RAM first hand but not in how it would speed up a web server.

3) The report we were running gave no indication of timing out. I'm wondering how the following PHP.ini directives come into play:

- max_execution_time = 30 : In seconds.  Why would the script appear to keep working? 

- max_input_time = 60 : Same question as above.

I'm wondering if setting the memory - ini_set('memory_limit', '64M'); in that app, if that would help much and if I extend the time if that might help - ini_set('max_execution_time',240);  along with ini_set("max_input_time", 240)  ?

Thanks in advance for your help!

------------------------
Keith Smith