מדידה וטיפול בנפח מוגזם של טבלאות ואינדקסים

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

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

קריאת הפלט של pgstattuple

כשמשתמשים בפקודה pgstattuple כדי לדווח על מאפייני האחסון הפיזי של טבלה או אינדקס, הפלט הוא בפורמט הבא:

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 7431888896
tuple_count        | 66666666
tuple_len          | 2796296271
tuple_percent      | 37.63
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 3873153652
free_percent       | 52.12

הפלט מתואר באופן הבא:

table_len (7431888896)

הגודל הכולל של הטבלה בבייטים. הוא כולל את כל הטופלים הפעילים, הטופלים הלא פעילים והשטח הפנוי. במקרה הזה, large_table תופס נפח של כ-7,088 GB.

tuple_count (66666666)

מייצג את מספר הטפלים הפעילים – שורות גלויות ופעילות – שנמצאים בטבלה. הטבלה מכילה בערך 66 מיליון שורות פעילות.

tuple_len (2796296271)

‫

האורך הכולל בבייטים של כל הטופלים של השידור החי ביחד, שהוא בערך 2.6 GB.

tuple_percent (37.63)

אחוז המקום שתופסים טפלים פעילים בtable_len. החישוב מתבצע כך:

‫(tuple_len/table_len) ∗ 100 = (2796296271/7431888896) ∗ 100 ≈ 37.63 percent

הפלט הזה מציין שהנתונים הפעילים תופסים פחות מ-40% משטח הדיסק של הטבלה.

dead_tuple_count (0)

מספר הטופלים המתים – שורות שפעולות הניקוי עדיין לא הסירו אחרי עדכונים או מחיקות. ערך של 0 מציין שהטבלה נקייה ואין בה טפלים מתים שממתינים לשחזור. זה בדרך כלל מצביע על כך ש-VACUUM או autovacuum פועלים ביעילות בטבלה.

dead_tuple_len (0)

האורך הכולל בבייטים של כל הטפלים המתים. בהתאם לערך dead_tuple_count, ערך של 0 מציין ששורות מתות לא תופסות מקום.

dead_tuple_percent (0)

האחוז של table_len שתופסים טאפלים לא פעילים. החישוב מתבצע כך:

‫(dead_tuple_len/table_len) ∗ 100 = (0/7431888896) ∗ 100 = 0 אחוז

free_space (3873153652)

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

free_percent (52.12)

הערך הזה מייצג את אחוז השטח הפנוי מתוך table_len. החישוב מתבצע באופן הבא:

‫(free_space/table_len) ∗ 100 = (3873153652/7431888896) ∗ 100 ≈ 52.12 אחוז

הערכת ניפוח באמצעות שאילתות

אפשר להעריך את הנפח המנופח באמצעות שאילתות מול טבלאות קטלוג המערכת, אבל השיטה הזו פחות מדויקת מאשר שימוש בתוספים כמו pgstattuple. אם משתמשים בסקריפטים כמו PostgreSQL table bloat check, הנתונים הסטטיסטיים של הטבלה צריכים להיות עדכניים, אחרת ההערכה לא תהיה מדויקת.

כשמשווים את הפלט של שאילתה עם מודול pgstattuple, האומדנים ניתנים להשוואה, אבל הם לא מדידות מדויקות של נפח הנתונים המיותרים. המודול עדיין מספק מדד מדויק יותר של ניפוח, כי שאילתת ה-SQL מסתמכת על מנוע הנתונים הסטטיסטיים המצטברים של PostgreSQL, ואילו המודול קורא את דפי הנתונים בפועל.

בדוגמה הבאה מוצגת השוואה בין הפלט של שתי שיטות שונות לניתוח נפח הטבלה ב-PostgreSQL עבור טבלה בשם large_table.

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 2475892736
tuple_count        | 22000000
tuple_len          | 922925931
tuple_percent      | 37.28
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1293696900
free_percent       | 52.25

Vs.

-[ RECORD 1 ]+------------
databasename | postgres
schemaname   | public
tablename    | large_table
can_estimate | t
est_rows     | 21965400
pct_bloat    | 54
mb_bloat     | 1269.24
table_mb     | 2361.195

ריבוי כתובות

אפשר לנהל את הניפוח בדרכים הבאות, שלכל אחת מהן יש יתרונות וחסרונות, ולכן צריך לתכנן בקפידה:

• מבצעים שאיבת אבק מלאה. הפעולה הזו מחייבת נעילה מלאה של הטבלה למשך הפעולה, ודורשת השבתה.
• העברת נתונים לטבלה חדשה. כדי להשתמש בשיטה הזו, צריך להשהות את השינויים בטבלה כדי לוודא שהשינויים לא מועברים ליחס החדש.
• שימוש ב-pg_squeeze. התוסף הזה מצמצם את נפח הנתונים המיותרים באינטרנט. התוסף יוצר טבלת צללים, מוסיף את כל הנתונים ואז מתחזק את השינויים בטבלה באמצעות משבצת שכפול עד לשלב האחרון, שבו הוא מחליף את הטבלה המנופחת בטבלה החדשה שנוצרה ולא מנופחת. לאחר מכן, המערכת מבקשת לנעול את הטבלה לזמן קצר כדי להעביר את הטבלה הלא מנופחת ולהחליף איתה את הטבלה המנופחת. השיטה הזו דורשת כפול מהנפח של האובייקט למשך הפעולה, אבל היא הכי פחות פולשנית לפעולה רגילה.