MySQL kill dead process script

Source: Internet
Author: User
Tags mysql manual

 

The MySQL + PHP mode often causes a large number of zombie processes in MySQL under high concurrency pressure, resulting in service downtime. In order to automatically kill these processes, a script is created and automatically executed on the backend of the server using crontab. After this is found, it is indeed a good relief of this problem. Send this script to Share it with everyone.

 

According to your actual needs, make some modifications:

 

SHELL script: mysqld_kill_sleep.sh

 

#! /Bin/sh

Mysql_pwd = "root Password"

Mysqladmin_exec = "/usr/local/bin/mysqladmin"

Mysql_exec = "/usr/local/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_timeout_log = "$ mysql_timeout_dir/mysql_timeout.log"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysqladmin_exec-uroot-p "$ mysql_pwd" processlist | awk '{print $12, $2, $4} '| grep-v Time | grep-v' | sort-rn> $ mysql_timeout_log

Awk '{if ($1> 30 & $3! = "Root") print "'" $ mysql_exec ""'-e ""/"kill ", $2 route /" "-uroot" "-p" "/" '"$ mysql_pwd" "'"/""; "} '$ mysql_timeout_log> $ mysql_kill_timeout_sh

Echo "check start..."> $ mysql_kill_timeout_log

Echo 'date'> $ mysql_kill_timeout_log

Cat $ mysql_kill_timeout_sh

 

Write this to mysqld_kill_sleep.sh. Then, run chmod 0 mysqld_kill_sleep.sh, chmod u + rx mysqld_kill_sleep.sh, and use the root account to run the command in cron. Adjust the time by yourself.

Displayed after execution:

 

 

 

Www #./mysqld_kill_sleep.sh

/Usr/local/bin/mysql-e "kill 27549"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27750"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27840"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27867"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27899"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27901"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27758"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27875"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27697"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27888"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27861"-uroot-p "mysql root Password ";

 

If the problem persists, change the last cat to sh.

 

I have rewritten the above script:

 

#! /Bin/bash

Mysql_pwd = "password"

Mysql_exec = "/usr/local/mysql/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysql_exec-uroot-p $ mysql_pwd-e "show processlist" | grep-I "Locked" >>$ mysql_kill_timeout_log

Chmod 777 $ mysql_kill_timeout_log

For line in '$ mysql_kill_timeout_log | awk' {print $1 }''

Do

Echo "$ mysql_exec-uroot-p $ mysql_pwd-e/" kill $ line/"> $ mysql_kill_timeout_sh

Done

Chmod 777 $ mysql_kill_timeout_sh

Cat $ mysql_kill_timeout_sh

 

========================================================== ==========================================

 

Many times! Lock the table accidentally! Here is the ultimate solution to the lock table!

 

Case 1

 

Mysql> show processlist;

 

See SQL statements

 

Rarely

 

Mysql> kill thread_id;

 

You can solve the problem.

 

The process of kill the first lock table is still not improved. Since it does not improve, we can try to kill all the lock table processes. The simple script is as follows.

 

#! /Bin/bash

Mysql-u root-e "show processlist" | grep-I "Locked"> locked_log. txt

 

For line in 'cat locked_log.txt | awk '{print $1 }''

Do

Echo "kill $ line;"> kill_thread_id. SQL

Done

Now the content of kill_thread_id. SQL is like this

 

Kill 66402982;

Kill 66402983;

Kill 66402986;

Kill 66402991;

.....

Well, we can execute it in the mysql shell to kill all the lock table processes.

 

Mysql> source kill_thread_id. SQL

 

Of course, you can do it in one line.

For id in 'mysqladmin processlist | grep-I locked | awk '{print $1 }''

Do

Mysqladmin kill $ {id}

Done

 

Case 2

 

If a large number of operations can be produced through a series of select statements, the results can be processed in batches theoretically.

However, mysql does not provide the eval function for analyzing the result set. Therefore, you can only save the select result to a temporary file and then execute the commands in the temporary file.

The specific process is as follows:

