דף הבית » טריקים וטיפים

דוגמא מהפרקטיקה: להקטין את גדול מסד הנתונים של וורדפרס

23 בפברואר 2013 | מאת רמי | 9 תגובות | תגיות: ,

היום אציג דוגמא לבעיה מוזרה שנתקלתי בה במסד הנתונים של אחד האתרים שלי. גודל מסד הנתונים הוא 96MB, כאשר 80MB מקורם בטבלת wp_commentmeta ששומרת מידע נוסף על התגובות. 80MB בטבלת התגובות זה לא דבר חריג, אבל במקרה של האתר הזה, יש לו רק 58 תגובות. השאלה היא מה כל המידע העודף? איך מוחקים אותו? ואיך זה קשור לוורדפרס?

לוגו MySQL

לוגו MySQL

רקע כללי

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

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

הבעיה

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

מתוך כ-96MB, כ-80MB מקורם בטבלת wp_commentmeta. טבלה זו שומרת מידע נוסף (meta data) על התגובות. זה קצת מוזר כי בטבלת התגובות wp_comments יש רק 162 שורות, ובטבלת wp_commentmeta יש 48,876 שורות. מוזר לא?

מסד הנתונים, עם הטבלאות הבעייתיות

מסד הנתונים, עם הטבלאות הבעייתיות

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

טבלת wp_commentmeta

טבלת wp_commentmeta

בדיקה קצרה בגוגל מלמדת אותנו שזו בעיה נפוצה. באחת הגרסאות הקודמות של אקיזמט הוא התחיל לנפח את מסד הנתונים והיו הרבה תלונות על כך. על פי ה-ChageLog, גרסה 2.5.4 הבעיה תוקנה (Purge old Akismet comment meta data, default of 15 days). מסתבר שהבעיה לא באמת תוקנה… 😉

הפתרון

יש מספר פתרונות לבעיה זו אבל הפתרון הנפוץ ביותר הוא לנקות את ה-metadata של אקיזמט בטבלת wp_commentmeta בעזרת פקודת ה-SQL הבאה:

DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

נראה שזה עושה את העבודה. הייתי ממליץ להריץ גם את התוסף WP-Optimize (תוסף שיצא לי לתרום לו) או כל תוסף דומה אחר.

מסד הנתונים אחרי התיקונים

מסד הנתונים אחרי התיקונים

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

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

מאת רמי

אחראי על אתר וורדפרס בעברית ועל תרגום ושחרור גרסאות וורדפרס בעברית. מפתח ליבה, בכל גרסה החל מגרסה 2.8, מפתח מוביל בגרסה 4.4 ובגרסה 4.6. מספק שירותי פיתוח אתרים, פיתוח תוספי וורדפרס לסטרטאפים ופיתוח כלים למפתחי וורדפרס - GenerateWP.com.

9 תגובות »

  • מאור חזן כותב :

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

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

    add_comment_metadata

    שנמצא ב-wp-includes/meta.php. יש לבצע בדיקת השוואה האם המפתח של ה-meta הוא akismet_history. במידה וכן, אפשר להחזיר כל ערך ששונה מ-NULL והערך לא יעבור למסד הנתונים.

    הנה הקוד הסופי:
    https://gist.github.com/maor/5025917

    אופצייה נוספת (והרבה פחות מומלצת) היא לנקות את ה-meta בצורה תקופתית-כרונולוגית, ע"י שימוש בפונקציות cron.

  • רמי כותב :

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

    במקרה הספציפי הזה, העדפתי SQL. זה מהיר יותר. וחד-פעמי. ובמכה אחת הקטנתי את מסד הנתונים ב-83%.

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

    DELETE FROM wp_commentmeta
    WHERE comment_id
    NOT IN (SELECT comment_id FROM wp_comments);

    שזה עדיף. כי ככה לא מוחקים metadata של תגובות קיימות.

    אבל בסופו של דבר שיקולים אחרים הכריעו.

  • מאור חזן כותב :

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

    מה שהפתרון שלי מציע הוא בעצם דרך להפטר אחת ולתמיד מההזנה האוטומטית של metadata שאקיזמט עושה (כרגע). קל יותר להשתיל קוד שמונע מרשומות חדשות להכנס ל-wp_commentmeta מאשר לדאוג לנקות את מסד הנתונים כל תקופה מסויימת. מה גם שכשאתה מכניס נתונים למסד נתונים ומוחק על בסיס גבוהה, לעניות דעתי זה שוחק את מסד הנתונים במידה מסויימת, ואז גם יש צורך לבצע אופטימיזצייה וכל מיני דברים נוספים. אה, והאינדקס סתם מתנפח (ה-UNIQUE KEY — ה-ID) – שזה גם חבל.

  • רמי כותב :

    מאור, אני מקבל את הטיעון שלך. מילה במילה!

  • Isramap כותב :

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

  • רמי כותב :

    היי Isramap, צמצום של 20% זה יפה.

    אני זוכר שהעברתי אתר גדול לוורדפרס ובעקבות המעבר (ועוד כמו פעולות) מסד הנתונים הצטמצם מ- 1.5GB ל- 300MB. חסכון של 1.2GB כ-80%.

    בדיקה מעמיקה גילתה שהמערכת הישנה (לא וורדפרס) שמרה בפוסטים הרבה פקודות HTML (הטקסט וה-HTML נשמרו ביחד ב-DB). וורדפרס, לעומת זאת, שומרת מידע גולמי (טקסט בלבד) ולפני ההצגה באתר מוסיפה אוטומטית תגיות HTML כמו פסקאות P.

    בקיצור, עם מתעמקים קצת, אפשר למצוא הרבה שומנים מיותרים.

  • Isramap כותב :

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

  • אסף כהן כותב :

    פוסט מעולה שחבל שנתקלתי בו רק עכשיו.
    עשיתי סריקה על מסד הנתונים וגילית כל מני דברים מטרידים:
    1) אכן האקיסמט אוגר מידע. אבל מה שיותר אדיוטי – שאי אפשר לגשת למידע הזה מפאנל הניהול – אז ממש מעניין לי ת'תחת (סליחה על הביטוי) שאקיסמט סינן לי 38642845 תגובות – אבל למה הוא לא נותן לי אליהן לאחר המחיקה? אם בחרתי למחוק את התגובות – שיוריד מהDB. לא רואה שום הגיון בשמירה – אלא אם אקיזמט מתכוון לשלוח את הרשימה הזו מתישהו למפתחים כדי שיצרו אלגוריתם על בסיס למידה של הספאם. מעבר לזה – אני חושב שזה באג רציני במערכת.

    2) בזכות הסקירה הזו בדקתי עוד כמה פלאגינים שמתחזרים לי על הDB. למשל redirection שהLOG שלו ששוקל כרגע 15 מגה, ו50 אלף שורות. מבט פנימה מראה שהוא מתעד כל שימוש ברידיירדט (בעיקר הפניות לCSS התאמה לאקספלורר, והפניות מקישור מקוצר לפרנדלי URL ועוד טינופת מהסוג הזה) – למרות שנראה לי שזה מסד שלא מכביד מדי על המערכת.
    ותוסך counterize שכרגע שוקל 20 מגה ועשרות אלפי שורות. התוסף עף לקיבינימט. התרומה שלו לא שווה את הנזק.

    שוב – פוסט מצוין ומועיל.
    תודה.
    חבל שאין לך כפתורי שיתוף…
    3)

  • רמי כותב :

    היי אסף, שמח שאהבת את הפוסט. 🙂

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

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

הוסף תגובה !

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

תגיות HTML מורשות לשימוש:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>