利用MySQL系统数据库做性能负载诊断

程序员文章站 2022-06-30 08:17:31

一样了解自己管理的数据库，个人认为包含了两个方面的了解： 1，在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施（就好比要保证生活的稳定性） 2，在实例级别的来说，需要关注内存、IO、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常 ......

一样了解自己管理的数据库，个人认为包含了两个方面的了解：

1，在稳定性层面来说，更多的是关注高可用、读写分离、负载均衡，灾备管理等等high level层面的措施（就好比要保证生活的稳定性）

2，在实例级别的来说，需要关注内存、io、网络，热点表，热点索引，top sql，死锁，阻塞，历史上执行异常的sql（好比生活品质细节）

mysql的performance_data库和sys库提供了非常丰富的系统日志数据，可以帮助我们更好地了解非常细节的，这里简单地列举出来了一些常用的数据。

sys库是以较为可读化的方式封装了performance_data中的某些表，因此这些个数据来源还是performance_data库中的数据。

这里粗略列举出个人常用的一些系统数据，可以在实例级别更加清楚地了解mysql的运行过程中资源分配情况。

status中的信息

mysql的status变量只是给出了一个总的信息，从status变量上无法得知详细资源的消耗，比如io或者内存的热点在哪里，库、表的热点在哪里，如果想要知道具体的明细信息就需要系统库中的数据。

前提要开启performance_schema，因为sys库的视图是基于performance_schema的库的。

内存使用：

内存/innodb_buffer_pool使用概要

innodb_buffer_pool的使用情况summary，已知当前实例262144*16/1024 = 4096mb buffer pool,已使用23260*16/1024 363mb

innodb_buffer_pool已占用内存的明细信息，可以按照库\表的维度来统计

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;

缓存命中率统计及冷热数据变化

查询缓存命中率相关：
information_schema.innodb_buffer_pool_stats中的数据行数是跟buffer_pool_instance一致的
也就是每个一行数据来描述一个buffer_pool_instance，这里简单取和，缓存命中率取平局值的方式来统计
需要注意的是
1，modified_database_pages是实时的，就是内存中的脏页的数量，经checkpoint之后被刷新到磁盘，因此会时大时小。
2，pages_made_young和pages_not_made_young是累积的增加的，不会减少，就是mysql实例截止到目前位置，做了多少pages_not_made_young和pages_not_made_young。
3，hit_rate在负载较低的情况下，没有参考意义，这一点很奇怪，低负载情况下，会发现很多buffer_pool的hit_rate是0。
　突然意识到，hit_rate的计算，是不是以某个时间间隔为基准，统计这个时间段内请求的命中率，如果这一小段时间内没有请求，统计出来的hit_rate就是0。
4，与其他视图不通，information_schema.innodb_buffer_pool_stats中的数据会在服务重启后清零。

select 
    sum(modified_database_pages) as total_modified_database_pages,
    sum(pages_made_young) as total_pages_made_young,
    sum(pages_not_made_young) as total_pages_not_made_young,
    sum(hit_rate)/count(hit_rate)*1000 as hit_rate
from

(
    select 
        pool_id,
        pool_size,
        database_pages,
        old_database_pages,
        modified_database_pages,
        pages_made_young,
        pages_not_made_young,
        hit_rate
    from information_schema.innodb_buffer_pool_stats
)t；

参考这里对这pages_made_young和page_not_made_young，个人觉得解释的非常好。

利用MySQL系统数据库做性能负载诊断

这里低负载下的information_schema.innodb_buffer_pool_stats中的信息，hit_rate的值简直不可思议。
这个实例是4gb的内存，基本上没有访问量，hit_rate竟然出来好多值为0的情况。

利用MySQL系统数据库做性能负载诊断

相反在对当前实例做压力测试的时候，这个数据看起来才是正常的，包括modified_database_pages，pages_made_young，pages_not_made_young，hit_rate

这里用mysqlslap 做混合读写的压力测试

./mysqlslap -uroot -proot -h127.0.0.1 -p8000 --concurrency=100 --iterations=10000 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=innodb --number-of-queries=10000

用python定时打印innodb_buffer_pool_stats

import pymysql
import logging
import time
import decimal


def execute_query(conn_dict,sql):
    conn = pymysql.connect(host=conn_dict['host'],
                           port=conn_dict['port'],
                           user=conn_dict['user'],
                           passwd=conn_dict['password'],
                           db=conn_dict['db'])
    cursor = conn.cursor(pymysql.cursors.dictcursor)
    cursor.execute(sql)
    list = cursor.fetchall()
    cursor.close()
    conn.close()
    return list

def check_innodb_buffer_pool_stats(flag,conn_dict):
    result = execute_query(conn_dict, '''select 
                                            modified_database_pages,
                                            pages_made_young,
                                            pages_not_made_young,
                                            hit_rate
                                        from information_schema.innodb_buffer_pool_stats;''')
    if result:
        column = result[0].keys()
        current_row = ''
        if(flag<=0):
            for key in column:
                current_row += str(key) + "    "
            print(current_row)

        for row in result:
            current_row = ''
            for key in row.values():
                current_row += str(key) + "    "
            print(current_row)

if __name__ == '__main__':
    conn  = {'host': '127.0.0.1', 'port': my_port, 'user': 'root', 'password': '***', 'db': 'mysql', 'charset': 'utf8mb4'}
    flag = 0
    while 1>0:
        check_innodb_buffer_pool_stats(flag,conn)
        time.sleep(3)
        flag = 1

这样子看下来，这个统计还是比较正常的。

利用MySQL系统数据库做性能负载诊断

突然意识到，hit_rate的计算，是不是以某个时间间隔为基准，统计这个时间段内请求的命中率，如果这一小段时间内没有请求，统计出来的hit_rate就是0。

库\表的读写统计，逻辑层面的热点数据统计

目标表是performance_schema.table_io_waits_summary_by_table，某些文章上说是逻辑io，其实这里跟逻辑io并无关系，这个表中的字段含义是基于表，读写的到的行数的统计。

至于真正的逻辑io层面的统计，笔者目前还有不知道有哪个可用的系统表来查询。

这个库可以很清楚地看到这个表中的统计结果是怎么计算出来的。

基于表的读写的行的次数统计，这是一个累计值，单纯的看这个值本身，个人觉得意义不大，需要定时收集计算差值，才具备参考意义。
以下按照库级别统计表的读写情况。

库\表的读写统计，物理io层面的热点数据统计

按照物理io的维度统计热点数据，哪些库\表消耗了多少物理io。

这里原始系统表中的数据是一个累计统计的值，最极端的情况就是一个表为0行，却存在大量的物理读写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_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：个人不太喜欢mysql自定义的format_***函数，这个函数的初衷是好的，把一些数据（时间，存储空间）等格式化成更加可读的模式。

但是却不支持单位的参数，更多的时候想以某个固定的单位来显示，比如格式化一个的时间，格式化后根据单位大小可能会显示微妙，或者是毫秒，或者是秒，或者分钟，或者天。

比如想把时间统一格式化成秒，对不起，不支持，某些个数据不仅仅是看一眼那么简单，甚至是要读出来存档分析的，因此这里不建议也不会使用那些个format函数

top sql 统计

可以按照执行时间，阻塞时间，返回行数等等维度统计top sql。
另外可以按照时间筛选last_seen，可以统计最近某一段时间出现过的top sql

利用MySQL系统数据库做性能负载诊断

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;

需要注意的是，这个统计是按照mysql执行一个事务消耗的资源做统计的，而不是一个语句，笔者一开始懵逼了一阵子，举个简单的例子。
参考如下，这里是循环写个数据的一个存储过程，调用方式就是call create_test_data(n)，写入n条测试数据。
比如call create_test_data(1000000)就是写入100w的测试数据，这个执行过程耗费了几分钟的时间，按照笔者的测试实例情况，avg_timer_wait的维度，绝对是一个top sql。
但是在查询的时候，始终没有发现这个存储过程的调用被列为top sql，后面尝试在存储过程内部加了一个事物，然后就顺利地收集到了整个top sql.
因此说performance_schema.events_statements_summary_by_digest里面的统计，是基于事务的，而不是某一个批处理的执行时间的。

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

