maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

I think it is a known issue wiht 'hsared hosting' . Any user can fire a
query killing the server (for example causing excessive memory load ude to
a cartesian join on huge tables, or CPU due a a routine entering an
infinite loop. It can be both due to user's lack of skills og even be a
malicious attack/teeny experiment.

I have always wondered how hosting companies handled that. MySQL does not
provide any mechanism for 'isolating server resources per user or per
database or whatever'. Still MySQL has been the most popular choice of
database for such hosting companies for a decade. I think that in extreme
cases most 'shared hosting will kill such query and sometimes warn users if
it repeats frequently.  But cases can alos be valid. Some queries require
lot of memory or CPU or whatever.

I think Marian's request for an admin option to pause a specific query is a
reasonable workaround.  But only that.  A priority setting by the server
for queries|users|databases and automatic allocation of resources
accordingly would be a better option.

>From one that used 'shared hosting' quite a lot many years ago - but who
did not for 6-7 years at least.

Peter
Webyog


On Mon, Jun 24, 2013 at 5:31 PM, Marian Marinov <mm@xxxxxxxx> wrote:

> I have seen the problems with both MyISAM, InnoDB and even with memory
> storage.
>
> I want to stop the executing thread instead of implementing the change in
> the storage engines.
>
> Unfortunately hosting companies can not change the software of their
> clients and also don't have the manpower to do that.
>
> So a solution for them would only be if it is possible to pause the
> executing thread until the server calms down.
> It is a unique situation where general replies as "Fix your database
> design", "Optimize your query" or "Upgrade to the next version" are not
> possible. It is impossible to fix the enormous amount of software that they
> are hosting.
>
> Marian
>
> On 06/24/2013 03:48 PM, Roberto Spadim wrote:
>
>> hum, it's a innodb table? a myisam? what are you talking about high
>> resource? locked tables? high write/read ?
>>
>>
>> 2013/6/24 Marian Marinov <mm@xxxxxxxx <mailto:mm@xxxxxxxx>>
>>
>>     Hi guys,
>>
>>     I work with hosting companies and all of my clients have the same
>> irritating problem, one query taking a lot of time
>>     to finish and consuming a lot of resources.
>>
>>     So I have solved the same issue but with processes on the machine by
>> simply pausing(kill -STOP) these processes when
>>     the load is high and continuing(kill -CONT) them when the server load
>> goes down.
>>
>>     I want to implement the same logic within MariaDB, so I can STOP/CONT
>> the thread that is executing this query.
>>
>>     My idea is to implement this in the 'KILL query_id' function, so it
>> will accept one more argument, which will be
>>     either STOP or CONT. If the parameter is not present, the default
>> behavior should remain.
>>
>>     What do you think about my approach?
>>
>>     Marian
>>
>>     ______________________________**___________________
>>     Mailing list: https://launchpad.net/~maria-_**_developers<https://launchpad.net/~maria-__developers><
>> https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
>> >
>>     Post to     : maria-developers@lists.__launc**hpad.net<http://launchpad.net><mailto:
>> maria-developers@**lists.launchpad.net<maria-developers@xxxxxxxxxxxxxxxxxxx>
>> >
>>     Unsubscribe : https://launchpad.net/~maria-_**_developers<https://launchpad.net/~maria-__developers><
>> https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
>> >
>>     More help   : https://help.launchpad.net/__**ListHelp<https://help.launchpad.net/__ListHelp><
>> https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
>> >
>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial
>>
>
>
> ______________________________**_________________
> Mailing list: https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
> Post to     : maria-developers@lists.**launchpad.net<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>
>