Take the 2-minute tour ×
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's 100% free, no registration required.

I asked this question on stackoverflow and was directed to ask this question here.

I have a couple of scripts in PHP that run MySQL queries. On occasion they cause the server to seize up or become clogged with MySQL processes that take a while, for example badly formed LEFT JOINs etc.

When I run the query to find information about all current MySQL queries that have been run is it be possible to resolve a query ID to the file that ran it?

There is a query that has been sleeping for 3973 there is no info about this query and so I am trying to find as much information as possible?

share|improve this question
2  
If you know the query, you can use grep on the php server's command line to help find the file. You can also modify your connection files (in php) to log the thread_id. Unfortunately, MySQL processes won't know what file called them. –  Derek Downey Oct 2 '12 at 15:40
    
Thanks for the response that what I was fearing. I will look into implementing your advice –  user1714133 Oct 2 '12 at 15:45

1 Answer 1

up vote 1 down vote accepted

There are three(3) ways to find out info on queries and its process ID

Way #1 : Activate the General Log as a Text File

If you add the following lines to my.cnf and restart mysql

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You can parse /var/log/mysql_general.log text file. The header of each query should have the process ID (Thread ID) that the query ran under

Way #2 : Activate the General Log as a MyISAM table

There is a table called mysql.general_log. It is a CSV file by default. Run the following commands:

ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

then add the following to my.cnf

[mysqld]
general-log
log-output=FILE

Once you have the general log entries stored as a table rows, you can run SELECTS against the MyISAM at will, with queries like:

SELECT * FROM mysql.general_log
WHERE event_time >= (DATE(NOW())+INTERVAL 0 SECOND)
AND event_time <= NOW();
AND argument like '...';

Way #3 : Check the Process List

If you want to catch the queries in the act of running long, do the following (if you have MySQL 5.1) :

SELECT id,info FROM information_schema.processlist
WHERE command = 'Query' AND user <> 'system user';

This shows you every query currently running. You can parse the info column for whatever query you are tracking down

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.