AWS ALB Price Planning w/ IIS : Active Connections

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

• This query is largely based on the same query used in AWS ALB Price Planning w/ IIS : New Connections [link needed].
• It’s slightly modified by getting the number of connections per minute rather than per second. But, all the same problems that were outlined in the last post are still true.
• AWS does their pricing based on average usage per hour. So, the sql will aggregate the data into hour increments in order to return results.

Active Connections SQL Script

Graphing the output from the script shows:

• # of Active Connections per Minute by Hour (for a month)
• The jump in the average number of new connections in the beginning of the month corresponds to a return of students to campus. During the beginning of the month, school was not in session and then the students returned.
• The dip at the end of the month has to do with a mistake I made loading some data. There is one day of IIS logs that I forgot to import, but I don’t really want to go back and correct the data. It will disappear from the database in about a month.
• # of Active Connections per Second by Hour Frequency
• This doesn’t help visualize it as well as I would have hoped. But, it does demonstrate that usually the number of active connections per minutes will be less than 3000; so it will be less than 1 LCU (1 LCU = 3000 active connections per minute).

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

AWS ALB Price Planning w/ IIS : New Connections

AWS ALB Pricing is not straight forward but that’s because they are trying to save their customers money while appropriately covering their costs. The way they have broken up the calculation for pricing indicates that they understand there are multiple different reasons to use an ALB, and they’re only gonna charge you for the feature (ie. dimension) that’s most important for you. That feature comes with a resource cost and they to charge you appropriately for the resource that’s associated with that feature.

Today, I’m going to (somewhat) figure out how to calculate one of those dimensions using IIS logs from an on-premises IIS/ARR proxy server. This will help me figure out what the projected costs will be to replace the on-premise proxy server with an AWS ALB. I will need to calculate out all the different dimensions, but today I’m just focusing on New Connections.

I’m gonna use the database that was created in Putting IIS Logs into a Database will Eat Disk Space. The IisLog table has 9 indexes on it, so we can get some pretty quick results even when the where clauses are ill conceived. Here are a couple things to note about the query to grab New Connections:

