Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use Performance Schema for you. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Introduction to Performance Schema

Oracle DBA should know that Oracle provides a large number of views for DBA to use to troubleshoot problems, and there are waiting events to help you quickly identify which category the problem belongs to. There is also Performance Schema in MySQL to help you analyze and troubleshoot problems, and Sys Schema has been added in 5.7. after formatting the information of Performance Schema and information_schema, it is more convenient for you to analyze the problem.

Here first introduce the use of Performance Schema, to facilitate the later we better use Sys Schema.

Performance Schema opening mode

Set instruments and consumers to enable

After enabling performance_schema=on, we also need to set instruments and consumers.

Generally speaking, instruments means that monitoring items can be set through the setup_instruments table to count which monitoring items need to be enabled.

Consumers is to control whether the monitored results are recorded.

UPDATE setup_instruments SET ENABLED = 'YES', TIMED =' YES'

UPDATE setup_consumers SET ENABLED = 'YES'

If instruments and consumers are not enabled, the corresponding event detection cannot be used, and the corresponding waiting events and performance statistics cannot be collected.

Of course, we can also set the opening and closing of a certain instruments separately.

UPDATE setup_instruments SET ENABLED = 'NO'

WHERE NAME = 'wait/io/file/sql/binlog'

To prevent the default configuration from being restored after restart, you can add settings to the configuration file.

Performance-schema-instrument='instrument_name=value'

Performance-schema-consumer-consumer_name=value

Instrument_name and consumer_name

You can view it by querying setup_instruments and setup_consumers;

Hierarchical relationships in setup_consumers

Mysql > select * from setup_consumers

+-+ +

| | NAME | ENABLED |

+-+ +

| | events_stages_current | YES |

| | events_stages_history | YES |

| | events_stages_history_long | YES |

| | events_statements_current | YES |

| | events_statements_history | YES |

| | events_statements_history_long | YES |

| | events_waits_current | YES |

| | events_waits_history | YES |

| | events_waits_history_long | YES |

| | global_instrumentation | YES |

| | thread_instrumentation | YES |

| | statements_digest | YES |

+-+ +

12 rows in set (0.00 sec)

The event table in setup_consumers has different levels. We can see that there are global, thread, current, history, history_long above. The levels between them are as follows:

Global_instrumentation

Thread_instrumentation

Events_waits_current

Events_waits_history

Events_waits_history_long

Events_stages_current

Events_stages_history

Events_stages_history_long

Events_statements_current

Events_statements_history

Events_statements_history_long

Statements_digest

In other words, if global is set, it will check whether thread is set, if thread is set, current will be checked, if global is not set, thread and current will not be checked, and the corresponding information will not be recorded in the corresponding table.

If global_instrumentation is not set, no information is recorded in the corresponding table.

The history and history_long here can be set by parameters, because it is a memory table, which takes up a lot of memory if it is too large.

Types in setup_instruments

Mysql > select name,count (*) from setup_instruments group by left (name,5)

+-+ +

| | name | count (*) |

+-+ +

| | idle | 1 | |

| | stage/sql/After create | 108 |

| | statement/sql/select | 168 | |

| | wait/synch/mutex/sql/PAGE::lock | 279 |

+-+ +

4 rows in set (0.00 sec)

We can see that there are four types in setup_instruments (version 5.6).

You can see the specific explanation on the official website:

Https://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html

Simple introduction to use

Briefly introduce the efficiency of several statements used to troubleshoot SQL statements

See which indexes have not been 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

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.