MySQL优化

MySQL优化

MySQL优化

1. max_connections（mysql最大连接）

MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当 然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开 销越多的内存，所以要适当调整该值，不能盲目提高设值。 数值过小会经常出现ERROR 1040: Too many connections错误，可以通过 mysql> show status like 'connections'; 通配符查看当前状态的连接数量(试 图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小

             Show variables like ‘max_connections’;   //查看MySQL最大连接数

              Show status like ‘max_used_connections’;    //已相应的连接数

max_used_connections / max_connections * 100% （理想值≈ 85%）  如果max_used_connections跟 max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。 如何设置max_connections? 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

重启mysql服务，之后查看

back_log MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就 会起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一 连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。 back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如 果期望在一个短时间内有很多连接，你需要增加它。 当观察你主机进程列表（mysql> show full processlist），发现大量 xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了或加大max_connections的值。 通过

       show variables like 'back_log';     //back_log是查看暂存的连接数量

如何设置back_log? 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

       Back_log = 数值

重启mysql服务，之后查看

wait_timeout和interactive_timeout wait_timeout -- 指的是MySQL在关闭一个非交互的连接之前所要等待 的秒数 interactive_time -- 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上 进入mysql管理，使用的即使交互的连接，这时候，如果没有 *** 作的时间超过了interactive_time设置的时 间就会自动断开。默认数值是28800，可调优为7200。 对性能的影响： wait_timeout： （1）如果设置 大小，那么连接关闭的很快，从而使一些持久的连接不起作用 （2）如果设置太大，容易造成连接打开 时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误 （3）一般希望wait_timeout尽可能地低 interactive_timeout的设置将要对你的web application没有多大 的影响 查看wait_timeout和interactive_timeout

       show variables like ‘%wait_tmeout%’;    //交互式需要等待的秒数

       show variables like ‘%interactive_timeout%’;      //非交互式需要等待的秒数

如何设置wait_timeout和interactive_timeout? 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

       Wait_timeout=数值

       Interactive_timeout=数值

重启mysql服务，查看设置已经生效

缓冲区变量

全局缓冲：

       key_buffer_size key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

key_buffer_size key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

key_buffer_size 默认配置数值是8388608(8M)

查看key_buffer_size默认配置数值

       Show varibles like ‘key_buffer_size’;   //key_buffer_size缓冲区大小

可以调优值为268435456(256MB) 修 改/etc/my.cnf文件，在[mysqld]下面添加如下内容

       key_buffer_size=268435456或key_buffer_size=256M

重启mysql服务，之后查看缓冲区

通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。 Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多 了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。  注：当一个表被更新之后，和 它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 FLUSH QUERY CACHE语句来清空free blocks Qcache_free_memory：Query Cache 中目前剩余的内存大小。通 过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多 了。 Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大， 缓存效果越理想。 Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不 得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到 的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。 Qcache_lowmem_prunes：多 少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相 互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内 存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重， 或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）  Qcache_not_cached：不适合 进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；  Qcache_total_blocks：当前Query Cache 中的block 数量；。  我们再查询一下服务器关于query_cache的配置：

       show variables like '%query_cache%';    //查看状态值

上图可以看出query_cache_type为off表示不缓存任何查询

 各字段的解释：

query_cache_limit：超过此大小的查 询将不缓存 

query_cache_min_res_unit：缓存块的最小大小

query_cache_min_res_unit的配置是一柄”双刃 剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪 费。 query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024 的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)

query_cache_type：缓存类型，决 定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

query_cache_type三个参数的含义:

query_cache_type=0（OFF）关闭

query_cache_type=1（ON）缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存

query_cache_type=2(DEMAND)，只缓存select语句中通过SQL_CACHE指定需要缓存的查询

如何设置query_cache_size？ 修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

  Query_cache_size=数值

  Query_cache_type=0到2

重启mysql服务，进入后查看是否生效

在优化之前执行mysqlslap工具进行测试

第一个是优化后的，第二个是优化前的，可以看出相差还是蛮大的