← Back to team overview

maria-developers team mailing list archive

Re: RFC: Pausing a query thread


On 06/24/2013 11:21 PM, Peter Laursen wrote:

Why not take a more daring approach - implement 'priorities' in processlist?
"SET PRIORITY = high|medium|low [or 1..9]  FOR processlist.ID=xx;"

I realize that this is not a trivial task, and obviously it requires so much change in server architecture that it is
not a solution for Marian here-and-now.

Priorities actually require a new logic for handling the queries. This is something that I don't think is feasible in SQL servers as a whole. The problem is that the queries are generally short lived, so implementing a priority based scheduling will only slow down the server.

It will be nice for long lived queries, but then the next problem is, what would be the priority criteria and what would be the actions? We will be going again back to the problem of pausing a query or stacking many queries because of one that took a lot of resources.

@Marian BTW (as you probably know the answer): what do hosting companies offering 2.95 $-per-month 'web hotel' solutions
do where hundreds or thousands of users/customers share a MySQL server and one user repeatedly executes idiotic queres
from home-grown applications resulting in starving of the server and thus effectively causing denial-of-service for
other users/customers? I was always surprised that they could manage it with MySQL, actually!

Most hosting companies do not monitor mysql processes and in fact don't care much until one user abuses the system. After a few strikes the user is usually persuaded either to upgrade or go to another host.


-- Peter

On Mon, Jun 24, 2013 at 9:50 PM, Marian Marinov <mm@xxxxxxxx <mailto:mm@xxxxxxxx>> wrote:

    Hi Roberto,
    MDEV-4623 is does exactly what I want in a different manner.

    I have a similar thing as MDEV-4427 written as MySQL plugin. Unfortunately this have one very big problem. It
    sometimes breaks tables. I have seen even InnoDB tables broken by this type of intervention :( I can't reproduce it
    very easy for InnoDB, but for MyISAM it should be pretty easy.
    Also killing query, even one that takes a lot of resources may result in a loss of data, which in turn says, that
    the service is bad.

    My opinion is, we should either accept a query and try to execute it while preventing resource starvation or we
    should deny it. It is very bad to accept it and then kill it in the middle of its work.
    This is a DataBase, it should either accept data or not.


    On 06/24/2013 08:15 PM, Roberto Spadim wrote:

        hi marian, could you see MDEV-4623 and MDEV-4427, and tell me if that could help you?

        MDEV-4623 <https://mariadb.atlassian.__net/browse/MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623>> -
        https://mariadb.atlassian.net/__browse/MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623> -
        lock/unlock connection
        MDEV-4427 <https://mariadb.atlassian.__net/browse/MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427>> -
        https://mariadb.atlassian.net/__browse/MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427> - twitter
        like patch (this one is to kill a query, but after MDEV-4623 we could use lock connection)

    Mailing list: https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers>
    Post to     : maria-developers@lists.__launchpad.net <mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>
    Unsubscribe : https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers>
    More help   : https://help.launchpad.net/__ListHelp <https://help.launchpad.net/ListHelp>

Follow ups