PostgreSQL ו-JDBC סוחטים את כל המיץ. ולדימיר סיטניקוב

אני מציע לך לקרוא את התמליל של הדו"ח של ולדימיר סיטניקוב בתחילת 2016 "PostgreSQL ו-JDBC סוחטים את כל המיץ"

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

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

אנחנו נדבר:

• לגבי דגימת נתונים.
• לגבי שמירת נתונים.
• וגם על ביצועים.
• ולגבי המגרפות התת מימיות שקבורות שם.

נתחיל בשאלה פשוטה. אנו בוחרים שורה אחת מהטבלה על סמך המפתח הראשי.

מסד הנתונים ממוקם על אותו מארח. וכל החקלאות הזו לוקחת 20 מילישניות.

20 מילישניות אלו הן הרבה. אם יש לך 100 בקשות כאלה, אז אתה מבלה זמן בשנייה בגלילה בין הבקשות האלה, כלומר אנחנו מבזבזים זמן.

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

האפשרות הראשונה היא בקשה פשוטה. מה טוב בזה? זה שאנחנו לוקחים אותו ושולחים אותו, ותו לא.

https://github.com/pgjdbc/pgjdbc/pull/478

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

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

ומה שאנחנו יכולים לעשות זה שאילתה פשוטה ושאילתה מורחבת.

מה מיוחד בכל גישה?

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

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

בואו נעבור לתרגול. כך נראית אפליקציה טיפוסית. זה יכול להיות ג'אווה וכו'.

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

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

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

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

איך נוכל להשיג זאת?

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

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

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

איך לעבוד נכון? מה אנחנו צריכים לעשות בשביל זה?

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

ו-PostgreSQL לא יודע איך לשמור שאילתות במטמון. יש צורך שכל הפעלה תיצור את המטמון הזה לעצמה.

ואנחנו גם לא רוצים לבזבז זמן על ניתוח.

וכרגיל יש לנו שתי אפשרויות.

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

יש לנו אפשרות שנייה - לקחת אותה ולחתוך אותה בעצמנו. אנחנו פותחים את המקורות ומתחילים לחתוך. ראינו וראינו. התברר שזה לא כל כך קשה לעשות.

https://github.com/pgjdbc/pgjdbc/pull/319

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

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

אפשר לשאול - איפה המספרים? מה אתה מקבל? וכאן לא אתן מספרים, כי לכל בקשה יש את שלה.

השאילתות שלנו היו כאלה שהשקענו בערך 20 מילישניות בניתוח של שאילתות OLTP. היו 0,5 מילישניות לביצוע, 20 מילישניות לניתוח. בקשה - 10 KiB של טקסט, 170 שורות של תוכנית. זוהי בקשת OLTP. הוא מבקש 1, 5, 10 שורות, לפעמים יותר.

אבל לא רצינו לבזבז 20 מילישניות בכלל. הורדנו את זה ל-0. הכל נהדר.

מה אתה יכול לקחת מכאן? אם יש לך ג'אווה, אז אתה לוקח את הגרסה המודרנית של הנהג ושמח.

אם אתה מדבר בשפה אחרת, אז תחשוב - אולי גם אתה צריך את זה? כי מבחינת השפה הסופית, למשל, אם PL 8 או יש לך LibPQ, אז לא ברור לך שאתה משקיע זמן לא בביצוע, בניתוח, וזה שווה בדיקה. אֵיך? הכל בחינם.

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

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

למה הוא רע? זה רע כי בכל פעם אנחנו מסיימים עם מחרוזת אחרת.

ואת ה-hashCode של המחרוזת השונה הזו צריך לקרוא שוב. זו באמת משימת CPU - למצוא טקסט בקשה ארוך אפילו ב-hash קיים זה לא כל כך קל. לכן, המסקנה פשוטה – אל תייצר בקשות. אחסן אותם במשתנה אחד. ולשמוח.

הבעיה הבאה. סוגי נתונים חשובים. יש ORM שאומרים שזה לא משנה איזה סוג של NULL יש, שיהיה איזה סוג. אם Int, אז אנחנו אומרים setInt. ואם NULL, אז שיהיה תמיד VARCHAR. ומה זה משנה בסופו של דבר איזה NULL יש? המאגר עצמו יבין הכל. והתמונה הזו לא עובדת.

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

