BigQuery-Optimierungen (Teil 3)

Teil 1

Teil 2

Die beiden vorherigen Abschnitte haben viele Themen abgedeckt: notwendiges Vorwissen, allgemein nützliches Hintergrundwissen und das Aufsetzen einer Infrastruktur zur Datenanalyse.

Der gesamte Code dieser Serie liegt im folgenden GitHub-Repository: https://github.com/doitintl/bigquery-optimization-queries

Da die beiden Monitoring-Methoden völlig unterschiedliche Schemata erzeugen, habe ich die SQL-Dateien im Repository auf zwei Ordner aufgeteilt (audit_log und information_schema).

Jedes Verzeichnis enthält eine Reihe von SQL-Dateien für unterschiedliche Zwecke. In jedem Ordner liegt eine README.md, die den Zweck der einzelnen Skripte erklärt.

Im Stammverzeichnis liegt ein Python-Skript namens generate_sql_files.py, das eine Kopie der SQL-Dateien mit Ihren Projekt- und Dataset-Informationen erzeugt.

Da das GitHub-Repository laufend wächst, habe ich einen separaten Artikel verfasst, der jede Query mit Beschreibung auflistet – Sie finden ihn hier.

Diese Übersicht wird aktualisiert, sobald neue Queries hinzukommen.

Ab dem nächsten Abschnitt und im weiteren Verlauf dieser Serie werde ich auf viele Queries verweisen und große Datenmengen untersuchen. Die BigQuery-Oberfläche kommt mit umfangreichen Queries und Analysen einigermaßen zurecht, ich empfehle aber DRINGEND, die Daten in eine Tabellenkalkulation zu exportieren.

Google Sheets ist die ideale Wahl, da BigQuery Ergebnisse direkt dorthin exportieren kann (mit gewissen Größenbeschränkungen). Außerdem ist ein einmaliger Export günstiger, als dieselbe Query für die Analyse mehrfach erneut auszuführen.

Behalten Sie auch die Kosten im Blick. Einige dieser Queries können sehr teuer werden, daher empfehle ich grundsätzlich, vor der Ausführung die Kosten zu prüfen. In jeder Query gibt es eine Variable namens interval_in_days, die steuert, wie weit die Query zurückblickt – damit lässt sich die gescannte Datenmenge reduzieren. Manche unserer Kunden haben einfach die WHERE-Klauseln angepasst, um eigene Bereiche abzugreifen – Ihrer Kreativität sind hier also keine Grenzen gesetzt.

Alle Schritte in diesem Teil lassen sich auch im BQ Lens-Bereich des CMP durchführen. Über das Explorer-Panel führen Sie diese Schritte visuell aus – tatsächlich sind viele der Audit-Log-Sink-Queries fast identisch mit denen, die das CMP intern zur Anzeige der Daten nutzt.

Der erste Schritt der Kostenoptimierung in BigQuery besteht darin, die teuersten Queries in Ihrer Umgebung aufzuspüren. Häufig ahnt niemand auch nur ihre Existenz – um es mit den Worten von G.I. Joe aus den Samstagmorgen-Cartoons zu sagen: "Knowing is half the battle."

Sind die Queries einmal identifiziert, geht es ans Optimieren: problematische Prozesse oder Nutzer ermitteln und gezielt nachbessern.

Ich werde auf SQL-Dateien aus dem oben erwähnten GitHub-Repository verweisen (Link). Sie können wahlweise die Audit-Log- oder die information_schema-Dateien verwenden – beide liefern dasselbe Ergebnis.

Die erste Query für die Analyse befindet sich in der Datei top_costly_queries.sql und ist – was Kosten angeht – wohl die wichtigste von allen. Sie erfasst die Gesamtkosten, die eine Query über das Intervall hinweg in sämtlichen Ausführungen verursacht. Sie zeigt also die tatsächlich teuersten Queries, die durch häufige Wiederholung weit mehr kosten können, als auf den ersten Blick ersichtlich ist.

Diese Query verfolgt zwei Ziele: zum einen die insgesamt teuerste Query in Ihrer Umgebung zu ermitteln, zum anderen festzustellen, ob eine teure Query häufiger läuft als nötig. Oft führt ein Prozess wie ein Cronjob, ein Task in einem Airflow-DAG, eine Cloud Function usw. eine Query mehrfach aus – häufig öfter als wirklich erforderlich. Und noch häufiger hat der Ersteller, Betreuer oder die Person, die die Cloud-Rechnung bezahlt, keine Vorstellung davon, was diese Queries pro Ausführung oder pro Monat kosten. Genau hier setzt diese Query an!

Die zweite Query in diesem Abschnitt liegt in der Datei top_billed_queries.sql. Sie listet die teuersten Queries Ihrer Umgebung absteigend sortiert nach Kosten auf. Anders als die erste Query berücksichtigt sie keine Mehrfachausführungen, sondern zeigt nur die Kosten pro einzelner Ausführung.

Beim Ausführen landen die teuersten Queries sofort an der Spitze des Ergebnissatzes. Eine bewährte Vorgehensweise: Schauen Sie sich die oberste Query an und prüfen Sie zunächst, wie viele Daten sie verarbeitet hat (die Spalten total*Billed). Nehmen Sie diesen Wert und werfen Sie dann einen Blick auf die Query selbst, um zu verstehen, was die hohen Kosten verursacht.

Häufig sind die oben genannten Probleme die Übeltäter, es kann aber auch andere Ursachen geben. Sehen Sie sich die Queries also genauer an, um offensichtliche Auffälligkeiten zu erkennen.

Falls dieselbe Query mehrfach im Ergebnis auftaucht und die Auswertung verzerrt oder erschwert, schauen Sie stattdessen in die Datei top_billed_queries_deduplicated.sql. Es ist exakt dieselbe Query, jedoch werden identische Queries dedupliziert, sodass nur ein Ergebnis erscheint. Sie läuft länger und scannt mehr Daten, liefert dafür aber deduplizierte Ergebnisse.

Zum Zeitpunkt der Erstellung bietet BigQuery keine native "String-Similarity"-Aggregation wie Hamming- oder Levenshtein-Distanz. Das Herausfiltern ähnlicher Queries mit kleinen Abweichungen (etwa nur einem geänderten Datum) ist deshalb relativ aufwendig. Wer es benötigt, findet einige UDF-Lösungen, die solche "String-Similarity"-Algorithmen für BigQuery umsetzen. Ich empfehle dafür allerdings eine Lösung außerhalb von BigQuery, da UDF-Implementierungen für rechenintensive Algorithmen dieser Art ziemlich langsam sind.

Die dritte und letzte Query zum Thema Kosten finden Sie in der Datei top_cost_users.sql. Sie listet die teuersten Queries Ihrer Umgebung auf, sortiert nach Nutzer und anschließend nach der teuersten Query.

Ziel ist es zu zeigen, welche Nutzer oder Service-Accounts am meisten Geld ausgeben – und für welche Queries. Häufig finden sich in dieser Liste Prozesse mit ineffizienten Queries, von denen niemand weiß. In Extremfällen handelt es sich um Prozesse aus anderen Projekten oder Clouds. Zusätzliche Filter zum Ausblenden solcher Treffer können hier sinnvoll sein.

Das sind die drei zentralen, allgemein einsetzbaren Queries, um Kostenfresser aufzuspüren. Im GitHub-Repo finden Sie weitere Queries für spezielle Zwecke, etwa um Looker-Queries zu identifizieren, die Häufigkeit einer Query zu ermitteln, die Kosten bestimmter gelabelter Queries zu prüfen usw. Sie sind sehr spezifisch, kamen aber bei mir und anderen Teammitgliedern bei DoiT bereits zum Einsatz – und wir teilen sie nun mit der Community.

Das nächste große Thema ist das Aufspüren von Queries, die mehr Ressourcen verbrauchen als nötig oder nicht so performen, wie sie sollten. In vielen Fällen überschneiden sich diese mit den teuersten Queries – es gibt also häufig gemeinsame Kandidaten.

In diesem Abschnitt fällt häufig der Begriff Komplexität. Diesen zu definieren ist – nun ja – komplex, aber der Einfachheit halber definiere ich ihn hier als "wie viele Slots eine Query während ihrer Ausführung verbraucht". Dieser Wert ergibt sich aus der gesamten in Slots verbrachten Arbeitszeit geteilt durch die gesamte Ausführungszeit der Query. Ein Beispiel für die View JOBS_BY_PROJECT:

SELECT
 SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount
FROM
 `<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

Eine häufig gestellte Frage gleich vorab: Es handelt sich um einen Näherungswert, da BigQuery die exakten Werte leider nicht zurückgibt. Genau diese Berechnung verwendet auch die BigQuery-UI für ihre Anzeige.

In jedem Datenbanksystem korreliert eine logisch komplexere Query üblicherweise direkt mit der internen Ausführungskomplexität. BigQuery bildet hier keine Ausnahme – und in der Regel besteht zusätzlich ein direkter Zusammenhang zwischen dieser Komplexität und den Query-Kosten. Vereinfacht gesagt: Wer Komplexität reduziert, senkt auch die Kosten.

Die wichtigste Query aus dem GitHub-Repository zur Bestimmung der Query-Komplexität ist top_complex_queries.sql. Sie liefert die Queries zurück, die über ihre Laufzeit die meisten Slots verbrauchen. Wer sich die Spitzenwerte ansieht, findet darin Queries, bei denen Performance-Probleme wahrscheinlich sind.

Das ist keine exakte Wissenschaft, denn manche komplexen Queries müssen genauso bleiben, um korrekt zu funktionieren. Es ist aber ein guter Weg, mögliche Kandidaten zu identifizieren.

Eine weitere Möglichkeit, Performance-Probleme aufzudecken, ist der Blick auf die am längsten laufenden Queries. Wie bereits erwähnt, ist auch das keine exakte Wissenschaft, denn manchmal braucht eine Query einfach ihre Zeit.

Die Query in longest_running_queries.sql liefert alle Queries des Zeitraums sortiert nach absteigender Laufzeit. So landen die langsamsten Queries oben. Die Spalte runtimeToBytesBilledRatio gibt einen guten Hinweis darauf, wie viele Bytes die Query pro Millisekunde verarbeitet. Liegt dieser Wert über 1, sollten Sie sich die Query ansehen und prüfen, ob sich die verarbeitete Datenmenge optimieren lässt.

In einem späteren Teil dieser Serie wird das Thema Performance-Tuning deutlich ausführlicher behandelt. Dort zeige ich einige der häufig übersehenen Stolperfallen der BigQuery-Performance und Wege, sie zu umgehen.

Das letzte Thema dieses Teils ist eine Sammlung von Queries im GitHub-Repository, die Informationen oder Metadaten allgemeinerer Natur liefern als die bisher besprochenen.

Es gibt verschiedene Job-Typen wie Queries, Loads, Extracts und Table Copies. Die mit Abstand häufigsten sind Queries und Loads. Daher liegen im Repository die Dateien load_job_information.sql und query_job_information.sql, die diese Job-Typen samt nützlicher Metadaten zurückgeben.

Es existiert außerdem eine generischere Variante namens general_job_information.sql, die alle Job-Typen mit allgemeinen Metadaten zurückgibt.

Vorab ein wichtiger Hinweis: Google hat im Dezember 2022 das Verhalten von BigQuery in Bezug auf gleichzeitige Queries verändert. Früher galt ein hartes Limit von 100 gleichzeitigen Queries pro Projekt – im Vorgriff auf kommende Änderungen und das Rollout der Query Queues skaliert dieser Wert nun mit der Anzahl der verfügbaren Slots.

Beachten Sie: Zum Zeitpunkt der Erstellung ist dies noch nicht vollständig dokumentiert. Ich habe diese Informationen aus Support-Gesprächen und der vorhandenen Dokumentation zusammengetragen.

Standardmäßig ist dieser Wert auf null gesetzt. Das bedeutet, dass BigQuery die Concurrency dynamisch anhand der dem Projekt zuweisbaren Ressourcen bestimmt. Beim Wechsel zu Flat-Rate-Pricing lässt sich auf der Reservation eine Einstellung namens "maximum concurrency" konfigurieren, mit der eine maximale Concurrency angefordert werden kann. Google kann diesen Wert nicht garantieren, versucht ihn aber zu erreichen. Die offizielle Dokumentation finden Sie auf der Query-Queues-Seite hier.

Mit diesem Disclaimer aus dem Weg geht es nun an die Probleme rund um gleichzeitige Queries und ihre Diagnose.

Wenn Sie viele Queries parallel ausführen, kommt es vor, dass Sie das gefürchtete "503 Service Unavailable" oder die neuere Meldung "Query was not admitted as the maximum concurrency has been reached" erhalten. Das heißt: Ihr Concurrency-Limit ist überschritten und die Queries werden nicht eingeplant. Aus naheliegenden Gründen ist das schlecht.

Selbst wenn Ihre Organisation in einigen Projekten dieses Limit nie erreicht hat, sollten Sie es im Hinterkopf behalten, um es vorbeugend zu vermeiden. Strategien zur Entschärfung oder zur Vermeidung durch entsprechendes Design beleuchte ich in einem späteren Teil dieser Serie, sobald die soeben von Google eingeführten Änderungen vollständig analysiert und verstanden sind.

Um in der Zwischenzeit Ihre durchschnittliche Concurrency pro Minute oder Sekunde zu ermitteln, nutzen Sie die Queries concurrent_queries_by_minute.sql und concurrent_queries_by_seconds.sql. Sie liefern die durchschnittliche Anzahl gleichzeitig laufender Queries pro Minute (bzw. Sekunde) im definierten Intervall.

Häufig wird eine Query mehrfach ausgeführt – entweder durch einen Nutzer oder durch automatisierte Prozesse. Da jede Ausführung Geld kostet, ist es sinnvoll zu wissen, wie oft eine Query läuft, um zu beurteilen, ob sie zu häufig ausgeführt wird und so unnötige Kosten verursacht.

Die Query in query_counts.sql zeigt an, wie oft eine Query innerhalb des Zeitintervalls ausgeführt wurde. Sie ist äußerst hilfreich, um zu erkennen, ob eine bestimmte Query zu häufig läuft und damit zu hohe Kosten verursacht. Zusätzlich werden die Gesamtkosten der Query über das angegebene Intervall ausgewiesen, damit Sie besser beurteilen können, ob Ausführungshäufigkeit und Kosten in einem akzeptablen Rahmen liegen.

Damit sind die ersten drei Teile zum Thema BigQuery-Optimierung abgeschlossen. Im nächsten Abschnitt finden Sie eine Tabelle mit den Inhalten des GitHub-Repositorys, die mit jeder neuen Query aktualisiert wird und die Funktionalität der einzelnen Skripte dokumentiert.

Weitere Teile dieser Serie folgen in Kürze. BigQuery befindet sich gerade in einer Phase größerer Veränderungen – ich werde mir daher genau ansehen, wie Sie Ihre Nutzung im Zuge dieser Rollouts optimal halten.