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

| | introduction |

In the previous article, "event logging | Comprehensive introduction to performance_schema," we introduced performance_schema 's event log table in detail and congratulated you on your two most difficult periods on your way to learning performance_schema. Now, I believe you have a better understanding of what an event is, but sometimes we do not need to know every event record information generated at every moment. For example, we want to know the event statistics for a period of time since the database is running. At this time, you need to check the event statistics table. Today we will take you on the journey of the fourth part of the series (a total of 7 chapters in the department). In this issue, we will give you a comprehensive explanation of the event statistics in performance_schema. The statistical event table is divided into five categories: waiting events, phase events, statement events, transaction events and memory events. Next, please follow us to start the learning journey of performance_schema system.

| | waiting event statistics table |

Performance_schema aggregates the data related to waiting events according to different grouping columns (different latitudes). The aggregate statistics include: number of events, total waiting time, minimum, maximum and average waiting time. Note: some of the collection functions of waiting events are disabled by default, and can be dynamically enabled through setup_instruments and setup_objects tables when needed. The waiting event statistics table contains the following tables:

Click (here) to collapse or open

Admin@localhost: performance_schema 06:17:11 > show tables like'% events_waits_summary%'

+-- +

| | Tables_in_performance_schema (% events_waits_summary%) |

+-- +

| | events_waits_summary_by_account_by_event_name |

| | events_waits_summary_by_host_by_event_name |

| | events_waits_summary_by_instance |

| | events_waits_summary_by_thread_by_event_name |

| | events_waits_summary_by_user_by_event_name |

| | events_waits_summary_global_by_event_name |

+-- +

6 rows in set (0.00 sec)

Let's take a look at what the statistics recorded in these tables look like.

Click (here) to collapse or open

# events_waits_summary_by_account_by_event_ name table

Root@localhost: performance_schema 11:07:09 > select * from events_waits_summary_by_account_by_event_name limit 1\ G

* * 1. Row *

USER: NULL

HOST: NULL

EVENT_NAME: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_waits_summary_by_host_by_event_ name table

Root@localhost: performance_schema 11:07:14 > select * from events_waits_summary_by_host_by_event_name limit 1\ G

* * 1. Row *

HOST: NULL

EVENT_NAME: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_waits_summary_by_ instance table

Root@localhost: performance_schema 11:08:05 > select * from events_waits_summary_by_instance limit 1\ G

* * 1. Row *

EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK_heap

OBJECT_INSTANCE_BEGIN: 32492032

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_waits_summary_by_thread_by_event_ name table

Root@localhost: performance_schema 11:08:23 > select * from events_waits_summary_by_thread_by_event_name limit 1\ G

* * 1. Row *

THREAD_ID: 1

EVENT_NAME: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_waits_summary_by_user_by_event_ name table

Root@localhost: performance_schema 11:08:36 > select * from events_waits_summary_by_user_by_event_name limit 1\ G

* * 1. Row *

USER: NULL

EVENT_NAME: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_waits_summary_global_by_event_ name table

Root@localhost: performance_schema 11:08:53 > select * from events_waits_summary_global_by_event_name limit 1\ G

* * 1. Row *

EVENT_NAME: wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

From the sample record information in the table above, we can see that:

Each table has its own grouped column or columns to determine how event information is aggregated (all tables have EVENT_NAME columns whose values correspond to NAME column values in the setup_ accounts table), as follows:

Events_waits_summary_by_account_by_event_ name table: grouping event information by columns EVENT_NAME, USER, HOST

Events_waits_summary_by_host_by_event_ name table: grouping event information by columns EVENT_NAME and HOST

Events_waits_summary_by_ instance table: group event information by columns EVENT_NAME and OBJECT_INSTANCE_BEGIN. If an instruments (event_name) is created with multiple instances, each instance has a unique OBJECT_INSTANCE_ begin value, so each instance is grouped separately

Events_waits_summary_by_thread_by_event_ name table: grouping event information by columns THREAD_ID and EVENT_NAME

Events_waits_summary_by_user_by_event_ name table: grouping event information by columns EVENT_NAME and USER

Events_waits_summary_global_by_event_ name table: grouping event information by EVENT_NAME column

The statistical columns (numeric) of all tables are as follows:

COUNT_STAR: the number of events executed. This value includes the number of times all events are executed, and instruments waiting for events needs to be enabled.

