01 缓存参数优化

数据库属于 IO 密集型的应用程序 , 其主要职责就是数据的管理及存储工作 . 而我们知道 , 从内从中读取一个数据库的时间是微秒级别 , 而从一块普通硬盘读取一个 IO 是在微秒级别 , 二者相差3个数量级 . 所以 , 要手滑数据库 , 首相第一步需要优化的就是 IO , 进可能的将磁盘 IO转化为内存 IO . 本文先从Mysql 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化:

1

query_cache_size/query_cache_type(global)

Query cache 错用与整个Mysql Instance , 主要用来缓存Mysql 中的 ResultSet , 也就是一条 SQL 语句执行的结果集 , 所以仅仅只能针对 select 语句 . 当我们打开了qUERY Cache 功能 , Mysql 在接收到一条select 语句的请求后 , 如果该语句满足Query Cache 的要求(未显示说明不允许使用Query Cache , 或者已经显示声明需要使用Query Cache) , Mysql 会直接根据预先设定好的HASH算法将接收到select 语句以字符串方式进行hash , 然后到Query Cache 中直接查找是否已经缓存 . 也就是说 , 如果已经在缓存中 , 该 select 请求就会直接将数据返回 , 从而省略了后面所有的步骤 , (如果 SQL 语句的解析 , 优化器优化以及向存储引擎请求数据等 ) , 极大的提高性能 .
当然 , Query Cache 也有一个致命的缺陷 , 那就是当某个表的数据有任何变化 , 都会导致素有引用了该表的select 语句在 Query Cache 中的缓存数据失效 , 所以 , 当我们的数据变化非常频繁的的情况下 , 使用 Query Cache 可能会得不偿失 .
Query Cache 的使用需要多个参数配合 , 其中最关键的就是 query_cache_size 和 query_cache_type , 前者设置用于缓存 ResultSet 的内存大小 , 后者设置在任何场景下使用 Query Cache . 在以往的经验看来 , 如果不是用来缓存基本不变的数据的Mysql 数据库 , query_cache_size 一般256M 是一个比较合适的大小 , 当然 , 这可以通过计算 Query Cache 的命中率 (Qcache_hits/(Qcache_hits+Qcache_inserts)*100) 来进行调整 . query_cache_type 可以设置为0(OFF) , 1(ON) 或者 2(DEOND) , 分别表示不完全使用query cache , 除显示要求不使用query cache(使用sql_no_cache)之外的所有select 都使用 query cache ,只用显示要求才使用query cache (使用 sql_cache) .

binlog_cache_size(global)
Binlog Cache 用于在打开了二进制日志(binlog)记录功能的环境 , 是 Mysql 用来提高binlog 的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域 .
一般来说 , 如果我们的数据库中没有什么大事务 , 写入也是特别频繁 , 2MB ~ 4MB 是一个合适的选择 . 但是如果我们的数据库大事务较多 , 写入量比较大 , 可以适当调高 binlog_cache_size . 同时 , 我们可以通过 binlog_cache_use 以及 binlog_cache_disk_use 来分析设置的binog_cache_size 是否足够 , 是否有大量的binlog_cache 由于内存大小不够而使用临时文件(binlog_cache_disk_use) 来缓存了 .

key_buffer_size (global)
Key Buffer 可能是大家最为熟悉的一个Mysql 缓存参数了, 尤其是在Mysql 没有变更 默认存储引擎的时候 , 很多朋友可能会发现 , 默认的Mysql 配置文件中设置最大的一个内存参数就是这个参数了 .
key_buffer_size 参数用来设置用于缓存MyISAM 存储引擎中索引文件的内存大小 , 如果我们有足够的内存 , 这个缓存区域最好是能够存放我们所有的MyISAM 引擎表的所有索引 , 以因可能提高性能 .
此外 , 当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意 , 由于MyISAM 引擎的特性闲着了他仅仅只会缓存索引块到内存中 , 而不会缓存表数据库块 . 所以 , 我们的 SQL 一定要尽可能让过滤条件都在索引中 , 一遍缓存帮助我们提高查询效率 .

bulk_insert_buffer_size(thread)
和 key_buffer_size 一样 , 这个参数同样也仅作用于使用MyISAM存储引擎 , 用来缓存批量插入数据的时候临时缓存写入数据 . 当我们使用如果集中数据写入语句的时候 , 会使用这个内存区域来缓存批量结构的数据以帮助批量写入数据 :

1
2
3

insert ... select ...
insert ... value (...) , (...) , (...) ...
load data infile ... into ...(非空表)

innodb_buffer_size(global)
当我们使用Innodb存储引擎的使用, innodb_buffer_pool_size 参数可能是影响我们性能的最为关键的一个参数了 , 它用来设置用于缓存 InnoDB 索引 及数据块的内存区域大小 , 类似于 MyISAM 存储引擎的key_buffer_size 参数 , 当然 , 可能更像是 Oracle 的db_cache_size . 简单来书 , 当我们操作一个InnoDB 表的时候, 返回的所有数据或者取数据过程中用到的任何一个索引块 , 都会在这个内存区域中走一遭 .
和key_buffer_size 对于MyISAM引擎一样 , innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小 ， 直接关系到InnoDB 存储引擎的性能 ， 所以如果我们有足够的内存 ， 尽可能将这个参数设置的足够大 ， 将尽可能多的 InnoDB 的索引及数据都存入到该缓存区域中 ， 直至全部 .
我们可以通过(Innodb_buffer_pool_read_request – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_request * 100% 计算缓存命中率 , 并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化 .

innodb_additional_mem_pool_size (global)
这个参数我们平时调整的可能不是太多 , 很多人都是用了默认值 , 可能很多人都不是太熟悉这个参数的作用 . innodb_additional_mem_pool_size 设置了 InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存大小 , 所以当我们一个Mysql instance 中的数据库对象非常多的时候 , 是需要适当调整该参数的大小以确保所有数据都存放在内从中提高访问的效率的 .
这个参数的大小是否足够还是比较容易知道的 , 因为当过小的时候 ,Mysql 会记录Waring 信息到数据库的 error log 中, 这时候你就知道该调整这个参数大小了 .