אני מציע לך לקרוא את תמליל הדו"ח של ולדימיר סיטניקוב מתחילת 2016, "PostgreSQL ו-JDBC: סחיטת כל המרכיבים"
שלום! שמי ולדימיר סיטניקוב. אני עובד ב-NetCracker כבר 10 שנים. ואני עוסק בעיקר בביצועים. כל מה שקשור לג'אווה, כל מה שקשור ל-SQL - זה מה שאני אוהב.
והיום אספר לכם על מה שנתקלנו בו בחברה כשהתחלנו להשתמש ב-PostgreSQL כשרת מסד נתונים. ואנחנו עובדים בעיקר עם ג'אווה. אבל מה שאספר לכם היום לא קשור רק לג'אווה. כפי שהראת התרגול, זה קורה גם בשפות אחרות.
נדבר:
- לגבי דגימת נתונים.
- לגבי שמירת נתונים.
- וגם לגבי פרודוקטיביות.
- ולגבי המגרפות התת-ימיות שקבורות שם.
נתחיל עם שאלה פשוטה. אנו בוחרים שורה אחת מטבלה לפי מפתח ראשי.
מסד הנתונים נמצא על אותו מחשב. וכל הדברים האלה לוקחים 20 מילישניות.
20 מילישניות אלו הן המון. אם יש לך 100 בקשות כאלה, אז אתה מבזבז זמן בכל שנייה כדי לגלול בין הבקשות הללו, כלומר אנחנו מבזבזים זמן.
אנחנו לא אוהבים לעשות את זה ואנחנו בוחנים מה מסד הנתונים מציע לנו לשם כך. מסד הנתונים מציע לנו שתי אפשרויות לביצוע שאילתות.
האפשרות הראשונה היא בקשה פשוטה. מה טוב בה? כי אנחנו מקבלים אותה ושולחים אותה, ולא יותר מזה.
למסד הנתונים יש גם שאילתה מורחבת, שהיא מתוחכמת יותר אך פונקציונלית יותר. ניתן לשלוח שאילתה בנפרד לצורך ניתוח, ביצוע, קשירת משתנים וכו'.
שאילתה מורחבת במיוחד היא משהו שלא נסקור בדוח הנוכחי. ייתכן שנרצה משהו מהבסיס ויש רשימת משאלות שנוצרה בצורה כלשהי, כלומר זה מה שאנחנו רוצים, אבל זה בלתי אפשרי עכשיו ובשנה הקרובה. לכן, פשוט רשמנו את זה ונסתובב וננער את האנשים העיקריים.
ומה שאנחנו יכולים לעשות זה שאילתה פשוטה ושאילתה מורחבת.
מה מיוחד בכל גישה?
שאילתה פשוטה טובה לביצוע חד פעמי. הפעילו אותה פעם אחת ושכחו ממנה. והבעיה היא שהיא לא תומכת בפורמט נתונים בינארי, כלומר היא לא מתאימה למערכות בעלות ביצועים גבוהים.
שאילתה מורחבת – מאפשרת לך לחסוך זמן בניתוח. זה מה שעשינו והתחלנו להשתמש בו. זה עזר לנו מאוד. יש לא רק חיסכון בניתוח. יש חיסכון בהעברת נתונים. העברת נתונים בפורמט בינארי יעילה הרבה יותר.
בואו נעבור לתרגול. כך נראית אפליקציה טיפוסית. זה יכול להיות ג'אווה וכו'.
יצרנו משפט. ביצענו פקודה. יצרנו סגירה. איפה השגיאה כאן? מה הבעיה? אין בעיה. כך זה כתוב בכל הספרים. כך זה צריך להיות כתוב. אם אתם רוצים ביצועים מקסימליים, כתבו כך.
אבל התרגול הראה שזה לא עובד. למה? כי יש לנו שיטה שנקראת "close". וכשאנחנו עושים את זה, מנקודת מבט של מסד נתונים מתברר - זה כמו מעשן שעובד עם מסד נתונים. אמרנו "PARSE EXECUTE DEALLOCATE".
למה כל כך הרבה יצירות ופריקות של משפטים? אף אחד לא צריך אותם. אבל בדרך כלל ב-PreparedStatement קורה שכאשר אנחנו סוגרים אותם, הם סוגרים הכל במסד הנתונים. זה לא מה שאנחנו רוצים.
אנחנו רוצים לעבוד עם מסד הנתונים כמו אנשים בריאים. אנחנו לוקחים ומכינים את המשפט שלנו פעם אחת, ואז מבצעים אותו פעמים רבות. למעשה, פעמים רבות פירוש הדבר שאנחנו מנתחים אותו פעם אחת בכל חיי האפליקציה. ואנחנו משתמשים באותו מזהה משפט על מערכות REST שונות. זוהי המטרה שלנו.
כיצד נוכל להשיג זאת?
זה מאוד פשוט - אין צורך לסגור פקודות. אנחנו כותבים כך: "להכין" "לבצע".
אם נפעיל משהו כזה, ברור שמשהו יעלה על גדותיו איפשהו. אם זה לא ברור, נוכל למדוד את זה. בואו ניקח ונכתוב מדד ביצועים, שבו שיטה פשוטה כזו. אנחנו יוצרים משפט. אנחנו מפעילים אותו על גרסה כלשהי של הדרייבר ומגלים שהוא קורס די מהר עם אובדן כל הזיכרון שיש לנו.
ברור ששגיאות כאלה מתוקנות בקלות. אני לא אדבר עליהן. אבל אני אגיד שבגרסה החדשה זה עובד הרבה יותר מהר. השיטה טיפשית, אבל בכל זאת.
איך לעבוד נכון? מה אנחנו צריכים לעשות בשביל זה?
במציאות, יישומים תמיד סוגרים פקודות. כל הספרים אומרים לסגור אותן, אחרת הזיכרון ידלוף.
ו-PostgreSQL לא יכול לשמור שאילתות במטמון. כל סשן צריך ליצור את המטמון הזה לעצמו.
ואנחנו גם לא רוצים לבזבז זמן על ניתוח.
וכרגיל יש לנו שתי אפשרויות.
האפשרות הראשונה היא שאנחנו אומרים, בואו נעטוף הכל ב-PgSQL. יש שם מטמון. הוא שומר הכל במטמון. זה יעבוד מצוין. בדקנו את זה. יש לנו 100500 שאילתות. זה לא עובד. אנחנו לא מסכימים להפוך שאילתות ידנית לפרוצדורות. לא, לא.
יש לנו אפשרות שנייה - לקחת ולנסר את זה בעצמנו. אנחנו פותחים את קוד המקור, מתחילים לנסר. ניסרנו וניסרנו. התברר שזה לא כל כך קשה לעשות.
זה הופיע באוגוסט 2015. עכשיו זו גרסה מודרנית יותר. והכל נהדר. זה עובד כל כך טוב שאנחנו לא משנים כלום באפליקציה. ואפילו הפסקנו לחשוב על PgSQL, כלומר זה הספיק לנו כדי להפחית את כל עלויות התקורה כמעט לאפס.
בהתאם לכך, פקודות שהוכנו על ידי השרת מופעלות בביצוע החמישי על מנת לא לבזבז זיכרון במסד הנתונים עבור כל בקשה חד פעמית.
האם אוכל לשאול - איפה המספרים? מה מקבלים? וכאן לא אתן את המספרים, כי לכל בקשה יש את שלה.
היו לנו שאילתות כאלה שהשקענו בערך 20 מילישניות בניתוח שאילתות OLTP. היו 0,5 מילישניות לביצוע, 20 מילישניות לניתוח. השאילתה היא 10 KiB של טקסט, 170 שורות של התוכנית. זוהי שאילתת OLTP. היא מבקשת 1, 5, 10 שורות, לפעמים יותר.
אבל לא רצינו לבזבז 20 מילישניות בכלל. הפחתנו את זה ל-0. הכל נהדר.
מה אפשר ללמוד מזה? אם יש לך ג'אווה, אז אתה יכול לקחת גרסה מודרנית של הדרייבר וליהנות.
אם יש לכם שפה אחרת, אז תחשבו על זה - אולי אתם צריכים גם את זה? כי מנקודת המבט של השפה הסופית, למשל, אם יש לכם PL 8 או LibPQ, אז לא ברור לכם שאתם מבזבזים זמן לא על ביצוע, אלא על ניתוח וזה שווה בדיקה. איך? הכל בחינם.
חוץ מזה שיש שגיאות, כמה מוזרויות. ועליהן נדבר עכשיו. רוב זה יעסוק בארכיאולוגיה תעשייתית, במה שמצאנו, במה שנתקלנו.
אם השאילתה נוצרת באופן דינמי. זה קורה. מישהו מדביק מחרוזות יחד, והתוצאה היא שאילתת SQL.
מה לא בסדר בזה? זה לא בסדר כי כל פעם אנחנו מקבלים מחרוזת אחרת.
ויש לחשב מחדש את קוד ה-hashCode של המחרוזת השונה הזו. זוהי באמת משימת CPU - מציאת טקסט שאילתה ארוך אפילו ב-hash קיים אינה כל כך קלה. אז המסקנה פשוטה - אל תיצרו שאילתות. אחסו אותן במשתנה אחד. ותהיו מאושרים.
הבעיה הבאה. סוגי נתונים הם חשובים. ישנם ORMs שאומרים שלא משנה מהו NULL, שיהיה any. אם הוא Int, אז נאמר setInt. ואם הוא NULL, אז שיהיה תמיד VARCHAR. ומה זה משנה מהו NULL? מסד הנתונים יבין את זה בעצמו. והתמונה הזו לא עובדת.
בפועל, למסד הנתונים כן אכפת. אם בפעם הראשונה אמרת שזה מספר, ובפעם השנייה שזה VARCHAR, אז אי אפשר לעשות שימוש חוזר בפקודות שהוכנו על ידי השרת. ובמקרה הזה, עלינו ליצור מחדש את הפקודה שלנו.
אם אתם מפעילים את אותה שאילתה, ודאו שסוגי הנתונים בעמודה אינם מעורבבים. עליכם להיזהר מ-NULL. זוהי טעות נפוצה שהייתה לנו לאחר שהתחלנו להשתמש ב-PreparedStatements.
אוקיי, הפעלנו את זה. לקחנו, אולי, נהג. והביצועים ירדו. הכל נהיה גרוע.
איך זה קורה? האם זה באג או פיצ'ר? לרוע המזל, לא הצלחנו להבין אם זה באג או פיצ'ר. אבל יש תרחיש פשוט מאוד לשחזור הבעיה הזו. היא ארבה לנו באופן בלתי צפוי לחלוטין. וזה מורכב מבחירה פשוטה מטבלה אחת. כמובן, היו לנו עוד שאילתות כאלה. בדרך כלל הן כללו שתיים או שלוש טבלאות, אבל יש תרחיש כזה לשחזור. קח כל גרסה של מסד הנתונים שלך ושחזר אותה.
העניין הוא שיש לנו שתי עמודות, כל אחת מאונדקסת. בעמודה אחת, יש מיליון שורות לפי ערך NULL. ובעמודה השנייה, יש רק 20 שורות. כאשר אנו מבצעים את הפעולה ללא משתנים מקושרים, הכל עובד כשורה.
אם נתחיל לבצע עם משתנים כבולים, כלומר נריץ את הסימן "?" או "$1" עבור השאילתה שלנו, מה נקבל בסופו של דבר?
הביצוע הראשון הוא כצפוי. השני קצת יותר מהיר. משהו שמור במטמון. השלישי-רביעי-חמישי. ואז בום - ומשהו כזה. והדבר הכי גרוע הוא שזה קורה בביצוע השישי. מי ידע שצריך לעשות בדיוק שש הוצאות להורג כדי להבין מהי תוכנית הביצוע האמיתית?
מי אשם? מה קרה? מסד הנתונים מכיל אופטימיזציה. והוא מותאם למקרה הגנרי. ובהתאם, החל מרגע מסוים הוא עובר לתוכנית הגנרית, שלמרבה הצער עשויה להיות שונה. היא עשויה להיות זהה, או שהיא עשויה להיות שונה. ויש ערך סף כלשהו שמוביל להתנהגות כזו.
מה אפשר לעשות עם זה? כאן, כמובן, קשה יותר להניח משהו. יש פתרון פשוט שאנחנו משתמשים בו. הוא +0, OFFSET 0. בוודאי אתם מכירים פתרונות כאלה. אנחנו פשוט לוקחים ומוסיפים "+0" לבקשה והכל בסדר. אני אראה לכם אחר כך.
ויש עוד אפשרות - לבחון את התוכניות ביתר קפידה. היזם חייב לא רק לכתוב את הבקשה, אלא גם לומר "הסבר נתח" 6 פעמים. אם 5, אז זה לא יעבוד.
וישנה אפשרות שלישית - לכתוב מכתב ל-pgsql-hackers. כתבתי, למרות שעדיין לא ברור אם מדובר בבאג או בתכונה.
בזמן שאנחנו חושבים האם זה באג או פיצ'ר, בואו נתקן את זה. בואו ניקח את השאילתה שלנו ונוסיף "+0". הכל בסדר. שני סמלים ואתם אפילו לא צריכים לחשוב איך ומה יש שם. זה מאוד פשוט. פשוט אסרנו על מסד הנתונים להשתמש באינדקס על העמודה הזו. אין לנו אינדקס על העמודה "+0" וזהו, מסד הנתונים לא משתמש באינדקס, הכל בסדר.
זהו כלל 6 הסברים. עכשיו, בגרסאות הנוכחיות, צריך לעשות זאת 6 פעמים אם יש משתנים כבולים. אם אין משתנים כבולים, אז נעשה זאת כך. ובסוף, זוהי הבקשה שקורסת. זה לא כל כך מסובך.
נראה, כמה זה אפשרי? באג פה, באג שם. באמת יש באג בכל מקום.
בואו נסתכל שוב. לדוגמה, יש לנו שתי סכמות. סכמה א' עם טבלה Y וסכמה ב' עם טבלה Y. השאילתה היא לבחור נתונים מהטבלה. מה יהיה לנו? תהיה לנו שגיאה. יהיו לנו את כל האפשרויות הנ"ל. הכלל הוא - אם באג נמצא בכל מקום, יהיו לנו את כל האפשרויות הנ"ל.
עכשיו השאלה היא: "למה?" נראה שיש תיעוד שאם יש לנו סכימה, אז יש משתנה "search_path" שאומר לנו היכן לחפש את הטבלה. נראה שהמשתנה קיים.
מה הבעיה? הבעיה היא שפקודות שהוכנו על ידי השרת אינן חושדות ש-search_path יכול להשתנות על ידי מישהו. ערך זה נשאר קבוע עבור מסד הנתונים. וחלקים מסוימים עשויים לא לקלוט ערכים חדשים.
כמובן, זה תלוי בגרסה עליה אתה בודק. זה תלוי עד כמה הטבלאות שלך שונות באופן משמעותי. וגרסה 9.1 פשוט תריץ את השאילתות הישנות. גרסאות חדשות יותר עשויות לזהות את התקלה ולומר לך שיש לך באג.
איך לטפל בזה? יש מתכון פשוט - אל תעשו זאת. אל תשנו את search_path באפליקציה. אם תשנו אותו, עדיף ליצור חיבור חדש.
אנחנו יכולים לדון בזה, כלומר לפתוח את זה, לדון בזה, להוסיף עוד קצת. אולי נוכל לשכנע את מפתחי מסד הנתונים שכאשר מישהו משנה ערך, מסד הנתונים צריך לומר ללקוח: "תראה, יש לך ערך מעודכן כאן. אולי אתה צריך לאפס את ההצהרות, ליצור אותן מחדש?". עכשיו מסד הנתונים מתנהג בסתר ולא מדווח בשום צורה שאיפשהו בתוך ההצהרות השתנה.
ואני אדגיש שוב - זה משהו שאינו אופייני לג'אווה. נראה את אותו הדבר ב-PL/pgSQL אחד לאחד. אבל זה ישוחזר שם.
בואו ננסה שוב לבחור נתונים. אנחנו בוחרים ובוחרים. יש לנו טבלה עם מיליון שורות. כל שורה היא קילובייט. בערך ג'יגה-בייט של נתונים. ויש לנו זיכרון עבודה במכונת ג'אווה של 128 מגה-בייט.
אנחנו, כפי שמומלץ בכל הספרים, משתמשים בעיבוד זרמים. כלומר, אנחנו פותחים את resultSet וקוראים נתונים משם לאט לאט. האם זה יעבוד? האם זה לא ייפול בזיכרון? האם זה ייקרא לאט לאט? בואו נאמין במסד הנתונים, בואו נאמין ב-Postgres. אנחנו לא מאמינים. האם OutOfMemory ייפול? למי OutOfMemory נפל? ומי הצליח לתקן את זה אחרי זה? מישהו הצליח לתקן את זה.
אם יש לך מיליון שורות, אתה לא יכול פשוט לבחור. אתה בהחלט צריך OFFSET/LIMIT. מי בעד האפשרות הזו? ומי בעד האפשרות שאתה צריך כדי לשחק עם autoCommit?
כאן, כרגיל, האפשרות הכי לא צפויה מתגלה כנכונה. ואם פתאום תכבה את autoCommit, זה יעזור. למה? המדע לא יודע על זה.
אבל כברירת מחדל, כל הלקוחות שמתחברים למסד נתונים של Postgres מאחזרים את כל הנתונים. PgJDBC אינו יוצא מן הכלל, והוא מאחזר את כל השורות.
יש וריאציה על ערכת הנושא FetchSize, כלומר ניתן לומר ברמת משפט נפרד שכאן, בבקשה, בחר נתונים לפי 10, 50. אבל זה לא עובד עד שמכבים את autoCommit. כבה את autoCommit - זה מתחיל לעבוד.
אבל לעבור על הקוד ולקבוע setFetchSize בכל מקום זה לא נוח. לכן יצרנו הגדרה שתגיד את ערך ברירת המחדל עבור כל החיבור.
אז אמרנו את זה. הגדרנו את הפרמטר. ומה קיבלנו? אם נבחר קטן, אם, למשל, נבחר 10 שורות, אז יש לנו עלויות תקורה גדולות מאוד. לכן, אנחנו צריכים להגדיר את הערך הזה לכמאה.
באופן אידיאלי, כמובן, עדיין נצטרך ללמוד להגביל את זה בבתים, אבל המתכון הוא זה: הגדירו את defaultRowFetchSize ליותר ממאה ותיהנו.
בואו נעבור להכנסת נתונים. הכנסתם פשוטה יותר, ישנן אפשרויות שונות. לדוגמה, INSERT, VALUES. זוהי אפשרות טובה. אפשר לומר "INSERT SELECT". בפועל, זה אותו הדבר. אין הבדל בביצועים.
ספרים אומרים שצריך לעשות פקודות Batch, ספרים אומרים שאפשר לעשות פקודות מורכבות יותר עם מספר סוגריים. ול-Postgres יש תכונה נהדרת - אפשר להעתיק, כלומר לעשות את זה מהר יותר.
אם נמדוד את זה, נוכל לגלות שוב כמה תגליות מעניינות. איך אנחנו רוצים שזה יעבוד? אנחנו לא רוצים לנתח ולא לבצע פקודות מיותרות.
בפועל, TCP לא מאפשר לנו לעשות זאת. אם הלקוח עסוק בשליחת בקשה, אז מסד הנתונים לא קורא את הבקשות בזמן שהוא מנסה לשלוח לנו תשובות. כתוצאה מכך, הלקוח ממתין שהמסד נתונים יקרא את הבקשה, והמסד נתונים ממתין שהלקוח יקרא את התגובה.
ולכן הלקוח נאלץ לשלוח חבילת סנכרון מעת לעת. אינטראקציות רשת נוספות, אובדן זמן נוסף.
וככל שאנחנו מוסיפים אותם יותר, כך המצב מחמיר. המנהל די פסימי ומוסיף אותם לעתים קרובות למדי, בערך פעם ב-200 שורות, תלוי בגודל השורות וכו'.
קורה שמתקנים רק שורה אחת והכל מאיץ פי 10. זה קורה. למה? כרגיל, הקבוע כבר היה בשימוש איפשהו. והערך "128" אומר - אל תשתמשו בעיבוד קבוצות (batching).
מזל שזה לא נכנס לגרסה הרשמית. זה התגלה לפני שהתחילו לשחרר את זה. כל המשמעויות שאני נותן מבוססות על גרסאות מודרניות.
בואו נמדוד. אנחנו מודדים InsertBatch פשוט. אנחנו מודדים InsertBatch מרובה, כלומר את אותו הדבר, אבל יש הרבה ערכים. מהלך חכם. לא כולם יכולים לעשות את זה, אבל זה מהלך כל כך פשוט, הרבה יותר פשוט מ-COPY.
אתה יכול לעשות העתקה.
ואתה יכול לעשות זאת על מבנים. להצהיר על סוג ברירת מחדל של משתמש, להעביר מערך ולהוסיף ישירות לטבלה.
אם תפתחו את הקישור: pgjdbc/ubenchmsrk/InsertBatch.java, אז הקוד הזה נמצא ב-GitHub. תוכלו לראות ספציפית אילו שאילתות נוצרות שם. זה לא משנה.
השקנו את זה. והדבר הראשון שהבנו הוא שאי אפשר להשתמש ב-batch. כל אפשרויות ה-batching הן אפס, כלומר זמן הביצוע הוא כמעט אפס בהשוואה לביצוע בודד.
אנחנו מכניסים נתונים. זוהי טבלה פשוטה מאוד. שלוש עמודות. ומה אנחנו רואים כאן? אנחנו רואים שכל שלוש האפשרויות דומות פחות או יותר. ו-COPY, כמובן, עדיף.
זה הזמן שבו אנו מוסיפים חתיכות. כאשר אמרנו שערך VALUES אחד, שני ערכי VALUES, שלושה ערכי VALUES, או שציינו 10 מהם מופרדים בפסיקים. זה עכשיו בדיוק אופקית. 1, 2, 4, 128. ניתן לראות ש-Batch Insert, המצויר בכחול, מקבל הקלה רבה מכך. כלומר, כאשר מוסיפים אחד אחד או אפילו כאשר מוסיפים ארבעה על ארבעה, זה הופך להיות טוב פי שניים, פשוט בגלל שדחפנו קצת יותר לתוך VALUES. פחות פעולות EXECUTE.
שימוש ב-COPY על נפחים קטנים הוא לא מבטיח במיוחד. אפילו לא ציירתי על שני הראשונים. הם עולים לשמיים, כלומר המספרים הירוקים האלה עבור COPY.
יש להשתמש ב-COPY כאשר נפח הנתונים שלכם הוא לפחות יותר ממאה שורות. התקורה של פתיחת חיבור זה גדולה. ולמען האמת, לא חפרתי בכיוון הזה. אופטימיזציתי את Batch, אבל לא את COPY.
מה נעשה הלאה? אנו מודדים. אנו מבינים שעלינו להשתמש או במבנים או בתבנית חכמה המשלבת מספר משמעויות.
מה עלינו לקחת מהדיווח של היום?
- PreparedStatement הוא הכל עבורנו. הוא נותן הרבה עבור ביצועים. הוא נותן חבית גדולה של זפת.
- ואתה צריך לעשות את הפעולה "הסבר" ו"ניתוח" 6 פעמים.
- ואנחנו צריכים לדלל את OFFSET 0 עם טריקים כמו +0 כדי לתקן את האחוז הנותר של השאילתות הבעייתיות שלנו.
מקור: www.habr.com
