Autopsy of AWS EC2 pricing - and some anomalies

Summary tables

• Linux
• Suse
• RedHat
• Windows
• SQL Server Web
• SQL Server Standard
• SQL Server Enterprise
• Infrastructure

Intro

At Strategic Blue, we care about the money, and we’ve been collecting prices of AWS and other cloud vendors for a while. Another characteristic of Strategic Blue is that we are curious and love digging into the numbers, checking common assumptions, and find new ways to save money.

• The first article in the series focused on finding the cost of Windows on AWS and make some basic comparison with Azure
• A second article show an anomaly in AWS pricing probably due to a human error

This article extends on those researches to split AWS pricing into three components:

1. the cost of the infrastructure
2. the cost of the OS licence
3. the cost of the software (SQL Server) license running on those instances

This is based only on EC2 data but could be replicated on other AWS services.

Note that for all tables there are some exceptions which are covered at the end of this article

Executive Summary

• All the pricing are consistent, showing great care on AWS part to charge consistently in each region
• There are lots of little exceptions that seem to be more human errors in entering the data

POMO

Purpose: to find if a good approximation of the cost of the infrastructure, Os, and software licensing on AWS Objective: being able to separate infrastructure and licensing costs Method: SQL queries on AWS pricing table Outcome: A set of tables license price list for AWS

Assumptions:

• Work is done on shared instances
• the infrastructure cost is the cost of a Linux VM
• The OS cost is instance price inc. OS - Linux instance price
• The Software cost is the hourly price - infrastructure price - os license price
• Based on April 2020 prices

Other possible experiments:

• check RI pricing logic - are RI calculated on the infrastructure cost then a license is added
• try to extract the different components of the infrastructure by making assumptions
• improve the queries
• make this analysis automated
• play with the estimated cost of DC supply (e.g. real-estate, electricity) between regions

Summary tables

• Linux
• Suse
• RedHat
• Windows
• SQL Server Web
• SQL Server Standard
• SQL Server Enterprise
• Infrastructure

Linux

is always 0 and is considered the price of the infrastructure.

• Top

Suse

SUSE being not standard the list is long

SELECT  gen, size, os, oslicprice
	FROM public.v_aws_infra_os_sfw
	where os = 'SUSE'
	group by gen, size, os, oslicprice
	order by size, gen, oslicprice
	;

• Top

RedHat

For an unknown reason (mistake?) the r4.xlarge costs $0.13 when for all other families the price for xlarge is .06

SELECT  size, os, oslicprice
	FROM public.v_aws_infra_os_sfw
	where os = 'RHEL'
	group by size, os, oslicprice
	order by size, oslicprice
	;

• Top

windows

SELECT  size, os, oslicprice, COUNT(*)
	FROM public.v_aws_infra_os_sfw
	where os = 'Windows'
	AND software = 'NA'
	AND gen NOT LIKE 't%'
	AND gen NOT LIKE 'd2'
	AND gen NOT LIKE 'c4'
	group by size, os, oslicprice
	order by size, oslicprice
	;

anomalies

d2 are priced differently

c4.8xlarge are priced differently

SQL Web

The query ignore when the number of occurrence of a price are < 12, this eliminates most strange behaviour in the pricing.

SELECT t1.size, software, ROUND(sfwprice,2) sfwprice, cpu, count(*) counting
	FROM public.v_aws_infra_os_sfw t1 JOIN aws_size2cpu t2 ON t1.size = t2.size
	where software = 'SQL Web'
	AND current_generation = 'Yes'
	AND gen NOT LIKE 't%'
	GROUP BY software, t1.size, cpu,  ROUND(sfwprice,2)
	HAVING count(*)  > 12
	ORDER BY cpu, software, sfwprice,  counting desc
	;

Anomalies

There are some Negative SQL Web pricing making it cheaper to get a Windows + SQL Web versus Windows only

SELECT  region, size, os, oslicprice, sfwprice, COUNT(*)
	FROM public.v_aws_infra_os_sfw
	where os = 'Windows'
	AND software = 'SQL Web'
	AND gen LIKE 'r4'
	AND size = '2xlarge'
	AND sfwprice < 0
	group by size, os, oslicprice, sfwprice, region
	order by size, oslicprice
	;

• Top

SQL Standard

The query ignore when the number of occurrence of a price are < 12, this eliminates most strange behaviour in the pricing.

SELECT t1.size, software, ROUND(sfwprice,2) sfwprice, cpu, count(*) counting
	FROM public.v_aws_infra_os_sfw t1 JOIN aws_size2cpu t2 ON t1.size = t2.size
	where software = 'SQL Std'
	AND current_generation = 'Yes'
	AND gen NOT LIKE 't%'
  AND gen NOT LIKE 'd2'
	GROUP BY software, t1.size, cpu,  ROUND(sfwprice,2)
	HAVING count(*)  > 12
	ORDER BY cpu, software, sfwprice,  counting desc
	;

• Top

SELECT  size, os, oslicprice, COUNT(*)
	FROM public.v_aws_infra_os_sfw
	where os = 'Windows'
	AND software = 'NA'
	AND gen LIKE 'c4'
	AND size = '8xlarge'
	group by size, os, oslicprice
	order by size, oslicprice
	;

SQL Enterprise

The query ignore when the number of occurrence of a price are < 12, this eliminates most strange behaviour in the pricing.

SELECT t1.size, software, ROUND(sfwprice,2) sfwprice, cpu, count(*) counting
	FROM public.v_aws_infra_os_sfw t1 JOIN aws_size2cpu t2 ON t1.size = t2.size
	where software = 'SQL Ent'
	AND current_generation = 'Yes'
	AND gen NOT LIKE 't%'
	GROUP BY software, t1.size, cpu,  ROUND(sfwprice,2)
	HAVING count(*)  > 12
	ORDER BY cpu, software, sfwprice,  counting desc
	;

• Top

Infrastructure

The price per CPU per generation seems consistent. Here is a summary table (the big one is 1.2M of text)

SELECT gen, MIN(ROUND(infraprice/t2.cpu,3)) percpuprice_min, ROUND(AVG(infraprice/t2.cpu),3) percpuprice_avg, MAX(ROUND(infraprice/t2.cpu,3)) percpuprice_max, ROUND(stddev_samp(infraprice/t2.cpu),3) percpuprice_standard_deviation
	FROM public.v_aws_infra_os_sfw t1 JOIN aws_size2cpu t2 ON t1.size = t2.size
	where current_generation = 'Yes'
	GROUP BY gen  
	ORDER BY gen
	;

• Top