Profiling mysqld on windows
By amrendra kumar on Dec 31, 2013
Profiling mysqld on windows using visual studio 2012 profiler tool (vsperfcmd)
Recently, when I was doing performance assessment of one innodb feature using sysbench standard tool then I observed performance drop in the feature. I had some hands on experience with codeanalyst (AMD's tool) so I did sampling profiling. I attached profiler vsperf to mysqld when sysbench client was doing transaction with some concurrent threads and captured the data for 1 minute. Just for information, there are 2 technique of profiling -1 sampling and instrumentation. For instrumentation, I had limitation because a)our test framework starts mysqld in instrumentation profiler needs to start mysqld b) in-between framework does shutdown for some clean-up of data and c) instrumentation profiling requires debug build. The codeanalyst does not provide very detailed data like 'vsperf' provides. Using visual studio IDE, we can profile (sampling or instrumentation). In my sampling profiling I measured CPU clocks and it was quite easy to do through IDE. It saves session so that we can refer profiled data and compare with another saved session profiled data. The list of events for your hardware, you can find by running: vsperfcmd /querycounters (note: in my installation vsperfcmd was @C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\x64) ‘Event based profiling’ like profiling for event 'L2 cachemiss' using IDE was giving me trouble and I got no data for L2CacheMiss event-profiling. I could not figure out like why event-profiling through IDE is not able to capture any data.
So, I thought of trying command-line option for this and command-line approach worked out and I got the data I needed. Here, a sample batch file I wrote to do event (L2CacheMiss) based profiling: @echo on cd C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\x64 c: rem VSPerfCmd.exe /attach:%1 /Output:"C:\amrendra\wl6906_analysis\report\baseline_L2LinesOut.vsp" /Start:SAMPLE /COUNTER:L2Misses,1000 rem VSPerfCmd.exe /attach:%1 /Output:"C:\amrendra\wl6906_analysis\report\baseline_L2LinesOut_2.vsp" /Start:SAMPLE /COUNTER:L2LinesOut,100 VSPerfCmd.exe /attach:%1 /Output:"C:\amrendra\wl6658\baseline\baseline_cpu.vsp" /Start:SAMPLE VSPerfCmd.exe /GlobalOn rem ping 184.108.40.206 -n 1 -w 60000 >NUL rem wscript c:\amrendra\wait.vbs 60 timeout /T 60 /NOBREAK VSPerfCmd.exe /GlobalOff VSPerfCmd.exe /detach VSPerfCmd.exe /Shutdown rem do not use vsperfcmd /admin:service,start rem vsperfcmd /querycounters Some reference links can be useful, found on msdn:
- For events detail I referred:http://oprofile.sourceforge.net/docs/intel-corei7-events.php