OProfile比较sql执行CPU消耗情况

安装：

wget http://prdownloads.sourceforge.net/oprofile/oprofile-0.9.7.tar.gz
tar -zxvf oprofile-0.9.7.tar.gz
cd oprofile-0.9.7
sudo yum install binutils-devel  --说明：configure如果报错安装下这个包 
./configure
make
sudo make install

使用：

开始：

sudo opcontrol --deinit; sudo /sbin/modprobe oprofile timer=1

sudo opcontrol --setup --no-vmlinux && sudo opcontrol --init && sudo opcontrol --reset && sudo opcontrol --start

采集期间跑程序：

$mysqlslap --no-defaults --query=" select count(*) as num from vid_film left join vid_class_map on vid_film.id=vid_class_map.v_id and vid_class_map.s_type=1 and vid_class_map.class_id=1;" --number-of-queries=5000 --concurrency=50 -uroot --socket=/u01/mysql/run/mysql.sock

结束，生成报告tmp.log：

sudo opcontrol --shutdown

opreport -g -l -p /u01/mysql /u01/mysql/libexec/mysqld --merge all -o tmp.log

抓不到采样数据问题：

如果你的CPU不被oprofile认识，或者说认识有问题，oprofile就会进入 timer interrupt模式
表现是
$ophelp
Using timer interrupt.

$sudo opcontrol --setup -e CPU_CLK_UNHALTED:6000:0:0:1
You cannot specify any performance counter events
because OProfile is in timer mode.

解决：

在oprofile模块加载的时候强制使用 timer=1.


$sudo opcontrol --deinit
Daemon not running
Unloading oprofile module
 
$sudo modprobe oprofile timer=1   
 
$dmesg|grep oprofile|tail -n 1
oprofile: using timer interrupt.

下面比较不同SQL对CPU的消耗

第一种sql写法：

SELECT * FROM t WHERE user_id=10 LIMIT 400000,10

$sudo opcontrol --init
$sudo opcontrol --setup --separate=lib,kernel,thread --no-vmlinux
$sudo opcontrol --start-daemon
Using 2.6+ OProfile kernel interface.
Using log file /var/lib/oprofile/oprofiled.log
Daemon started.
$sudo opcontrol --start
Profiler running.
$time mysql -u root test<< EOF;
> select * from t where user_id=10 limit 400000,10;
> EOF
real    0m2.898s
user    0m0.008s
sys     0m0.002s
$sudo opcontrol --dump
$sudo opreport --demangle=smart --symbols --long-filenames --merge tgid $(which mysqld) | head -n 20
warning: /no-vmlinux could not be found.
CPU: Core 2, speed 1992 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 6000
samples  %        image name               symbol name
308374   40.0385  /lib64/tls/libc-2.3.4.so memcpy
61705     8.0116  /u01/mysql/libexec/mysqld row_sel_store_mysql_rec
57988     7.5290  /lib64/tls/libc-2.3.4.so memset
25651     3.3305  /u01/mysql/libexec/mysqld rec_get_offsets_func
23232     3.0164  /lib64/tls/libpthread-2.3.4.so pthread_mutex_trylock
22879     2.9706  /u01/mysql/libexec/mysqld btr_search_guess_on_hash
19499     2.5317  /lib64/tls/libpthread-2.3.4.so pthread_mutex_unlock
18479     2.3993  /u01/mysql/libexec/mysqld row_search_for_mysql
9393      1.2196  /u01/mysql/libexec/mysqld cmp_dtuple_rec_with_match
9053      1.1754  /u01/mysql/libexec/mysqld select_send::send_data(List<Item>&)
8259      1.0723  /u01/mysql/libexec/mysqld mem_pool_fill_free_list
7844      1.0184  /u01/mysql/libexec/mysqld Protocol_simple::store(Field*)
7755      1.0069  /u01/mysql/libexec/mysqld MYSQLparse(void*)
5823      0.7560  /u01/mysql/libexec/mysqld mtr_memo_slot_release
4841      0.6285  /u01/mysql/libexec/mysqld anonymous symbol from section .plt
4669      0.6062  /u01/mysql/libexec/mysqld my_longlong10_to_str_8bit
4610      0.5986  /u01/mysql/libexec/mysqld Protocol::net_store_data(char const*, unsigned int)
$sudo opcontrol --stop
$sudo opcontrol --deinit
Stopping profiling.
Killing daemon.
Unloading oprofile module
$sudo opcontrol --reset

第二种sql写法：

SELECT *                                                            
  FROM (SELECT id FROM t WHERE user_id = 10 LIMIT 400000, 10) a, t b
 WHERE a.id = b.id;

$sudo opcontrol --init
$sudo opcontrol --setup --separate=lib,kernel,thread --no-vmlinux
$sudo opcontrol --start-daemon
$sudo opcontrol --start
$time mysql -u root test << EOF;
> select *
>   from (select id from t where user_id = 10 limit 400000, 10) a, t b
>  where a.id = b.id;
> EOF
 
real    0m1.214s
user    0m0.010s
sys     0m0.001s
 
 
$sudo opcontrol --dump
$sudo opreport --demangle=smart --symbols --long-filenames --merge tgid $(which mysqld) | head -n 20
warning: /no-vmlinux could not be found.
CPU: Core 2, speed 1992 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 6000
samples  %        image name               symbol name
170549   42.5976  /lib64/tls/libc-2.3.4.so memcpy
29998     7.4925  /u01/mysql/libexec/mysqld row_sel_store_mysql_rec
24308     6.0714  /lib64/tls/libc-2.3.4.so memset
13637     3.4061  /u01/mysql/libexec/mysqld rec_get_offsets_func
13469     3.3641  /u01/mysql/libexec/mysqld row_search_for_mysql
13440     3.3569  /lib64/tls/libpthread-2.3.4.so pthread_mutex_trylock
8477      2.1173  /u01/mysql/libexec/mysqld btr_search_guess_on_hash
8244      2.0591  /lib64/tls/libpthread-2.3.4.so pthread_mutex_unlock
6492      1.6215  /u01/mysql/libexec/mysqld cmp_dtuple_rec_with_match
4611      1.1517  /u01/mysql/libexec/mysqld MYSQLparse(void*)
2744      0.6854  /u01/mysql/libexec/mysqld mtr_memo_slot_release
2641      0.6596  /u01/mysql/libexec/mysqld buf_page_optimistic_get_func
2508      0.6264  /u01/mysql/libexec/mysqld btr_cur_search_to_nth_level
2332      0.5825  /u01/mysql/libexec/mysqld mem_pool_fill_free_list
2272      0.5675  /u01/mysql/libexec/mysqld sync_array_print_long_waits
2217      0.5537  /u01/mysql/libexec/mysqld btr_pcur_store_position
2187      0.5462  /u01/mysql/libexec/mysqld anonymous symbol from section .plt
 
$sudo opcontrol --stop
$sudo opcontrol --deinit
$sudo opcontrol --reset

参考：http://www.fromdual.ch/mysql-oprofile

原文：http://blog.yufeng.info/archives/1283

         http://www.dbunix.com/?p=3073