Mysql> SELECT concat ('Kill ', id,'; ') FROM information_schema.processlist WHERE user = 'root ';

+ ------------------------ +

| Concat ('Kill ', id ,';')

+ ------------------------ +

| KILL 3101;

| KILL 2946;

+ ------------------------ +

2 rows in set (0.00 sec)

Mysql> SELECT concat ('Kill ', id,'; ') FROM information_schema.processlist WHERE user = 'root' INTO outfile'/tmp/a.txt ';

Query OK, 2 rows affected (0.00 sec)

Mysql> source/tmp/a.txt;

Query OK, 0 rows affected (0.00 sec)

 

 

Case 3

 

The MySQL + PHP mode often causes a large number of zombie processes in MySQL under high concurrency pressure, resulting in service downtime. In order to automatically kill these processes, a script is created and automatically executed on the backend of the server using crontab. After this is found, it is indeed a good relief of this problem. Send this script to Share it with everyone.

According to your actual needs, make some modifications:

SHELL script: mysqld_kill_sleep.sh

#! /Bin/sh

Mysql_pwd = "root Password"

Mysqladmin_exec = "/usr/local/bin/mysqladmin"

Mysql_exec = "/usr/local/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_timeout_log = "$ mysql_timeout_dir/mysql_timeout.log"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysqladmin_exec-uroot-p "$ mysql_pwd" processlist | awk '{print $12, $2, $4} '| grep-v Time | grep-v' | sort-rn> $ mysql_timeout_log

Awk '{if ($1> 30 & $3! = "Root") print "'" $ mysql_exec ""'-e "" \ "kill ", $2 "\" "-uroot" "-p" "\" '"$ mysql_pwd ""'""\"""; "} '$ mysql_timeout_log> $ mysql_kill_timeout_sh

Echo "check start..."> $ mysql_kill_timeout_log

Echo 'date'> $ mysql_kill_timeout_log

Cat $ mysql_kill_timeout_sh

Write this to mysqld_kill_sleep.sh. Then, run chmod 0 mysqld_kill_sleep.sh, chmod u + rx mysqld_kill_sleep.sh, and use the root account to run the command in cron. Adjust the time by yourself.

Displayed after execution:

 

Www #./mysqld_kill_sleep.sh

/Usr/local/bin/mysql-e "kill 27549"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27750"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27840"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27867"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27899"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27901"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27758"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27875"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27697"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27888"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27861"-uroot-p "mysql root Password ";

If the problem persists, change the last cat to sh.

 

I have rewritten the above script:

#! /Bin/bash

Mysql_pwd = "password"

Mysql_exec = "/usr/local/mysql/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysql_exec-uroot-p $ mysql_pwd-e "show processlist" | grep-I "Locked" >>$ mysql_kill_timeout_log

Chmod 777 $ mysql_kill_timeout_log

For line in '$ mysql_kill_timeout_log | awk' {print $1 }''

Do

Echo "$ mysql_exec-uroot-p $ mysql_pwd-e \" kill $ line \ "" >>$ mysql_kill_timeout_sh

Done

Chmod 777 $ mysql_kill_timeout_sh

Cat $ mysql_kill_timeout_sh

 

Is it convenient!

========================================================== ==================================

 

The output result of the processlist command shows which threads are running. This command can be used to identify problematic query statements.

 

1. Enter mysqladmin processlist In the mysql/bin directory;

 

2. Start mysql and enter show processlist;

 

If you have the SUPER permission, you can see all the threads. Otherwise, you can only see the self-initiated threads (this refers to the thread currently running in the corresponding MySQL account ).

 

The data format is as follows (only three are intercepted ):

 

Mysql> show processlist;

 

+ ----- + ------------- + -------------------- + ------- + --------- + ------- + ---------------------------------- + ----------

 

| Id | User | Host | db | Command | Time | State | Info

 

+ ----- + ------------- + -------------------- + ------- + --------- + ------- + ---------------------------------- + ----------

 

| 207 | root | 192.168.0.20: 51718 | mytest | Sleep | 5 | NULL

 

