内容字号:默认大号超大号

段落设置:段首缩进取消段首缩进

字体设置:切换到微软雅黑切换到宋体

Mysql服务运行一段时间后就变慢了,怎么查找原因?

2017-10-24 23:29 出处:投稿 人气: 评论(0

我们的Mysql服务运行一段时间后,不知什么原因就变慢了,怎么查找原因呢?

一、关键性指标

在数据库性能评测中,有几项指标很重要,用它来评估数据库的能力,不是他们能起着多么关键的作用,而是他们能够较为明确的代表数据库在某些方面的能力。

1.IOPS

IOPS:Input/Output operation Per Second, 每秒处理的IO请求次数。
我们知道I/O就是磁盘的读写能力,比如每秒读 300M,写 200M,这个即数据的吞吐量(I/O能力的另一个关键指标),但是 IOPS 指的可不是读写的数据吞吐量,IOPS 指的是每秒能够处理的 I/O 请求次数

如果想I/O 系统响应够快,那么 IOPS 越高越好,因为IOPS 和硬件有关,所以,要提高IOPS,就目前来看基本只能拼硬件,传统方案是使用多块磁盘通过 RAID 条带后,使 I/O 读写能力获得提升,我们也可以使用固态硬盘SSD来提升IOPS,不过固态硬盘成本可能比较大。

2.QPS

QPS:Query Per Second,每秒请求(查询)次数。
这个参数非常重要,可以直观的反映系统的性能,这就像IOPS衡量磁盘每秒钟能接收多少次请求。

我们可以在MySQL命令行模式下执行 status 命令,返回的最后一行输出信息中就包含 QPS 指标。

3.TPS

TPS:Transaction Per Second,每秒事务数。
TPS参数MySQL原生没有提供,如果需要我们自己算,可以利用计算的公式:

TPS = (Com_commit + Com_rollback) / Seconds

这个公式有两个状态变量,分别代表提交次数和回滚次数,Seconds 就是我们定义的时间间隔。

二、TPCC测试关键性指标

TPCC-MySQL 由Percona基于TPCC规范开发的一套MySQL基准测试程序,我们使用这套工具来测试前面的三个重要指标。

1.TPCC工具安装及使用

具体的安装,可以看这这两篇博文 mysql压力测试工具tpcc-mysql安装测试使用,mysql性能测试-tpcc,TPCC更能模拟线上业务。

三、数据库参数配置优化

如果数据库参数配置合理,则可以大大的提高运行效率,即最大化利用系统资源。

1.连接相关参数

1.1 max_connections

max_connections:指定 MySQL 服务端最大并发连接数,值得范围从 1~10 万,默认值为151.
这个参数非常重要,因为它决定了同时最多能有多少个会话连接到 MySQL 服务。设定该参数时,根据数据库服务器的配置和性能,一般将参数值设置在 500~2000 都没太大的问题。

1.2 max_connect_errors

max_connect_errors:指定允许连接不成功的最大尝试次数,值得范围从 1~2^64 之间,在 5.6.6 版本默认值是 100。

一定不要忽视这个参数,如果尝试连接的错误数量超过该参数指定值,则服务器就不再允许新的连接,没错,就是拒绝连接,尽管 MySQL 仍在提供服务,但无法创建新的连接了。可以使用 FLUSH HOSTS,使状态清零或重新启动数据库服务,不过这个代价太高了,一般不会这么干,所以,这个参数的默认值太小,这里建议将之设置为 10 万以上的量级

1.3 interactive_timeout 和 wait_timeout

这两个参数都与连接会话的自动超时断开有关,前者用于指定关闭交互连接前等待的时间,后者用于指定关闭非交互连接前的等待时间,单位均是秒,默认值均为 28800,即 8 个小时。

1.4 skip-name-resolve

skip-name-resolve:可以将其简单的理解为禁用 DNS 解析,注意啊,这个是服务端的行为,连接时不检查客户端主机名,而只使用IP。如果制定了该参数,那么在创建用户及授予权限时,HOST 列必须是IP而不能是主机名。建议启用该参数,对于加快网络连接有一定的帮助,等于是跳过了主机名的解析。

1.5 back_log

back_log:指定 MySQL 连接请求队列中存放的最大连接请求数量,在 5.6.6 版本之,默认是 50 个,最大值不超过 65535。在 5.6.6 版本之,默认值为 -1,表示由MySQL自动调节,所谓自行调节其实也有规则,即 50+(max_connections/5)。

该参数主要应对短时间内有大量的连接请求,MySQL 主线程无法及时为每一个连接请求分配(或创建)连接的线程,怎么办呢,它也不能直接拒绝,于是就将一部分请求放到等待队列中待处理,这个等待队列的长度就是 back_log 的参数值,若等待队列也被放满了,那么后续的连接请求才会被拒绝。

2.文件相关参数

2.1 sync_binlog

sync_binlog:指定同步二进制日志文件的平率,默认为0.
如果要性能,则指定该参数为0,为了安全起见则指定该参数值为 1.

2.2 expire_logs_day

expire_logs_day:指定设置二进制日志文件的生命周期,超出则将自动被删除,参数值以天为单位,值得范围从0~99,默认值是0,建议将该参数设置为 7~14 之间,保存一到两周就足够了。

2.2 max_binlog_size

max_binlog_size: 指定二进制日志的大小,值得范围从 4KB~1GB,默认为 1GB。

3.缓存控制参数

3.1 thread_cache_size

thread_cache_size:指定MySQL为快速重用而缓存的线程数量。值得范围从 0~16384,默认值为0.
一般当客户端中断连接后,为了后续再有连接创建时,能够快速创建成功,MySQL 会将客户端中断的连接放入缓存区,而不是马上中断释放资源。这样当有新的客户端请求连接时,就可以快速创建成功。因此,本参数最好保持一定的数量,建议设置在 300~500 之间均可.另外,线程缓存的命中率也是一项比较重要的监控指标,计算规则为(1-Threads_created/Connections)* 100%,我们可以通过该指标来优化和调整thread_cache_size参数。

3.2 query_cache_type

sql_cache意思是说,将查询结果放入查询缓存中。
sql_no_cache意思是查询的时候不缓存查询结果。
sql_buffer_result意思是说,在查询语句中,将查询结果缓存到临时表中。

这三者正好配套使用。sql_buffer_result将尽快释放表锁,这样其他sql就能够尽快执行。

使用 FLUSH QUERY CACHE 命令,你可以整理查询缓存,以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢?

是根据query_cache_type这个变量来决定的。

这个变量有三个取值:0,1,2,分别代表了off、on、demand
mysql默认为开启 on

意思是说,如果是0,那么query cache 是关闭的。
如果是1,那么查询总是先到查询缓存中查找,即使使用了sql_no_cache仍然查询缓存,因为sql_no_cache只是不缓存查询结果,而不是不使用查询结果。

select count(*) from innodb;
1 row in set (1.91 sec)
 
select sql_no_cache count(*) from innodb;
1 row in set (0.25 sec)

如果是2,DEMAND。
在my.ini中增加一行
query_cache_type=2
重启mysql服务

select count(*) from innodb;
1 row in set (1.56 sec)
 
select count(*) from innodb;
1 row in set (0.28 sec)

没有使用sql_cache,好像仍然使用了查询缓存

select sql_cache count(*) from innodb;
1 row in set (0.28 sec)

使用sql_cache查询时间也一样,因为sql_cache只是将查询结果放入缓存,没有使用sql_cache查询也会先到查询缓存中查找数据

结论:只要query_cache_type没有关闭,sql查询总是会使用查询缓存,如果缓存没有命中则开始查询的执行计划到表中查询数据。

相关栏目

相关文章



分享给小伙伴们:

评论

发表评论愿您的每句评论,都能给大家的生活添色彩,带来共鸣,带来思索,带来快乐。

签名: 验证码: 点击我更换图片

评论列表

    © 2002-2017 dngsw.cn 电脑高手网 版权所有

    粤ICP备13005586号-3