ホーム 話題 library logo ライブラリ 自分
詳細表示

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 命中率=1- Key_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_requests- Innodb_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

ホーム - Wiki
Copyright © 2011-2024 iteam. Current version is 2.137.1. UTC+08:00, 2024-11-15 15:14
浙ICP备14020137号-1 $お客様$