SUM_TIMER_WAIT: counts the total wait time for a given timing event. This value is only for event instruments with timing feature or instruments with timing feature enabled. If the instruments of an event does not support timing or does not enable timing feature, this field is NULL. Other xxx_TIMER_WAIT field values are similar

MIN_TIMER_WAIT: the minimum waiting time for a given timing event

AVG_TIMER_WAIT: the average wait time for a given timing event

MAX_TIMER_WAIT: the maximum waiting time for a given timing event

PS: wait event statistics allow the use of TRUNCATE TABLE statements.

The following behavior occurs when the statement is executed:

For statistical tables that are not aggregated by account, host, and user, the truncate statement resets the statistical column values to zero instead of deleting rows.

For statistical tables aggregated by account, host, and user, the truncate statement deletes the corresponding rows of accounts, hosts, or users that have started the connection, and resets the statistical column values of other connected rows to zero (the actual measurement is the same as the statistical tables not aggregated by account, host, and user, which will only be reset and not deleted).

In addition, according to each wait event statistics table or events_waits_summary_global_by_event_name table aggregated by account, host, user, thread, if the dependent join tables (accounts, hosts, users tables) execute truncate, then the statistics in these tables will also be implicitly truncate.

Note: these tables only count the wait event information, that is, the collector + idle idle collector that contains the beginning of wait/% in the setup_ events table. The number of rows of statistical records of each wait event in each table depends on how to group (for example, the number of active users in the table according to user grouping, the number of records of the same collector in the table). In addition, Whether the statistical counter is in effect depends on whether the corresponding wait event collector is enabled in the setup_ events table.

| | Statistical table of phase events |

Performance_schema classifies and aggregates the statistical tables of phase events according to rules similar to those of waiting events. Some phase events are disabled by default and some are enabled. The statistical table of phase events contains the following tables:

Click (here) to collapse or open

Admin@localhost: performance_schema 06:23:02 > show tables like'% events_stages_summary%'

+-- +

| | Tables_in_performance_schema (% events_stages_summary%) |

+-- +

| | events_stages_summary_by_account_by_event_name |

| | events_stages_summary_by_host_by_event_name |

| | events_stages_summary_by_thread_by_event_name |

| | events_stages_summary_by_user_by_event_name |

| | events_stages_summary_global_by_event_name |

+-- +

5 rows in set (0.00 sec)

Let's take a look at what the statistics recorded in these tables look like.

Click (here) to collapse or open

# events_stages_summary_by_account_by_event_ name table

Root@localhost: performance_schema 11:21:04 > select * from events_stages_summary_by_account_by_event_name where USER is not null limit 1\ G

* * 1. Row *

USER: root

HOST: localhost

EVENT_NAME: stage/sql/After create

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.01 sec)

# events_stages_summary_by_host_by_event_ name table

Root@localhost: performance_schema 11:29:27 > select * from events_stages_summary_by_host_by_event_name where HOST is not null limit 1\ G

* * 1. Row *

HOST: localhost

EVENT_NAME: stage/sql/After create

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_stages_summary_by_thread_by_event_ name table

Root@localhost: performance_schema 11:37:03 > select * from events_stages_summary_by_thread_by_event_name where thread_id is not null limit 1\ G

* * 1. Row *

THREAD_ID: 1

EVENT_NAME: stage/sql/After create

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.01 sec)

# events_stages_summary_by_user_by_event_ name table

Root@localhost: performance_schema 11:42:37 > select * from events_stages_summary_by_user_by_event_name where user is not null limit 1\ G

* * 1. Row *

USER: root

EVENT_NAME: stage/sql/After create

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

# events_stages_summary_global_by_event_ name table

Root@localhost: performance_schema 11:43:03 > select * from events_stages_summary_global_by_event_name limit 1\ G

* * 1. Row *

EVENT_NAME: stage/sql/After create

COUNT_STAR: 0

SUM_TIMER_WAIT: 0

MIN_TIMER_WAIT: 0

AVG_TIMER_WAIT: 0

MAX_TIMER_WAIT: 0

1 row in set (0.00 sec)

From the example record information in the table above, we can see that the columns that are also similar to waiting events are grouped and counted according to the latitudes of users, hosts, users + hosts, threads, and so on. The meaning of these columns is similar to that of waiting events. I won't repeat it here.

