אופטימיזציה של שימוש גבוה ב-CPU במופעים

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

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

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

שימוש בתובנות לגבי שאילתות כדי לזהות שאילתות עם צריכת CPU גבוהה

תובנות לגבי שאילתות עוזרות לכם לזהות, לאבחן ולמנוע בעיות בביצועי שאילתות שעלולות לגרום לצריכת CPU גבוהה במסדי נתונים של Cloud SQL.

שימוש בבקרת גישה למסד נתונים של MySQL

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

המלצות

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

  1. כדאי לעיין במאמרים בנושא Threads_running וThreads_connected

    כדי לראות את מספר השרשורים הפעילים, משתמשים בשאילתה הבאה:

    > SHOW STATUS like 'Threads_%';

    ‫Threads_running היא תת-קבוצה של Threads_connected. שאר השרשורים לא פעילים. עלייה בערך של Threads_running תגרום לעלייה בשימוש במעבד. מומלץ לבדוק מה פועל בשרשורים האלה.

  2. בדיקת מצבי השאילתה

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

    mysql> SHOW [FULL] PROCESSLIST;

    שימו לב לעמודות 'מצב' ו'משך'. בודקים אם יש הרבה שאילתות שנתקעו באותו מצב.

    • אם הרבה שרשורים מציגים את הסמל Updating, יכול להיות שיש מחלוקת על נעילת רשומות. הסבר מופיע בשלב הבא.
    • אם בהרבה שרשורים מופיע Waiting לנעילת מטא-נתונים של טבלה, צריך לבדוק את השאילתה כדי לדעת איזו טבלה זו, ואז לחפש DDL (כמו ALTER TABLE) שיכול להיות שמונע את נעילת המטא-נתונים. יכול להיות ש-DDL ימתין גם לנעילת מטא-נתונים של טבלה אם שאילתה מוקדמת, כמו SELECT query שפועלת במשך זמן רב, מעכבת אותו.

  3. בדיקה של התנגשות נעילה של רשומות

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

    השאילתה הבאה מפרטת כל עסקה חוסמת ואת מספר העסקאות החסומות שמשויכות אליה.

    SELECT
  t.trx_id,
  t.trx_state,
  t.trx_started,
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM
  information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

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

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

  4. איתור עסקאות ממושכות

    • שימוש ב-SHOW ENGINE INNODB STATUS

      בקטע TRANSACTIONS (עסקאות), אפשר לראות את כל העסקאות הפתוחות, מהמוקדמת ביותר ועד המאוחרת ביותר.

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      מתחילים עם העסקאות הכי ישנות ומחפשים תשובות לשאלות הבאות:

      • כמה זמן העסקאות האלה פועלות?
      • כמה מבני נעילה(lock) ונעילות שורות קיימים?
      • כמה רשומות יש ביומן הביטול?
      • מי המארחים והמשתמשים שהצטרפו לפגישה?
      • מהי הצהרת ה-SQL המתמשכת?

    • שימוש ב-information_schema.innodb_trx

      אם SHOW ENGINE INNODB STATUS קוצץ, אפשר לבדוק את כל העסקאות הפתוחות בדרך אחרת – באמצעות הטבלה information_schema.innodb_trx:

      SELECT
 trx_id, trx_state,
 timestampdiff(second, trx_started, now()) AS active_secs,
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked,
 trx_lock_structs,
 trx_rows_locked,
 trx_rows_modified,
 trx_query
FROM information_schema.innodb_trx

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

  5. בדיקת הצהרות SQL של עסקאות שפועלות במשך זמן רב

    • שימוש ב-performance_schema

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

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      אם הם לא מופעלים, צריך להפעיל אותם:

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

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

      בעזרת mysql thread id, שהוא processlist_id, שולחים שאילתה לגבי אירועים בהיסטוריה:

      SELECT
 t.thread_id,
 event_name,
 sql_text,
 rows_affected,
 rows_examined,
 processlist_id,
 processlist_time,
 processlist_state
FROM events_statements_history h
INNER JOIN threads t
ON h.thread_id = t.thread_id
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • שימוש ביומן של שאילתות איטיות

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

  6. בדיקת התחרות על סמפור

    בסביבה מקבילית, יכול להיות שנקודת המחלוקת היא mutex ו-read/write latch במשאבים משותפים, מה שמאט את ביצועי השרת. בנוסף, אם זמן ההמתנה של הסמפור הוא יותר מ-600 שניות, המערכת עלולה לקרוס כדי לצאת מהמצב הזה.

    כדי לראות את התחרות על הסמפור, משתמשים בפקודה הבאה:

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

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

    יש נקודות מחלוקת שונות בעומסי עבודה ובהגדרות שונות.

    אם הסמפורים מופיעים לעיתים קרובות ב-btr0sea.c, יכול להיות שהאינדקס של הגיבוב הדינמי הוא מקור המחלוקת. אפשר לנסות להשבית אותו באמצעות Google Cloud המסוף אוgcloud CLI.

  7. אופטימיזציה של שאילתות ארוכות SELECT

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

    • בדיקת תוכנית הביצוע של השאילתה:
    mysql> EXPLAIN <the query>;

    אפשר לעיין בתיעוד של MySQL כדי להבין איך לפרש את הפלט ולהעריך את יעילות השאילתה.

    • שימוש באינדקס הנכון

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

    mysql> analyze table <table_name>

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

    • ניצול מלא של האינדקס

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

    • שימוש ברמזים לאופטימיזציה

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

  8. איך להימנע מרשימת היסטוריה ארוכה באמצעות READ COMMITTED

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

  9. שינוי הגדרות השרת

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

    • אם Threads_created/Connections גדול, צריך לשנות את thread_cache_size. מטמון תקין של threads יקצר את זמן יצירת ה-thread ויעזור לעומס עבודה עם רמת מקביליות גבוהה.
    • אם Table_open_cache_misses/Table_open_cache_hits לא טריוויאלי, משנים את table_open_cache. אם יש טבלאות במטמון הטבלאות, זמן הביצוע של השאילתות מתקצר, ויכול להיות הבדל משמעותי בסביבה עם מספר גבוה של פעולות שמתבצעות בו-זמנית.

  10. איך מסיימים חיבור לא רצוי

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

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

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