MyISAM内存优化

MyISAM内存优化

​一、key_buffer_size

​ key_buffer_size 决定 MyISAM 索引块缓冲区的大小，它直接影响 MyISAM 表的存取效率。对于一般的 MyISAM 数据库，建议至少将 1/4 可用内存分配给 key_buffer_size

​ 1、通过索引块的物理读写比率衡量 key buffer 的效率

Key_reads / Key_read_requests 索引块物理读比率 应该小于 0.01

Key_writes / Key_write_requests 索引块写比率也应该尽可能小，但对于更新和删除操作特别多的应用，此值可能会接近 1 ; 而对于每次更新很多行记录的应用就会比较小。

Key_reads			从硬盘读取键的数据块的次数。
Key_read_requests	从缓存读键的数据块的请求数。
Key_writes			数据块写入磁盘的次数
Key_write_requests	写入cache的请求数

​ 2、评估 key buffer 的使用率来判断索引缓存设置是否合理,使用率在 80% 左右比较合适，大于 80% 可能因索引缓存不足而导致性能下降，小于 80% ，会导致内存浪费。

key buffer 使用率 = 1-((key_blocks_unused * key_cache_block_size)/key_buffer_size)

二、多个索引缓存

​ session 间会对 key buffer 进行竞争，如果一个 session 对一个很大的索引进行扫描，就可能将其他的索引数据块挤出索引缓存区。

​ 1、通过命令行创建索引缓存

1)创建新的 key buffer , 其中 hot_cache 为新建索引缓存的名称
set global hot_cache.key_buffer_size=128*1024;

2)删除索引缓存（不能删除默认的key buffer）
set global hot_cache.key_buffer_size=0;

3)指定表的索引缓存
mysql> cache index tload,tmyisam in hot_cache;
+--------------+--------------------+----------+----------+
| Table        | Op                 | Msg_type | Msg_text |
+--------------+--------------------+----------+----------+
| test.tload   | assign_to_keycache | status   | OK       |
| test.tmyisam | assign_to_keycache | status   | OK       |
+--------------+--------------------+----------+----------+
2 rows in set (0.00 sec)

​ 2、通过配置文件在 MySQL 启动时自动创建并加载索引缓存

1)配置文件设置
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 1G
init_file=/path/to/mysqld_init.sql

2)通过 mysqld_init.sql 进行索引预加载
cache index tload in hot_cache;
cache index tmyisam in hot_cache;
load index into cache tload,tmyisam;

三、启动中点插入策略

​ key_cache_division_limit 控制多大比例的缓存用做 warm 表， key_cache_division_limit 的默认值是 100，意思是全部缓存块都放在 warm 子表，也就是不启动“中点插入策略”。如果希望将 30% 的缓存用来 cache 最热的索引块，可如下配置。

set global key_cache_division_limit=70;
set global hot_cache.key_cache_division_limit=70;

​ key_cache_age_threshold 控制数据块有 hot 子表向 warm 子表降级的时间。如果一个在 hot 子表头部的索引块，在最后 N*key_cache_age_threshold/100 次缓存命中内未被访问，就会降级到 warm 表。

四、read_buffer_size 和 read_rnd_buffer_size

​ 调整 read_buffer_size 和 read_rnd_buffer_size ，注意：这两个参数是每个 session 独占的，默认值太大，会造成内存浪费，甚至导致物理内存耗尽。

1)如果需要经常顺序扫描 MyISAM 表，可以增大 read_buffer_size

2)对于需要做排序的 MyISAM 表查询，如带有 order by 子句的 SQL ，可以适当增大 read_rnd_buffer_size 的值。