Note: these tables only count phase event information, that is, collectors that include the beginning of stage/% in the setup_ events table. The number of statistical records of each phase event in each table depends on how to group (for example, the number of active users in the table according to user grouping, the number of records of the same collector in the table). In addition, Whether the statistical counter is in effect depends on whether the corresponding phase event collector is enabled in the setup_ events table.

PS: use the truncate statement on these tables to have a similar effect as wait events.

| | Statistical table of transaction events |

Performance_schema classifies the transaction event statistical table according to the rules similar to the waiting event statistical table. Transaction event instruments has only one transaction, which is disabled by default. The transaction event statistical table has the following tables:

Click (here) to collapse or open

Admin@localhost: performance_schema 06:37:45 > show tables like'% events_transactions_summary%'

+-- +

| | Tables_in_performance_schema (% events_transactions_summary%) |

+-- +

| | events_transactions_summary_by_account_by_event_name |

| | events_transactions_summary_by_host_by_event_name |

| | events_transactions_summary_by_thread_by_event_name |

| | events_transactions_summary_by_user_by_event_name |

| | events_transactions_summary_global_by_event_name |

+-- +

5 rows in set (0.00 sec)

Let's first take a look at what the statistics recorded in these tables look like (because the single-row record is long, only the sample data in the events_transactions_summary_by_account_by_event_ name table is listed here, and some of the same fields are omitted from the sample data in the rest of the tables).

Click (here) to collapse or open

# events_transactions_summary_by_account_by_event_ name table

Root@localhost: performance_schema 01:19:07 > select * from events_transactions_summary_by_account_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

USER: root

HOST: localhost

EVENT_NAME: transaction

COUNT_STAR: 7

SUM_TIMER_WAIT: 8649707000

MIN_TIMER_WAIT: 57571000

AVG_TIMER_WAIT: 1235672000

MAX_TIMER_WAIT: 2427645000

COUNT_READ_WRITE: 6

SUM_TIMER_READ_WRITE: 8592136000

MIN_TIMER_READ_WRITE: 87193000

AVG_TIMER_READ_WRITE: 1432022000

MAX_TIMER_READ_WRITE: 2427645000

COUNT_READ_ONLY: 1

SUM_TIMER_READ_ONLY: 57571000

MIN_TIMER_READ_ONLY: 57571000

AVG_TIMER_READ_ONLY: 57571000

MAX_TIMER_READ_ONLY: 57571000

1 row in set (0.00 sec)

# events_transactions_summary_by_host_by_event_ name table

Root@localhost: performance_schema 01:25:13 > select * from events_transactions_summary_by_host_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

HOST: localhost

EVENT_NAME: transaction

COUNT_STAR: 7

.

1 row in set (0.00 sec)

# events_transactions_summary_by_thread_by_event_ name table

Root@localhost: performance_schema 01:25:27 > select * from events_transactions_summary_by_thread_by_event_name where summary timestamp WAITY 0\ G

* * 1. Row *

THREAD_ID: 46

EVENT_NAME: transaction

COUNT_STAR: 7

.

1 row in set (0.00 sec)

# events_transactions_summary_by_user_by_event_ name table

Root@localhost: performance_schema 01:27:27 > select * from events_transactions_summary_by_user_by_event_name where summary timestamp WAITY 0\ G

* * 1. Row *

USER: root

EVENT_NAME: transaction

COUNT_STAR: 7

.

1 row in set (0.00 sec)

# events_transactions_summary_global_by_event_ name table

Root@localhost: performance_schema 01:27:32 > select * from events_transactions_summary_global_by_event_name where summary timestamp WAITY 0\ G

* * 1. Row *

EVENT_NAME: transaction

COUNT_STAR: 7

.

1 row in set (0.00 sec)

From the sample record information in the table above, we can see that the columns that are grouped and counted by user, host, user + host, thread and other latitudes are also similar to waiting events. The meaning of these columns is similar to waiting events. I will not repeat them here, but for transaction statistics events, separate statistics are done for read-write transactions and read-only transactions (xx_READ_WRITE and xx_READ_ONLY columns). Read-only transactions need to be set with the read-only transaction variable transaction_read_only=on to be counted.

Note: these tables only count the transaction event information, that is, it contains and only contains the transaction collector in the setup_ events table. The number of rows of statistical records of each transaction event in each table depends on how to group (for example, the number of active users in the table according to user grouping, the number of records of the same collector in the table). In addition, whether the statistical counter takes effect depends on whether the transaction collector is enabled.

Statistical rules for transaction aggregation

* Collection of transaction events does not take into account isolation level, access mode or autocommit mode

