Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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.
0
marrowyung
Asked:
marrowyung
  • 3
  • 3
3 Solutions
 
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
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
marrowyungAuthor 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
 
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
 
marrowyungAuthor 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
 
marrowyungAuthor 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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now