Eine Einführung in die Kosten- und Performance-Optimierung von BigQuery
Ausgangslage
Stellen Sie sich vor, in Ihrem Unternehmen laufen mehrere BigQuery-Datasets, und vor einem Monat ist ein neues Analystenteam dazugekommen, das fleißig Abfragen darauf fährt. Eines Morgens öffnen Sie bei einer Tasse Kaffee (oder Tee – wir wollen ja niemanden ausschließen) Ihre GCP-Rechnung und sehen: Die Kosten für BQ-Analyse und -Speicher sind um 5.000 % gestiegen. Gut möglich, dass Sie Ihr Getränk reflexartig auf Tastatur und Monitor verteilen. Dieser Artikel zeigt Ihnen, wie Sie diese unangenehme Situation in den Griff bekommen – oder besser noch von vornherein vermeiden – und stellt dafür mehrere Methoden zur Optimierung Ihrer BigQuery-Kosten vor.
Die Grundlagen schaffen
Bevor Sie Ihre BigQuery-Kosten angehen, sind ein paar Vorbereitungen und etwas Grundwissen nötig.
Beides behandle ich in diesem ersten Teil der Blogserie. Sobald die Basis steht und die Vorbereitungen erledigt sind, geht es in Teil 2 und den folgenden Teilen direkt an die eigentliche Optimierung.
Folgendes sollten Sie über BigQuery-Slots, Preismodelle und die kostenbestimmenden Faktoren wissen:
Slots
Im Zentrum der BigQuery-Verarbeitung steht ein Konstrukt namens Slot. Ein Slot ist im Grunde eine vCPU mit etwas Arbeitsspeicher – eine Mini-VM, die ausschließlich BigQuery zuarbeitet. Wenn Sie eine Abfrage ausführen, wird sie über eine Reihe zugewiesener Slots verarbeitet. Theoretisch gilt: Je mehr Slots zugewiesen und verfügbar sind, desto schneller laufen Ihre Abfragen.
Ein Slot übernimmt nahezu jede Aufgabe, die im Verlauf eines BigQuery-Jobs anfällt.
Shuffle Slots
Eine in der Dokumentation kaum erwähnte Aufgabe der Slots in BigQuery ist das Shuffling von Daten. Bis zu 60 % der Ihrem Projekt zugewiesenen Slots können zu jedem Zeitpunkt als Shuffle Slot fungieren.
Vereinfacht ausgedrückt heißt Shuffling: Bereits verarbeitete Daten werden an einen neuen Ort umverteilt, damit der aktuelle oder nächste Schritt im Abfrageplan schneller läuft. Googles Whitepaper zur BigQuery-Architektur gibt Ihnen einen tieferen Einblick in die Funktionsweise. Shuffling sorgt für eine effizientere Abfrageausführung, bindet aber wertvolle Slots für Operationen, die die Abfrage selbst nicht direkt voranbringen.
Preismodelle
BigQuery bietet derzeit zwei verschiedene Preismodelle: On-Demand und Flat-Rate.
On-Demand-Pricing
Standard ist das On-Demand-Pricing. Hier stehen Ihren Abfragen 2.000 Slots zur Verfügung, dazu kommt ein weiterer Pool mit einer nicht offiziell genannten Anzahl an Slots für Loads, Tabellenkopien und Extract-Jobs. Werden BigQuery-weit Slots frei, kann der Pool über die 2.000 hinauswachsen – garantiert ist das aber nicht, verlassen Sie sich also nicht darauf. Das Maximum, das mir bisher untergekommen ist, lag bei kurzen Spitzen bei rund 3.500 Slots.
Abgerechnet wird in diesem Modell zu einem festen Satz von 5 USD pro TB an gescannten Abfragedaten.
In vielen Unternehmen ist genau das der zentrale Kostentreiber bei BigQuery.
Flat-Rate-Pricing
Flat-Rate- (oder Slot-)Pricing legt einen Festpreis für BigQuery-Scans fest – möglicherweise zulasten der Performance. Dazu wird die Anzahl der verfügbaren Slots auf das vorab gekaufte Kontingent begrenzt und der Preis von 5 USD pro gescanntem TB entfällt.
Slots (einschließlich der weiter unten beschriebenen Flex Slots) werden stets in 100er-Paketen erworben. Standard-Slots (also alles außer Flex Slots) werden im Rahmen eines Commitments mit einer Laufzeit von einem Monat oder einem Jahr gebucht. Beachten Sie: Eine einmal getätigte Reservierung lässt sich nicht ohne Weiteres stornieren. Ein Monats-Commitment können Sie in ein Jahres-Commitment umwandeln, der umgekehrte Weg ist nicht möglich.
Was am Ende eines Commitments passiert, unterscheidet sich zwischen den beiden Varianten und hat sich bereits mehrfach geändert. Deshalb verlinke ich hier einfach die Google-Dokumentation zu Monats-Commitments und Jahres-Commitments, falls sich daran erneut etwas ändert. Ich empfehle dringend, sich rechtzeitig vor Ablauf einen Kalendereintrag zu setzen, um entsprechend reagieren zu können.
Im Flat-Rate-Modell lassen sich Ihre Slots zudem durch einen zusätzlichen Pool ergänzen oder vollständig ersetzen: die sogenannten Flex Slots. In Kapazität und Funktion sind sie mit normalen Slots identisch, bieten aber kürzere und flexiblere Commitment-Zeiten ab einer Mindestlaufzeit von 60 Sekunden. Nach Ablauf dieser Mindestzeit können Sie die Reservierung jederzeit kündigen, woraufhin die Slots verschwinden. So lassen sich Slot-Kapazitäten bei Bedarf nach oben oder unten skalieren. Sinnvoll ist das vor allem dann, wenn kurzfristig zusätzliche Slots gebraucht werden – etwa bei Lastspitzen oder besonders ressourcenintensiven Jobs, die andernfalls sämtliche Slots einer Reservierung belegen würden. Ein Paradebeispiel sind Einzelhandelsunternehmen am Black Friday oder Cyber Monday, den umsatzstärksten Einkaufstagen in den USA.
Zum Zeitpunkt dieses Artikels kosten 100 Slots 2.000 USD bei einem Monats-Commitment und 1.700 USD pro Monat bei einem Jahres-Commitment. Um die Performance des On-Demand-Modells zu erreichen, müssten Sie also zwanzig 100er-Pakete buchen – das entspricht 40.000 USD pro Monat im Monats-Commitment bzw. 34.000 USD pro Monat im Jahres-Commitment. Der Kauf von 2.000 Slots lohnt sich daher in der Regel nur, wenn Ihre BigQuery-Scankosten ohnehin mindestens in dieser Größenordnung liegen.
So ermitteln Sie die Kosten
Bevor Sie Kosten optimieren können, müssen Sie die Nutzungsdaten Ihrer Projekte analysieren. Dafür brauchen Sie Zugriff auf die Abfragedaten Ihrer Projekte und Datasets.
Eine kurze Anmerkung zu den Erwartungen: Ich behandle diesen Schritt bewusst schon im ersten Teil, damit Sie ihn frühzeitig umsetzen und mit der Datenerfassung beginnen können – falls Sie nicht alle Teile am Stück durchlesen (oder durchstehen). Tatsächlich genutzt werden die so gesammelten Daten erst ab Teil 3.
Es gibt zwei Wege: Audit-Log-Sinks und die INFORMATION_SCHEMA-Tabellen.
Der Audit-Log-Sink ist die bevorzugte Variante, denn die Daten sind deutlich umfangreicher und decken mehrere Projekte, Datasets und Regionen ab. Ist er noch nicht aktiviert, dauert es allerdings eine Weile, bis genügend Daten zusammengekommen sind, da diese erst während der Ausführung von BigQuery-Jobs erfasst werden. Beachten Sie, dass für den Audit-Log-Sink geringe Kosten in Form von BigQuery-Speicherkosten für die Daten anfallen.
Die INFORMATION_SCHEMA-Views existieren dagegen bereits in jedem Dataset, Projekt und jeder Region. Anders als beim Audit-Log-Sink hat aber jede Kombination aus Dataset und Region ihren eigenen Tabellensatz, sodass Abfragen mehrfach ausgeführt werden müssen. Außerdem fehlen in den INFORMATION_SCHEMA-Tabellen einige Informationen, die in den Audit-Log-Tabellen vorhanden sind. Beispiele dafür sind als Kommentare in den SQL-Abfragen weiter hinten im Artikel dokumentiert – dort stimmen sie nicht 1:1 mit den Audit-Log-Sink-Abfragen überein.
Wenn Sie die INFORMATION_SCHEMA-Methode wählen, können Sie den nächsten Abschnitt zur Einrichtung des Audit-Log-Sinks überspringen, denn Ihre Daten sind ja bereits vorhanden. Ein Vorbehalt bleibt: Die im Audit-Log-Sink gesammelten Daten sind reichhaltiger als das, was die INFORMATION_SCHEMA-Views bieten. Der Nachteil: Die Erfassung beginnt erst ab dem Zeitpunkt der Einrichtung. Sie müssen Ihre regulären Workloads also eine Weile laufen lassen, bevor die Daten nutzbar sind. Wie lange das dauert, hängt stark von den ausgeführten Workloads ab und davon, was in Ihrer Umgebung als "normale" Ausführungshäufigkeit gilt.
Hinweis: Wenn Sie bereits DoiT-Kunde sind und das BigQuery Lens-Dashboard in unserer Cloud Management Platform (CMP) nutzen, ist der Audit Log Sink in Ihrer Umgebung bereits eingerichtet. Ich empfehle, ihn für die in dieser Artikelserie beschriebenen Schritte zu verwenden. Weitere Informationen finden Sie hier.
Der voll qualifizierte Name des Datasets lautet
In diesem Fall können Sie den nächsten Abschnitt überspringen.
Audit Log Sink einrichten
Wenn Sie sich für die INFORMATION_SCHEMA-Views statt eines Audit Log Sinks entscheiden, können Sie diesen Abschnitt überspringen.
Im Folgenden finden Sie eine Anleitung zur Einrichtung eines Audit Log Sinks. Führen Sie die Befehle am besten lokal oder in der Cloud Shell aus, mit gcloud CLI auf das Projekt konfiguriert, in dem das Dataset gespeichert werden soll.
Führen Sie zunächst folgenden Befehl aus und passen Sie Sink-Name, Projekt-ID und Dataset-Name an:
gcloud logging sinks create <Sink Name> bigquery.googleapis.com/projects/<Project ID>/datasets/<Dataset Name> \ — use-partitioned-tables \
— log-filter=’protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"’
Der Befehl gibt anschließend ein eigens dafür erstelltes Service Account aus. Kopieren Sie sich die vollständige E-Mail-Adresse dieses Service Accounts in eine Notiz.
Im nächsten Schritt weisen Sie diesem Service Account in jedem Projekt, das BigQuery nutzt, die Rolle BigQuery Editor zu. Wie immer in der Cloud führen mehrere Wege zum Ziel – ich zeige hier die drei gängigsten: pro Projekt, organisationsweit und über einen Ordner innerhalb einer Organisation.
Hinweis: Projekt-, Ordner- und Organisations-ID finden Sie über den "Projektpicker" oben in der GCP-Konsole; in der Spalte ID steht die jeweils benötigte Kennung.
Pro Projekt:
gcloud projects add-iam-policy-binding <Project ID> \
— member=<Service Account> — role=roles/bigquery.dataEditor
Organisationsweit:
gcloud organizations add-iam-policy-binding <Organization ID> \
— -member=<Service Account> — role=roles/bigquery.dataEditor
Über einen Ordner:
gcloud resource-manager folders add-iam-policy-binding <Folder ID> \
— -member=<Service Account> — role=roles/bigquery.dataEditor
Ab diesem Zeitpunkt landen Abfragedaten im oben angegebenen Dataset. Für eine aussagekräftige Datenbasis sollten Sie mindestens einige Tage warten – für belastbare Ergebnisse besser einige Wochen. Je länger Daten aus Ihrer normalen Nutzung erfasst werden, desto besser, denn so werden Lastspitzen und Nutzungsmuster zuverlässig sichtbar.
INFORMATION_SCHEMA-Views
Die INFORMATION_SCHEMA-Views existieren bereits in jedem Dataset, Projekt und jeder Region. Anders als beim Audit-Log-Sink hat aber jede Kombination aus Dataset und Region ihren eigenen Tabellensatz, sodass Abfragen mehrfach ausgeführt werden müssen. In den INFORMATION_SCHEMA-Views fehlen außerdem einige Informationen, die in den Audit-Log-Tabellen vorhanden sind. Beispiele dafür sind als Kommentare in den SQL-Abfragen weiter hinten in der Serie dokumentiert – dort stimmen sie nicht exakt mit den Audit-Log-Sink-Abfragen überein.
Der Vorteil der INFORMATION_SCHEMA-Views: Die Daten sind bereits vorhanden, auch wenn Sie den Sink nicht eingerichtet haben.
Wie es weitergeht
Damit endet der erste Teil dieser Serie. Er bildet die Grundlage für die Methoden, die in den folgenden Teilen beschrieben werden.