Re: Slow SQL slowing application - how to speed things up?
Re: Slow SQL slowing application - how to speed things up?
- Subject: Re: Slow SQL slowing application - how to speed things up?
- From: Chuck Hill <email@hidden>
- Date: Wed, 8 Apr 2009 17:04:54 -0700
Hi Peter,
On Apr 8, 2009, at 3:40 PM, Peter Newnam wrote:
Hi All
We have some slow SQL (40 instances of code in an 8 hour period
taking between 5 and 10 seconds to run).
While slow 40 times in 8 hours should not be the end of the world.
I'd be more concerned about the other 40,000 instances that took 2-3
seconds.
Obviously the SQL needs to be tuned, indexes need to be added etc.
The result is that pages start timing out when user numbers increase
( > 30 users on a single Tomcat machine running Project Wonder with
WO5.4).
That sounds like a very small number of users. What are the average
response times?
It appears that the database connection becomes the bottle neck and
is responsible for slowing things down.
I suspect that you mean the single threaded lock in EOF and not the
database connection itself.
At the moment each page access writes to the database as a page
access log - so the connection is heavy used for updates/inserts.
So ..... what do you thing the best practice would be to improve
performance?
First, I'd avoid an insert/update per RR loop just for an access log.
Write it out to disk instead. Or at least queue up a message for
another thread to use JDBC to write this out.
My initial thoughts would be to tune the SQL, check/correct abuse of
loading large datasets and add additional database connections as it
appears to only use one regardless of the load (however I noticed
another connection open on the server that seems to be WO internal
workings - it gets information on data types etc.). However based
on this thread - http://lists.apple.com/archives/webobjects-dev/2005/Jul/msg00501.html
it appears that multiple database connections don't work as
expected. Would it help though?
No. Depending on what is wrong and how much data your users share
more EOF stacks might help. They will consume more memory and if
there is a lot of shared data this can be problematic.
It seems a bit overkill to start clustering Tomcat instances for
less than 100 users - any ideas on the best way to improve
performance?
First, figure out what the problem is. Excessive DB traffic (usage
log) and slow queries seem likely candidates. Memory starvation can
also cause this indirectly. How much heap space do these instances
have?
ERXAdaptorChannelDelegate can be setup to log all queries that take
longer than X milliseconds to execute. That is useful to identify
what needs to be tuned at the database.
Then I'd override dispatchRequest in Application to log out each URI
and how long it too to return a response. Do this in a form that is
easy to grep out and import as CSV into Excel. Then you can sort and
graph to try and make some sense of it.
After that, I'd look at profiling the code.
The performance appears to have gotten worse with WO5.4. Is that
possible?
I have not noticed that, but is possible that you are doing something
that is affected by changes in 5.4. I can't think of anything
specific.
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden