The real time running MySQL query.

Dear all expertist,

as right now we found that we just execute the select * from <table> can kill the MySQL and we need some command to find out what is executing SQL process and try to kill some.

when I take a look on the "SHOW FULL PROCESSLIST\G" it seems not what we want.

What is the way you are using, please share.

Also I read this: http://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries:

"Edit: another alternative is the General Query Log, but having it written to a flat file would remove a lot of possibilities for flexibility of displaying, especially in real-time. If you just want a simple, easy-to-implement way to see what's going on though, enabling the GQL and then using running tail -f on the logfile would do the trick."

What is GQL, how to enable it ?  what use is it ?

I come from MS SQL background and we can simply use dbcc opentrac + inputbuff to find out what is the related real time query.

Please share how you dual with it and how to kill that process, like the troublesome "select * from xxx" command.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
johanntagleCommented:
The left-most "Id" column gives your thread Id.  See below:

mysql> show full processlist;
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
| Id | User | Host      | db                      | Command | Time | State | Info                  |
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
| 34 | root | localhost | sphinx_test_development | Sleep   | 2096 |       | NULL                  |
| 35 | root | localhost | sphinx_test_development | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

mysql> kill connection 34;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> show full processlist;
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
| Id | User | Host      | db                      | Command | Time | State | Info                  |
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
| 35 | root | localhost | sphinx_test_development | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+-------------------------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

Open in new window

0
 
johanntagleCommented:
Why do you think "SHOW FULL PROCESSLIST" isn't giving you what you want?  Remember you need to run it as mysql root or someone with PROCESS or SUPER privilege to be able see all currently running queries.  See http://dev.mysql.com/doc/refman/5.1/en/kill.html
0
 
Tomas Helgi JohannssonCommented:
Hi!

Take a look at this free tool
http://sourceforge.net/projects/mysqlmt/
also there is a good tool called MonYog
http://www.webyog.com/en/monyog_feature_list.php

GQL stands for General Query Log
http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Take also look at the Slow query log which I think is where you should look at first for your problem.
http://dev.mysql.com/doc/refman/5.1/en/server-logs.html
Enabling the slow query log with suitable parameters will certainly give you those killing queries. :)
Look at the queries and determine if you have suitable indexes on relying tables.

Regards,
    Tomas Helgi
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
johanntagleCommented:
The slow query log will show long queries that have already completed execution.  If the objective is to kill them while still executing, you cannot use that.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
johanntagle,

Yeah, this is what I am saying and right now I read both:

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html# 
and
http://dev.mysql.com/doc/refman/5.1/en/kill.html

when I run "SHOW FULL PROCESSLIST\G", it seems don't return the thread Id, where can I find it so that I can use the kill command to kill it?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
execellent,

I know why I get an error message before as I send this command to other MySQL instance and some do not have that connection id/thread id.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
it seems that if I want to know what is going on on the MySQL itself on what make it slow, I can only do

1) SHOW FULL PROCESSLIST \G
2) Check slow_log table,

Am I right? any thing I can do to see why last night at 4:00 AM when we are sleeping, the MySQL very slow ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.