The memory limit was doing the design review

# Self-authored for the EML case corpus (no external origin). A query memory
# limit was raised from 4 GB to 16 GB. What the analysts wrote afterwards is
# computed below, along with what the old limit had been doing.
#
# Raising the limit was correct. Three legitimate quarterly reports could not
# run at all, the workarounds were worse than the queries - one of them wrote
# a temporary table and read it back - and the machine had 400 GB sitting
# idle. Refusing to run a correct report on hardware that can afford it is not
# a virtue.
#
# The limit was also the only thing anybody consulted about how a query should
# be shaped. A kill at 4 GB is a review comment delivered by the scheduler:
# it arrives every time, it cannot be argued with, and it is free. Raising the
# ceiling removed the report failures and it removed the review.
#
# Queries are counted by the memory they need, before and after.

# [bucket GB, queries per month before, queries per month after]
[[1, 210, 190], [2, 90, 84], [4, 31, 40], [8, 0, 46], [16, 3, 22], [32, 0, 9]] => buckets

len(buckets) => n
4 => limit_before
16 => limit_after

"memory needed   attempts/month before   after" ^0
for b in buckets:
    "  " + str(b[0]) + " GB              " + str(b[1]) + "                    " + str(b[2]) ^0
"" ^0

0 => before_total
0 => after_total
for b in buckets:
    before_total + b[1] => before_total
    after_total + b[2] => after_total
"queries attempted a month : " + str(before_total) + " -> " + str(after_total) + ", " + str(int(after_total * 100 / before_total)) + " per 100" ^0
"limit           : " + str(limit_before) + " GB -> " + str(limit_after) + " GB" ^0
"" ^0

# ---- what gets killed at each limit ----

def killed_at(limit, which):
    0 => k
    for b in buckets:
        if b[0] > limit:
            if which == "before":
                k + b[1] => k
            else:
                k + b[2] => k
    return k

"queries killed per month" ^0
"  old workload against the old " + str(limit_before) + " GB limit : " + str(killed_at(limit_before, "before")) ^0
"  new workload against the new " + str(limit_after) + " GB limit : " + str(killed_at(limit_after, "after")) ^0
"  new workload against the old " + str(limit_before) + " GB limit : " + str(killed_at(limit_before, "after")) ^0
"  the kill count came back, against a ceiling four times higher" ^0
"" ^0

# ---- what the distribution did ----

0 => weighted_before
0 => weighted_after
for b in buckets:
    weighted_before + b[0] * b[1] => weighted_before
    weighted_after + b[0] * b[2] => weighted_after
"average memory per query" ^0
"  before : " + str(int(weighted_before * 10 / before_total)) + " tenths of a GB" ^0
"  after  : " + str(int(weighted_after * 10 / after_total)) + " tenths of a GB" ^0
"  multiplied by " + str(int(weighted_after * before_total * 10 / (weighted_before * after_total))) + " tenths" ^0
"total memory demanded per month" ^0
"  before : " + str(weighted_before) + " GB" ^0
"  after  : " + str(weighted_after) + " GB" ^0
"  attempts rose " + str(int((after_total - before_total) * 100 / before_total)) + "% and memory demanded rose " + str(int((weighted_after - weighted_before) * 100 / weighted_before)) + "%" ^0
"" ^0

# ---- the three reports the change was made for ----

# [report, GB it needs, ran before?, the workaround it was using]
[["quarterly cohort", 9, "no", "a temporary table read back in two passes"], ["margin by region", 11, "no", "sampling to a tenth and scaling up"], ["retention curve", 14, "no", "three separate queries joined by hand"]] => blocked
"the reports that could not run" ^0
0 => blocked_gb
for r in blocked:
    blocked_gb + r[1] => blocked_gb
    "  " + r[0] + " : " + str(r[1]) + " GB, workaround was " + r[3] ^0
"  they run now, correctly, in one pass each" ^0
"  memory they use : " + str(blocked_gb) + " GB a month between them" ^0
"  that is " + str(int(blocked_gb * 100 / (weighted_after - weighted_before))) + "% of the increase in memory demanded" ^0
"" ^0

# ---- what the rest of the increase is ----

weighted_after - weighted_before => increase
"the increase, apportioned" ^0
"  the three reports the change was for : " + str(blocked_gb) + " GB" ^0
"  everything else                      : " + str(increase - blocked_gb) + " GB" ^0
"  ratio : " + str(int((increase - blocked_gb) / blocked_gb)) + " to 1" ^0
"  none of the second group was blocked before, so none of it was waiting" ^0
"  for the change - it is queries that would have been written smaller" ^0
"" ^0

# ---- what a kill used to do ----

