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

[Lisa Kachold]

On Wed, Jul 8, 2009 at 5:02 PM, Eric Shubert <ejs at shubes.net> wrote:

> 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'
>
> ---------------------------------------------------
> 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
>

Reports server is generally the best bet, I agree.

Since the database is essentially the back end of a fluid stream, your web
processing and user experience can be vastly improved through standard
performance tuning:

Use the source my friend:
http://forums.mysql.com/read.php?24,92131,92131

And be sure to run a fine  http://portswigger.net/proxy/ Burp scan against
your queries before production, just in case.

-- 
(623)239-3392 Skype: obn0sis
(503)754-4452 www.obnosis.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20090708/d844394d/attachment.htm