October 7th, 2013, 04:26 AM
Sleep processes w/ many rows examined
I write code that pings a DB server that another piece of code also pings, although it pings a separate DB on that server. The server's CPU is currently taking a hit and I'm trying to figure out why.
Running show full processlist is bringing back a bunch of lines from the other DB, though I'm unclear of exactly what's happening. Many lines with "Query" seem to be 'stuck', as in they always show up, and yet their times are always 1 or 2 and running the queries on their own seem to take a reasonably short amount of time. After a "Query" command, there appear to be several "Sleep" commands, which continue to go on longer and longer. I'd imagine the SQL connection isn't being closed properly on these, which might not be so big a deal, except for the noted CPU problem. Also, oddly, the "rows examined" on some of these Sleep commands are huge...much larger than the amount of rows analyzed by the Query commands before them.
Any idea what might be going on? Could this be the CPU culprit or am I barking up the wrong tree?
I've attached a screenshot of show full processlist.
Thanks for any quick help you can provide.
October 7th, 2013, 04:49 AM
How does this pinging work?
And do you have a querylog running on the server?
October 7th, 2013, 04:56 AM
By pinging I simply mean its connecting to the db through a standard mysql connection call, to a DB server on the same stack.
I do not believe we have a full log, no. Would grow too fast.
October 7th, 2013, 11:34 AM
What do you consider to be a standard connection call? There are may ways to connect and some have problems, like PHP's pconnect().
You wouldn't run it constantly, but it seems logical to run it while you are examining a problem. How else are you going to find out what the datbase is really doing when the problem occurs?