כוונון משתני מערכת MySQL לביצועים גבוהים

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


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

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

למה?

באופן מפתיע, הסיבה היא לא עצלנות (אם כי בחלקה).

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

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

סקרן? בואו נצלול פנימה!

לא סקרן? ובכן, צללו פנימה בכל מקרה, כי הקריירה שלכם תלויה בזה! ��

בצע אופטימיזציה של מטמון שאילתת MySQL

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

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

אתה יכול לבדוק אם יש לך מטמון שאילתה זמין (הערה, זמין, לא מופעל) במסד הנתונים שלך על ידי הפעלת שאילתה זו במסוף הנתונים:

MariaDB [(אין)]> הצג שינויים כמו ‘have_query_cache’;
+——————+——-+
| שם משתנה | ערך |
+——————+——-+
| have_query_cache | כן |
+——————+——-+

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

עכשיו נראה אם ​​יש לי את מטמון השאילתות בפועל:

MariaDB [(אין)]> הצג שינויים כמו ‘query_cache_type’;
+——————+——-+
| שם משתנה | ערך |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

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

MariaDB [(אין)]> הגדר GLOBAL query_cache_type = ON;

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

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

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

MariaDB [(אין)]> הצג שונות כמו “query_cache_size”;
+——————+———-+
| שם משתנה | ערך |
+——————+———-+
| גודל הקובץ: info 16777216 |
+——————+———-+

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

נניח שאתה מגדיר את גודל מטמון השאילתה להיות 500 KB:

MariaDB [(אין)]> הגדר GLOBAL query_cache_size = 500000;

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

  • ראשית, משתנה query_cache_size חייב להיות גדול מספיק בכדי להחזיק את התוצאה של השאילתות שלך. אם הוא קטן מדי, שום דבר לא יהיה במטמון.
  • שנית, אם גודל ה- query_cache_s מוגדר למספר גבוה מדי, יהיו שני סוגים של בעיות: 1) המנוע יצטרך לבצע עבודות נוספות באחסון ואיתור תוצאות השאילתה באזור זיכרון מסיבי זה. 2) אם מרבית השאילתות יובילו בגדלים קטנים בהרבה, המטמון יעבור מקוטע והיתרונות של שימוש במטמון יאבדו..

איך אתה יודע שהמטמון מתפרק? בדוק את המספר הכולל של חסימות במטמון כך:

MariaDB [(אין)]> הצג סטטוס כמו ‘Qcache_total_blocks’;
+———————+——-+
| שם משתנה | ערך |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

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

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

הברגה, הברגה בריכות, המתנה ופסק זמן

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

הברגה

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

בריכת חוטים

אף תוכנית במערכת מחשבים לא יכולה לפתוח כמה שרצים שהיא רוצה. הסיבה היא כפולה: 1) זיכרון עלויות של זיכרון (RAM), ומערכת ההפעלה פשוט לא תאפשר לך להשתולל ולאכול את כל זה. 2) ניהול, נגיד, מיליון אשכולות הוא משימה מאסיבית בפני עצמה, ואם שרת MySQL יכול היה ליצור את האשכולות הרבים ההם, הוא היה מת תוך ניסיון להתמודד עם התקורה.

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

ariaDB [(אין)]> הצג משתנים כמו ‘thread_pool_size’;
+——————+——-+
| שם משתנה | ערך |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

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

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

המתנה ופסק זמן

לאחר שנוצר שרשור ויצורף ללקוח, יהיה זה בזבוז של משאבים אם הלקוח לא שלח שאלות לשניות (או הדקות) הבאות. כתוצאה, MySQL מסיים חיבור לאחר תקופה של חוסר פעילות. זה נשלט על ידי משתנה wait_timeout:

MariaDB [(אין)]> הצג משתנים כמו ‘לחכות%’;
+—————+——-+
| שם משתנה | ערך |
+—————+——-+
| זמן ההמתנה | 28800 |
+—————+——-+

הערך שהתקבל הוא בשניות. אז כן, כברירת מחדל MySQL מוגדרת לחכות 8+ שעות לפני שהיא תנתק את הכבל! זה יכול להיות טוב אם יש לך שאילתות ארוכות וממש ברצונך לחכות להם (אבל גם אז, שמונה שעות זה אבסורד!) אבל נורא ברוב המקרים. כאשר מופעלת שאילתה, ערך זה מוגדר ל- 0 (כלומר לנצח), אך באופן כללי, יש להגדיר את זה לערך נמוך מאוד (5 שניות, למשל, או אולי אפילו פחות) כדי לשחרר את החיבור לתהליכים אחרים..

