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@yuhu.biz> 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@yuhu.biz <mailto:mm@yuhu.biz>>
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@lists.launchpad.net>
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@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>