"what happened when a query was killed at " + str(limit_before) + " GB" ^0
"  the analyst rewrote it     : every time, there was no other option" ^0
"  time to rewrite            : under an hour, usually a narrower date range" ^0
"  reviews that comment on query memory : 0" ^0
"  so the scheduler was the only reviewer, and it reviewed every query" ^0
"  the same way for free" ^0
"" ^0

# ---- where the ceiling is now ----

"queries above each candidate ceiling, on the current workload" ^0
[4, 8, 16, 32] => ceilings
for c in ceilings:
    "  " + str(c) + " GB : " + str(killed_at(c, "after")) + " killed a month" ^0
"  the workload reshapes itself around whichever of these is chosen, so the" ^0
"  kill count is a property of the ceiling and not of the analysis" ^0
"" ^0

# ---- the control: memory set by the data, not by the query ----
#
# Where a job's memory is a function of how much data exists rather than of
# how the query was written, raising the ceiling changes nothing about it.

[["nightly rollup", 6, 6], ["index rebuild", 12, 12], ["export", 3, 3]] => bounded
"control - jobs whose memory is fixed by the input size" ^0
0 => moved
for j in bounded:
    "  " + j[0] + " : " + str(j[1]) + " GB before, " + str(j[2]) + " GB after" ^0
    if not (j[1] == j[2]):
        moved + 1 => moved
"  jobs whose memory changed when the ceiling rose : " + str(moved) + " of " + str(len(bounded)) ^0
if moved == 0:
    "  none, because nobody chooses these numbers - the data does" ^0
    "  here the ceiling is a safety limit rather than a design constraint," ^0
    "  and raising it is purely an improvement" ^0
"" ^0

"The three reports really were blocked and their workarounds really were" ^0
"worse than the queries. The limit was also the only review any query got," ^0
"so " + str(increase - blocked_gb) + " GB of the " + str(increase) + " GB increase came from queries nobody had blocked." ^0

buckets = [[1, 210, 190], [2, 90, 84], [4, 31, 40], [8, 0, 46], [16, 3, 22], [32, 0, 9]]
n = len(buckets)
limit_before = 4
limit_after = 16
print("memory needed   attempts/month before   after")
for b in buckets:
    print("  " + str(b[0]) + " GB              " + str(b[1]) + "                    " + str(b[2]))
print("")
before_total = 0
after_total = 0
for b in buckets:
    before_total = before_total + b[1]
    after_total = after_total + b[2]
print("queries attempted a month : " + str(before_total) + " -> " + str(after_total) + ", " + str(int(after_total * 100 / before_total)) + " per 100")
print("limit           : " + str(limit_before) + " GB -> " + str(limit_after) + " GB")
print("")

def killed_at(limit, which):
    k = 0
    for b in buckets:
        if b[0] > limit:
            if which == "before":
                k = k + b[1]
            else:
                k = k + b[2]
    return k

print("queries killed per month")
print("  old workload against the old " + str(limit_before) + " GB limit : " + str(killed_at(limit_before, "before")))
print("  new workload against the new " + str(limit_after) + " GB limit : " + str(killed_at(limit_after, "after")))
print("  new workload against the old " + str(limit_before) + " GB limit : " + str(killed_at(limit_before, "after")))
print("  the kill count came back, against a ceiling four times higher")
print("")
weighted_before = 0
weighted_after = 0
for b in buckets:
    weighted_before = weighted_before + b[0] * b[1]
    weighted_after = weighted_after + b[0] * b[2]
print("average memory per query")
print("  before : " + str(int(weighted_before * 10 / before_total)) + " tenths of a GB")
print("  after  : " + str(int(weighted_after * 10 / after_total)) + " tenths of a GB")
print("  multiplied by " + str(int(weighted_after * before_total * 10 / (weighted_before * after_total))) + " tenths")
print("total memory demanded per month")
print("  before : " + str(weighted_before) + " GB")
print("  after  : " + str(weighted_after) + " GB")
print("  attempts rose " + str(int((after_total - before_total) * 100 / before_total)) + "% and memory demanded rose " + str(int((weighted_after - weighted_before) * 100 / weighted_before)) + "%")
print("")
blocked = [["quarterly cohort", 9, "no", "a temporary table read back in two passes"], ["margin by region", 11, "no", "sampling to a tenth and scaling up"], ["retention curve", 14, "no", "three separate queries joined by hand"]]
print("the reports that could not run")
blocked_gb = 0
for r in blocked:
    blocked_gb = blocked_gb + r[1]
    print("  " + r[0] + " : " + str(r[1]) + " GB, workaround was " + r[3])
