Query End concept

Question

My table MYTABLE contains 14 million record data

CREATE MYTABLE `MYTABLE` (
`COLUM1` int(10) unsigned NOT NULL DEFAULT '0',
`COLUM2` int(10) unsigned NOT NULL DEFAULT '0',
`COLUM4` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`COLUM3` char(1) NOT NULL,
 UNIQUE KEY `COLUM2` (`COLUM2`,`COLUM1`),
 KEY `COLUM1` (`COLUM1`)
) ENGINE=InnoDB

show processlist at a given time

 | 318 1204333 | user | xx.xxx.xx.xxx:24396 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11285294','9765238',now())              | 

 | 318 1204334 | user | xx.xxx.xx.xxx:38671  | newjs | Query       |        0 | Updating                                                              | UPCOLUM4 newjs.MYTABLE SET COLUM3='Y' WHERE COLUM1=11200140 AND COLUM2=10927439                 | 

 | 318 1204335 | user | xx.xxx.xx.xxx:35259 | newjs | Query       |        0 | query end                                                             | UPCOLUM4 newjs.MYTABLE SET COLUM3='Y' WHERE COLUM1=8667546 AND COLUM2=9731954                   | 

 | 318 1204337 | user | xx.xxx.xx.xxx:24405 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 

 | 318 1204339 | user | xx.xxx.xx.xxx:24412 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11267957','11250673',now())             | 

 | 318 1204342 | user | xx.xxx.xx.xxx:35287 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11229209','9873622',now())              | 

 | 318 1204343 | user | xx.xxx.xx.xxx:38681  | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('10157460','10967184',now())             | 

 | 318 1204347 | user | xx.xxx.xx.xxx:24424 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11268751','9423745',now())              | 

 | 318 1204348 | user | xx.xxx.xx.xxx:35316 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11268611','9263111',now())              | 

 | 318 1204349 | user | xx.xxx.xx.xxx:35319 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 

 | 318 1204350 | user | xx.xxx.xx.xxx:35345 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('9049736','9585434',now())               | 

 | 318 1204352 | user | xx.xxx.xx.xxx:35375 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 

 | 318 1204353 | user | xx.xxx.xx.xxx:35376 | newjs | Query       |        0 | uptime                                                                | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('10578170','9843201',now())              | 

 | 318 1204354 | user | xx.xxx.xx.xxx:35379 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('8445209','11055214',now())              | 

 | 318 1204355 | user | xx.xxx.xx.xxx:24485 | newjs | Query       |        0 | uptime                                                                | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('9699909','11236269',now())              | 

 | 318 1204357 | user | xx.xxx.xx.xxx:35396 | newjs | Sleep       |        0 |                                                                       | NULL                                                                                                 | 

 | 318 1204359 | user | xx.xxx.xx.xxx:24531 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('8925641','7987794',now())               | 

 | 318 1204360 | user | xx.xxx.xx.xxx:24550 | newjs | Query       |        0 | query end                                                             | REPLACE INTO MYTABLE  (COLUM1,COLUM2,COLUM4) VALUES ('11242379','9740270',now())              | 

 | 318 1204362 | user | xx.xxx.xx.xxx:35464 | newjs | Sleep       |        0 |

What is this query end ?

Perhaps surprisingly, it is the state of a query that is being ended (flushed, closed, etc.). Is there a problem? How long do the same processes remain in that state? — underscore_d, Jul 22 '15 at 15:18
You might take a look at http://stackoverflow.com/questions/13234290/lots-of-query-end-states-in-mysql-all-connections-used-in-a-matter-of-minutes — C0d1ngJammer, Jul 28 '15 at 06:59
You having any perfomance issues with the "Query end"-State? — C0d1ngJammer, Jul 28 '15 at 07:17

C0d1ngJammer · Answer 1 · 2015-07-28T07:51:01.733

"The "END" connection state is used for writing binary log records, query cache operations, and other things.." MySQL

"Probably the most common reason for a thread to stay with either killed or query end for a longer period of time is waiting for a transaction rollback on InnoDB tables. This sometimes can take a lot of time to complete, especially when hundreds of thousands or millions of changes have to be removed"

"Actually, there can be two different cases. If KILL was issued, there would be killed in the process list. KILL QUERY doesn’t kill a connection, but rather it only stops a running query within a connection, so in that case query end text may appear instead." psce

Many people reporting that they have performance issues with "The End"-State. Fixes:

Update to a newer Version of MySQL
reduce cache

Query End concept

1 Answers1