Subject Re: [Firebird-Architect] Re: Can we, can we, can we????...
Author Ann W. Harrison
Alexander Klenin wrote:
> 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!

You mean you're counting system time in addition to student think time?
If I were a student, I'd be really annoyed. And if I were your
instructor, I'd suggest that you rethink the application before you
submitted it for a grade.

> 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.

Which should not be a problem if your students were measured on their
think-time rather than connect time.

> This system currently uses quota-based proprietary database which
> allows it to dynamically change quotas for "teacher" account depending
> on the system load.

Here's what I would expect to have happen in that case. I'm a teacher,
I've got a big query, and I need the results. I submit my query, run
out of quota and lose all benefit of the work done so far. Irked, and
on a deadline, I resubmit the query and continue in that loop, wasting
huge numbers of cycles, until the query finally succeeds or somebody
kills me.

> 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 ;)

If that works for you, fine. Be careful of funny dates - I'll bet your
application will have a grand time with 2005.0.15 and such MySQL
>>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.

Somehow I think the denial of service (DoS) attack recognition hasn't
been moved into most databases yet. And, to my way of thinking, quotas
are still an application specific problem that doesn't generalize enough
to be moved into the database. Your examples haven't convinced me for
the reasons above.
>>>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?

Well, we pretty much need to monitor our servers 24/7 because from time
to time the phone company kills our line or the power company decides to
do some work and we need to bring in battery backup in case the
batteries on the UPS don't last as long as the interruption, or our
up-stream ISP has a problem and we need to respond instantly for our
clients. If you're offering major service, you've got to have somebody
there to handle problems.
>>>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.

I've never thought of DoS as a data security problem.