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

[Stephen]

for a webstore i would run about 1gb of ram unless it was a small
userbase, just for the reports to run in a timely manner.

I know here we tie into our MS_SQL collections Database and run huge
and byzantine reports and procedures from PHP because its the best
interface we have found for them, but they often take a very long time
to run so instead we run an Ajax wrapper of sorts to kind of keep the
thing running in either digestible amounts or just kind of sidestep
the hazard of the timeout.

and a report server is very good if you have no regular backup.

On Wed, Jul 8, 2009 at 12:42 PM, <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
>>
>>
>>
>> ---------------------------------------------------
>> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
>> To subscribe, unsubscribe, or to change your mail settings:
>> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>



-- 
A mouse trap, placed on top of your alarm clock, will prevent you from
rolling over and going back to sleep after you hit the snooze button.

Stephen