* read-write transactions usually take up more resources than read-only transactions, so transaction statistics contain separate statistical columns for read-write and read-only transactions

* the resource requirements occupied by the transaction may also vary depending on the transaction isolation level (for example, lock resources). However: each server may use the same isolation level, so statistical columns related to the isolation level are not provided separately

PS: use the truncate statement on these tables to have a similar effect as wait events.

| | statement event statistics table |

Performance_schema classifies the statement event statistics according to similar rules as the waiting event statistics table, and the statement event instruments is enabled by default. Therefore, all statement event statistics are recorded by default in the statement event statistics table, which contains the following tables:

Events_statements_summary_by_account_by_event_name: statistics based on each account and statement event name

Events_statements_summary_by_digest: statistics are carried out according to the original statement text statistics (md5 hash string) of each library-level object and statement event, which is refined based on the original statement text of the event (the original statement is converted into a standardized statement). The relevant numerical fields in each row of data are statistical results with the same statistical value.

Events_statements_summary_by_host_by_event_name: Statement events counted by each hostname and event name

Events_statements_summary_by_program: Statement events that are counted by the event name of each stored program (stored procedures and functions, triggers and events)

Events_statements_summary_by_thread_by_event_name: Statement events counted by each thread and event name

Events_statements_summary_by_user_by_event_name: Statement events counted by each user name and event name

Events_statements_summary_global_by_event_name: Statement events counted by each event name

Prepared_statements_instances: statistics aggregated by each instance of prepare statement

You can view the statement event statistics table with the following statement:

Click (here) to collapse or open

Admin@localhost: performance_schema 06:27:58 > show tables like'% events_statements_summary%'

+-- +

| | Tables_in_performance_schema (% events_statements_summary%) |

+-- +

| | events_statements_summary_by_account_by_event_name |

| | events_statements_summary_by_digest |

| | events_statements_summary_by_host_by_event_name |

| | events_statements_summary_by_program |

| | events_statements_summary_by_thread_by_event_name |

| | events_statements_summary_by_user_by_event_name |

| | events_statements_summary_global_by_event_name |

+-- +

7 rows in set (0.00 sec)

Admin@localhost: performance_schema 06:28:48 > show tables like'% prepare%'

+-+

| | Tables_in_performance_schema (% prepare%) |

+-+

| | prepared_statements_instances |

+-+

1 row in set (0.00 sec)

Let's first take a look at what the statistics recorded in these tables look like (because the single-row records are long, only the sample data in the events_statements_summary_by_account_by_event_name table is listed here, and some of the same fields are omitted from the sample data in the rest of the tables).

Click (here) to collapse or open

# events_statements_summary_by_account_by_event_ name table

Root@localhost: performance_schema 10:37:27 > select * from events_statements_summary_by_account_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

USER: root

HOST: localhost

EVENT_NAME: statement/sql/select

COUNT_STAR: 53

SUM_TIMER_WAIT: 234614735000

MIN_TIMER_WAIT: 72775000

AVG_TIMER_WAIT: 4426693000

MAX_TIMER_WAIT: 80968744000

SUM_LOCK_TIME: 26026000000

SUM_ERRORS: 2

SUM_WARNINGS: 0

SUM_ROWS_AFFECTED: 0

SUM_ROWS_SENT: 1635

SUM_ROWS_EXAMINED: 39718

SUM_CREATED_TMP_DISK_TABLES: 3

SUM_CREATED_TMP_TABLES: 10

SUM_SELECT_FULL_JOIN: 21

SUM_SELECT_FULL_RANGE_JOIN: 0

SUM_SELECT_RANGE: 0

SUM_SELECT_RANGE_CHECK: 0

SUM_SELECT_SCAN: 45

SUM_SORT_MERGE_PASSES: 0

SUM_SORT_RANGE: 0

SUM_SORT_ROWS: 170

SUM_SORT_SCAN: 6

SUM_NO_INDEX_USED: 42

SUM_NO_GOOD_INDEX_USED: 0

1 row in set (0.00 sec)

# events_statements_summary_by_ digest table

Root@localhost: performance_schema 11:01:51 > select * from events_statements_summary_by_digest limit 1\ G

* * 1. Row *

SCHEMA_NAME: NULL

DIGEST: 4fb483fe710f27d1d06f83573c5ce11c

DIGEST_TEXT: SELECT @ @ `version _ comment`LIMIT?

COUNT_STAR: 3

.

FIRST_SEEN: 2018-05-19 22:33:50

LAST_SEEN: 2018-05-20 10:24:42

1 row in set (0.00 sec)

# events_statements_summary_by_host_by_event_ name table

Root@localhost: performance_schema 11:02:15 > select * from events_statements_summary_by_host_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

HOST: localhost

EVENT_NAME: statement/sql/select

COUNT_STAR: 55

.

1 row in set (0.00 sec)

# events_statements_summary_by_ programmer table (data needs to be called after a stored procedure or function is called)

Root@localhost: performance_schema 12:34:43 > select * from events_statements_summary_by_program\ G

* * 1. Row *

OBJECT_TYPE: PROCEDURE

OBJECT_SCHEMA: sys

OBJECT_NAME: ps_setup_enable_consumer

COUNT_STAR: 1

.

1 row in set (0.00 sec)

# events_statements_summary_by_thread_by_event_ name table

Root@localhost: performance_schema 11:03:19 > select * from events_statements_summary_by_thread_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

THREAD_ID: 47

EVENT_NAME: statement/sql/select

COUNT_STAR: 11

.

1 row in set (0.01 sec)

# events_statements_summary_by_user_by_event_ name table

Root@localhost: performance_schema 11:04:10 > select * from events_statements_summary_by_user_by_event_name where counting starters 0 limit 1\ G

* * 1. Row *

USER: root

EVENT_NAME: statement/sql/select

COUNT_STAR: 58

.

1 row in set (0.00 sec)

# events_statements_summary_global_by_event_ name table

Root@localhost: performance_schema 11:04:31 > select * from events_statements_summary_global_by_event_name limit 1\ G

* * 1. Row *

EVENT_NAME: statement/sql/select

COUNT_STAR: 59

.

1 row in set (0.00 sec)

From the example record information in the table above, we can see that it is also similar to waiting events, according to user, host, user + host, thread, etc.

Latitude grouping and statistical columns, grouping and partial time statistical columns are similar to waiting events, which are not discussed here, but for statement statistical events

There are additional statistical columns for statement objects, as follows:

SUM_xxx: statistics are made for the corresponding xxx columns in the events_statements_* event record table. For example: statement statistics

SUM_LOCK_TIME and SUM_ERRORS columns for LOCK_TIME and ERRORS columns in the events_statements_current event record table

Statistics

The events_statements_summary_by_digest table has its own additional statistical columns:

* FIRST_SEEN,LAST_SEEN: displays the timestamp of the first time a given statement is inserted into the events_statements_summary_by_digest table and the last time the table is updated

The events_statements_summary_by_program table has its own additional statistical columns:

* COUNT_STATEMENTS,SUM_STATEMENTS_WAIT,MIN_STATEMENTS_WAIT,AVG_STATEMENTS_WAIT,MAX_STATEMENTS_WAIT: statistics on nested statements called during the execution of the stored program

The prepared_statements_instances table has its own additional statistical columns:

* COUNT_EXECUTE,SUM_TIMER_EXECUTE,MIN_TIMER_EXECUTE,AVG_TIMER_EXECUTE,MAX_TIMER_EXECUTE: statistics of objects executing prepare statements

PS1:

About the events_statements_summary_by_ digest table

If statements_digest consumers is enabled in the setup_consumers configuration table, the statement text will be md5 hash calculated and then sent to the events_statements_summary_by_ digest table when the statement execution is complete. The grouping column is based on the DIGEST column value (md5 hash value) of the statement

* if the statistics row of a given statement already exists in the events_statements_summary_by_ digest table, update the statistics of the statement and update the LAST_SEEN column value to the current time

* if the statistics row of the given statement does not have an existing row in the events_statements_summary_by_digest table, and the events_statements_summary_by_digest table space limit is not full, a new row of statistics will be inserted in the events_statements_summary_by_ digest table, and both the FIRST_SEEN and LAST_SEEN columns use the current time

* if the statistics row of a given statement does not have an existing row in the events_statements_summary_by_digest table, and the events_statements_summary_by_digest table space limit is full, the statistics of the statement will be added to the special "catch-all" row with the value of the DIGEST column NULL, and if the special row does not exist, a new row will be inserted, with FIRST_SEEN and LAST_SEEN listed as the current time. Update the information of the special row if it already exists, LAST_SEEN is the current time

