搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 数据和云 > MySQL CPU性能定位

MySQL CPU性能定位

数据和云 2020-07-05

墨墨导读:经常会看到看到cpu 使用率非常高的情况。在这种情况下,资源的使用监控分析才是性能故障分析的根本首要任务,通过这些分析,理解服务器如何运行,资源损耗在哪些方面对问题进行故障诊断是非常有价值有意义的。


经常会看到看到cpu 使用率非常高的情况。在这种情况下,资源的使用监控分析才是性能故障分析的根本首要任务,通过这些分析,理解服务器如何运行,资源损耗在哪些方面对问题进行故障诊断是非常有价值有意义的。MySQL那些情况,会导致cpu上升。


CPU的5种状态


在linux平台下cpu存在5种状态使用组合。


MySQL CPU性能定位
  • us:用户空间占用CPU百分比

  • sy:内核空间占用CPU百分比

  • ni:用户进程空间内改变过优先级的进程占用CPU百分比

  • id:空闲CPU百分比

  • wa: 等待输入输出的CPU时间百分比

  • hi: 硬件中断

  • si: 软件中断

  • st: 实时

备注:从上述情况介绍来看,sy系统和ni&si软硬中断,基本系统自动控制,干涉部分不是太多.us,id,wa有一定的优化空间,有效的使用资源。

通过上述介绍,已经了解了cpu的基础,下面看看MySQL方面cpu的表现。

MySQL常见CPU案例


以往的MySQL案例中,因为使用上的一些问题,经常会导致高CPU使用率上升情况:这里包括连接数增加、执行差效率的查询SQL、哈希连接或多表合并连接、写和读IO慢、参数设置不合理等。

1. SQL语句
那些常见的SQL语句会导致cpu上升 先从最直观的SHOW PROCESSLIST,查询时间长、运行状态(State列)
“Sending data”、
“Copying to tmp table”、
“Copying to tmp table on disk”、
“Sorting result”、
“Using filesort”等都可能是有性能问题的查询(SQL)。
MySQL CPU性能定位

“Sending data”官网解释:

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

状态的含义,原来这个状态的名称很具有误导性,所谓的“Sending data”并不是单纯的发送数据,而是包括“收集 + 发送 数据”。

体现在:
1. 没有使用索引
2. mysql索引表结构,要是没有使用主键查询的话,需要进行回表操作,在返回客户端。
3. 返回的行数太多,需要频繁io交互

Copying to tmp table,Copying to tmp table on disk:官网解释:

Copying to tmp table
The server is copying to a temporary table in memory.
Copying to tmp table on disk
The server is copying to a temporary table on disk. The temporary result set has become too large

整体来说生成临时表内存空间,落磁盘临时表,临时表使用太
体现在 多表join,buffer_size设置不合理,alter algrithem copy等方式

Sorting result:

For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

结果集使用大的排序,基本上SQL语句上order by 字段上没有索引

上述的情况大量堆积,就会发现CPU飙升的情况,当然也有并发量太高的情况。

优化方向:
1.添加索引,组合索引,坚持2张表以内的join方式 这样查询执行成本就会大幅减少。
2.隐私转换避免,系统时间函数的调用避免
3.相关缓存大小设置:join_buffer_size,sort_buffer_size,read_buffer_size ,read_rnd_buffer_size ,tmp_table_size。

在紧急情况下,无法改动下,通过参数控制并发度,执行时间 innodb_thread_concurrency ,max_execution_time都是有效的临时控制手段。

2. SQL语句
CPU对于IO方面的处理方式如下:等待的IO队列信息,会放置CPU里进行spin操作。
MySQL CPU性能定位
MySQL事务关联操作方面有redo, undo, binlog日志。但实际InnoDB实现方式是同步IO和异步IO两种文件读写方式

1.对于读操作,通常用户线程触发的数据请求都是同步读,其他后台线程触发的是异步读。
同步读写操作通常由用户线程来完成,当用户线程执行一句SQL时,如果请求的数据页不在buffer pool中,就需要将文件中的数据页加载到buffer pool中,如果IO有瓶颈,响应延迟,那么该线程就会被阻塞。

