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@gmail.com>
Date: Wed, 8 Jul 2009 12:23:15
To: Main PLUG discussion list<plug-discuss@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@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@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@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@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)