Announcement

Announcement Module
Collapse
No announcement yet.

Random "Killed" queries

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Random "Killed" queries

    We have a set of mysql servers we use for hosting several client systems. These are fairly recent, large Dell boxes - 32gb ram each, 4 - 6 physical cores each, doubled to 8 - 12 via hyperthreading.

    We have around 20 boxes like this, each hosting the database instance for 60 - 100 client systems.

    We recently increased RAM in each box from 16 to the current 32, and at the same time, moved from MySQL 5.0 to Percona 5.1. This in an effort to allow each box to host more database instances.

    Generally, the move has been successful - we have not had any downtime or data corruption/loss issues surrounding the move.

    However, since the change, we have seen a strange occurrence, seemingly random, across servers/client instances.

    We use a home brew mysql query monitor to monitor/list the mysql processlist, and alert based on problem queries. In this monitor, we have started seeing a thread in "Killed" state, which we know we did not kill ourselves, nor did any of our systems kill. There are very limited cases in our applications which would actually kill a thread and none of those cases is to blame here based on our diagnosis.

    Furthermore, the Killed thread does not have an actual query associated with it. The only info that 'mysqladmin -v processlist' has for these threads is a pid, a user, an originating client host/port, the database instance, and that killed state.

    So in any case, this Killed thread, while showing in the processlist, usually for up to 2s at a time, seems to slow the entire server down, and other queries stack up until that Killed thread goes away, at which point the other queries go away and things go generally back to normal.

    This Killed thread shows up at least once per minute though, so there is definite slowdown in overall database and therefore application throughput.

    The only clue we have so far is that usually, after that Killed thread goes away, a series of other threads show the "Waiting on query cache mutex" state, which is new in the Percona 5.1 release, and has to do with the query cache.

    As a test, we have tried disabling the query cache altogether, with "SET GLOBAL query_cache_type=OFF", but the random Killed queries still appear, and the server still backs up with them. The only difference is that we then no longer see the threads in the 'waiting on query cache mutex' state.

    Can anyone provide any hints as to what's happening here and why?

    Thanks

  • #2
    No ideas? What would Percona team charge to diagnose this?

    Comment


    • #3
      Hi there Adhir,

      Well, I checked with our team and MySQL (Percona Server) do not kill queries on it's own. So it's sure that something else killing the queries.

      Can you see anything else in the process list while that query got killed? Another thread should execute the kill command meanwhile. That would be a good start to see what's wrong.

      About the charge of Percona team, please fill our contact us form on Percona.com and our sales will get back to you. I'm not sure without checking your the servers about the time it would take. But probably not more than 1-2 hours.

      But at first I say you should find in the process list what is killing that query, and try to find where it comes from. That what Perconians would do too.

      Istvan

      Comment


      • #4
        The user we connect to the server with doesn't have permission to kill queries. This is definitely not happening from our application - is it possible that the server is reporting the status back incorrectly?

        Comment


        • #5
          What software/connector are you using to connect to the server? Does it have a query timeout setting? It's possible that the application is killing its own queries after timing them out, and you don't know it because it's happening in the connector's code. I would use tcpdump to diagnose this if you want to prove it.

          Comment


          • #6
            We are connecting from PHP via it's mysqli_* client interface.

            Comment


            • #7
              I believe we have found the cause. the servers which were most often exhibiting this behavior tended to be heavily into swap. we tuned down some memory using variables in my.cnf, and turned linux's "swappiness" vm tune to 0 so it will only swap when necessary. We still see the "Killed" queries flash in and out on occasion, but that seems to be normal behavior after a client closes a connection, and the Killed does not persist past a very brief instant now. Previously, I believe these connections were just taking a long time to complete, due to swapping.

              Comment


              • #8
                What do you get if you do the following?


                mysql> show global status like '%kill%'; select sleep(600); show global status like '%kill%';

                Comment

                Working...
                X