Forum OpenACS Q&A: Re: Monitoring and Controlling Queries

Posted by Andrew Piskorski on
But, um, backing to Steve's original scenario:

For your case of "firing off the same slow query again and again bringing the server to its knees", if there's a legitimate reason for that query to be slow (it really is doing hard important work, it's not just written poorly), then what you want to do is prevent the firing off of the queries 2 to N, not abort each N-1 query query as query N is submitted.

Probably that means you need some UI to let the user go find the results of the query he submitted earlier, as he will have clicked away to another page in the meantime. In OpenACS, a simple smallish scale system for that shouldn't be too hard to do. You already have every logged in user uniquely identified, so only allow each user to run query Q once ever N minutes, and cache the results of the query in a NSV for N or N*2 minutes as well, or something like that.

Of course, that's assuming that the expensive query is giving different results for each user. If all your users are running the same query with the same results, then none of that fancy stuff is necessary, all you need is a little cacheing in AOLserver and you're done. You're users will never see any difference except in speed. And you originally said this is for an E-commerce site... Most of the time query tuning and maybe some cacheing is probably all they need.

Posted by Claudio Pasolini on
A (very) partial answer to the problem is the ad_return_if_another_copy_is_running API, wich simply prevents the starting of the tcl script if another copy is running.