← Back to team overview

maria-developers team mailing list archive

Re: RFC: Pausing a query thread


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 :(


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.


    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

Follow ups