Home > MySQL > Killing MySQL queries

Killing MySQL queries

Problem: Opps query on a table is taking too long. Due to
a) Lack of index
b) Forgot to put a limit.
The table in question has over 20Million rows.

Solution:
1. Connect to mysql as your given users eg guest
mysql> show process list;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 156 | guest | localhost:52753 |database1 | Query | 40 | Copying to tmp table | SELECT * FROM table1 INNER JOI |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+

2. Identify the query you would like to stop.
mysql>kill 156;

3. Check to see that the process has gone away.
mysql> show processlist;
+———–+———+——————+——–+———+——-+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+———–+———+——————+——–+———+——-+——-+——————+
| 120 | guest | localhost:57031 | database1 | Query | 0 | NULL | show processlist |
| 192 | guest | localhost:54919 | database1 | Sleep | 21491 | | NULL |
+———–+———+——————+——–+———+——-+——-+——————+

The documentation for the KILL command is here http://dev.mysql.com/doc/refman/5.0/en/kill.html.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: