内存配置相关参数

前面我们为大家介绍了MYSQL设置参数的作用域,以及如何对全局含有session参数进行配置,下面我们就来具体的

看一下参数的配置项,首先我们来看内存相关的参数,前面我们说过,内存的使用对于MYSQL的性能是非常重要的,

所以如何正确的配置MYSQL服务器所使用的内存参数,就显得至关重要了,MYSQL对于内存的使用呢,可以分为两类来看,

一类是我们无法通过配置参数所配置的,如MYSQL服务器运行,解析,查询,以及内部管理所消耗的内存,这是我们无法通过

配置参数所配置的,另一类是我们可以通过配置参数所配置的,如各类的缓冲池所使用的内存,要对MYSQL使用的内存进行

配置,我们首先要搞清楚一下几个问题,确定可以使用的内存的上限,在所有的操作系统上呢,在所有的操作系统上呢,都会

存在给MYSQL服务器所使用内存的上限的限制,最根本的一点呢,是我们给MYSQL服务器配置的内存呢是不可以超过本身安装

的物理内存的,不要以为这是开玩笑,没有人会这样配置,但是实际上呢,我见过很多这样配置的情况,比如对于进程内存配置

过大时,当MYSQL连接突然增长,就会产生这样的情况,从而造成MYSQL服务器呢,因为内存溢出而奔溃,但另一方面呢,限制和

我们的操作系统架构有关系,比如32位的操作系统,对于一个进程可以使用的内存呢,又是有限制的,因为MYSQL是在单进程下

进行的,所以如果使用的是32位操作系统的话,可能只能使用3G以内的内存了,所以之前也建议过大家,对于现在的服务器呢,

和操作系统来说,都一定要使用64位架构的,刚才提到了对MYSQL的内存进行配置,我们很容易犯的错误就是,给每个连接配置的

内存过大,所以第二个要确定的问题就是,确定MYSQL的每个连接使用的内存,MYSQL对于一些缓存,是会对每个连接进行单独分配的,

所以连接数如果越大,内存占用的也就会越多,参数注意主要有以下几个,首先第一个是排序缓存区的尺寸,这个参数定义了MYSQL

每个线程使用排序缓存区的大小,MYSQL并不是在连接初始化时就会每个缓存区分配缓存,而是在有查询排序操作时,

才会为每个缓冲区分配内存,然后重要的是呢,一旦查询需要排序,MYSQL会立即分配这个参数指定大小的全部内存,

而不管该排序是否需要这么大内存,有些时候人们为了某些大的查询的排序,可以效率高一些,把这个参数直接设置

到一个很大的值,比如说100M,如果有100个连接同时进行排序,那么MYSQL就会占用10个G的内存,所以很容易造成服务器

内存的溢出,所以大家进行这个参数配置的时候呢,一定要多加小心,那么另一个需要注意的呢,连接缓存区的尺寸,

这个参数定义的是,MYSQL的每个线程使用连接缓冲区的大小,对于这个参数需要注意的是,如果一个查询中关联了多张表,

那么就会为每个关联分配一个连接缓冲,所以每个查询可能会有多个连接缓冲,所以这个参数呢,不可能设置的太大,接下来

就是read_buffer_size,这个参数指定了当一个MYISAM表进行全表扫描时,所分配的读缓存池的大小,MYSQL只有在查询需要时

才会被改缓冲分配内存,同样会分配该参数值大小的内存,另外有一点需要注意的是,参数值的大小呢,一定要是4K的一个

倍数,下面这个参数呢,控制的是索引缓冲区的大小,MYSQL也只是在有查询需要时,才会对该缓冲区分配内存,并且只会分配

需要内存的大小,而不是参数指定的大小,一定要记住,以上四个参数,全是为每个线程所分配的,如果我们有100个连接,

那就可能分配100倍以上四个参数值内存大小的和,所以以上四个参数配置的过大,很有可能会造成很大的内存浪费,甚至

