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

[Eric Shubert]

tshipley at deru.com wrote:
> In generic terms the database 101 class would say analyze your query to see if you can finagle the efficiency. A lot of reports link queries at the report/sub-report level, so look at the algorithm there. 
> 
> It is EASY to have a critical report that brings a system to its knees. Therefore, with any scale and $, DBAs replicate data from the transaction server to a report server. The report server doubles as a backup. 
> Sent from my BlackBerry Smartphone provided by Alltel
> 
> -----Original Message-----
> From: Joshua Zeidner <jjzeidner at gmail.com>
> 
> Date: Wed, 8 Jul 2009 12:23:15 
> To: Main PLUG discussion list<plug-discuss at lists.plug.phoenix.az.us>
> Subject: Re: PHP Script timing out and MySql using almost all the CPU and RAM 
> 	questions
> 
> 
>   sounds like you have either a MySQl bug or a problem query.  Turn on
> query logging and get the query that is causing the problem and post
> it here.
> 
>   -jmz
> 
> On Wed, Jul 8, 2009 at 12:21 PM, keith smith<klsmith2020 at yahoo.com> wrote:
>>
>> 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
>>

I've used that strategy numerous times. Depending on the query, adding 
an addition key/index can sometimes reduce reporting utilization 
substantially.

-- 
-Eric 'shubes'