Εξοικονομήστε μια δεκάρα σε μεγάλους όγκους στο PostgreSQL

Συνέχιση του θέματος της καταγραφής μεγάλων ροών δεδομένων που τέθηκαν από προηγούμενο άρθρο σχετικά με την κατάτμηση, σε αυτό θα εξετάσουμε τους τρόπους με τους οποίους μπορείτε μειώστε το «φυσικό» μέγεθος του αποθηκευμένου στο PostgreSQL και τον αντίκτυπό τους στην απόδοση του διακομιστή.

Θα μιλήσουμε για Ρυθμίσεις TOAST και ευθυγράμμιση δεδομένων. "Κατά μέσο όρο", αυτές οι μέθοδοι δεν θα εξοικονομήσουν πάρα πολλούς πόρους, αλλά χωρίς να τροποποιήσουν καθόλου τον κώδικα της εφαρμογής.

Ωστόσο, η εμπειρία μας αποδείχθηκε πολύ παραγωγική ως προς αυτό, καθώς η αποθήκευση σχεδόν οποιασδήποτε παρακολούθησης από τη φύση της είναι ως επί το πλείστον μόνο προσαρτήματα όσον αφορά τα καταγεγραμμένα στοιχεία. Και αν αναρωτιέστε πώς μπορείτε να διδάξετε τη βάση δεδομένων να γράφει στον δίσκο 200MB / s τα μισά - παρακαλώ κάτω από γάτα.

Μικρά μυστικά μεγάλων δεδομένων

Ανά προφίλ εργασίας η υπηρεσία μας, του πετάνε τακτικά από τα λημέρια πακέτα κειμένου.

Και από τότε Σύμπλεγμα VLSIτου οποίου η βάση δεδομένων παρακολουθούμε είναι ένα προϊόν πολλαπλών συστατικών με πολύπλοκες δομές δεδομένων και στη συνέχεια ερωτήματα για μέγιστη απόδοση βγουν αρκετά έτσι «Πολυτόμος» με σύνθετη αλγοριθμική λογική. Έτσι, ο όγκος κάθε μεμονωμένης παρουσίας ενός αιτήματος ή του σχεδίου εκτέλεσης που προκύπτει στο αρχείο καταγραφής που έρχεται σε εμάς αποδεικνύεται ότι είναι «κατά μέσο όρο» αρκετά μεγάλος.

Ας δούμε τη δομή ενός από τους πίνακες στον οποίο γράφουμε "ακατέργαστα" δεδομένα - δηλαδή, εδώ είναι το αρχικό κείμενο από την καταχώριση καταγραφής:

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

Ένα τυπικό σημάδι (ήδη κομμένο, φυσικά, άρα αυτό είναι ένα πρότυπο ενότητας), όπου το πιο σημαντικό πράγμα είναι το κείμενο. Μερικές φορές αρκετά ογκώδες.

Θυμηθείτε ότι το «φυσικό» μέγεθος μιας εγγραφής σε ένα PG δεν μπορεί να καταλαμβάνει περισσότερες από μία σελίδες δεδομένων, αλλά το «λογικό» μέγεθος είναι εντελώς διαφορετικό θέμα. Για να γράψετε μια ογκομετρική τιμή (varchar/text/bytea) σε ένα πεδίο, χρησιμοποιήστε το Τεχνολογία TOAST:

Η PostgreSQL χρησιμοποιεί ένα σταθερό μέγεθος σελίδας (συνήθως 8 KB) και δεν επιτρέπει στις πλειάδες να εκτείνονται σε πολλές σελίδες. Επομένως, είναι αδύνατο να αποθηκεύσετε απευθείας πολύ μεγάλες τιμές πεδίου. Για να ξεπεραστεί αυτός ο περιορισμός, οι μεγάλες τιμές πεδίου συμπιέζονται και/ή χωρίζονται σε πολλές φυσικές γραμμές. Αυτό συμβαίνει απαρατήρητο από τον χρήστη και έχει μικρή επίδραση στον περισσότερο κώδικα διακομιστή. Αυτή η μέθοδος είναι γνωστή ως τοστ...

Μάλιστα, για κάθε πίνακα με πεδία «δυνητικά μεγάλα», αυτόματα δημιουργείται ένας ζευγαρωμένος πίνακας με "slicing". κάθε «μεγάλη» εγγραφή σε τμήματα 2 KB:

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

Δηλαδή, αν πρέπει να γράψουμε μια συμβολοσειρά με τιμή “large”. data, τότε θα γίνει η πραγματική εγγραφή όχι μόνο στο κυρίως τραπέζι και το ΠΚ του, αλλά και στο TOAST και το ΠΚ του.

Μείωση της επιρροής TOAST

Αλλά τα περισσότερα από τα ρεκόρ μας δεν είναι ακόμα τόσο μεγάλα, πρέπει να χωράει σε 8KB - Πώς μπορώ να εξοικονομήσω χρήματα σε αυτό;

