每天进步一点点————优化MySQL SERVER（1）MyISAM篇

1.   优化MySQL SERVER

7组后台进程

         masterthread：主要负责将脏缓存页刷新到数据文件，执行purge操作，触发检查点，合并插入缓冲区等。

         insertbuffer thread:主要负责插入缓冲区的合并操作。

         readthread:负责数据库读取操作，可配置多个线程

         writethread:负责数据库写操作，可配置多个线程。

         logthread:用于将重做日志刷新到logfile中。

         purgethread:MySQL5.5之后用于单独的purge thread 执行purge操作。

         lockthread:负责锁控制和死锁检测。

         错误监控线程：主要负责错误监控和错误处理。

内存管理及优化

内存管理原则

在调整MySQL内存分配时，要注意以下几点。

         将尽量多的内存分配给MySQL做缓存，但是要给操作系统和其他程序的运行预留足够的内存，否则如果产生SWAP页交换，将严重影响系统性能。

         MyISAM的数据文件读取依赖操作系统自身IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

         排序区、连接区等缓存是分配给每个数据库会话(seesion)专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存消耗尽。

MyISAM内存优化

MyISAM存储引擎使用 key buffer缓存索引块，以加速MyISAM索引的读写速度。对于MyISAM变的数据库块，MySQL没有铁别的缓存机制，完全依赖操作系统的IO缓存。

key_buffer_size设置

key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。

我们可以通过检查key_read_requests、key_reads、key_write_requests和key_writes等MySQL状态来评估索引缓存的效率。一般来说，索引块物理读比率key_reads/key_read_requests应该小于0.01。索引块写比率key_wirte/key_write_requests也应尽可能小。但是这与应用特点有关，对于更新和删除操作特别多的应用，key_writes/key_wirte_requests可能会接近1，而对于 更新很多行记录的应用，key_wirtes/key_write_requests就会比较小。

除了通过索引块的物理读写比率衡量key_buffer的效率外，我们也可以通过评估key buffer的使用率来判断索引缓存设置是否合理。key buffer使用率计算公式如下：

         1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)

         一般来说，使用率在80%左右笔记哦啊合适，大于80%可能因索引还粗不足而导致性能下降，小于80%会导致内存浪费

设置key_buffer_size

mysql>set global key_buffer_size=8088608;

Query OK, 0 rows affected, 1 warning (0.00sec)

永久设置

[root@localhostbinlog]# vim /etc/my.sql

[mysql]

key_buffer_size=8088608

查看key_buffer_size

mysql> show variables like'key_buffer_size';

+-----------------+---------+

| Variable_name   | Value  |

+-----------------+---------+

| key_buffer_size | 8388608 |

+-----------------+---------+

1 row in set (0.00 sec)

 

mysql>show global status like 'key_%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Key_blocks_not_flushed | 0     |

| Key_blocks_unused      | 6452 |

| Key_blocks_used        | 0    |

| Key_read_requests      | 0    |

| Key_reads              | 0     |

| Key_write_requests     | 0    |

| Key_writes             | 0     |

+------------------------+-------+

7 rows in set (0.00 sec)

使用多个索引缓存

MySQL通过session共享的key buffer提高了MyISAM索引存取的性能，但是冰不能消除session间对key buffer的竞争。比如一个session如果对某个很大的索引进行扫描，就可能将其他的索引数据基础索引缓存区，而这些索引块可能是其他session要用的热数据。

新建key buffer

mysql>set global hot_cache.key_buffer_size=128*1024;

Query OK, 0 rows affected (0.01 sec)

hot_cache是新建索引的缓存名。

删除key buffer

mysql>set global hot_cache.key_buffer_size=0;

Query OK, 0 rows affected (0.01 sec)

但是我们不能删除默认的keybuffer

将某个表的索引加入到新建的key buffer中

mysql>cache index t1 in hot_cache;

+----------+--------------------+----------+----------+

| Table   | Op                 | Msg_type |Msg_text |

+----------+--------------------+----------+----------+

| test2.t1 | assign_to_keycache |status   | OK       |

+----------+--------------------+----------+----------+

1 row in set (0.00 sec)

T1表示T1表

 

在配置文件自动创建并加载索引缓存

[root@localhostbinlog]# vim /etc/my.sql

[mysql]

key_buffer_size=8088608

hot_cache.key_buffer_size=128*1024

cold_cache.key_buffer_size=1G

init_file=/usr/local/mysql/config/mysqld_init.sql

[root@localhostbinlog]# vim /usr/local/mysql/config/mysqld_init.sql
cache index t1 hot_cache

cache index t2 cold_cache;

load index into cache t1,t2;

调整“中点插入策略”

         MySQL默认使用简单的LRU（LeastRecently Used）策略来选择要淘汰的索引数据库。但这种算法很可能在某些情况下导致真正的热块被淘汰。

         如果出现这种情况，除了使用上面介绍的多高索引缓存机制外，还可以利用中点插入策略(Midpoint Insertion Strategy）来优化索引块淘汰算法。

         可以调整key_cache_division_limit来控制多大比例的缓存块做warm子表，key_cache_division_limit的默认值是100，意思是全部缓存块都放在warm子表，其实也就是不启用“中点查询策略”。如果我们希望大致30%的缓存用来cache最热的索引块，可以对key_cache_division_limit做如下设置；

         SETglobal key_cache_division_limit=70;

         SETglobal hot_cache.key_cache_division_limit=70;

除了调节warm子表的比例外，还可以通过key_cache_age_threshold控制数据块由hot子表向warm子表降级的时间，值越小，数据降级越快。对于有N个块的索引缓存来说，如果一个在hot子表头部的索引块,在最后N*key_cache_age_threshold/100次缓存命准内未被访问过，就会被降级到warm子表

调整read_buffer_size和read_rnd_buffer_size

         如果需要经常扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能。但是注意的是read_buffer_size是每个session独占的，如果默认设置太大就会造成资源浪费，甚至导致物理内存耗尽。

         对于需要排序的MyISAM表，应适当增大read_rnd_buffer_size的值，也可以改善此类SQL的性能。但是要注意此参数也是按SESSION分配的。