Die Geschichte einer SQL-Ermittlung

Im Dezember letzten Jahres erhielt ich einen interessanten Fehlerbericht vom VWO-Supportteam. Die Ladezeit eines der Analytics-Berichte für einen großen Unternehmenskunden schien übermäßig lang zu sein. Da dies in meinem Verantwortungsbereich liegt, konzentrierte ich mich sofort auf die Lösung des Problems.

Vorgeschichte

Um zu erklären, worum es geht, möchte ich ein wenig über VWO erzählen. Es ist eine Plattform, die es ermöglicht, verschiedene zielgerichtete Kampagnen auf Ihren Websites zu starten: A/B-Tests durchzuführen, Besucher und Conversions zu verfolgen, die Verkaufstrichteranalyse zu erstellen, Heatmaps anzuzeigen und Besuchsaufzeichnungen abzuspielen.

Das Wichtigste an der Plattform ist jedoch die Erstellung von Berichten. Alle oben genannten Funktionen sind miteinander verknüpft. Und für Unternehmenskunden wäre ein riesiger Datenpool ohne eine leistungsstarke Plattform, die diese Daten für die Analyse darstellt, einfach nutzlos.

Mit der Plattform können Sie beliebige Abfragen auf großen Datensätzen durchführen. Hier ist ein einfaches Beispiel:

Zeige alle Klicks auf der Seite "abc.com" 
VON <Datum d1> BIS <Datum d2> 
für Personen, die 
Chrome benutzt haben ODER 
(in Europa waren UND iPhone benutzt haben)

Beachten Sie die booleschen Operatoren. Diese sind im Abfrageinterface für Kunden verfügbar, um beliebig komplexe Abfragen zur Datenextraktion zu erstellen.

Langsame Abfrage

Der betreffende Kunde versuchte, etwas zu tun, das intuitiv schnell funktionieren sollte:

Zeige alle Sitzungsaufzeichnungen 
für Nutzer, die eine beliebige Seite mit einer URL besucht haben, die "\/jobs" enthält

Auf dieser Website war ein enormes Verkehrsaufkommen, und wir speicherten mehr als eine Million einzigartiger URLs nur für sie. Sie wollten ein ziemlich einfaches URL-Muster finden, das zu ihrem Geschäftsmodell gehörte.

Vorläufige Untersuchung

Lassen Sie uns sehen, was in der Datenbank passiert. Hier ist die ursprüngliche langsame SQL-Abfrage:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls && array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

Hier sind die Zeitangaben:

Geplante Zeit: 1.480 ms
Ausführungszeit: 1431924.650 ms

Die Anfrage umfasste 150.000 Zeilen. Der Abfrageplaner zeigte ein paar interessante Details, aber keine offensichtlichen Engpässe.

Lassen Sie uns die Anfrage weiter untersuchen. Wie zu sehen ist, macht sie JOIN drei Tabellen:

1. sessions: zur Anzeige von Sitzungsinformationen: Browser, Benutzer-Agent, Land und so weiter.
2. recording_data: aufgezeichnete URLs, Seiten, Dauer der Besuche
3. urls: um die doppelte Speicherung extrem langer URLs zu vermeiden, speichern wir sie in einer separaten Tabelle.

Bitte beachten Sie auch, dass alle unsere Tabellen bereits nach account_idgetrennt sind. So wird ausgeschlossen, dass aufgrund eines besonders großen Kontos Probleme bei anderen auftreten.

Auf der Suche nach Hinweisen

Bei näherer Betrachtung sehen wir, dass etwas an dieser speziellen Anfrage nicht stimmt. Es ist notwendig, sich diese Zeile genauer anzusehen:

urls && array(
	select id from acc_{account_id}.urls 
	where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]

Der erste Gedanke war, dass möglicherweise wegen ILIKE bei all diesen langen URLs (wir haben über 1,4 Millionen einzigartiger URLs, die für dieses Konto gesammelt wurden) die Leistung beeinträchtigt sein könnte.

Aber nein - das ist nicht der Fall!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Zeit: 5231.765 ms

Die tatsächliche Abfrage nach dem Muster dauert nur 5 Sekunden. Die Musterabfrage bei einer Million einzigartiger URLs ist offensichtlich kein Problem.

Der nächste Verdächtige auf der Liste sind einige JOIN. Möglicherweise hat ihr übermäßiger Gebrauch zu einer Verlangsamung geführt? In der Regel JOINsind sie die offensichtlichsten Kandidaten für Leistungsprobleme, aber ich glaubte nicht, dass unser Fall typisch ist.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time = 5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Zeit: 147.851 ms

Und das war ebenfalls nicht unser Fall. JOINSie erwiesen sich als recht schnell.

Wir verengen den Kreis der Verdächtigen

Ich war bereit, die Abfrage zu ändern, um mögliche Leistungsverbesserungen zu erreichen. Mein Team und ich entwickelten 2 Hauptideen:

