暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片

MySQL CPU性能定位

原创 Hulong Cui 2020-06-27
2560

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

CPU的5种状态

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

  • 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)。
image.png

“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操作。
image.png
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

image.png

补充:CPU使用率过高

查看CPU飙高的mysql线程,top -H -p <mysqld进程id>

#top -H -p 1821 top - 21:04:24 up 10 min, 1 user, load average: 0.00, 0.02, 0.04 Threads: 31 total, 0 running, 31 sleeping, 0 stopped, 0 zombie %Cpu(s): 90.0 us, 0.2 sy, 0.0 ni, 99.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 1867048 total, 390212 free, 1227176 used, 249660 buff/cache KiB Swap: 2097148 total, 2097148 free, 0 used. 454824 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1942 mysql 20 0 12.327g 1.082g 12004 S 90 60.8 0:00.26 mysqld 1821 mysql 20 0 12.327g 1.082g 12004 S 90 60.8 0:13.02 mysqld 1924 mysql 20 0 12.327g 1.082g 12004 S 90 60.8 0:00.00 mysqld

根据具体PID,定位问题SQL

SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info FROM performance_schema.threads a,information_schema.processlist b WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;

3.总结

以往的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性能上去得案例也存在
最后修改时间:2022-12-27 13:47:47
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论