On 6/17/05, Jim Starkey <jas@...> wrote:

> Alexander Klenin wrote:
> >TABLE RDB$QUOTAS
> >(
> > RDB$USER INTEGER REFERENCES RDB$USERS(..),
> > RDB$MAX_ELAPSED_TIME_MSEC INTEGER,
> > RDB$MAX_CPU_TIME_MSEC INTEGER,
> > ...
> >)
> >
> Are these quotas per request, per session (attachment), or per day?

Per request, of course.

> If per request, aren't you just encouraging uses to break piggy requests
> into two or more small but less efficient requests?

Sure, but any of the previosly discussed solutions do the same.
Also, notice the ellipsis at the end of the list ;-) In real
quota-based systems, there are limits on requests per second and
connections per second to prevent DoS,
bytes sent per second to prevent databombing and so on, and so forth.
Important part here is per-user, not per-database granularity and
integration with general database security subsystems. It could even
be implemented as a part of your SecurityPlugin architecture ;-)

> And how could per day quotes address the original requirement, which was
> to catch runaway queries? Is a developer who accidently started a
> runaway query forced to reattach or go home and wait for the next day?

There are several points here:

1) Quotas are NOT a replacement for on-demand request cancelling, they
are different, although related, features appropriate for different
use cases.

2) Not all run-away queries are created equal ;-) Some of them could
be initiated by the developer during actual coding, but these are the
least of the problems, because they are seldom run against production
databases.
More dangerous are run-away queries initiated by users, who either
give bad request paramaters, request too complex reports or stumble
upon inefficiently coded request just when the developer/dba is not
around. The "cancel" button is good, but not a perfect solution
because
a) it does not work in web interfaces
b) there may be situation when user is content to wait a few minutes,
but the query is so heavy that it blocks all other database activity
c) there may be security implications in trusting users to cancel
their own requests

3) Lastly, even in the case of developer/dba, who is for some reason
forced to run untested requests againts production database, the
following scenario could be beneficial:
by default, dba uses the account with normal quota restictions, so he
can not accidettially overload the engine. If dba is sure he needs
additinal resources, he can lift some quotas or use another account.
With proper integration, this could be as easy as
START TRANSACTION;
UPDATE RDB$QUOTAS SET RDB$MAX_REQUEST_ELAPSED_TIME = NULL
WHERE RDB$USER='SYSDBA';
EXECUTE PROCEDURE ReallySlowUpdate;
UPDATE RDB$QUOTAS SET RDB$MAX_REQUEST_ELAPSED_TIME = 1000
WHERE RDB$USER='SYSDBA';
COMMIT;

> What do you mean by elapsed time? Is it total clock time since a request began,

Yes, elapsed time is total wall clock time since the beginning of request.

> or does it exclude think time when the request is inactive pending human interaction?

Wow, how is this possible?

> How to do propose to measure CPU time in a multi-threaded environment?
> As has been mentioned, pthreads doesn't expose per-thread CPU times.
> Does it really make sense to define a quota that we can't enforce?

Of course, all this concept is subject to implementation restrictions.
And I do not ask to implement any of this right now, on-demand request
cancelling will already be a great step ahead.
I just wanted to note that if and when any kind of quota will be
implemented, it should, IMHO, have the interface/specification
similiar to what I described above.

--
Alexander Klenin
Insight Experts Ltd.