• EXISTS für die URL-Unterabfrage verwenden: Wir wollten noch einmal überprüfen, ob es Probleme mit der Unterabfrage für die URLs gibt. Eine Möglichkeit, dies zu erreichen, besteht darin, einfach zu verwenden EXISTS. EXISTS plötzliche Fehler verarbeiten und nützliche Details zur Fehlersuche anzeigen, wie z. B. einen Stack-Trace. Die Fehlermeldung wurde vereinfacht – es reicht aus, auf den Link zu klicken. Die Leistung erheblich verbessern, da sie sofort endet, sobald die einzige Zeile mit der Bedingung gefunden wird.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time = 5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 Zeile)
Zeit: 1636.637 ms

Ja. Eine Unterabfrage, die in EXISTS, macht alles super schnell. Die nächste logische Frage ist, warum die Abfrage mit JOIN-en und die Unterabfrage für sich genommen schnell sind, aber zusammen schrecklich langsam sind?

• Wir verschieben die Unterabfrage in CTE : wenn die Abfrage schnell für sich ist, können wir einfach zuerst das schnelle Ergebnis berechnen und es dann der Hauptabfrage bereitstellen.

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time = 5 
    AND recording_data.num_of_pages > 0;

Aber das war immer noch sehr langsam.

Wir finden den Übeltäter.

Während all dieser Zeit hat ein kleines Detail immer wieder in meinem Kopf geschwebt, von dem ich ständig abgelenkt wurde. Aber da nichts mehr übrig war, beschloss ich, es mir auch anzusehen. Ich spreche von && Operator. Während EXISTS ich einfach die Leistung verbessert habe, && war der einzige verbleibende gemeinsame Faktor in allen Versionen der langsamen Abfrage.

Wenn wir uns Dokumentationansehen, sehen wir, dass && verwendet wird, wenn es darum geht, gemeinsame Elemente zwischen zwei Arrays zu finden.

In der Originalabfrage ist es:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Was bedeutet, dass wir eine Musterabgleichung auf unsere URLs durchführen, dann das Schnittmuster mit allen URLs in den gemeinsamen Aufzeichnungen finden. Das ist ein bisschen verwirrend, da "urls" hier nicht auf die Tabelle verweist, die alle URLs enthält, sondern auf die Spalte "urls" in der Tabelle. recording_data.

Mit wachsendem Verdacht in Bezug auf &&, habe ich versucht, diese im generierten Abfrageplan zu bestätigen. EXPLAIN ANALYZE (Ich hatte bereits einen gespeicherten Plan, aber es ist mir normalerweise lieber, in SQL zu experimentieren, als die Intransparenzen von Abfrageplanern zu verstehen).

Filter: ((urls && ($0)::text[]) UND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) UND (r_time = '5'::double precision) UND (num_of_pages > 0))
                           Vom Filter entfernte Zeilen: 52710

Es gab einige Filterzeilen nur aus &&. Das bedeutete, dass diese Operation nicht nur teuer war, sondern auch mehrmals ausgeführt wurde.

Ich habe das überprüft, indem ich die Bedingung isoliert habe

SELECT 1
FROM 
    acc_{account_id}.urls AS recordings_urls, 
    acc_{account_id}.recording_data_30 AS recording_data_30, 
    acc_{account_id}.sessions_30 AS sessions_30 
WHERE 
	urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[]

Diese Abfrage wurde langsam ausgeführt. Da JOIN-s schnell sind und Unterabfragen schnell sind, blieb nur der && Operator übrig.

Das ist nur der kritische Vorgang. Wir müssen immer in der gesamten Haupttabelle der URLs suchen, um im Muster zu suchen, und wir müssen immer die Überschneidungen finden. Wir können nicht direkt in den URL-Einträgen suchen, da dies nur IDs sind, die auf urls.

Auf dem Weg zur Lösung

&& langsam ist, da beide Mengen groß sind. Der Vorgang wird relativ schnell sein, wenn ich urls auf { "http://google.com/", "http://wingify.com/" }.

Ich begann nach einer Möglichkeit zu suchen, in Postgres Mengen zu schneiden, ohne zu &&, aber ohne besonderen Erfolg.

Schließlich entschieden wir uns, das Problem isoliert zu lösen: Gib mir alle urls Zeilen, für die die URL dem Muster entspricht. Ohne zusätzliche Bedingungen wird dies sein — 

SELECT urls.url
FROM 
	acc_{account_id}.urls AS urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id UND
	urls.url ILIKE '%jobs%'

Statt JOIN syntaxisch habe ich einfach eine Unterabfrage verwendet und entrollte recording_data.urls das Array, sodass die Bedingung direkt in WHERE.

Das Wichtigste hier ist, dass && verwendet wird, um zu überprüfen, ob dieser Eintrag die entsprechende URL enthält. Wenn man ein wenig blinzelt, kann man in diesem Vorgang das Durchlaufen der Array-Elemente (oder Tabellenzeilen) sehen und das Stoppen, wenn die Bedingung (Übereinstimmung) erfüllt ist. Kommt dir das bekannt vor? Ja, EXISTS.

