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
| index | explain |
|---|---|
| ALL | Display all performance information |
| BLOCK IO | Displays the number of block IO operations |
| CONTEXT SWITCHES | Displays the number of context switches, active or passive |
| CPU | Display user CPU time and system CPU time |
| IPC | Displays the number of messages sent and received |
| MEMORY | [not currently implemented] |
| PAGE FAULTS | Displays the number of page errors |
| SOURCE | Displays the function name and location in the source code |
| SWAPS | Displays 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
| field | explain |
|---|---|
| THREAD_ID, EVENT_ID | Thread ID and event ID at start |
| END_EVENT_ID | The 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_NAME | The NAME of the instrument that generated the event. This is setup_ NAME value in the instruments table |
| SOURCE | The 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_WAI | TIMER_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_ESTIMATED | WORK_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_ID | The event of the nested EVENT_ID value. Nested events of events are usually statement events. |
| NESTING_EVENT_TYPE | Nested 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.