Mysql数据库全局分析及太极后端优化实战

微信扫一扫,分享到朋友圈

Mysql数据库全局分析及太极后端优化实战

| 导语   腾讯机器学习平台太极后端数据库是自己运维的Mysql,历史原因没有用公司CDB、TDSQL等,之后还是要进行数据库迁移把db维护交给专业的人去运维,这块太极平台没有专门的dba运维出现了不少问题,如Mysql主节点硬盘故障,备机切主导致系统中断半小时;后端接口调用不合理导致循环调用数据库致使数据库cpu持续维持在高位以及前端接口数据返回缓慢等问题。这块Mysql优化就需要开发自己去多了解Mysql系统架构、性能调优相关问题,监控Mysql 机器运行状态,本文就简单介绍下Mysql系统分析思路和采用的工具。

概述

数据库是个比较大的话题,有各种各样数据库常见的关系型数据库如Mysql 、oracle、非关系型数据库,还有图数据库等。数据库性能会跟许多部分有关联,从硬件底层存储设备、操作系统、数据库配置参数、数据库架构、数据库表结构、应用层面的连接池设置、以及SQL索引等。

数据库架构

对Mysql数据库进行分析,首先需要了解MySql的系统架构,如下图所示:

从这个架构图,来看Mysql系统架构分为应用层、MySql服务层、存储引擎层。

  • 应用层,应用层是MySQL体系架构的最上层,它和其他client-server架构一样,主要包含:连接处理、用户鉴权、安全管理

  • MySQL服务层:该层是Mysql Server的核心层,提供了Mysql Server 数据库所有逻辑功能

  • 存储引擎层
    存储引擎是MySQL中具体与文件打交道的子系统,也是MySQL最有特色的地方。MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制(该机制叫存储引擎)。
    物理文件包括:redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等

SQL运行过程

知道数据库架构后,在性能分析时候需要知道这些模块的功能及运行逻辑,明白一个具体的sql所需要经历的过程:一个sql首先经过Connection Pool到达系统后,需要先进入Sql interface模块判断这个语句,是什么类型。然后通过Parser 模块进行语法与语义检查,并生成相应的执行计划;接着到Optimizer模块进行优化,判断走什么索引,执行顺序等,然后就到Cache中找数据,如果Caches中找不到数据的话,就得通过文件系统到磁盘中进行寻找。

性能分析基本监控指标

了解了mysql系统架构和mysql执行过程还不够,在进行性能分析时,需要找出mysql的问题所得先了解一些基础知识和相应的监控工具。
首先需要了解的两个Schema 分别是information_schema和performance_schema,information_schema,它们保存了数据库中的所有表、列、索引、权限、配置参数、状态参数 等信息。像我们常执行的show processlist;就来自于这个 schema 中的 processlist 表。performance_schema提供了数据库运行时的资源消耗情况,它以较低的代价收集信息, 可以提供不少性能数据。
还有在分析mysql是需要知道的两个命令:show global variables ;和show global status ;前一个用来查看配置的参数值,后一个用来查询状态值。不过这些命令只是简单的罗列信息,并没有统计分析,接下来我们介绍两个个比较好的监控工具。

全局分析:mysqlreport

show status 输出的报告是用来计算性能瓶颈的参考数据,但是数据只是简单的罗列,不好一下子看出性能问题,而mysqlreport 不像show status简单的罗列数据,而是对这些参考数据加以融合计算,整理成一个个优化参考点,然后就可以根据这个优化参考点的值以及该点的衡量标准,进行对应的调整。

linux 环境下mysqlreport安装

  • 步骤一:yum -y install perl-DBD-MySQL 依賴包

  • 步骤二:yum -y install perl-DBI #依賴包

  • 步骤三 :yum -y install mysqlreport
    在linux系统上经过这三步就安装好了这个工具。接下来就可以对数据库运行状况进行分析了。

mysqlreport使用

使用比较简单,直接执行:mysqlreport –user tesla –password xxx@2015 –host 127.0.0.1 –no-mycnf –flush-status –outfile ./result.txt 就可以把数据库整体情况保存到当前目录中。
具体命令参数查看 mysqlreport —help

