Im Dezember letzten Jahres erhielt ich einen interessanten Fehlerbericht vom Support-Team von VWO. Die Ladezeit eines der Analyseberichte für einen großen Unternehmenskunden erschien übermäßig lang. Da dies in meinem Verantwortungsbereich liegt, konzentrierte ich mich sofort darauf, das Problem zu lösen.
Hintergrund
Um zu verdeutlichen, worum es geht, möchte ich kurz etwas über VWO erzählen. Dies ist eine Plattform, mit der man verschiedene zielgerichtete Kampagnen auf seinen Websites durchführen kann: A/B-Tests, Besucher- und Konversionsverfolgung, Analyse des Verkaufstrichters, Anzeige von Heatmaps und Wiedergabe von Besuchsaufzeichnungen.
Das Wichtigste an der Plattform ist jedoch die Erstellung von Berichten. Alle genannten Funktionen hängen miteinander zusammen. Für Unternehmenskunden wäre eine große Menge an Informationen ohne eine leistungsstarke Plattform, die diese für die Analyse darstellt, einfach nutzlos.
Mit der Plattform kann man beliebige Anfragen auf großen Datensätzen stellen. Hier ist ein einfaches Beispiel:
Zeigen Sie alle Klicks auf der Seite "abc.com" Von <Datum d1> BIS <Datum d2> für Personen, die Chrome verwendet haben ODER (in Europa waren UND iPhone verwendet haben)
Beachten Sie die booleschen Operatoren. Diese stehen Kunden in der Abfrageoberfläche zur Verfügung, um beliebig komplexe Anfragen für Abfragen zu erstellen.
Langsame Anfrage
Der betreffende Kunde hat versucht, etwas zu tun, das intuitiv schnell funktionieren sollte:
Zeige alle Sitzungsaufzeichnungen für Benutzer, die eine beliebige Seite mit einer URL besucht haben, die "/jobs" enthält
Auf dieser Website gab es eine enorme Menge an Verkehr, und wir haben über eine Million einzigartiger URLs nur dafür gespeichert. Und sie wollten ein recht einfaches URL-Muster finden, das zu ihrem Geschäftsmodell passt.
Vorläufige Untersuchung
Lassen Sie uns sehen, was in der Datenbank passiert. Hier ist die ursprüngliche langsame SQL-Anfrage:
WÄHLEN
COUNT(*)
VON
acc_{account_id}.urls AS recordings_urls,
acc_{account_id}.recording_data AS recording_data,
acc_{account_id}.sessions AS sessions
WO
recording_data.usp_id = sessions.usp_id
UND sessions.referrer_id = recordings_urls.id
UND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )
UND r_time > to_timestamp(1542585600)
UND r_time = 5
UND recording_data.num_of_pages > 0 ;Hier sind die Zeitangaben:
Geplante Zeit: 1,480 ms Ausführungszeit: 1.431.924,650 ms
Die Abfrage durchlief 150.000 Zeilen. Der Abfrage-Planer zeigte einige interessante Details, jedoch keine offensichtlichen Engpässe.
Lassen Sie uns die Abfrage weiter untersuchen. Wie zu sehen ist, führt sie JOIN drei Tabellen aus:
- sessions: um Sitzungsinformationen anzuzeigen: Browser, User-Agent, Land usw.
- recording_data: aufgezeichnete URLs, Seiten, Besuchsdauer
- urls: um die Duplizierung extrem großer URLs zu vermeiden, speichern wir sie in einer separaten Tabelle.
Bitte beachten Sie auch, dass alle unsere Tabellen bereits nach account_idunterteilt sind. So wird die Situation ausgeschlossen, dass durch ein besonders großes Konto Probleme bei anderen entstehen.
Auf der Suche nach Hinweisen
Bei genauerer Betrachtung sehen wir, dass etwas mit der spezifischen Anfrage nicht stimmt. Es lohnt sich, diese Zeile 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 einzigartige URLs, die für dieses Konto gesammelt wurden) die Leistung beeinträchtigt sein könnte.
Aber nein – es liegt nicht daran!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Zeit: 5231.765 msDie tatsächliche Suchanfrage nach dem Muster dauert nur 5 Sekunden. Die Mustererkennung bei einer Million einzigartiger URLs scheint eindeutig kein Problem darzustellen.
Der nächste Verdächtige auf der Liste sind einige JOIN. Möglicherweise hat deren übermäßige Nutzung zu einer Verlangsamung geführt? Normalerweise JOINsind sie die offensichtlichsten Kandidaten für Leistungsprobleme, aber ich glaube 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 msUnd das war auch nicht unser Fall. JOINDie Anfragen waren tatsächlich ziemlich schnell.
Wir schränken den Kreis der Verdächtigen ein.
Ich war bereit, die Anfrage zu verändern, um mögliche Leistungsverbesserungen zu erreichen. Mein Team und ich haben zwei Hauptideen entwickelt:
- EXISTS für die URL-Subabfrage verwenden.: Wir wollten erneut überprüfen, ob es Probleme mit der Subabfrage für die URLs gibt. Eine Möglichkeit, dies zu erreichen, ist einfach
EXISTS.EXISTSdie Leistung erheblich zu verbessern, da die Abfrage sofort stoppt, sobald sie eine einzige Zeile anhand der 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 =5
AND recording_data.num_of_pages > 0 ;
count
32519
(1 row)
Time: 1636.637 msJa, genau. Eine Subabfrage, die in EXISTS, macht alles super schnell. Die nächste logische Frage ist, warum die Anfragen mit JOIN-en und die Subabfrage für sich genommen schnell sind, aber zusammen schrecklich langsam werden?
- Wir verschieben die Subabfrage in eine CTE. : Wenn die Anfrage selbst schnell ist, können wir einfach zuerst das schnelle Ergebnis berechnen und es dann der Hauptanfrage 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 =5
AND recording_data.num_of_pages > 0;Aber das war immer noch sehr langsam.
Den Schuldigen finden
Während all dieser Zeit schwebte ein kleines Detail vor meinen Augen, von dem ich ständig abgelenkt war. Aber da ich nichts anderes mehr hatte, beschloss ich, es mir anzusehen. Ich spreche von && Operator. Während EXISTS einfach die Leistung verbessert wurde, && war der einzige verbleibende gemeinsame Faktor in allen Versionen der langsamen Anfrage.
Wenn wir auf , sehen wir, dass && verwendet wird, wenn es darum geht, gemeinsame Elemente zwischen zwei Arrays zu finden.
Im ursprünglichen Antrag ist das:
UND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Das bedeutet, dass wir eine Mustersuche in unseren URLs durchführen, um dann eine Schnittmenge mit allen URLs mit gemeinsamen Einträgen zu finden. Das ist etwas verwirrend, da „urls“ hier nicht auf eine Tabelle verweist, die alle URL-Adressen enthält, sondern auf die Spalte „urls“ in der Tabelle. recording_data.
Mit dem Anstieg der Verdachtsmomente gegen &&, versuchte ich, Bestätigungen im generierten Abfrageplan zu finden, EXPLAIN ANALYZE (ich hatte bereits einen gespeicherten Plan, aber es ist mir in der Regel lieber, in SQL zu experimentieren, als die Intransparenzen der Abfrageplaner zu verstehen).
Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time = '5'::double precision) AND (num_of_pages > 0))
Vom Filter entfernte Zeilen: 52710Es gab mehrere Filterzeilen nur aus &&. Das bedeutete, dass diese Operation nicht nur kostspielig war, sondern auch mehrfach ausgeführt wurde.
Ich überprüfte dies, indem ich die Bedingung isolierte
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.
Dies ist der entscheidende Schritt. Wir müssen stets in der gesamten Haupttabelle der URLs nach Mustern suchen und immer die Schnittmengen finden. Eine direkte Suche in den URL-Einträgen ist nicht möglich, da es sich lediglich um IDs handelt, die auf urls.
den Weg zur Lösung
&& langsam ist, weil beide Mengen enorm sind. Der Vorgang wird relativ schnell sein, wenn ich urls findet man { "http://google.com/", "http://wingify.com/" }.
begonnen habe, nach einer Möglichkeit zu suchen, in Postgres Schnittmengen zu erstellen, ohne &&, jedoch ohne nennenswerten Erfolg.
Schließlich haben wir beschlossen, 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 AND
urls.url ILIKE '%jobs%'Anstelle von JOIN Bei der Syntax habe ich einfach eine Teilabfrage verwendet und die recording_data.urls Array entrollt, um die Bedingung direkt anwenden zu können in WHERE.
Das Wichtigste hier ist, dass && wird verwendet, um zu überprüfen, ob dieser Datensatz die entsprechende URL enthält. Wenn man genau hinsieht, kann man in dieser Operation das Durchlaufen von Array-Elementen (oder Tabellenzeilen) und das Stoppen bei erfüllten Bedingungen erkennen. Kommt Ihnen das bekannt vor? Ah, EXISTS.
Da bei recording_data.urls von außerhalb des Kontextes der Unterabfrage verwiesen werden kann, können wir zu unserem alten Freund zurückkehren EXISTS und ihn um die Unterabfrage wickeln.
Fassen wir alles zusammen, 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 = 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 endgültige Ausführungszeit Zeit: 1898,717 ms Zeit zu feiern?!?
Nicht so schnell! Zuerst muss die Richtigkeit überprüft werden. Ich war äußerst misstrauisch gegenüber EXISTS Optimierung, da sie die Logik auf ein früheres Ende ändert. Wir müssen sicherstellen, dass wir keinen nicht offensichtlichen Fehler in die Anfrage eingefügt haben.
Eine einfache Überprüfung bestand darin, count(*) und sowohl bei langsamen als auch bei schnellen Anfragen für eine Vielzahl von verschiedenen Datensätzen. Anschließend habe ich für eine kleine Teilmenge der Daten die Richtigkeit aller Ergebnisse manuell überprüft.
Alle Überprüfungen ergaben durchweg positive Ergebnisse. Wir haben alles behoben!
Learnings
Aus dieser Geschichte lassen sich einige Lehren ziehen:
- Abfragepläne erzählen nicht die ganze Geschichte, können aber Hinweise geben.
- Die Hauptverdächtigen sind nicht immer die tatsächlichen Schuldigen.
- Langsame Anfragen können aufgedeckt werden, um Engpässe zu isolieren.
- Nicht alle Optimierungen sind von Natur aus reduktiv.
- Nutzung
EXIST, wo möglich, kann zu einem erheblichen Leistungszuwachs führen.
Fazit
Wir haben die Abfragezeit von etwa 24 Minuten auf 2 Sekunden reduziert — das ist ein erheblicher Leistungszuwachs! Auch wenn dieser Artikel lang war, fanden alle Experimente an einem Tag statt und brauchen schätzungsweise 1,5 bis 2 Stunden für Optimierungen und Tests.
SQL ist eine wunderbare Sprache, wenn man sich nicht scheut, sie zu lernen und zu nutzen. Mit einem soliden Verständnis dafür, wie SQL-Abfragen ausgeführt werden, wie Datenbanken Abfragepläne erstellen, wie Indizes funktionieren und welche Datenmengen man verarbeitet, können Sie Ihre Abfragen erheblich optimieren. Es ist jedoch ebenso wichtig, weiterhin verschiedene Ansätze auszuprobieren und das Problem schrittweise zu zerlegen, um Engpässe zu finden.
Der beste Teil beim Erreichen solcher Ergebnisse ist die spürbare Verbesserung der Geschwindigkeit — wenn ein Bericht, der zuvor nicht einmal geladen wurde, jetzt fast sofort geladen wird.
Besonderer Dank meinen Kollegen im Team: Aditie Mishra, Aditie Gaur und für das Brainstorming und Dinku Pandir dafür, dass er einen wichtigen Fehler in unserer finalen Abfrage gefunden hat, bevor wir uns endgültig von ihr verabschiedet haben!
Quelle: habr.com
