A master once said that he knows the database he manages as well as his wife. Personally, he thinks it contains two aspects of understanding:

1. At the level of stability, more attention is paid to high-level measures such as high availability, separation of reading and writing, load balancing, disaster preparedness management and so on.

2. At the instance level, hotspot libraries, hot tables, hot index, top sql, deadlocks, blockages, and historically executed abnormal SQL (like quality of life details) at the physical/logical IO or memory level.

MySQL's performance_data and sys libraries provide very rich system log data, which can help us better understand the very details. Here are some commonly used data.

sys libraries encapsulate some tables in performance_data in a more readable way, so these data sources are also data in performance_data libraries.

Here is a rough list of some common personal system data, at the instance level, a clearer understanding of the operation of MySQL.

 

Information in Status

MySQL status variable only gives a general information. From status variable, we can not know the consumption of detailed resources, such as IO or memory hotspots, library and table hotspots. If we want to know specific details, we need the data in the system library.

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

 

Memory usage:

Memory/innodb_buffer_pool usage profile

innodb_buffer_pool usage summary, known as the current instance 262144*16/1024 = 4096MB buffer pool, has used 23260*16/1024 363MB

Detailed information about 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/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
) t
GROUP BY database_name
ORDER BY allocated_memory DESC
LIMIT 10;

 

Reading and Writing Statistics of DatabaseTable and Hot Point Data Statistics at Logical Level

The target table is performance_schema.table_io_waits_summary_by_table, which is logically IO in some articles. In fact, it has nothing to do with logically IO. The field meaning in this table is based on the statistics of the number of rows read and written by the table.

As for the real logic IO level statistics, I do not know which system tables are available to query at present.

This library can clearly see how the statistics in this table are calculated.

Based on the statistics of the number of rows read and written by the table, this is a cumulative value. Simply looking at the value itself, the individual feels that it is of little significance. It is necessary to collect and calculate the difference regularly in order to have reference significance.
The following is the reading and writing of statistical tables at the library level.

 

Reading and Writing Statistics of LibraryTable, Hot Point Data Statistics at Physical IO Level

According to the dimension statistics of 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 a table with 0 rows, but there are a lot of physical read-write IO.

TOP SQL Statistics

Topsql can be counted in terms of execution time, blocking time, number of rows returned, etc.
In addition, last_seen can be filtered according to the time, and top sql can be counted in a recent 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_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, rather than a statement. The author was confused for a while at first, to give a simple example.
Reference to the following, here is a stored procedure that writes data in a loop. The calling method is call create_test_data(N), which writes N test data.
For example, call create_test_data(1000000) is to write 100W test data. This execution process takes several minutes. According to the author's test example, the dimension of avg_timer_wait is absolutely a TOP SQL.
However, during the query, it was never found that the call of this stored procedure was listed as TOP SQL. Later, we tried to add something inside the stored procedure, and then we collected the whole TOP SQL smoothly.
So 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`@`%` 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 truncate-enabled tables, but it is also a process of continuous collection internally.

 

Index usage statistics

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

Statistics can be made according to which indexes are used most or least.

However, there is a potential misunderstanding in this statistics:
count_read,count_write,count_fetch,count_insert,count_update,count_delete count the number of rows affected when an index is used on the index. sum_timer_wait is the accumulated waiting time on the index.
If the index is used, but no data is affected (i.e., no DML statement condition, no hit data), count *** will not be counted, but sum_timer_wait will be counted.
There is a misleading place. This index has not hit many times, but it produces a lot of timer_wait. When the index sees similar information, it can not delete the index rashly.

 

Waiting event statistics

Every action in MySQL database needs to wait (a certain amount of time to complete). There are more than 1000 waiting events, which belong to unknown categories. Each version is different, and not all waiting events are enabled by default.

Personally, I think that the waiting event is only for reference and does not have diagnostic value. Even if the database is re-optimized or low-load, 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, such as the lock waiting of things, which will inevitably be generated in the process of concurrent execution. The statistical results of the waiting events are also cumulative. Simply looking at a direct value does not have any reference significance.
Unless it is collected regularly and calculated differentially, it will be of referential 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_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 should be noted that:
1. The data provided by MySQL in many system tables (views) simply look at the value itself, because it is a cumulative value, which is of little significance to individuals, especially avg ***. It needs to be used for reference in combination with various comprehensive factors.
2. Any query of system tables may have some impact on the system performance itself. Do not collect data statistically when the system may have a greater negative impact.

 

Reference resources:

http://blog.woqutech.com/

https://www.cnblogs.com/cchust/p/5061131.html