I have a processlist to show you, but it will be only one of many different cases. And I do not want to discuss
everything case by case. This will not solve the problem.
Consuming a lot of resources can mean one of the following:
- locked query is forcing other queries to stack
- one query is using a lot of I/O and in doing so is generating situations where other queries may stay in locked state
for longer times and adding the first issue to the problems
- single query is taking a lot of CPU/Memory resources to produce its output, in doing so it starves all other services
on the machine
I understand Sergei's point, that is why I started this discussion.
Stopping a single thread may lead to more problems then it will solve, but I'm
sure we can come up with a solution.
I already have a plugin, that looks for queries that take long time to finish and while they are executing it prevents
new queries for the same table to be executed. But this is not a good solution :(
Marian
On 06/24/2013 06:37 PM, Roberto Spadim wrote:
could you explain better what is very consuming a lot of resources? maybe a
processlist output
consuming != locked
like sergei wrote, a lock may cause problems when you 'pause' a query
2013/6/24 Sergei Golubchik <[email protected] <mailto:[email protected]>>
Hi, Marian!
On Jun 24, Marian Marinov wrote:
> 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?
That's surely possible. And it can be done with a relatively small patch.
But note that if you "pause" a query that's holding locks, be it
pthread mutexes, row locks, table locks, metadata locks, whatever, all
other connections that might need them will "pause" too. They'll wait
for the lock, and may eventually time out.
Regards,
Sergei
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
<mailto:[email protected]>
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
--
Roberto Spadim
SPAEmpresarial
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
