אופטימיזציה של שימוש גבוה בזיכרון במופעים

‫MySQL   |  PostgreSQL   |  SQL Server

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

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

בכל פעם שמתרחש שימוש גבוה בזיכרון על ידי מכונת Cloud SQL ל-MySQL, ‏ Cloud SQL ממליץ לזהות את השאילתה או התהליך שמשתמשים בהרבה זיכרון ולשחרר אותו. צריכת הזיכרון של MySQL מחולקת לשלושה חלקים עיקריים:

• שרשורים וצריכת זיכרון של תהליכים
• צריכת זיכרון של מאגר נתונים זמני
• צריכת זיכרון מטמון

שרשורים וצריכת זיכרון של תהליכים

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

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

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

צריכת זיכרון של מאגר נתונים זמני

החלק הזה של הזיכרון משותף לכל השאילתות ונשלט על ידי פרמטרים כמו innodb_buffer_pool_size, innodb_log_buffer_size ו-key_buffer_size.

מאגר הנתונים הזמני של InnoDB, שמוגדר באמצעות הדגל innodb_buffer_pool_size, תופס כמות משמעותית של זיכרון במכונת Cloud SQL ל-MySQL ומשמש כמטמון לשיפור הביצועים. כדי להקטין את הסיכון לאירועים של חוסר זיכרון (OOM), אפשר להפעיל מאגר חוצץ מנוהל (גרסת Preview).

צריכת זיכרון מטמון

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

צריכת זיכרון אחרת

הזיכרון משמש גם לפעולות של הצטרפות ומיון. אם השאילתות שלכם משתמשות בפעולות של צירוף או מיון, הן משתמשות בזיכרון על בסיס join_buffer_size ו-sort_buffer_size.

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

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

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

הפרמטר שקשור ל-MyISAM להוספת נתונים בכמות גדולה הוא bulk_insert_buffer_size.

מידע על השימוש בזיכרון ב-MySQL זמין במסמכי התיעוד של MySQL.

המלצות

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

הפעלה של מאגר חוצץ מנוהל

גרסת טרום-השקה

השימוש במוצר הזה או בתכונה הזו כפוף לתנאי השימוש במוצרים בגרסת טרום-GA, שמפורטים בחלק "תנאים כלליים לשירות" של התנאים הספציפיים לשירות. השימוש בתכונות ובמוצרים בגרסת טרום-GA הוא "כפי שהם" (As is), ויכול להיות שהתמיכה בהם תהיה מוגבלת. אתם יכולים לקרוא מידע נוסף בקטע תיאור שלבי ההשקה.

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

אי אפשר להפעיל מאגר חוצץ מנוהל במכונות עם ליבות משותפות, או ב-MySQL 5.6 או ב-MySQL 5.7.

כדי להפעיל מאגר באפר מנוהל עבור המכונה, מגדירים את הדגל innodb_cloudsql_managed_buffer_pool לערך on. מידע נוסף על הגדרת דגלים של מסד נתונים זמין במאמר הגדרת דגל של מסד נתונים.

שינוי הערך של הדגל innodb_cloudsql_managed_buffer_pool לא מחייב הפעלה מחדש של מופע Cloud SQL.

כברירת מחדל, אם מסד הנתונים שלכם ב-MySQL חורג מ-95% מהזיכרון שהוקצה לו, מערכת Cloud SQL מתחילה לצמצם את הגודל של innodb_buffer_pool_size. כדי לשנות את ערך הסף של 95%, מגדירים את הדגל innodb_cloudsql_managed_buffer_pool_threshold_pct לערך אחוזים אחר. לדוגמה, כדי לשנות את ערך הסף ל-97%, משתמשים בפקודה הבאה:

gcloud sql instances patch INSTANCE_NAME
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

אפשר להגדיר את הדגל innodb_cloudsql_managed_buffer_pool_threshold_pct לערך של מספר שלם בין 50 ל-99. כדי לשנות את ערך הסף של השימוש בזיכרון, לא צריך להפעיל מחדש את מופע Cloud SQL.

כשמאגר הבאפר המנוהל מבצע התאמות לערך של innodb_buffer_pool_size, השינויים לא משתקפים בערך הדגל שמוצג במסוף Google Cloud . כדי לראות את הערך הנוכחי של innodb_buffer_pool_size כשמאגר הבאפר המנוהל מופעל, אפשר לשלוח שאילתה לגבי ערך הדגל באמצעות לקוח MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

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

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

