MySQL8.0 setup_actors execution time statistics

The resource usage statistics of each sql statement in the database is often the most troublesome part and the most effective way to locate the problem. In addition, the resource usage can help the DBA to confirm whether the business concurrency increases, whether the hardware resources meet the existing requirements, and whether the parameters need to be adjusted.
At present, the mechanism provided by MySQL is not as convenient and careful as oracle's AWR report, but MySQL also provides a monolingual PROFILE command line, which provides great help in troubleshooting.

At present, with the popularity of MySQL 8.0, the PROFILE statement in the official introduction is ready to be discarded and performance is used_ schema.setup_ Actors override.

PROFILE statementdisplays the resource usage of statements executed during the current session. Provide the following information

indexexplain
ALLDisplay all performance information
BLOCK IODisplays the number of block IO operations
CONTEXT SWITCHESDisplays the number of context switches, active or passive
CPUDisplay user CPU time and system CPU time
IPCDisplays the number of messages sent and received
MEMORY[not currently implemented]
PAGE FAULTSDisplays the number of page errors
SOURCEDisplays the function name and location in the source code
SWAPSDisplays the number of SWAP

In fact, in use, thread level and human operation control are really troublesome. Sometimes the performance of collecting information is also squeezed.

Then learn about setup_ How do actors provide resource utilization statistics.


setup_actors

From the official introduction to performance_ Setup under schema_ The actors table can be used to limit the collection of historical events by host, user, or account to reduce runtime overhead and the amount of data collected in the history table. That is, the resource usage of each SQL is automatically counted through user level settings. This is more complete than the original PROFILE.

By default, setup_actors are configured to allow monitoring and historical event collection of all foreground threads: however, relevant information cannot be collected. But you need to open collector instruments.

1) User settings

mysql> SELECT * FROM performance_schema.setup_actors;
+------+------+------+---------+---------+
| HOST | USER | ROLE | ENABLED | HISTORY |
+------+------+------+---------+---------+
| %    | %    | %    | YES     | YES     |
+------+------+------+---------+---------+
1 row in set (0.00 sec)

mysql> UPDATE performance_schema.setup_actors
       SET ENABLED = 'NO', HISTORY = 'NO'
       WHERE HOST = '%' AND USER = '%';

mysql> INSERT INTO performance_schema.setup_actors
       (HOST,USER,ROLE,ENABLED,HISTORY)
       VALUES('localhost','root','%','YES','YES');

mysql> SELECT * FROM performance_schema.setup_actors;
+-----------+------+------+---------+---------+
| HOST      | USER | ROLE | ENABLED | HISTORY |
+-----------+------+------+---------+---------+
| %         | %    | %    | NO      | NO      |
| localhost | root | %    | YES     | YES     |
+-----------+------+------+---------+---------+
2 rows in set (0.00 sec)

2)setup_instruments on
instruments by default, 780 indicators such as wait, stage sql statements and memory have been enabled, that is, processlist, InnoDB, Stu and other information monitoring for daily use.

By updating setup_ The instruments table to ensure that it is enabled. Some indicators may have been enabled by default. The scope is too wide. If there is no detailed corresponding relationship, all are opened.

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%statement/%';

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';
mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_statements_%';

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';
##sql time ID after query execution
mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%t1%';
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EVENT_ID | Duration | SQL_TEXT                                                                                                                                                                                                                        |
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       48 |   0.0065 | select *from db1.t1                                                                                                                                                                                                             |
|       84 |   0.0004 | select * from db1.t1                                                                                                                                                                                                            |
|       41 |   0.0082 | SELECT /*!40001 SQL_NO_CACHE */ * FROM `test1`                                                              |
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

#It is only time-consuming to query through the event ID.
mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=41;

Note: Events_ stages_ history_ The long table contains N recent phase events that end globally across all threads. Stage activities are not added to the table until they are finished. When the table is full and a new row is added, the oldest row is discarded, regardless of which thread generated the row. events_ stages_ history_ The long table allows rows to be deleted using TRUNCATE TABLE mode

fieldexplain
THREAD_ID, EVENT_IDThread ID and event ID at start
END_EVENT_IDThe column is set to NULL at the beginning of the event and updated to the thread's current event number at the end of the event
EVENT_NAMEThe NAME of the instrument that generated the event. This is setup_ NAME value in the instruments table
SOURCEThe name of the source file that contains the instrumented code that generated the event and the line number in the file where the instrumentation occurred
TIMER_START,TIMER_END, TIMER_WAITIMER_START and timer_ The end value indicates the start and end time of the event timing. TIMER_WAIT is the elapsed time (duration) of the event. Picosecond (trillionth of a second)
WORK_COMPLETED, WORK_ESTIMATEDWORK_COMPLETED indicates how many work units have been completed in this stage_ Estimated indicates how many units of work are expected at this stage
NESTING_EVENT_IDThe event of the nested EVENT_ID value. Nested events of events are usually statement events.
NESTING_EVENT_TYPENested event types. The values are TRANSACTION, state, STAGE or WAIT.

In addition, events_ stages_ history_ The long table is affected by parameters and can only record a limited number of SQL statements. By default, there are 1000 SQL statements

 

mysql> show variables like '%events_stages_history_long%'; +----------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------+-------+ | performance_schema_events_stages_history_long_size | 10000 | +----------------------------------------------------+-------+


summary

At present, the functions provided are very limited. It is estimated that this function should be honed for another 2 years.

  • No information except time consuming.
  • instruments is turned on and off, unable to reset and restore the initial value.
  • The impact range and the performance consumption of instruments are too high to be evaluated.
    Although the profile comparison is incomplete, it can also be used in practice in the 8.0 environment.

Tags: Database MySQL DBA

Posted on Wed, 10 Nov 2021 20:32:21 -0500 by evanesq