当前位置:Gxlcms > mysql > MySQL优化之缓存优化详解(一)

MySQL优化之缓存优化详解(一)

时间:2021-07-01 10:21:17 帮助过:19人阅读

在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助。

高兴的是有博友mark了我的文章。我知道mark之后,很少会再来继续关注的。但是从侧面说明了在博友点开博客的同时,他感觉这篇博客是有价值的,是能够弥补他的知识欠缺。一篇博客最重要的是对自己有用,如果再对别人有用,那是最好的结果。我坚持写博客的目的是为了当自己遗忘知识点的时候,能够最快的找到靠谱的解决方案。当自己的归纳的知识,再记起来就会遗忘的慢一点,等时间久了,这部分知识终于化成了自己脱口而出的话,那就再也不怕遗忘了。这篇博客将继续讲MySQL的内容,这篇讲缓存优化,讲的过程也是我学习的过程。

先来看下我们mysql的版本,我的mac 上装的版本是5.7的,很多内容都已经变化掉了。这里讲的主要是5.6的版本。


[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

一、MySQL缓存分类

MySQL的优化指的是一个很大的系统,面试的时候我之前是从sql的语句优化方面去说的,这种优化也有作用,不过是从逻辑方面去优化。但是当所有的逻辑层面已经无可优化,所有的索引都已经加好,表结构也设计的合理,但是遇到高并发的时候,为什么MySQL还是扛不住呢。当然可以通过其他的方面去缓解MySQL的压力,这里我们暂且不谈。对于MySQL而言,我们要尽最大的可能去压榨机器的性能,让所有的计算资源都不浪费,都可以为我们服务。MySQL运行在服务器上,这里特指Linux服务器。那么服务器的硬盘、CPU,内存,网络都有影响到MySQL的性能。MySQl是非常耗费内存的,线上服务器的MySQL内存要吃到80%左右,内存过小,其他的优化空间其实很小。

另外连接(connection)也是影响MySQL性能的重要一方面。MySQL客户机与MySQL服务器之间的连接是MySQL客户机与MySQL服务器反复握手的结果。每次'握手'都经历身份验证、权限验证等环节,握手需要占用一定的网络资源和MySQL服务器内存资源。

不得不提的是锁竞争,对于并发性能要求比较高的数据库而言,如果存在激烈的锁竞争,对数据库的性能将是很大的打击。锁竞争会明显的增加线程上下文切换的开销,这些开销都与预期的需求无关。

二、show status 与 show variables

在MySQL系列的前几篇博客,会经常的看到这些命令,那么我们分别看下,这两个命令给MySQL系统管理员展示的是什么信息:

show status

MySQL服务运行的时候,MySQL服务实例的状态信息是动态的。用该命令可以显示当前MySQL服务器连接的会话状态变量信息。默认情况下变量名首字母大写。

show variables

show variables 用来显示MySQL 服务实例的各种系统变量(如:全局系统变量,会话系统变量,静态变量),这些变量包含MySQL编译时参数的默认值,或者是my.cnf中设置的参数值。系统变量或者参数是一个静态的概念,默认情况下系统变量名都是小写字母。

使用MySQL命令show status 或者 show session status ,可以查看当前MySQL 服务器连接的会话变量信息,会话状态的变量值对当前的MySQL客户机有效,例如:Opened_tables、Opened_table_definitions状态变量。

缓存机制

缓存之所以有效,主要是因为程序运行时对内存或者外存的访问呈现局部性特征,局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问,空间局部性是指,某个位置被访问后,其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据(时间局部性)以及未来即将访问到的数据(空间局部性)保存到缓存中,甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同,MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢,或者频繁的I/O,对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后,一次性的写入到硬盘中。Buffer 缓存主要用于写数据,提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据,如果Cache缓存已经存储满,则启用LRU算法,进行数据淘汰。淘汰掉最远未使用的数据,从而开辟新的存储空间。不过对于特大型的网站,依靠这种策略很难缓解高频率的读请求,一般会把访问非常频繁的数据静态化,直接由nginx返还给用户。程序和数据库I/O设备交互的越少,则效率越高。

三、MySQL 超时

在使用MySQL的过程中,可能会出现各种超时(timeout)异常,典型的有连接超时、锁等待等。

查看超时时间的类型有哪些:


mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name        | Value  |
+-----------------------------+----------+
| connect_timeout       | 10    |
| delayed_insert_timeout   | 300   |
| innodb_flush_log_at_timeout | 1    |
| innodb_lock_wait_timeout  | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout     | 28800  |
| lock_wait_timeout      | 31536000 |
| net_read_timeout      | 30    |
| net_write_timeout      | 60    |
| rpl_stop_slave_timeout   | 31536000 |
| slave_net_timeout      | 3600   |
| wait_timeout        | 28800  |
+-----------------------------+----------+

1、连接超时(connect_timeout)

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果,并且是多次握手的结果,每次握手,除了验证账户名和身份信息外,还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障,可以通过connect_timeout参数来设置,防止它们之间重复握手。

interactive_timeout指的是交互式的终端,在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端,比如PHP实例化的Mysql连接,一直占用着,超过了这个参数设置的值,就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集,MySQL客户机在该值设置的时间内不能接受完毕,则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据,在设置值内不能读取完毕,则会自动断开连接。

InnoDB锁等待超时


mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50  |
+--------------------------+-------+

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值,当出现锁等待的时候,等待时长超过该值会导致锁等待的SQL回滚(不是整个事务回滚)。如果希望整个事务回滚,需要开启innodb_rollback_on_timeout参数。


mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF  |
| innodb_rollback_segments  | 128  |
+----------------------------+-------+

innodb_rollback_on_timeout设置为true 后,遇到事务超时,会回滚整个事务的操作。

复制连接超时

当主从配置是,从服务器(slave)从主服务器(master)读取二进制日志失败后,从服务器会等待 slave_net_timeout 后,从新从master机拉去二进制日志。可以设置成10s.


mysql> show variables like 'slave_net_timeout';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| slave_net_timeout | 3600 |
+-------------------+-------+

这部分总结,应该是周日晚上就该整理好的,结果拖到了今天。后面的计划又要后延了,拖延症真严重。

以上就是MySQL优化之缓存优化详解(一)的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行