另外一点比较有意思的是，这个系统表是为数不多的支持truncate的，当然它在内部，也是在不断收集的一个过程。

利用MySQL系统数据库做性能负载诊断

执行失败的sql 统计

一直以为系统不会记录执行失败的\解析错误的sql，比如想统计因为超时而执行失败的语句，后面才发现，这些信息，mysql会完整地记录下来

利用MySQL系统数据库做性能负载诊断

这里会详细记录执行错误的语句，包括最终执行失败（超时之类的），语法错误，执行过程中产生了警告之类的语句。用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使用情况统计

基于performance_schema.table_io_waits_summary_by_index_usage这个系统表，其统计的维度同样是“按照某个索引查询返回的行数的统计”。

可以按照哪些索引使用最多\最少等情况进行统计。

利用MySQL系统数据库做性能负载诊断

不过这个统计有一个给人潜在一个误区：
count_read,count_write,count_fetch,count_insert,count_update,count_delete统计了某个索引上使用到索引的情况下，受影响的行数，sum_timer_wait是累计在该索引上等待的时间。
如果使用到了该索引，但是没有数据受影响（就是没有dml语句的条件没有命中数据），将count_***不会统计进来，但是sum_timer_wait会统计进来
这就存在一个容易受到误导的地方，这个索引明明没有命中过很多次，但是却产生了大量的timer_wait，索引看到类似的信息，也不能贸然删除索引。

等待事件统计

mysql数据库中的任何一个动作，都需要等待（一定的时间来完成），一共有超过1000个等待事件，分属不懂的类别，每个版本都不一样，且默认不是所有的等待事件都启用。

利用MySQL系统数据库做性能负载诊断

个人认为等待事件这个东西，仅做参考，不具备问题的诊断性，即便是再优化或者低负载的数据库，累计一段时间，某些事件仍旧会积累大量的等待事件。
这些事件的等待事件，不一定都是负面性的，比如事物的锁等待，是在并发执行过程中必然会生成的，这个等待事件的统计结果，也是累计的，单纯的看一个直接的值，不具备任何参考意义。
除非定期收集，做差值计算，根据实际情况，才具备参考意义。

利用MySQL系统数据库做性能负载诊断

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;

最后，需要注意的是，
1，mysql提供的诸多的系统表（视图）中的数据，单纯的看这个值本身，因为它是一个累计值，个人觉得意义不大，尤其是avg_***，需要结合多方面的综合因素，做参考使用。
2，任何系统表的查询，都可能对系统性能的本身造成一定的影响，不要再对系统可能产生较大负面影响的情况下做数据的统计收集。

打赏

微信扫一扫

Oracle数据库之六单行函数

六、单行函数 6.1、认识单行函数函数就是和 Java 语言之中的方法的功能是一样的，都是为了完成某些特定操作的功能支持，而在 Oracle ... [阅读全文]
【原】导入framework报错解决（以ReactiveObjC.framework为例）

1、当导入ReactiveObjC.framework后，进行编译时报错：framework not found xxx 报错如下图：解决办法： ... [阅读全文]
jQuery 源码解析(七) jQuery对象和DOM对象的互相转换

jQuery对象是一个类数组对象，它保存的是对应的DOM的引用，我们可以直接用[]获取某个索引内的DOM节点，也可以用get方法获取某个索引内的DO... [阅读全文]
MySQL语言分类——DML

DML DML的全称是Database management Language，数据库管理语言。主要包括以下操作： insert、delete、up... [阅读全文]
Redis之与python交互

Redis之与python交互，内容包括 Python操作redis方法，python操作String，redis存储session信息。其中，Py... [阅读全文]