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

This article mainly introduces what matters needing attention in the use of performance_schema in MYSQL, the article is very detailed, has a certain reference value, interested friends must read it!

Recently, compared with MYSQL's performance_schema, the summary before is relatively scattered, there is no overall view, it is only a trivial thing. For performance_schema as a whole, take a look at the future (MYSQL 8) and see what changes are needed in MYSQL's performance issues. Recruitment to kill is actually the old monitoring method to use "attractor" to attractiveness.

More and more attention has been paid to performance_schema since MYSQL5.6, but there has always been an idea that performance_schema should not be opened as far as possible, mainly due to the following reasons, system resource consumption, and inexplicable failures. As a result, performance_schema has not been a common way to observe the performance of the system that can be taken to the desktop.

If it is because of the problem of BUG in the initial stage, I agree not to open performance_schema, but because performance_schema consumes system resources and does not open it, in fact, I do not agree with this point. The performance monitoring of a system must be carried out all the time, and since the monitoring must be lossy to the system, in order not to lose, but not to monitor, I do not know what kind of reason it is, if the resource consumption of the system is controllable. And in the initial construction of the system, we will count this part of the resources into the consumption of the system.

To MYSQL 5.7 performance_schema monitoring methods and data are becoming more and more important, and it has become more flexible to set up. Roughly 5. 7 performance_schema of MYSQL is more convenient to control. Of course, there must be a plan. The following rough division, in fact, can also be subdivided. Let's talk about the general functions of these modules first.

There is no doubt that 1 Event series accounts for a large share, and event is enough to make a classification.

The division of event is divided into two dimensions: 1 category of statistical information; 2. Time is a storage category of statistical information.

From the category of statistical information, stages, statements, transactions, waits these four categories, and storage methods also have current, historical, distant historical information these three types, of course, there are all kinds of statistical analysis, for accounts, hosts, threads, users, and other information summary.

Here is a list, because there are too many things, if each gives an example, it will become an instruction manual.

Events_transactions_current Let's take this as an example. Through the following sentence, you should probably have a count of the running time of your current transaction, and you also have thread_id. If you can count the statement corresponding to your running time, is the current slow query method ready to be abandoned (there is a text that has been written about the abandonment of traditional slow query).

Select thread_id,event_name,state,trx_id,timeR_wait/1000000000000 as wait_second from events_transactions_current

We are using it, can we track a list, a graph about the waiting time of the transaction (especially for newly launched systems, you can start troubleshooting as soon as there is something wrong with the graph, you don't have to wait, and I don't think it's a difficult task to develop a slow query system.)

With a little modification, we can see that it is no worse than some functions of ORACLE or SQL SERVER. (some realize that MYSQL (including PG) is changing rapidly, and it is still in a period of rapid rise. Unlike ORACLE SQL SERVER, if you don't keep up, you can't keep up.)

2 setup

When it comes to the criticized issue of performance_schema encroaching on MYSQL performance, although from a personal point of view, this is not a problem (apart from BUG, I did not say that BUG is not a problem). Normal performance encroachment should be tolerated, but we can't do what we want, and we should use performance_schema to a certain extent.

Let's talk about how to get here.

Filter from the user's point of view, some information can not be recorded, for example, you think the system is stable, I only need to monitor some new business, what should I do?

The first step is to filter the application account and control it through the setup_actors table.

If we change the default full table to agree to the collection, instead, filter through the account, there will be a natural relative information collection.

Second, the filtering of certain events, some people like to eat sweet, some people like to eat sour, you can not only control the data source of entry, but also select the information entered.

Refer to the information in the figure above. We can filter the information of events listed in setup_consumers and directly UPDATE it.

Third, we use finer granularity to filter the information of instruments. It has two filtering granularities. 1 for filtering data like events_waits_summary_by_account_by_event_name, we can control it from two aspects: ONE event_name, we can filter some event that we do not need and do not collect his information, the other is to control timer, collect some time-related information (reduce the frequency of collection), control.

For example, we can stop monitoring the wait/synch/mutex/sql/Event_scheduler::LOCK_scheduler_state event (because we don't use event_scheduler at all because of MYSQL)

Fourth, control the OBJECTS of the database. Through setup_objects, we can isolate some database projects. For example, if there are no stored procedures, no functions, and no trigger in your database, we will naturally turn off the capture of these performance.

Through the above four tricks, we can greatly reduce the resource consumption of the system by performance_schema, and at the same time, we can also monitor the system through performance_schema. Whether to write a statement or set it in CNF, of course, depends on whether the demand is stable.

There is also a feature that earlier versions of MYSQL do not have, for an index that uses usage rate to take up IO.

Select * from table_io_waits_summary_by_index_usage

Through this statistics, you can use the index for the tables in the database, and there is a clear number as a guide for the distribution of Imax O that does not use the index. Through this table, you can know in detail whether there is a possibility of missing indexes in the current database.

That's all for today. In fact, there are still a lot of things about performance_schema. If you are interested, you can continue to dig, it will be good for the performance judgment of the system and the search for problems in the future. In addition, recently I have seen a lot of books and online courses such as learning MYSQL in 12 hours and becoming a master of MYSQL in 21 days. In fact, I don't have any opinions on these courses, just for fear of misleading some people into thinking that MYSQL is easy to learn, and it will be OK in a few days. Personal experience, including SQL SERVER ORACLE, PG, MONGODB, these databases, are not so easy, in which the "pit" is through years of experience and all kinds of scolding, fear to come over, a short course, can let people who do not understand, know some knowledge, any knowledge accumulation is composed of unremitting efforts and various grievances.

By the way, when performance_schema is enabled, check mysql bugs. When some versions of MYSQL are enabled, there will be OOM.

The above is all the contents of the article "what are the precautions for MYSQL to use performance_schema?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.