Shulou(Shulou.com)06/01 Report--

In the previous article, "grouping Statistical views by host | understanding the sys system Library in an all-round way", we introduced the views grouped by host in the sys system library. Similarly, this issue will introduce you to views classified by user. Next, please follow us to start the systematic learning journey of sys system library.

01username description summary

View statistics such as total execution time, average execution time, total IOS, total memory usage, and number of table scans grouped by users in active connections. By default, they are sorted by total delay time (execution time) in descending order. Data sources: performance_schema.accounts, sys.x$user_summary_by_statement_latency, sys.x$user_summary_by_file_io, sys.x$memory_by_user_by_current_bytes

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:54:32 > select * from user_summary limit 1\ Graph * 1. Row * * user: admin statements: 90530statement_latency: 2.09 hstatement_avg_latency: 83.12 ms Table_scans: 498file_ios: 60662 file_io_latency: 31.05scurrent_connections: 4total_connections: 1174 unique_hosts: 2 current_memory: 85.34MiBtotal_memory_allocated: 7.21GiB1 row in set (0.04sec) # View with x$ prefix admin@localhost: sys 12:55:48 > select * from x$user_summary limit 1\ Gridged images * * 1. Row * * user: admin statements: 90752statement_latency: 7524792139504000statement_avg_latency: 82915992369.3583 table_scans: 500 file_ios: 60662file_io_latency: 31053125849250current_connections: 4total_connections: 1174unique_hosts: 2 current_memory: 89381384total_memory_allocated: 77551734361 row in set (0.02 sec)

The view field has the following meanings:

User: client access user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

Statements: the total number of statements executed by the corresponding user

Statement_latency: total delay time of statements executed by the corresponding user (execution time)

