set profiling=1 Unknown system variable 'profiling'
MySQL从5.0.37起,提供了profile的功能,对性能调试极其有用,不过今天在一台服务器上使用时却出了问题:
# /usr/local/mysql/bin/mysqld -V
/usr/local/mysql/bin/mysqld Ver 5.0.83-log for pc-linux-gnu on i686 (MySQL Community Server (GPL))
版本号是5.0.83,大于5.0.37,感觉应该可以使用profile功能,可是:
mysql> set profiling = 1;
ERROR 1193 (HY000): Unknown system variable 'profiling'
出人意料,竟然无法使用profile功能。接着试试别的命令:
mysql> show profiles;
ERROR 1289 (HY000): The 'SHOW PROFILES' feature is disabled; you need MySQL built with 'enable-profiling' to have it working
这次的错误信息相对而言更明确了,说编译时没有激活enable-profiling选项。确认一下目前的编译参数:
# cat /usr/local/mysql/bin/mysqlbug | grep CONFIGURE_LINE
CONFIGURE_LINE="./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Server (GPL)' '--with-server-suffix=' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--with-pic' '--with-fast-mutexes' '--with-client-ldflags=-static' '--with-mysqld-ldflags=-static' '--with-zlib-dir=bundled' '--with-big-tables' '--with-yassl' '--with-readline' '--with-innodb' '--with-ndbcluster' '--with-archive-storage-engine' '--with-blackhole-storage-engine' '--with-csv-storage-engine' '--without-example-storage-engine' '--with-federated-storage-engine' '--with-extra-charsets=all' 'CC=ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc' 'CFLAGS=-g -O3 -march=i686' 'CXX=ccache /usr/local/gcc-4.3.2/bin/gcc -static-libgcc' 'CXXFLAGS=-g -O3 -march=i686'"
确实没enable-profiling选项,不过别以为简单加上这个选项之后重新编译一次就行了,实际上如果想编译成功,还得同时确保使用enable-community-features选项,可参阅官方文档。如果觉得记这些编译命令太烦,那就使用新版的MySQL吧,从5.1.28版本开始,这些选项缺省就是激活的。
以下这段来自mysql官方:
individual features, such as --enable-profiling
to enable statement profiling. This option was added in MySQL 5.1.24. It is enabled by default as of MySQL 5.1.28; to disable it, use --disable-community-features
.
When given with --enable-community-features
, the --enable-profiling
option enables the statement profiling capability exposed by the SHOW PROFILE
and SHOW PROFILES
statements. (See Section 12.5.5.33, “SHOW PROFILES
Syntax”.) This option was added in MySQL 5.1.24. It is enabled by default as of MySQL 5.1.28; to disable it, use --disable-profiling
.
补充点知识,查看nginx,apache,mysql,php的编译参数(假设都安装在/usr/local目录):
查看nginx编译参数:/usr/local/nginx/sbin/nginx -V
查看apache编译参数:cat /usr/local/apache/build/config.nice
查看mysql编译参数:cat /usr/local/mysql/bin/mysqlbug | grep CONFIGURE_LINE
查看php编译参数:/usr/local/php/bin/php -i | grep configure 或者 /usr/local/php/bin/php-config