Paul, Locks and blocks can occur and have no overall impact on general DB server performance - but still bring down your site because they prevent other queries from completing. I'd bet my left eye that's what's happening. Here's what I would do.
* monitor for locks and blocks and long running queries. * consider Nolock hints where it might make sense to use them. Particularly on job searches you really don't "need" to lock. * Run profiler and set up a tuning trace for JUST that DB. Watch it in real time and look at the "duration" column.. see if you can tease out a few lengthy queries there and fine tune them. Save the trace file and... * Run the index tuning wizard against trace trace and see what it tells you. Note - not "everything" it tells you may be great to implement - but you will get a good idea of what IT thinks will help performance. Usually there are a ton of statistics and some indexes. On a site with a ratio of 1000/1 selects to update/insert you can often accept all the suggestions without penalty. The issue is usually one of maintenance. To reiterate I would say the problem is definitely related to your DB if cfquery time outs are showing up in the log cooresponding to your lock ups. -Mark Mark Kruger - CFG CF Webtools www.cfwebtools.com www.coldfusionmuse.com O: 402.932.3318 E: mkru...@cfwebtools.com Skype: markakruger -----Original Message----- From: Paul Vernon [mailto:paul.ver...@web-architect.co.uk] Sent: Thursday, June 13, 2013 9:12 AM To: cf-talk Subject: RE: What would you call high traffic for CF8 standard? After looking through the logs (and there are a lot of logs) when the server crashes, the first thing to start appearing is cfquery timing out. The DB however is fully responsive to other clients when this happens, so the issue is CF data source related rather than the DB directly. This all happens when there is a surge in traffic and I've identified the source of this surge as twitter users. It seems there are a few Government run careers services on Twitter with significant numbers of followers that have been tweeting links to jobs and the job search function on the sites I'm having issues with. Within 30 minutes of a tweet by those accounts, I've got a surge in traffic that swells to the point that CF gives up. > It's a good idea but I don't think the change DB time out is saving you > much. These DB connections are very tiny in terms of resources - > probably > around 24k or so. No data is associated with a connection - and a site > as > busy as yours will drop fe of the connections from the pool anyway. If > it > does drop connections regularly it has to build new ones which takes > more > time than pulling one from the pool since it has to authenticate. I figure that by reducing the time to clean up old unused connections and increasing the clean up rate, it should at least give CF more capacity to open connections when there is a surge in traffic. I'm not expecting a huge difference, just a smoother performance curve with this sort of change. > Having > said all that I really don't think this change will be noticeable or > measurable :) This may be the case. At the moment stability is much better since I made the data source and JVM changes this morning although traffic is a bit quieter today (Monday is our busiest day). Having said that, looking at the stats right now, I've got over 4000 active sessions on the server at the moment so it's not that quiet! Paul ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:355925 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