אם אתה מבצע את אותה שאילתה, ודא שסוגי הנתונים בעמודה שלך אינם מבולבלים. אתה צריך להיזהר מ-NULL. זו שגיאה נפוצה שהייתה לנו לאחר שהתחלנו להשתמש ב-PreparedStatements

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

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

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

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

אם נתחיל לבצע עם משתנים קשורים, כלומר נבצע את ה-"?" או "$1" עבור הבקשה שלנו, מה אנחנו מקבלים בסופו של דבר?

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

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

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

מה אתה יכול לעשות בנידון? כאן, כמובן, קשה יותר להניח משהו. יש פתרון פשוט שבו אנו משתמשים. זה +0, OFFSET 0. בטח אתה מכיר פתרונות כאלה. אנחנו פשוט לוקחים את זה ומוסיפים "+0" לבקשה והכל בסדר. אני אראה לך מאוחר יותר.

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

ויש אפשרות שלישית - לכתוב מכתב ל-pgsql-hackers. כתבתי, עם זאת, עדיין לא ברור אם זה באג או תכונה.

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

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

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

זה נראה, כמה זה אפשרי? באג פה, באג שם. למעשה, הבאג נמצא בכל מקום.

בואו נסתכל מקרוב. לדוגמה, יש לנו שתי סכמות. סכימה A עם טבלה S ותרשים B עם טבלה S. שאילתה - בחר נתונים מטבלה. מה יהיה לנו במקרה הזה? תהיה לנו שגיאה. יהיה לנו את כל האמור לעיל. הכלל הוא - באג נמצא בכל מקום, יהיה לנו את כל האמור לעיל.

עכשיו השאלה היא: "למה?" נראה שיש תיעוד שאם יש לנו סכמה, אז יש משתנה "search_path" שאומר לנו היכן לחפש את הטבלה. נראה שיש משתנה.

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

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

הגדר search_path + הצהרות מוכנות על ידי שרת =
התוכנית המאוחסנת במטמון אינה יכולה לשנות את סוג התוצאה

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

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

ואדגיש שוב - זה משהו שלא אופייני לג'אווה. נראה את אותו הדבר ב-PL/pgSQL אחד לאחד. אבל זה ישוחזר שם.

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

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

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

כאן, כרגיל, האופציה הכי לא צפויה מתבררת כנכונה. ואם תכבה פתאום את autoCommit, זה יעזור. למה? המדע לא יודע על זה.

אבל כברירת מחדל, כל הלקוחות המתחברים למסד נתונים Postgres מביאים את כל הנתונים. PgJDBC אינו יוצא דופן בהקשר זה; הוא בוחר את כל השורות.

יש וריאציה על ערכת הנושא FetchSize, כלומר אתה יכול לומר ברמה של הצהרה נפרדת שכאן, נא לבחור נתונים לפי 10, 50. אבל זה לא עובד עד שאתה מכבה את autoCommit. כבוי את autoCommit - זה מתחיל לעבוד.

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

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

באופן אידיאלי, כמובן, אתה עדיין צריך ללמוד איך להגביל את זה בבתים, אבל המתכון הוא כזה: הגדר את defaultRowFetchSize ליותר ממאה ותהיה מאושר.

בואו נעבור להכנסת נתונים. ההכנסה קלה יותר, יש אפשרויות שונות. לדוגמה, INSERT, VALUES. זוהי אפשרות טובה. אתה יכול לומר "INSERT SELECT". בפועל זה אותו דבר. אין הבדל בביצועים.

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

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

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

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

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

https://github.com/pgjdbc/pgjdbc/pull/380

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

רתמת Java microbenchmark

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

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

אתה יכול לעשות COPY.

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

אם אתה פותח את הקישור: pgjdbc/ubenchmsrk/InsertBatch.java, אז הקוד הזה נמצא ב-GitHub. אתה יכול לראות ספציפית אילו בקשות נוצרות שם. זה לא משנה.

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

אנו מכניסים נתונים. זה שולחן מאוד פשוט. שלושה עמודים. ומה אנחנו רואים כאן? אנו רואים שכל שלוש האפשרויות הללו בר השוואה בערך. ו-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