Statement_avg_latency: the average delay time (execution time) of each statement in the statements executed by the user (SUM (stmt.total_latency/SUM (stmt.total))

Table_scans: the total number of table scans corresponding to the statements executed by the user

File_ios: the total number of file Imax O events generated by the statements executed by the user

File_io_latency: the total delay time (execution time) of the file Imax O event generated by the statement executed by the user

Current_connections: the current number of connections for the corresponding user

Total_connections: the total number of historical connections of the corresponding user

Unique_hosts: the number of connections corresponding to users from different hosts (deduplicated for hostname)

Current_memory: the amount of memory allocated currently used by the corresponding user's connection

Total_memory_allocated: the amount of historical memory allocated to the corresponding user's connection

PS: this view only counts the file IO wait event information ("wait/io/file/%")

02username characters by bylaws fileholders iothol.x user names by bylaws filetellio

By default, it is sorted by the descending order of the total file Imax O time delay time (execution time) according to the file Iram O delay time and IOS statistics of the user group. Source: performance_schema.events_waits_summary_by_user_by_event_name

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:56:18 > select * from user_summary_by_file_io limit 3 +-+ | user | ios | io_latency | +-+ | admin | 30331 | 15.53s | | background | 10119 | 2.49s | | qfsys | 281 | 4.69s | +- -+ 3 rows in set (0.01 sec) # View with x$ prefix admin@localhost: sys 12:56:21 > select * from x$user_summary_by_file_io limit 3 +-+ | user | ios | io_latency | +-+ | admin | 30331 | 15526562924625 | background | 10122 | 2489231563125 | | qfsys | 281 | 4689150375 | +- -+ 3 rows in set (0.00 sec)

The view field has the following meanings:

User: client user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

Ios: the total number of file Istroke O events of the corresponding user

Io_latency: the total delay time (execution time) of the corresponding user's file Istroke O event

PS: this view only counts the file IO wait event information ("wait/io/file/%")

03username filled by fileholders, fileholders, types, genres, genres, types

File Imax O delay and IOS statistics grouped by user and event type (event name) are sorted by default by user name and total file Imax O time delay time (execution time) in descending order. Source: performance_schema.events_waits_summary_by_user_by_event_name

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:56:24 > select * from user_summary_by_file_io_type limit 3 +-+ | user | event_name | total | latency | max_latency | +-+- -- + | admin | wait/io/file/sql/io_cache | 27955 | 10.53s | 67.61 ms | | admin | wait/io/file/innodb/innodb_log_file | 912 | 2.14s | 28.22 Ms | | admin | wait/io/file/sql/binlog | 879 | 2.05s | 31.75 ms | +-+-- + 3 rows in set (0.00 sec) # views with x$ prefix admin@localhost: sys 12:56:48 > select * from x$user_summary_by_file_io_type limit 3 +-+-+ | user | event_name | total | latency | max_latency | +- -- +-+ | admin | wait/io/file/sql/io_cache | 27955 | 10534662677625 | 67608294000 | | admin | wait/io/file/innodb/innodb_log_file | 912 | 2143870695375 | 28216455000 | | admin | wait/io/file/sql/binlog | 879 | 2054976453000 | 31745275125 | +-+-+ 3 rows in set (0.01 sec)

The view field has the following meanings:

User: client user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

EVENT_NAME: the name of the file iCando event

Total: the total number of file Iamp O events that occurred to the corresponding user

Latency: the total delay time (execution time) of the corresponding user's file Istroke O event

Max_latency: the maximum delay time (execution time) corresponding to the user's single file Icano event

PS: this view only counts the file IO wait event information ("wait/io/file/%")

04username characters by bylaws stages.xdistinct usernames by bylaws stages

Phase event statistics grouped by user, sorted by default by user name and total delay time (execution time) of the phase event in descending order. Source: performance_schema.events_stages_summary_by_user_by_event_name

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:56:51 > select * from user_summary_by_stages limit 3 +-+-+ | user | event_name | total | total_latency | avg_latency | +- -- +-+ | background | stage/innodb/buffer pool load | 1 | 12.56s | 12.56s | +-+- -+ 1 row in set (0.01 sec) # View with x$ prefix admin@localhost: sys 12:57:10 > select * from x$user_summary_by_stages limit 3 +-+-+ | user | event_name | total | total_latency | avg_latency | + -+-+ | background | stage/innodb/buffer pool load | 1 | 12561724877000 | 12561724877000 | +- -+ 1 row in set (0.00 sec)

The view field has the following meanings:

User: client user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

EVENT_NAME: phase event name

Total: the total number of phase events corresponding to the user

Total_latency: the total delay time corresponding to the user's phase event (execution time)

Avg_latency: the average delay time (execution time) of the corresponding user's phase event

05usernames, parts, states, books, latencybooks, books, books

By default, it is sorted in descending order by total statement delay time (execution time) according to the statement statistics of the user group. Source: performance_schema.events_statements_summary_by_user_by_event_name

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:57:13 > select * from user_summary_by_statement_latency limit 3 +- -+ | user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +- -+ | admin | 45487 | 1.05h | 45.66m | 19.02 s | 6065 | 17578842 | 1544 | 258 | | qfsys | 9 | 929.43 ms | 928.68 ms | 0 ps | 5 | 0 | 0 | 0 | background | 0 | 0 ps | 0 | 0 | 0 | + -+ 3 rows in set (0.00 sec) # View with x$ prefix admin@localhost: sys 12:57:34 > select * from x$user_summary_by_statement_latency limit 3 +- +-+ | user | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +- -+ | admin | 45562 | 3762457232413000 | 2739502018445000 | 19019928000000 | 6068 | 17579421 | 1544 | 259 | | qfsys | 9 | 929429421000 | 928682487000 | 0 | 5 | | | 0 | 0 | 0 | background | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-- | -+-+ 3 rows in set (0.00 sec)

The view field has the following meanings:

User: client user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

Total: the total number of statements executed by the corresponding user

Total_latency: total delay time of statements executed by the corresponding user (execution time)

Max_latency: the maximum delay time per statement executed by the user (execution time)

Lock_latency: the total time spent waiting for the lock of the statement executed by the user

Rows_sent: the total number of data rows returned to the client corresponding to the statements executed by the user

Rows_examined: the total number of rows of data read from the storage engine by the statements executed by the user

Rows_affected: the total number of rows of data affected by the statements executed by the user

Full_scans: the total number of full table scans corresponding to statements executed by the user

06username characters by bylaws statementology type

Statement statistics grouped by user and statement event type (the event type name is the event_name of the statement event intercepting the last part of the string, which is also similar to the statement command type string), sorted by default by user name and the total delay time (execution time) of the corresponding statement in descending order. Source: performance_schema.events_statements_summary_by_user_by_event_name

Let's take a look at the results returned by the query using this view.

# views without x$ prefix admin@localhost: sys 12:57:38 > select * from user_summary_by_statement_type limit 3 +-+ -+ | user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +-+- -+ | admin | alter_table | 2 | 56.56m | 43.62m | 0 ps | 0 | 0 | 0 | | admin | select | 3662 | 5.53m | 2.02m | 4.73s | 6000 | 17532984 | 0 | 148 | | admin | insert | 1159 | 36.04s | 337.22 ms | 14.23s | 0 | 1159 | 0 | +- -- +-+ 3 rows in set (0.00 sec) # with x$ prefix View of admin@localhost: sys 12:57:50 > select * from x$user_summary_by_statement_type limit 3 +- -+-+ | user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans | +- -+-+ | admin | alter_table | 2 | 3393877088372000 | 2617456143674000 | 0 | 0 | 0 | 0 | 0 | admin | select | 3663 | 331756087959000 | 121243627173000 | 473310000000 | 6003 | 17533557 | 0 | 149 | admin | insert | 1159 | 36041502943000 | 337218573000 | 14229439000000 | 0 | 1159 | 0 | +- -+ 3 rows in set (0.00 sec)

The view field has the following meanings:

User: client user name. If user is listed as NULL in the performance_ schema table, it is assumed to be a background thread, and the field is' background',. If it is a foreground thread, then this field corresponds to the specific user name

Statement: the last part of the string of the statement event name, similar to the command type string of the statement

Other fields have the same meaning as the fields in the user_summary_by_statement_latency,x$user_summary_by_statement_latency view

This is the end of this issue, and the reference links for this issue are as follows:

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-type.html

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io.html

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-file-io-type.html

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-stages.html

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary-by-statement-latency.html

Https://dev.mysql.com/doc/refman/5.7/en/sys-user-summary.html

| | author profile |

Luo Xiaobo Walk senior database technology expert

IT has worked in the industry for many years, has served as an operation and maintenance engineer, senior operation and maintenance engineer, operation and maintenance manager, database engineer, has participated in the design and preparation of version release systems, lightweight monitoring systems, operation and maintenance management platform, database management platform, familiar with MySQL architecture, InnoDB storage engine, like to specialize in open source technology, the pursuit of perfection.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.