ubuntu6.06下编译MySQL5.0.26（打slow log补丁）

gladness

                                                MySQL的slow log可以用来看执行时间超过指定时间的SQL，但是指定的时间最小只能是一秒，有点太大了。通常是要找那些执行次数很多但每次执行又超不过一秒的SQL。打一个补丁，即可记录所有SQL的详细执行时间。原文见：
为slow log打补丁的原文
注意这里用了prefix，是因为机器还有一个mysql5.0.22，我不想引发冲突。
brum@brum-laptop:~/mysql-5.0.26$ ./configure --prefix=/usr/local/mysql
....
....
checking for termcap functions library... configure: error: No curses/termcap library found
需要安装libncurses5-dev包，安装之后，configure就通过了
补丁从这里下载：
打补丁
brum@brum-laptop:~$ pwd
/home/brum
brum@brum-laptop:~$ patch -p0
好了，成功了。
               
                修改/usr/local/mysql/etc/my.cnf
在[mysqld]中加上
log-slow-queries
long-query-time=0
前者是让mysql记录slow query；后者是利用前面打的补丁，设置为0的含义是把所有的SQL都记录在slow query log中。如果不打补丁，那么long-query-time最小只能设为1，即1秒，即只有超过1秒的SQL才会记录。打了补丁则没有这个限制了。
随便执行了几个SQL，看一下日志：
brum@brum-laptop:~/mysql-5.0.26$ cat /usr/local/mysql/var/brum-laptop-slow.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.26-log. started with:
Tcp port: 3307  Unix socket: /usr/local/mysql/var/mysql.sock
Time                 Id Command    Argument
# Time: 061104 17:47:22 # User@Host: [brum] @ localhost []
# Query_time: 0.014273  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use test;
create table test(a int, b varchar(100))engine=innodb;
# Time: 061104 17:47:50 # User@Host: [brum] @ localhost []
# Query_time: 0.000378  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
alter table test modify a auto_increment primary key;
# Time: 061104 17:48:23 # User@Host: [brum] @ localhost []
# Query_time: 0.007990  Lock_time: 0.000329  Rows_sent: 0  Rows_examined: 0
SET insert_id=1;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:31 # User@Host: [brum] @ localhost []
# Query_time: 0.004855  Lock_time: 0.000116  Rows_sent: 0  Rows_examined: 0
SET insert_id=2;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:32 # User@Host: [brum] @ localhost []
# Query_time: 0.000767  Lock_time: 0.000114  Rows_sent: 0  Rows_examined: 0
SET insert_id=3;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:34 # User@Host: [brum] @ localhost []
# Query_time: 0.031763  Lock_time: 0.000115  Rows_sent: 0  Rows_examined: 0
SET insert_id=4;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:40 # User@Host: [brum] @ localhost []
# Query_time: 0.000846  Lock_time: 0.000116  Rows_sent: 4  Rows_examined: 4
select * from test;
# Time: 061104 17:48:43 # User@Host: [brum] @ localhost []
# Query_time: 0.000042  Lock_time: 0.000000  Rows_sent: 4  Rows_examined: 4
# administrator command: Quit;
精确到微秒级了。然后，还可以下载一个mysql_slow_log_parser，在我上面提供的链接中可以下载。
brum@brum-laptop:~/MySQL5.0.26SlowLogPatch$ ./mysql_slow_log_parser /usr/local/mysql/var/brum-laptop-slow.log
Starting...
### 1 Query
### Total time: 0.014273, Average time: 0.014273
### Taking 0.014273  seconds to complete
### Rows analyzed 0
use test;
create table test(a int, b varchar(XXX))engine=innodb;
use test;
create table test(a int, b varchar(100))engine=innodb;
### 1 Query
### Total time: 0.000846, Average time: 0.000846
### Taking 0.000846  seconds to complete
### Rows analyzed 4
select * from test;
select * from test;
### 1 Query
### Total time: 0.000378, Average time: 0.000378
### Taking 0.000378  seconds to complete
### Rows analyzed 0
alter table test modify a auto_increment primary key;
alter table test modify a auto_increment primary key;
### 4 Queries
### Total time: 0.045375, Average time: 0.01134375
### Taking 0.000767 , 0.004855 , 0.007990 , 0.031763  seconds to complete
### Rows analyzed 0, 0, 0 and 0
SET insert_id=XXX;
insert into test(b) values('XXX');
SET insert_id=4;
insert into test(b) values('aaaaa');
每个SQL执行了几遍，总共多少时间，非常清楚。打了补丁以后，可以检测到那些每次执行时间小于1秒而执行次数非常多的SQL。很多时候都是这样的SQL在做怪，把这些SQL进行优化，或者修改程序减少这样的SQL的执行次数，都会大大提高应用的效率。
我之前在RHEL4.0上也装过，非常顺利，
./configure --prefix=/usr/local/mysql
之后，make和make install都没有问题。但在ubuntu6.06上，就需要使用
./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql
没有仔细去研究原因。

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/25477/showart_192926.html