Μήνες πριν - δημόσιο στην PostgreSQL.
Το έχετε ήδη χρησιμοποιήσει περισσότερες από 6000 φορές, αλλά ένα εύχρηστο χαρακτηριστικό που μπορεί να έχει περάσει απαρατήρητο είναι δομικές ενδείξεις, που μοιάζουν κάπως έτσι:
Ακούστε τους και τα αιτήματά σας θα «γίνουν λεία και μεταξένια». 🙂
Αλλά σοβαρά, πολλές καταστάσεις που κάνουν ένα αίτημα αργό και διψασμένο για πόρους είναι τυπικά και μπορούν να αναγνωριστούν από τη δομή και τα δεδομένα του σχεδίου.
Σε αυτήν την περίπτωση, κάθε μεμονωμένος προγραμματιστής δεν χρειάζεται να αναζητήσει μια επιλογή βελτιστοποίησης μόνος του, βασιζόμενος αποκλειστικά στην εμπειρία του - μπορούμε να του πούμε τι συμβαίνει εδώ, ποιος θα μπορούσε να είναι ο λόγος και πώς να προσεγγίσετε μια λύση. Αυτό κάναμε.
Ας ρίξουμε μια πιο προσεκτική ματιά σε αυτές τις περιπτώσεις - πώς ορίζονται και σε ποιες συστάσεις οδηγούν.
Για να βυθιστείτε καλύτερα στο θέμα, μπορείτε πρώτα να ακούσετε το αντίστοιχο μπλοκ από , και μόνο τότε προχωρήστε σε μια λεπτομερή ανάλυση κάθε παραδείγματος:
#1: ευρετήριο "υποταξινόμηση"
Πότε κάνει
Εμφάνιση του πιο πρόσφατου τιμολογίου για τον πελάτη "LLC Kolokolchik".
Πώς να αναγνωρίσετε
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
συστάσεις
Ευρετήριο που χρησιμοποιείται επέκταση με πεδία ταξινόμησης.
Παράδειγμα:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Μπορείτε να παρατηρήσετε αμέσως ότι περισσότερες από 100 εγγραφές αφαιρέθηκαν από το ευρετήριο, οι οποίες στη συνέχεια ταξινομήθηκαν όλες και στη συνέχεια έμεινε η μόνη.
Διόρθωση:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ακόμη και σε ένα τόσο πρωτόγονο δείγμα - 8.5 φορές πιο γρήγορα και 33 φορές λιγότερες αναγνώσεις. Όσο περισσότερα «στοιχεία» έχετε για κάθε τιμή, τόσο πιο προφανές είναι το αποτέλεσμα fk.
Σημειώνω ότι ένα τέτοιο ευρετήριο θα λειτουργεί ως ευρετήριο "πρόθεμα" όχι χειρότερα από πριν για άλλα ερωτήματα με fk, όπου ταξινόμηση κατά pk δεν υπήρχε και δεν υπάρχει (μπορείτε να διαβάσετε περισσότερα για αυτό ). Συμπεριλαμβανομένων, θα παρέχει κανονικό ρητή υποστήριξη ξένου κλειδιού σε αυτό το πεδίο.
#2: τομή ευρετηρίου (BitmapAnd)
Πότε κάνει
Εμφάνιση όλων των συμφωνιών για τον πελάτη «LLC Kolokolchik», που έχουν συναφθεί για λογαριασμό της «NAO Buttercup».
Πώς να αναγνωρίσετε
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanσυστάσεις
δημιουργήσετε σύνθετος δείκτης κατά πεδία και από τα δύο αρχικά ή επεκτείνετε ένα από τα υπάρχοντα με πεδία από το δεύτερο.
Παράδειγμα:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Διόρθωση:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Η ανταμοιβή εδώ είναι μικρότερη, καθώς το Bitmap Heap Scan είναι αρκετά αποτελεσματικό από μόνο του. Αλλά τέλος πάντων 7 φορές πιο γρήγορα και 2.5 φορές λιγότερες αναγνώσεις.
#3: Συγχώνευση ευρετηρίων (BitmapOr)
Πότε κάνει
Εμφάνιση των πρώτων 20 παλαιότερων αιτημάτων "εμείς" ή μη εκχωρημένων αιτημάτων για επεξεργασία, με προτεραιότητα τη δική σας.
Πώς να αναγνωρίσετε
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanσυστάσεις
Χρήση ΕΝΩΣΗ [ΟΛΟΙ] για να συνδυάσετε υποερωτήματα για κάθε ένα από τα μπλοκ OR συνθηκών.
Παράδειγμα:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Διόρθωση:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Εκμεταλλευτήκαμε το γεγονός ότι και οι 20 απαιτούμενες εγγραφές ελήφθησαν αμέσως στο πρώτο μπλοκ, οπότε η δεύτερη, με το πιο «ακριβό» Bitmap Heap Scan, δεν εκτελέστηκε καν - στο τέλος 22 φορές ταχύτερα, 44 φορές λιγότερες αναγνώσεις!
Μια πιο λεπτομερής ιστορία σχετικά με αυτήν τη μέθοδο βελτιστοποίησης σε συγκεκριμένα παραδείγματα μπορεί να διαβαστεί σε άρθρα и .
Γενικευμένη έκδοση παραγγελία επιλογής με βάση πολλά κλειδιά (και όχι μόνο το ζεύγος const/NULL) συζητείται στο άρθρο .
#4: Διαβάζουμε πολλά περιττά πράγματα
Πότε κάνει
Κατά κανόνα, προκύπτει όταν θέλετε να "επισυνάψετε ένα άλλο φίλτρο" σε ένα ήδη υπάρχον αίτημα.
«Και δεν έχετε το ίδιο, αλλά με μαργαριταρένια κουμπιά? " ταινία "The Diamond Arm"
Για παράδειγμα, τροποποιώντας την παραπάνω εργασία, εμφανίστε τα πρώτα 20 παλαιότερα "κρίσιμα" αιτήματα για επεξεργασία, ανεξάρτητα από τον σκοπό τους.
Πώς να αναγνωρίσετε
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
συστάσεις
Δημιουργήστε [περισσότερα] εξειδικευμένα ευρετήριο με συνθήκη WHERE ή να συμπεριλάβετε επιπλέον πεδία στο ευρετήριο.
Εάν η κατάσταση του φίλτρου είναι "στατική" για τους σκοπούς σας - δηλαδή δεν συνεπάγεται επέκταση λίστα τιμών στο μέλλον - είναι καλύτερο να χρησιμοποιήσετε ένα ευρετήριο WHERE. Διάφορες καταστάσεις boolean/enum ταιριάζουν καλά σε αυτήν την κατηγορία.
Εάν η κατάσταση φιλτραρίσματος μπορεί να πάρει διαφορετικές έννοιες, τότε είναι καλύτερο να επεκτείνετε το ευρετήριο με αυτά τα πεδία - όπως στην περίπτωση με το BitmapAnd παραπάνω.
Παράδειγμα:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Διόρθωση:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Όπως μπορείτε να δείτε, το φιλτράρισμα έχει εξαφανιστεί εντελώς από το σχέδιο και το αίτημα έχει γίνει 5 φορές πιο γρήγορα.
#5: αραιό τραπέζι
Πότε κάνει
Διάφορες προσπάθειες να δημιουργήσετε τη δική σας ουρά επεξεργασίας εργασιών, όταν ένας μεγάλος αριθμός ενημερώσεων/διαγραφών εγγραφών στον πίνακα οδηγεί σε μια κατάσταση μεγάλου αριθμού «νεκρών» εγγραφών.
Πώς να αναγνωρίσετε
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
συστάσεις
Εκτελέστε τακτικά με το χέρι ΚΕΝΟ [ΠΛΗΡΕΣ] ή να επιτύχετε επαρκώς συχνή εκπαίδευση προσαρμόζοντας τις παραμέτρους του, συμπεριλαμβανομένων .
Στις περισσότερες περιπτώσεις, τέτοια προβλήματα προκαλούνται από κακή διάταξη ερωτημάτων κατά την κλήση από επιχειρηματική λογική όπως αυτά που συζητήθηκαν στο .
Αλλά πρέπει να καταλάβετε ότι ακόμη και το VACUUM FULL μπορεί να μην βοηθά πάντα. Για τέτοιες περιπτώσεις, αξίζει να εξοικειωθείτε με τον αλγόριθμο από το άρθρο .
#6: Ανάγνωση από τη «μέση» του δείκτη
Πότε κάνει
Φαίνεται ότι διαβάσαμε λίγο και όλα είχαν ευρετηριαστεί και δεν φιλτράραμε κανέναν υπερβολικά - αλλά παρόλα αυτά διαβάσαμε πολύ περισσότερες σελίδες από ό,τι θα θέλαμε.
Πώς να αναγνωρίσετε
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
συστάσεις
Ρίξτε μια προσεκτική ματιά στη δομή του ευρετηρίου που χρησιμοποιείται και στα βασικά πεδία που καθορίζονται στο ερώτημα - πιθανότατα μέρος του δείκτη δεν έχει οριστεί. Πιθανότατα θα πρέπει να δημιουργήσετε ένα παρόμοιο ευρετήριο, αλλά χωρίς τα πεδία προθέματος ή .
Παράδειγμα:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Όλα φαίνονται να είναι καλά, ακόμη και σύμφωνα με το ευρετήριο, αλλά είναι κάπως ύποπτο - για καθεμία από τις 20 εγγραφές που διαβάσαμε, έπρεπε να αφαιρέσουμε 4 σελίδες δεδομένων, 32 KB ανά εγγραφή - δεν είναι τολμηρό; Και το όνομα του ευρετηρίου tbl_fk_org_fk_cli_idx που προκαλεί σκέψη.
Διόρθωση:
CREATE INDEX ON tbl(fk_cli);
Ξαφνικά - 10 φορές πιο γρήγορα και 4 φορές λιγότερο στην ανάγνωση!
Άλλα παραδείγματα καταστάσεων αναποτελεσματικής χρήσης ευρετηρίων μπορείτε να δείτε στο άρθρο .
#7: CTE × CTE
Πότε κάνει
Κατόπιν αιτήματος σημείωσε «χοντρό» CTE από διαφορετικά τραπέζια, και στη συνέχεια αποφάσισε να το κάνει μεταξύ τους JOIN.
Η υπόθεση είναι σχετική για εκδόσεις κάτω από το v12 ή αιτήματα με WITH MATERIALIZED.
Πώς να αναγνωρίσετε
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
συστάσεις
Αναλύστε προσεκτικά το αίτημα - και ? Αν ναι, τότε εφαρμόστε το "λεξικό" στο hstore/json σύμφωνα με το μοντέλο που περιγράφεται στο .
#8: εναλλαγή σε δίσκο (εγγραφή θερμοκρασίας)
Πότε κάνει
Η εφάπαξ επεξεργασία (ταξινόμηση ή μοναδικοποίηση) μεγάλου αριθμού εγγραφών δεν χωράει στη μνήμη που έχει διατεθεί για αυτό.
Πώς να αναγνωρίσετε
-> *
&& temp written > 0συστάσεις
Εάν η ποσότητα της μνήμης που χρησιμοποιείται από τη λειτουργία δεν υπερβαίνει κατά πολύ την καθορισμένη τιμή της παραμέτρου , αξίζει να το διορθώσετε. Μπορείτε αμέσως να εισάγετε τις ρυθμίσεις για όλους ή μπορείτε να το κάνετε μέσω SET [LOCAL] για συγκεκριμένο αίτημα/συναλλαγή.
Παράδειγμα:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Διόρθωση:
SET work_mem = '128MB'; -- перед выполнением запроса
Για προφανείς λόγους, εάν χρησιμοποιείται μόνο μνήμη και όχι δίσκος, τότε το ερώτημα θα εκτελεστεί πολύ πιο γρήγορα. Ταυτόχρονα, αφαιρείται και μέρος του φορτίου από τον σκληρό δίσκο.
Αλλά πρέπει να καταλάβετε ότι δεν θα μπορείτε πάντα να διαθέτετε πολλή μνήμη - απλά δεν θα είναι αρκετή για όλους.
#9: άσχετα στατιστικά
Πότε κάνει
Έχυσαν πολλά στη βάση δεδομένων ταυτόχρονα, αλλά δεν είχαν χρόνο να τη διώξουν ANALYZE.
Πώς να αναγνωρίσετε
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10συστάσεις
Πραγματοποιήστε το ANALYZE.
Αυτή η κατάσταση περιγράφεται λεπτομερέστερα στο .
#10: "κάτι πήγε στραβά"
Πότε κάνει
Υπήρχε μια αναμονή για ένα κλείδωμα που επιβλήθηκε από ένα ανταγωνιστικό αίτημα ή δεν υπήρχαν επαρκείς πόροι υλικού CPU/υπερεπόπτη.
Πώς να αναγνωρίσετε
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
συστάσεις
Χρησιμοποιήστε εξωτερική σύστημα παρακολούθησης διακομιστή για αποκλεισμό ή μη φυσιολογική κατανάλωση πόρων. Έχουμε ήδη μιλήσει για την έκδοσή μας για την οργάνωση αυτής της διαδικασίας για εκατοντάδες διακομιστές и .
Πηγή: www.habr.com