AWS ALB Price Planning w/ IIS : Add IisLogWebAppId

This post continues the series from AWS ALB Price Planning w/ IIS : Grouped Sites.

This doesn’t really help figure out much in the larger picture. But, I wanted to separate out statistics about the Web API applications from the normal web applications. Web API applications are strong candidates for rewrites as Serverless ASP.NET Core 2.0 Applications on Lambda Functions. Changing these applications to Lambda Functions won’t reduce the cost of the ALB as they will still use host names that will be serviced by the ALB. But, this will help figure out the tiny tiny costs that the Lambda Functions will charge each month.

This is just an intermediary step to add WebAppId’s to all of the requests.

Background Info

Instead of adding a WebAppId column onto the IisLog, I’m going to create a new table which will link the IisLog table entries to the ProxyWebApp table entries. The reason for this is that the IisLog table has 181,507,680 records and takes up 400 GB of space on disk. Adding a new column, even a single integer column, could be a very dangerous operation because I don’t know how much data the system might want to rearrange on disk.

Plan of Action and Execution

Instead, I’m going to

1. Add a WebAppId int Identity column onto table dbo.ProxyWebApp. The identity column won’t be part of the Primary Key, but it’s also a super tiny table.
2. Create a new table called dbo.IisLogWebAppId which takes the Primary Key of table dbo.IisLogWebAppId and combines it with WebAppId.
3. Create a script to populate dbo.IisLogWebAppId.
4. Create a stored procedure to add new entries nightly.

The scripts are below, but I think it’s worthwhile to note that the script to populate dbo.IisLogWebAppId took 4h57m to create on 181,507,680 records which was 15 GBs of disk space.

AWS ALB Price Planning w/ IIS : Grouped Sites

This post continues the series from AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2.

Having all the data in IIS and generating out all the hourly LCU totals helps define what the monthly charges could be. But, my expectations are that I will need to split the 71 DNS host names over multiple ALBs in order to reduce the total cost of the LCUs. My biggest fear is the Rule Evaluation Dimension. The more the host names on a single ALB, the more likely a request will go past the free 10 rule evaluations.

To do this, I need to build a script/program that will generate out possible DNS host name groupings and then evaluate the LCUs based upon those groupings.

In the last post I had already written a simple script to group sites based upon the number sub-applications (ie. rules) they contain. That script didn’t take the next step, which is to evaluate the combined LCU aggregates and recalculates the fourth dimension (the Rule Evaluation LCU).

But, before that …

The Full Database Model

So, instead of having to comb through the previous posts and cobble together the database. I think the database schema is generic enough that it’s fine to share all of it. So …

• Full database object script

New Additions:

• ALBGrouping
  
  This is the grouping table that the script/program will populate.
  
• vw_ALB*
  
  These use the ALBGrouping table recalculate the LCUs.
  
• usp_Aggregate_ALBLCUComparison_For_DateRange
  
  This combines all the aggregates (similar to vw_ProxySiteLCUComparison). But, the way the aggregation works, you can’t filter the result by Date. So, I needed a way to pass a start and end date to filter the results.

ALB Grouping Results

Wow! It’s actually cheaper to go with a single ALB rather than even two ALBs. It way cheaper than creating individual ALBs for sites with more than 10 sub-applications.

I wish I had more confidence in these numbers. But, there’s a really good chance I’m not calculating the original LCU statistics correctly. But, I think they should be in the ball park.

I have it display statistics on it’s internal trials before settling on a final value. And, from the internal trials, it looks like the least expensive options is actually using a single ALB ($45.71)!

Next Up, AWS ALB Price Planning w/ IIS : Add IisLogWebAppId.

AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2

This post continues the series from AWS ALB Price Planning w/ IIS : Rule Evaluations.

In the last post, I looked at the basics of pricing a single site on an ALB server. This time I’m going to dig in a little further into how to group multiple sites onto multiple ALB servers. This would be to allow a transition from a single IIS proxy server to multiple ALB instances.

Background

• The IIS proxy server hosts 73 websites with 233 web applications.
• Any site with 8 or more web applications within it will be given it’s own ALB server. The LCU cost of having over 10 rule evaluations on a single ALB are so dominant that it’s best to cut the number of rules you have to less than 10.
• Of the 73 websites, only 6 sites have 8 or more web applications within them. Leaving 67 other websites containing 103 web applications.

Findings from last time

I looked at grouping by number or rules and by average request counts.