mysqlreport结果分析:

  • 数据库操作报表和查询排序报表

这个表反映数据库使用情况,608每秒操作量有点大,slow 这个参数挺重要,只是因为这里设置的慢查询10s太长了,正常情况下尽量设置在1s左右,这块需要对db 进行配置,把慢查询统计设置的短些。

DMS部分告诉我们这个数据库中各种 SQL 所占的比例,这个例子中,SELECT多,要做 SQL 优化的话,肯定优先考虑SELECT语句,才会起到立竿见影的效果。

  • select and sort 查询和排序报表
    这块的报表数据具有极大的参考性,一下就能看出问题的所在, 这里的Scan(代表全表扫描)每秒48次执行全表扫描,实在是太多了,需要对语句进行修改, 也是我们后面优化的重点内容。

  • InnoDB 缓存池报表

    InnoDB 缓存池报表,Innodb Buffer Pool size 定义了Innodb 存储引擎的表数据和索引数据的最大内存缓存大小。这部分对MySQL来说很重要,这里使用已经达到100% 这种情况下就必须要增加Innodb缓存池了。这里的Read hit达到 92.57%,这个值越大越好,尽量达到100% 这里的值与Innodb buffer太小有关。

  • 连接报表

    从这里可以看出数据连接还完全够用。

  • 表锁报表

    Waited表示有多少次查询需要等待表锁定;Immediate表示有多少次查询可以立即获得表锁定,同时后面还有一个比例

    对数据库来说『等待』几乎可以肯定是一件很不好的事情,因此 Waited 的值应该要越小越好。最具有代表性的是第三个字段 (Waited 占所有 table lock 的百分比)这里是0.00%,非常好,没有发送过表锁。

  • 临时表报表

执行explain 在sql分析时出现Using temporary的状态,这意味着查询过程中需要创建临时表来存储中间数据,我们需要通过合理的索引来避免它。另一方面,当临时表在所难免时,也要尽量减少临时表本身的开销,MySQL可以将临时表创建在磁盘(Disk table)、内存(Table)以及临时文件(File)中,显然,在磁盘上创建临时表的开销最大,所以我们希望MySQL尽量不要在磁盘上创建临时表,上面分析结果来看从临时表创建在磁盘(Disk table)和临时文件(File) 上的 量级来说,还是有点偏大了,所以,可以增大tmp_table_size。
其它全局信息可以查下资料

全局分析结果

通过mysqlreport这个工具反应的结果,有以下问题需要去解决下:

  • 总体数据库操作达到600多每秒,对于内网系统用户不太多,操作有点太频繁,看下能够减少不必要的数据库操作。

  • 慢查询未开启,而且设置的时间太长长达10s,通常一个语句大于100ms 可任务需要进行优化,这里需要设置较短分析下慢查询

  • 全表扫描48.5/s 这块要分析下具体的sql写法

  • Innodb 缓存占用使用100% ,而且设置大小太小,需要增加缓存大小。

pt-query-digest 工具

作为分析mysql工具的首选,因为它可以从logs、processlist、和tcpdump 来分析MySQL的状况,logs包括slow log、general log、binlog。也可以把分析结果输出到文件中,或则把文件写到表中。分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。

安装方法

安装:centos依赖包 yum -y install perl-TermReadKey perl-Time-HiRes perl-IO-Socket-SSL.noarch
pt-query-digest –help

pt-query-digest分析 slow /bin log 时产生的报告逻辑非常清晰,并且数据也比较完整。执 行命令后就会生成一个报告,因为线网没开启slow log日志,这里我们分析下线网bin log日志

使用方法

对binlog日志进行转换:mysqlbinlog –no-defaults -vv –base64-output=DECODE-ROWS mysql-bin.000818 > mysql-bin.000818.txt
pt-query-digest –type=binlog mysql-bin.000818.txt > 818.report.log
筛选出全表扫描语句
设置数据库设置开启 log_queries_not_using_indexes=on;就会输出全表扫描语句到慢查询日志当中。值得注意的是,执行时间超过long_query_time的SQL语句也将记录到slow log中,无论该SQL语句是否使用索引。

