400-1513-886

全国统一服务热线

系统运维

CentOS CPU占用100%居高不下之mysql调优

发布时间:2020-04-29 12:58:42 156 次

服务器最近偶尔出现cpu百分百居高不下的情况,进行分析调优。

如下:

1、top    #查看CPU占用进程,确认mysql进程占用CPU资源过大

2、首先考虑磁盘占用问题,清理日志文件释放空间

     df -h  #查看磁盘空间占用

     du -h #查看当前文件夹或文件大小

     ls -lh  $(find / -type f -size +100M)  #查找大于100M的文件

    #检查清理nginx日志及其他日志文件。

3、清除服务器缓存 

     free -m #查看缓存的命令,其中buff/cache项为缓存占用

     #清理缓存的命令 

    echo 1 > /proc/sys/vm/drop_caches

    echo 2 > /proc/sys/vm/drop_caches

    echo 3 > /proc/sys/vm/drop_caches

4、mysql 调优,数据库数据激增,数据表需要进行维护

    show full processlist;     #文章表tbl_article数据量大,查询慢,根据state列查询状态,进行优化

    状态:

    cpu消耗过大由于慢sql造成,慢sql包括全表扫描,扫描数据量太大,内存排序,磁盘排序,锁争用等;

    执行状态为:sending data,copying to tmp table,copying to tmp table on disk,sorting result,using filesort,locked;

    sending data #正从表中查询数据,如果查询条件没有适当索引,会导致sql执行时间过长

    copying to tmp table on disk #因临时结果集太大,超过数据库规定的临时内存大小,需要拷贝临时结果集到磁盘上

    sorting result,using filesort #正在执行排序操作,排序操作会引起较多的cpu消耗,可以通过添加索引,或减小排序结果集

    locked #被其他查询锁住


    show open tables;  #看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表,Name_locked表示表名是否被锁

    show open tables from tt_data; #同上,指定数据库名为tt_data

    show status like '%lock%'; #查看服务器状态

    show engine innodb status\G; #查看innodb引擎的运行时信息

    show variables like ‘%timeout%’; #查看服务器配置参数。


    查看表索引:

    show index from  tbl_article;   #查看表中索引,根据查询语句进行优化

    ALTER TABLE tbl_article ADD INDEX union_search (cateid,isopen,status,isrec,ishot);   #对查询中使用的字段建立联合索引,按照查询语句中字段的查询顺序排列,提升查询效率

    ALTER TABLE tbl_article ADD FULLTEXT fulltext_search (title, content);   #对标题及内容字段建立全文检索索引,关于全文检索索引配置和使用下面

    ALTER TABLE tbl_article RENAME old_index_name TO new_index_name; #索引名为了便于区分,mysql5.7及以上版本可以通过此命令修改索引名

    ALTER TABLE tbl_article DROP INDEX  old_index_name; #mysql5.7以下版本可以通过此命令修改索引名

    ALTER TABLE tbl_article ADD INDEX  new_index_name(column_name); #mysql5.7以下版本可以通过此命令修改索引名

    REPAIR TABLE  tbl_article  QUICK;   


5.关于全文检索

   SHOW VARIABLES LIKE 'ft_min_word_len';#查询默认ft_min_word_len = 4,退出mysql,修改mysql配置

   cat /etc/my.cnf  #修改 ft_min_word_len = 2,保存后重启MYSQL,并修复全文索引


6.使用MySQL分页存储过程

/***************************************************** 

MySQL分页存储过程 

*****************************************************/  

DROP PROCEDURE IF EXISTS pr_pager;  

CREATE PROCEDURE pr_pager(  

    IN     p_table_name        VARCHAR(1024),        /*表名*/  

    IN     p_fields            VARCHAR(1024),        /*查询字段*/  

    IN     p_page_size         INT,                  /*每页记录数*/  

    IN     p_page_now          INT,                  /*当前页*/  

    IN     p_order_string      VARCHAR(128),         /*排序条件(包含ORDER关键字,可为空)*/    

    IN     p_where_string      VARCHAR(1024),        /*WHERE条件(包含WHERE关键字,可为空)*/      

    OUT    p_out_rows          INT                   /*输出记录总数*/      

)  

NOT DETERMINISTIC  

SQL SECURITY DEFINER  

COMMENT '分页存储过程'  

      

BEGIN  

  

    /*定义变量*/  

    DECLARE m_begin_row INT DEFAULT 0;  

    DECLARE m_limit_string CHAR(64);  

  

    /*构造语句*/      

    SET m_begin_row = (p_page_now - 1) * p_page_size;  

    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);  

      

    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);  

    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);  

  

    /*预处理*/  

    PREPARE count_stmt FROM @COUNT_STRING;  

    EXECUTE count_stmt;  

    DEALLOCATE PREPARE count_stmt;  

    SET p_out_rows = @ROWS_TOTAL;  

  

    PREPARE main_stmt FROM @MAIN_STRING;  

    EXECUTE main_stmt;  

    DEALLOCATE PREPARE main_stmt;  

      

END;  

扫码进入小程序

公司名称:广州统天网络科技有限公司

公司地址:广州市白云区石井镇潭村水闸街6号

公司电话:400-1513-886

公司邮箱:info@tongtian.tech