Da auf recording_data.urls von außerhalb des Kontexts der Unterabfrage verwiesen werden kann, wenn dies passiert, können wir zu unserem alten Freund EXISTS zurückkehren und ihn um die Unterabfrage wickeln.

Wenn wir alles zusammenfassen, erhalten wir die endgültige optimierte Abfrage:

WÄHLEN Sie 
    count(*) 
VON 
    acc_{account_id}.urls als recordings_urls, 
    acc_{account_id}.recording_data als recording_data, 
    acc_{account_id}.sessions als sessions 
WO 
    recording_data.usp_id = sessions.usp_id 
    UND  (  1 = 1  )  
    UND sessions.referrer_id = recordings_urls.id 
    UND r_time > to_timestamp(1542585600) 
    UND r_time =5 
    UND recording_data.num_of_pages > 0
    UND EXISTS(
        WÄHLEN urls.url
        VON 
            acc_{account_id}.urls als urls,
            (WÄHLEN unnest(urls) ALS rec_url_id VON acc_{account_id}.recording_data) 
            ALS unrolled_urls
        WO
            urls.id = unrolled_urls.rec_url_id UND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Und die endgültige Ausführungszeit Zeit: 1898.717 ms Zeit zum Feiern?!?

Nicht so schnell! Zunächst müssen wir die Richtigkeit überprüfen. Ich war extrem misstrauisch gegenüber EXISTS der Optimierung, da sie die Logik für ein früheres Ende ändert. Wir müssen sicher sein, dass wir keinen offensichtlichen Fehler in der Anfrage hinzugefügt haben.

Eine einfache Überprüfung bestand darin, count(*) auszuführen und das sowohl für langsame als auch für schnelle Anfragen bei einer Vielzahl unterschiedlicher Datensätze. Dann habe ich für eine kleine Teilmenge von Daten die Richtigkeit aller Ergebnisse manuell überprüft. Alle Überprüfungen ergaben konstant positive Ergebnisse. Wir haben alles repariert! Abgeleitete Lektionen

Aus dieser Geschichte lassen sich einige Lektionen ziehen:

Abfragepläne erzählen nicht die ganze Geschichte, können aber Hinweise geben

Die Hauptverdächtigen sind nicht immer die tatsächlichen Schuldigen

1. Langsame Anfragen können auseinandergezogen werden, um Engpässe zu isolieren
2. Nicht alle Optimierungen sind von Natur aus reduktiv
3. EXIST
4. , wo möglich, kann zu einem drastischen Anstieg der Leistung führen
5. Verwendung Wir haben die Abfragezeit von ~24 Minuten auf 2 Sekunden reduziert – ein erheblicher Leistungszuwachs! Obwohl dieser Artikel lang geworden ist, fanden alle Experimente, die wir durchgeführt haben, an einem Tag statt und dauerten schätzungsweise 1,5 bis 2 Stunden für Optimierungen und Tests.SQL ist eine wunderbare Sprache, wenn man keine Angst davor hat, sondern versucht, sie zu verstehen und zu nutzen. Mit einem guten Verständnis dafür, wie SQL-Abfragen ausgeführt werden, wie die Datenbank Abfragepläne generiert, wie Indizes funktionieren und einfach der Größe der Daten, mit denen man es zu tun hat, kann man im Hinblick auf das Optimieren von Abfragen sehr erfolgreich sein. Ebenso wichtig ist es jedoch, verschiedene Ansätze weiter auszuprobieren und das Problem langsam zu zerlegen, um Engpässe zu finden.

Ausgabe

Wir haben die Anfragezeit von etwa 24 Minuten auf 2 Sekunden reduziert – ein erheblicher Leistungsanstieg! Obwohl dieser Artikel lang ist, fanden alle Experimente, die wir durchgeführt haben, an einem Tag statt und dauerten insgesamt schätzungsweise 1,5 bis 2 Stunden für Optimierungen und Tests.

SQL ist eine wunderbare Sprache, wenn man keine Angst davor hat, sondern versucht, sie zu verstehen und zu nutzen. Wenn Sie gut nachvollziehen können, wie SQL-Abfragen ausgeführt werden, wie die Datenbank Abfragepläne generiert, wie Indizes funktionieren und einfach auch die Datenmenge, mit der Sie es zu tun haben, werden Sie bei der Optimierung von Abfragen sehr erfolgreich sein. Ebenso wichtig ist es jedoch, verschiedene Ansätze auszuprobieren und das Problem langsam zu zerlegen, um Engpässe zu finden.

Der beste Teil beim Erreichen solcher Ergebnisse ist die bemerkbare Verbesserung der Arbeitsgeschwindigkeit – als der Bericht, der zuvor nicht einmal geladen werden konnte, jetzt fast sofort geladen wird.

Besonderer Dank meinen Kollegen im Team von Aditiya Mishra, Aditiya Gaur und Varun Malhotra für das Brainstorming und Dinkar Pandir dafür, dass er einen wichtigen Fehler in unserer finalen Anfrage gefunden hat, bevor wir uns endgültig davon verabschiedet haben!

Quelle: habr.com