深入浅出mysql_优化sql server

二十一 优化mysql Server


mysql的体系结构


mysql的内存管理优化


内存优化原则


1. 将尽量多的内存分配给mysql做缓存，但要给操作系统和其他应用程序足够多的内存，否则产生swap页交换，将严重影响系统性能。


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


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


MyISAM内存优化


1 key_buffer_size设置
该配置决定了MyISAM索引块缓存区的大小，他直接影响了MyISAM表的存取效率。
可以在mysql参数文件中设置key_buffer_size的值。对于一般MyISAM表，建议至少是可用内存的四分之一；


评估索引缓存的效率：
1.可用通过检查key_reads 、key_read_requests、 key_writes、key_write_requests等mysql状态变量来评估索引缓存的效率；
指标：
读比率：一般来说，索引块物理读比率key_reads/key_reads_requests应该小于0.01。
写比率：索引块写比率 key_writes/key_write_requests也应该尽量的小，但这与应用特点有关，对于更新和删除操作特别多的应用，key_writes/key_write_requests比率可能接近1。
key_buffer 的使用率： 可以通过评估 key buffer 的使用率来判断索引缓存是否合理。 计算公式： 1 - ( (key_blocks_unuesd*key_cache_blokc_size ) / key_buffer_size )。
一般使用率在80%左右合适，大于80%可能因为缓存不足而导致性能下降，小于80%可能导致内存浪费。


2. 使用多个索引缓存


3 调整”中点插入策略“


4 , 调整read_buffer_size和read_md_buffer_size


InnoDB内存优化


1 InnoDB缓存机制


2 . innodb_buffer_pool_size的设置


3 调整old_sublist大小


4 调整innodb_old_blocks_time的设置


5 调整缓存池数量


6。 控制InnoDB_buffer 刷新，延长数据缓存时间，减缓磁盘IO


7. InnoDB doublewrite


调整用户服务线程排序缓存区


InnoDB的log机制及优化


InnoDB重做日志


innodb_flush_log_at_trx_commit设置


设置 log file size ，控制检查点


调整 innodb_log_buffer_size


调整mysql并发相关参数


调整max_connections,提高并发量


调整back_log


调整table_open_cache


调整thread_cache_size


innodb_lock_wait_time的设置