Εδώ είναι που το χαρακτηριστικό έρχεται να μας βοηθήσει STORAGE στη στήλη του πίνακα:

• ΕΠΕΚΤΑΘΗΚΕ επιτρέπει τόσο συμπίεση όσο και χωριστή αποθήκευση. Αυτό τυπική επιλογή για τους περισσότερους τύπους δεδομένων που είναι συμβατοί με το TOAST. Πρώτα επιχειρεί να εκτελέσει συμπίεση και, στη συνέχεια, την αποθηκεύει έξω από τον πίνακα, εάν η σειρά είναι ακόμα πολύ μεγάλη.
• ΚΥΡΙΩΣ επιτρέπει τη συμπίεση αλλά όχι ξεχωριστή αποθήκευση. (Στην πραγματικότητα, η χωριστή αποθήκευση θα εξακολουθήσει να εκτελείται για τέτοιες στήλες, αλλά μόνο ως έσχατη λύση, όταν δεν υπάρχει άλλος τρόπος να συρρικνωθεί η συμβολοσειρά ώστε να χωράει στη σελίδα.)

Στην πραγματικότητα, αυτό ακριβώς χρειαζόμαστε για το κείμενο - συμπιέστε το όσο το δυνατόν περισσότερο και αν δεν χωράει καθόλου, βάλτε το σε TOAST. Αυτό μπορεί να γίνει άμεσα, με μία εντολή:

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

Πώς να αξιολογήσετε το αποτέλεσμα

Δεδομένου ότι η ροή δεδομένων αλλάζει κάθε μέρα, δεν μπορούμε να συγκρίνουμε απόλυτους αριθμούς, αλλά σε σχετικούς όρους μικρότερο μερίδιο Το γράψαμε στο TOAST - τόσο το καλύτερο. Αλλά εδώ υπάρχει ένας κίνδυνος - όσο μεγαλύτερος είναι ο «φυσικός» όγκος κάθε μεμονωμένης εγγραφής, τόσο πιο «πλατύς» γίνεται ο δείκτης, επειδή πρέπει να καλύψουμε περισσότερες σελίδες δεδομένων.

Τμήμα πριν τις αλλαγές:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

Τμήμα μετά από αλλαγές:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

Στην πραγματικότητα, εμείς άρχισε να γράφει στο TOAST 2 φορές λιγότερο συχνά, το οποίο ξεφόρτωσε όχι μόνο το δίσκο, αλλά και την CPU:

Θα σημειώσω ότι έχουμε γίνει μικρότεροι και στην «ανάγνωση» του δίσκου, όχι μόνο στη «γραφή» - αφού κατά την εισαγωγή μιας εγγραφής σε έναν πίνακα, πρέπει επίσης να «διαβάζουμε» μέρος του δέντρου κάθε ευρετηρίου για να προσδιορίσουμε το μελλοντική θέση σε αυτά.

Ποιος μπορεί να ζήσει καλά στο PostgreSQL 11

Μετά την ενημέρωση στο PG11, αποφασίσαμε να συνεχίσουμε να "συντονίζουμε" TOAST και παρατηρήσαμε ότι ξεκινώντας από αυτήν την έκδοση η παράμετρος έγινε διαθέσιμη για συντονισμό toast_tuple_target:

Ο κώδικας επεξεργασίας TOAST ενεργοποιείται μόνο όταν η τιμή γραμμής που θα αποθηκευτεί στον πίνακα είναι μεγαλύτερη από TOAST_TUPLE_THRESHOLD byte (συνήθως 2 KB). Ο κωδικός TOAST θα συμπιέσει και/ή θα μετακινήσει τις τιμές των πεδίων έξω από τον πίνακα έως ότου η τιμή της σειράς γίνει μικρότερη από TOAST_TUPLE_TARGET byte (μεταβλητή τιμή, επίσης συνήθως 2 KB) ή το μέγεθος δεν μπορεί να μειωθεί.

Αποφασίσαμε ότι τα δεδομένα που έχουμε συνήθως είναι είτε "πολύ σύντομα" ή "πολύ μεγάλα", οπότε αποφασίσαμε να περιοριστούμε στην ελάχιστη δυνατή τιμή:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

Ας δούμε πώς οι νέες ρυθμίσεις επηρέασαν τη φόρτωση του δίσκου μετά την επαναδιαμόρφωση:

Καθόλου άσχημα! Μέση τιμή η ουρά στο δίσκο έχει μειωθεί περίπου 1.5 φορές και ο δίσκος "απασχολημένος" είναι 20 τοις εκατό! Αλλά ίσως αυτό να επηρέασε κατά κάποιο τρόπο την CPU;

