If you have isolated the periodic slowdown problem to your queries and MySQL database, and optimising the queries has helped, then I suggest that you look at the following :
a) upgrade your MySQL to the latest production release (5.0.xx). b) Use latest MYSQl Admin to tune up the caching on your server. I suggest that possibly one or more of the caches are being filled up and flushed, making subsequent queries slow down whilst table/index/ query cache loads up your data into memory once again. That would account for periodic slowdowns, and this can be sensitive to the traffic on the server, explaining why your production server displayed the problem more than development. This characteristic would be sensitive to server load/traffic... If your server has plenty of memory then increasing the cache sizes can help a lot. c) take a good close look at your queries. My SQL query caching is case sensitive so something like 'Select * from mytable Where Fred Is Not Null' is not deemed to be the same query as 'SELECT * FROM MYTABLE WHERE FRED IS NOT NULL' even though they both work and get the same results and so you can end up with copies of the same or similar queries filling up the query cache, making it more likely your offending query is getting flushed out if not most recently used on a busy server. (and the same applies to in- memory temporary tables linked to a particular query). Basically the rule for mysql is to make sure that all of your column names and table names etc have a consistent use of case and also the SQL keywords such as Select, FROM & etc. I am not sure if white space makes any difference (I'd like to think not but wouldn't put anything past it...) Perhaps others can comment on this aspect. Cheers, Bryn Parrott On May 23, 5:26 am, nedlud <[EMAIL PROTECTED]> wrote: > For what it's worth, I seem to have resolved this issue by optimising > some of the sql queries. > > I still can't claim to understand exactly what the problem was, but by > using *a lot* of cfdump statments, I found that a particular query > occasionaly ran *way* slow (we are using MySQL 4.1). Adding an index, > and optimising some queries to use joins instead of sub-queries seems > to have made the problem go away. > > At least I can say it doesn't seem to have been my coldfusion code > that was at fault. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
