All Products
Search
Document Center

SHOW PROCESSLIST and KILL of the old version

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, continue this topic.
  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, see SHOW PROCESSLIST and KILL commands.

SHOW PROCESSLIST command

In a DRDS instance, you can run the SHOW 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] PROCESSLIST

When an SQL statement is excessively long, the responses of the SHOW PROCESSLIST command may be truncated. In this case, you can run the SHOW FULL 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.

Example:

  1. mysql> SHOW 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. ROWS_SENT: NULL
  11. ROWS_EXAMINED: NULL
  12. ROWS_READ: NULL
  13. *************************** 2. row ***************************
  14. ID: 0-0-521570
  15. USER: tddl5
  16. DB: tddl5_00
  17. COMMAND: Query
  18. TIME: 0
  19. STATE: User sleep
  20. INFO: /*DRDS /88.88.88.88/b67a0e4d8800000/ */ select sleep(1000)
  21. ROWS_SENT: NULL
  22. ROWS_EXAMINED: NULL
  23. ROWS_READ: NULL
  24. 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' | 'ALL'

The KILL command has two functions:

  1. Ends a specific SQL statement:KILL 'PROCESS_ID'

    The PROCESS_ID parameter is obtained from the ID column in the responses of the SHOW PROCESSLIST command.

    Note: Different from MySQL, DRDS returns the ID column as a string rather than a number. Therefore, PROCESS_ID must be enclosed by a pair of single quotes in the KILL command.

    Example:

    1. mysql> KILL '0-0-521570';
    2. Query OK, 0 rows affected (0.01 sec)
  2. Ends all 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 SQL statements that are being executed in the DRDS database.

    All 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.

Note: DRDS instances of earlier versions do not support KILL 'ALL'. An error is returned when you run the command in these instances. You can upgrade the DRDS instances to the latest version. For the upgrade method, see Upgrade the instance version.