MySQL Series (11)--performance analysis other common monitoring

Show status

Show status can query the current MySQL server state information. The statement does not require any permissions.

For show status you can use the LIKE clause to retrieve the required state information for fuzzy retrieval. Such as:

SHOW STATUS LIKE ‘Key%‘;

Show status is not a global and session-level state information. For the meaning of Session and global, refer to my previous article MySQL variables

SHOW SESSION STATUS;SHOW GLOBAL STATUS;

Show status shows a lot of state information, but the usual ones are the following:

1. About InnoDB Line Lock:

• Innodb_row_lock_current_waits
• Innodb_row_lock_time
• Innodb_row_lock_time_avg
• Innodb_row_lock_time_max
• Innodb_row_lock_waits

2. About Table Locks

• Table_locks_immediate
• table_locks_waited

3. About the Table class

• Created_tmp_disk_tables
• Created_tmp_tables
• Open_table_definitins
• Open_tables
• Opened_table_definitions
• Opened_tables

Innodb_lock_waits table

You can get the wait lock information for the current INNODB storage engine by querying the table.

Innodb_trx

You can query the table for transaction information that is open by the current server.

Wait a minute. Overall, MySQL instances typically have two schemas:

• INFORMATION_SCHEMA: There are many statistical tables in the schema, such as storage engine related, transaction, lock, and so on, which are some soft resource information about the server runtime;

• Performance_schema: This schema mainly contains the hard resource usage statistics run by server;

MySQL Series (11)--performance analysis other common monitoring