אינדקס בריאות ב-PostgreSQL דרך עיניו של מפתח Java

שלום.

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

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

כתב ויתור

הגרסה הראשית של PostgreSQL איתה אני עובד היא 10. כל שאילתות ה-SQL שבהן אני משתמש נבדקות גם בגרסה 11. הגרסה המינימלית הנתמכת היא 9.6.

פרהיסטוריה

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

בעיה ראשונה - תצורת ברירת מחדל

כנראה שכולם די עייפים מהמטאפורה על Postgres, שאפשר להפעיל על מכונת קפה, אבל... תצורת ברירת המחדל באמת מעלה מספר שאלות. לכל הפחות כדאי לשים לב תחזוקה_עבודת_מזכר, temp_file_limit, statement_timeout и lock_timeout.

במקרה שלנו תחזוקה_עבודת_מזכר היה ברירת המחדל 64 MB, ו temp_file_limit משהו בסביבות 2 GB - פשוט לא היה לנו מספיק זיכרון כדי ליצור אינדקס על שולחן גדול.

לכן, ב pg-index-health אספתי סדרה מַפְתֵחַ, לדעתי, הפרמטרים שיש להגדיר עבור כל מסד נתונים.

בעיה שניה - כפול אינדקסים

מסדי הנתונים שלנו חיים על כונני SSD, ואנחנו משתמשים בהם HA-תצורה עם מספר מרכזי נתונים, מארח מאסטר ו n-מספר העתקים. שטח דיסק הוא משאב בעל ערך רב עבורנו; זה לא פחות חשוב מביצועים וצריכת מעבד. לכן מצד אחד אנחנו צריכים אינדקסים לקריאה מהירה ומצד שני לא רוצים לראות אינדקסים מיותרים בבסיס הנתונים שכן הם אוכלים מקום ומאטים את עדכון הנתונים.

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

בעיה שלישית - מדדים מצטלבים

רוב המפתחים המתחילים יוצרים אינדקסים על עמודה אחת. בהדרגה, לאחר שהתנסו היטב בעסק הזה, אנשים מתחילים לייעל את השאילתות שלהם ולהוסיף אינדקסים מורכבים יותר הכוללים מספר עמודות. כך מופיעים אינדקסים על עמודות A, A + B, A + B + C וכולי. ניתן לזרוק את שני הראשונים מהמדדים הללו בבטחה, מכיוון שהם קידומות של השלישי. זה גם חוסך הרבה מקום בדיסק ויש לזה אבחון intersected_indexes.

בעיה רביעית - מפתחות זרים ללא אינדקסים

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

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

delete from <table> where id in (…)

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

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

בעיה חמישית - ערך ריק באינדקסים

כברירת מחדל, Postgres כולל ערכי null באינדקסים של btree, אך בדרך כלל אין בהם צורך שם. לכן, אני מנסה בחריצות לזרוק את האפסים האלה (אבחון indexes_with_null_values), יצירת אינדקסים חלקיים על עמודות ניתנות לאפס לפי סוג where <A> is not null. בדרך זו הצלחתי להקטין את הגודל של אחד מהאינדקסים שלנו מ-1877 מגה-בייט ל-16 ק"ב. ובאחד השירותים, גודל מסד הנתונים ירד בסך הכל ב-16% (ב-4.3 ג'יגה-בייט במספרים מוחלטים) עקב אי הכללת ערכי אפס מהאינדקסים. חיסכון עצום בשטח דיסק עם שינויים פשוטים מאוד. 🙂

בעיה שש - חוסר במפתחות ראשיים

בשל אופי המנגנון MVCC בפוסטגרס מצב כזה אפשרי לְהַתְפִּיחַכאשר גודל השולחן שלך גדל במהירות עקב מספר רב של רשומות מתות. האמנתי בתמימות שזה לא יאיים עלינו, ושזה לא יקרה לבסיס שלנו, כי אנחנו, וואו!!!, מפתחים נורמליים... כמה טיפש ותמים הייתי...

יום אחד, הגירה נפלאה אחת לקחה ועדכנה את כל הרשומות בטבלה גדולה ובשימוש פעיל. קיבלנו +100 ג'יגה-בייט לגודל השולחן באופן ישיר. זה היה חבל, אבל התקלות שלנו לא הסתיימו שם. לאחר שהשואב האוטומטי בשולחן הזה הסתיים 15 שעות לאחר מכן, התברר שהמיקום הפיזי לא יחזור. לא יכולנו להפסיק את השירות ולהפוך את VACUUM FULL, אז החלטנו להשתמש pg_repack. ואז התברר ש pg_repack לא יודע לעבד טבלאות ללא מפתח ראשי או אילוץ ייחוד אחר, ובטבלה שלנו לא היה מפתח ראשי. כך נולדה האבחון tables_without_primary_key.

בגרסת הספרייה 0.1.5 נוספה היכולת לאסוף נתונים מנפיחות של טבלאות ואינדקסים ולהגיב אליהם בזמן.

בעיות שבע ושמונה - אינדקסים מספיקים ואינדקסים שאינם בשימוש

שני האבחונים הבאים הם: טבלאות_עם_אינדקסים_חסרים и unused_indexes – הופיעו בצורתם הסופית יחסית לאחרונה. הנקודה היא שאי אפשר היה פשוט לקחת ולהוסיף אותם.

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

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

כמסקנה

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

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

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

אני באמת מקווה ש pg-index-health יהיה שימושי ומבוקש. תוכל גם לתרום לפיתוח הספרייה על ידי דיווח על בעיות שמצאת והצעת אבחון חדש.

מקור: www.habr.com