2.对于写操作,InnoDB是WAL(Write-Ahead Logging)模式,先写日志,延迟写数据页然后在写入磁盘,这样保证数据的安全性 数据不丢失;
异步写,主要在下面场景下触发

  • binlog,undo,redo log 空间不足时 ;

  • 当参数innodb_flush_log_at_trx_commit,sync_binlog设置为1时,每次事务提交都会做一次fsync,相当于是同步写;

  • master线程每秒做一次redo fsync;

  • Checkpoint

  • undo,binlog切换时

  • Page cleaner线程负责脏页的刷新操作,其中double write buffer的写磁盘是同步写, 数据文件的写入是异步写。

大量的io堆积,等待的状态下,都会导致CPU使用率上升。
log方面多注意以下方面配置:
1.相关mysql参数 innodb_flush_log_at_trx_commit ,sync_binlog,innodb_io_capacity ,sync_relay_log的参数合理设置。
2.独立表空间 (innodb_file_per_table),日志文件伸缩大小,临时表使用,
3.尽量使用IOPS高的硬件设备


SQL语句定位cpu核


通过sys库定位当前执行pid, 先对应3247

mysql> select thd_id,conn_id,user,pid,program_name,command,current_statement from sys.processlist where conn_id>0 and pid>0;+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+| thd_id | conn_id | user | pid | program_name | command | current_statement |+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+| 33 | 3 | root@localhost | 3247 | mysql | Query | select thd_id,conn_id,user,pid ... list where conn_id>0 and pid>0 |+--------+---------+----------------+------+--------------+---------+-------------------------------------------------------------------+
  • ps方式

通过ps工具查看对应的cpu是在哪个核上执行

[root@ss30 ~]# ps -o pid,psr,comm -p 3247 PID PSR COMMAND 3247 3 mysql


输出表示进程的 PID 为 3247(名为”mysql”)目前在编号为 3的CPU 上运行着。如果该过程没有被固定,PSR 列会根据内核可能调度该进程到不同CPU而改变显示。

  • top方式
    通过top方式查看对应的cpu是在哪个核上执行,
    按下 “F”键->使用上下键选择P = Last Used Cpu,并按下空格键,出现 “*”即可->ESC 退出,这时候top界面上的P列就是对应的CPU信息.

[root@ss30 ~]# top -p 3247

MySQL CPU性能定位

总结


以往的CPU案例中,优化的方向:

  • 对于MySQL硬件环境资源,建议CPU起步8核开始,SSD硬盘;

  • 索引 ,合理设计表结构,优化SQL。

  • 读写分离,将对数据一致性不敏感的查询转移到只读实例上,分担主库压力。

  • 对于由应用负载高导致的 CPU 使用率高的状况,从应用架构、实例规格等方面来解决。

  • 使用 Memcache 或者 Redis缓存技术,尽量从缓存中获取常用的查询结果,减轻数据库的压力。

MySQL性能测试CPU优化方向:

  • 系统参数:磁盘调度算,SHELL资源限制,numa架构,文件系统ext4,exfs

  • 刷新mysql log相关刷新参数:
    临近页(innodb_flush_neighbors)
    死锁检查机制(innodb_deadlock_detect),
    双1刷新:sync_binlog,innodb_flush_log_at_trx_commit

  • 并发参数: innodb_buffer_pool_instances, innodb_thread_concurrency 等

  • 因为一些服务器的特性,导致cpu通道 和 内存协调存在一些问题,导致cpu性能上去得案例也存在

墨天轮原文链接:https://www.modb.pro/db/26704(复制到浏览器中打开或者点击“阅读原文”)


推荐阅读:


数据和云

ID:OraNews

如有收获,请划至底部,点击“在看”,谢谢!


点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方


请备注:云和恩墨大讲堂

  点个“在看”
你的喜欢会被看到❤

版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《MySQL CPU性能定位》的版权归原作者「数据和云」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注数据和云微信公众号

数据和云微信公众号:OraNews

数据和云

手机扫描上方二维码即可关注数据和云微信公众号

数据和云最新文章

精品公众号随机推荐