Contents
Why are so many query end States in MySQL?
When I checked the process list I found loads of update queries (simple ones, incrementing a “hitcounter”) that were in query end state. We couldn’t kill them (well, we could, but they remained in the killed state indefinitely) and our site ground to a halt.
Why are all MySQL connections stuck in query end?
We’ve been having a very similar problem, where the mysql processlist showed that almost all of our connections were stuck in the “query end” state. Our problem was also related to replication and writing the binlog.
Is it possible to kill a query in MySQL?
However, when it comes to queries being in status where they are not killable, that is documented in our manual. Simply, this is not like killing a process in Unix. Query can be killed only when it is in such a state that all memory is released, files closed, etc, etc …… Hence, query can be quite long in a state where it can not be killed.
How to reduce the size of MySQL log files?
Deleted that, started MySQL, ran a PURGE LOGS BEFORE ‘2012-10-01 00:00:00’ query to clear out a load of space and reduced the /var/lib/mysql directory size from 346GB to 169GB. Changed back to master and everything is running great again. From this I’ve learnt that our log files get VERY large, VERY quickly.
How to kill a stuck thread in MySQL?
It will kill the stuck thread and there will be no side effects on the database as it will be a normal shutdown. Other suggestion above asked to kill the process, which will lead to database recovery which could have its own issues. So I would recommend going with the daemon graceful stop.
How to kill a thread in MySQL with sytnax?
The command sytnax is KILL [QUERY | CONNECTION] , where thread_id is the value from the first column of the process list output. The optional argument determines whether only running query or should the entire session be terminated.
Why is MySQL process that was killed still on my processlist?
If it is an INNODB database, you can for instance look at this question: https://dba.stackexchange.com/questions/5654/internal-reason-for-killing-process-taking-up-long-time-in-mysql In my case, my /var partition was full, where the MySQL binlogs are written. Once I freed some disk space, the killed connections immediately went away.