If you have figured out how to get all the import jobs, tables, and stored procedures setup from the last couple posts then you are amazing! I definitely left out a number of scripts for database objects and some of the scripts have morphed throughout the series. But, if you were able to get everything setup, here’s a nice little view t0 help look at the expenses of rule evaluation LCUs.

Just like in the last post, there is a section at the bottom to get a more accurate grouping.

Simple Grouping

To do the simple groupings, I’m first going to generate some site statistics, usp_Regenerate_ProxySiteRequestStats. They really aren’t useful, but they can give you something to work with.

You can combine those stats with the WebAppCounts and use them as input into a PowerShell function. This PowerShell function attempts to:

• Separate Single Host ALBs from Shared Host ALBs
• $singleSiteRuleLimit sets the minimum number of sub-applications a site can have before it is required to be on it’s on ALB
• Group Host names into ALBs when possible
• It creates a number of shared ALBs (“bags”) which it can place sites into.
• It uses a bit of an elevator algorithm to try and evenly distribute the sites into ALBs.
• Enforce Rule Limits
• Unfortunately, elevator algorithms aren’t great at finding a good match every time. So, if adding a site to a bag would bring the total number of evaluation rules over $sharedSiteRuleLimit, then it tries to fit the site into the next bag (and so on).
• Give Options for how the sites will be prioritized for sorting
• Depending on how the sites are sorted before going into the elevator algorithm you can get different results. So, $sortByOptions lets you choose a few ways to sort them and to see the results of each options side by side.

The results look something like this:

