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