profiling的操作步骤:查看详细执行计划

  • 步骤一 :set profiling=1; //这一步是为了打开profiling功能

  • 步骤二 :执行语句 //执行你从慢日志中看到的语句

  • 步骤三 :show profiles; //这一步是为了查找步骤二中执行的语句的ID

  • 步骤四 :show profile all for query id; //这一步是为了显示出profiling的结果

修改表结构增加索引:索引名一般是表名加字段名

show index from project_permissions;
ALTER table project_permissions ADD INDEX idex_project (project_id);
ALTER table tableName ADD INDEX indexName(columnName)
create index 索引名 on 表名(字段名1,字段名2)

分析:执行频率非常高的语句以及全表扫描

  • 1)explain SELECT project_id, modified_time, name, permissions, isGroup FROM project_permissions WHERE project_id=2076;
    根据执行计划和查询条件分析,需要对project_id 建立索引,建立索引后需要注意where条件中值的类型,这里需要把project_id 改成字符串,mysql隐式的将数值类型转换成了字符串类型

  • 2)explain SELECT id, model_name, model_type, job_id, properties, gmt_create, owner, last_execution_model, gmt_modified, published, status, module_id from mlstudio_model where job_id=13788; 数据库表记录9000条,没有增加索引,可以适当对job_id增加索引,也因为数据较小优先级比较低 ALTER table mlstudio_model ADD INDEX index_model(job_id) 有2倍性能能提升

  • 3)explain SELECT id, name, user_id, property, gmt_create, gmt_modified, appstatus, execution_info FROM mlstudio_deployed_notebooks WHERE appstatus in (10,140,20,120) ORDER BY gmt_modified desc;
    分析及方案:数据库表记录200多条,没有增加索引,会全表扫描,优先级不太高,只不过property字段和execution_info信息数据比较大,建议如果property字段没有用到 查询语句就不指定property

  • 4)explain select id, algorithm_id, version, create_time, modify_time, module_id, shared, type, source_algorithm_version_id from ti_user_algorithm_version where module_id = 813;
    解决方式:数据表记录目前较少 数据库字段比较短
    ALTER table ti_user_algorithm_version ADD INDEX index_algorithm(module_id)

  • 5)explain select id, gmt_create, gmt_modified, name, type, description, checked, permission, user_id, nick_name, config_file_name, config_file_res, module_res, module_dependencies, job_type, user_coded, has_model, icon, module_jars from mlstudio_modules where module_res=0 and type>0 and type <1001 and job_type=2;
    数据记录不多,字段值相对都比较短,查询出来占据空间相对较小 625条影响较小

  • 6)explain SELECT id, name, type, gmt_create, owner, gmt_modified, published, status, module_id, properties from mlstudio_dataset where module_id = 229;

