הבאג ב-MariaDB 10.4 שמשתק אינדוקס Magento

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

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

SET SESSION optimizer_switch = 'rowid_filter=off';
SET SESSION optimizer_use_condition_selectivity = 1;

הסימפטומים: מה שראינו

התהליך שנתקע היה בניית ה-flat catalog של Magento אותו INSERT ... SELECT ענקי שמאחד שבע טבלאות EAV זמניות לטבלה שטוחה אחת. בדרך כלל הוא מסתיים בפחות מדקה.

הצילום מה-processlist נראה כך:

Id    User     Command  Time  State          Info
5793  shop_db  Query    47    Sending data   INSERT INTO `catalog_product_flat_1_tmp_indexer` ...

ובמקביל, גודל טבלת היעד על הדיסק:

$ watch -n1 du -sh catalog_product_flat_1_tmp_indexer.*
56K   catalog_product_flat_1_tmp_indexer.frm
164K  catalog_product_flat_1_tmp_indexer.ibd

הצירוף הזה הוא כל האבחנה. Sending data במשך 47 שניות כשקובץ ה-.ibd תקוע על 164K אומר שצד ה-SELECT כמעט לא מייצר שורות. זו לא בעיית כתיבה, לא בעיית flush ולא בעיית I/O התהליך פשוט לא מצליח לקרוא.

מה זה לא היה: השלילות שחוסכות שעות

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

1. טרנזקציות פתוחות ו-purge lag

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SHOW ENGINE INNODB STATUS\G

בפלט של SHOW ENGINE INNODB STATUS חפשו את History list length. אם המספר במיליונים, טרנזקציה אחת נטושה במצב Sleep מקפיאה את ה-read view, ה-purge נעצר, וכל סריקה נאלצת לעבור על שרשראות undo ענקיות. זה מייצר בדיוק את אותו סימפטום: "הכל איטי ותקוע", שורד restart, בלי קפיצה ב-CPU או ב-I/O.

אצלנו innodb_trx חזר ריק. לא הייתה טרנזקציה פתוחה בכלל.

2. שים לב לפילטר של ה-processlist

טעות נפוצה שראינו במהלך האבחון: ניטור שמריץ

mysql -e 'show full processlist;' | grep -v 'Sleep\|system user\|replication'

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

3. רפליקציה שנגררת מאחור

SHOW SLAVE STATUS\G   -- Seconds_Behind_Master, Slave_SQL_Running_State

תהליך applier חד-ערוצי שמעכל שעות של relay log מחזיק row locks שהאינדקסר ממתין עליהם. שווה שלילה.

4. מפתחות ראשיים בטבלאות הזמניות

SHOW CREATE TABLE catalog_product_entity_int_tmp_indexer\G
SHOW CREATE TABLE catalog_product_entity_varchar_tmp_indexer\G

כל שבע טבלאות ה-_tmp_indexer חייבות PRIMARY KEY (entity_id). אם ריצה קודמת שקרסה השאירה אחת מהן בלי מפתח, ה-nested loop הופך לריבועי ומקבלים בדיוק "בדרך כלל דקה, עכשיו שעות". זה חשוד לגיטימי לחלוטין וכדאי לשלול אותו לפני שממשיכים.

5. תשתית

df -h /var/lib/mysql
dmesg -T | tail -60
iostat -x 1 5
free -h

דיסק שמתמלא, swap, או snapshot של ה-VM שהתחיל באותו בוקר כל אחד מהם מסביר "הכל האט בבת אחת" בזמן שבסיס הנתונים נראה תקין. שלילה של 30 שניות.

איך האופטימייזר בוחר מסלול

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

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

הכיוון הקריטי הוא הערכה נמוכה מדי. אם האופטימייזר מאמין שטבלה תחזיר 3 שורות, הוא יבחר תוכנית שהיא קטסטרופה כשמדובר ב-400,000 שורות: nested loop בלי buffer, טבלה מובילה שגויה, סריקות במקום גישות לפי מפתח. הערכה גבוהה מדי, לעומת זאת, רק גורמת לו להיות שמרני מדי עלות נסבלת.

המשתנה שהפך את הקערה: optimizer_use_condition_selectivity

המשתנה הזה קובע מכמה מקורות מידע האופטימייזר בונה את הערכת השורות שלו:

הערכים לעיל מצוטטים מתוך sql/sys_vars.cc בקוד המקור של MariaDB. שם אפשר גם לראות שברירת המחדל היא DEFAULT(1) בענף 10.3 ו-DEFAULT(4) בענף 10.4 כלומר השינוי הזה מגיע עם השדרוג, בלי שאף אחד נגע בהגדרות.

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

