Τον περασμένο Δεκέμβριο έλαβα μια ενδιαφέρουσα αναφορά σφαλμάτων από την ομάδα υποστήριξης του VWO. Ο χρόνος φόρτωσης μιας από τις αναφορές αναλυτικών στοιχείων για έναν μεγάλο εταιρικό πελάτη φαινόταν απαγορευτικός. Και επειδή αυτός είναι ο τομέας της ευθύνης μου, επικεντρώθηκα αμέσως στην επίλυση του προβλήματος.
Ιστορικό
Για να γίνει κατανοητό για τι πράγμα μιλάω, θα σας πω λίγα λόγια για το VWO. Αυτή είναι μια πλατφόρμα με την οποία μπορείτε να ξεκινήσετε διάφορες στοχευμένες καμπάνιες στους ιστότοπούς σας: να διεξάγετε πειράματα A/B, να παρακολουθείτε επισκέπτες και μετατροπές, να αναλύετε τη διοχέτευση πωλήσεων, να εμφανίζετε χάρτες θερμότητας και να αναπαράγετε εγγραφές επισκέψεων.
Αλλά το πιο σημαντικό πράγμα για την πλατφόρμα είναι η αναφορά. Όλες οι παραπάνω λειτουργίες είναι αλληλένδετες. Και για εταιρικούς πελάτες, ένας τεράστιος όγκος πληροφοριών θα ήταν απλώς άχρηστος χωρίς μια ισχυρή πλατφόρμα που τις παρουσιάζει σε μορφή αναλυτικών στοιχείων.
Χρησιμοποιώντας την πλατφόρμα, μπορείτε να κάνετε ένα τυχαίο ερώτημα σε ένα μεγάλο σύνολο δεδομένων. Εδώ είναι ένα απλό παράδειγμα:
Εμφάνιση όλων των κλικ στη σελίδα "abc.com" ΑΠΟ <ημερομηνία d1> ΕΩΣ <ημερομηνία d2> για άτομα που χρησιμοποίησαν Chrome OR (βρίσκεται στην Ευρώπη ΚΑΙ χρησιμοποίησαν iPhone)
Δώστε προσοχή στους τελεστές Boolean. Είναι διαθέσιμα στους πελάτες στη διεπαφή ερωτημάτων για να κάνουν αυθαίρετα πολύπλοκα ερωτήματα για τη λήψη δειγμάτων.
Αργό αίτημα
Ο εν λόγω πελάτης προσπαθούσε να κάνει κάτι που διαισθητικά θα έπρεπε να λειτουργεί γρήγορα:
Εμφάνιση όλων των εγγραφών περιόδου σύνδεσης για χρήστες που επισκέφτηκαν οποιαδήποτε σελίδα με διεύθυνση URL που περιέχει "/jobs"
Αυτός ο ιστότοπος είχε έναν τόνο επισκεψιμότητας και αποθηκεύαμε πάνω από ένα εκατομμύριο μοναδικές διευθύνσεις URL μόνο για αυτόν. Και ήθελαν να βρουν ένα αρκετά απλό πρότυπο URL που να σχετίζεται με το επιχειρηματικό τους μοντέλο.
Προκαταρκτική έρευνα
Ας ρίξουμε μια ματιά στο τι συμβαίνει στη βάση δεδομένων. Παρακάτω είναι το αρχικό αργό ερώτημα SQL:
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 ;Και ιδού οι χρονισμοί:
Προγραμματισμένος χρόνος: 1.480 ms Χρόνος εκτέλεσης: 1431924.650 ms
Το ερώτημα ανίχνευσε 150 χιλιάδες σειρές. Ο προγραμματιστής ερωτημάτων έδειξε μερικές ενδιαφέρουσες λεπτομέρειες, αλλά όχι εμφανή σημεία συμφόρησης.
Ας μελετήσουμε περαιτέρω το αίτημα. Όπως μπορείτε να δείτε, το κάνει JOIN τρία τραπέζια:
- συνεδρίες: για εμφάνιση πληροφοριών συνεδρίας: πρόγραμμα περιήγησης, παράγοντας χρήστη, χώρα και ούτω καθεξής.
- εγγραφή_δεδομένων: καταγεγραμμένες διευθύνσεις URL, σελίδες, διάρκεια επισκέψεων
- urls: Για να αποφύγουμε την αντιγραφή εξαιρετικά μεγάλων διευθύνσεων URL, τα αποθηκεύουμε σε ξεχωριστό πίνακα.
Σημειώστε επίσης ότι όλοι οι πίνακές μας έχουν ήδη χωριστεί κατά account_id. Έτσι, αποκλείεται μια κατάσταση όπου ένας ιδιαίτερα μεγάλος λογαριασμός προκαλεί προβλήματα σε άλλους.
Ψάχνοντας για ενδείξεις
Μετά από πιο προσεκτική εξέταση, βλέπουμε ότι κάτι δεν πάει καλά με ένα συγκεκριμένο αίτημα. Αξίζει να ρίξετε μια πιο προσεκτική ματιά σε αυτή τη γραμμή:
urls && array(
select id from acc_{account_id}.urls
where url ILIKE '%enterprise_customer.com/jobs%'
)::text[]Η πρώτη σκέψη ήταν ότι ίσως επειδή ILIKE σε όλες αυτές τις μεγάλες διευθύνσεις URL (έχουμε πάνω από 1,4 εκατομμύρια μοναδικό Η απόδοση των διευθύνσεων URL που συλλέγονται για αυτόν τον λογαριασμό) ενδέχεται να υποφέρει.
Αλλά όχι, δεν είναι αυτό το θέμα!
SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
id
--------
...
(198661 rows)
Time: 5231.765 msΤο ίδιο το αίτημα αναζήτησης προτύπου διαρκεί μόνο 5 δευτερόλεπτα. Η αναζήτηση για ένα μοτίβο σε ένα εκατομμύριο μοναδικές διευθύνσεις URL σαφώς δεν αποτελεί πρόβλημα.
Ο επόμενος ύποπτος στη λίστα είναι αρκετοί JOIN. Ίσως η υπερβολική χρήση τους να έχει προκαλέσει την επιβράδυνση; Συνήθως JOINΕίναι οι πιο προφανείς υποψήφιοι για προβλήματα απόδοσης, αλλά δεν πίστευα ότι το δικό μας ήταν μια τυπική περίπτωση.
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Και αυτό δεν ήταν επίσης η περίπτωσή μας. JOINαποδείχθηκε αρκετά γρήγορο.
Περιορίζοντας τον κύκλο των υπόπτων
Ήμουν έτοιμος να αρχίσω να αλλάζω το ερώτημα για να επιτύχω πιθανές βελτιώσεις απόδοσης. Η ομάδα μου και εγώ αναπτύξαμε 2 βασικές ιδέες:
- Χρησιμοποιήστε το EXISTS για τη διεύθυνση URL υποερωτήματος: Θέλαμε να ελέγξουμε ξανά εάν υπήρχαν προβλήματα με το υποερώτημα για τις διευθύνσεις URL. Ένας τρόπος για να επιτευχθεί αυτό είναι η απλή χρήση
EXISTS.EXISTSβελτιώνει σημαντικά την απόδοση αφού τελειώνει αμέσως μόλις βρει τη μοναδική συμβολοσειρά που ταιριάζει με τη συνθήκη.
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Λοιπον ναι. Υποερώτημα όταν είναι τυλιγμένο EXISTS, κάνει τα πάντα εξαιρετικά γρήγορα. Το επόμενο λογικό ερώτημα είναι γιατί το αίτημα με JOIN-Το ami και το ίδιο το subquery είναι γρήγορα ξεχωριστά, αλλά είναι τρομερά αργά μαζί;
- Μετακίνηση του υποερωτήματος στο CTE : Εάν το ερώτημα είναι γρήγορο από μόνο του, μπορούμε απλώς να υπολογίσουμε πρώτα το γρήγορο αποτέλεσμα και μετά να το παρέχουμε στο κύριο ερώτημα
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;Αλλά ήταν ακόμα πολύ αργό.
Βρίσκοντας τον ένοχο
Όλο αυτό το διάστημα, ένα μικρό πράγμα άστραψε μπροστά στα μάτια μου, το οποίο παραμερίζω συνεχώς. Επειδή όμως δεν είχε μείνει τίποτα άλλο, αποφάσισα να την κοιτάξω κι εγώ. Μιλάω για && χειριστής. Αντίο EXISTS απλά βελτιωμένη απόδοση && ήταν ο μόνος κοινός παράγοντας που απομένει σε όλες τις εκδόσεις του αργού ερωτήματος.
Κοιταζώ , το βλέπουμε && χρησιμοποιείται όταν χρειάζεται να βρείτε κοινά στοιχεία μεταξύ δύο πινάκων.
Στο αρχικό αίτημα αυτό είναι:
AND ( urls && array(select id from acc_{account_id}.urls where url ILIKE '%enterprise_customer.com/jobs%')::text[] )Που σημαίνει ότι κάνουμε μια αναζήτηση μοτίβων στις διευθύνσεις URL μας και, στη συνέχεια, βρίσκουμε τη διασταύρωση με όλες τις διευθύνσεις URL με κοινές αναρτήσεις. Αυτό είναι λίγο μπερδεμένο επειδή το "urls" εδώ δεν αναφέρεται στον πίνακα που περιέχει όλες τις διευθύνσεις URL, αλλά στη στήλη "urls" στον πίνακα recording_data.
Με αυξανόμενες υποψίες σχετικά με &&, προσπάθησα να βρω επιβεβαίωση για αυτούς στο σχέδιο ερωτήματος που δημιουργήθηκε EXPLAIN ANALYZE (Είχα ήδη αποθηκευμένο ένα σχέδιο, αλλά συνήθως αισθάνομαι πιο άνετα να πειραματίζομαι σε SQL παρά να προσπαθώ να κατανοήσω την αδιαφάνεια των προγραμμάτων σχεδιασμού ερωτημάτων).
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Υπήρχαν πολλές σειρές φίλτρων μόνο από &&. Πράγμα που σήμαινε ότι αυτή η επέμβαση όχι μόνο ήταν ακριβή, αλλά και ότι έγινε πολλές φορές.
Το δοκίμασα αυτό απομονώνοντας την κατάσταση
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[]Αυτό το ερώτημα ήταν αργό. Επειδή η JOINΤα -s είναι γρήγορα και τα subqueries είναι γρήγορα, το μόνο που έμεινε ήταν && χειριστής.
Αυτή είναι απλώς μια βασική λειτουργία. Χρειάζεται πάντα να αναζητούμε ολόκληρο τον υποκείμενο πίνακα των διευθύνσεων URL για να αναζητήσουμε ένα μοτίβο και πρέπει πάντα να βρίσκουμε διασταυρώσεις. Δεν μπορούμε να κάνουμε απευθείας αναζήτηση βάσει εγγραφών URL, επειδή πρόκειται απλώς για αναγνωριστικά που αναφέρονται urls.
Στο δρόμο για λύση
&& αργό γιατί και τα δύο σετ είναι τεράστια. Η επέμβαση θα είναι σχετικά γρήγορη αν αντικαταστήσω urls επί { "http://google.com/", "http://wingify.com/" }.
Άρχισα να ψάχνω έναν τρόπο να κάνω καθορισμένη διασταύρωση στο Postgres χωρίς χρήση &&, αλλά χωρίς μεγάλη επιτυχία.
Στο τέλος, αποφασίσαμε να λύσουμε το πρόβλημα μεμονωμένα: δώστε μου τα πάντα urls γραμμές για τις οποίες η διεύθυνση URL ταιριάζει με το μοτίβο. Χωρίς πρόσθετους όρους θα είναι -
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%'Αντί για JOIN σύνταξη Μόλις χρησιμοποίησα ένα υποερώτημα και επέκτεινα recording_data.urls πίνακα, ώστε να μπορείτε να εφαρμόσετε απευθείας τη συνθήκη WHERE.
Το πιο σημαντικό εδώ είναι ότι && χρησιμοποιείται για να ελέγξει εάν μια δεδομένη καταχώριση περιέχει μια αντίστοιχη διεύθυνση URL. Εάν στραβίσετε λίγο, μπορείτε να δείτε αυτή τη λειτουργία να κινείται μέσα από τα στοιχεία ενός πίνακα (ή σειρές ενός πίνακα) και να σταματά όταν πληρούται μια συνθήκη (ταίριασμα). Δεν σου θυμίζει τίποτα; Ναι, EXISTS.
Από τότε recording_data.urls μπορεί να γίνει αναφορά εκτός του πλαισίου του υποερωτήματος, όταν συμβεί αυτό, μπορούμε να επαναλάβουμε τον παλιό μας φίλο EXISTS και τυλίξτε το υποερώτημα με αυτό.
Συνδυάζοντας τα πάντα, παίρνουμε το τελικό βελτιστοποιημένο ερώτημα:
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%'
);
Και ο τελικός χρόνος παράδοσης Time: 1898.717 ms Ώρα να γιορτάσουμε;;!
Οχι τόσο γρήγορα! Πρώτα πρέπει να ελέγξετε την ορθότητα. Ήμουν εξαιρετικά καχύποπτος EXISTS βελτιστοποίηση καθώς αλλάζει τη λογική για να ολοκληρωθεί νωρίτερα. Πρέπει να είμαστε σίγουροι ότι δεν έχουμε προσθέσει ένα μη προφανές σφάλμα στο αίτημα.
Ένα απλό τεστ ήταν να τρέξει count(*) τόσο σε αργά όσο και γρήγορα ερωτήματα για μεγάλο αριθμό διαφορετικών συνόλων δεδομένων. Στη συνέχεια, για ένα μικρό υποσύνολο δεδομένων, επαλήθευσα με μη αυτόματο τρόπο ότι όλα τα αποτελέσματα ήταν σωστά.
Όλοι οι έλεγχοι έδωσαν σταθερά θετικά αποτελέσματα. Διορθώσαμε τα πάντα!
Διδάγματα
Υπάρχουν πολλά διδάγματα που μπορούμε να αντλήσουμε από αυτή την ιστορία:
- Τα σχέδια ερωτημάτων δεν λένε ολόκληρη την ιστορία, αλλά μπορούν να παρέχουν ενδείξεις
- Οι κύριοι ύποπτοι δεν είναι πάντα οι πραγματικοί ένοχοι
- Τα αργά ερωτήματα μπορούν να αναλυθούν για να απομονωθούν τα σημεία συμφόρησης
- Δεν είναι όλες οι βελτιστοποιήσεις αναγωγικής φύσης
- Χρήση
EXIST, όπου είναι δυνατόν, μπορεί να οδηγήσει σε δραματικές αυξήσεις στην παραγωγικότητα
Παραγωγή
Περάσαμε από ένα χρόνο ερωτήματος ~24 λεπτών σε 2 δευτερόλεπτα - μια αρκετά σημαντική αύξηση απόδοσης! Αν και αυτό το άρθρο βγήκε μεγάλο, όλα τα πειράματα που κάναμε έγιναν σε μια μέρα και υπολογίστηκε ότι χρειάστηκαν μεταξύ 1,5 και 2 ώρες για βελτιστοποιήσεις και δοκιμές.
Η SQL είναι μια υπέροχη γλώσσα αν δεν τη φοβάστε, αλλά προσπαθήστε να τη μάθετε και να τη χρησιμοποιήσετε. Έχοντας καλή κατανόηση του τρόπου με τον οποίο εκτελούνται τα ερωτήματα SQL, του τρόπου με τον οποίο η βάση δεδομένων δημιουργεί σχέδια ερωτημάτων, πώς λειτουργούν τα ευρετήρια και απλώς το μέγεθος των δεδομένων με τα οποία ασχολείστε, μπορείτε να έχετε μεγάλη επιτυχία στη βελτιστοποίηση των ερωτημάτων. Είναι εξίσου σημαντικό, ωστόσο, να συνεχίσουμε να δοκιμάζουμε διαφορετικές προσεγγίσεις και να διαλύουμε σιγά σιγά το πρόβλημα, βρίσκοντας τα σημεία συμφόρησης.
Το καλύτερο μέρος για την επίτευξη τέτοιων αποτελεσμάτων είναι η αισθητή, ορατή βελτίωση της ταχύτητας - όπου μια αναφορά που προηγουμένως δεν θα φορτωνόταν καν τώρα φορτώνεται σχεδόν αμέσως.
Ιδιαίτερες ευχαριστίες προς οι σύντροφοί μου με εντολή του Aditya Mishra, Aditya Gauru и για καταιγισμό ιδεών και Ντινκάρ Παντίρ για την εύρεση ενός σημαντικού σφάλματος στο τελικό μας αίτημα πριν το αποχαιρετήσουμε τελικά!
Πηγή: www.habr.com