Repair with keycache产生的原因和解决方法

11月 26th, 2010 | Posted by | Filed under 未分类

今天给一个MyISAM的大表加索引,临时表MYD文件的大小增长非常慢,到了MYD文件创建完成后,却发现MYI文件很小,ALTER TABLE语句始终不能结束,登录MYSQL查看show processlist;发现,ALTER TABLE语句在“Repair with keycache”状态。
于是想到,myisam_max_sort_file_size参数我们设置的是10G,但是现在这个文件正好>10G一点点,于是不能用tmpdir指定的目录排序索引。于是增加myisam_max_sort_file_size参数到20G,再次ALTER TABLE,首先MYD文件的创建速度就快了非常多,MYD文件创建结束后,MYI文件开始增长,查看processlist,发现在“Repair by sorting”状态,很快表创建完成。
如果遇到类似的情况,你可以一试。

自动生成索引的脚本

11月 12th, 2010 | Posted by | Filed under 程序设计

写了个自动建索引的脚本,基本原理是解析SQL的条件,然后得出每个条件的运算符,最后通过优先级矩阵将可以建索引的列排序求最长的序列,也就是尽可能把更多的列包含进索引。

使用方法:
./AutoCreateIndex.pl -e “你的SQL

对于多表连接,务必将列名前跟上表名,否则无法处理,因为脚本并不连接数据库,无法判断哪个列属于那个表。
测试版本,Bug很多,欢迎提供意见。

  AutoCreateIndex.pl (5.3 KiB, 3,162 hits)

下面是一些例子

plx@plinux-Laptop:~/Dropbox/MySQLScript$ ./AutoCreateIndex.pl -e "SELECT * FROM foo1 a,foo2 b WHERE 'fdsfsa'=a.col1 AND b.col2 = 'abc' AND a.col1=b.col2 AND a.col3 >1 ORDER BY b.col3,b.col1"
ALTER TABLE foo1 ADD INDEX idx (col1,col3);
ALTER TABLE foo2 ADD INDEX idx (col2,col3,col1);
plx@plinux-Laptop:~/Dropbox/MySQLScript$ ./AutoCreateIndex.pl -e "SELECT * FROM t1 where t1 =1 and t2=2"
ALTER TABLE t1 ADD INDEX idx (t2,t1);
plx@plinux-Laptop:~/Dropbox/MySQLScript$ ./AutoCreateIndex.pl -e "SELECT * FROM t1 where col1=1 and col2=4"
ALTER TABLE t1 ADD INDEX idx (col1,col2);
plx@plinux-Laptop:~/Dropbox/MySQLScript$ ./AutoCreateIndex.pl -e "SELECT * FROM t1 where col1>1 and col2=4"
ALTER TABLE t1 ADD INDEX idx (col2,col1);

静态编译TCMalloc到MySQL

10月 30th, 2010 | Posted by | Filed under 未分类

Linux下的malloc函数性能问题,想必大部分在Linux下写C的人都深有感受,纷纷利用内存池来改进内存分配效率。
Google开源的tcmalloc则改进了malloc的一些效率问题,在大量malloc和free时,操作系统的内存曲线明显比Linux下malloc函数要平稳,在大并发情况下,提升程序稳定性和性能。
一般网上都是把tcmalloc动态库加到mysqld_safe中启动,但是我们的MySQL都是静态编译的,这时候动态加载是否生效呢?所以还是静态编译入MySQL好。

编译tcmalloc先要编译libunwind:

wget http://download.savannah.gnu.org/releases/libunwind/libunwind-0.99.tar.gz
tar zxvf libunwind-0.99.tar.gz

CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 -fPIC \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure && make && make install

然后编译tcmalloc:

tar zxvf google-perftools-1.6.tar.gz

CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure && make && make install

记得要把libtammloc加入系统路径,否则编译MySQL时找不到:

echo “/usr/local/lib” > /etc/ld.so.conf.d/usr_local_lib.conf
/sbin/ldconfig

最后就是编译MySQL了:

