Method of using MySQL system database for performance load diagnosis

A master once said that you should know the database you manage as well as you know your wife. I personally think this includes two aspects of understanding:

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

2. At the instance level, you need to pay attention to memory, IO, network, hot tables, hot indexes, top SQL, deadlocks, blocking, and SQL with historical abnormal execution (like quality of life details). MySQL's performance_data library and sys library provide very rich system log data, which can help us better understand the details. Here is a brief list of some commonly used data.

The sys library encapsulates some tables in performance_data in a more readable way, so the source of these data is still the data in the performance_data library.

Here is a rough list of some commonly used system data, which can help you understand the resource allocation during MySQL operation more clearly at the instance level.

Information in Status

The MySQL status variable only gives a general information. You cannot know the detailed resource consumption from the status variable, such as where the IO or memory hot spots are, where the database and table hot spots are. If you want to know the specific detailed information, you need the data in the system library.

The prerequisite is to enable performance_schema , because the views of the sys library are based on the performance_schema library.

Memory usage:

Memory/innodb_buffer_pool usage

Summary of innodb_buffer_pool usage: It is known that the current instance has 262144*16/1024 = 4096MB buffer pool, and 23260*16/1024 363MB has been used

Detailed information about the memory occupied by innodb_buffer_pool, which can be counted by library\table dimensions

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT 
  database_name,
  SUM(compressed_size)/1024/1024 AS allocated_memory,
  SUM(data_size)/1024/1024 AS data_memory,
  SUM(is_hashed)*16/1024 AS is_hashed_memory,
  SUM(is_old)*16/1024 AS is_old_memory
FROM 
(
 SELECT 
  case when INSTR(TABLE_NAME,'.')>0 then replace(SUBSTRING(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,0) is_old
 FROM information_schema.innodb_buffer_page
 WHERE TABLE_NAME IS NOT NULL
)
GROUP BY database_name
ORDER BY allocated_memory DESC
LIMIT 10;

Database and table read and write statistics, hot data statistics at the logical level

The target table is performance_schema.table_io_waits_summary_by_table . Some articles say it is logical IO. In fact, 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 currently don't know which available system table to query. This library can clearly show how the statistical results in this table are calculated.

This is a cumulative value based on the number of rows read and written in the table. I personally think that simply looking at this value itself is not very meaningful. It is necessary to collect and calculate the difference regularly to make it meaningful for reference.

The following table shows the reading and writing status at the library level.

Read and write statistics of libraries and tables, and hot data statistics at the physical IO level

Hot spot data is counted based on the physical IO dimension to determine which libraries and tables consume how much physical IO. The data in the original system table here 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 IOs.

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_written
FROM
(
 SELECT 
  substring(REPLACE(file, '@@datadir/', ''),1,instr(REPLACE(file, '@@datadir/', ''),'/')-1) 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 
)t
GROUP BY database_name
ORDER BY total_read_written DESC;

ps: I personally don’t like MySQL’s custom format_*** function. The original intention of this function is good, and it formats some data (time, storage space, etc.) into a more readable mode. However, it does not support unit parameters. Most of the time, we want to display it in a fixed unit. For example, when formatting a time, it may be displayed in microseconds, milliseconds, seconds, minutes, or days according to the size of the unit. For example, if you want to format the time uniformly into seconds, sorry, it is not supported. Some data is not just as simple as taking a look, and even needs to be read out for archiving and analysis. Therefore, it is not recommended and will not be used here.

TOP SQL Statistics

You can count the top SQL statements by execution time, blocking time, number of returned rows, and so on.
In addition, you can filter by time last_seen, you can count the top SQL that appeared in a certain period of time recently.

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_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE 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_sent
DESC
limit 10;

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

For reference, here is a stored procedure that writes data in a loop. The calling method is call create_test_data(N)， to write N test data.
For example, call create_test_data(1000000) writes 1 million test data. This execution process takes several minutes. According to the author's test instance, the dimension of avg_timer_wait is definitely a TOP SQL.

However, when querying, I never found that the call of this stored procedure was listed as the TOP SQL. Later, I tried to add a transaction inside the stored procedure, and then successfully collected the entire TOP SQL.

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

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
 IN `loopcnt` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
 -- START TRANSACTION; 
  while loopcnt>0 do
   insert into test_mrr(rand_id,create_date) values ​​(RAND()*100000000,now(6));
   set loopcnt=loopcnt-1;
  end while;
 -- commit;
END

Another interesting point is that this system table is one of the few that supports truncation. Of course, it is also in a continuous collection process internally.

Statistics of failed SQL executions

I always thought that the system would not record SQL statements that failed to execute or parsed incorrectly. For example, I wanted to count the statements that failed to execute due to timeout. Later I found that MySQL would record this information completely.

Statements with execution errors are recorded in detail here, including final execution failure (such as timeout), syntax errors, and warnings generated during execution. Use sum_errors>0 or sum_warnings>0去performance_schema.events_statements_summary_by_digest .

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select 
 schema_name,
 digest_text,
 count_star,
 first_seen,
 last_seen
from performance_schema.events_statements_summary_by_digest
where sum_errors>0 or sum_warnings>0 
order by last_seen desc;

Index usage statistics

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

Statistics can be collected based on which indexes are used most or least.

However, this statistic has a potential misunderstanding:
count_read,count_write,count_fetch,count_insert,count_update,count_delete count the number of rows affected when an index is used, and sum_timer_wait is the cumulative waiting time on the index.

If the index is used but no data is affected (that is, no data is hit by the conditions of the DML statement), count_*** will not be counted, but sum_timer_wait will be counted. This is a misleading point. The index has not been hit many times, but a large number of timer_wait are generated. If the index sees similar information, the index cannot be deleted rashly.

Wait event statistics

Any action in the MySQL database requires waiting (a certain amount of time to complete). There are more than 1,000 wait events in total, which belong to different categories. Each version is different, and not all wait events are enabled by default.

Personally, I think that wait events are only for reference and are not diagnostic of problems. Even if the database is optimized or low-loaded, certain events will still accumulate a large number of wait events over a period of time.

The waiting events of these events are not necessarily all negative. For example, the lock waiting of things is bound to be generated during the concurrent execution process. The statistical results of this waiting event are also cumulative. Simply looking at a direct value has no reference significance.
Unless data is collected regularly and difference calculations are done based on actual conditions, it will only be of reference value.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT SUBSTRING_INDEX(NAME, '/', 1) as wait_type,COUNT(1) 
FROM performance_schema.setup_instruments
GROUP BY 1 
ORDER BY 2 DESC;


SELECT
event_name,
count_star,
sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'idle'
order by sum_timer_wait desc
limit 100;

Finally, it is important to note that

1. For the data in many system tables (views) provided by MySQL, simply looking at the value itself is not very meaningful because it is a cumulative value, especially avg_***, which needs to be combined with many comprehensive factors for reference.
2. Any query on the system table may have a certain impact on the system performance itself. Do not collect data statistics when it may have a significant negative impact on the system.

Summarize

The above is the method I introduced to you for using the MySQL system database to perform performance load diagnosis. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!