Closed Bug 1609209 Opened 6 years ago Closed 6 years ago

taskclusteretl - Break down worker overhead costs by type

Categories

(Data Platform and Tools :: General, enhancement, P1)

Product:
Data Platform and Tools
Component:
General
Type:
enhancement
Points:
3

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: catlee, Assigned: trink)

Details

There are several types of overhead costs that we are interested in tracking and associating back to worker pools where possible. e.g. idle CPU time, EBS costs, S3 costs. We need to be able to determine the breakdown of each type of overhead into each worker pool.

Points: --- → 3
Priority: -- → P2
Assignee: nobody → mtrinkala
Priority: P2 → P1

We are trying to track down what looks like a bunch of orphaned volumes (these are just for one worker type resourcetags_user_name = "gecko-t-win10-64") basically anything with a /gecko. Here is a list of win10-64. Can these simply be deleted and free up $10-15K a month?
https://mozilla.slack.com/files/U4EH1CW94/F01150S8TU4/untitled

Flags: needinfo?(klibby)

This will be run as a one off.

  1. GCP we only have instance compute costs so there is nothing to breakout from the costs you are already seeing.
  2. releng (these are hand calculated/averagedcosts but if necessary we could break out hardware (mac, moonshot, bitbar (that is basically worker type)) and datacenter(mdc1/2 Data center, Rack, power, cooling, Connectivity, Remote hands, Cross connects) if that would be useful.
  3. AWS has a decent breakout but the only big ones are compute and storage. Below is the query that is being tweaked for the one off
WITH
  data AS (
  SELECT
    *
  FROM
    `jthomas-billing.billing.aws_billing_v1`
  INNER JOIN
    `jthomas-billing.billing.aws_billing_latest_reports_v1`
  USING
    (billing_assembly_id) ),
  rate AS (
  SELECT
    usage_start_date,
    resourcetags_user_name,
    --/*
    CASE
      WHEN lineitem_lineitemdescription LIKE "%Spot Instance%" THEN "Spot"
      WHEN lineitem_lineitemdescription LIKE "%On Demand%" THEN "On Demand"
      WHEN lineitem_lineitemdescription LIKE "%reserved instance%" THEN "Reserved Instance"
      WHEN lineitem_lineitemdescription LIKE "%data transfer%"
    OR lineitem_lineitemdescription LIKE "%AWS-In-Bytes%"
    OR lineitem_lineitemdescription LIKE "%AWS-Out-Bytes%" THEN "Data Transfer"
      WHEN lineitem_lineitemdescription LIKE "%CloudTrail%" THEN "CloudTrail"
      WHEN lineitem_lineitemdescription LIKE "%storage%" THEN "Storage"
      WHEN lineitem_lineitemdescription LIKE "%event recorded%" THEN "Event Recorded"
      WHEN lineitem_lineitemdescription LIKE "%HTTP_%Requests%" THEN "HTTP Requests"
      WHEN lineitem_lineitemdescription LIKE "%Lambda%" THEN "Lambda"
      WHEN lineitem_lineitemdescription LIKE "%log data ingested%" THEN "Log Data Ingested"
      WHEN lineitem_lineitemdescription LIKE "%metrics%" THEN "Metrics"
      WHEN lineitem_lineitemdescription LIKE "%data analyzed%" THEN "Data Analyzed"
      WHEN lineitem_lineitemdescription LIKE "%Dashboard%" THEN "Dashboard"
      WHEN lineitem_lineitemdescription LIKE "%VPN%" THEN "VPN"
      WHEN lineitem_lineitemdescription LIKE "%DynamoDB%" THEN "DynamoDB"
      WHEN lineitem_lineitemdescription LIKE "%snapshot data%" THEN "Snapshot Data"
      WHEN lineitem_lineitemdescription LIKE "%shard%" THEN "shard"
      WHEN lineitem_lineitemdescription LIKE "%SQS%" THEN "SQS"
    ELSE
    "other"
  END as description,
  --*/
    --lineitem_lineitemdescription AS description,
    SUM(lineitem_unblendedcost) AS cost,
    SUM(
    IF
      (pricing_unit = "Hours"
        OR pricing_unit = "Hrs"
        OR (pricing_unit IS NULL
          AND lineitem_lineitemdescription LIKE "%Spot Instance-hour%"),
        lineitem_usageamount,
        NULL)) AS hours,
    CASE lineitem_usageaccountid
      WHEN 692406183521 THEN "firefox"      -- TaskCluster Platform - 8100
      WHEN 43838267467 THEN "firefox"      -- cloudops-taskcluster-aws-prod
      WHEN 885316786408 THEN "community" -- moz-fx-tc-community-workers
      WHEN 710952102342 THEN "staging"   -- taskcluster-aws-staging
      WHEN 897777688886 THEN "staging"   -- cloudops-taskcluster-aws-stage
      WHEN 400370709957 THEN "staging"   -- taskcluster-provisioner-staging
  END
    AS cluster
  FROM
    data
  WHERE
    lineitem_usageaccountid IN ( 692406183521,
      43838267467,
      885316786408,
      710952102342,
      897777688886,
      400370709957)
    AND usage_start_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 5 day)
    AND usage_start_date < CURRENT_DATE()
  GROUP BY
    usage_start_date,
    resourcetags_user_name,
    lineitem_lineitemdescription,
    cluster),
  aws AS (
  SELECT
    usage_start_date AS date,
    REGEXP_EXTRACT(resourcetags_user_name, "(.+)/.+") AS provisionerId,
    ifnull(REGEXP_EXTRACT(resourcetags_user_name, ".+/(.+)"),
      resourcetags_user_name) AS workerType,
    description,
    SUM(cost) AS cost,
    SUM(hours) AS hours,
    SUM(cost) / (ifnull(SUM(hours),
        24) * 3600 * 1000) AS cost_per_ms,
    "aws" AS cost_origin,
    cluster
  FROM
    rate
  GROUP BY
    date,
    provisionerId,
    workerType,
    description,
    cost_origin,
    cluster)
SELECT
  *
FROM
  aws
WHERE
  --workerType = "t-linux-metal" and
  cluster = "firefox"
  order by workerType, cost desc

fubar: It's not quite as easy as I had thought. it turns out that some of those are backing stores for images that may still be in use; wander did a clean up a while back that nuked some and apparently it was a world of pain to put back together but I've got a script to play with to try and sort the good from the bad. just been limited in time

Status: NEW → RESOLVED
Closed: 6 years ago
Flags: needinfo?(klibby)
Resolution: --- → FIXED

When auditing/reworking the relops cost data detailed billing information has been added to all cost_origins.
https://datastudio.google.com/reporting/1rH-6FhYt6Qa61-Il6g0NFf0jtxmO31po/page/Y3oPB

(In reply to Mike Trinkala [:trink] from comment #5)

fubar: It's not quite as easy as I had thought. it turns out that some of those are backing stores for images that may still be in use; wander did a clean up a while back that nuked some and apparently it was a world of pain to put back together but I've got a script to play with to try and sort the good from the bad. just been limited in time

following up - coop removed a bunch of volumes today and tomorrow we'll remove more. in the end we decided that trying the slow but sure method would cost us a lot so instead we're ripping the bandaid off and are on hand to deal with any fallout.

Are all S3 and EBS costs captured in "Storage"? It might be useful to break those apart into distinct items on the report.

Component: Datasets: General → General
You need to log in before you can comment on or make changes to this bug.