MySQL系统调优及问题查找(二)

Thread Cache 命中率：（应该保持在90%以上）

Thread_Cache_hit = (Connections - Threads_created) / Connections * 100%;

MyISAM引擎优化

MyISAM引擎适用场景：以读为主的非事务性数据系统，对数据的准确性要求不高时有优异的性能表现。

系统参数：

key_buffer_size: 索引缓存大小

key_buffer_block_size: 索引缓存中的Cache Block Size:

key_cache_division_limit: LRU链表中的Hot Area和Warm Area的分界值（范围1-100），系统默认100，及只有Warm Cache。

key_cache_aeg_threshold: 控制 Cache Block从Hot Area降到 Warm Area的限制

性能参数：

key_block_not_flushed 已经更改但还未刷新到磁盘的Dirty Cache Block

key_blocks_unused 目前未被使用的Cache Block数目

key_read_requests Cache Block被请求读取的总次数

key_read, 在Cache Block中找不到需要读取的Key信息后到 .MYI 文件中读取的次数

key_write_requests, Cache Block被请求修改的总次数

key_writes 在Cache Block中找不到需要修改的Key信息后到 .MYI 文件中读入再修改的次数

参数合理性判断指标：

Key_buffer_UsageRatio = ( 1- Key_blocks_used / (key_blocks_used +key_blocks_unused ) ) * 100%

(应该在99%以上，如果该值过低，说明key_buffer_size设置过大，MySQL根本用不完)

Key_buffer_read_HitRatio = ( 1- Key_reads / key_read_requests ) * 100%

(应该在99%以上，如果值过低，说明key_buffer_size设置过小，需要增加；也可能是key_cache_age_threshold 和 key_cache_division_limit 的设置不当，造成 Key Cache cache 失效太快 。一般来说，在实际应用场景中，很少有人调整 key_cache_age_threshold 和 key_cache_division_limit 这两个参数的值，大都是使用系统的默认值)

Key_buffer_write_HitRatio = ( 1- Key_writes / key_Write_requests ) * 100%

多Cache系统

MySQL 官方建议在比较繁忙的系统上一般可以设置三个 Key Cache ：

一个 Hot Cache 使用 20% 的大小用来存放使用非常频繁且更新很少的表的索引；

一个 Cold Cache 使用 20% 的大小用来存放更新很频繁的表的索引；

一个 Warm Cache 使用剩下的 60% 空间，作为整个系统默认的 Key Cache ；

Key Cache 的 Mutex 问题：目前MySQL在Active线程数量较高时非常容易出现 Cache Block 的锁问题 www.2cto.com

Key Cache 预加载

在 MySQL 中，为了让系统刚启动之后不至于因为 Cache 中没有任何数据而出现短时间的负载过高或 者

是响应不够及时的问题。 MySQL 提供了 Key Cache 预加载功能，可以通过相关命令（ LOAD INDEX INTO CACHE tb_name_list ... ），将指定表的所有索引都加载到内存中，而且还可以通过相关参数控制是否只 Load 根结点和枝节点还是将页节点也全部 Load 进来，主要是为 Key Cache 的容量考虑。

对于这种启动后立即加载的操作，可以利用 MySQL 的 init_file 参数来设置相关的命令，如下：

mysql@sky:~$ cat /usr/local/mysql/etc/init.sql

SET GLOBAL hot_cache.key_buffer_size=16777216

SET GLOBAL cold_cache.key_buffer_size=16777216

CACHE INDEX example.top_message in hot_cache

CACHE INDEX example.event in cold_cache

LOAD INDEX INTO CACHE example.top_message,example.event IGNORE LEAVES

这里我的 init file 中首先设置了两个 Key Cache （ hot cache 和 cold cache ）各为 16M ，然后分别将 top_message 这个变动很少的表的索引 Cache 到 Hot Cache ，再将 event 这个变动非常频繁的表的索引Cache 到了 Cold Cache 中，最后再通过 LOAD INDEX INTO CACHE 命令预加载了 top_message，groups 这两个表所有索引的所有节点以及 event 和 user 这两个表索引的非叶子节点数据到 Key Cache 中，以提高 系统启动之初的响应能力。

www.2cto.com

其他可以优化的地方

1. 通过 OPTIMIZE 命令来整理 MyISAM 表的文件。这就像我们使用 Windows 操作系统会每过一段时间后都会做一次磁盘碎片整理，让系统中的文件尽量使用连续空间，提高文件的访问速度。 MyISAM 在通过 OPTIMIZE 优化整理的时候，主要也是将因为数据删除和更新造成的碎片空间清理，使整个文件连续在一起。一般来说，在每次做了较大的数据删除操作之后都需要做一次 OPTIMIZE 操作。而且每个季度都应该有一次 OPTIMIZE 的维护操作。

2. 设置 myisam_max_[extra]_sort_file_size 足够大，对 REPAIR TABLE 的效率可能会有较大改善。

3. 在执行 CREATE INDEX 或者 REPAIR TABLE 等需要大的排序操作的之前可以通过调整 session 级别的 myisam_sort_buffer_size 参数值来提高排序操作的效率。

4. 通过打开 delay_key_write 功能，减少 IO 同步的操作，提高写入性能。

5. 通过调整 bulk_insert_buffer_size 来提高 INSERT...SELECT... 这样的 bulk insert 操作的整体性能， LOAD DATA INFILE... 的性能也可以得到改善。当然，在设置此参数的时候，也不应该一味的追求很大，很多时候过渡追求极端反而会影响系统整体性能，毕竟系统性能是从整体来看的，而不能仅仅针对