{"id":230,"date":"2013-04-23T18:04:24","date_gmt":"2013-04-23T16:04:24","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=230"},"modified":"2013-11-11T11:07:53","modified_gmt":"2013-11-11T09:07:53","slug":"generating-graphs-massively-from-windows-performance-counters-logs","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/generating-graphs-massively-from-windows-performance-counters-logs\/","title":{"rendered":"Generating graphs massively from Windows Performance Counters logs"},"content":{"rendered":"

Windows Performance Monitor is an invaluable tool when you don’t have external enterprise monitoring tools and you need to face performance problems, whether you have a web\/application server, a mail server or a database server.<\/p>\n

But what I don’t personally like of it is what you get in terms of graphing. If you schedule and collect a big amount of performance metrics you will likely get lost in adding\/removing such metrics from the graphical interface.<\/p>\n

What I’ve done long time ago (and I’ve done again recently after my old laptop has been stolen \ud83d\ude41 ) is to prepare a PHP script that parse the resulting CSV file and generate automatically one graph for each metric that could be found.<\/p>\n

Unfortunately, most of Windows Sysadmin between you will disagree that I’ve done this using a Linux Box. But I guess you can use my script if you install php inside cygwin. The other tool you need, is rrdtool<\/a>, again I use it massively to resolve my graphing needs.<\/p>\n

How to collect your data<\/strong><\/p>\n

Basically you need to create any Data Collector within the Performance Monitor that generates a log file. You can specify directly a CSV file (Log format: Comma separated) or generate a BLG file and convert it later (Log format: Binary). System dumps are not used, so if you use the standard Performace template, you can delete it from your collection.<\/p>\n

Remember that the more counters you take, the more the graph generation will take. The script does not run in parallel, so it will use only one core. Generally:<\/p>\n

(Time to complete) = (Num Counters) * (Num Samples) * (Speed factor)<\/pre>\n
Where (Speed factor) is depending on both the CPU speed and the disk speed because of the huge number of syncs required to update several thousands of files. I’ve tried to reduce the number of rrdupdates by queuing several update values in a single command line and I’ve noticed an important increase of performances, but I know it’s not enough.<\/p>\n
Converting a BLG (binary) log into a CSV log<\/strong><\/p>\n
Just use the relog tool:<\/p>\n
C:\\PerfLogs\\Admin\\Perftest\\LUDO_20130423-000002> relog \"Performance Counter.blg\" -f csv -o \"Performance Counter.csv\"<\/strong>\r\n\r\nInput\r\n----------------\r\nFile(s):\r\n     Performance Counter.blg (Binary)\r\n\r\nBegin:    23.4.2013 14:56:02\r\nEnd:      23.4.2013 15:33:37\r\nSamples:  452\r\n\r\n100.00%\r\n\r\nOutput\r\n----------------\r\nFile:     Performance Counter.csv\r\n\r\nBegin:    23.4.2013 14:56:02\r\nEnd:      23.4.2013 15:33:37\r\nSamples:  452\r\n\r\nThe command completed successfully.<\/pre>\n
\u00a0Generating the graphs<\/strong><\/p>\n
Transfer the CSV on the box where you have the php and rrdtool configured, then run:<\/p>\n
 <\/p>\n
[root@lucrac01 temp]# php process_l.php PerformanceCounter.csv\r\n\r\n453--Creating rrd \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Delivered_sec.rrd\r\n\r\nCreating rrd \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Unknown_Protocol.rrd\r\nCreating rrd \/root\/temp\/LUDO\/IPv4\/Fragmented_Datagrams_sec.rrd\r\nCreating rrd \/root\/temp\/LUDO\/IPv4\/Datagrams_sec.rrd\r\n\r\n...\r\n\r\nCreating rrd \/root\/temp\/LUDO\/Memory\/Pages_Input_sec.rrd\r\nCreating rrd \/root\/temp\/LUDO\/Memory\/Pool_Paged_Resident_Bytes.rrd\r\nCreating rrd \/root\/temp\/LUDO\/Memory\/Write_Copies_sec.rrd\r\n\r\n...\r\n\r\nCreating rrd \/root\/temp\/LUDO\/PhysicalDisk_2_E__\/Avg._Disk_sec_Transfer.rrd\r\nCreating rrd \/root\/temp\/LUDO\/PhysicalDisk_1_D__\/Avg._Disk_sec_Transfer.rrd\r\nCreating rrd \/root\/temp\/LUDO\/PhysicalDisk_0_C__\/Avg._Disk_sec_Transfer.rrd\r\n----......\r\n\r\n1.Generating Graph: \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Delivered_sec.png\r\nrrdtool graph \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Delivered_sec.png --start \"1366721762\" --end \"1366724017\" --width 453 DEF:ds0=\/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Delivered_sec.rrd:value:LAST:step=5 LINE1:ds0#0000FF:\"IPv4\\Datagrams Received Delivered\/sec\" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:\" \" COMMENT:\" Maximum \" GPRINT:ds0max:\"%6.2lf\" COMMENT:\" Average \" GPRINT:ds0avg:\"%6.2lf\" COMMENT:\" Minimum \" GPRINT:ds0min:\"%6.2lf\"\r\n534x177\r\n2.Generating Graph: \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Unknown_Protocol.png\r\nrrdtool graph \/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Unknown_Protocol.png --start \"1366721762\" --end \"1366724017\" --width 453 DEF:ds0=\/root\/temp\/LUDO\/IPv4\/Datagrams_Received_Unknown_Protocol.rrd:value:LAST:step=5 LINE1:ds0#0000FF:\"IPv4\\Datagrams Received Unknown Protocol\" VDEF:ds0max=ds0,MAXIMUM VDEF:ds0avg=ds0,AVERAGE VDEF:ds0min=ds0,MINIMUM COMMENT:\" \" COMMENT:\" Maximum \" GPRINT:ds0max:\"%6.2lf\" COMMENT:\" Average \" GPRINT:ds0avg:\"%6.2lf\" COMMENT:\" Minimum \" GPRINT:ds0min:\"%6.2lf\"\r\n534x177\r\n...<\/pre>\n
\"generated_graphs\"<\/a><\/p>\n
Now it’s done!\u00a0<\/strong><\/p>\n
The script generate a folder with the name of the server (LUDO in my example) and a subfolder for each class of counters (as you see in Performance Monitor).<\/p>\n
Inside each folder you will have a PNG (and an rrd) for each metric.<\/p>\n
 <\/p>\n
\"generated_graph_cpu\"<\/a><\/p>\n
 <\/p>\n
Important:\u00a0<\/strong>The RRD are generated with a single round-robin archive with a size equal to the number of samples. If you want to have the rrd to store your historical data you’ll need to modify the script. Also, the size of the graph will be the same as the number of samples (for best reading), but limited to 1000 to avoid huge images.<\/p>\n
Future Improvements<\/strong><\/p>\n
Would be nice to have a prepared set of graphs for standard graphs with multiple metrics (e.g. CPU user, system and idle together) and additional lines like regressions…<\/p>\n
Download the script:\u00a0process_l_php.txt<\/a>\u00a0<\/strong>and rename it with a .php extension.
\n<\/strong><\/p>\n
Hope you’ll find it useful!<\/p>\n
Cheers<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
Windows Performance Monitor is an invaluable tool when you don’t have external enterprise monitoring tools and you need to face performance problems, whether you have a web\/application server, a mail server or a database server. But what I don’t personally … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8,132],"tags":[15,71,72,67,286,70,60,73,74],"class_list":["post-230","post","type-post","status-publish","format-standard","hentry","category-perf","category-sqlserver","category-triblog","tag-graph","tag-image","tag-monitor","tag-monitoring","tag-perf","tag-plot","tag-sqlserver-2","tag-troubleshooting","tag-windows"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/230","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=230"}],"version-history":[{"count":9,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/230\/revisions"}],"predecessor-version":[{"id":279,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/230\/revisions\/279"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}