MYSQL内存管理及优化
内存是影响数据库性能的主要资源,也是mysql性能优化的一个重要方面;
内存优化的原则
- 将尽量多的内存分配给mysql做缓存,但是要给操作系统和其他程序的运行预留足够的内存,否则产生swap页交换,将严重影响系统性能;
- myisam的数据文件读取以来操作系统自身的io缓存,如果有myisam的表,就要预留更多的内存给操作系统做io缓存;
- 排序区,连接区等缓存是分配给每个数据库会话session专用的,默认值的设置要根据最大的连接数据合理分配,如果设置的太大,不但浪费内存资源,在并发比较高的时候会导致物理内存耗尽;
MyISAM内存优化
MyISAM存储引擎使用 key_buffer缓存索引块,以加速MyISAM索引的读写速度。
MyISAM表的数据库,mysql没有特别的索引机制,完全依赖系统的io 缓存。
1.ey_buffer_size 设置
key_buffer_size决定MyISAM索引缓存区的大小;一般可以内存的1/4;
2.用多个索引缓存
3.整 中点插入策略
4.整 read_buffer_size 和 read_rnd_buffer_size
innodb的内存优化
innod的缓存机制
innodb用以块内存做io缓存池,该缓存池不仅用来缓存innodb的索引,而且也用来缓存innodb的数据块;
与MyISAM是不同的;
innod的缓存机制
innodb_buffer_pool_size的设置
innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓存区的大小;
与oracle的缓存机制很类似。
缓存区越大,缓存的命中率越高,需要的磁盘IO越少,性能越高。
在一个专用的数据库服务器上,可以将80%的物理内存分配给innodb buffer pool ,但是一定要注意设置过大导致页交换,系统或其他的程序可以内存不足;
调整old sublist 大小
old sublist的比例有innodb_old_blocks_pct决定,默认是37;
在没有比较大的表扫描或索引扫描的情况下,如果youngs的值很低,可能需要适当的增大innodb_old_blocks_pct的值,或者减少 innodb_old_blocks_time的值。
调整innodb_old_blocks_time的设置;
这个参数决定了缓存数据块由old sublist转移到young sublit的快慢;
调整缓存池的数量,减少内部对缓存池数据结构的挣用;
mysql内部不同的线程对innodb缓存池的访问在某些阶段是互斥的,这种内部的挣用现象也会产生性能问题,尤其是在搞并发和buffer pool 比较大的情况下。
通过inndb_buffer_pool_instances参数设置
控制innodb buffer刷新,延长数据缓存时间,减缓磁盘io;
innodb doublewrite