数据记录不多,字段值相对都比较短,查询出来占据空间相对较小 55条影              响较小,对module_id加索引处理,查询很少可以不用处理

  • 7)explain select algorithm_id from ti_user_algorithm_favorite where user_id = ‘jianfehuang’ and algorithm_id = 101;
    create index algorithm on ti_user_algorithm_favorite (user_id,algorithm_id);
    解决方案 :创建联合索引,索引后速度有一定提升,只会查出一行记录对缓存占用小。目前数据库记录196条

  • 8)explain select cid, cname, cdesc, cicon, clevel, cparent, cvisible, group_concat(mid order by mname), sum(mpermission) as public_num from (select  mmc.id  as cid,  mmc.name  as cname,mmc.desc as cdesc,mmc.icon as cicon,mmc.level as clevel, mmc.parent_id as cparent,mmc.visible as cvisible,mmc.order_num as corder, mm.id  as mid,  mm.name  as mname, mm.permission as mpermission from mlstudio_module_category mmc left join mlstudio_modules mm on  mmc.id  = mm.type) as t group by cid, cname, cdesc, cicon, clevel, cparent, cvisible order by corder;

  • 9)select queuequota0_.id as id1_1_, queuequota0_.cpu as cpu2_1_, queuequota0_.gmt_create as gmt_crea3_1_, queuequota0_.gpu_map as gpu_map4_1_, queuequota0_.jizhi_business_flag as jizhi_bu5_1_, queuequota0_.memory as memory6_1_, queuequota0_.name as name7_1_, queuequota0_.gmt_modified as gmt_modi8_1_, queuequota0_.uuid as uuid9_1_ from queue_quota queuequota0_ where queuequota0_.name=‘g_teg_teslaml_appgroup04’;
    分析全表扫描:目前数据表比较小 ,数据量才155条,对性能影响较小,如果预期后面数据量变大,考虑增加索引。

  • 10)select task0_.id as id1_0_, task0_.admin_group as admin_gr2_0_, task0_.alert_group as alert_gr3_0_, task0_.business_flag as business4_0_, task0_.gmt_create as gmt_crea5_0_, task0_.creator as creator6_0_, task0_.description as descript7_0_, task0_.flag as flag8_0_, task0_.modifier as modifier9_0_, task0_.name as name10_0_, task0_.project_id as project11_0_, task0_.props as props12_0_, task0_.type as type13_0_, task0_.gmt_modified as gmt_mod14_0_, task0_.view_group as view_gr15_0_ from tj_task task0_ where task0_.project_id in (1157 , 1913 , 2078);
    分析全表扫描:目前太极任务数据表比较小 ,数据量才9条,对性能影响较小,如果预期后面数据量变大,考虑增加索引。

  • 11 查询随着某个工程下工作流越多越慢,性能影响很大 select flow_id, max(id * 1000 + status) % 1000 as last_user_drive_status from mlstudio_execution_jobflow where (drive_type = 1 or drive_type is null) and project_id in (24529) group by flow_id

  • 存在问题扫描大量数据,拷贝到临时表,在执行文件排序。

    修改为:select f.flow_id,f.status from mlstudio_model_flow t inner join mlstudio_execution_jobflow f on t.last_jobflow_id=


    f.id
     where t.project_id in (24529)

    MySQL调优之innodb_buffer_pool_size大小设置

    查询线上配置:
    sql> show global variables like ‘innodb_buffer_pool_size’;
    sql> show global status like ‘Innodb_buffer_pool_pages_data’;
    sql> show global status like ‘Innodb_page_size’;
    sql> show global status like ‘Innodb_buffer_pool_pages_total’;

    内网查询数据结果:
    Innodb_buffer_pool_pages_total | 8191
    Innodb_buffer_pool_pages_data | 8116
    Innodb_page_size | 16384
    innodb_buffer_pool_size | 134217728

    调优参考计算方法:

    val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
    val > 95% 则考虑增大 innodb_buffer_pool_size, 建议使用物理内存的75%
    val < 95% 则考虑减小 innodb_buffer_pool_size, 建议设置为:Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)
    内网计算出来:8116/8190=99% 需要加大这个数据

    数据库配置修改: 测试环境修改的/etc/my.cnf

    1、开启慢查询日志,慢查询记录为1秒 ,这个对数据库性能有1%的影响,可以开启一段时间收集一段时间数据后关闭
    slow_query_log = ON
    long_query_time = 1
    2、Innodb缓存增大
    innodb_buffer_pool_size = 2G #设置2G
    3、临时表目前64M 需要加大
    tmp_table_size = 256M;
    max_heap_table_size = 256M;

    总结

    本文简单介绍了数据库优化的相关方法,通过两个工具全局分析:mysqlreport对show status 这些参考数据加以融合计算,整理成一个个优化参考点,然后就可以根据这个优化参考点的值以及该点的衡量标准,进行对应的调整。
    pt-query-digest 工具,可以从logs、processlist、和tcpdump 来分析MySQL的状况,logs包括slow log、general log、binlog,可以借助分析结果找出问题进行优化。通过这两个工具可以在数据库配置层,对mysql进行相对比较优化的配置还可以找出性能比较慢的语句,通过profiling 详细分析sql执行的过程进行优化。

    商业化增长之路:如何设计好玩的抽奖

    微信扫一扫,分享到朋友圈

    Mysql数据库全局分析及太极后端优化实战

    电子显微镜下的Intel 14nm与台积电7nm:差别其实没那么夸张

    上一篇

    随着气温的升高 北极地区变得越来越绿了

    下一篇

    你也可能喜欢

    Mysql数据库全局分析及太极后端优化实战

    长按储存图像,分享给朋友