Due to the memory limitations of the performance_schema table, the special row of DIGEST = NULL is maintained. When the limit capacity of the events_statements_summary_by_digest table is full, and when the new statement statistics need to be inserted into the table and no matching DIGEST column values are found in the table, these statement statistics will be counted into the row of DIGEST = NULL. This row will help you estimate whether the limits of the events_statements_summary_by_digest table need to be adjusted

* if the COUNT_STAR column value of the DIGEST = NULL row accounts for more than 0% of the COUNT_STAR column value of all statistics in the whole table, it means that some statement statistics cannot be classified and saved because the table limit is full. If you need to save the statistics of all statements, you can adjust the value of the system variable performance_schema_digests_size before server starts. The default size is 200.

PS2: about stored program monitoring behavior: for stored program types with instruments enabled in the setup_objects table, events_statements_summary_by_program maintains stored program statistics as follows:

When a given object is first used in server (that is, when a stored procedure or custom stored function is called using the call statement), a row of statistics is added to the events_statements_summary_by_program table

When a given object is deleted, the statistics row of that object in the events_statements_summary_by_ program table will be deleted

When a given object is executed, its corresponding statistics are recorded in the events_statements_summary_by_ program table and counted.

PS3: use the truncate statement on these tables to have a similar effect as wait events.

| | memory event statistics table |

Performance_schema classifies the memory event statistics according to the rules similar to the waiting event statistics.

Performance_schema records memory usage and aggregates memory usage statistics, such as memory types used (various caches, internal buffers, etc.) and memory operations performed indirectly by threads, accounts, users, and hosts. Performance_schema depends on the amount of memory used, the number of related operations, and the high and low water levels (the maximum and minimum relevant statistics for a single operation of memory).

Memory size statistics help you understand the current memory consumption of server so that memory adjustments can be made in a timely manner. The counting of memory-related operations helps to understand the overall pressure of the current server memory allocator and grasp server performance data in a timely manner. For example, the performance overhead of allocating a single byte a million times is different from allocating a million bytes at a time, and you can know the difference by tracking the amount of memory allocated by the memory allocator and the number of times allocated.

It is critical to detect peak memory workloads, overall workload stability of memory, possible memory leaks, and so on.

In the memory event instruments, except for the event instruments configuration related to the memory allocation of performance_schema itself, all other memory event instruments configurations are turned off by default, and there are no separate configuration items in the setup_consumers table, such as wait events, phase events, statement events and transaction events.

PS: memory statistics do not contain timing information because memory events do not support time information collection.

The memory event statistics table has the following tables:

Click (here) to collapse or open

Admin@localhost: performance_schema 06:56:56 > show tables like'% memory%summary%'

+-- +

| | Tables_in_performance_schema (% memory%summary%) |

+-- +

| | memory_summary_by_account_by_event_name |

| | memory_summary_by_host_by_event_name |

| | memory_summary_by_thread_by_event_name |

| | memory_summary_by_user_by_event_name |

| | memory_summary_global_by_event_name |

+-- +

5 rows in set (0.00 sec)

Let's first take a look at what the statistics recorded in these tables look like (because the single-row records are long, only the sample data in the memory_summary_by_account_by_event_name table is listed here, and some of the same fields are omitted from the sample data in the rest of the tables).

Click (here) to collapse or open

# if you need to count memory event information, you need to turn on the memory event collector

Root@localhost: performance_schema 11:50:46 > update setup_instruments set enabled='yes',timed='yes' where name like 'memory/%'

Query OK, 377 rows affected (0.00 sec)

Rows matched: 377 Changed: 377 Warnings: 0

# memory_summary_by_account_by_event_ name table

Root@localhost: performance_schema 11:53:24 > select * from memory_summary_by_account_by_event_name where COUNTIX ALLOCING 0 limit 1\ G

* * 1. Row *

USER: NULL

HOST: NULL

EVENT_NAME: memory/innodb/fil0fil

COUNT_ALLOC: 103

COUNT_FREE: 103

SUM_NUMBER_OF_BYTES_ALLOC: 3296

SUM_NUMBER_OF_BYTES_FREE: 3296

LOW_COUNT_USED: 0

CURRENT_COUNT_USED: 0

HIGH_COUNT_USED: 1

LOW_NUMBER_OF_BYTES_USED: 0

CURRENT_NUMBER_OF_BYTES_USED: 0

HIGH_NUMBER_OF_BYTES_USED: 32

1 row in set (0.00 sec)