So, sort by WebAppCount (ie. # of sub-applications) got it down to 19 ALBs. That’s 6 single ALBs and 13 shared ALBs.

Conclusion:

The cost of 19 ALBs without LCU charges is $307.80 per month ($0.0225 ALB per hour * 24 hours * 30 days * 19 ALBs). Our current IIS proxy server, which can run on a t2.2xlarge EC2 image, would cost $201.92 per month on a prepaid standard 1-year term.

The Sorting PowerShell Script

How to get more accurate groupings:

• Instead of generating hourly request statistics based upon Date, Time, and SiteName; the hourly request statistics need to be based upon Date, Time, SiteName, and AppPath. To do this you would need to:
• Assign a WebAppId to the dbo.ProxyWebApps table
• Write a SQL query that would use the dbo.ProxyWebApps data to evaluate all requests in dbo.IisLogs an assign the WebAppId to every request
• Regenerate all hourly statistics over ALL dimensions using Date, Time, SiteName and AppPath.
• Determine a new algorithm for ALB groupings that would attempt to make the number of rules in each group 10. But, the algorithm should leave extra space for around 1000~1500 (1.0~1.5 LCU) worth of requests per ALB. The applications with the lowest number of requests should be added to the ALBs at this point.
• You need to ensure that all applications with the same SiteName have to be grouped together.
• The base price per hour for an ALB is around 2.8 LCU. So, if you can keep this dimension below 2.8 LCU, it’s cheaper to get charged for the rule evaluations than to create a new ALB.

Next Up, AWS ALB Price Planning W/ IIS : Grouped Sites.

AWS ALB Price Planning w/ IIS : Rule Evaluations

This post continues the series from AWS ALB Price Planning w/ IIS : Bandwidth. Here are couple things to note about the query to grab Rule Evaluations:

• This can either be the most straightforward or most difficult dimension to calculate. For me, it was the most difficult.
• The IIS logs I’m working have 73 distinct sites (sometimes referred to as DNS host names or IP addresses). But there are 233 web applications spread across those 73 distinct sites. An ALB is bound to an IP address, so all of the sub-applications under a single site will all become rules within that ALB. At least, this is the way I’m planning on setting things up. I want every application/rule under a site to be pointed at a separate target server list.
• An important piece of background information is that the first 10 rule evaluations on a request are free. So, if you have less than 10 rules to evaluate on an ALB, you will never get charged for this dimension.
• Another important piece of information: Rules are evaluated in order until a match is found. So, you can put heavily used sub-applications at the top of the rules list to ensure they don’t go over the 10 free rule evaluation per request limit.
• However, you also need to be aware of evaluating rules in the order of “best match”. For example, you should place “/webservices/cars/windows” before “/webservices/cars”, because the opposite ordering would send all requests to /webservices/cars.
• The point being, you can tweak the ordering of the rules to ensure the least used sub-application is the only one which goes over the 10 free rule evaluations limit.

With all that background information, the number of rule evaluations are obviously going to be difficult to calculate. And, that’s why I fudged the numbers a lot. If you want some ideas on how to make more accurate predictions please see the notes at the bottom.

Here were some assumptions I made up front:

• If the site has over 8 sub-applications, that site should have it’s own ALB. It should not share that ALB with another site. (Because the first 10 rule evaluations are free.)
• All sites with less than 8 sub-applications should be grouped onto shared ALBs.
• For simplicity the groupings will be based on the number of rule evaluations. The number of requests for each sub-applications will not be used to influence the groupings.

Findings

Here were my biggest take aways from this:

• When an ALB is configured with more than the 10 free rule evaluations allowed, the rule evaluation LCUs can become the most dominant trait. But, that only occurs if number of requests are very high and the ordering of the rules is very unfavorable.
• The most influential metric on the LCU cost of a site is the number of requests it receives. You really need a high traffic site to push the LCU cost.
• As described in the “How to get more accurate numbers” section below. The hourly base price of an ALB is $0.0225 per hour. The hourly LCU price is $0.008. So, as long as you don’t spend over 2.8 LCU per hour; it’s cheaper to bundle multiple sites onto a single ALB rather than make a new one.

To demonstrate this, here was the second most heavily “requested” site. That site has 22 sub-applications. I used some gorilla math and came up with a statement of “on average there will be 6 rule evaluations per request” ((22 sub-applications / 2) – (10 free requests / 2)). Looking at August 1st 2018 by itself, the Rule Evaluations LCU was always lower the amount of Bandwidth used.

How to Gather the Data

Since I wanted every application under a site to need a rule; I first needed to get the number of web applications on the IIS server. I do not have that script attached. You should be able to write something using the WebAdministration or IISAdministration powershell modules. I threw those values into a very simple table:

Once you get your data into dbo.ProxyWebApps, you can populate dbo.ProxyWebAppCounts easily with:

Now, we need to calculate the number of requests per application for each hour.

And, finally, generate the LCUs for rule evaluations and compare it with the LCU values from the previous dimensions:

• usp_Calculate_ProxySiteNewConnections_For_Date.sql
• usp_Calculate_ProxySiteActiveConnections_For_Date.sql
• usp_Calculate_ProxySiteBandwidth_For_Date.sql
• usp_Calculate_ProxySiteRuleEvaluations_For_Date.sql

How to get more accurate numbers:

• Instead of generating hourly request statistics based upon Date, Time, and SiteName; the hourly request statistics need to be based upon Date, Time, SiteName, and AppPath. To do this you would need to:
• Assign a WebAppId to the dbo.ProxyWebApps table
• Write a SQL query that would use the dbo.ProxyWebApps data to evaluate all requests in dbo.IisLogs an assign the WebAppId to every request
• Regenerate all hourly statistics over ALL dimensions using Date, Time, SiteName and AppPath.
• Determine a new algorithm for ALB groupings that would attempt to make the number of rules in each group 10. But, the algorithm should leave extra space for around 1000~1500 (1.0~1.5 LCU) worth of requests per ALB. The applications with the lowest number of requests should be added to the ALBs at this point.
• You need to ensure that all applications with the same SiteName have to be grouped together.
• The base price per hour for an ALB is around 2.8 LCU. So, if you can keep this dimension below 2.8 LCU, it’s cheaper to get charged for the rule evaluations than to create a new ALB.

Next Up, AWS ALB Price Planning w/ IIS : Rule Evaluations Pt. 2.

AWS ALB Price Planning w/ IIS : Bandwidth

This post continues the series about from AWS ALB Price Planning w/ IIS : Active Connections. Here are couple things to note about the query to grab Bandwidth:

• This is one thing that IIS logs can accurately evaluate. You can get the number of bytes sent and received through an IIS/ARR proxy server by turning on the cs-bytes and sc-bytes W3C logging values. (see screen shot below)
• AWS does that pricing based on average usage per hour. So, the sql will aggregate the data into hour increments in order to return results.

Bandwidth SQL Script

Graphing the output from the script shows:

• Mbps per Hour (for a month)
• The jump in the average number of new connections in the beginning of the month corresponded to a return of students to campus. During the beginning of the month, school was not in session and then students returned.
• The dip at the end of the month has to do with a mistake I made loading some data. There is one of data that I forgot to import the IIS logs, but I don’t really want to go back and correct the data. It will disappear from the database in about a month.
• Mbps per Hour LCUs
• This is a critical number. We put 215+ websites through the proxy server. The two AWS ALB dimensions that will have the biggest impact on the price (the number of LCUs) will be the Bandwidth usage and the Rule Evaluations.
• I’m very surprised that the average LCUs per hour for a month is around 2.3 LCUs, which is very low.

Next Up, AWS ALB Price Planning w/ IIS : Rule Evaluations.