CXX=gcc \
CHOST=”x86_64-pc-linux-gnu” \
CFLAGS=” -O3 \
-fomit-frame-pointer \
-pipe \
-march=nocona \
-mfpmath=sse \
-m128bit-long-double \
-mmmx \
-msse \
-msse2 \
-maccumulate-outgoing-args \
-m64 \
-ftree-loop-linear \
-fprefetch-loop-arrays \
-freg-struct-return \
-fgcse-sm \
-fgcse-las \
-frename-registers \
-fforce-addr \
-fivopts \
-ftree-vectorize \
-ftracer \
-frename-registers \
-minline-all-stringops \
-felide-constructors \
-fno-exceptions \
-fno-rtti \
-fbranch-target-load-optimize2″ \
CXXFLAGS=”${CFLAGS}” \
./configure –prefix=/usr/alibaba/install/mysql-ent-custom-5.1.49sp1 \
–with-server-suffix=-alibaba-edition \
–with-mysqld-user=mysql \
–with-plugins=partition,blackhole,csv,heap,innobase,myisam,myisammrg \
–with-charset=utf8 \
–with-collation=utf8_general_ci \
–with-extra-charsets=gbk,gb2312,utf8,ascii \
–with-big-tables \
–with-fast-mutexes \
–with-zlib-dir=bundled \
–enable-assembler \
–enable-profiling \
–enable-local-infile \
–enable-thread-safe-client \
–with-readline \
–with-pthread \
–with-embedded-server \
–with-client-ldflags=-all-static \
–with-mysqld-ldflags=-all-static \
–with-mysqld-ldflags=-ltcmalloc \
–without-query-cache \
–without-geometry \
–without-debug \
–without-ndb-debug
make && make install

经过试用,大并发下内存分配和释放曲线都比Linux原生的平稳。

数据分布对MySQL执行计划的影响

10月 9th, 2010 | Posted by | Filed under 未分类

以前我一直以为,MySQL优化器只会根据数据的基数来判断执行计划的优化,在5.0时,基本上只要基数达不到要求,MySQL就不会选择索引。不过从今天优化一组SQL的情况来看,5.1早已不是这样,MySQL优化器考虑了数据分布的影响,使用不同的值,对同一条SQL可能产生完全不同的执行计划。可以做如下测试。

有一张表 A (id,c1),假设采用“SELECT c1,count(*) FROM table GROUP BY c1”的方式来查看每一列每个值的数据量的结果如下:

c1 count(*)
1 100
2 1000
3 10000
4 100000

假设现在c1上有个索引 idx_1 (c1)。
当我采用只有100个值的1作为条件的筛选值查看执行计划时,
MySQL会毫不犹豫的走 idx_1 索引。
但是改为4作为筛选值,基本上都可以看到全表扫描的执行计划。

EXPLAIN SELECT * FROM A WHERE c1=1;
EXPLAIN SELECT * FROM A WHERE c1=4;

虽然从源码目录的 sql/sql_select.cc 中并未找到具体的优化代码,但是从sql/opt_range.cc来看,至少RANGE查询的优化已经通过蒙特卡罗方法来估算要选择的值的出现概率,那么有理由相信,如此简单的选择查询,MYSQL肯定也估算了要查询的值出现的概率,然后以此为依据计算访问路径。
所以,一条SQL优化时,不仅要看数据的离散度,还得看经常被当作筛选值的数值唯一性是否够好,如果被筛选的值唯一性不好,建立索引依然是没有什么意义的,因为MySQL根本不会选择这个索引。

中小站点的监控利器——监控宝

9月 12th, 2010 | Posted by | Filed under 技术笔记

监控宝

对于任何一个希望保证可用性的站点,监控都是必不可少的组成部分。
但是对于大部分中小站点和个人站点,是没有余力去开发一套监控系统的,大都采用的方法都是利用开源的产品,但是开源产品配置上大都比较麻烦,而且需要占用宝贵的服务器资源,并且需要去运维部署的监控系统,对于资源本身就比较紧张的中小站点,是一笔不小的开支。
监控宝是一个第三方的监控站点,它可以从外部对网站实施监控,并且可以监控用户体验等自身比较难实现的监控点,即使内网也能通过端口映射来实现。监控宝部署在云智慧自己的服务器上,并不占用网站自身的服务器资源,也不需要自己去运维这个系统,这一点是使用Nagios、Cacti等开源监控系统不能比的。
监控宝通过SNMP协议获取主机性能,这是一个安全的协议,并不会带来安全隐患。同时监控宝还内置了MySQL/Apache/LightHttp/Nginx/Memcached等常用的服务监控,并且有接口可以自己扩展需要的监控项。
监控宝的关联功能可以将域名、服务、性能、告警等监控合为一体,自定义视图可以组合自己认为重要的性能视图,通过可靠的短信告警和Email/Gtalk等告警方式,可以及时的获取网站的异常。
通过共享功能,运维人员可以将视图共享给其他用户只读,这样可以让开发人员也了解服务器的状况,但不会修改任何配置。
试想,一个中小站点,自己搭建一套性能监控、故障告警系统,运维它的代价会有多高,使用监控宝,一切都变得简单、可靠。