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

This article mainly introduces how to use the MySQL system database to do performance load diagnosis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

A master once said that knowing the database you manage like knowing your wife, I think it includes two aspects of understanding:

1. In terms of stability, more attention is paid to high level-level measures such as high availability, read-write separation, load balancing, disaster preparedness management and so on (just like ensuring the stability of life)

2, at the instance level, we need to pay attention to memory, IO, network, hotspot table, hotspot index, top sql, deadlock, blocking, historical execution of abnormal SQL (such as quality of life details) MySQL performance_ data library and sys library provide very rich system log data, can help us better understand the very details, here is a simple list of some commonly used data.

The sys library encapsulates some tables in performance_data in a more readable way, so these data sources are still data in the performance_data library.

Here is a rough list of some system data commonly used by individuals, so that you can have a clearer understanding of resource allocation during the operation of MySQL at the instance level.

Information in Status

The status variable of MySQL only gives a general information, and it is impossible to know the detailed resource consumption from the status variable, such as where the hot spots of IO or memory are, where are the hot spots of libraries and tables. If you want to know the specific details, you need the data in the system library.

The premise is to turn on performance_schema, because the view of the sys library is based on the performance_schema library.

Memory usage:

Memory / innodb_buffer_pool usage

Summary of the usage of innodb_buffer_pool summary. It is known that the current instance 26214416Accord 1024 = 4096MB buffer pool has been used with 2326016Accord1024 363MB

The details of the memory occupied by innodb_buffer_pool can be counted according to the dimensions of the library\ table.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT database_name, SUM (compressed_size) / 1024 AS is_hashed_memory 1024 AS allocated_memory, SUM (data_size) / 1024 AS is_hashed_memory, SUM (is_hashed) * 16 AS is_hashed_memory, SUM (is_old) * 16 AS is_old_memoryFROM (SELECT case when INSTR (TABLE_NAME,'.') > 0 then replace (TABLE_NAME,1,INSTR (TABLE_NAME,'.')-1),'`' '') else 'system_database' end as database_name, case when INSTR (TABLE_NAME,'.') > 0 then replace (SUBSTRING (TABLE_NAME,INSTR (TABLE_NAME,'.') + 1),'','') ELSE 'system_obj' END AS table_name, if (compressed_size = 0, 16384, compressed_size) AS compressed_size, data_size, if (is_hashed =' YES',1,0) is_hashed, if (is_old = 'YES') 1 is_old FROM information_schema.innodb_buffer_page WHERE TABLE_NAME IS NOT NULL 0) tGROUP BY database_nameORDER BY allocated_memory DESCLIMIT 10

Read and write statistics of library\ table, hot data statistics at logical level

The target table is performance_schema.table_io_waits_summary_by_table, and in some articles it is called logical IO, but it has nothing to do with logical IO. The meaning of the fields in this table is based on the statistics of the number of rows read and written to the table. As for the real logical IO-level statistics, I still don't know which system tables are available to query. This library can clearly see how the statistical results in this table are calculated.

Based on the statistics of the number of rows read and written in the table, this is a cumulative value, simply looking at the value itself, I think it is of little significance, and it is necessary to collect and calculate the difference regularly in order to have reference significance.

The following is based on the reading and writing of the library-level statistics.

Database\ table read and write statistics, hot data statistics at the physical IO level

