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

MYSQL in the performance_schema process is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Today we introduce the process of performance_schema in MYSQL.

The connection of 1 thread is very important in some monitoring of MYSQL. If a developer finds that MYSQL can not log in after launching a certain program, in addition to setting aside an extra port for you to deal with this matter, the second thing is to get the connection number of your MYSQL quickly, because it is likely to be a bad SQL, resulting in the number of connections has been exhausted. We can pass.

Select * from performance_schema.accounts

To query an information, how many connections are there and the number of historical connections? it is very easy for us to do a monitoring in zabbix through this information. Of course, there is another thing that has nothing to do with technology, that is, the problem of account, no matter how good the technology has to match a good management, what kind of management? Otherwise, even if you find this information, you will not know which app caused the trouble.

Select thread_id,event_id,end_event_id,event_name,timer_start/1000000000000 as timer_start_s,timer_end/1000000000000 as timer_end_s, (timer_wait/1000000000000) as timer_wait_s,work_estimated from events_stages_history limit 1

Through the above query, you can view the history of the time and the waiting time of the related time, and monitor some key indicators to find some problems. (you need to find out what you care about in event_name by yourself.)

The above statement can be modified to get more accurate information, and if you do it on a regular basis, you can replace the old slow query method.

Select user,host,event_name,count_alloc,count_free,sum_number_of_bytes_alloc/1000/1000 as sum_number_of_MB_alloc,sum_number_of_bytes_free/1000/1000 as sum_number_of_bytes_free_MB,current_number_of_bytes_useD/1000/1000 as current_number_of_MB_USED from memory_summary_by_account_by_event_name where count_alloc 0 and USER = 'app_collection' ORDER BY current_number_of_bytes_used desc limit 10

Or the waiting time for database operation files in your current system

Select event_name, (avg_timer_wait/1000000000000) as avg_timer_wait_s from file_summary_by_event_name where min_timer_wait 0 limit 20

Finally, there are a lot of tables in performance_schema, and the newer version of MYSQL will give us more information here.

Here, let's summarize the general types of tables here.

Setup_table configuration table

Current_events_table records what happens to the current thread

History of events that occurred in history_table

Summary_table 's statistical tables for various events

Whether setup_intruments has enabled some monitoring for the current database

Sampling rate monitored by setup_timers

The above reminds me of the question that someone in interview asked me last year. The question was that there was something like an ORACLE AWR report in MYSQL. What I told him at that time was no, no. In fact, although there is no ready-made AWR, can we create a MYSQL AWR by ourselves through certain scripts or PYTHON programs? now think about it, this is not a problem.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.