print("  they run now, correctly, in one pass each")
print("  memory they use : " + str(blocked_gb) + " GB a month between them")
print("  that is " + str(int(blocked_gb * 100 / (weighted_after - weighted_before))) + "% of the increase in memory demanded")
print("")
increase = weighted_after - weighted_before
print("the increase, apportioned")
print("  the three reports the change was for : " + str(blocked_gb) + " GB")
print("  everything else                      : " + str(increase - blocked_gb) + " GB")
print("  ratio : " + str(int((increase - blocked_gb) / blocked_gb)) + " to 1")
print("  none of the second group was blocked before, so none of it was waiting")
print("  for the change - it is queries that would have been written smaller")
print("")
print("what happened when a query was killed at " + str(limit_before) + " GB")
print("  the analyst rewrote it     : every time, there was no other option")
print("  time to rewrite            : under an hour, usually a narrower date range")
print("  reviews that comment on query memory : 0")
print("  so the scheduler was the only reviewer, and it reviewed every query")
print("  the same way for free")
print("")
print("queries above each candidate ceiling, on the current workload")
ceilings = [4, 8, 16, 32]
for c in ceilings:
    print("  " + str(c) + " GB : " + str(killed_at(c, "after")) + " killed a month")
print("  the workload reshapes itself around whichever of these is chosen, so the")
print("  kill count is a property of the ceiling and not of the analysis")
print("")
bounded = [["nightly rollup", 6, 6], ["index rebuild", 12, 12], ["export", 3, 3]]
print("control - jobs whose memory is fixed by the input size")
moved = 0
for j in bounded:
    print("  " + j[0] + " : " + str(j[1]) + " GB before, " + str(j[2]) + " GB after")
    if not j[1] == j[2]:
        moved = moved + 1
print("  jobs whose memory changed when the ceiling rose : " + str(moved) + " of " + str(len(bounded)))
if moved == 0:
    print("  none, because nobody chooses these numbers - the data does")
    print("  here the ceiling is a safety limit rather than a design constraint,")
    print("  and raising it is purely an improvement")
print("")
print("The three reports really were blocked and their workarounds really were")
print("worse than the queries. The limit was also the only review any query got,")
print("so " + str(increase - blocked_gb) + " GB of the " + str(increase) + " GB increase came from queries nobody had blocked.")

memory needed   attempts/month before   after
  1 GB              210                    190
  2 GB              90                    84
  4 GB              31                    40
  8 GB              0                    46
  16 GB              3                    22
  32 GB              0                    9

queries attempted a month : 334 -> 391, 117 per 100
limit           : 4 GB -> 16 GB

queries killed per month
  old workload against the old 4 GB limit : 3
  new workload against the new 16 GB limit : 9
  new workload against the old 4 GB limit : 77
  the kill count came back, against a ceiling four times higher

average memory per query
  before : 16 tenths of a GB
  after  : 39 tenths of a GB
  multiplied by 23 tenths
total memory demanded per month
  before : 562 GB
  after  : 1526 GB
  attempts rose 17% and memory demanded rose 171%

the reports that could not run
  quarterly cohort : 9 GB, workaround was a temporary table read back in two passes
  margin by region : 11 GB, workaround was sampling to a tenth and scaling up
  retention curve : 14 GB, workaround was three separate queries joined by hand
  they run now, correctly, in one pass each
  memory they use : 34 GB a month between them
  that is 3% of the increase in memory demanded

the increase, apportioned
  the three reports the change was for : 34 GB
  everything else                      : 930 GB
  ratio : 27 to 1
  none of the second group was blocked before, so none of it was waiting
  for the change - it is queries that would have been written smaller

what happened when a query was killed at 4 GB
  the analyst rewrote it     : every time, there was no other option
  time to rewrite            : under an hour, usually a narrower date range
  reviews that comment on query memory : 0
  so the scheduler was the only reviewer, and it reviewed every query
  the same way for free

queries above each candidate ceiling, on the current workload
  4 GB : 77 killed a month
  8 GB : 31 killed a month
  16 GB : 9 killed a month
  32 GB : 0 killed a month
  the workload reshapes itself around whichever of these is chosen, so the
  kill count is a property of the ceiling and not of the analysis

control - jobs whose memory is fixed by the input size
  nightly rollup : 6 GB before, 6 GB after
  index rebuild : 12 GB before, 12 GB after
  export : 3 GB before, 3 GB after
  jobs whose memory changed when the ceiling rose : 0 of 3
  none, because nobody chooses these numbers - the data does
  here the ceiling is a safety limit rather than a design constraint,
  and raising it is purely an improvement

The three reports really were blocked and their workarounds really were
worse than the queries. The limit was also the only review any query got,
so 930 GB of the 964 GB increase came from queries nobody had blocked.

Trace event types