造成MYSQL内存的溢出,而使MYSQL内存奔溃,这一点大家要注意,接下来我们要看看,如何为MYSQL的各种缓冲池来配置内存

那么在这之前呢,我们要先来看看,操作系统保留的内存呢,还包括在同一个服务器上运行的所有其他服务,

所需要的内存,以及前面提到的,MYSQL服务器中,我们无法通过参数来控制的,那一部分内存,我推荐大家使用

专用的服务器,而不要和其他应用公用一个服务器,因为如果和其他服务器公用一个服务器的话,不可避免的

会进行内存的征用,也会给MYSQL配置内存带来更多的要考虑的因素,另外也有人喜欢在一台物理服务器上呢,

进行多个MYSQL实例,从内存分配上来看,这样做也并不好,除非是在测试或者是开发环境,这样做可以解决服务器

成本,但是在生产环境中呢,大家最好不要在一台物理服务器上呢运行多个MYSQL实例,因为这样也会导致内存IO

的征用,从而影响MYSQL服务器的性能,最后对于MYSQL服务器内存的分配呢,需要考虑的就是,如何为缓冲池来分配

内存,MYSQL有很多种内存

其中最主要的是以下两个,一个是innodb_buffer_pool_size,这个参数定义了innodb所使用的缓冲池的大小,

这个参数对于innodb存储引擎的性能呢,是非常重要的,innodb存储引擎,不仅要缓存索引,同时呢也要缓存数据,

哈希索引插入缓冲,锁,以及其他的内部数据结构,前面提到过,innodb还利用缓存池呢,来帮助延迟写入,这样就能

合并多个写入操作,然后一起顺序的写入到磁盘,总之innodb的性能呢严重依赖于缓存池,所以我们要保证给他分配了

足够的内存,如果我们在系统中只能使用innodb表,那么innodb的缓存池的大小呢,可以大致用下面的方法呢来计算,

也就是总内存减去每个线程所需要的内存,乘以连接数,这是我们前面所介绍的,所有各个线程所需要的内存的总和,

乘以我们可能要达到的连接数,再减去系统保留的内存,大致就是我们innodb缓存池的大小了,而在MYSQL手册中呢,

innodb_buffer_pool_size大小呢,应该为服务器内存的97%以上,前面我们提到了,MYSQL是如何使用内存的,所以呢,

不能完全按照服务器内存的75%,这样来配置这个值,我们还必须考虑其他的一些因素,当然了,如果我们当前innodb的

数量很小,并且在很长一段时间内呢,增长都是可以预计的话,那就没有必要把所有的内存呢,分配给Innodb缓冲池,

因为缓冲池的内存总量,如果超过innodb表数量,加索引所占的空间的大小呢,那就没有意义了,如果我们的数据量会

快速的增长,就需要innodb缓冲池,多留一些预留空间,因为对于MYSQL5.7之前的版本来说,innodb大小的变更呢,是需要

重启服务器的,所以我们不能随意的调整innodb缓冲池的大小,而对于innodb缓冲池需要注意的是,很大的缓冲池也使得

innodb在关闭的时候需要更多地时间,把缓冲池刷到磁盘上,那另一个和存储引擎有关的缓冲池呢,key_buffer_size,

和innodb_buffer_size不同,这个参数所配置的缓冲池呢,主要是用于MYISAM存储引擎的,MYISAM存储引擎只会缓存

索引,数据则是依赖操作系统的缓存,所以如果我们的系统存在很多的MYISAM表的话,和innodb表一样,超过缓冲池大小的

配置呢,我们可以通过下面的查询知道MYISAM表,索引所占空间的大小,这里有一点需要注意的就是,就算我们系统中

使用的全都是innodb表,也还是要给key_buffer_size来分配一定的空间,就是MYSQL的系统表呢,目前还是在使用MYISAM

存储引擎,以上就是配置参数,以上的参数一定要根据我们的实际情况来进行调整,所以不可能存在一个通用的配置项