Generate the iRules Runtime Calculator Excel Spreadsheet with the Python SDK

This last week I noticed an internal request for the iRules Runtime Calculator Excel Spreadsheet that we've hosted here on DevCentral for many years. The spreadsheet requires the following input from the user:

• The BIG-IP CPU "speed", which is found by multiplying the number of cores times the MHz value reported on the processor times 1,000,000. These values are found in the /proc/cpuinfo system file.
• The min/avg/max CPU cycles for each event in the iRule the user is analyzing. This requires:
• The user to activate iRule timing by using the "timing on" command in the iRule. I usually do this on the first line if I'm looking at global performance. You can also enable on specific events by using it in the event call like "when EVENT timing on { }".

Showing the rule output to collect the cycles via the tmsh "show tmsh ltm rule RULE field-fmt" and then copying that over to the spreadsheet.

This is all well and good, but to do this repeatedly while troubleshooting and/or optimizing is quite tedious. In this article, I’ll show you how to auto-generate the spreadsheet using the F5 Python SDK and a handy little module called XlsxWriter.

Running Test Traffic to Populate the iRule Statistics

This is the simple part. In my lab, I just use the apachebench command line utility either directly from my Mac or from the Ubuntu shell on my Windows 10 box. I like to hit at least ten-thousand requests just to dial down the extremes of the max value, but you can go much longer than I did for these tests.
 

ab -n 5000 -c 5 -k http://192.168.102.50/

This makes 5000 requests with 5 concurrent requests and session keepalives enabled. This will result in (if resetting your iRule stats between runs) a total of 5000 requests in the HTTP events, but a far reduced number of CLIENT_ACCEPTED.

Interrogating the BIG-IP for iRule Information and Stats

First, let’s look at examples of the spreadsheet requirements.
 

# CPU Info from /proc/cpuinfo
[root@ltm13:Active:Standalone] config # cat /proc/cpuinfo | grep ^processor
processor      : 0
processor      : 1
[root@ltm13:Active:Standalone] config # cat /proc/cpuinfo | grep MHz
cpu MHz        : 3400.606
cpu MHz        : 3400.606

One could use the iControl REST bash utility to cat that file, grep the important data, then parse it down into the necessary information. In fact, my first iteration of the script did exactly that and used regex to parse the important stuff. But one of my early testers had an issue with no access to the bash utility. So I had to find another way to eliminate that as a problem for others, and found that tmsh access to sys/hardware returned those values, albeit buried in tangled web of nested stats. To get to a cpu speed for our spreadsheet, we need to access those stat values and then perform the calculation.
 

# Selflinks for cpu cores/speed, stats are deep nested
hw_sub1 = 'https://localhost/mgmt/tm/sys/hardware/hardware-version'
hw_sub2 = 'https://localhost/mgmt/tm/sys/hardware/hardware-version/cpus'
hw_sub3 = 'https://localhost/mgmt/tm/sys/hardware/hardwareVersion/cpus/versions'
hw_sub4_cores = 'https://localhost/mgmt/tm/sys/hardware/hardwareVersion/cpus/versions/1'
hw_sub4_speed = 'https://localhost/mgmt/tm/sys/hardware/hardwareVersion/cpus/versions/2'

# Grab the hardware info from BIG-IP
hw = obj.tm.sys.hardware.load()

# Grab the processor MHz value recorded for the processor
cpu_MHz = hw.entries\
    .get(hw_sub1).get('nestedStats').get('entries')\
    .get(hw_sub2).get('nestedStats').get('entries')\
    .get(hw_sub3).get('nestedStats').get('entries')\
    .get(hw_sub4_speed).get('nestedStats').get('entries')\
    ['version']['description']

# Grab the number of cores recorded for the system
cpu_cores = hw.entries\
    .get(hw_sub1).get('nestedStats').get('entries')\
    .get(hw_sub2).get('nestedStats').get('entries')\
    .get(hw_sub3).get('nestedStats').get('entries')\
    .get(hw_sub4_cores).get('nestedStats').get('entries')\
    ['version']['description']

# The cores value has text in addition to the count, isolate and store
cpu_cores = cpu_cores.split(' ')[0]

# Calculate the total CPU speed in Hz, not MHz
cpu_speed = float(cpu_MHz) * int(cpu_cores) * 1000000

Next we need an iRule applied to a virtual server so we an populate the spreadsheet with data, so this simple test iRule works nicely.
 

# iRule timing command examples
timing on
when CLIENT_ACCEPTED {
  log local0. "Timestamp: [clock clicks -milliseconds]"
}
when HTTP_REQUEST {
  log local0. "Timestamp: [clock clicks -milliseconds]"
}
when HTTP_RESPONSE {
  log local0. "Timestamp: [clock clicks -milliseconds]"
}

The iRule is available on the command line via “tmsh list ltm rule RULE”. In the SDK, we load it like this:
 