כוונון שולחנות זמניים

נתחיל עם הטבלאות הזמניות ב- MySQL.

נניח שיש לנו MySQL שנראה באופן מבני כך: TABLE A UNION (TABLE B INNER JOIN C). כלומר, אנו מעוניינים להצטרף לטבלאות B ו- C, ואז לבצע איחוד של התוצאה עם טבלה A. כעת, MySQL היה ממשיך תחילה להצטרף לטבלאות B ו- C, אך לפני שהוא יכול לבצע איחוד, הוא צריך לאחסן נתונים אלה איפשהו. כאן נכנסות טבלאות זמניות – MySQL משתמשת בהן לאחסון נתונים בשלבי ביניים בשאילתות מורכבות באופן זמני, וברגע שהשאילתה מסתיימת, הטבלה הזמנית הזו מושלכת.

כעת השאלה היא: מדוע לטרוח עם כל זה?

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

ישנם שני משתנים השולטים בהתנהגות זו:

MariaDB [(אין)]> הראה משתנים כמו ‘MariaDB [(אין)]> הצג משתנים כמו ‘tmp_table_size’;
+—————-+———-+

| שם משתנה | ערך |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| שם משתנה | ערך |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(אין)]> הצג משתנים כמו ‘tmp_table_size’;
+—————-+———-+
| שם משתנה | ערך |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

הראשון, max_heap_table_size, מספר לנו כמה RAM יכול לשמש על ידי טבלת MySQL (“ערימה” כאן מתייחסת למבנה הנתונים המשמש להקצאת RAM וניהול – קרא עוד כאן), בעוד שהשני, tmp_table_size, מראה מה הגודל המרבי של הטבלה הזמנית. במקרה שלי, שניהם מוגדרים ל- 16 מגה בייט, אם כי הנקודה שאני מנסה לגרום לכך שהגדלת רק tmp_table_size לא תעבוד בסך הכל, MySQL עדיין יהיה מוגבל על ידי max_table_heap_size.

כעת מגיעה הנקודה: אם הטבלאות הזמניות שנוצרות גדולות מהמגבלה המותרת על ידי משתנים אלה, MySQL ייאלץ לכתוב אותם לדיסק הקשיח, וכתוצאה מכך ביצועים גרועים במיוחד. תפקידנו כעת הוא פשוט: נעשה כמיטב יכולתנו לנחש את גודל הנתונים המדויק ביותר לטבלאות זמניות ולהגדיל משתנים אלה עד גבול זה. עם זאת, אני מבקש להזהיר מפני אבסורד: הגדרת גבול זה ל- 16 ג’יגה-בייט (בהנחה שיש לך הרבה זיכרון RAM) כאשר מרבית הטבלאות הזמניות בגודל של פחות מ- 24 מגהב היא טיפשות – אתה פשוט מבזבז זיכרון RAM שיכול ‘ השתמשו בשאילתות אחרות או בחלקים של המערכת (למשל, מטמון).

סיכום

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

התוצאה הנחשקת ביותר שלי לכתיבת מאמר זה היא שתקח ממני שלושה דברים:

  1. MySQL הוא פיסת תוכנה טיפוסית העובדת בגבולות שנקבעו על ידי מערכת ההפעלה. זו לא תוכנית מסתורית שעושה אלוהים יודע-מה ואי אפשר לאלף. כמו כן, למרבה המזל, זה לא כל כך קשה להבין כיצד הוא מוגדר ונשלט על ידי משתני המערכת שלו.
  2.  אין הגדרה אחת שתגרום להתקנה של MySQL שלך לזום. אין לך ברירה אלא להסתכל במערכות הריצה שלך (זכור, האופטימיזציה מגיעה לאחר שהאפליקציה נמצאת בייצור, לא לפני כן), בצע את הניחושים והמדידות הטובים ביותר, ולחיות עם המציאות שהיא לעולם לא תהיה מושלמת.
  3. כוונון המשתנים אינה הדרך היחידה לבצע אופטימיזציה של MySQL – שאילתות כתיבה יעילות הן עניין נוסף, אבל זה משהו שאייחס אליו במאמר אחר. אך העניין הוא שגם אם עשית ניתוח אלוהי וכיוונת את הפרמטרים האלה בצורה הטובה ביותר, עדיין תוכל להביא את הכל לבלום..

מה משתנה המערכת האהוב עליך לכוונון? ��

תגיות:

  • מאגר מידע

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map