15 טיפים שימושיים לכיוון ואופטימיזציה של MySQL/MariaDB

MySQL הוא מערכת ניהול מסד נתונים רב רשמי וקוד פתוח עוצמתית או בקיצור RDBMS. הוא יצא לאור בשנת 1995 (בן 20). הוא משתמש ב־Structured Query Language שהיא כנראה הבחירה הפופולרית ביותר לניהול תוכן בתוך מסד נתונים. גרסת MySQL האחרונה היא 5.6.25 ויצאה ב־29 במאי 2015.

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

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

היום MySQL ו־MariaDB הם אחת מהמערכות הניהול הרב רשמי הנפוצות ביותר (אם לא הכי נפוצות) המשמשות ליישומי אינטרנט כמו WordPress, Joomla, Magento ואחרים.

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

1. מערכת התקנה של LAMP על RHEL/CentOS 7
2. מערכת התקנה של LAMP על Fedora 22
3. הגדרת מערכת LAMP על Ubuntu 15.04
4. התקנת MariaDB על Debian 8
5. התקנת MariaDB על Gentoo Linux
6. התקנת MariaDB על Arch Linux

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

דברים שעליכם לדעת:

1. קובץ ההגדרות של MySQL/MariaDB נמצא במקום /etc/my.cnf. בכל פעם שתערברו בקובץ הזה תצטרכו להפעיל מחדש את שירות הMySQL כדי שהשינויים החדשים יוכלו להתחיל להיות מופעלים.
2. בשביל כתיבת המאמר הזה נעשה באמצעות גרסת MySQL 5.6 כתב דוגמה.

1. אפשר את הקבצים של InnoDB לשם טבלה אחת

ראשית חשוב להסביר כי InnoDB היא מנוע אחסון. MySQL ו־MariaDB משתמשות ב־InnoDB כמנוע אחסון ברירת מחדל. בעבר, MySQL השתמשה בשמירת טבלאות מסד נתונים ואינדקסים ב־system tablespace. גישה זו הייתה מיועדת לשרתים שמטרתם היא עיבוד מסדי נתונים בלבד והדיסק האחסון שלהם לא נעשה בו שימוש למטרות אחרות.

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

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

ה־innodb_file_per_table מופעל כברירת מחדל ב־MySQL 5.6 ומעלה. ניתן לראות זאת בקובץ /etc/my.cnf. ההוראה נראית כך:

innodb_file_per_table=1

2. אחסון נתוני מסד הנתונים של MySQL במחיצה נפרדת

הערה: תצורה זו עובדת רק עם MySQL, אך לא עם MariaDB.

לעיתים קרובות קריאות/כתיבות של מערכת ההפעלה עשויות להשפיע על ביצועי השרת שלך ב־MySQL, במיוחד אם הן נמצאות על אותו דיסק קשיח. במקום זאת, אמליץ על שימוש בדיסק קשיח נפרד (רצוי SSD) עבור שירות ה־MySQL.

להשלים זאת, יהיה עליך לצרף את הדיסק החדש למחשב/שרת שלך. לצורך המאמר הזה, אני אניח כי הדיסק יהיה ב־/dev/sdb.

השלב הבא הוא להכין את הדיסק החדש:

# fdisk /dev/sdb

. כעת לחץ על "n" כדי ליצור מחיצה חדשה. לאחר מכן לחץ על "p" כדי להפוך את המחיצה החדשה לראשית. לאחר מכן, קבע את מספר המחיצה בטווח של 1-4. לאחר מכן תבחר את גודל המחיצה. לחץ על אנטר כאן. בשלב הבא תצטרך להגדיר את גודל המחיצה.

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

# mkfs.ext4 /dev/sdb1

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

# mkdir /ssd/

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

# mount /dev/sdb1  /ssd/

. באפשרותך לבצע את הערכה בהפעלה בהוספת השורה הבאה בקובץ /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

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

# service mysqld stop

I would recommend you stopping Apache/nginx as well to prevent any attempts to write in the databases:

# service httpd stop
# service nginx stop

. עכשיו העתק את כל תיקיית ה MySQL לדיסק החדש:

# cp /var/lib/mysql /ssd/ -Rp

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

# mv /var/lib/mysql /var/lib/mysql-backup

. לאחר מכן נעשה סימלינק.

# ln -s /ssd/mysql /var/lib/mysql

. עכשיו אתה מוכן להתחיל את השירותים של MySQL והאינטרנט שלך:

# service mysqld start
# service httpd start
# service nginx start

. בנקודה זו, בסיסי הנתונים של MySQL שלך ייכנסו מהדיסק החדש.