שימוש ב-Metrics Explorer כדי לזהות את השימוש בזיכרון

אפשר לבדוק את השימוש בזיכרון של מופע באמצעות המדד database/memory/components.usage בMetrics Explorer.

באופן כללי, אם יש לכם פחות מ-10% זיכרון ב-database/memory/components.cache וב-database/memory/components.free יחד, הסיכון לאירוע OOM גבוה. כדי לעקוב אחרי השימוש בזיכרון ולמנוע אירועי OOM, מומלץ להגדיר מדיניות התראות עם תנאי של סף מדד ב-database/memory/components.usage.

בטבלה הבאה מוצג הקשר בין הזיכרון של המופע לבין סף ההתראה המומלץ:

זיכרון המכונה	סף התראות מומלץ
פחות מ-16 GB או שווה ל-16 GB	90%
יותר מ-16 GB	‪95%

חישוב צריכת הזיכרון

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

השימוש המקסימלי בזיכרון של MySQL‏ = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

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

• ‫innodb_buffer_pool_size: הגודל בבייטים של מאגר הנתונים הזמני, אזור הזיכרון שבו InnoDB שומר במטמון נתונים של טבלאות ואינדקסים.
• ‫innodb_additional_mem_pool_size: הגודל בבייטים של מאגר הזיכרון ש-InnoDB משתמש בו כדי לאחסן מידע על מילון הנתונים ומבני נתונים פנימיים אחרים.
• ‫innodb_log_buffer_size: הגודל בבייטים של המאגר ש-InnoDB משתמש בו כדי לכתוב לקובצי היומן בדיסק.
• ‫tmp_table_size: הגודל המקסימלי של טבלאות זמניות פנימיות בזיכרון שנוצרות על ידי מנוע האחסון MEMORY, ומגרסה MySQL 8.0.28, על ידי מנוע האחסון TempTable.
• ‫key_buffer_size: גודל המאגר שמשמש לבלוקים של אינדקסים. בלוקים של אינדקסים בטבלאות MyISAM נשמרים בזיכרון המטמון ומשותפים לכל השרשורים.
• ‫read_buffer_size: כל שרשור שמבצע סריקה רציפה של טבלת MyISAM מקצה מאגר בגודל הזה (בבייטים) לכל טבלה שהוא סורק.
• ‫read_rnd_buffer_size: המשתנה הזה משמש לקריאות מטבלאות MyISAM, לכל מנוע אחסון ולאופטימיזציה של קריאה מטווחים מרובים.
• ‫sort_buffer_size: כל סשן שצריך לבצע מיון מקצה מאגר בגודל הזה. sort_buffer_size לא ספציפי למנוע אחסון כלשהו, והוא חל באופן כללי על אופטימיזציה.
• ‫join_buffer_size: הגודל המינימלי של מאגר הנתונים הזמני שמשמש לסריקות של אינדקסים רגילים, לסריקות של אינדקסים בטווח ולצירופים שלא משתמשים באינדקסים, ולכן מבצעים סריקות מלאות של הטבלה.
• ‫max_connections: המספר המקסימלי המותר של חיבורי לקוח בו-זמניים.

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

פתרון בעיות שקשורות לצריכת זיכרון גבוהה

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

  mysql> SHOW [FULL] PROCESSLIST;
  
  

• כדי לראות את מאגר הנתונים הזמני הנוכחי ואת השימוש בזיכרון, אפשר לבדוק את SHOW ENGINE INNODB STATUS בקטע BUFFER POOL AND MEMORY. כך תוכלו להגדיר את הגודל של מאגר הנתונים הזמני.

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• משתמשים בפקודה SHOW variables של MySQL כדי לבדוק את ערכי המונה, שמספקים מידע כמו מספר הטבלאות הזמניות, מספר השרשורים, מספר מטמוני הטבלאות, דפים מלוכלכים (dirty), טבלאות פתוחות והשימוש במאגר הנתונים הזמני.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

החל שינויים

אחרי שמנתחים את השימוש בזיכרון לפי רכיבים שונים, מגדירים את הדגל המתאים במסד הנתונים של MySQL. כדי לשנות את הדגל במכונת Cloud SQL ל-MySQL, אפשר להשתמש במסוף Google Cloud או ב-ה-CLI של gcloud. כדי לשנות את ערך הדגל באמצעות המסוף Google Cloud , עורכים את הקטע Flags, בוחרים את הדגל ומזינים את הערך החדש.

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

המאמרים הבאים

• Google Cloud recommenders