בסכמת ה-EAV של Magento התנאים תלויים מאוד זה בזה: entity_id, attribute_id, store_id ו-status זזים יחד. כפלו שלוש הערכות מתואמות של 10% ותקבלו 0.1% כשהאמת היא 9% הערכה נמוכה פי תשעים. רמה 1 מכניסה לחישוב את המספר המינימלי של מקורות, ולכן חשופה פחות לאפקט הזה.

ולמה זה חמור במיוחד בטבלאות ה-tmp_indexer

זה החלק הספציפי ל-Magento, והוא מה שהופך את רמה 4 מ"לא אופטימלית" ל"מזיקה":

הטבלאות catalog_product_entity_int_tmp_indexer ושש אחיותיה נוצרות ומתמלאות באותה ריצה עצמה. ברגע שבו האופטימייזר בוחר תוכנית עבור ה-INSERT ... SELECT הגדול, לטבלאות האלה אין סטטיסטיקות בכלל או שיש להן סטטיסטיקות מרגע שבו הן היו ריקות. InnoDB אכן מחשב מחדש ברקע כשטבלה משתנה ביותר מ-10%, אבל התוכנית נבחרת לפני שזה מדביק את הפער.

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

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

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

גם היא חדשה ב-10.4 ודלוקה כברירת מחדל הדגל OPTIMIZER_SWITCH_USE_ROWID_FILTER נכלל ב-OPTIMIZER_SWITCH_DEFAULT בענף 10.4, ואינו קיים כלל בענף 10.3. כשלטבלה יש שני אינדקסים שימושיים, במקום לבצע lookup מלא לכל מועמד, האופטימייזר יכול:

1. לסרוק את האינדקס הסלקטיבי יותר
2. לאסוף את המפתחות הראשוניים המתאימים אל מבנה נתונים בזיכרון
3. למיין אותם
4. להשתמש במבנה כדי לדלג על lookups שייזרקו בכל מקרה

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

ההחלטה אם זה משתלם נשענת כולה על אומדני selectivity. אותם אומדנים שבורים מהסעיף הקודם. לכן שתי ההגדרות אינן שני מתגים נפרדים: rowid_filter היא תכונה שהערך שלה טוב בדיוק כמו המספרים ש-optimizer_use_condition_selectivity=4 מזרים אליה. כיביתם גם את הקלט הרע וגם את התכונה הרגישה ביותר אליו.

למה השאילתה הזו שברירית במיוחד

הסתכלו על תנאי ה-WHERE של האינדקסר:

WHERE (IFNULL(dstatus.value, `catalog_product_entity_int_tmp_indexer`.`status`) = 1)

פונקציה שעוטפת עמודות משתי טבלאות שונות. המשמעויות:

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

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

עכשיו הוסיפו שבעה LEFT JOIN, כולם על entity_id. התוכנית הנכונה טריוויאלית: לסרוק את et, ולבצע lookup לפי מפתח ראשוני אל כל אחת מהשבע. העלות לינארית בכמות השורות דקה.

אבל אם האופטימייזר מאמין שהטבלאות האלה זעירות, הוא עלול לשנות סדר, לעבור ל-join buffer, או לבנות rowid filter. כל אחת מהאפשרויות האלה הופכת שבעה lookups לכל שורה לשבע סריקות לכל שורה. זה מקור הפער של פי 180 והחתימה קלאסית. הנתונים לא גדלו פי 180 בלילה אחד. התוכנית התחלפה.

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

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

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

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

הטריגר: innodb_stats_auto_recalc

InnoDB מחשב מחדש סטטיסטיקות ברקע כשכמות השורות בטבלה משתנה ביותר מ-10%. זה לא מספר מעוגל בקוד המקור, ב-dict0stats_bg.cc, זו בדיוק השורה counter > n_rows / 10. עדכון נתונים גדול בלילה יבוא, עדכון המוני, קמפיין עונתי מייצר סטטיסטיקות חדשות, שדוחפות את מודל העלות לצד השני של סף שהוא ישב לידו חודשים. אין כאן פגם בקוד, אין שינוי הגדרות, שום דבר "לא נשבר" וזה בדיוק מה שהופך את התקלה לכל כך קשה לאבחון.

שני הדברים שמאפשרים לזה לקרות דלוקים כברירת מחדל, ואין סיבה לכבות אותם: innodb_stats_persistent = ON ו-innodb_stats_auto_recalc = ON.

אפשר לראות את זה ישירות, וזו השאילתה הכי שימושית לאבחון בדיעבד:

