View detailed execution information of MySQL active sessions from performance_schema,

View detailed execution information of MySQL active sessions from performance_schema,

 

Source: http://www.cnblogs.com/wy123/p/7851294.html

 

During database exception diagnosis, one of the most important reference information on SQL Server is to see which active sessions are currently available,
What statements are executed by these active sessions, how they are executed (plans), how long they have been run, and what resources are waiting?
This information is then used to provide a basis for problem diagnosis.

In mysql, although the database platform is changed, although some things are different, I personally think that these indicators are nothing more
One of the most common commands for viewing active sessions is show processlist;
The result contains a time field, which is not helpful in some cases. It is not the running time of a Session, but the running time of the current statement.
If you want to know how long a Session has been executed, it is not enough to use show processlist.

 

The following is a simple stored procedure.

Create definer = 'root' @ '%' PROCEDURE 'test _ long_run_ SQL '() BEGIN -- use select sleep (10) to simulate a long running process or SQL statement select sleep (10); select count (1) from a; select sleep (15); delete from test01; END

When this stored procedure is called, execute show processlist from another Session.
How to understand the meaning of the time field, for example, there are multiple SQL statements in the stored procedure, each statement will reset the time in show processlist when running.
For example, this stored procedure will be executed for a total of 25 seconds. When select sleep (15); is executed, at least 10 seconds are run. Why is the Time here 3 seconds?
The answer is, this 3 second is the execution time of the third SQL statement, rather than the execution time of the entire Session (stored procedure.

The reason why I am entangled in this problem is that when I first came into contact with MySQL (older than 5.6), I was able to guess the problem.
In the test environment, an endless loop occurs in a stored procedure due to a bug. The stored procedure ran for two days, knowing that the server was busy,
However, when using show processlist, you cannot find a Session that has been running for a long time.
Because of the loop, the executed SQL statements are different, Time is reset constantly, and the source of the statement cannot be found (the name of the call stored procedure), and a long Session cannot be known.

 

After mysql5.7, the new sys database has a session System View,
The information recorded in this view is relatively complete, not only recording the execution time of the current statement, but also the Session-level execution time,
Reference: A statement_latency is Session-level, and a statement_latency is statement-level.

This is the definition of the session System View in the sys system library. It is found that it is queried from processlist.

SELECT `processlist`.`thd_id` AS `thd_id`        , `processlist`.`conn_id` AS `conn_id`        , `processlist`.`user` AS `user`        , `processlist`.`db` AS `db`        , `processlist`.`command` AS `command`        , `processlist`.`state` AS `state`        , `processlist`.`time` AS `time`        , `processlist`.`current_statement` AS `current_statement`        , `processlist`.`statement_latency` AS `statement_latency`        , `processlist`.`progress` AS `progress`        , `processlist`.`lock_latency` AS `lock_latency`        , `processlist`.`rows_examined` AS `rows_examined`        , `processlist`.`rows_sent` AS `rows_sent`        , `processlist`.`rows_affected` AS `rows_affected`        , `processlist`.`tmp_tables` AS `tmp_tables`        , `processlist`.`tmp_disk_tables` AS `tmp_disk_tables`        , `processlist`.`full_scan` AS `full_scan`        , `processlist`.`last_statement` AS `last_statement`        , `processlist`.`last_statement_latency` AS `last_statement_latency`        , `processlist`.`current_memory` AS `current_memory`        , `processlist`.`last_wait` AS `last_wait`        , `processlist`.`last_wait_latency` AS `last_wait_latency`        , `processlist`.`source` AS `source`        , `processlist`.`trx_latency` AS `trx_latency`        , `processlist`.`trx_state` AS `trx_state`        , `processlist`.`trx_autocommit` AS `trx_autocommit`        , `processlist`.`pid` AS `pid`        , `processlist`.`program_name` AS `program_name`FROM `sys`.`processlist`WHERE `processlist`.`conn_id` IS NOT NULL    AND `processlist`.`command` <> 'Daemon'

Continue with the definition of processlist.

SELECT `pps`.`THREAD_ID` AS `thd_id`    ,`pps`.`PROCESSLIST_ID` AS `conn_id`    , if(`pps`.`NAME` = 'thread/sql/one_connection', concat(`pps`.`PROCESSLIST_USER`, '@', `pps`.`PROCESSLIST_HOST`), replace(`pps`.`NAME`, 'thread/', '')) AS `user`    , `pps`.`PROCESSLIST_DB` AS `db`    , `pps`.`PROCESSLIST_COMMAND` AS `command`, `pps`.`PROCESSLIST_STATE` AS `state`    ,`pps`.`PROCESSLIST_TIME` AS `time`    , `sys`.`format_statement`(`pps`.`PROCESSLIST_INFO`) AS `current_statement`    , if(isnull(`esc`.`END_EVENT_ID`), `sys`.`format_time`(`esc`.`TIMER_WAIT`), NULL) AS `statement_latency`    , if(isnull(`esc`.`END_EVENT_ID`), round(100 * (`estc`.`WORK_COMPLETED` / `estc`.`WORK_ESTIMATED`), 2), NULL) AS `progress`    , `sys`.`format_time`(`esc`.`LOCK_TIME`) AS `lock_latency`, `esc`.`ROWS_EXAMINED` AS `rows_examined`    , `esc`.`ROWS_SENT` AS `rows_sent`    , `esc`.`ROWS_AFFECTED` AS `rows_affected`    , `esc`.`CREATED_TMP_TABLES` AS `tmp_tables`    , `esc`.`CREATED_TMP_DISK_TABLES` AS `tmp_disk_tables`    , if(`esc`.`NO_GOOD_INDEX_USED` > 0 OR `esc`.`NO_INDEX_USED` > 0, 'YES', 'NO') AS `full_scan`    , if(`esc`.`END_EVENT_ID` IS NOT NULL, `sys`.`format_statement`(`esc`.`SQL_TEXT`), NULL) AS `last_statement`    , if(`esc`.`END_EVENT_ID` IS NOT NULL, `sys`.`format_time`(`esc`.`TIMER_WAIT`), NULL) AS `last_statement_latency`    , `sys`.`format_bytes`(`mem`.`current_allocated`) AS `current_memory`    , `ewc`.`EVENT_NAME` AS `last_wait`    , if(isnull(`ewc`.`END_EVENT_ID`)        AND `ewc`.`EVENT_NAME` IS NOT NULL, 'Still Waiting', `sys`.`format_time`(`ewc`.`TIMER_WAIT`)) AS `last_wait_latency`    , `ewc`.`SOURCE` AS `source`, `sys`.`format_time`(`etc`.`TIMER_WAIT`) AS `trx_latency`, `etc`.`STATE` AS `trx_state`    , `etc`.`AUTOCOMMIT` AS `trx_autocommit`    , `conattr_pid`.`ATTR_VALUE` AS `pid`    , `conattr_progname`.`ATTR_VALUE` AS `program_name`FROM `performance_schema`.`threads` `pps`    LEFT JOIN `performance_schema`.`events_waits_current` `ewc` ON `pps`.`THREAD_ID` = `ewc`.`THREAD_ID`    LEFT JOIN `performance_schema`.`events_stages_current` `estc` ON `pps`.`THREAD_ID` = `estc`.`THREAD_ID`    LEFT JOIN `performance_schema`.`events_statements_current` `esc` ON `pps`.`THREAD_ID` = `esc`.`THREAD_ID`    LEFT JOIN `performance_schema`.`events_transactions_current` `etc` ON `pps`.`THREAD_ID` = `etc`.`THREAD_ID`    LEFT JOIN `sys`.`x$memory_by_thread_by_current_bytes` `mem` ON `pps`.`THREAD_ID` = `mem`.`thread_id`    LEFT JOIN `performance_schema`.`session_connect_attrs` `conattr_pid`    ON `conattr_pid`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`        AND `conattr_pid`.`ATTR_NAME` = '_pid'    LEFT JOIN `performance_schema`.`session_connect_attrs` `conattr_progname`    ON `conattr_progname`.`PROCESSLIST_ID` = `pps`.`PROCESSLIST_ID`        AND `conattr_progname`.`ATTR_NAME` = 'program_name'ORDER BY `pps`.`PROCESSLIST_TIME` DESC, `last_wait_latency` DESC

It can be found that statement_latency, that is, the execution time of the statement or session,
Obtained from the system table 'performance _ scheme'. 'events _ statements_current '.
That is, the timer_wait field in 'performance _ scheme'. 'events _ statements_current '.

To observe the information in 'performance _ scheme'. 'events _ statements_current '.
1. It is not difficult to find that the session-level running time is different from the statement-level running time. When thread_id is the same, event_id is different. This is also an SQL statement used to differentiate sessions and statements.
2. From event_name, we can see that the former is call procedure, and the latter is the specific statement in procedure.
3. SQL _text is a real SQL statement. You can clearly understand what statements are being executed.

To be honest, I have never liked some MySQL-encapsulated commands, such as show variables and show status.
This is not to say that these commands are not good. If you can find them directly from the system table, you can find out where the data of these encapsulated commands comes from.
After figuring out the source, you may get more things you want based on your own needs.

 

In other databases, there are a wide range of system tables available for query. The performance_schema of MySQL provides the same similar content. Using the information in this database can be of great help.
When diagnosing exceptions, figuring out the active Session and its execution source, the statements currently executed, the execution time, and so on is an important basis for performing trouble shooting.
Otherwise, if the database is connected to what statements are currently running and where the source of the statement initiation is, it will be hard to figure out how long it has been running, and there is no way to talk about optimization and diagnosis.