| 208 | root | 192.168.0.20: 51719 | mytest | Sleep | 5 | NULL

 

| 220 | root | 192.168.0.20: 51731 | mytest | Query | 84 | Locked |

 

Select bookname, culture, value, type from book where id = 001

 

Let's briefly describe the meaning and purpose of each column. The first column, id, needless to say, is an identifier. It is useful when you want to kill a statement. The user column displays the user before the ticket. If it is not the root user, this command only displays the SQL statements within your permission range. Host column, indicating the port from which the statement is sent. Well, it can be used to track users with problematic statements. Db column to display the database to which the process is currently connected. Command column, showing the commands executed by the current connection, which is generally sleep, query, and connect ). Time column, the duration of this state, in seconds. The state column displays the status of the SQL statement that uses the current connection. It is an important column and will be described in the future. Note that the state is only a certain state in the statement execution, for an SQL statement that has been queried as an example, it may need to pass through the copying to tmp table, Sorting result, Sending data and other statuses to complete the SQL statement. The info column displays this SQL statement because the length is limited, therefore, long SQL statements are not displayed completely, but an important basis for determining a problematic statement.

 

The most important part of this command is the state column. mysql lists the following states:

 

Checking table

Checking the data table (this is automatic ).

Closing tables

Refreshing the modified data in the table to the disk and closing the used table. This is a very fast operation. If not, check whether the disk space is full or the disk is under a heavy load.

Connect Out

The replication slave server is connecting to the master server.

Copying to tmp table on disk

Because the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.

Creating tmp table

Creating a temporary table to store some query results.

Deleting from main table

The server is executing the first part of multi-Table deletion. The first table has just been deleted.

Deleting from reference tables

The server is executing the second part of multi-Table deletion and is deleting records of other tables.

Flushing tables

Executing flush tables, waiting for other threads to close the data table.

Killed

If a kill request is sent to a thread, the thread will check the kill flag and discard the next kill request. MySQL checks the kill flag in each primary loop. However, in some cases, the thread may die after a short period of time. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.

Locked

It is locked by other queries.

Sending data

Processing the record of the Select query and sending the result to the client.

Sorting for group

Sorting for group.

Sorting for order

Sorting order.

Opening tables

This process should be fast unless it is disturbed by other factors. For example, a data TABLE cannot be opened by another thread before the Alter TABLE or lock table statement is executed. Opening a table.

Removing duplicates

A Select DISTINCT query is being executed, but MySQL cannot optimize those duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records and then send the results to the client.

Reopen table

A lock is obtained for a table, but the lock can be obtained only after the table structure is modified. The lock has been released, the data table is closed, and the data table is being re-opened.

Repair by sorting

The repair command is being sorted to create an index.

Repair with keycache

The repair command is using the index cache to create a new index one by one. It is slower than Repair by sorting.

Searching rows for update

We are talking about identifying qualified records for updates. It must be completed before the related record is updated.

Sleeping

Waiting for the client to send a new request.

System lock

Waiting for an external system lock to be obtained. If multiple mysqld servers are not running to request the same table at the same time, you can add the -- skip-external-locking parameter to disable external system locks.

Upgrading lock

Insert DELAYED is trying to get a lock table to Insert a new record.

Updating

Searching for matched records and modifying them.

User Lock

Waiting for GET_LOCK ().

Waiting for tables

This thread is notified that the data table structure has been modified. You need to re-open the data table to obtain the new structure. Then, in order to re-open the data table, you must wait until all other threads close the table. This notification is generated in the following situations: flush tables tbl_name, Alter TABLE, rename table, repair table, analyze table, or optimize table.

Waiting for handler insert

Insert DELAYED has completed all the Insert operations to be processed and is waiting for new requests.

Most States correspond to fast operations. As long as one thread remains in the same state for several seconds, a problem may occur and you need to check it.

Other statuses are not listed above, but most of them are only needed to check whether there are errors on the server.

 

The mysql Manual contains descriptions of all states, links to the following: http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.