# Grab the iRule
r1 = args.rule[0]
r = obj.tm.ltm.rules.rule.load(name=r1)

Finally, the statistics we are after for each event are the number of executions and the minimum, average, and maximum CPU cycles it took to complete the event.
 

# iRule min/avg/max cycles information
ltm rule-event event_order:HTTP_RESPONSE {
    aborts 0
    avg-cycles 43.0K
    event-type HTTP_RESPONSE
    failures 0
    max-cycles 853.9K
    min-cycles 6.5K
    name event_order
    priority 500
    total-executions 2.0K
}

You can see this information on the command line via “tmsh show ltm rule RULE”. In the SDK, we take the rule object we’ve already loaded (r) and load the stats:
 

# Grab the iRule stats
rstats = r.stats.load()

From the SDK perspective, the BIG-IP work required is complete. The heavy lifting in this script is accessing the XlsxWriter module, which we’ll cover next.
 

Working with XlsxWriter

This python module is amazing. It's powerful feature-wise, and it's one of the better documented projects I've worked with. Major tip of the hat to the author, John McNamara! The first thing we need to do is create a workbook.

Excel Workbooks

Given that you might want to track several runs of the same iRule and to make it easy to distinguish what workbook you need to open, the format for the name is iRulesRuntimeCalculator__iRuleName__timestamp. Also, we want to make the default window size large enough to see most of the data without having to resize it after opening.
 

# Get the current time
timestr = time.strftime("%Y%m%d-%H%M%S")

# Name the workbook iRuleRuntimeCalculator__<rulename>__<timestamp>
fname = 'iRulesRuntimeCalculator__{}__{}.xlsx'.format(r.name, timestr)
workbook = xlsxwriter.Workbook(fname)

# Set the initial Excel window size
workbook.set_size(1500,1200)

We also want to included some different cell formatting options for titles, headers, percentages, etc. Those formats are below.
 

# iRule textbox formatting
textbox_options = {
    'width': 1200,
    'height': 1400,
    'font': { 'color': 'white', 'size': 16 },
    'align': { 'vertical': 'top' },
    'gradient': { 'colors': ['#00205f', '#84358e'] }
}

# Title Block formatting
title_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
})
title_format.set_font_size(20)
title_format.set_font_color('white')
title_format.set_bg_color('#00205f')

# Section Header Formatting
secthdr_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
})
secthdr_format.set_font_size(16)
secthdr_format.set_font_color('white')
secthdr_format.set_bg_color('#00205f')

# Table Data Formatting - BOLD for headers and total
tabledata_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
})
tabledata_format.set_font_size(14)

# Table Data Formatting - ints for rule data max requests
tabledata2_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'num_format': '0',
})
tabledata2_format.set_font_size(14)

# Table Data Formatting - percentages for rule data
tabledata3_format = workbook.add_format({
    'bold': 1,
    'border': 1,
    'align': 'center',
    'valign': 'vcenter',
    'num_format': '0.0000000000000%',
})
tabledata3_format.set_font_size(14)

Now that we have our workbook created and the formats we'll use to populate cells with data, let's move on to the worksheets.

Excel Worksheets

First, let's create the worksheets. We need two: one for the iRule performance data and one for the iRule itself.
 

worksheet1 = workbook.add_worksheet('iRule Stats')
worksheet2 = workbook.add_worksheet('iRule Contents')

Next, let's generate the header information for worksheet1 that you'll find in the original document linked at the top of this article.
 

worksheet1.set_column(1, 1, 30)
worksheet1.set_column(2, 2, 15)
worksheet1.set_column(3, 5, 25)
worksheet1.merge_range('B2:F2', 'iRules Runtime Calculator - {}'.format(r.name), title_format)
worksheet1.write_string(4, 1, 'BIG-IP version: {}, OS version: {} {}, '
                              'Python version: {}'.format(bigip_version,
                                                          platform.system(),
                                                          platform.release(),
                                                          platform.python_version()))
worksheet1.write_string(5, 1, 'For more details, see article "Intermediate iRules: '
                              'Evaluating Performance" on DevCentral:')
worksheet1.write_string(6, 1, 'https://devcentral.f5.com/s/articles/intermediate-irules-evaluating-performance-20433')
worksheet1.write_string(8, 1, 'Cycles/Sec', tabledata_format)
worksheet1.write_number(8, 3, cpu_speed, tabledata_format)

This is writing values to the specified cell/column location in the worksheet and applying the formatting we created above as appropriate. The result in the generated spreadsheet for this section of cod is shown below.



Providing the BIG-IP, OS, and Python versions will help provide context between iterations of testing as any or all of those things can change. (Darwin is Mac OS, I have no idea why. Drop a comment below if you know!)
The second section in this worksheet is the section that you have to fill out manually if you use the original document. But we'll take the data we collected from the BIG-IP in the earlier code samples and populate it instead.
 