Count the hotspot data according to the dimensions of the physical IO, which libraries\ tables consume how much physical IO. Here the data in the original system table is a cumulative statistical value, the most extreme case is that a table has 0 rows, but there are a large number of physical read and write IO.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT database_name, IFNULL (cast (sum (total_read) as signed), 0) AS total_read, IFNULL (cast (sum (total_written) as signed), 0) AS total_written, IFNULL (cast (sum (total) AS SIGNED), 0) AS total_read_writtenFROM (SELECT substring (REPLACE (file,'@ @ datadir/',')), 1 (REPLACE (file,'@ datadir/','),'/') AS database_name, count_read Case when instr (total_read,'KiB') > 0 then replace (total_read,'KiB','') / 1024 when instr (total_read,'MiB') > 0 then replace (total_read,'MiB','') / 1024 when instr (total_read,'GiB') > 0 then replace (total_read,'GiB','') * 1024 END AS total_read, case when instr (total_written,'KiB') > 0 then replace (total_written,'KiB') '') / 1024 when instr (total_written,'MiB') > 0 then replace (total_written,'MiB','') when instr (total_written,'GiB') > 0 then replace (total_written,'GiB','') * 1024 END AS total_written, case when instr (total,'KiB') > 0 then replace (total,'KiB','') / 1024 when instr (total,'MiB') > 0 then replace (total,'MiB','') when instr (total 'GiB') > 0 then replace (total,'GiB','') * 1024 END AS total from sys.io_global_by_file_by_bytes WHERE FILE LIKE'% @ @ datadir%' AND instr (REPLACE (file,'@ datadir/','),'/') > 0) tGROUP BY database_nameORDER BY total_read_written DESC

Ps: personally, I don't like MySQL's custom format_*** function, which is well-intentioned to format some data (time, storage space, etc.) into a more readable schema. But it does not support unit parameters, more often want to display in a fixed unit, such as formatting a time, according to the unit size may show subtle, or milliseconds, or seconds, or minutes, or days. For example, if you want to format the time uniformly into seconds, sorry, it is not supported. Some data is not only as simple as a glance, but even needs to be read and archived for analysis. Therefore, it is not recommended and will not use those format functions here.

TOP SQL statistics

You can count top sql by execution time, blocking time, number of rows returned, and so on.

In addition, you can filter last_seen by time, and you can count the top sql that has appeared in a certain period of time.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT schema_name, digest_text, count_star, avg_timer_wait/1000000000000 AS avg_timer_wait, max_timer_wait/1000000000000 AS max_timer_wait, sum_lock_time/count_star/1000000000000 AS avg_lock_time, sum_rows_affected/count_star AS avg_rows_affected, sum_rows_sent/count_star AS avg_rows_sent, sum_rows_examined/count_star AS avg_rows_examined, sum_created_tmp_disk_tables/count_star AS avg_create_tmp_disk_tables Sum_created_tmp_tables/count_star AS avg_create_tmp_tables, sum_select_full_join/count_star AS avg_select_full_join, sum_select_full_range_join/count_star AS avg_select_full_range_join, sum_select_range/count_star AS avg_select_range, sum_select_range_check/count_star AS avg_select_range, first_seen, last_seenFROM performance_schema.events_statements_summary_by_digestWHERE last_seen > date_add (NOW () Interval-1 HOUR) ORDER BY max_timer_wait-- avg_timer_wait-- sum_rows_affected/count_star-sum_lock_time/count_star-- avg_lock_time-- avg_rows_sentDESClimit 10

It should be noted that this statistics is based on the resources consumed by MySQL to execute a transaction, not a statement. The author was confused for a while at first, to give a simple example.

Refer to the following, here is a stored procedure to loop write a piece of data, the calling method is call create_test_data (N), write N pieces of test data.

For example, call create_test_data (1000000) is to write 100W test data, this execution process took several minutes, according to the author's test example, the dimension of avg_timer_wait is definitely a TOP SQL.

But in the query, we never found that the call to the stored procedure was listed as TOP SQL. Later, we tried to add a thing inside the stored procedure, and then successfully collected the whole TOP SQL.

Therefore, the statistics in performance_schema.events_statements_summary_by_digest are based on transactions, not on the execution time of a batch.

CREATE DEFINER= `root` @ `% `root` create_test_ data` (IN `loopcnt` INT) LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT''BEGIN-- START TRANSACTION; while loopcnt > 0 do insert into test_mrr (rand_id,create_date) values (RAND () * 1000000000 values (6)); set loopcnt=loopcnt-1; end while;-- commit;END

Another interesting point is that this system table is one of the few that supports truncate, of course it is internal, and it is also a process of continuous collection.

Failed to perform SQL statistics

I always thought that the system would not record the SQL whose execution failed\ parsing errors, for example, I wanted to count the statements that failed because of timeout. Later, I found that MySQL would record the information completely.

Here, statements with execution errors are recorded in detail, including statements such as final execution failures (such as timeouts), syntax errors, and warnings generated during execution. Use sum_errors > 0 or sum_warnings > 0 to go to performance_schema.events_statements_summary_by_digest to filter.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select schema_name, digest_text, count_star, first_seen, last_seenfrom performance_schema.events_statements_summary_by_digestwhere sum_errors > 0 or sum_warnings > 0 order by last_seen desc

Index usage statistics

Based on performance_schema.table_io_waits_summary_by_index_usage, the system table, its statistical dimension is also "statistics of the number of rows returned by an index query".

Statistics can be made according to which indexes are most used\ least, and so on.

But there is a potential misunderstanding in this statistic:

Count_read,count_write,count_fetch,count_insert,count_update,count_delete counts the number of rows affected when an index is used on an index, and sum_timer_wait is the cumulative waiting time on that index.

If the index is used, but no data is affected (that is, the condition without the DML statement does not hit the data), count_*** will not be counted, but sum_timer_wait will.

This is a misleading place, this index obviously did not hit many times, but produced a large number of timer_wait, the index saw similar information, can not rashly delete the index.

Waiting event statistics

Any action in the MySQL database needs to wait (a certain amount of time to complete). There are more than 1000 wait events belonging to unknown categories, each version is different, and not all wait events are enabled by default.

Personally, I think that waiting for events, only for reference, does not have the diagnostic nature of the problem, even if it is re-optimized or low-load database, accumulated for a period of time, some events will still accumulate a large number of waiting events.

The waiting events of these events are not necessarily negative, for example, the lock waiting of things is bound to be generated in the process of concurrent execution, and the statistical results of these waiting events are also cumulative, simply looking at a direct value. It doesn't have any reference significance.

Unless it is collected regularly and the difference is calculated, it will be of reference significance according to the actual situation.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SUBSTRING_INDEX (NAME,'/', 1) as wait_type,COUNT (1) FROM performance_schema.setup_instrumentsGROUP BY 1 ORDER BY 2 DESC;SELECTevent_name,count_star,sum_timer_waitFROM performance_schema.events_waits_summary_global_by_event_nameWHERE event_name! = 'idle'order by sum_timer_wait desclimit 100

Finally, it is important to note that

1 the data in many system tables (views) provided by avg_***, simply look at the value itself, because it is a cumulative value, which I don't think is meaningful. In particular, MySQL needs to combine various comprehensive factors for reference.

2, any query of the system table may have a certain impact on the performance of the system itself, and do not do statistical data collection when it may have a greater negative impact on the system.

Thank you for reading this article carefully. I hope the article "how to use MySQL system database to diagnose performance load" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.