• As AWS notes, most connections have multiple requests flowing through them before they’re closed. And, IIS logs the requests, not the connections. So, you have to fudge the numbers a bit to get the number of new connections. I’m going to assume that each unique IP address per second is a “new connection”.
• There are all sorts of things wrong with this assumption:
• Browsers often use multiple connections to pull down webpage resources in parallel. Chrome uses up to 6 at once.
• I have no idea how long browsers actually hold open connections.
• Some of the sites use the websocket protocol (wss://) and others use long polling, so there are definitely connections being held open for a long time which aren’t being accounted for.
• And I’m probably going to reuse this poorly defined “fudging” for the number of Active Connections per Minute (future post / [link needed]).
• When our internal web app infrastructure reaches out for data using our internal web services, those connections are generally one request per connection. So, for all of the requests that are going to “services”, it will be assumed each request is a new connection.
• AWS does their pricing based on average usage per hour. So, the sql will aggregate the data into hour increments in order to return results.
• Sidenote: Because the AWS pricing is calculated per hour, I can’t roll these numbers up into a single “monthly” value. I will need to calculate out all the dimensions for each hour before having a price calculation for that hour. And, one hour is the largest unit of time that I can “average”. After that, I have to sum the results to find out a “monthly” cost.

New Connections SQL Script

Graphing the output from the script shows:

• # of New Connections per Second by Hour (for a month)
• The jump in the average number of new connections in the beginning of the month corresponds to a return of students to campus. During the beginning of the month, school was not in session and then the students returned.
• The dip at the end of the month has to do with a mistake I made loading some data. There is one day of IIS logs that I forgot to import, but I don’t really want to go back and correct the data. It will disappear from the database in about a month.
• # of New Connections per Second by Hour Frequency
• This just helps to visualize where the systems averages are at. It helps show that most hours will be less than 30 connections per second; which is less than 2 LCU. (1 LCU = 25 new connections per second)

Next Up, AWS ALB Price Planning w/ IIS : Active Connections.

Apigee Catch All Proxy

I’ve written before about how Apigee’s security is NOT Default Deny. In a similar thread of thought, I was recently speaking with an Apigee Architect who pointed out that it’s good idea to setup a Catch All Proxy in order to hide default error message information and help prevent search bots from indexing those error messages.

It’s really quick to setup and and can actually help out your end users by having the catch-all proxy redirect them back to your Developer Portal.

To do this:

1. Create a new proxy, + Proxy.

2. Select No Target

3. Give it a Proxy Name, and Description, but make the Proxy Base Path is set to /. Apigee’s url matching system is really smart and it will select the best match for each incoming url. This pattern will be the last to match, making it the ‘catch all’.

4. Everything about this is going to be very barebones. So, make it Pass through (none).


5. Set it up for all your endpoints.

6. And then Build it for Dev (or whatever your non-Prod environment is). Don’t worry about the Proxy Name, I needed to remake this picture.

7. Once it’s Built and Deployed, navigate over to the Develop tab of the new proxy.

8. In your proxy, you’re going to have only 1 policy and that policy will redirect traffic over to your Developer Portal.

9. To set this up, use a RaiseFault Policy and set the fault response to look like this:

10. Make sure you added the new DevPortal-Response policy into your PreFlow Proxy Endpoint as shown in Step 8.

11. Open up a browser and give it a spin using your Dev endpoint. Of course, test out some of your other API Proxies to make everything still works as you expect. Once everything looks good, promote it on up the environment stack.

That’s it! It take less than 10 minutes.

Putting IIS Logs into a Database will Eat Disk Space

The first thing is, Don’t Do This.

I needed to put our main proxy servers IIS logs into a database in order to calculate out the total bytes sent and total bytes received over time. The reason for the analytics was to estimate the expected cost of running a similar setup in AWS with an Application Load Balancer.

To load the data, I copied a powershell script from this blog (Not so many…), which was a modification of the original script from the same blog. The script is below. It is meant to be run as scheduled task and to log the details of each run using PowerShellLogging. The script is currently setup to only import a single day of data, but it can be altered to load many days without much effort.

But I want to focus on the size of the database and the time it takes to load.

IIS Log Information

90 Sites
38 days of logs
58.8 GB of IIS Logs

Database Configuration Information

My Personal Work Machine (not an isolated server)
MS SQL Server 2016 SP 1
1TB SSD Drive
Limited to 5GB Memory
Core i7-6700
Windows 10 1803

First Attempt – An Unstructured Database

This was “not thinking ahead” in a nutshell. I completely ignored the fact that I needed to query the data afterwards and simply loaded all of into a table which contained no Primary Key or Indexes.

The good news was it loaded “relatively” quickly.

Stats

• 151 Million Records
• 161 GB of Disk Space (that’s a 273% increase)
• 7h 30m Running Time

The data was useless as I couldn’t look up anything without a full table scan. I realized this problem before running my first query, so I have no data on how long that would have taken; but I figure it would have been a long time.

First Attempt Part 2 – Adding Indexes (Bad Idea)

Foolishly, I thought I could add the indexes to the table. So, I turned on Simple Logging and tried to add a Primary Key.

Within 1h 30m the database had grown to over 700 GB and a lot of error messages started popping up. I had to forcefully stop MSSQL Server and delete the .mdf/.ldf files by hand.

So, that was a really bad idea.

Second Attempt – Table with Indexes

This time I created a table with 9 indexes (1 PK, 8 IDX) before loading the data. Script below.

With the additional indexes and a primary key having a different sort order than the way the data was being loaded, it took significantly longer to load.

Stats

• 151 Million Records
• 362 GB of Disk Space (that’s a 615% increase)
• 77 GB Data
• 288 GB Indexes
• 25h Running Time

I was really surprised to see the indexes taking up that much space. It was a lot of indexes, but I wanted to be covered for a lot of different querying scenarios.

Daily Imports

Stats

• 3.8 Million Records
• 6 GB of Disk Space
• 30m Running Time

Initial Thoughts …

Don’t Do This.

There are a lot of great log organizing companies out there: Splunk, New Relic, DataDog, etc. I have no idea how much they cost, but the amount of space and the amount of time it takes to organize this data for querying absolutely justifies the need for their existence.