All Products
Search
Document Center

SHOW PROCESSLIST and KILL

Last Updated: Aug 17, 2020

Version description

  1. The Distributed Relational Database Service (DRDS) versions earlier than 5.1.28-1408022 only support the SHOW PROCESSLIST and KILL commands for physical connections. If your DRDS instance version is earlier than 5.1.28-1408022, see SHOW PROCESSLIST and KILL commands for earlier versions .
  2. The DRDS version 5.1.28-1408022 and later support the SHOW PROCESSLIST and KILL commands for logical and physical connections. If your DRDS instance version is 5.1.28-1408022 or later, continue this topic.

SHOW PROCESSLIST command

In a DRDS instance, you can run the SHOW PROCESSLIST command to view the information such as connections to the DRDS instance and SQL statements that are being executed in this instance.

Syntax

  1. SHOW [FULL] PROCESSLIST

Examples

  1. mysql> SHOW PROCESSLIST\G
  2. ID: 1971050
  3. USER: admin
  4. HOST: 111.111.111.111:4303
  5. DB: drds_test
  6. COMMAND: Query
  7. TIME: 0
  8. STATE:
  9. INFO: show processlist
  10. 1 row in set (0.01 sec)

The following describes the meanings of the fields in the result set:

  • ID: the ID of the connection. The value is a number of the Long type.
  • USER: the name of the user who establishes the connection.
  • HOST: the IP address and port number of the host that establishes the connection.
  • DB: the name of the database to which the connection is established.
  • COMMAND: the usage state of the connection. Currently, this field can be set to the following values:
    • Query: indicates that the current connection is executing an SQL statement.
    • Sleep: indicates that the current connection is idle.
  • TIME: the duration when the connection is in the current state:
    • When the value of COMMAND is Query, TIME indicates how long the current SQL statement has been executed over the connection.
    • When the value of COMMAND is Sleep, this parameter indicates how long the connection has been in idle state.
  • STATE: Currently, no meaning has been assigned for this field. The value is constantly empty.
  • INFO:
    • When the value of COMMAND is Query, INFO indicates the content of the SQL statement being executed over the connection. When the FULL parameter is not specified, a maximum of the first 30 characters of the SQL statement being executed are returned. When the FULL parameter is specified, a maximum of the first 1,000 characters of the SQL statement being executed are returned.
    • When the value of COMMAND is other values, this field is meaningless and left empty.

SHOW PHYSICAL_PROCESSLIST command

In a DRDS instance, you can run the SHOW PHYSICAL_PROCESSLIST command to view the information about all the SQL statements that are being executed on the underlying ApsaraDB RDS for MySQL instances.

Syntax

  1. SHOW [FULL] PHYSICAL_PROCESSLIST

When an SQL statement is excessively long, the responses of the SHOW PHYSICAL_PROCESSLIST command may be truncated. In this case, you can run the SHOW FULL PHYSICAL_PROCESSLIST command to obtain the complete SQL statement.

The meaning of each column in the responses is equivalent to that in the responses of the SHOW PROCESSLIST command. For more information, see SHOW PROCESSLIST syntax.

Different from MySQL, DRDS returns the physical connection ID as a string rather than a number.

Examples

  1. mysql> SHOW PHYSICAL_PROCESSLIST\G
  2. *************************** 1. row ***************************
  3. ID: 0-0-521414
  4. USER: tddl5
  5. DB: tddl5_00
  6. COMMAND: Query
  7. TIME: 0
  8. STATE: init
  9. INFO: show processlist
  10. *************************** 2. row ***************************
  11. ID: 0-0-521570
  12. USER: tddl5
  13. DB: tddl5_00
  14. COMMAND: Query
  15. TIME: 0
  16. STATE: User sleep
  17. INFO: /*DRDS /88.88.88.88/b67a0e4d8800000/ */ select sleep(1000)
  18. 2 rows in set (0.01 sec)

KILL command

The KILL command is used to terminate an SQL statement that is being executed.

A DRDS instance connects to an ApsaraDB RDS for MySQL instance by using the username created in this instance. Therefore, after you connect to the ApsaraDB RDS for MySQL instance directly, you still do not have the KILL permission on the requests initiated by the DRDS instance.

To terminate an SQL statement that is being executed on the DRDS instance, you must use tools such as the MySQL command line and DMS to connect to the DRDS instance, and then run the KILL command on the DRDS instance.

Syntax

  1. KILL PROCESS_ID | 'PHYSICAL_PROCESS_ID' | 'ALL'

The KILL command has three functions:

  • Ends a specific logic SQL statement: KILL PROCESS_ID
    The PROCESS_ID parameter is obtained from the ID column in the responses of the SHOW [FULL] PROCESSLIST command.
    In the DRDS instance, the KILL PROCESS_ID command terminates the logical and physical SQL statements being executed over the connection, and disconnects the connection.
    The DRDS instance does not support the KILL QUERY command.
  • Ends a specific physical SQL statement: KILL 'PHYSICAL_PROCESS_ID'
    The PHYSICAL_PROCESS_ID parameter is obtained from the ID column in the responses of the SHOW PHYSICAL_PROCESS_ID command.

    PHYSICAL\_PROCESS\_ID is a string rather than a number. Therefore, PHYSICAL\_PROCESS\_ID must be enclosed in a pair of single quotation marks in the KILL command.

    Example:

    1. mysql> KILL '0-0-521570';
    2. Query OK, 0 rows affected (0.01 sec)
  • Ends all physical SQL statements that are executed by DRDS in the current database: KILL 'ALL'
    When the underlying ApsaraDB RDS for MySQL instances are overloaded due to several SQL statements, you can use the KILL 'ALL' command to terminate all the physical SQL statements that are being executed in the current DRDS database.
    All physical SQL statements indicated by PROCESS that meet the following conditions can be terminated by the KILL 'ALL' command:

    • The user of the PROCESS is created in an ApsaraDB RDS for MySQL instance by the DRDS instance.
    • The physical SQL statement indicated by PROCESS is executing a query. In other words, the value of COMMAND is Query.