SELECT table_name, n_rows, clustered_index_size, last_update
FROM mysql.innodb_table_stats
WHERE database_name = 'shop_db'
ORDER BY last_update DESC
LIMIT 30;

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

הפרט שהופך את זה לגרוע יותר: גודל המדגם

SHOW GLOBAL VARIABLES LIKE 'innodb_stats_persistent_sample_pages';

שימו לב לשם המשתנה. innodb_stats_sample_pages זה שרוב האנשים מחפשים מסומן deprecated בקוד המקור, ברירת המחדל שלו היא 8, והוא מתייחס לסטטיסטיקות ה-transient. כיוון שסטטיסטיקות persistent דלוקות כברירת מחדל (innodb_stats_persistent = ON), המשתנה שרלוונטי אליכם הוא innodb_stats_persistent_sample_pages, וברירת המחדל שלו היא 20.

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

למה restart לא עוזר

הסטטיסטיקות של InnoDB הן persistent. הן נשמרות על הדיסק בטבלאות mysql.innodb_table_stats ו-mysql.innodb_index_stats, ושורדות אתחול. אחרי כל הפעלה מחדש המערכת קוראת בדיוק את אותם מספרים, מגיעה לאותו חישוב, ובוחרת שוב באותו מסלול שגוי.

# my.cnf נקרא בזמן shutdown, לא בזמן startup
innodb_buffer_pool_dump_pct = 75

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

התיקון

ברמת הסשן לבדיקה, בלי לגעת בכלום

SET SESSION optimizer_switch = 'rowid_filter=off';
SET SESSION optimizer_use_condition_selectivity = 1;

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

ברמת השרת בזמן ריצה

SET GLOBAL optimizer_switch = 'rowid_filter=off';
SET GLOBAL optimizer_use_condition_selectivity = 1;

שתי הערות שחוסכות בלבול: הערך הגלובלי חל על חיבורים חדשים בלבד pools קיימים של PHP-FPM ימשיכו עם הערך הקודם עד שיתחברו מחדש. ובנוסף, ל-MariaDB 10.4 אין SET PERSIST (זו תכונה של MySQL 8), ולכן חייבים גם את שינוי הזמן-אמת וגם את הרשומה ב-my.cnf.

ב-my.cnf קבוע

[mysqld]
optimizer_switch                    = "rowid_filter=off"
optimizer_use_condition_selectivity = 1

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

optimizer_switch = "rowid_filter=off,condition_pushdown_for_derived=off"

איזה קובץ? אל תכתבו ל-/etc/my.cnf באופן עיוור בדקו מה נטען בפועל:

grep -r '!include' /etc/my.cnf /etc/mysql/ 2>/dev/null

# Debian / Ubuntu → /etc/mysql/mariadb.conf.d/50-server.cnf
# RHEL / Alma / Rocky → /etc/my.cnf.d/server.cnf

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

SHOW GLOBAL VARIABLES LIKE 'optimizer_switch'\G
SHOW GLOBAL VARIABLES LIKE 'optimizer_use_condition_selectivity';

איך מאמתים שזה באמת התיקון

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

קודם כל: הטיעון הלוגי, בלי להריץ שום דבר

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

• טבלאות ה-tmp_indexer היו טריות בשני המקרים. Magento מוחק ובונה אותן בכל ריצה, כולל בריצה האיטית. כלומר "טבלה פגומה שריצה קודמת השאירה" אינה יכולה להסביר את ההבדל בין הריצה האיטית לריצות המהירות במצב הטבלאות לא היה שום הבדל.
• ה-restart החמיר את התנאים, לא שיפר אותם. הוא זרק את רוב ה-buffer pool. הריצות המהירות רצו על cache קר יותר מהריצה האיטית, ובכל זאת הסתיימו בסדרי גודל מהר יותר. restart לא יכול להסביר שיפור כשהוא עצמו מוריד ביצועים.

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

אימות בקריאה בלבד בטוח להרצה על production

רוצים מספר קונקרטי ולא רק טיעון? אפשר לקבל אותו בלי לגעת בכלום. מריצים את צד ה-SELECT של השאילתה בלבד, בלי ה-INSERT, עם LIMIT, בסשן בודד:

SET SESSION optimizer_switch = 'rowid_filter=on';
SET SESSION optimizer_use_condition_selectivity = 4;

ANALYZE FORMAT=JSON
SELECT et.entity_id, ... FROM catalog_product_entity_tmp_indexer AS et
  INNER JOIN ...
LIMIT 1000;

