On Wed, Jul 8, 2009 at 5:02 PM, Eric Shubert wrote: > tshipley@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 > > > > Date: Wed, 8 Jul 2009 12:23:15 > > To: Main PLUG discussion list > > 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 > 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@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