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

‫MySQL   |  PostgreSQL   |  SQL Server

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

אם השימוש בזיכרון מתייצב על ערך נמוך יותר למשך 10 דקות או יותר, MySQL מגדילה את הערך של 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 לגודל מינימלי קבוע ומוגדר מראש. במקום זאת, המערכת מקטינה את הגודל באופן איטרטיבי ודינמי עד שרמת הניצול הכוללת של הזיכרון של המופע יורדת מתחת לאחוז הסף שהוגדר (innodb_cloudsql_managed_buffer_pool_threshold_pct). היא מקטינה את מאגר הנתונים הזמני על ידי שינוי הערך של הדגל innodb_buffer_pool_size, תוך שימוש בפונקציית שינוי הגודל המובנית של מאגר הנתונים הזמני של InnoDB.

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

גודל מאגר MySQL	גודל מינימלי של מאגר נתונים זמני
‫1,025 עד 2,048 מגה-בייט	‪35%
‫2,049 עד 6,528 מגה-בייט	30%
‫6,529 עד 11,315 מגה-בייט	40%
‫11316 עד 22630 מגה-בייט	45%
גדלים אחרים (ברירת מחדל)	50%

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

גודל מאגר MySQL	אחוז הירידה
‫1,025 עד 2,048 מגה-בייט	15%
‫2,049 עד 6,528 מגה-בייט	‫11%
‫6,529 עד 11,315 מגה-בייט	‫8%
‫11316 עד 22630 מגה-בייט	‫6%
גדלים אחרים (ברירת מחדל)	‫5%

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

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

מעקב

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

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

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

  mysql> SHOW variables like 'VARIABLE_NAME'
  

החל שינויים

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

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

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

• Google Cloud recommenders