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

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

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

בכל פעם שמתרחש שימוש גבוה בזיכרון על ידי מכונת Cloud SQL for 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), אתם יכולים להפעיל מאגר זיכרון מנוהל.

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

זיכרון המטמון כולל מטמון שאילתות, שמשמש לשמירת השאילתות והתוצאות שלהן כדי לאחזר נתונים מהר יותר בשאילתות עוקבות זהות. הוא כולל גם את מטמון 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.

המלצות

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

הפעלה של מאגר נתונים זמני מנוהל

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

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

קריטריונים לזכאות

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

הפעלת התכונה

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

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

אם הפעלתם מאגר נתונים זמני מנוהל, וצריכת הזיכרון של המופע חורגת מאחוז הסף שמוגדר כברירת מחדל מתוך הזיכרון שהוקצה לו, אז Cloud SQL מתחיל להקטין את הגודל של innodb_buffer_pool_size. אחוז הסף שמוגדר כברירת מחדל משתנה בין 90% ל-97% בהתאם לקיבולת ה-RAM של המופע. כדי לשנות את ערך הסף, מגדירים את הדגל 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 כששימוש הזיכרון מתייצב למשך 10 דקות או יותר אחרי צמצומים קודמים. כברירת מחדל, השימוש בזיכרון של מסד הנתונים צריך להיות 70% או פחות כדי להפעיל את הגידול. כדי לשנות את ערך הסף, מגדירים את האפשרות innodb_cloudsql_managed_buffer_pool_tuneup_pct לאחוז אחר. לדוגמה, כדי לשנות את סף העלייה ל-80%, משתמשים בפקודה הבאה:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_tuneup_pct=80

לוגיקת ההתאמה

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

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

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

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

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

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

מגבלות

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

מעקב

אפשר לעקוב אחרי מאגר הנתונים הזמני המנוהל ביומן השגיאות של MySQL. ב-Cloud Logging Logs Explorer, אפשר לסנן את היומן mysql.err כדי למצוא רשומות עם הקידומת Managed Buffer Pool Plugin: או Tuner Plugin:, וכך לראות את אירועי ההתאמה האחרונים.

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

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

ביומנים הבאים מוצגות דוגמאות להפחתות אוטומטיות של innodb_buffer_pool_size:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

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

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

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

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

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

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

כדי לבחור את סוג המכונה המתאים למסד הנתונים שלכם ב-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.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

החל שינויים

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

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

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

• Cloud de Confiance by S3NS recommenders