On 6/18/05, Ann W. Harrison <aharrison@...> wrote:

> Granted - but I'd need to hear from somebody with a real application
> that could be improved by quotas. I can certainly imagine situations
> where quotas make managing an application more difficult, so I need a
> very concrete reason to go there - not just because we can.

In addition to examples already provided, I can give my own. One of my
projects is student testing system for the local university. It asks
students questions and monitors time taken to answer them -- so any
loss of responsiveness can have a direct effect on grades!
Naturally, as many students as possible should be tested
simultaneously, to preserve the secrecy of the questions. This numbers
are measured in hundreds.
Web-oriented nature of the system allows distributed usage, with some
branches of the university located hundreds of kilometers away, and in
another time zone. (e.g. Vladivostok and Petropavlovsk-Kamchatsky)
At the same time, the system supports very extensive set of queries
for the teacher, some of them are detailed statistical reports which
could take anywhere form few seconds to few hours to complete,
depending on data range.
This system currently uses quota-based proprietary database which
allows it to dynamically change quotas for "teacher" account depending
on the system load.
We would like very much to convert to some open-source DBMS, and the
primary candidate is now MySQL 5.0, not in the least because it now
finally has most of attributes required of "real" DBMS: transactions,
views, stored procedures, decent optimizer and quotas ;)

> > 2) ...
> > More dangerous are run-away queries initiated by users, ...
> > The "cancel" button is good, but not a perfect solution
>
> > a) it does not work in web interfaces
>
> A Web application can (should?) do its own monitoring of user queries
> and kill those it considers too long.

Yes, it could. It could also prevent DoS attacks by monitoring the
connection pool on the web-server, preserve referential intergity with
application-level checks, simulate transactions with try/except
blocks, use client functions instead of stored procedures, etc. I
remember the time when Michael "Monty" Widenius (the primary developer
of MySQL) used all these arguments quite often. But the real life
shown that all these functions do belong to a DBMS, not to the client.

> > 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
> And probably we need a way for an administrator to identify and stop
> runaway queries.

Yes, but should he sit at the terminal and monitor the database 24 hours/day?

> > c) there may be security implications in trusting users to cancel
> > their own requests
> Such as?

Such as the current situation, where any malicious or buggy client can
easily bring the whole database to a halt, and the sad thing -- there
is nothing that DBA can do to prevent it short of sitting and watching
the server load continuously, then fractically scrambling to find out
*which* of the queries caused that 100% CPU load.
I know, had to do this a few times ;-)

--
Alexander Klenin
Insight Experts, Ltd.