On Tue, Sep 09, 2003 at 02:10:43PM +1000, Gavin Cooney wrote: > > I've just launched a new online Multiple choice questions website for the Board of > Studies, and as you can imagine (coming up to HSC time) it's getting huge usage. > We're getting about a thousand tests done a day at the moment, (and it's only been > up for 5 days!). > > > The request has exceeded the allowable time limit Tag: CFQUERY The specific sequence > of files included or processed is: > /some path... /online_mcq/some page name.cfm > > The site is running on a dedicated Linux server with a gig and a half of ram. The DB > runs on MySQL. The pages that are getting this error have nothing too special about > them, and they do not have a timeout set on the CFQUERY tags. > > Can anyone advise me on how to prevent this? > > Currently I have the following cf admin settings: > Limit simultaneous requests to 20 > Timeout Requests after ( seconds ) 180 > and in the Advanced settings for the datasource: > Limit to 20 connections. > Maintain Connections with 20 minute timeout. > > If anyone could give me some tips, I would be very grateful
You've got several options: - reduce the number of hits you're getting - reduce the time it takes for a query to operate - reduce the number of queries performed Since you can't stop people from accessing the site, you have to look at what you can affect. I take it it's not possible to put the database on a better machine? More memory & fast scsi raid systems can help here. I'm aware of a PostgreSQL system that's handling 1,000 concurrent users, but it's got 6gig of ram & a raid array. If you're stuck with that box it might be possible to tune the MySQL settings for better performance. Are ColdFusion and MySQL on the same box? Having each on their own box means the OS doesn't have to keep switching tasks, which improves performance. I'd recommend turning on MySQL tracing/logging if it's got it and see what types of queries are causing problems. It might be that some queries are not optimized. Check for correct index usage. Check any long-running queries to see if they can be re-coded. The only CF specific I can suggest up front is to check your CFQUERY caching usage. In queries that don't reflect data that's going to change, you can set the CACHEDWITHIN="#CreateTimeSpan(day, hour, min, sec)#" and have the query come directly from the CF server's memory. Oh, and there's always the option of increasing the timeout value... Cheers Paul Haddon Technical Services Manager Formstar Print Technologies (Insert Obligatory PostgreSQL Plug Here) --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
