After I kill my clients, MySQL continues to process queries for hours

Question

Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second.

When the client has been running for hours, and I terminate the client, using mytop i see transactions are still ocurring, even new ones. It lasts for hours before it "catches up".

Now as a DBA, how can I view this "back log"? what variable is it listed as? What my.cnf parameters would control it? Why is this even happening? I thought the

Answer 1

Here is a VERY casual explanation...

MySQL works like this:

You send it a query that has a result set larger than its buffers, so it creates a temp table on disk to hold the data. That is an expensive process as it is usually reading from, swapping and writing to the same disk, unless purposely configured differently. Nonetheless, performance tanks. So now you're thinking "I have to do something, right?" So you kill your PHP process. The question now is "did your query process get killed, or is it running or is it now freeing space?" To find out what state your process is in, you'll need to run this as root or a user with the PROCESS privilege:

SELECT TIME,STATE,ID,HOST,DB,INFO 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE INFO IS NOT NULL 
ORDER BY TIME DESC LIMIT 3\G

This will give you the top three longest running processes. Just increase the limit to reveal more. If you really want to end the offending process, look at the ID field and KILL that #. E.g.

*************************** 1. row ***************************
 TIME: 0
STATE: executing
   ID: 4
 HOST: localhost
   DB: NULL
 INFO: SELECT TIME,STATE,ID,HOST,DB,INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO IS NOT NULL ORDER BY TIME DESC
1 row in set (0.03 sec)

mysql> KILL 4;

This won't simply kill all the things happening with that ID, but at least you'll be assured that it's definitely set into a STATE of "FREEING ITEMS" or equivalent.

Answer 2

Are you sure that terminating the client killed the PHP process that is running the INSERTs? if it is using a "new" connection for each call and relying on some sort of connection pool for efficiency, then is might just shuffle onto the next connection available in the pool when you kill the one currently being used.