How can one see which MySQL command(s) is causing the server load avg to go crazy

[WorldNews]

Hello,

I have a very important/critical question.

What is the best method for seeing via remote control, that is like Putty, what MySQL command is misfiring and causing server load average to go crazy?

I mean if there is a MySQL command that is written bad which is causing the load average of server spike up to crazy levels, how can one find out via the processors running which one can see via Putty as the Root as to which MySQL command(s) are causing the problem?

Best Regards,

[chris_fuel]

You can use mysqladmin like so:

mysqladmin -u root -h localhost -p processlist

This will give you a list of processes, where you can see how long they've been running and what the status is. Here's an example output:

+------+------+-----------+----+---------+------+-------+------------------+
| Id   | User | Host      | db | Command | Time | State | Info             |
+------+------+-----------+----+---------+------+-------+------------------+
| 3917 | root | localhost |    | Query   | 0    |       | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+

Then you can kill the process that's causing total disaster:

mysqladmin -u root -h localhost -p kill 3917

[WorldNews]

Greetings,

I entered the command as you suggested and see the table, but how
do I know which process is causing the problem?
I mean what value(s) should I be looking for as being cause of the problem?

Also, this table is wrapping around, making it hard to read. Do you have
a suggestion to make the table to fit in the window without wrapping?

Regards,

[chris_fuel]

Check the time and State columns. If the time is ridiculously long and the State is "Sending Data", you've got a really slow query on your hands. See alternate values for the State column:

http://dev.mysql.com/doc/refman/5.0/...ad-states.html

Note that if you login to mysql you can also run the SHOW PROCESSLIST (not sure of permissions around this, but I'd say root at least), which you can add \G at the end of it to drop column => value entries to a single line. From there, you simply apply the KILL command to the thread in question. Also note that Time is measured in seconds.

[WorldNews]

Greetings Chris,

1st, Thanx for info you have sent to date.

So let me ask you some follow up questions:
1- What value for the Time column would indicate that that thread is running bad or has a bug in it?

2- Similarly what are some warning "State" column values to look for?

3- How can I best map a badly/buggy running thread to the MySQL code (lines of codes) in the program that is causing this problem?

4- And should I notice a badly/buggy running thread, how do I best Kill just that thread?

Best Regards,

[WorldNews]

Hello,

I was wondering if you got my follow up questions to your reply.

I would appreciate your reply ASAP since I need to address critical
problems being caused by some buggy mySQL code.

Regards,

[Aleksejs]

Try enabling slow-query-log

[longneck]

1- What value for the Time column would indicate that that thread is running bad or has a bug in it?

anything more than a few seconds.

2- Similarly what are some warning "State" column values to look for?

generally, the ones you will see are either "sending data" or "copying...", and either of those can mean that your query is either returning or processing large amounts of data, possibly inefficiently.

3- How can I best map a badly/buggy running thread to the MySQL code (lines of codes) in the program that is causing this problem?

the simplest way is to add comments to all of your SQL queries so you know where the queries came from. that's not so simple if you're not using a DB abstraction layer. alternatively, if you KILL a query then an error should be logged in your webserver or application log and you can track down the line of code that way.

4- And should I notice a badly/buggy running thread, how do I best Kill just that thread?

with the KILL command.

[WorldNews]

Thanks for this detailed reply.

Some more related questions:

1- What is the best Kill command for killing a MySQL thread?
just "Kill thread-id"?
Or you suggest something else.

2- Is it best to issue such command while loged in as MySQL root or server root?

Regards,

[longneck]

please be more judicious in your use of the quote button. quoting the entire message you're replying to makes the thread difficult to read. i've edited your posts to remove the unneeded quote boxes.

1- What is the best Kill command for killing a MySQL thread? just "Kill thread-id"?

that's the only command for killing a thread.

2- Is it best to issue such command while loged in as MySQL root or server root?

root or not does not matter. whoever issues the command must have the PROCESS or SUPER privilege.

[WorldNews]

please be more judicious in your use of the quote button. quoting the entire message you're replying to makes the thread difficult to read. i've edited your posts to remove the unneeded quote boxes.

Yes, I will try to be next time.

that's the only command for killing a thread.

Ok.

root or not does not matter. whoever issues the command must have the PROCESS or SUPER privilege.

Ok, so user needs to be Root.

One related question:

what is the command for continuously seeing what Process (MySQL commands) are executing?

I mean "Show processlist" shows one static page of the most recent set of processlist. Is there a command that shows a continuous updating list of the processlists? Something like "Top", but one that shows what MySQL commands or like commands are executing?

I am hope you know what I mean.

Regards,

[longneck]

there is none.

[WorldNews]

Hum!
That is a real shame.
Such as software, info, is really needed.
I am surprised no one has taken the initiative to develop such a
software, specially given all the open source software that is
available today.

Maybe we should

there is none.

[longneck]

there is a third-party program call mysql top that you might be interested in.

[WorldNews]

Hi,

What is the URL to where I can get this?

Regards,

there is a third-party program call mysql top that you might be interested in.

[longneck]

http://jeremy.zawodny.com/mysql/mytop/

[WorldNews]

Hi,

Thanks for this info.
But this is a software that runs on the server side!
Is there a software that runs on client side, I mean on the desktop like
Putty, which allows us to keep an eye on the MySQL lines executing like
Top allows us to see all processes running on the server.

Regards,

http://jeremy.zawodny.com/mysql/mytop/