MySQL optimization mainly starts from the core layer of its logical architecture – analysis, optimization and execution stages.
During the experiment, I used MySQL version 5.7. For quick installation of MySQL, please refer to how to quickly install MySQL5.7 in CentOS7.
MySQL tuning, let’s start with performance monitoring to get a feel for it.
useshow profile
Show Profile is a query profiling tool for MySQL.
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
Copy the codeYou can see that the tool is OFF by default and can be changed dynamically at the current session level using server variables
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Copy the codeWhen the setup is complete, all statements executed on the server are measured for time and other data related to changes in query execution status.
Let’s create a table to demonstrate this:
mysql> create database mytest;
Query OK, 1 row affected (0.01 sec)
mysql> use mytest;
Database changed
mysql> create table stu(id int(8), name varchar(20));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into stu(id, name) values(1.'Jack Ma');
Query OK, 1 row affected (0.01 sec)
mysql> insert into stu(id, name) values(1.'Pony');
Query OK, 1 row affected (0.03 sec)
Copy the codeQuery the stU table:
mysql> select * from stu;
+------+---------+
| id | name |
+------+---------+
| 1 | Jack Ma |
| 2 | Pony |
+------+---------+
2 rows in set (0.00 sec)
Copy the codeThis 2 rows in set (0.00 SEC) is the prompt for executing an SQL query from the command line, and the execution time is 0.00 seconds.
In the mysql command line mode, only two decimal digits can be displayed. You can run the following command to view the execution time
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000061 |
| checking permissions | 0.000009 |
| Opening tables | 0.000019 |
| init | 0.000020 |
| System lock | 0.000009 |
| optimizing | 0.000005 |
| statistics | 0.000014 |
| preparing | 0.000011 |
| executing | 0.000003 |
| Sending data | 0.000121 |
| end | 0.000012 |
| query end | 0.000011 |
| closing tables | 0.000011 |
| freeing items | 0.000021 |
| cleaning up | 0.000021 |
+----------------------+----------+
Copy the codeThis allows us to clearly see how much time was spent Starting, System lock, Sending data, and so on.
As you can see from the demo above, the Show profile returns the execution time of each item as soon as it is executed. It does not show which SQL statement it is for, but rather lists the execution time of the last SQL statement you executed.
There must be cases where you can look at multiple SQL statements that have been executed before? Yes, show Profiles
show profiles
mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+
| 1 | 0.02907350 | create table stu(id int(8), name varchar(20)) |
| 2 | 0.00337800 | create database mytest |
| 3 | 0.02786850 | SELECT DATABASE() |
| 4 | 0.00065300 | show databases |
| 5 | 0.00086700 | show tables |
| 6 | 0.06554900 | create table stu(id int(8), name varchar(20)) |
| 7 | 0.00079850 | insert into stu(1.'Jack') |
| 8 | 0.06901975 | show create table stu |
| 9 | 0.00008800 | insert into stu(1.'Jack') |
| 10 | 0.00616000 | insert into stu(id, name) values(1.'Jack Ma') |
| 11 | 0.03119675 | insert into stu(id, name) values(1.'Pony') |
| 12 | 0.03124900 | update stu set id=2 where name='Pony' |
| 13 | 0.00036975 | select * from stu |
| 14 | 0.00034925 | select * from stu |
+----------+------------+------------------------------------------------+
14 rows in set.1 warning (0.00 sec)
Copy the codeInsert into STu (id, name) values(1, ‘Pony’);
mysql> show profile for query 11;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000072 |
| checking permissions | 0.000009 |
| Opening tables | 0.000021 |
| init | 0.000018 |
| System lock | 0.000008 |
| update | 0.000094 |
| end | 0.000005 |
| query end | 0.030815 |
| closing tables | 0.000036 |
| freeing items | 0.000081 |
| cleaning up | 0.000039 |
+----------------------+----------+
11 rows in set, 1 warning (0.00 sec)
Copy the codeThat is, Query_ID can be combined with show profile.
MySQL > show profile;
SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: { ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS }Copy the codeShow profile, for Query n, show profile, for query n
All Displays all performance information
Block IO Displays the number of block I/O operations
Context Switches show the number of context switches, passive and active
CPU Displays the user CPU time and system CPU time
IPC displays the number of messages sent and received
Memory is not implemented yet
Page faults displays the number of page errors
Source displays the function name and location in the source code
Swaps shows the number of swaps
Let’s do it
mysql> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------+
| 1 | 0.00009625 | show variable like '%profi%' |
| 2 | 0.00373950 | show variables like '%profi%' |
| 3 | 0.00025825 | select * from stu |
+----------+------------+-------------------------------+
3 rows in set.1 warning (0.00 sec)
Copy the codeLet’s look at Query_ID 3
all
mysql> show profile all for query 3\G
*************************** 1. row ***************************
Status: starting
Duration: 0.000070
CPU_user: 0.000000
CPU_system: 0.000064
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: NULL
Source_file: NULL
Source_line: NULL
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000011
CPU_user: 0.000000
CPU_system: 0.000009
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: check_access
Source_file: sql_authorization.cc
Source_line: 809
*************************** 3. row ***************************
Status: Opening tables
Duration: 0.000021
CPU_user: 0.000000
CPU_system: 0.000021
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: open_tables
Source_file: sql_base.cc
Source_line: 5793
*************************** 4. row ***************************
Status: init
Duration: 0.000019
CPU_user: 0.000000
CPU_system: 0.000019
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 128
*************************** 5. row ***************************
Status: System lock
Duration: 0.000009
CPU_user: 0.000000
CPU_system: 0.000009
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_lock_tables
Source_file: lock.cc
Source_line: 330
*************************** 6. row ***************************
Status: optimizing
Duration: 0.000005
CPU_user: 0.000000
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 158
*************************** 7. row ***************************
Status: statistics
Duration: 0.000014
CPU_user: 0.000000
CPU_system: 0.000014
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 374
*************************** 8. row ***************************
Status: preparing
Duration: 0.000011
CPU_user: 0.000000
CPU_system: 0.000010
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: optimize
Source_file: sql_optimizer.cc
Source_line: 482
*************************** 9. row ***************************
Status: executing
Duration: 0.000004
CPU_user: 0.000000
CPU_system: 0.000004
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 126
*************************** 10. row ***************************
Status: Sending data
Duration: 0.000050
CPU_user: 0.000000
CPU_system: 0.000050
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: exec
Source_file: sql_executor.cc
Source_line: 202
*************************** 11. row ***************************
Status: end
Duration: 0.000005
CPU_user: 0.000000
CPU_system: 0.000005
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: handle_query
Source_file: sql_select.cc
Source_line: 206
*************************** 12. row ***************************
Status: query end
Duration: 0.000008
CPU_user: 0.000000
CPU_system: 0.000008
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 4956
*************************** 13. row ***************************
Status: closing tables
Duration: 0.000008
CPU_user: 0.000000
CPU_system: 0.000008
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_execute_command
Source_file: sql_parse.cc
Source_line: 5009
*************************** 14. row ***************************
Status: freeing items
Duration: 0.000013
CPU_user: 0.000000
CPU_system: 0.000013
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: mysql_parse
Source_file: sql_parse.cc
Source_line: 5622
*************************** 15. row ***************************
Status: cleaning up
Duration: 0.000014
CPU_user: 0.000000
CPU_system: 0.000013
Context_voluntary: 0
Context_involuntary: 0
Block_ops_in: 0
Block_ops_out: 0
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: dispatch_command
Source_file: sql_parse.cc
Source_line: 1931
15 rows in set.1 warning (0.00 sec)
Copy the codeThe purpose of the last \G query is to format the display of the query result.
Here we can see that show Profile All shows all performance information.
block io
mysql> show profile block io for query 3;
+----------------------+----------+--------------+---------------+
| Status | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+
| starting | 0.000070 | 0 | 0 |
| checking permissions | 0.000011 | 0 | 0 |
| Opening tables | 0.000021 | 0 | 0 |
| init | 0.000019 | 0 | 0 |
| System lock | 0.000009 | 0 | 0 |
| optimizing | 0.000005 | 0 | 0 |
| statistics | 0.000014 | 0 | 0 |
| preparing | 0.000011 | 0 | 0 |
| executing | 0.000004 | 0 | 0 |
| Sending data | 0.000050 | 0 | 0 |
| end | 0.000005 | 0 | 0 |
| query end | 0.000008 | 0 | 0 |
| closing tables | 0.000008 | 0 | 0 |
| freeing items | 0.000013 | 0 | 0 |
| cleaning up | 0.000014 | 0 | 0 |
+----------------------+----------+--------------+---------------+
15 rows in set.1 warning (0.00 sec)
Copy the codeThe last two columns represent the number of block IO operations.
The syntax for each type is listed below, and the demonstration effect will not be described here.
show profile context switches for query 3show profile cpu for query 3show profile ipc for query 3show profile page faults for query 3show profile source for query 3show profile swaps for query 3
The show profile allows you to see the execution time of each part of a SQL statement. This is useful for tracking the execution time of an online SQL statement.
Note
The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead;
Pay attention to
The SHOW PROFILE and SHOW PROFILES statements are not recommended and will be removed in future MySQL releases.
Switch to Performance Schema;
All right, delete it if you want. However, before MySQL removes show profile, we can still use it, after all, it smells good.
Since future releases will use Performance Schema, let’s see what it is!
Performance Schema
MySQL performance Schema is used to monitor resource consumption and resource waiting during MySQL Server running at a lower level. Performance Schema makes it easier to monitor MySQL.
In MySQL 5.7, performance mode is enabled by default. If you want to disable it explicitly, you need to modify the configuration file. Variable ‘Performance_schema’ is a read only Variable.
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
mysql> set performance_schema=0;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable
Copy the codeModify performance_SCHEMA in the configuration file. On indicates that performance_schema is enabled, and off indicates that performance_schema is disabled
[mysqld]
performance_schema=ON
Copy the codePerformance_schema: performance_schema
mysql> use performance_schema;
mysql> show tables;
Copy the code
There are 87 tables, each containing relevant information.
You can run the show create table tablename command to view the table structure when the table is created:
mysql> show create table threads\G
*************************** 1. row ***************************
Table: threads
Create Table: CREATE TABLE `threads` (
`THREAD_ID` bigint(20) unsigned NOT NULL,
`NAME` varchar(128) NOT NULL,
`TYPE` varchar(10) NOT NULL,
`PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL,
`PROCESSLIST_USER` varchar(32) DEFAULT NULL,
`PROCESSLIST_HOST` varchar(60) DEFAULT NULL,
`PROCESSLIST_DB` varchar(64) DEFAULT NULL,
`PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL,
`PROCESSLIST_TIME` bigint(20) DEFAULT NULL,
`PROCESSLIST_STATE` varchar(64) DEFAULT NULL,
`PROCESSLIST_INFO` longtext,
`PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
`ROLE` varchar(64) DEFAULT NULL,
`INSTRUMENTED` enum('YES'.'NO') NOT NULL,
`HISTORY` enum('YES'.'NO') NOT NULL,
`CONNECTION_TYPE` varchar(16) DEFAULT NULL,
`THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Copy the codeThreads, THREAD_ID, PROCESSLIST_ID, etc.
Take a look at the table setup_consumers again:
mysql> show create table setup_consumers;
+-----------------+------------------------------------------------------------------------------------------------------ --------------------------------------------------+
| Table | Create Table |
+-----------------+------------------------------------------------------------------------------------------------------ --------------------------------------------------+
| setup_consumers | CREATE TABLE `setup_consumers` (
`NAME` varchar(64) NOT NULL,
`ENABLED` enum('YES'.'NO') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+------------------------------------------------------------------------------------------------------ --------------------------------------------------+
1 row in set (0.00 sec)
Copy the codeThis consumer feels familiar.
In fact, there are two basic concepts we need to understand in order to understand what follows:
- The instruments producer is used to collect events generated by various mysql operations. The configuration items in the configuration table are called monitoring collection configuration items.
- Consumers, the corresponding consumer table is used to store data collected from instruments, and the configuration items in the corresponding configuration table can be called consumer storage configuration items.
Performance_schema Table classification
Tables under the Performance_SCHEMA library can be grouped by monitoring different latitude rows.
Statement event log tables. These tables record statement event information. The current statement event table events_statements_current, the history statement event table events_statements_History and the long statement history event table events_statements_history_LONG, and the aggregated summary table summar Y, where the summary table can be further divided by account, host, program, thread, user and global.
show tables like '%statement%';
Wait event log table, similar to the related log table for statement event types:
show tables like '%wait%';
-- Phase event record table, which records the phase events of the statement execution
show tables like '%stage%';
Transaction event log table, a table that records events related to a transaction
show tables like '%transaction%';
Monitor tables called by the file system layer
show tables like '%file%';
Table that monitors memory usage
show tables like '%memory%';
Performance_schema configuration tables that dynamically configure performance_schema
show tables like '%setup%';
Copy the codeHere, take a look at the one that’s relevant:
mysql> show tables like '%transaction%';
+------------------------------------------------------+
| Tables_in_performance_schema (%transaction%) |
+------------------------------------------------------+
| events_transactions_current |
| events_transactions_history |
| events_transactions_history_long |
| 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 |
+------------------------------------------------------+
8 rows in set (0.00 sec)
Copy the codePerformance_schema simple configuration and use
When the database is first initialized and started, not all Instruments (event collection items, each item in the collection items configuration table has a switch field, either YES or NO) and consumers(similar to the collection items, there is also a corresponding event type save table configuration items, YES indicates that the corresponding table stores performance data, NO indicates that the corresponding table does not store performance data) are enabled, so not all events are collected by default.
It is possible that the events you need to detect are not turned on and need to be set. You can turn on the corresponding Instruments and consumers using the following two statements (row counts may vary from MySQL version to MySQL version).
To enable the collector configuration item waiting for events, modify the collector configuration item in the seTUP_INSTRUMENTS configuration table
mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 267 rows affected (0.03 sec)
Rows matched: 321 Changed: 267 Warnings: 0
Copy the codeTurn on the save table configuration switch for waiting events and modify the corresponding configuration item in the setup_consumers configuration table
mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Copy the codeAfter the configuration is complete, you can view what the current server is doing.
- You can query
events_waits_currentEach thread in this table contains only one row of data to display the latest monitoring events for each thread:
Definition:
This information indicates that the thread with id 3 is waiting for the BUF_DBLWR_MUtex lock at a time of 69917. Attribute Description: Id: Which thread the event came from, Event_name: indicates the detected content. Source: indicates the source file and line number of the detected code. Timer_start: indicates the start time of the event Timer_wait: indicates the total elapsed time of the event. Note: Only one record is kept for each thread in the _current table. Once the thread completes its work, the event information of the thread is not recorded in the table.Copy the code_historyThe table records the number of events that should be completed by each thread, but only 10 events are recorded for each thread, and any more will be overwritten. (As you can see in the following demonstration, there are only 10 events per thread.)_history_longThe table records the event information of all threads, but the total number of records is 10000, exceeding will be overwritten
mysql> mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;
+-----------+----------+------------------------------------------+------------+
| thread_id | event_id | event_name | timer_wait |
+-----------+----------+------------------------------------------+------------+
| 3 | 4042 | wait/synch/mutex/innodb/buf_pool_mutex | 47785 |
| 3 | 4043 | wait/synch/mutex/innodb/flush_list_mutex | 43761 |
| 3 | 4044 | wait/synch/mutex/innodb/buf_pool_mutex | 58851 |
| 3 | 4045 | wait/synch/mutex/innodb/buf_dblwr_mutex | 73438 |
| 3 | 4046 | wait/synch/mutex/innodb/buf_pool_mutex | 222829 |
| 3 | 4047 | wait/synch/mutex/innodb/buf_pool_mutex | 42755 |
| 3 | 4048 | wait/synch/mutex/innodb/flush_list_mutex | 44767 |
| 3 | 4049 | wait/synch/mutex/innodb/buf_pool_mutex | 59857 |
| 3 | 4050 | wait/synch/mutex/innodb/buf_dblwr_mutex | 72432 |
| 3 | 4041 | wait/synch/mutex/innodb/buf_pool_mutex | 243452 |
| 17 | 2442 | wait/synch/mutex/innodb/sync_array_mutex | 74947 |
| 17 | 2440 | wait/synch/mutex/innodb/sync_array_mutex | 82492 |
| 17 | 2439 | wait/synch/mutex/innodb/sync_array_mutex | 458233 |
| 17 | 2438 | wait/synch/mutex/innodb/log_sys_mutex | 239428 |
| 17 | 2437 | wait/synch/mutex/innodb/sync_array_mutex | 85510 |
| 17 | 2446 | wait/synch/mutex/innodb/sync_array_mutex | 84504 |
| 17 | 2445 | wait/synch/mutex/innodb/sync_array_mutex | 77462 |
| 17 | 2444 | wait/synch/mutex/innodb/log_sys_mutex | 238422 |
| 17 | 2443 | wait/synch/mutex/innodb/sync_array_mutex | 83498 |
| 17 | 2441 | wait/synch/mutex/innodb/log_sys_mutex | 229368 |
| 19 | 7281 | wait/synch/mutex/innodb/flush_list_mutex | 43761 |
+-----------+----------+------------------------------------------+------------+
21 rows in set (0.00 sec)
Copy the codesummaryTables provide a summary of all events, and tables in this group aggregate event data in different ways (by user, by host, by thread, and so on).
Such as: To see which instruments take up the most time, Queries can be made on the COUNT_STAR or SUM_TIMER_WAIT columns of the EVENTS_WAITS_SUMMARY_global_BY_EVENT_name table (COUNT (*) for the number of events recorded, and S for the TIMER_WAIT column of event records) UM (TIMER_WAIT)
mysql> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
+-----------------------------------------------+------------+
| EVENT_NAME | COUNT_STAR |
+-----------------------------------------------+------------+
| wait/synch/mutex/innodb/buf_pool_mutex | 3357 |
| wait/synch/mutex/innodb/log_sys_mutex | 3357 |
| wait/synch/mutex/innodb/sync_array_mutex | 2241 |
| wait/synch/mutex/innodb/flush_list_mutex | 2238 |
| wait/io/file/sql/FRM | 1590 |
| wait/synch/mutex/innodb/log_flush_order_mutex | 1119 |
| wait/synch/mutex/innodb/log_sys_write_mutex | 1119 |
| wait/synch/mutex/innodb/fil_system_mutex | 1119 |
| wait/synch/mutex/innodb/dict_sys_mutex | 1119 |
| wait/synch/mutex/innodb/buf_dblwr_mutex | 1119 |
+-----------------------------------------------+------------+
10 rows in set (0.10 sec)
Copy the codeinstanceThe table records which types of objects are detected. When these objects are used by the server, an event record is generated in the table.
For example, the file_Instances table lists file I/O operations and their associated file names
mysql> select * from file_instances limit 10;
+----------------------------------------+--------------------------------------+------------+
| FILE_NAME | EVENT_NAME | OPEN_COUNT |
+----------------------------------------+--------------------------------------+------------+
| /usr/share/mysql/english/errmsg.sys | wait/io/file/sql/ERRMSG | 0 |
| /usr/share/mysql/charsets/Index.xml | wait/io/file/mysys/charset | 0 |
| /var/lib/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 |
| /var/lib/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 |
| /var/lib/mysql/mysql/engine_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 3 |
| /var/lib/mysql/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 3 |
+----------------------------------------+--------------------------------------+------------+
10 rows in set (0.00 sec)
Copy the codeParameter Description of common configuration items
- Startup options
performance_schema_consumer_events_statements_current=TRUETable events_statementS_current (this table records current statement events) The eventS_statementS_current configuration item in the Setup_Consumers configuration table can also be dynamically updated using the UPDATE statement in the SetuP_Consumers table after startup. The default value isTRUE
performance_schema_consumer_events_statements_history=TRUE# Similar to the performance_schemA_consumer_EVENTS_statementS_current option, but this option is used to configure whether to record the short history of statement events, which defaults toTRUE
performance_schema_consumer_events_stages_history_long=FALSE# Similar to the performance_schemA_consumer_EVENTS_statementS_current option, but this option is used to configure whether to record the long history of statement events. The default isFALSEIn addition to statement events, the following are supported: Wait events, State events, and Transaction events have the same configuration as statement events. However, these wait events are not enabled by default. If you need to start them together with MySQL Server startup, Performance_schema_consumer_global_instrumentation is usually written into the my.cnf configuration file=TRUEWhether to enable global tables at MySQL Server startup (e.g. Mutex_instances, RWLOCK_instances, cond_instances, File_instances, Users, Hostsaccounts, socket_SUMMARY_by_EVENT_name, file_sum Global configuration items can be dynamically updated in the setup_consumers table using the UPDATE statement. The default value isTRUE
performance_schema_consumer_statements_digest=TRUEWhether to enable the recording function of the eventS_statementS_SUMMARY_BY_DIGEST table when MySQL Server is started. After MySQL Server is started, the DIGEST configuration item can be dynamically updated using the UPDATE statement in the Setup_CONSUMERS table. The default value isTRUE
performance_schema_consumer_thread_instrumentation=TRUEWhether to enable the recording function of the eventS_xxx_SUMMARY_BY_YYY_by_EVENT_NAME table when MySQL Server is started. After MySQL Server is started, you can also use the UPDATE statement to dynamically UPDATE thread configuration items in the Setup_CONSUMERS tableTRUE
performance_schema_instrument[=Name] Specifies whether to enable some collectors when MySQL Server is started. There are thousands of instruments configuration items, so this configuration item supports key-valueModes are also supported%# [=Name] can be specified as the specific Name of the Instruments (but then this option will need to be used multiple times if more than one needs to be specified), or as a wildcard, you can specify the same prefix for the Instruments+Wildcard characters can also be used%Specifies the opening of a single instrument on behalf of all instruments--performance-schema-instrument= 'instrument_name=value'Open multiple Instruments using wildcards--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'Switch all instruments--performance-schema-instrument= '%=ON'
--performance-schema-instrument= '%=OFF'Note that for these startup options to take effect, performance_SCHEMA needs to be set=ON. In addition, these boot options are not availableshowVariables statement, but we can query the values specified by these options through the seTUP_INSTRUMENTS and setup_Consumers tables.Copy the code- System variables
show variables like '%performance_schema%'; Performance_schema =ON /* Controls the switch of performance_schema. To use MySQL's Performance_schema, enable it when mysqld is started. This parameter is disabled by default in performance_schema versions that support performance_schema before 5.7.x, and enabled by default from 5.7.x. Note: Performance_schema is automatically disabled if mysqld finds it cannot allocate any associated internal buffers when initializing, Set performance_SCHEMA to OFF */ PERFORMANce_schemA_digests_size =10000 /* Controls the maximum number of rows in the eventS_statementS_SUMMARY_BY_digest table. If more statement summary information is generated than this maximum, the table cannot be continued. */ Performance_schema_events_statementS_historY_long_size =10000 /* Controls the maximum number of rows in the eventS_statementS_HISTORy_LONG table. This controls the total number of event records that all sessions can hold in the eventS_StatementS_HISTORy_LONG table. In * 5.6.x, the default value of 5.6.5 and earlier versions is 10000. in 5.6.3, the default value of 5.6.5 and later versions is -1. In most cases, the automatically calculated value is 10000 x 5.7.x, and the default value is -1. Automatically computed values are 10000 */ Performance_schemA_events_statementS_history_size =10 /* Controls the maximum number of rows for a single thread (session) in the events_statements_history table, This parameter controls the number of event records that can be stored in a single session in the events_statements_history table. After this limit is exceeded, the earliest records of a single session will be overwritten by global variables, read-only variables, and integer values. For versions 5.6.5 and earlier, the default value is 10, 5.6.6 and later, the default value is -1. In versions 5.6.5, the default value is -1, and the default value is 10. A wait event, a state event, a transaction event, and a transaction event have the same parameters as a statement event. */ performance_schemA_max_digest_length =1024 /* Used to limit the length of SQL statement text stored in performance_schema in standardised form, This variable is related to max_digest_length, a read-only global variable with a default value of 1024 bytes and an integer value. Value range: 0 to 1048576 */ performance_schemA_max_SQL_TEXt_length =1024 /* Controls storing events_statements_current, The maximum NUMBER of SQL-length bytes for the SQL_TEXT column in the eventS_statements_HISTORY and EVENTs_statementS_HISTORy_LONG statement event tables. Portions that exceed the system variable performance_schemA_MAX_SQL_TEXt_length are discarded and not logged. In general, this parameter does not need to be adjusted unless the truncated portion is significantly different from other SQL variables. Global variable, read-only variable, integer value, default 1024 bytes, The value ranges from 0 to 1048576, 5.7.6 version Introduced to reduce the system variable performance_schemA_MAX_SQL_TEXt_length. The performance_schemA_max_SQL_TEXt_length value can reduce the memory usage. As a result, there is no way to distinguish between these widely different SQL. Increasing the value of this system variable increases memory usage, but makes it more accurate to distinguish between different parts for summary SQL. * /Copy the codePerformance_schema Specifies the performance_schema operation
Once you have a basic understanding of the table information, you can use these tables to perform actual query operations for actual analysis.
What type of SQL is executed the most?
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
Which type of SQL has the highest average response time?
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
SQL > select * from 'SQL';
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
4. Which type of SQL scan records has the largest number?
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
Which type of SQL uses temporary tables the most?
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
Which type of SQL returns the most result sets?
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
Which table has the most physical IO?
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
Which table has the most logical IO?
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
Which index is accessed the most?
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
Which index is never used?
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
11. Which wait event consumes the most time?
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name ! = 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1. Analyze the execution of an SQL statement, STEge, and WAIT information
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2. Check the time consumed at each stage
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3. Check the lock wait status at each stage
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;
Copy the codeWe can refer to the official website for more details about Performance Schema.
Let’s think about whether we can build a system to query some information of the Performance Schema table and display it on the Web, so that we can carry out visual monitoring.
show processlist
Use show ProcessList to check the number of connected threads to see if a large number of threads are in an abnormal state or have other abnormal characteristics.
mysql> show processlist;
+----+------+-----------+--------------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------------------+---------+------+----------+------------------+
| 6 | root | localhost | performance_schema | Query | 0 | starting | show processlist |
+----+------+-----------+--------------------+---------+------+----------+------------------+
1 row in set (0.00 sec)
Copy the codeAttribute Description:
idsession idUser Indicates the user
Host Host for the operation
Db Database for operation
Command Current Status
- Sleep: The thread is waiting for the client to send a new request
- Query: The thread is executing a query or sending the result to the client
- Locked: At the mysql service layer, the thread is waiting for a table lock
- Analyzing and Statistics: The thread is collecting storage engine statistics and generating query execution plans
- Copying to TMP Table: Threads are executing queries and Copying their result sets into a temporary table
- Sorting result: Threads are sorting result sets
- Sending Data: A thread may be sending data between states, generating a result set, or sending data back to a client
Info Detailed SQL statements
Time Time when a command is executed
State Command execution status
summary
MySQL tuning performance monitoring
show profileUsing the show profile query profiling tool, you can specify a specific typeperformance schemaUse performance Schema to make monitoring mysql easiershow processlistUse show ProcessList to check the number of connected threads to see if a large number of threads are in an abnormal state or have other abnormal characteristics
The first public line 100 li ER, welcome the old iron attention reading correction. GitHub github.com/xblzer/Java…