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

הביטו בזמן שהבעיה מתרחשת

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

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

ל-PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

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

חיבורים

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

אם Max_used_connections מתקרב ל-max_connections, האפליקציה מקבלת מדי פעם סירוב «too many connections». והעלאת המגבלה אינה הדבר הראשון שצריך לעשות: כל חיבור תופס זיכרון, והעלאתה בשרת שכבר חסר לו זיכרון רק תזרז את המסע לאזור ההחלפה. תחילה בררו למה החיבורים אינם משתחררים: בדרך כלל שאילתה ארוכה או היעדר מאגר חיבורים בצד האפליקציה.

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

שאילתות איטיות

בלי יומן שאילתות איטיות אין לאן להתקדם. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

יממה אחר כך:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, ב-postgresql.conf:

log_min_duration_statement = 1000

אחר כך ההרחבה pg_stat_statements, שמציגה את הזמן הכולל לכל שאילתה ישירות:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

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

ההגדרה שנשכחת לרוב

ל-MySQL ול-MariaDB זהו גודל מאגר החוצץ של InnoDB. ערך ברירת המחדל הוא 128 מגה-בייט והוא לא השתנה עשרות שנים. ובשרת שבו מסד הנתונים תופס כמה ג׳יגה-בייטים פירוש הדבר קריאה מתמדת מהדיסק במקום מהזיכרון.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

אמת מידה סבירה לשרת מסד נתונים ייעודי היא מחצית ה-RAM; ולשרת שרץ עליו גם שרת אתרים וגם PHP, רבע, מתוך תשומת לב לכך שלא ייכנס לאזור החלפה. וההגדרה האחת הזאת נותנת בדרך כלל יותר משבוע של אופטימיזציית שאילתות.

שני דברים שאוכלים את הדיסק בשקט

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

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

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

קובצי WAL ב-PostgreSQL עם חריץ שכפול שנשכח. מקרה פחות מוכר ומסוכן יותר: אם קיים חריץ שכפול אבל הצרכן שלו כבר אינו מתחבר, PostgreSQL מחויב לשמור כל יומן מרגע הניתוק — וישמור עד שהדיסק ייגמר.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

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

גודל וגידול

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

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

אבטחה בשתי שורות

ואם כבר קונסולת מסד הנתונים פתוחה, שווה לבדוק שני דברים. ראשית, האם מסד הנתונים נגיש מבחוץ (ss -tulpn | grep 3306): כמעט אף פעם הוא אינו צריך להביט החוצה. ושנית, חשבונות שיכולים להתחבר מכל כתובת:

mysql -e "SELECT user, host FROM mysql.user"

רשומות עם host = '%' פירושן «חיבורים מכל מקום». ואם גם הפורט פתוח, הדבר היחיד שעומד בין מסד הנתונים לאינטרנט הוא סיסמה.

גיבויים

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

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