חסוך אגורה על נפחים גדולים ב-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), ואינו מאפשר ל-tuples להשתרע על פני מספר עמודים. לכן, אי אפשר לאחסן ישירות ערכי שדות גדולים מאוד. כדי להתגבר על מגבלה זו, ערכי שדה גדולים נדחסים ו/או מפוצלים על פני מספר קווים פיזיים. זה קורה מבלי לשים לב למשתמש ויש לו השפעה מועטה על רוב קוד השרת. שיטה זו ידועה בשם TOAST...

למעשה, עבור כל שולחן עם שדות "פוטנציאליים גדולים", באופן אוטומטי נוצרת טבלה מזווגת עם "חיתוך". כל רשומה "גדולה" במקטעים של 2KB:

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

כלומר, אם עלינו לכתוב מחרוזת עם ערך "גדול". data, אז ההקלטה האמיתית תתרחש לא רק לשולחן הראשי ול-PK שלו, אלא גם ל-TOAST ול-PK שלו.

הפחתת השפעת 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 פחות, אשר פרק לא רק את הדיסק, אלא גם את המעבד:

אציין כי הפכנו לקטנים גם ב"קריאת" הדיסק, לא רק ב"כתיבה" - שכן בעת ​​הכנסת רשומה לטבלה, עלינו "לקרוא" גם חלק מהעץ של כל אינדקס כדי לקבוע את תפקיד עתידי בהם.

מי יכול לחיות טוב על PostgreSQL 11

לאחר עדכון ל-PG11, החלטנו להמשיך "לכוון" TOAST ושמנו לב שהחל מגרסה זו הפרמטר הפך זמין לכוונון toast_tuple_target:

קוד העיבוד של TOAST מופעל רק כאשר ערך השורה שיש לאחסן בטבלה גדול מ-TOAST_TUPLE_THRESHOLD בתים (בדרך כלל 2 KB). קוד ה-TOAST ידחוס ו/או יעביר ערכי שדות מהטבלה עד שערך השורה יפחת מ-TOAST_TUPLE_TARGET בתים (ערך משתנה, גם בדרך כלל 2 KB) או שלא ניתן להקטין את הגודל.

החלטנו שהנתונים שיש לנו בדרך כלל הם "קצרים מאוד" או "ארוכים מאוד", אז החלטנו להגביל את עצמנו לערך המינימלי האפשרי:

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

בואו נראה כיצד ההגדרות החדשות השפיעו על טעינת הדיסק לאחר הגדרה מחדש:

לא רע! מְמוּצָע התור לדיסק ירד בערך 1.5 פעמים, והדיסק "עסוק" הוא 20 אחוז! אבל אולי זה השפיע איכשהו על המעבד?

לפחות זה לא נהיה יותר גרוע. למרות שקשה לשפוט אם אפילו נפחים כאלה עדיין לא יכולים להעלות את עומס המעבד הממוצע גבוה יותר 5%.

על ידי שינוי מקומות המונחים, הסכום... משתנה!

כפי שאתה יודע, אגורה חוסכת רובל, ועם נפחי האחסון שלנו זה בערך 10TB/חודש אפילו אופטימיזציה קטנה יכולה לתת רווח טוב. לכן, שמנו לב למבנה הפיזי של הנתונים שלנו – איך בדיוק שדות "מוערמים" בתוך הרשומה כל אחד מהשולחנות.

כי בגלל יישור נתונים זה ישר קדימה משפיע על הנפח המתקבל:

ארכיטקטורות רבות מספקות יישור נתונים על גבולות מילות מכונה. לדוגמה, במערכת x32 של 86 סיביות, מספרים שלמים (סוג מספר שלם, 4 בתים) יהיו מיושרים על גבול מילים של 4 בתים, וכך גם מספרי נקודה צפה דיוק כפולה (נקודה צפה כפולה, 8 בתים). ובמערכת של 64 סיביות, ערכים כפולים יהיו מיושרים לגבולות מילים של 8 בתים. זוהי סיבה נוספת לחוסר התאמה.

עקב יישור, גודל שורת טבלה תלוי בסדר השדות. בדרך כלל השפעה זו אינה בולטת במיוחד, אך במקרים מסוימים היא עלולה להוביל לגידול משמעותי בגודל. לדוגמה, אם אתה מערבב שדות char(1) ושדות שלמים, בדרך כלל יתבזבזו ביניהם 3 בתים.

נתחיל עם דגמים סינתטיים:

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 байт

מאיפה הגיעו כמה בתים נוספים במקרה הראשון? זה פשוט - 2-byte smallint מיושר על גבול 4-byte לפני השדה הבא, וכאשר הוא האחרון, אין שום דבר ואין צורך ליישר.

בתיאוריה, הכל בסדר ואתה יכול לסדר מחדש את השדות כרצונך. בואו נבדוק את זה על נתונים אמיתיים באמצעות הדוגמה של אחת הטבלאות, שהחלק היומי שלה תופס 10-15GB.

מבנה ראשוני:

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