Why did I fail on using user-mode sampling collector to profile MySQL server?

Problem:
When you use VTune(TM) Amplifier XE’s user-mode sampling collector, e.g. Hotspots Collector to profile running MySQL server (attach-mode), you will meet unexpected result. See following example:

# ps -ae | grep mysql
47368 pts/000:00:00 mysqld_safe
49555 pts/000:00:03 mysqld

#amplxe-cl -collect hotspots -duration 10 -target-pid 49555
amplxe: Collection started. To stop the collection, either press CTRL-C or enter from another console window: amplxe-cl -r /data/peter/r013hs -command stop.
amplxe: Collection detached.
amplxe: Using result path `/data/peter/r013hs'
amplxe: Executing actions 50 % Generating a report

# amplxe-cl -report hotspots
amplxe: Using result path `/data/peter/r013hs'
amplxe: Executing actions 50 % Generating a report
Function Module CPU Time:Self Overhead Time:Self Spin Time:Self
-------- ------ ------------- ------------------ --------------
amplxe: Executing actions 100 % done

There was no hot function displayed in report. In general speaking, you need to use benchmark (mysqlslap, for example) to send requests from the client to MySQL server, then let MySQL sever busy for profiling purpose. So try:

# mysqlslap --user=root --auto-generate-sql --iterations=1000   ; if you worked on "root" user
mysqlslap: Error when connecting to server: Access denied for user 'root'@'localhost' (using password: NO)

Root-cause:
1.    User-mode sampling collectors don’t support to profile mysql server when mysql server starts up from “mysql” user account, but you work on “root” user.
2.    You should start up MySQL sever by the user account, which is same as VTune Amplifier XE’s use, e.g. "root" user or others.

Solution:
1.    # /etc/init.d/mysql stop ; shutdown running mysql if exits (started up by "mysql" user, default) 
2.    # mysqld_safe --user=root --skip-grant-tables --skip-networking & ; start-up mysql server by using “root” user
3.    # mysqlslap --user=root --auto-generate-sql --iterations=10000 & ; run bench mark, can use “top” or “ps” to verify if it is busy
108632 root20 0 35.3g 3.1g 6924 S75.0 0:07.85 mysqld
109262 root20 0 96084 2136 1568 S10.0 0:00.59 mysqlslap
4.    # amplxe-cl -collect hotspots -target-pid 108632 -d 30; Use VTune to profile target process for 30s
5.    # amplxe-cl -report hotspots
amplxe: Using result path `/data/peter/r027hs'
amplxe: Executing actions 50 % Generating a report
FunctionModuleCPU Time:SelfOverhead Time:SelfSpin Time:Self
-----------------------------------------------------------------------------------------------------
inline_mysql_socket_sendmysqld    0.106 0 0.040
recvmysqld    0.086 0 0
my_writemysqld    0.080 0 0.080
MYSQLparsemysqld0.058 0 0
...

For more complete information about compiler optimizations, see our Optimization Notice.