# memory_summary_by_host_by_event_ name table

Root@localhost: performance_schema 11:54:36 > select * from memory_summary_by_host_by_event_name where COUNTIX ALLOCING 0 limit 1\ G

* * 1. Row *

HOST: NULL

EVENT_NAME: memory/innodb/fil0fil

COUNT_ALLOC: 158

.

1 row in set (0.00 sec)

# memory_summary_by_thread_by_event_ name table

Root@localhost: performance_schema 11:55:11 > select * from memory_summary_by_thread_by_event_name where COUNTIX ALLOCING 0 limit 1\ G

* * 1. Row *

THREAD_ID: 37

EVENT_NAME: memory/innodb/fil0fil

COUNT_ALLOC: 193

.

1 row in set (0.00 sec)

# memory_summary_by_user_by_event_ name table

Root@localhost: performance_schema 11:55:36 > select * from memory_summary_by_user_by_event_name where COUNTIX ALLOCING 0 limit 1\ G

* * 1. Row *

USER: NULL

EVENT_NAME: memory/innodb/fil0fil

COUNT_ALLOC: 216

.

1 row in set (0.00 sec)

# memory_summary_global_by_event_ name table

Root@localhost: performance_schema 11:56:02 > select * from memory_summary_global_by_event_name where COUNTIX ALLOCING 0 limit 1\ G

* * 1. Row *

EVENT_NAME: memory/performance_schema/mutex_instances

COUNT_ALLOC: 1

.

1 row in set (0.00 sec)

From the sample record information in the table above, we can see that the columns that are grouped and counted according to the latitudes of users, hosts, users + hosts, threads, etc., are also similar to waiting events, and the grouping columns are similar to waiting events. I will not repeat it here, but for memory statistical events, statistical columns are different from other event statistical columns (because memory events do not count time overhead. So there is no same statistical column compared with other event types), as follows:

Each memory statistics table has the following columns:

* COUNT_ALLOC,COUNT_FREE: total number of calls to memory allocation and freeing memory functions

* SUM_NUMBER_OF_BYTES_ALLOC,SUM_NUMBER_OF_BYTES_FREE: total byte size of allocated and freed memory blocks

* CURRENT_COUNT_USED: this is a convenient column, equal to COUNT_ALLOC-COUNT_FREE

* CURRENT_NUMBER_OF_BYTES_USED: the statistical size of the currently allocated memory block but not released. This is a convenient column, equal to SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE

* LOW_COUNT_USED,HIGH_COUNT_USED: the low and high water mark corresponding to the CURRENT_COUNT_USED column

* LOW_NUMBER_OF_BYTES_USED,HIGH_NUMBER_OF_BYTES_USED: the low and high water mark corresponding to the CURRENT_NUMBER_OF_BYTES_USED column

Memory statistics allow the use of TRUNCATE TABLE statements. The following behavior occurs when using the truncate statement:

* in general, the truncate operation resets the baseline data of the statistics (that is, the data before emptying), but does not modify the memory allocation and other states of the current server. That is, the truncate memory statistics table does not release allocated memory

* reset the COUNT_ALLOC and COUNT_FREE columns and restart the count (equal to memory statistics using the reset value as the benchmark data)

* SUM_NUMBER_OF_BYTES_ALLOC and SUM_NUMBER_OF_BYTES_FREE column resets are similar to COUNT_ALLOC and COUNT_FREE column resets

* LOW_COUNT_USED and HIGH_COUNT_USED will be reset to CURRENT_COUNT_USED column values

* LOW_NUMBER_OF_BYTES_USED and HIGH_NUMBER_OF_BYTES_USED will be reset to CURRENT_NUMBER_OF_BYTES_USED column values

* in addition, memory statistical tables or memory_summary_global_by_event_name tables classified by account, host, user or thread will implicitly execute truncate statements on these memory statistical tables when truncate is executed on the dependent accounts, hosts, and users tables

Behavior monitoring settings and precautions for memory events

Memory behavior monitoring settings:

