MySQL optimization

לפני כמה שבועות פרסמתי פוסט על אופטימיזציות.
הנה ההמשך.
דוגמא מעשית, שיפור ביצועים של שאילתות בFireStats (לגרסא 1.6)
מנגנון מקובל לשיפור ביצועים הוא מטמון (cache). הרעיון פשוט : בפעם הראשונה ששואלים אותנו משהו אנחנו מחשבים אותו וזוכרים את התוצאה. בפעם השניה אנחנו משתמשים בתוצאה שחישבנו קודם.
כשבודקים ביצועים, חשוב לוודא שהבדיקה נותנת תוצאות זהות בשתי הרצות שונות. במקרים רבים יש מנגנוני מטמון יגרמו לבדיקה לרוץ הרבה יותר מהר בפעם השניה, ובדרך כלל הם רק מפריעים בסיטואציות של בדיקת ביצועים כי אנחנו רוצים למדוד את השיפור שנובע מהקוד שלנו, ולא מזה שהCache הכיל את התוצאה שכבר חישבנו בהרצה הראשונה.
דוגמא לכך היא הCache של MySQL: ההרצה השניה של שאילת תמיד תהיה הרבה יותר מהירה מההרצה הראשונה, כי MySQL זוכר את התוצאות מהפעם הראשונה ואם שום דבר בנתונים לא השתנה הוא פשוט מחזיר את אותה תוצאה.

כדי לבטל את אותו Cache, אפשר להשתמש בפקודת הMySQL:
[code lang="sql"]
SET GLOBAL query_cache_size = 0;
[/code]

FireStats תומך בגרסאות MySQL ממשפחת 4.0, 4.1 ו5.0, כאשר ההבדל בין 4.0 ל4.1 כל כך משמעותי שיש שאילתות בFireStats שכתובות אחרת לכל אחת מהגרסאות.
נסיון לשיפור ביצועים, במיוחד כזה שמבוסס על שינויים בסכמת הנתונים יצריך שינויים בקוד שמטפל ב4.0 ובקוד של 4.1, לכן כדאי לבדוק שהכל עובד על השרת הרלוונטי. בנוסף, מכיוון שרוב (60%) משתמשי FireStats עובדים עם MySQL 5.X, כדי גם לבדוק את השיפור על השרת הזה.
כדי לבצע את זה, הרמתי שלושה שרתי MySQL, נציג אחד מכל משפחת גרסאות, והכנסתי לכל שרת חבילת נתונים די כבדה (וזהה בין השרתים) שמבוססים על סטטיסטיקות מהשרת שלי.
ובכל אחד מהשרתים ביטלתי את מטמון השאילתות (query_cache_size).
יצרתי טבלא בגנומטיק (כמו אקסל), שנראית ככה (תת טבלא כזו לכל בדיקה שאני רוצה)

נניח שהפונקציה שאני רוצה לשפר היא:
[code lang="php"]
function foo()
{
$sql = "SELECT * from …";
return query($sql);
}
[/code]

קודם כל הכפלתי את הקוד והוספתי הדפסה של שאילת הSQL שנשלחת לשרת:
[code lang="php"]
function foo()
{
if (true) // old code
{
$sql = "SELECT * from …";
echo $sql; return;
return query($sql);
}else{
$sql = "SELECT * from …";
echo $sql; return;
return query($sql);
}
}
[/code]
מכאן זו היתה שיטת העבודה:
1. הרצה של הקוד במצב הישן.
2. העתקה של השאילתה שהודפסה, והרצה שלה ישירות על כל אחד משלושת בסיסי הנהנתונים. ושמירה של הזמן שנדרש בטבלא מסודרת.
3. מעבר למצב חדש (פשוט לשנות את הfalse לtrue בראש הפונקציה), אופטימיזציה של השאילתה וחזרה על הבדיקה מול כל אחד מהשרתים, ושוב שמירה של התוצאות בטבלא, הפעם בעמודה של תוצאות אחרי אופטימיזציה.

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

אז מה בעצם עשיתי?
פיירסטטס תומך בכמה אתרים בו זמנית, ולכן יש בו מזהה אתר. בהתחלה שמרתי את מזהה האתר בטבלאת הכניסות, בשלב מסויים הבנתי שאני צריך מזהה אתר גם בטבלאת הכתובות (URLים), כי הרי כל URL שיך לכל היותר לאתר אחד.
אז הוספתי את העמודה, אבל שכחתי למחוק את העמודה מטבלאת הכניסות.
מצב כזה של כפילות נתונים הוא לא מומלץ לפי הגישה המקובלת בתכנון בסיסי נתונים, כי הוא מאפשר מצבים של חוסר עקביות במידע.
באחת הגרסאות האחרונות של פיירסטטס תיקנתי את המעוות: הסרתי את העמודה מטבלאת הכניסות ותיקנתי את כל הקוד שהושפע מזה לבצע join עם טבלאת הכתובות כשהוא צריך גישה לאתר.
על פניו הדבר הנכון לעשות: אבל זה יצר בעיה חדשה:
כמעט כל השאילתות דרשו עכשיו join נוסף. עבור משתמשים עם בסיסי נתונים קטנים (נניח עד חצי מליון כניסות בטבלאת הכניסות ועד 50 אלף כתובות בטבלאת העסק עבד סביר, אבל עבור משתמשים עם בסיסי נתונים גדולים יותר העומס על השרת התחיל לגדול בצורה חדה ככל שנפח הנתונים עלה.
הפתרון שלי הוא לחזור בי מהדבר "הנכון" שעשתי קודם, כלומר להחזיר את עמודת הsite_id לטבלאת הכניסות, שתשמש לצרכי פילטור בזמן השאילות כדי להמנע מהjoin עם טבלאת הכתובות.

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

ככה נראית הטבלא שלי אחרי שמילאתי אותה: