| 导语 腾讯机器学习平台太极后端数据库是自己运维的Mysql,历史原因没有用公司CDB、TDSQL等,之后还是要进行数据库迁移把db维护交给专业的人去运维,这块太极平台没有专门的dba运维出现了不少问题,如Mysql主节点硬盘故障,备机切主导致系统中断半小时;后端接口调用不合理导致循环调用数据库致使数据库cpu持续维持在高位以及前端接口数据返回缓慢等问题。这块Mysql优化就需要开发自己去多了解Mysql系统架构、性能调优相关问题,监控Mysql 机器运行状态,本文就简单介绍下Mysql系统分析思路和采用的工具。
1
概述
数据库是个比较大的话题,有各种各样数据库常见的关系型数据库如Mysql 、oracle、非关系型数据库,还有图数据库等。数据库性能会跟许多部分有关联,从硬件底层存储设备、操作系统、数据库配置参数、数据库架构、数据库表结构、应用层面的连接池设置、以及SQL索引等。
数据库架构
对Mysql数据库进行分析,首先需要了解MySql的系统架构,如下图所示:
从这个架构图,来看Mysql系统架构分为应用层、MySql服务层、存储引擎层。
SQL运行过程
知道数据库架构后,在性能分析时候需要知道这些模块的功能及运行逻辑,明白一个具体的sql所需要经历的过程:一个sql首先经过Connection Pool到达系统后,需要先进入Sql interface模块判断这个语句,是什么类型。然后通过Parser 模块进行语法与语义检查,并生成相应的执行计划;接着到Optimizer模块进行优化,判断走什么索引,执行顺序等,然后就到Cache中找数据,如果Caches中找不到数据的话,就得通过文件系统到磁盘中进行寻找。
2
性能分析基本监控指标
了解了mysql系统架构和mysql执行过程还不够,在进行性能分析时,需要找出mysql的问题所得先了解一些基础知识和相应的监控工具。 首先需要了解的两个Schema 分别是information_schema和performance_schema,information_schema,它们保存了数据库中的所有表、列、索引、权限、配置参数、状态参数 等信息。像我们常执行的show processlist;就来自于这个 schema 中的 processlist 表。performance_schema提供了数据库运行时的资源消耗情况,它以较低的代价收集信息, 可以提供不少性能数据。 还有在分析mysql是需要知道的两个命令:show global variables ;和show global status ;前一个用来查看配置的参数值,后一个用来查询状态值。不过这些命令只是简单的罗列信息,并没有统计分析,接下来我们介绍两个个比较好的监控工具。
3
全局分析:mysqlreport
show status 输出的报告是用来计算性能瓶颈的参考数据,但是数据只是简单的罗列,不好一下子看出性能问题,而mysqlreport 不像show status简单的罗列数据,而是对这些参考数据加以融合计算,整理成一个个优化参考点,然后就可以根据这个优化参考点的值以及该点的衡量标准,进行对应的调整。
linux 环境下mysqlreport安装
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语句,才会起到立竿见影的效果。
InnoDB 缓存池报表,Innodb Buffer Pool size 定义了Innodb 存储引擎的表数据和索引数据的最大内存缓存大小。这部分对MySQL来说很重要,这里使用已经达到100% 这种情况下就必须要增加Innodb缓存池了。这里的Read hit达到 92.57%,这个值越大越好,尽量达到100% 这里的值与Innodb buffer太小有关。
从这里可以看出数据连接还完全够用。
对数据库来说『等待』几乎可以肯定是一件很不好的事情,因此 Waited 的值应该要越小越好。最具有代表性的是第三个字段 (Waited 占所有 table lock 的百分比)这里是0.00%,非常好,没有发送过表锁。
执行explain 在sql分析时出现Using temporary的状态,这意味着查询过程中需要创建临时表来存储中间数据,我们需要通过合理的索引来避免它。另一方面,当临时表在所难免时,也要尽量减少临时表本身的开销,MySQL可以将临时表创建在磁盘(Disk table)、内存(Table)以及临时文件(File)中,显然,在磁盘上创建临时表的开销最大,所以我们希望MySQL尽量不要在磁盘上创建临时表,上面分析结果来看从临时表创建在磁盘(Disk table)和临时文件(File) 上的 量级来说,还是有点偏大了,所以,可以增大tmp_table_size。 其它全局信息可以查下资料
4
全局分析结果
通过mysqlreport这个工具反应的结果,有以下问题需要去解决下:
pt-query-digest 工具
作为分析mysql工具的首选,因为它可以从logs、processlist、和tcpdump 来分析MySQL的状况,logs包括slow log、general log、binlog。也可以把分析结果输出到文件中,或则把文件写到表中。分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,可以借助分析结果找出问题进行优化。
安装方法
下载 :https://www.percona.com/downloads/percona-toolkit/LATEST/
安装: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的操作步骤:查看详细执行计划
修改表结构增加索引:索引名一般是表名加字段名
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)
分析:执行频率非常高的语句以及全表扫描
数据记录不多,字段值相对都比较短,查询出来占据空间相对较小 55条影 响较小,对module_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;
5
总结
本文简单介绍了数据库优化的相关方法,通过两个工具全局分析:mysqlreport对show status 这些参考数据加以融合计算,整理成一个个优化参考点,然后就可以根据这个优化参考点的值以及该点的衡量标准,进行对应的调整。 pt-query-digest 工具,可以从logs、processlist、和tcpdump 来分析MySQL的状况,logs包括slow log、general log、binlog,可以借助分析结果找出问题进行优化。通过这两个工具可以在数据库配置层,对mysql进行相对比较优化的配置还可以找出性能比较慢的语句,通过profiling 详细分析sql执行的过程进行优化。