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.

	<############# SETTINGS #################>
	# number of rules/sub-applications to select a site to have it's own ALB
	$singleSiteRuleLimit = 1000
	# maximum number of rules on a shared site.
	$sharedSiteRuleLimit = 1000
	# The "shared" ALBs will evenly distribute the sites using a SINGLE primary
	# sort options. Each value in $sortByOptions will generate out a new distribution.
	# Each option list results in different ALB configurations
	#$sortByOptions = @("Avg","WebAppCount")
	$sortByOptions = @("WebAppCount")
	# This is the number of "shared" ALBs to create. It's really hard to
	# have the system dynamically figure this out.
	$minimumBagCount = 1
	# your database server name (ie. "localhost,1433")
	$datatbaseServer = "<your-server>,<port>"
	# your database name
	$databaseName = "IisLogs"
	<############# END OF SETTINGS ##########>
	# pull the input from the database
	function Invoke-SqlCmd {
	param (
	[string] $Server,
	[string] $Database,
	[string] $Query,
	[int] $Timeout = 30
	)
	$connection = New-Object System.Data.SqlClient.SqlConnection
	$connection.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;Connection Timeout=$Timeout"
	$connection.Open()
	try {
	$command = $connection.CreateCommand()
	$command.CommandText = $Query
	$command.CommandTimeout = $Timeout
	$result = $command.ExecuteReader()
	$table = New-Object System.Data.DataTable
	$table.Load($result)
	} finally {
	$connection.Close()
	}
	return $table
	}
	$lcuSumsQuery = @"
	select SiteName
	,min(WebAppCount) [WebAppCount]
	,sum([requests-per-hour]) [requests-per-hour-sum]
	,sum([rule-eval-lcus]) [rule-eval-lcus-sum]
	,sum([new-conn-lcus]) [new-conn-lcus-sum]
	,sum([active-conn-lcus]) [active-conn-lcus-sum]
	,sum([bandwidth-lcus]) [bandwidth-lcus-sum]
	,sum([max-lcus]) [max-lcus-sum]
	from dbo.vw_ProxySiteLCUComparison
	where [Date] >= '$startDateSql' and [Date] < '$endDateSql'
	group by SiteName
	"@
	$lcuSumsComparison = DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $lcuSumsQuery
	# calculate everything
	$singles = $lcuSumsComparison |? { $_.WebAppCount -gt $singleSiteRuleLimit }
	$shared = $lcuSumsComparison |? { $_.WebAppCount -le $singleSiteRuleLimit }
	#$singles | ft -AutoSize WebAppCount, request-per-hour-sum, SiteName
	#$shared | ft -AutoSize
	$albSortedOptions = @{}
	foreach($sortBy in $sortByOptions) {
	$albs = @{ Cnt = 0 }
	foreach($site in $singles) {
	$albs."$($albs.Cnt)" = @($site)
	$albs.Cnt++
	}
	function Get-RuleEvalLcu {
	param(
	$RuleCount,
	$RequestsPerHourCount
	)
	# This follows this statement of “on average there will be 6 rule evaluations per request”
	# ((22 sub-applications / 2) – (10 free requests / 2))
	$ruleEvaluations = ($RuleCount / 2) - 5
	$ruleEvaluationsPerHour = $ruleEvaluations * $RequestsPerHourCount
	$lcu = $ruleEvaluationsPerHour / 3600 # 3600 seconds in an hour = 60 seconds * 60 minutes
	return $lcu
	}
	function Sort-IntoBags {
	param(
	$BaseCount,
	$SharedSites,
	$BagCount,
	$SortBy,
	$RuleLimit
	)
	$results = @{ Cnt = $BaseCount + 1 }
	$bi = 1;
	$lastbi = $bi;
	$isEdge = $true;
	$increment = 1;
	$retry = 0;
	$SharedSites = $SharedSites | sort -Property $SortBy -Descending
	for($i = 0; $i -lt $SharedSites.Count; $i++) {
	$site = $SharedSites[$i]
	$bagIndex = $baseCount + $bi
	if($results."$bagIndex" -eq $null) { $results."$bagIndex" = @(); $results.Cnt++; }
	$bag = $results."$bagIndex"
	$ruleCount = ($bag.WebAppCount | Measure-Object -Sum).Sum
	if($ruleCount + $site.WebAppCount -gt $RuleLimit) {
	$i--
	$retry++
	if($retry -eq $BagCount) {
	$i++
	$bag += @($site)
	$retry = 0
	}
	} else {
	$bag += @($site)
	$retry = 0
	}
	if(@(1, $BagCount) -contains $bi -and $bi -ne $lastbi) {
	$i++
	if($i -ge $SharedSites.Count) { break; }
	$site = $SharedSites[$i]
	$bag += @($site)
	$increment = -1 * $increment
	}
	$results."$bagIndex" = $bag
	$lastbi = $bi
	if($BagCount -eq 1) {
	# edge case, only 1 bag, don't increment the bag index
	} else {
	$bi += $increment
	}
	}
	return $results
	}
	$currentBagCount = $minimumBagCount
	$continueSorting = $true
	$baseCount = $Albs.Cnt - 1
	$firstSharedIdx = $Albs.Cnt
	while($continueSorting) {
	$loopBagCount = $currentBagCount
	$sortedBags = Sort-IntoBags -BaseCount $baseCount `
	-SharedSites $shared `
	-BagCount $currentBagCount `
	-SortBy $sortBy `
	-RuleLimit $sharedSiteRuleLimit
	# create groupings in database
	$truncateGroupings = "truncate table dbo.ALBGrouping"
	DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $truncateGroupings
	for($j = $firstSharedIdx; $j -lt $sortedBags.Cnt; $j++) {
	$sites = $sortedBags."$j"
	foreach($site in $sites) {
	$insertGrouping = "insert into dbo.ALBGrouping (GroupId, SiteName) values ($j, '$($site.SiteName)')"
	DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $insertGrouping
	}
	}
	# retrieve the LCU values
	$albMonthlyLCUCost = "exec usp_Aggregate_ALBLCUComparison_For_DateRange @dateStart = '$startDateSql', @dateEnd = '$endDateSql'"
	$albLcuComparison = DatabaseUcsb\Invoke-SqlCmd -Server $datatbaseServer -Database $databaseName -Query $albMonthlyLCUCost
	# https://aws.amazon.com/elasticloadbalancing/pricing/
	$daysInMonth = ($endDate - $startDate).Days
	$albMonthlyBaselineCost = 0.0225 * $daysInMonth * 24 # ALB hour * days in month * 24 hours
	# check that no single ALB's LCU costs more than the baseline ALB monthly price
	# (if the LCU cost goes above the ALB monthly price, then add a new ALB and resort the sites)
	$continueSorting = $false
	foreach($grp in $albLcuComparison) {
	if($grp."max-lcus-cost-mth" -gt $albMonthlyBaselineCost) {
	$currentBagCount++
	$continueSorting = $true
	}
	}
	<# Used for debugging the incremental attempts #>
	$sortedStats = @()
	for($k = 0; $k -lt $sortedBags.Cnt; $k++) {
	$b = $sortedBags."$k"
	$lcus = $albLcuComparison |? GroupId -eq $k
	$props = @{
	Id = $k
	SiteNames = $b.SiteName
	SiteCount = ($b.WebAppCount | Measure-Object -Sum).Count
	RuleCount = ($b.WebAppCount | Measure-Object -Sum).Sum
	AvgRequestCount = ($b."requests-per-hour-sum" | Measure-Object -Sum).Sum
	LCUs = $lcus."max-lcus-mth"
	LCUCost = $lcus."max-lcus-cost-mth"
	BaselineCost = $albMonthlyBaselineCost
	TotalCost = $albMonthlyBaselineCost + $lcus."max-lcus-cost-mth"
	}
	$sortedStats += @([PSCustomObject] $props)
	}
	Write-Host "Testing BagCount: $loopBagCount (Total Cost: `$$(($sortedStats.TotalCost | Measure-Object -Sum).Sum))"
	$sortedStats | ft -AutoSize Id, SiteCount, RuleCount, AvgRequestCount, LCUs, LCUCost, BaselineCost, TotalCost, SiteNames
	}
	# add the sorted bag results into the albs
	for($j = $firstSharedIdx; $j -lt $sortedBags.Cnt; $j++) {
	$albs."$j" = $sortedBags."$j"
	$albs.Cnt++
	}
	$albStats = @()
	for($j = 0; $j -lt $albs.Cnt; $j++) {
	$alb = $albs."$j"
	$lcus = $albLcuComparison |? GroupId -eq $j
	$props = @{
	Id = $j
	SiteNames = $alb.SiteName
	SiteCount = ($alb.WebAppCount | Measure-Object -Sum).Count
	RuleCount = ($alb.WebAppCount | Measure-Object -Sum).Sum
	AvgRequestCount = ($alb."requests-per-hour-sum" | Measure-Object -Sum).Sum
	LCUs = $lcus."max-lcus-mth"
	LCUCost = $lcus."max-lcus-cost-mth"
	BaselineCost = $albMonthlyBaselineCost
	TotalCost = $albMonthlyBaselineCost + $lcus."max-lcus-cost-mth"
	}
	$albStats += @([PSCustomObject] $props)
	}
	$albSortedOptions."$sortBy" = $albStats
	}
	foreach($sortBy in $sortByOptions) {
	$configuration = $albSortedOptions."$sortBy"
	Write-Host "Sorted by $sortBy :"
	$configuration | ft -AutoSize Id, SiteCount, RuleCount, AvgRequestCount, LCUs, LCUCost, BaselineCost, TotalCost, SiteNames
	}

view raw Calculate-AwsAlbSorting.ps1 hosted with ❤ by GitHub