Im vergangenen Dezember erhielt ich einen interessanten Fehlerbericht vom VWO-Supportteam. Die Ladezeit für einen der Analyseberichte für einen großen Unternehmenskunden schien unerschwinglich. Und da dies mein Verantwortungsbereich ist, habe ich mich sofort auf die Lösung des Problems konzentriert.
Hintergrund
Um klar zu machen, wovon ich spreche, erzähle ich Ihnen ein wenig über VWO. Dies ist eine Plattform, mit der Sie verschiedene gezielte Kampagnen auf Ihren Websites durchführen können: A / B-Experimente durchführen, Besucher und Conversions verfolgen, Verkaufstrichter analysieren, Heatmaps anzeigen und Aufzeichnungen von Besuchen abspielen.
Das Wichtigste auf der Plattform ist jedoch die Berichterstattung. Alle oben genannten Funktionen sind miteinander verbunden. Und für Firmenkunden wäre eine Vielzahl von Informationen ohne eine leistungsstarke Plattform, die sie in Form von Analysen präsentiert, einfach nutzlos.
Über die Plattform können Sie eine beliebige Anforderung für einen großen Datensatz stellen. Hier ist ein einfaches Beispiel:
Zeige alle Klicks auf abc.com
VON <Datum d1> BIS <Datum d2>
für Menschen, die
verwendet Chrome ODER
(waren in Europa und benutzten das iPhone)
Achten Sie auf boolesche Operatoren. Sie stehen Clients in der Abfrageoberfläche zur Verfügung, um beliebig komplexe Abfragen zum Abrufen von Beispielen durchzuführen.
Langsame Anfrage
Der betreffende Kunde hat versucht, etwas zu tun, das intuitiv schnell funktionieren sollte:
Alle Sitzungsnotizen anzeigen
für Benutzer, die eine Seite besuchen
mit URL wo es "/ jobs" gibt
Es gab viel Verkehr auf dieser Site und wir haben über eine Million eindeutiger URLs nur dafür gespeichert. Und sie wollten eine ziemlich einfache URL-Vorlage finden, die mit ihrem Geschäftsmodell zusammenhängt.
Voruntersuchung
Mal sehen, was in der Datenbank passiert. Das Folgende 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 ;
Und hier sind die Zeiten:
Geplante Zeit: 1.480 ms
Vorlaufzeit: 1431924.650 ms
Die Anfrage umging 150.000 Zeilen. Der Abfrageplaner zeigte einige interessante Details, aber keine offensichtlichen Engpässe.
Lassen Sie uns die Abfrage weiter untersuchen. Wie Sie sehen können, werden drei Tabellen JOIN :
- Sitzungen : Zum Anzeigen von Sitzungsinformationen: Browser, Benutzeragent, Land usw.
- record_data: aufgezeichnete URLs, Seiten, Dauer der Besuche
- URLs : Um das Duplizieren extrem großer URLs zu vermeiden, speichern wir sie in einer separaten Tabelle.
Beachten Sie auch, dass alle unsere Tabellen bereits nach account_id . Somit ist eine Situation ausgeschlossen, wenn aufgrund eines besonders großen Kontos die anderen Probleme haben.
Nach Beweisen suchen
Bei näherer Betrachtung stellen wir fest, dass etwas in einer bestimmten Anfrage nicht stimmt. Ein Blick auf diese Zeile lohnt sich:
urls && array( select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%' )::text[]
Der erste Gedanke war, dass möglicherweise aufgrund von ILIKE in all diesen langen URLs (wir haben mehr als 1,4 Millionen eindeutige URLs für dieses Konto gesammelt) die Leistung nachlassen könnte.
Aber nein - das ist nicht der Punkt!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%'; id -------- ... (198661 rows) Time: 5231.765 ms
Die Anforderung der Vorlagensuche selbst dauert nur 5 Sekunden. Die Suche nach einem Muster für eine Million eindeutiger URLs ist eindeutig kein Problem.
Der nächste Verdächtige auf der Liste sind einige JOIN . Vielleicht führte ihre Überbeanspruchung zu einer Verlangsamung? JOIN normalerweise die offensichtlichsten Kandidaten für Leistungsprobleme, aber ich glaubte nicht, dass unser Fall typisch war.
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 < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count ------- 8086 (1 row) Time: 147.851 ms
Und das war auch nicht unser Fall. JOIN sich als ziemlich schnell herausgestellt.
Wir schränken den Kreis der Verdächtigen ein
Ich war bereit, die Abfrage zu ändern, um mögliche Leistungsverbesserungen zu erzielen. Mein Team und ich haben zwei Hauptideen entwickelt:
- Verwenden Sie EXISTS für die URL der Unterabfrage : Wir wollten erneut prüfen, ob Probleme mit der Unterabfrage für URLs aufgetreten sind. Eine Möglichkeit, dies zu erreichen, besteht darin, einfach
EXISTS . EXISTS kann die Leistung erheblich verbessern, da es sofort endet, sobald es eine einzelne Zeile nach Bedingung findet.
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 < to_timestamp(1549177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 ; count 32519 (1 row) Time: 1636.637 ms
Na ja. Die in EXISTS verpackte EXISTS macht alles superschnell. Die nächste logische Frage ist, warum die Abfrage mit JOINs und die Unterabfrage selbst einzeln schnell, aber zusammen furchtbar langsam sind.
- Wir verschieben die Unterabfrage in den CTE : Wenn die Anforderung von sich aus schnell ist, können wir einfach zuerst das schnelle Ergebnis berechnen und es dann der Hauptanforderung zur Verfügung stellen
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 < to_timestamp(1545107599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0;
Aber es war immer noch sehr langsam.
Finde den Täter
Während dieser ganzen Zeit blitzte ein kleines Ding vor meinen Augen auf, von dem ich ständig beiseite wischte. Aber da nichts mehr übrig war, beschloss ich, sie anzusehen. Ich spreche über den && Operator. Während EXISTS lediglich die Leistung verbesserte, war && der einzige verbleibende gemeinsame Faktor in allen Versionen der langsamen Abfrage.
In der Dokumentation sehen wir, dass && , wenn Sie gemeinsame Elemente zwischen zwei Arrays suchen müssen.
In der ursprünglichen Anfrage lautet dies:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
Das heißt, wir führen eine Vorlagensuche nach unseren URLs durch und finden dann den Schnittpunkt mit allen URLs mit gemeinsam genutzten Datensätzen. Dies ist etwas verwirrend, da sich "URLs" hier nicht auf eine Tabelle bezieht, die alle URLs enthält, sondern auf eine Spalte "URLs" in der Tabelle "ording_data ".
Als der Verdacht auf && , versuchte ich, eine Bestätigung in dem von EXPLAIN ANALYZE generierten EXPLAIN ANALYZE (ich hatte bereits einen gespeicherten Plan, aber es ist normalerweise bequemer, mit SQL zu experimentieren, als zu versuchen, die Deckkraft von Abfrageplanern zu verstehen).
Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0)) Rows Removed by Filter: 52710
Es gab nur ein paar Filterzeilen von && . Dies bedeutete, dass diese Operation nicht nur teuer war, sondern auch mehrmals durchgeführt wurde.
Ich habe dies überprüft, indem ich den Zustand 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 Anfrage war langsam. Da JOIN schnell und Unterabfragen schnell sind, bleibt nur der Operator && übrig.
Dies ist nur eine Schlüsseloperation. Wir müssen immer in der gesamten Haupttabelle der URLs suchen, um nach Mustern zu suchen, und wir müssen immer Schnittpunkte finden. Wir können URL-Einträge nicht direkt durchsuchen, da dies nur Bezeichner sind, die auf urls .
Auf dem Weg zu einer Lösung
&& langsam, weil beide Sets riesig sind. Der Vorgang ist relativ schnell, wenn ich urls durch { "http://google.com/", "http://wingify.com/" } urls .
Ich suchte nach einer Möglichkeit, Sätze in Postgres zu schneiden, ohne && , aber ohne großen Erfolg.
Am Ende haben wir beschlossen, das Problem einfach isoliert zu lösen: Geben Sie mir alle urls Zeichenfolge, für die die URL dem Muster entspricht. Ohne zusätzliche Bedingungen wird es 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 AND urls.url ILIKE '%jobs%'
Anstelle der JOIN Syntax habe ich nur eine Unterabfrage verwendet und das Array recording_data.urls erweitert, sodass die Bedingung direkt auf WHERE angewendet werden kann.
Das Wichtigste dabei ist, dass mit && überprüft wird, ob ein bestimmter Eintrag eine geeignete URL enthält. Wenn Sie ein wenig blinzeln, können Sie in dieser Operation sehen, wie Sie sich durch die Elemente des Arrays (oder der Zeilen der Tabelle) bewegen und anhalten, wenn die Bedingung (Übereinstimmung) erfüllt ist. Ähnelt nichts? Ja, EXISTS .
Da auf record_data.urls in diesem Fall von außerhalb des Kontexts der Unterabfrage verwiesen werden kann, können wir zu unserem alten Freund EXISTS und ihn mit einer Unterabfrage umschließen.
Wenn wir alles miteinander kombinieren, erhalten wir die endgültige optimierte 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 ( 1 = 1 ) AND sessions.referrer_id = recordings_urls.id AND r_time > to_timestamp(1542585600) AND r_time < to_timestamp(1545177599) AND recording_data.duration >=5 AND recording_data.num_of_pages > 0 AND EXISTS( SELECT urls.url FROM acc_{account_id}.urls as urls, (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) AS unrolled_urls WHERE urls.id = unrolled_urls.rec_url_id AND urls.url ILIKE '%enterprise_customer.com/jobs%' );
Und die letzte Laufzeit Time: 1898.717 ms Es ist Zeit zu feiern?!?
Nicht so schnell! Zuerst müssen Sie die Richtigkeit überprüfen. Ich war der EXISTS Optimierung äußerst misstrauisch, da sie die Logik auf ein früheres Ende ändert. Wir müssen sicher sein, dass wir der Anfrage keinen nicht offensichtlichen Fehler hinzugefügt haben.
Eine einfache Überprüfung bestand darin, sowohl langsame als auch schnelle Abfragen für eine große Anzahl verschiedener Datensätze zu count(*) . Dann habe ich für eine kleine Teilmenge der Daten alle Ergebnisse manuell überprüft.
Alle Kontrollen ergaben durchweg positive Ergebnisse. Wir haben es behoben!
Lektionen gelernt
Aus dieser Geschichte können viele Lehren gezogen werden:
- Abfragepläne erzählen nicht die ganze Geschichte, können aber Hinweise geben
- Die Hauptverdächtigen sind nicht immer die wirklichen Schuldigen
- Langsame Abfragen können unterbrochen werden, um Engpässe zu isolieren
- Nicht alle Optimierungen sind reduktiver Natur
- Wenn möglich, kann die Verwendung von
EXIST zu einer starken Steigerung der Produktivität führen.
Fazit
Wir sind von einer Anforderungszeit von ~ 24 Minuten auf 2 Sekunden gegangen - eine sehr ernsthafte Leistungssteigerung! Obwohl sich dieser Artikel als umfangreich herausstellte, wurden alle Experimente, die wir durchgeführt haben, am selben Tag durchgeführt. Schätzungen zufolge dauerte die Optimierung und Prüfung 1,5 bis 2 Stunden.
SQL ist eine wunderbare Sprache, wenn Sie keine Angst davor haben, aber versuchen Sie zu lernen und zu verwenden. Wenn Sie genau wissen, wie SQL-Abfragen ausgeführt werden, wie die Datenbank Abfragepläne generiert, wie Indizes funktionieren und wie groß die Daten sind, mit denen Sie sich befassen, können Sie die Abfrageoptimierung sehr erfolgreich durchführen. Es ist jedoch ebenso wichtig, weiterhin verschiedene Ansätze auszuprobieren und das Problem langsam zu lösen, um Engpässe zu finden.
Das Beste daran, solche Ergebnisse zu erzielen, ist eine spürbare Verbesserung der Geschwindigkeit - wenn ein Bericht, der zuvor noch nicht einmal heruntergeladen wurde, jetzt fast sofort geladen wird.
Besonderer Dank geht an meine Teamkollegen Aditya Misra , Aditya Gauru und Varun Malhotra für das Brainstorming und an Dinkar Pandir für das Auffinden eines wichtigen Fehlers in unserer letzten Anfrage, bevor wir uns endlich von ihm verabschiedeten!