Τουλάχιστον δεν έγινε χειρότερο. Ωστόσο, είναι δύσκολο να κρίνουμε εάν ακόμη και τέτοιοι όγκοι δεν μπορούν να αυξήσουν το μέσο φορτίο της CPU 5%.

Αλλάζοντας τις θέσεις των όρων, το άθροισμα... αλλάζει!

Όπως γνωρίζετε, μια δεκάρα εξοικονομεί ένα ρούβλι, και με τους όγκους αποθήκευσης μας είναι περίπου 10 TB/μήνα ακόμη και μια μικρή βελτιστοποίηση μπορεί να δώσει ένα καλό κέρδος. Επομένως, δώσαμε προσοχή στη φυσική δομή των δεδομένων μας - πώς ακριβώς «στοιβάζονται» πεδία μέσα στην εγγραφή καθένα από τα τραπέζια.

Επειδή λόγω ευθυγράμμιση δεδομένων αυτό είναι ευθύ επηρεάζει τον όγκο που προκύπτει:

Πολλές αρχιτεκτονικές παρέχουν ευθυγράμμιση δεδομένων στα όρια λέξεων μηχανής. Για παράδειγμα, σε ένα σύστημα x32 86 bit, οι ακέραιοι (τύπος ακέραιου αριθμού, 4 byte) θα ευθυγραμμιστούν σε ένα όριο λέξης 4 byte, όπως και οι αριθμοί κινητής υποδιαστολής διπλής ακρίβειας (διπλής ακρίβειας κινητής υποδιαστολής, 8 byte). Και σε ένα σύστημα 64 bit, οι διπλές τιμές θα ευθυγραμμιστούν σε όρια λέξεων 8 byte. Αυτός είναι ένας ακόμη λόγος ασυμβατότητας.

Λόγω της στοίχισης, το μέγεθος μιας γραμμής πίνακα εξαρτάται από τη σειρά των πεδίων. Συνήθως αυτή η επίδραση δεν είναι πολύ αισθητή, αλλά σε ορισμένες περιπτώσεις μπορεί να οδηγήσει σε σημαντική αύξηση του μεγέθους. Για παράδειγμα, αν συνδυάσετε πεδία char(1) και ακέραιο, συνήθως θα σπαταληθούν 3 byte μεταξύ τους.

Ας ξεκινήσουμε με τα συνθετικά μοντέλα:

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

Από πού προήλθαν μερικά επιπλέον byte στην πρώτη περίπτωση; Είναι απλό - Μικρόχρωμα 2 byte ευθυγραμμισμένα σε όριο 4 byte πριν από το επόμενο πεδίο, και όταν είναι το τελευταίο, δεν υπάρχει τίποτα και δεν χρειάζεται να ευθυγραμμιστεί.

Θεωρητικά, όλα είναι καλά και μπορείτε να αναδιατάξετε τα πεδία όπως θέλετε. Ας το ελέγξουμε σε πραγματικά δεδομένα χρησιμοποιώντας το παράδειγμα ενός από τους πίνακες, η ημερήσια ενότητα του οποίου καταλαμβάνει 10-15 GB.

Αρχική δομή:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

Ενότητα μετά την αλλαγή της σειράς στηλών - ακριβώς ίδια πεδία, απλά διαφορετική σειρά:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

Ο συνολικός όγκος του τμήματος καθορίζεται από τον αριθμό των "γεγονότων" και εξαρτάται μόνο από εξωτερικές διεργασίες, οπότε ας διαιρέσουμε το μέγεθος του σωρού (pg_relation_size) από τον αριθμό των εγγραφών σε αυτό - δηλαδή, παίρνουμε μέσο μέγεθος της πραγματικής αποθηκευμένης εγγραφής:

Μείον 6% όγκος, Εξαιρετική!

Αλλά όλα, φυσικά, δεν είναι τόσο ρόδινα - τελικά, στα ευρετήρια δεν μπορούμε να αλλάξουμε τη σειρά των πεδίων, και επομένως «γενικά» (pg_total_relation_size) ...

...ακόμα κι εδώ εξοικονόμηση 1.5%χωρίς να αλλάξετε ούτε μια γραμμή κώδικα. Ναι ναι!

Σημειώνω ότι η παραπάνω επιλογή για τακτοποίηση πεδίων δεν είναι το γεγονός ότι είναι η πιο βέλτιστη. Επειδή δεν θέλετε να "σκίσετε" μερικά πεδία για αισθητικούς λόγους - για παράδειγμα, ένα ζευγάρι (pack, recno), που είναι το PK για αυτόν τον πίνακα.

Γενικά, ο προσδιορισμός της «ελάχιστης» διάταξης των πεδίων είναι μια αρκετά απλή εργασία «ωμής δύναμης». Επομένως, μπορείτε να έχετε ακόμα καλύτερα αποτελέσματα από τα δεδομένα σας από τα δικά μας - δοκιμάστε το!

Πηγή: www.habr.com