maria-developers team mailing list archive

[Marian Marinov <mm@xxxxxxxx>]

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.

Marian

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