From: James Day
Date: October 30 2008 9:51pm
Subject: Re: Unthawable
List-Archive: http://lists.mysql.com/falcon/119
Message-Id: <490A2C4E.7090403@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin,

>  >> Do we abort transactions that have been idle for a certain period of
>  >> time to avoid a malicious user keeping transactions open in order to
>  >> fill up our disks?
> ...
> Pervasive.SQL and some other database systems have the ability to allow 
> a DBA to selectively kill a long running transaction or connection from 
> a separate connection.  I do not know if this can be done in MySQL.  But 
> it would be a server feature, not Falcon.

Falcon has an obligation to respond within a few seconds when it's told that 
the DBA has used the KILL command on a thread. Polling the thd->killed 
status every 500 row/index accesses is the sort of thing that other engines 
have done.

If there are parts of Falcon that don't perform such checks and could both 
take more than a couple of seconds and be viably abortable, those areas need 
to be improved. If there are some known common and easy to address areas, 
doing them first and more complex ones later would be fine.

The sort of thing we need to avoid is a big operation (query, ALTER, 
whatever) that's hitting the disk hard and denying service to other jobs, 
but which doesn't respond to being told to die. If they don't die the DBA 
will end up doing kill -9 on the database server as the first step in 
restoring service.

A couple of example non-Falcon bugs:

http://bugs.mysql.com/bug.php?id=25421
http://bugs.mysql.com/bug.php?id=33146

We used to have quite regular issues with InnoDB not responding to kills but 
those have largely ceased to be an issue after improvements to the affected 
areas.

James Day, MySQL Senior Support Engineer, Sun Microsystems