您现在的位置是:首页 >  云笔记 >  开发随笔 >  文章详情

CentOS服务器上mysql数据库优化过程分析(一)

admin   2019-02-13 17:22:20   886人已围观

       最近有一套生产系统的数据库CPU老是偏高,发现闲时也在40%-60%左右,感觉很不正常,决定优化一下,在这里记录一下自己的优化过程。希望对有类似情况的同学有所帮助。

       先使用top确定一下服务器上消耗内存最大的进程,确定没有其他程序的影响:

  

确定mysql是服务器上最消耗资源的进程后,就针对mysql进行优化,如果发现还有其他的程序消耗资源导致mysql性能变低就需要优先处理掉其他程序。明确之后可以先使用show full processlist 或者show processlist;命令查看一下哪些线程正在运行。

  

show full processlist 和show processlist的区别是show  processlist展示的结果只显示前100个字符,show full processlist是显示完整的信息。展示结果中的列表示的意思是:

id:ID标识,进程ID,如果要停止可以直接使用kill id号;例如 kill 1062;

User: 当前链接的用户

Host: 表示当前这个链接是从那个IP那个端口上发出的,上图被我马赛克了。

db:当前链接的书库名称

Command:连接状态,常见的是休眠(sleep),查询(query),连接(connect),具体说明参照下面

Time:连接持续时间,单位是秒

State:显示当前sql语句的状态,这个很重要,详情请参照下面的表格

Info:显示这个sql语句

附加说明:

Command补充说明:

sleep:线程正在等待客户发送新的请求。

query:正在执行查询或者正在将结果发送客户端

connect:连接到主服务器

详细请参照mysql手册:https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html

如果其中的sleep进程过多则通常代表资源未释放,如果是通过连接池,sleep状态应该恒定在一定数量范围内。

state的状态十分关键,下表列出state主要状态和描述:

状态 描述 
  Checking table  正在检查数据表(这是自动的)
  Closing tables  正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操
作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
  Connect Out  复制从服务器正在连接主服务器。
  Copying to tmp table on disk  由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省
内存。
  Creating tmp table  正在创建临时表以存放部分查询结果。
  deleting from main table  服务器正在执行多表删除中的第一部分,刚删除第一个表。
  deleting from reference tables  服务器正在执行多表删除中的第二部分,正在删除其他表的记录。
  Flushing tables  正在执行FLUSH TABLES,等待其他线程关闭数据表。
  Killed  发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求
。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能
死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。
  Locked  被其他查询锁住了。
  Sending data  正在处理SELECT查询的记录,同时正在把结果发送给客户端。
  Sorting for group  正在为GROUP BY做排序。
  Sorting for order  正在为ORDER BY做排序。
  Opening tables  这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK 
TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。
  Removing duplicates  正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉
那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。
  Reopen table  获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭
数据表,正尝试重新打开数据表。
  Repair by sorting  修复指令正在排序以创建索引。
  Repair with keycache  修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
  Searching rows for update  正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前
就完成了。
  Sleeping  正在等待客户端发送新请求.
  System lock  正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同
一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
  Upgrading lock  INSERT DELAYED正在尝试取得一个锁表以插入新记录。
  Updating  正在搜索匹配的记录,并且修改它们。
  User Lock  正在等待GET_LOCK()。
  Waiting for tables 该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。
然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情
况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE,
 REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。


  waiting for handler insert  INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。
  
  
  

以上表格内容来自mysql手册https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html

例如我上面的截图中有好几个Sending data,需要注意的是Sending data并不是发送数据,而是从物理磁盘获取数据的进程,如果你的影响结果集较多,那么就需要从不同的磁盘碎片去抽取数据,偶尔出现该状态连接无碍。一般而言,如果sending data连接过多,通常是某查询的影响结果集过大,也就是查询的索引项不够优化,就需要优化查询索引。

这篇文章就记录到这里,下篇文章记录使用explain 优化sql语句


希望对大家有所帮助,由onekbit.com云笔记整理发布

分享到:

编辑发布时间:2019-02-13 17:22:20