זו פעולת קריאה, בסשן אחד, שאינה כותבת לשום טבלה ואינה משנה שום הגדרה גלובלית. השוו את rows ל-r_rows בפלט: הפער ביניהם הוא בדיוק גודל הטעות של האופטימייזר בהערכה. אחר כך חזרו על אותה פקודה עם rowid_filter=off ו-selectivity=1, והשוו את התוכניות. זו ההוכחה, והיא לא עולה לכם דבר.

בפלט חפשו Using join buffer, Using rowid filter, וסריקות מלאות על טבלאות ה-_tmp_indexer. על thread שרץ כרגע, בלי לחכות שיסתיים:

SHOW EXPLAIN FOR 5793;

A/B מלא רק בסביבת בדיקות או בחלון תחזוקה

הבדיקה החד-משמעית ביותר היא להחזיר את ברירות המחדל של 10.4 ולתזמן ריאינדוקס שלם:

SET GLOBAL optimizer_switch = 'rowid_filter=on';
SET GLOBAL optimizer_use_condition_selectivity = 4;

time bin/magento indexer:reindex catalog_product_flat

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

הניחוש המבוסס הוא optimizer_use_condition_selectivity: ברירת המחדל שלו השתנתה מ-1 ל-4 בין 10.3 ל-10.4, ושינוי בודד זה עומד מאחורי חלק גדול מדיווחי "עשינו שדרוג ו-Magento נפל".

באילו מצבים נוספים ההגדרות האלה עוזרות

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

ומתי זה עלול להזיק

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

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

rowid_filter=off צר בהרבה וסיכון נמוך משמעותית.

מה זה לא פותר

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

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

אפשרויות משלימות, לפי סדר עלות:

• העלאת innodb_stats_persistent_sample_pages ל-100–200 הופך את ההערכות ליציבות וחזרתיות. זול, ומקטין את הסיכוי להתהפכות אקראית.
• Engine-Independent Table Statistics והיסטוגרמות ANALYZE TABLE ... PERSISTENT FOR ALL על הטבלאות היציבות (לא הזמניות) נותן לאופטימייזר נתונים טובים יותר במקום פחות נתונים.
• ביטול flat catalog Magento הוציאה את הפיצ'ר משימוש, והוא מקור מוכר לתקיעות אינדוקס ארוכות בקטלוגים גדולים. אם החזית לא תלויה בו, כיבוי מסיר את הבעיה מהשורש במקום לכייל אותה. הבדיקה: Stores → Configuration → Catalog → Catalog → Storefront → Use Flat Catalog Product.
• שדרוג גרסה  10.4 סבלה משנה ראשונה לא פשוטה עם rowid_filter, וסדרה של תיקוני רגרסיה וקריסות נכנסה לאורך קו 10.4/10.5. ב-10.6 ומעלה התמונה טובה בהרבה. 10.4 הגיעה לסוף חיים, וזו סיבה טובה בפני עצמה.

צ'קליסט מהיר

1. הריצו show full processlist בלי פילטרים
2. information_schema.innodb_trx יש טרנזקציה נטושה?
3. SHOW ENGINE INNODB STATUS מה ה-History list length?
4. יש PRIMARY KEY (entity_id) בכל שבע טבלאות ה-_tmp_indexer?
5. שלילת תשתית: df -h, dmesg -T, iostat -x 1, free -h
6. mysql.innodb_table_stats אילו סטטיסטיקות חושבו מחדש ומתי?
7. בדיקת סשן: rowid_filter=off + selectivity=1, ואז הרצה מתוזמנת
8. אם עזר קבעו ב-my.cnf, ואמתו ב-SHOW GLOBAL VARIABLES
9. אימות בקריאה בלבד: ANALYZE FORMAT=JSON על ה-SELECT, השוואת rows מול r_rows

למקרא נוסף

• optimizer_use_condition_selectivity ההגדרה המרכזית, כולל טבלת הרמות
• optimizer_switch התחביר וכל הדגלים
• Rowid Filtering Optimization איך התכונה עובדת ומתי היא מזיקה
• InnoDB Persistent Statistics למה restart לא מאפס את הבעיה
• ANALYZE statement  ANALYZE FORMAT=JSON להשוואת rows מול r_rows

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

• sql/sys_vars.cc  optimizer_use_condition_selectivity, כולל DEFAULT(4) ותיאור חמש הרמות. השוו לאותו קובץ בענף 10.3, שם כתוב DEFAULT(1).
• sql/sql_priv.h OPTIMIZER_SWITCH_DEFAULT, שם מופיע OPTIMIZER_SWITCH_USE_ROWID_FILTER

שורה תחתונה

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

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

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

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