MYSQL使服务器内存CPU占用过高问题的分析及解决方法
方法一:
使用 show processlist 语句,查找负荷最重的 SQL 语句,优化该SQL,比如适当建立某字段的索引。
mysql>show processlist或者mysql>select * from information_schema.processlist;
方法二:
#查看慢SQL日志是否启用及日志存储位置
mysql> show variables like '%slow_query_log%';
navicat 查看mysql日志
1. show global variables like '%general%';
set global general_log = 'ON'
set global general_log = 'OFF'
#查看执行慢于多少秒的SQL会记录到日志文件中
mysql> show variables like 'long_query_time';
#配置my.ini文件(inux下文件名为my.cnf), 查找到[mysqld]区段,增加日志的配置,如下示例:
[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
long_query_time=1
#log指示日志文件存放目录;
log_slow_queries指示记录执行时间长的sql日志目录;
long_query_time指示多长时间算是执行时间长,单位秒。
方法三:优化SQL
调优参考计算方法:
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 / (1024*1024*1024)
原文链接:https://blog.csdn.net/sunny05296/article/details/78916775
问题:今天网站又卡死了,发现大量的sleep
造成sleep的原因:
1 The client program did not call mysql_close() before exiting.
2 The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
3 The client program ended abruptly in the middle of a data transfer
如果你的sleep进程数在同一时间内过多,再加上其他状态的连接,总数超过了max_connection的值,那mysql除了root用户外,就无法再继续处理任何请求无法与任何请求建立连接或者直接挂了
解决方式:
1.先要先检查你的程序是否使用的是pconnect的方式,其次,检查在页面执行完毕前是否及时调用了mysql_close()。尽量不要使用pconnect的方式,即使用mysql_connect。程序执行完毕,应该显式调用mysql_close
2.在my.cnf里面加上wait_timeout和interactive_timeout,把值设的小一些,默认情况下wait_timeout的值是8小时的时间,你可以改成1个小时,或半个小时。这样mysql会更快的杀死死连接。防止连接总数超过max_connection的值。
wait_timeout 过大,会导致MySQL里大量的SLEEP进程无法及时释放,拖累系统性能,不过设置的过小,可能会遇到“MySQL has gone away”之类的问题
把max_connection的值设置的更大,不过这样显然不妥,连接的数量越多,对你服务器的压力越大。实际上那些连接都是冗余的,把它们尽快杀死才是上策。
3.逐步分析系统的SQL查询,找到查询过慢的SQL,优化之
1)interactive_timeout:
参数含义:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
参数默认值:28800秒(8小时)
(2)wait_timeout:
参数含义:服务器关闭非交互连接之前等待活动的秒数。
在线程启动时,根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。
参数默认值:28800秒(8小时)
MySQL服务器所支持的最大连接数是有上限的,因为每个连接的建立都会消耗内存,因此我们希望客户端在连接到MySQL Server处理完相应的操作后,应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接,他们不仅会白白消耗内存,而且如果连接一直在累加而不断开,最终肯定会达到MySQL Server的连接上限数,这会报'too many connections'的错误。对于wait_timeout的值设定,应该根据系统的运行情况来判断。在系统运行一段时间后,可以通过show processlist命令查看当前系统的连接状态,如果发现有大量的sleep状态的连接进程,则说明该参数设置的过大,可以进行适当的调整小些。
sql命令:
- SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
- SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
- set global wait_timeout=120;
- SET GLOBAL interactive_timeout=120;
这种修改方式在重启mysql服务后,会失效,所以最好还是把这两个属性配置到mysql配置文件中。
linux 下修改my.cnf 文件:
- [mysqld]
- wait_timeout=120
- interactive_timeout=120