对MySQL数据库CPU/IO瞬间出现峰值的思索
CPU负载如下:
报警信息时不时的提示,某台数据库服务器的负载爬高,异常烦人。通过对数据库分析以及参考网络资源,针对此现象总结了一下思路。
针对CPU方面:可调整tmp_table_size,max_heap_table_size,sort_buffer_size,thread_cache_size.在负载高的时候,show processlist时会发现有很多copy tmp table,适当的调整下tmp_table_size大小。如果还有converting HEAP to MyISAM时,需要调整max_heap_table_size。大部分SQL语句都含有order by和group by,sort_buffer_size可以提高这类语句的执行效率,这个参数是每线程独占的。可以通过查看sort_merge_passes状态值的变化频率来设置sort_buffer_size大小;另外为了避免创建临时表,可通过索引就能实现排序分组的目的,这样也就大大减少了CPU的消耗。filesort有两种算法:低效率的算法需要读两次表,升级版的算法一次就能将最终的结果取出,而它就需要缓存更多的信息,一般都会选择效率高的升级版的算法,除非筛选字段有blob或text字段,可以调整max_length_for_sort_data大小来提高升级算法的效率。每一个新的连接,都是需要很大的CPU资源的,需要设置thread_cache_size来缓存一些线程,以减少CPU的消耗。
针对IO方面:在IO高时,记录下当时的SQL语句,分析各个语句对IO的消耗情况,有针对性的添加索引,减少扫描的数量。需要提高磁盘的性能,可改变磁盘调度方法,添加memcache,使用RAID等,以减少在磁盘间进行操作。