rowval = 12
event_list = []
for sl in rstats.entries:
    raw_data = rstats.entries.get(sl).get('nestedStats').get('entries')
    event_name = raw_data['eventType']['description']
    event_list.append(event_name)
    executions = raw_data['totalExecutions']['value']
    min_cycles = raw_data['minCycles']['value']
    avg_cycles = raw_data['avgCycles']['value']
    max_cycles = raw_data['maxCycles']['value']
    worksheet1.write_row(rowval, 1, (str(event_name), int(executions), int(min_cycles), int(avg_cycles), int(max_cycles)), tabledata_format)
    rowval += 1

worksheet1.write_string(rowval, 1, 'Total', tabledata_format)
worksheet1.write_formula(rowval, 2, '=MAX({})'.format(xl_range(12,2,rowval-1,2)), tabledata_format)
worksheet1.write_formula(rowval, 3, '=SUM({})'.format(xl_range(12,3,rowval-1,3)), tabledata_format)
worksheet1.write_formula(rowval, 4, '=SUM({})'.format(xl_range(12,4,rowval-1,4)), tabledata_format)
worksheet1.write_formula(rowval, 5, '=SUM({})'.format(xl_range(12,5,rowval-1,5)), tabledata_format)

We have to track what row we're on, because you might have more or less than the three events I've chosen to use in this example iRule and not tracking would end badly otherwise. We do that here with the rowval variable. We also create a list variable called event_list to store the names of the events for populating the analysis tables. Next, we iterate through the events in the rule statistics object and write each event's data with the write_row method. Finally, after we've iterated through the events, we use the write_formula method to summarize the cycles from each event. This results in the following table in the spreadsheet:



What follows in this spreadsheet is a series of three analysis tables. I'll walk through the final section on expected requests/sec here but the entire rule is linked at the conclusion of this article.
 

# increment rowval again to start third analysis table
rowval += 3

# Populate the Max # of requests table based on the rule stats
worksheet1.merge_range('B{0}:F{0}'.format(rowval), 'Max Requests', secthdr_format)
worksheet1.write_row(rowval, 1, ('Event Name', '# of Requests', 'MIN', 'AVG', 'MAX'), tabledata_format)
rowval += 1
for event in event_list:
    worksheet1.write_string(rowval, 1, event, tabledata_format)
    worksheet1.write_formula(rowval, 2, '=C{}'.format(rowval - (3 + len(event_list))), tabledata_format)
    worksheet1.write_formula(rowval, 3, '=1/D{}'.format(rowval - (3 + len(event_list))), tabledata2_format)
    worksheet1.write_formula(rowval, 4, '=1/E{}'.format(rowval - (3 + len(event_list))), tabledata2_format)
    worksheet1.write_formula(rowval, 5, '=1/F{}'.format(rowval - (3 + len(event_list))), tabledata2_format)
    rowval += 1

worksheet1.write_string(rowval, 1, 'Total', tabledata_format)
worksheet1.write_formula(rowval, 2, '=MAX({})'.format(xl_range(rowval-len(event_list),2,rowval-1,2)), tabledata_format)
worksheet1.write_formula(rowval, 3, '=1/D{}'.format(rowval - (3 + len(event_list))), tabledata2_format)
worksheet1.write_formula(rowval, 4, '=1/E{}'.format(rowval - (3 + len(event_list))), tabledata2_format)
worksheet1.write_formula(rowval, 5, '=1/F{}'.format(rowval - (3 + len(event_list))), tabledata2_format)

Each section starts with incrementing the rowval variable to advance the curson down the spreadsheet so as to provide a little space between sections. After writing the section header info, we iterate through the event list we stored, and calculate the max number of requests (at the reported min/avg/max cycles per event), and then taking an overall total not on our calculated totals from this section, but from the totals in the previous section. This results in the following screenshot:



This wraps up the work in worksheet1. I did notice that on Windows, the formatting for integers instead of decimals doesn't seem to work.

The final step for content is to write the iRule itself to worksheet2. This is a one liner thanks to our formatting options and the insert_textbox method.
 

worksheet2.insert_textbox('B2', r.apiAnonymous, textbox_options)

The screenshot of that handywork is below.

Wrapping Up

Finally, let's print a notification to the console and then close the workbook and call it good!
 

print('\n\n\tHoly iRule perfomance analysis, Batman! Your mission file is {}\n\n'.format(fname))
# Close the workbook
workbook.close()

I didn't include the BIG-IP instantiation or some of the other little details in the snippets of code above, but they are all included in the codeshare entry. Usage:
 

(py35) mymac:scripts me$ python runtime_calc_v2.py ltm3.test.local admin event_order

  Well hello admin, please enter your password: 


  Holy iRule perfomance analysis, Batman! Your mission file is iRulesRuntimeCalculator__event_order__20190910-181009.xlsx

Give it a shot and let me know what you think!