MySQL Processlist state状态查询

Processlist是DBA排查问题的主要线索,而state就是processlist的心脏指标。

GreatOpenSource> show processlist;
+----+------+-----------------+------+---------+------+---------------------------------+------------------+
| Id | User | Host            | db   | Command | Time | State                           | Info             |
+----+------+-----------------+------+---------+------+---------------------------------+------------------+
|  3 | root | localhost:52618 | test | Query   |    5 | Waiting for table metadata lock | select * from t1 |
|  4 | root | localhost:52919 | test | Query   |    0 | starting                        | show processlist |
+----+------+-----------------+------+---------+------+---------------------------------+------------------+

状态定义位置

sql/mysqld.cc


PSI_stage_info *all_server_stages[]=

PSI_stage_info stage_waiting_for_handler_lock= { 0, "waiting for handler lock", 0};
PSI_stage_info stage_waiting_for_handler_open= { 0, "waiting for handler open", 0};
PSI_stage_info stage_waiting_for_insert= { 0, "Waiting for INSERT", 0};
PSI_stage_info stage_waiting_for_master_to_send_event= { 0, "Waiting for master to send event", 0};
PSI_stage_info stage_waiting_for_master_update= { 0, "Waiting for master update", 0};
P

sql/mdl.cc

PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{
  {0, "Waiting for global read lock", 0},
  {0, "Waiting for tablespace metadata lock", 0},
  {0, "Waiting for schema metadata lock", 0},
  {0, "Waiting for table metadata lock", 0},
  {0, "Waiting for stored function metadata lock", 0},
  {0, "Waiting for stored procedure metadata lock", 0},
  {0, "Waiting for trigger metadata lock", 0},
  {0, "Waiting for event metadata lock", 0},
  {0, "Waiting for commit lock", 0},
  {0, "User lock", 0}, /* Be compatible with old status. */
  {0, "Waiting for locking service lock", 0},
  {0, "Waiting for backup lock", 0},
  {0, "Waiting for binlog lock", 0}
};

设置状态函数

  void enter_stage(const PSI_stage_info *stage,
                   PSI_stage_info *old_stage,
                   const char *calling_func,
                   const char *calling_file,
                   const unsigned int calling_line);

  void enter_cond(mysql_cond_t *cond, mysql_mutex_t* mutex,
                  const PSI_stage_info *stage, PSI_stage_info *old_stage,
                  const char *src_function, const char *src_file,
                  int src_line)

两个函数可以设置当前线程的状态。

线程基本流程

  1. stage_starting
  2. stage_checking_permissions
  3. stage_opening_tables
  4. stage_init
  5. stage_system_lock
  6. stage_optimizing
  7. stage_statistics
  8. stage_preparing
  9. stage_executing
  10. stage_sending_data
  11. stage_end
  12. stage_query_end
  13. stage_closing_tables
  14. stage_freeing_items
  15. stage_cleaning_up