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: Q <email@hidden>
- Date: Thu, 9 Apr 2009 09:15:29 +1000
On 09/04/2009, at 8:40 AM, 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). 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). It appears that
the database connection becomes the bottle neck and is responsible
for slowing things down. 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?
Very first thing I would do is look at the generated SQL and ensure
that when a query executes that it is not wasting time due to a lack
of indexes, and that it's not locking rows or tables unnecessarily.
Next I would profile my app to see exactly where it's spending the
time and ensure that the problem(s) cannot be solved with effective
use of caching.
I would also consider moving the page access log writes out of the RR
loop and turn them into event notifications that get received by a
dedicated thread so that these writes can be done asynchronously.
If your database connection is currently the choke point, more
connections is unlikely to help.
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?
It seems a bit overkill to start clustering Tomcat instances for
less than 100 users - any ideas on the best way to improve
performance?
The performance appears to have gotten worse with WO5.4. Is that
possible?
Thanks
Peter
_______________________________________________
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
--
Seeya...Q
Quinton Dolan - email@hidden
Gold Coast, QLD, Australia (GMT+10)
Ph: +61 419 729 806
_______________________________________________
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