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 的值。