Letzten Dezember erhielt ich einen interessanten Fehlerbericht vom VWO-Supportteam. Die Ladezeit für einen der Analyseberichte für einen großen Firmenkunden schien unerschwinglich zu sein. Und da dies mein Verantwortungsbereich ist, habe ich mich sofort auf die Lösung des Problems konzentriert.
Vorgeschichte
Um zu verdeutlichen, wovon ich spreche, erzähle ich Ihnen ein wenig über VWO. Hierbei handelt es sich um eine Plattform, mit der Sie verschiedene gezielte Kampagnen auf Ihren Websites starten können: A/B-Experimente durchführen, Besucher und Conversions verfolgen, den Verkaufstrichter analysieren, Heatmaps anzeigen und Besuchsaufzeichnungen abspielen.
Aber das Wichtigste an der Plattform ist die Berichterstattung. Alle oben genannten Funktionen sind miteinander verbunden. Und für Firmenkunden wären riesige Informationsmengen ohne eine leistungsstarke Plattform, die sie in analytischer Form darstellt, einfach nutzlos.
Mit der Plattform können Sie eine zufällige Abfrage eines großen Datensatzes durchführen. Hier ist ein einfaches Beispiel:
Alle Klicks auf der Seite „abc.com“ vom <Datum d1> bis zum <Datum d2> für Personen anzeigen, die Chrome ODER (in Europa ansässig UND ein iPhone verwendet) verwendet haben.
Achten Sie auf boolesche Operatoren. Sie stehen Clients in der Abfrageschnittstelle zur Verfügung, um beliebig komplexe Abfragen zum Erhalten von Beispielen durchzuführen.
Langsame Anfrage
Der betreffende Kunde versuchte etwas zu tun, das intuitiv schnell funktionieren sollte:
Alle Sitzungsdatensätze für Benutzer anzeigen, die eine Seite mit einer URL besucht haben, die „/jobs“ enthält.
Diese Website hatte eine Menge Traffic und wir haben allein für sie über eine Million eindeutige URLs gespeichert. Und sie wollten eine relativ einfache URL-Vorlage finden, die zu ihrem Geschäftsmodell passt.
Voruntersuchung
Werfen wir einen Blick darauf, was in der Datenbank vor sich geht. Unten 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 Ausführungszeit: 1431924.650 ms
Die Abfrage hat 150 Zeilen gecrawlt. Der Abfrageplaner zeigte einige interessante Details, aber keine offensichtlichen Engpässe.
Lassen Sie uns die Anfrage weiter untersuchen. Wie Sie sehen, tut er es JOIN drei Tabellen:
- Sessions: um Sitzungsinformationen anzuzeigen: Browser, Benutzeragent, Land usw.
- Aufnahmedaten: erfasste URLs, Seiten, Dauer der Besuche
- urls: Um die Duplizierung extrem großer URLs zu vermeiden, speichern wir sie in einer separaten Tabelle.
Beachten Sie auch, dass alle unsere Tabellen bereits partitioniert sind account_id. Dadurch wird ausgeschlossen, dass ein besonders großes Konto anderen Probleme bereitet.
Auf der Suche nach Hinweisen
Bei näherer Betrachtung stellen wir fest, dass mit einer bestimmten Anfrage etwas nicht stimmt. Es lohnt sich, einen genaueren Blick auf diese Zeile zu werfen:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Der erste Gedanke war vielleicht, weil ILIKE auf all diesen langen URLs (wir haben über 1,4 Millionen). einzigartig Die Leistung der für dieses Konto erfassten URLs kann beeinträchtigt sein.
Aber nein, darum geht es nicht!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msDie Vorlagesuchanfrage selbst dauert nur 5 Sekunden. Die Suche nach einem Muster in einer Million eindeutiger URLs ist offensichtlich kein Problem.
Der nächste Verdächtige auf der Liste sind mehrere JOIN. Vielleicht hat ihre Überbeanspruchung die Verlangsamung verursacht? Gewöhnlich JOINsind 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 < to_timestamp(1545177599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0 ;
count
-------
8086
(1 row)
Time: 147.851 msUnd das war auch nicht unser Fall. JOINEs ist ziemlich schnell gegangen.
Einen Kreis der Verdächtigen eingrenzen
Ich war bereit, mit der Änderung der Abfrage zu beginnen, um mögliche Leistungsverbesserungen zu erzielen. Mein Team und ich haben zwei Hauptideen entwickelt:
- Verwenden Sie EXISTS für die Unterabfrage-URL: Wir wollten noch einmal prüfen, ob es Probleme mit der Unterabfrage für die URLs gab. Eine Möglichkeit, dies zu erreichen, ist die einfache Verwendung
EXISTS.EXISTSverbessert die Leistung erheblich, da es sofort beendet wird, sobald die einzige Zeichenfolge gefunden wird, die der Bedingung entspricht.
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 msNun ja. Unterabfrage beim Einschließen EXISTS, macht alles superschnell. Die nächste logische Frage ist, warum die Anfrage mit JOIN-ami und die Unterabfrage selbst sind einzeln schnell, aber zusammen furchtbar langsam?
- Verschieben der Unterabfrage in den CTE : Wenn die Abfrage alleine schnell 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 < to_timestamp(1545107599)
AND recording_data.duration >=5
AND recording_data.num_of_pages > 0;Aber es war immer noch sehr langsam.
Den Täter finden
Die ganze Zeit über blitzte vor meinen Augen eine Kleinigkeit auf, die ich ständig beiseite schob. Da mir aber nichts anderes übrig blieb, beschloss ich, sie auch anzuschauen. Ich rede von && Operator. Tschüss EXISTS nur verbesserte Leistung && war der einzige verbliebene gemeinsame Faktor in allen Versionen der langsamen Abfrage.
Anschauen , wir sehen das && Wird verwendet, wenn Sie gemeinsame Elemente zwischen zwei Arrays finden müssen.
Im ursprünglichen Antrag 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 Mustersuche in unseren URLs durch und finden dann die Schnittmenge aller URLs mit gemeinsamen Beiträgen. Das ist etwas verwirrend, da sich „urls“ hier nicht auf die Tabelle bezieht, die alle URLs enthält, sondern auf die Spalte „urls“ in der Tabelle recording_data.
Mit wachsendem Verdacht bzgl &&Ich habe versucht, im generierten Abfrageplan eine Bestätigung dafür zu finden EXPLAIN ANALYZE (Ich hatte bereits einen Plan gespeichert, aber normalerweise ist es für mich bequemer, mit SQL zu experimentieren, als zu versuchen, die Undurchsichtigkeit 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: 52710Es gab nur mehrere Filterlinien von &&. Dies bedeutete, dass dieser Eingriff nicht nur teuer war, sondern auch mehrere Male durchgeführt werden musste.
Ich habe dies getestet, 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 Abfrage war langsam. Weil das JOIN-s sind schnell und Unterabfragen sind schnell, das Einzige, was übrig blieb, war && Operator.
Dies ist nur eine Schlüsseloperation. Wir müssen immer die gesamte zugrunde liegende URL-Tabelle durchsuchen, um nach einem Muster zu suchen, und wir müssen immer Schnittmengen finden. Wir können nicht direkt nach URL-Datensätzen suchen, da es sich lediglich um IDs handelt, auf die verwiesen wird urls.
Auf dem Weg zu einer Lösung
&& langsam, weil beide Sets riesig sind. Der Vorgang wird relativ schnell gehen, wenn ich ihn austausche urls auf { "http://google.com/", "http://wingify.com/" }.
Ich begann nach einer Möglichkeit zu suchen, Schnittmengen in Postgres festzulegen, ohne sie zu verwenden &&, aber ohne großen Erfolg.
Am Ende haben wir beschlossen, das Problem einfach isoliert zu lösen: Gib mir alles urls Zeilen, deren URL mit dem Muster übereinstimmt. 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%'Statt JOIN Syntax Ich habe einfach eine Unterabfrage verwendet und erweitert recording_data.urls Array, sodass Sie die Bedingung direkt anwenden können WHERE.
Das Wichtigste hier ist das && Wird verwendet, um zu überprüfen, ob ein bestimmter Eintrag eine passende URL enthält. Wenn Sie ein wenig blinzeln, können Sie sehen, dass sich dieser Vorgang durch die Elemente eines Arrays (oder Zeilen einer Tabelle) bewegt und stoppt, wenn eine Bedingung (Übereinstimmung) erfüllt ist. Erinnert Sie an nichts? Ja, EXISTS.
Seit am recording_data.urls kann von außerhalb des Unterabfragekontexts referenziert werden. In diesem Fall können wir auf unseren alten Freund zurückgreifen EXISTS und umschließen Sie die Unterabfrage damit.
Wenn wir alles zusammenfügen, 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 Vorlaufzeit Time: 1898.717 ms Zeit zu feiern?!?
Nicht so schnell! Zuerst müssen Sie die Richtigkeit überprüfen. Ich war äußerst misstrauisch EXISTS Optimierung, da dadurch die Logik so geändert wird, dass sie früher beendet wird. Wir müssen sicherstellen, dass wir der Anfrage keinen nicht offensichtlichen Fehler hinzugefügt haben.
Es sollte ein einfacher Test durchgeführt werden count(*) sowohl bei langsamen als auch bei schnellen Abfragen für eine große Anzahl unterschiedlicher Datensätze. Dann habe ich für eine kleine Teilmenge der Daten manuell überprüft, ob alle Ergebnisse korrekt waren.
Alle Tests ergaben durchweg positive Ergebnisse. Wir haben alles repariert!
gewonnene Erkenntnisse
Aus dieser Geschichte lassen sich viele Lehren ziehen:
- Abfragepläne erzählen nicht die ganze Geschichte, können aber Hinweise liefern
- Die Hauptverdächtigen sind nicht immer die wahren Täter
- Langsame Abfragen können aufgeschlüsselt werden, um Engpässe zu isolieren
- Nicht alle Optimierungen sind reduktiver Natur
- Verwenden
EXIST, wo möglich, kann zu dramatischen Produktivitätssteigerungen führen
Abschluss
Wir haben die Abfragezeit von ca. 24 Minuten auf 2 Sekunden erhöht – eine deutliche Leistungssteigerung! Auch wenn dieser Artikel groß herauskam, fanden alle von uns durchgeführten Experimente an einem Tag statt und es wurde geschätzt, dass die Optimierungen und Tests zwischen 1,5 und 2 Stunden dauerten.
SQL ist eine wunderbare Sprache, wenn Sie keine Angst davor haben, sondern versuchen, sie zu lernen und zu verwenden. Wenn Sie gut verstehen, wie SQL-Abfragen ausgeführt werden, wie die Datenbank Abfragepläne generiert, wie Indizes funktionieren und einfach über die Größe der Daten, mit denen Sie arbeiten, können Sie Abfragen sehr erfolgreich optimieren. Ebenso wichtig ist es jedoch, weiterhin verschiedene Ansätze auszuprobieren und das Problem langsam aufzuschlüsseln und die Engpässe zu finden.
Das Beste daran, solche Ergebnisse zu erzielen, ist die spürbare, sichtbare Geschwindigkeitsverbesserung – ein Bericht, der früher nicht einmal geladen werden konnte, wird jetzt fast sofort geladen.
Besonderer Dank meine Kameraden auf Befehl von Aditya Mishra, Aditya Gauru и zum Brainstorming und Dinkar Pandir dafür, dass wir in unserer letzten Anfrage einen wichtigen Fehler gefunden haben, bevor wir uns endgültig davon verabschiedet haben!
Source: habr.com