maria-developers team mailing list archive

[Marian Marinov <mm@xxxxxxxx>]

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 <serg@xxxxxxxxxxx <mailto:serg@xxxxxxxxxxx>>
>
>     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     : maria-developers@xxxxxxxxxxxxxxxxxxx <mailto:maria-developers@xxxxxxxxxxxxxxxxxxx>
>     Unsubscribe : https://launchpad.net/~maria-developers
>     More help   : https://help.launchpad.net/ListHelp
>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial