In einem bearbeiteten Auszug aus unserer Serie zur Optimierung von BigQuery-Kosten und -Performance zeigen wir, wie Sie die kostenintensivsten Abfragen in Ihrer Umgebung identifizieren.
Ein bearbeiteter Auszug aus einer Serie zur Optimierung von BigQuery-Kosten und -Performance
Der erste Teil dieser Serie hat skizziert, was Sie brauchen, um Abfragen in BigQuery auszuführen, und der zweite Teil behandelte die häufigsten Fehler, die Ihnen bei der Optimierung Ihrer BigQuery-Nutzung begegnen. In diesem Artikel zeigen wir, wie Sie die teuersten Abfragen in Ihrer Umgebung identifizieren. Den gesamten in dieser Serie verwendeten Code finden Sie in diesem GitHub-Repository.
Insgesamt teuerste Abfragen
Die erste Abfrage, mit der Sie sich befassen sollten, finden Sie in der Datei top_costly_queries.sql. In Sachen Kosten ist das wohl die wichtigste Abfrage überhaupt: Sie umfasst die Gesamtkosten, die eine Abfrage im betrachteten Zeitraum über alle Ausführungen hinweg verursacht. Sie verfolgt zwei Ziele: zum einen die insgesamt teuerste Abfrage Ihrer Umgebung zu ermitteln, zum anderen festzustellen, ob eine teure Abfrage häufiger ausgeführt wird als nötig.
Teuerste Einzelabfragen
Die zweite Abfrage für diesen Abschnitt finden Sie in der Datei top_billed_queries.sql. Sie listet die teuersten Abfragen Ihrer Umgebung absteigend nach Kosten sortiert auf. Anders als die erste Abfrage berücksichtigt sie nicht, wie oft eine Abfrage ausgeführt wurde – sie zeigt schlicht die Kosten pro Ausführung.
Sobald Sie diese Abfrage ausführen, schießen die teuersten Abfragen sofort an die Spitze des Ergebnissatzes. Suchen Sie sich die oberste Abfrage heraus und prüfen Sie zunächst, wie viele Daten sie verarbeitet hat (die Spalten total*Billed). Werfen Sie dann mit diesem Wert im Hinterkopf einen Blick auf die Abfrage selbst, um herauszufinden, was sie so teuer macht.
Hinweis zu doppelten Abfragen
Falls dieselbe Abfrage mehrfach in den Ergebnissen auftaucht und diese dadurch verzerrt oder unübersichtlich macht, schauen Sie stattdessen in die Datei top_billed_queries_deduplicated.sql. Es ist dieselbe Abfrage, allerdings mit Deduplizierung der exakt ausgeführten Abfrage, sodass jedes Ergebnis nur einmal erscheint. Die Ausführung dauert länger und scannt mehr Daten, liefert dafür aber deduplizierte Ergebnisse.
BigQuery bietet derzeit kein natives Aggregat für "String Similarity" – etwa Hamming- oder Levenshtein-Distanz –, weshalb sich ähnliche Abfragen mit kleinen Abweichungen wie etwa einem Datum nur schwer herausfiltern lassen. Bei Bedarf finden Sie einige UDF-Lösungen, die solche "String Similarity"-Algorithmen für BigQuery umsetzen. Eine Lösung außerhalb von BigQuery ist allerdings vorzuziehen, da UDF-Implementierungen rechenintensiver Algorithmen dieser Art recht langsam sind.
Teuerste Nutzer
Eine dritte Abfrage zur Kostenanalyse ist die Datei top_cost_users.sql. Sie listet die teuersten Abfragen Ihrer Umgebung auf – sortiert nach Nutzer und anschließend nach der teuersten Abfrage.
Diese Abfrage zeigt, welche Nutzer oder Service-Accounts das meiste Geld ausgeben und wofür. Häufig stehen auf dieser Liste auch Prozesse mit ineffizienten Abfragen, von denen Sie womöglich gar nichts wissen. In Extremfällen liegen diese Prozesse in anderen Projekten oder Clouds. Es kann sinnvoll sein, zusätzliche Filter zu setzen, um sie auszublenden.
Weitere Abfragen im Repository
Im GitHub-Repo finden Sie zudem weitere Abfragen für speziellere Zwecke – etwa zum Auffinden von Abfragen, die aus Looker stammen, zur Anzahl der Ausführungen einer Abfrage oder zu den Kosten von Abfragen mit bestimmten Labels. Sie sind sehr spezifisch, aber genau die, die wir bei DoiT selbst nutzen – und deshalb mit der Community teilen.
Performance-Probleme bei Abfragen aufspüren
Das nächste große Thema ist das Aufspüren von Abfragen, die mehr Ressourcen verbrauchen als nötig und nicht wie erwartet performen. Diese Abfragen gehören häufig auch zu den teuersten – Überschneidungen sind also möglich.
In diesem Abschnitt definieren wir den Begriff "Komplexität" als die Anzahl der Slots, die eine Abfrage während ihrer Ausführung nutzt. Dieser Wert ergibt sich aus der gesamten Slot-Zeit für die Bearbeitung geteilt durch die Gesamtausführungszeit der Abfrage. Hier ein Beispiel, wie das für die View JOBS_BY_PROJECT aussieht:
SELECTSAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCountFROM`<project-name>`.`<dataset-region>`.INFORMATION_SCHEMA.JOBS_BY_PROJECTEs handelt sich um eine Näherung, da BigQuery die exakten Werte leider nicht zurückgibt – und genau diese Berechnung verwendet auch die BigQuery-UI für ihre Anzeige.
Komplexität und Slot-Anzahl
In jedem Datenbanksystem korreliert eine wachsende logische Komplexität einer Abfrage in der Regel direkt mit der Komplexität ihrer internen Ausführung. BigQuery bildet hier keine Ausnahme. Hinzu kommt: Es besteht meist eine direkte Korrelation zwischen dieser Komplexität und den Abfragekosten. Wer also Komplexität reduziert, reduziert in der Regel auch die Kosten.
Die zentrale Abfrage aus dem GitHub-Repository zur Bestimmung der Komplexität ist top_complex_queries.sql. Sie liefert die Abfragen, die über ihre Laufzeit die meisten Slots beanspruchen – die obersten Treffer helfen, Abfragen mit potenziellen Performance-Problemen zu identifizieren. Eine exakte Wissenschaft ist das nicht, denn komplexe Abfragen müssen oft komplex bleiben, um korrekt zu funktionieren – aber es ist ein Weg, um problematische Kandidaten aufzuspüren.
Lang laufende Abfragen
Eine weitere Möglichkeit, Performance-Probleme aufzuspüren, ist der Blick auf die am längsten laufenden Abfragen. Allerdings dauern viele Abfragen einfach deshalb lange, weil es nicht anders geht.
Die Abfrage in longest_running_queries.sql liefert alle Abfragen im betrachteten Zeitraum, sortiert von der längsten zur kürzesten Laufzeit. Dadurch landen die zeitintensivsten Abfragen ganz oben und können näher untersucht werden. Die Spalte runtimeToBytesBilledRatio gibt einen guten Hinweis darauf, wie viele Bytes die Abfrage pro Millisekunde verarbeitet hat. Liegt dieser Wert über 1, lohnt sich ein genauerer Blick, ob sich etwas optimieren lässt, um die verarbeitete Datenmenge zu reduzieren.
Ein Hinweis zu Performance-Problemen
In einem späteren Teil dieser Serie gehe ich detailliert auf Performance-Tuning ein und zeige einige der häufigsten, kaum bekannten Stolperfallen rund um die BigQuery-Performance sowie Methoden, sie zu umgehen.
Allgemeine Abfragen aus dem Repository
Das letzte Thema dieses Serienteils ist eine Reihe von Abfragen im GitHub-Repository, die allgemeinere Informationen oder Metadaten liefern als die bisher behandelten.
Abfragen nach Job-Typ
Mit Abstand am häufigsten unter den verschiedenen Job-Typen (Queries, Loads, Extracts und Table Copies) sind Queries und Loads. Die Dateien load_job_information.sql und query_job_information.sql im Repository liefern genau diese Job-Typen samt nützlicher Metadaten. Eine generischere Variante davon, general_job_information.sql, gibt sämtliche Job-Typen mit allgemeinen Metadaten zurück.
Gleichzeitige Abfragen
Im Dezember 2022 hat Google das Verhalten von BigQuery in Bezug auf gleichzeitige Abfragen geändert. Im Vorfeld anstehender Änderungen und des Rollouts der Query-Queues wurde das harte Limit von 100 gleichzeitigen Abfragen pro Projekt aufgehoben; es skaliert nun mit der Zahl der verfügbaren Slots. Zum Zeitpunkt der Erstellung war das nicht vollständig dokumentiert; die Informationen stammen aus dem Support und der bestehenden Dokumentation.
Standardmäßig steht dieser Wert auf null, was bedeutet, dass BigQuery die Concurrency dynamisch anhand der verfügbaren, dem Projekt zuweisbaren Ressourcen ermittelt. Beim Wechsel auf Flat-Rate-Pricing lässt sich auf der Reservierung eine Einstellung namens "maximum concurrency" setzen, um diesen Wert als Concurrency-Obergrenze anzufordern. Google kann nicht garantieren, dass dieser Wert eingehalten wird, bemüht sich aber darum. Die offizielle Dokumentation finden Sie auf der Seite zu Query-Queues hier. Mit diesem Hinweis aus dem Weg widmen wir uns nun den Problemen rund um gleichzeitige Abfragen.
Wer zu viele Abfragen parallel ausführt, kassiert die gefürchtete 503-Meldung "Service Unavailable" oder die neuere Variante "Query was not admitted as the maximum concurrency has been reached." Das bedeutet: Ihr Concurrency-Limit ist überschritten und die Abfragen werden nicht eingeplant. Aus naheliegenden Gründen sollten Sie das vermeiden.
Sobald die neuen Änderungen von Google vollständig analysiert und verstanden sind, werden wir Strategien vorstellen, wie sich das abmildern oder durch passendes Design ganz vermeiden lässt. In der Zwischenzeit können Sie mit den Abfragen concurrent_queries_by_minute.sql und concurrent_queries_by_seconds.sql Ihre durchschnittliche Concurrency pro Minute beziehungsweise Sekunde ermitteln. Sie liefern die durchschnittliche Anzahl gleichzeitig laufender Abfragen pro Minute (oder Sekunde) im definierten Zeitraum.
Anzahl der Abfragen
Abfragen werden häufig mehrfach ausgeführt – durch Nutzer oder durch automatisierte Prozesse. Die Abfrage query_counts.sql zeigt, wie oft eine Abfrage innerhalb des Zeitraums ausgeführt wurde. Das ist äußerst nützlich, um festzustellen, ob eine bestimmte Abfrage häufiger läuft als nötig und damit Geld verbrennt. Auch die Gesamtkosten der Abfrage über den definierten Zeitraum sind enthalten.
So geht es weiter
Dieser Beitrag ist eine verdichtete Fassung meiner Artikelserie zur Optimierung Ihrer BigQuery-Abfragen, die mit jeder neuen Änderung in BigQuery erweitert wird. Als bestehender DoiT-Kunde können Sie all diese Schritte direkt über die BQ-Lens-Funktion in DoiT Cloud Intelligence™ durchführen. Bis dahin können Sie sich gerne an DoiT wenden und von unserer fundierten und breit aufgestellten Expertise in BigQuery sowie in Machine Learning und Business Intelligence profitieren.