Chris, I've been there and feel your pain (some from my own doing, though, so I can't throw too many stones).
In the meantime, as a stop-gap you might be able to get by with adding a couple of multi-column indexes on the database side (if you haven't already) for your commonly used 'WHERE' columns to limit the number of records being parsed on the base query. If your query is: SELECT * FROM foo WHERE bar = 'FooBar' AND foo_user = 2; Create an index with the two columns: ALTER table foo ADD INDEX(bar,foo_user); This will speed things up on the DBMS side and possibly help the page execution times - freeing up more memory since CF isn't waiting around. It won't help you with the processing that CF is doing of the returned query itself, though. For that you'll need to roll up your sleeves and apply some lipstick to that pig... :-) Regards, Jon On Apr 16, 2007, at 2:14 PM, Peterson, Chris wrote: > Jon, > > You are most likely correct, as the people who originally coded this > site didn't know how to do a cross-database join, so instead did a > SELECT * from Users, and now there are, ohhh, 26,000+ users on the > site. > Not to mention the SELECT * from items, which contains about 100,000+ > items. Unfortunately, this code is on about 50 - 100 pages, and > you can > imagine that its not written for readability or ease of > maintenance. I > am ban aiding it for now, and working on permanent fixes for the > future. > > Chris > > -----Original Message----- > From: Jon Clausen [mailto:[EMAIL PROTECTED] > Sent: Monday, April 16, 2007 1:25 PM > To: CF-Talk > Subject: Re: Urgent Help Please! > > Chris, > > First of all, I defer to any of the suggestions that Jochem gives you, > since he's way better in the server configuration arena than I > am (or many of us, for that matter). Windows defaults out of the > box to about 7200 concurrent threads (src - http://gregluck.com/blog/ > archives/2004/12/limits_to_threa_1.html - and his thread-test.jar > available from the same post) for the JVM, though which should be > adequate in most cases if what's being served from CF is optimized. > > If I may, I might also suggest taking a look at the queries on your > hardest hit pages. > > The biggest violator in CF I've found, personally, for > java.lang.OutOfMemory errors has been in manipulating the results of > queries that are returning a large number of records in the base > query (i.e. QoQ or <cfoutput query="..." group="...">). Sometimes > this starts to happen unexpectedly when a table has grown very large > over time and the original queries weren't designed to deal with > recordsets on that scale. Memory issues then get progressively > worse as the table grows larger until the number of records being > manipulated is so large that it creates the Out of memory errors and > eventually hangs the server. > > HTH, > > Jon > > PS - "Connection reset by peer: socket write error" which you also > mentioned can be caused by database connectivity issues - possibly > also > a query issue on the DBMS side. > > On Apr 16, 2007, at 11:31 AM, Peterson, Chris wrote: > >> Nope, debugging is not enabled on either cluster-member instance. >> Check >> this one out, now I am finding this in my err.log file: >> >> java.lang.OutOfMemoryError: unable to create new native thread >> >> It looks like its hitting some type of native OS memory limit for >> creating threads? I found this article about it: >> http://www.talkingtree.com/blog/index.cfm/2005/3/11/NewNativeThread >> > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275425 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