* memory instruments has a name in memory/code_area/instrument_name format in the setup_ accounts table. But most instruments is disabled by default, and only instruments at the beginning of memory/performance_schema/* is enabled by default

* instruments named after the prefix memory/performance_schema can collect information such as the size of the internal cache consumed by the performance_schema itself. Memory/performance_schema/* instruments is enabled by default and cannot be turned off at startup or run time. Performance_schema 's own memory statistics are saved only in the memory_summary_global_by_event_ name table, not in memory statistics tables aggregated by account, host, user, or thread.

* invalid TIMED column in the memory instruments,setup_ accounts table because time statistics are not supported for memory operations

* Note: if you modify memory instruments after server starts, it may result in negative memory statistics after releasing due to the loss of previous allocation operation data, so it is not recommended to switch memory instruments repeatedly at run time. If memory event statistics are needed, it is recommended to configure event collection to be counted in my.cnf before server startup.

When a thread in server performs a memory allocation operation, it detects and aggregates according to the following rules:

* if the thread does not enable the collection function in the threads table or the corresponding instruments in setup_instruments, the memory block allocated by the thread will not be monitored

* if both the collection function of the thread in the threads table and the corresponding memory instruments in the setup_ accounts table are enabled, the memory blocks allocated by the thread will be monitored

For the release of memory blocks, detect and aggregate according to the following rules:

* if a thread enables capture, but memory-related instruments is not enabled, the memory release operation will not be monitored and the statistics will not change.

* if a thread does not enable the collection function, but the memory-related instruments is enabled, the memory release operation will be monitored and the statistics will change, which is why repeatedly modifying memory instruments at run time may cause the statistics to be negative.

For statistics per thread, the following rules apply.

When a memory block N that can be monitored is allocated, performance_schema updates the memory statistics as follows:

* COUNT_ALLOC: add 1

* CURRENT_COUNT_USED: add 1

* HIGH_COUNT_USED: if CURRENT_COUNT_USED plus 1 is a new maximum, the field value increases accordingly

* SUM_NUMBER_OF_BYTES_ALLOC: add N

* CURRENT_NUMBER_OF_BYTES_USED: add N

* HIGH_NUMBER_OF_BYTES_USED: if the CURRENT_NUMBER_OF_BYTES_USED increases N to a new maximum value, the field value increases accordingly

When a memory block N that can be monitored is released, performance_schema updates the statistics as follows:

* COUNT_FREE: add 1

* CURRENT_COUNT_USED: minus 1

* LOW_COUNT_USED: if CURRENT_COUNT_USED minus 1 is followed by a new minimum, this field decreases accordingly

* SUM_NUMBER_OF_BYTES_FREE: add N

* CURRENT_NUMBER_OF_BYTES_USED: reduce N

* LOW_NUMBER_OF_BYTES_USED: if the CURRENT_NUMBER_OF_BYTES_USED minus N is followed by a new minimum value, this field decreases accordingly

For higher-level aggregations (global, by account, by user, by host) statistics, low and high water levels apply to the following rules:

* LOW_COUNT_USED and LOW_NUMBER_OF_BYTES_USED are lower estimates of low water level. The low water level value output by performance_schema ensures that the memory allocation times and memory in the statistical table are less than or equal to the real memory allocation value in the current server.

* HIGH_COUNT_USED and HIGH_NUMBER_OF_BYTES_USED are higher estimates of high water level. The low water level value output by performance_schema ensures that the memory allocation times and memory in the statistical table are greater than or equal to the real memory allocation value in the current server.

For the low water estimate in the memory statistics table, if memory ownership is transferred between threads in the memory_summary_global_by_event_name table, the estimate may be negative

| | warm reminder |

The data entries in the performance event statistics table cannot be deleted, and the corresponding statistical fields can only be cleared.

Whether an instruments in the performance event statistics table performs statistics depends on whether the configuration item in the setup_ events table is enabled.

The performance event statistical table is only controlled by the "global_instrumentation" configuration item in the setup_consumers table, that is, once the "global_instrumentation" configuration item is turned off, all statistical entries of the statistical table do not perform statistics (the statistical column value is 0).

Memory events do not have a separate configuration item in the setup_consumers table, and memory/performance_schema/* instruments is enabled by default and cannot be turned off at startup or run time. Performance_schema-related memory statistics are saved only in the memory_summary_global_by_event_ name table, not in memory statistics aggregated by account, host, user, or thread.

| | author profile |

IT, a senior database technology expert in Luo Po Walk Technology, has worked for many years, and has served as an operation and maintenance engineer, senior operation and maintenance engineer, operation and maintenance manager, and database engineer. He has participated in the design and preparation of version release system, lightweight monitoring system, operation and maintenance management platform and database management platform. He is familiar with MySQL architecture and Innodb storage engine, and likes to specialize in open source technology and pursue 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.