2012-08 网易DBA 王洪权:MySQL参数优化

文字内容
1. MySQL 重点参数介绍 2012.08.03 By DBA组 王洪权 mydbalife@gmail.com Weibo @foreverreturn
2. 内容概要 • 1 mysql 体系结构 • 2 重点参数介绍 (innodb_buffer_pool_size,innodb_log_ buffer_size, innodb_log_file_size, key_buffer_size, query_cache_size ) • 3 Q&A
3. 体系结构概览 2012-8-9
4. 内存设置规则 2012-8-9
5. Myisam 参数 • Key_buffer_size (根据需要分配使用的空间 ,注意没做修改都会清空缓存,尤其是在 线操作的时候尤为注意) • 重点关注参数关注 • Key_blocks_used • Key_blocks_unused • Key_reads • Key_read_requests 2012-8-9
6. Key cache • Key cache的使用率=Key_blocks_used/ Key_blocks_used+ Key_blocks_unused • Key cache 命中率=1Key_reads/Key_read_requests*100% •key_buffer_size:无MyISAM 16MB~32M , 否则所有MYI大小之内尽可能大 ,因为它 之缓存索引数据,还要注意.MYD是缓存到 OS cache的。 2012-8-9
7. Innodb_buffer_pool_size 确定最大连接数是多少(max_connections) • 系统使用,预留800M • 线程独享(每个session) sort_buffer_size,join_buffer_size,read_buffer_size ,read_rnd_buffer_size,thread_statck • Myisam key cache 假设1.5G • Innodb_buffer_pool_size=总的内存大小-操作系统占用连接数*(独享线程之和) • 还要考虑tmp_table_size等 2012-8-9
8. Innodb_buffer_pool_size 诊断 Buffer pool 命中率=(Innodb_buffer_pool_read_requestsInnodb_buffer_pool_reads)/Innodb_buffer_pool_read_request*100 Buffer pool 大小是否合理 • Innodb_buffer_pool_pages_free The number of free pages. • 空闲的数据页的数量 • Innodb_buffer_pool_reads 当在buffer pool 中无法满足锁获得的数据,读磁盘获得的次数。 • Innodb_buffer_pool_wait_free 如果需要读或者创建一个数据页的时候,如果没有干净的页获得的时候,通常需要先等 待buffer pool 刷新脏页,这个值统计了实例发生等待的次数。这个数太小通常说明buffer pool 设置过小 Show engine innodb status\G; 2012-8-9
9. Innodb_buffer_pool_size • Buffer Pool越大,能缓存的数据和索引就越 多,QPS就越高 • Buffer Pool缓存命中率越高,DB热点数据查 询性能就越好 2012-8-9
10. Query cache介绍 • • • • • • have_query_cache query_cache_limit query_cache_min_res_unit query_cache_size query_cache_type query_cache_wlock_invalidate 2012-8-9
11. Query cache介绍 • Mysql 的查询缓存保留了查询返回给客户端 的完成的所有的完整的结果集。在缓存命 中的情况下,服务器马上回返回保存的结 果,并跳过解析,优化执行的步骤。 • 查询缓存保留了查询使用过的表,如果表 发生了变化,那么缓存就失效了。 2012-8-9
12. Query cache参数介绍 query_cache_type Option Description 0 or OFF 不会缓存和获取任何结果集到query cache中,注意这个并不会释放quer cache buffer,要想释放 query cache buffer,你需要设置query_cache_size 为 0 1 or ON 缓存所有的的查询结果到 query cache 中,除非你在查询中使用SELECT SQL_NO_CACHE关键字,这时候是不 会缓存到query cache的。 2 or DEMAND 只有在使用关键字 SELECT SQL_CACHE. 的时候才会将结果集缓存到query cache中 This variable defaults to ON. 2012-8-9
13. Query cache参数介绍 • query_cache_size 总体分配给query cache的大小,默认情况下是0,这也就意味着是禁用query cache的,注意query cache size 是一次性分配所定义的内存大小,不管你用不用 。 • query_cache_limit 如果要缓存的结果集超过这个大小的话将不会缓存。默认是1M • query_cache_min_res_unit 在 query cache中最小的块大小,默认的是4096(4K) 如果你有很多的查询都是比较小的结果集,那么默认大小很可能导致很多内存碎 片,这将会产生大量有空闲空间的内存块,大量的碎片可能会导致没有可用的内 存,这样的话将不得减少query_cache_min_res_unit的大小。通过 Qcache_free_blocks , Qcache_lowmem_prunes 这两个参数,可以清晰的看到空 闲块的数量,已经由于内存不足而造成清理query cache中结果集的次数。 2012-8-9
14. Query cache状态变量 • Qcache_free_blocks 带有空闲空间的内存块的数量 • Qcache_free_memory query cache空闲内存大小 • Qcache_hits 查询从query cache中命中的次数,Com_select 不会 增加 • Qcache_inserts 不管查询有没有命中,则该值则会增加1 2012-8-9
15. Query cache状态变量 • Qcache_lowmem_prunes 由于内存不足,或者有太多碎片导致清除的查询结果集的次 数 • Qcache_not_cached 没有缓存sql的次数(可能有些时间函数等等) • Qcache_queries_in_cache 在qury cache中注册的 query的数量,同一条sql即使执行多次 也不会增加 • Qcache_total_blocks Query cache中总的块数 2012-8-9
16. Query cache 命中率 • Query cache命中率 Qcache_hits /(Qcache_hits + Qcache_inserts) 清空query cache Reset query cache; 清理query cache碎片 Flush query cache; 2012-8-9
17. Query cache诊断 • 综上所述 通过以下参数,检查query cache设置是不是 合理 Qcache_free_blocks Qcache_free_memory Qcache_lowmem_prunes Qcache_total_blocks 2012-8-9
18. Query cache 诊断 • 通过Qcache_free_blocks的值来探测缓存中 的碎片,他可以显示有多少内存块处于free • 如果说Qcache_free_blocks 等于 Qcache_total_blocks/2的时候说明碎片很严 重 • 如果这时候Qcache_free_memory还有很多 空余内存,Qcache_lowmem_prunes 在不断 增加,以为着碎片导致查询正在被从缓存 中清除。 2012-8-9
19. query cache 总结 • 如果你的应用对数据库的更新很少,那么QC将会 作用显著,如果你的数据表更新频繁的话,那么 Query Cache将会成为系统的负担,不要设置超过 256M. • Query Cache有如下规则,如果数据表被更改,那 么和这个数据表相关的全部Cache全部都会无效, 并删除。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等 2012-8-9
20. innodb_log_buffer_size • 通常设置8M~16M ,除非你使用大字段 blob,可以适当加大 show global status like '%Innodb_log_waits%' Innodb_log_waits 0 由于log buffer 太小,在写日志缓冲区的时候 不得不等待主线程将日志缓冲区内容刷新到 logfile 如果该值大于0,并且持续增大,应该调整期 大小。 2012-8-9
21. innodb_log_file_size • 这个参数非常重要,具体设置大小要看应 用IO负载,目前建议大家最好设置512M • 在写入密集型机器上,因为这个参数设置 过小将直接导致IO使用率增加,检查点写入 次数增加,切日志频繁,应用程序访问数 据库响应时间增加,更可怕的是将直接导 致数据库挂起。 2012-8-9
22. 其他重要参数设置 • • • • • • • 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 = 4 2012-8-9
23. 其他重要参数设置 • 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 = 1M 2012-8-9
24. 其他重要参数设置 • • • • • • • • 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=1 2012-8-9
25. 其他重要参数设置 • innodb_flush_log_at_trx_commit=1 • log-bin = /home/binlog/binlog • log-bin-index = /home/binlog/binlog.index 2012-8-9
26. Q&A 2012-8-9