这段时间mysql 数据库的性能明显降低,iowait达到了30, 响应时间明显变长. 通过show processlist 查看,发现有很多session在处理sort 操作, 跟DBA一起调试优化,增大sort_buffer_size 好象效果也不大, 通过查看监控,也没发现有硬盘排序. 我怀疑是sort导致性能下降,固让开发修改程序, sort由程序来处理. 星期五发布后,今天发现压力固然好了很多.
因此基本上能确定是sort引起的问题. 今天仔细分析问题,查看mysql的参数时,看到一个叫做max_length_for_sort_data 的参数, 值是1024 仔细查看mysql 的filesort算法时, 发现mysql的filesort有两个方法,MySQL 4.1之前是使用方法A, 之后版本会使用改进的算法B, 但使用方法B的前提是列长度的值小于max_length_for_sort_data, 但我们系统中的列的长度的值会大于1024. 因此也就是说在sort的时候, 是在使用方法A, 而方法A的性能比较差, 也就解释了我们的mysql系统在有sort时,性能差,去掉之后性能马上提高很多的原因.
马上修改max_length_for_sort_data这个值，增大到8096, 果然性能就提高了.
总结:
mysql对于排序,使用了两个变量来控制sort_buffer_size和 max_length_for_sort_data, 不象oracle使用SGA控制. 这种方式的缺点是要单独控制,容易出现排序性能问题.
对于filesort的两个方法介绍,以及优化方式,见
http://forge.mysql.com/wiki/MySQL_Internals_Algorithms
Using the modified filesort algorithm, the tuples are longer than the pairs used in the original method, and fewer of them fit in the sort buffer (the size of which is given by sort_buffer_size). As a result, it is possible for the extra I/O to make the modified approach slower, not faster. To avoid a slowdown, the optimization is used only if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable. (A symptom of setting the value of this variable too high is that you should see high disk activity and low CPU activity.)
分享到：
相关推荐
"g.".$data['spe_data']['sort_order']." ".$data['spe_data']['asc_desc'] : "gst.".$data['spe_data']['sort_order']." ".$data['spe_data']['asc_desc']; } $con = array( 'conditions' => "1=1 ". $...
i for(int j=data.length-1;j>i;j--){ if(data[j] SortUtil.swap(data,j,j-1); } } } } } 改进后的快速排序 package org.rut.util.algorithm.support; import org.rut.util.algorithm.SortUtil; public class ...
for (i=L.length/2; i>0; i--)// build the heap { Heap_Adjust(L,i,L.length); } for (i=L.length; i>1; i--) { L.r[0]=L.r[i];// 将 堆顶记录和当前未经排序子队列（1——i）中 的 最后一个记录 交换 L...
a.cleanData=function(b){for(var d=0,e;(e=b[d])!=null;d++)try{a(e).triggerHandler("remove")}catch(f){}c(b)}}else{var d=a.fn.remove;a.fn.remove=function(b,c){return this.each(function(){c||(!b||a.filter...
data (cppstring) #define (preproc) difftime (stddate) div (stdmath) empty (cppdeque) empty (cpplist) empty (cppmap) empty (cppmultimap) empty (cppmultiset) empty (cpppriorityqueue) empty ...
return{key:e.type,data:n}},t._utf8ArrayToStr=function(t){for(var e=void 0,r=void 0,i="",a=0,n=t.length;a;){var o=t[a++];switch(o>>4){case 0:return i;case 1:case 2:case 3:case 4:case 5:case 6:case 7:i+...
1.2.11 data 6 1.2.12 empty 6 1.2.13 end 6 1.2.14 erase 6 1.2.15 find 6 1.2.16 find_first_not_of 7 1.2.17 find_first_of 8 1.2.18 find_last_not_of 8 1.2.19 find_last_of 8 1.2.20 get_allocator 8 1.2.21 ...
1.2.11 data 7 1.2.12 empty 7 1.2.13 end 7 1.2.14 erase 7 1.2.15 find 7 1.2.16 find_first_not_of 8 1.2.17 find_first_of 8 1.2.18 find_last_not_of 9 1.2.19 find_last_of 9 1.2.20 get_allocator 9 1.2.21 ...
(select max(rowid) from a b where a.aa=b.aa); 13、删除同其他表相同的行 delete from a where exits (select \'X\' from b where b.no=a.no); 或 delete from a where no in (select no from b); 14、查询...
Even faster Key/Value store nosql embedded database engine utilizing the new MGIndex data structure with MurMur2 Hashing and WAH Bitmap indexes for duplicates. See Also More like this More by this...
//sortOrder:'asc', striped:true, toolbar:[{ text:'New', iconCls:'icon-add', handler:function(){ if(listenerFieldsEditCount>0){ jq.messager.alert("error","有可编辑的单元格，不能添加!",'...
// max decoder pixel stack size // LZW decoder working arrays protected short[] prefix; protected byte[] suffix; protected byte[] pixelStack; protected byte[] pixels; protected ArrayList frames...
bufferLength NetStream.bufferLength bufferTime NetStream.bufferTime builtInItems ContextMenu.builtInItems bullet TextFormat.bullet 按钮 Button 类 bytesLoaded NetStream.bytesLoaded ...
if(e)for(var f=0,g=e.length;g>f;f++)if(e[f]==b){1==g?delete d[a]:e.splice(f,1);break}}},a.off=a.removeEventListener,a.removeAllEventListeners=function(a){a?(this._listeners&&delete; this._listeners[a]...
public static int len = data.length; public static void main(String args[]) { //要查找的数 int keyValue = 89; Test t = new Test(); boolean b = t.BinarySearch(keyValue); if(b) ...
* [Data Structure](https://github.com/kamyu104/LeetCode#data-structure) * [Math](https://github.com/kamyu104/LeetCode#math) * [Two Pointers](https://github.com/kamyu104/LeetCode#two-pointers) * [Sort]...
"This technique allows a program to save hierarchical information like the data in a TreeView in a way that is easy to understand."<END><br>11 , OLE.zip Demonstrates the use of OLE.<END><br>12 , ...
Toast.makeText(mContext, mContext.getResources().getString(R.string.publish_select_photo_max, maxCount), Toast.LENGTH_SHORT).show(); return; } mSelectlist.add(imageBean); imageBean....
JavaScript权威指南 犀牛书 Chapter 1. Introduction to JavaScript Section 1.1. JavaScript Myths Section 1.2.... Section 1.3.... Section 1.4.... Section 1.5.... Section 1.6.... Section 1.7.... Array.sort( ...
error('Non-binary data cannot be clustered using Hamming distance.'); end end else error('The ''distance'' parameter value must be a string.'); end % ---------------------------------------------...