Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
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.
