搜索
MySQL重点参数介绍2012.08.03ByDBA组王洪权mydbalife@gmail.comWeibo@foreverreturn内容概要•1mysql体系结构•2重点参数介绍（innodb_buffer_pool_size,innodb_log_buffer_size,innodb_log_file_size,key_buffer_size,query_cache_size）•3Q&A2012-8-9体系结构概览内存设置规则2012-8-9Myisam参数•Key_buffer_size(根据需要分配使用的空间，注意没做修改都会清空缓存，尤其是在线操作的时候尤为注意)•重点关注参数关注•Key_blocks_used•Key_blocks_unused•Key_reads•Key_read_requests2012-8-9Keycache•Keycache的使用率=Key_blocks_used/Key_blocks_used+Key_blocks_unused•Keycache命中率=1-Key_reads/Key_read_requests*100%•key_buffer_size：无MyISAM16MB~32M，否则所有MYI大小之内尽可能大，因为它之缓存索引数据，还要注意.MYD是缓存到OScache的。2012-8-9Innodb_buffer_pool_size确定最大连接数是多少(max_connections)•系统使用，预留800M•线程独享（每个session）sort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_size,thread_statck•Myisamkeycache假设1.5G•Innodb_buffer_pool_size=总的内存大小-操作系统占用-连接数*(独享线程之和)•还要考虑tmp_table_size等2012-8-9Innodb_buffer_pool_size诊断Bufferpool命中率=(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_request*100Bufferpool大小是否合理•Innodb_buffer_pool_pages_freeThenumberoffreepages.•空闲的数据页的数量•Innodb_buffer_pool_reads当在bufferpool中无法满足锁获得的数据，读磁盘获得的次数。•Innodb_buffer_pool_wait_free如果需要读或者创建一个数据页的时候，如果没有干净的页获得的时候，通常需要先等待bufferpool刷新脏页，这个值统计了实例发生等待的次数。这个数太小通常说明bufferpool设置过小Showengineinnodbstatus\G;2012-8-9Innodb_buffer_pool_size•BufferPool越大，能缓存的数据和索引就越多，QPS就越高•BufferPool缓存命中率越高,DB热点数据查询性能就越好2012-8-9Querycache介绍•have_query_cache•query_cache_limit•query_cache_min_res_unit•query_cache_size•query_cache_type•query_cache_wlock_invalidate2012-8-9Querycache介绍•Mysql的查询缓存保留了查询返回给客户端的完成的所有的完整的结果集。在缓存命中的情况下，服务器马上回返回保存的结果，并跳过解析，优化执行的步骤。•查询缓存保留了查询使用过的表，如果表发生了变化，那么缓存就失效了。2012-8-9Querycache参数介绍OptionDescription0orOFF不会缓存和获取任何结果集到querycache中，注意这个并不会释放quercachebuffer,要想释放querycachebuffer,你需要设置query_cache_size为01orON缓存所有的的查询结果到querycache中，除非你在查询中使用SELECTSQL_NO_CACHE关键字，这时候是不会缓存到querycache的。2orDEMAND只有在使用关键字SELECTSQL_CACHE.的时候才会将结果集缓存到querycache中2012-8-9ThisvariabledefaultstoON.query_cache_typeQuerycache参数介绍•query_cache_size总体分配给querycache的大小，默认情况下是0，这也就意味着是禁用querycache的，注意querycachesize是一次性分配所定义的内存大小，不管你用不用。•query_cache_limit如果要缓存的结果集超过这个大小的话将不会缓存。默认是1M•query_cache_min_res_unit在querycache中最小的块大小，默认的是4096（4K）如果你有很多的查询都是比较小的结果集，那么默认大小很可能导致很多内存碎片，这将会产生大量有空闲空间的内存块，大量的碎片可能会导致没有可用的内存，这样的话将不得减少query_cache_min_res_unit的大小。通过Qcache_free_blocks，Qcache_lowmem_prunes这两个参数，可以清晰的看到空闲块的数量，已经由于内存不足而造成清理querycache中结果集的次数。2012-8-9Querycache状态变量•Qcache_free_blocks带有空闲空间的内存块的数量•Qcache_free_memoryquerycache空闲内存大小•Qcache_hits查询从querycache中命中的次数，Com_select不会增加•Qcache_inserts不管查询有没有命中，则该值则会增加12012-8-9Querycache状态变量•Qcache_lowmem_prunes由于内存不足，或者有太多碎片导致清除的查询结果集的次数•Qcache_not_cached没有缓存sql的次数（可能有些时间函数等等）•Qcache_queries_in_cache在qurycache中注册的query的数量，同一条sql即使执行多次也不会增加•Qcache_total_blocksQuerycache中总的块数2012-8-9Querycache命中率•Querycache命中率Qcache_hits/(Qcache_hits+Qcache_inserts)清空querycacheResetquerycache;清理querycache碎片Flushquerycache;2012-8-9Querycache诊断•综上所述通过以下参数，检查querycache设置是不是合理Qcache_free_blocksQcache_free_memoryQcache_lowmem_prunesQcache_total_blocks2012-8-9Querycache诊断•通过Qcache_free_blocks的值来探测缓存中的碎片，他可以显示有多少内存块处于free•如果说Qcache_free_blocks等于Qcache_total_blocks/2的时候说明碎片很严重•如果这时候Qcache_free_memory还有很多空余内存，Qcache_lowmem_prunes在不断增加，以为着碎片导致查询正在被从缓存中清除。2012-8-9querycache总结•如果你的应用对数据库的更新很少，那么QC将会作用显著，如果你的数据表更新频繁的话，那么QueryCache将会成为系统的负担,不要设置超过256M.•QueryCache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除。这里“数据表更改”包括:INSERT,UPDATE,DELETE,TRUNCATE,ALTERTABLE,DROPTABLE,orDROPDATABASE等2012-8-9innodb_log_buffer_size•通常设置8M~16M，除非你使用大字段blob,可以适当加大showglobalstatuslike'%Innodb_log_waits%'Innodb_log_waits0由于logbuffer太小，在写日志缓冲区的时候不得不等待主线程将日志缓冲区内容刷新到logfile如果该值大于0，并且持续增大，应该调整期大小。2012-8-9innodb_log_file_size•这个参数非常重要，具体设置大小要看应用IO负载，目前建议大家最好设置512M•在写入密集型机器上，因为这个参数设置过小将直接导致IO使用率增加，检查点写入次数增加，切日志频繁，应用程序访问数据库响应时间增加，更可怕的是将直接导致数据库挂起。2012-8-9其他重要参数设置•max_connections•log-output=file•slow_query_log=1•slow_query_log_file=/home/mysql/slow.log•log-queries-not-using-indexes=1•innodb_read_io_threads=4•innodb_write_io_threads=42012-8-9其他重要参数设置•innodb_flush_method=O_DIRECT•innodb_file_per_table=1•transaction_isolation=READ-COMMITTED（以后没有特殊需求采用这种事物隔离级别）•binlog_format=ROW(行复制)•innodb_flush_log_at_trx_commit=1•innodb_max_dirty_pages_pct=50•read_buffer_size=1M2012-8-9其他重要参数设置•sort_buffer_size=2M•join_buffer_size=2M•thread_cache_size=256•read_buffer_size=1M•tmp_table_size=16M•max_heap_table_size=32M•sync_binlog=1•innodb_support_xa=12012-8-9其他重要参数设置•innodb_flush_log_at_trx_commit=1•log-bin=/home/binlog/binlog•log-bin-index=/home/binlog/binlog.index2012-8-9Q&A2012-8-9