15 เคล็ดลับการปรับแต่งและเพิ่มประสิทธิภาพ MySQL/MariaDB ที่เป็นประโยชน์
MySQL เป็น ระบบการจัดการฐานข้อมูลเชิงสัมพันธ์ แบบโอเพ่นซอร์สที่มีประสิทธิภาพ หรือเรียกสั้นๆ ว่า RDBMS เปิดตัวในปี 1995 (อายุ 20 ปี) ใช้ Structured Query Language ซึ่งอาจเป็นตัวเลือกยอดนิยมที่สุดในการจัดการเนื้อหาภายในฐานข้อมูล MySQL เวอร์ชันล่าสุดคือ 5.6.25 และเผยแพร่เมื่อ 29 พฤษภาคม 2015
ข้อเท็จจริงที่น่าสนใจเกี่ยวกับ MySQL ก็คือชื่อนี้มาจากลูกสาวของ Michael Widenius (ผู้สร้าง MySQL) My แม้ว่าจะมีข้อเท็จจริงที่น่าสนใจมากมายเกี่ยวกับ MySQL แต่บทความนี้มีจุดมุ่งหมายเพื่อแสดงแนวทางปฏิบัติที่เป็นประโยชน์เพื่อช่วยคุณจัดการเซิร์ฟเวอร์ MySQL ของคุณ
ในเดือนเมษายน 2009 โครงการ MySQL ถูกซื้อโดย Oracle ผลก็คือมีการสร้าง Community Fork ของ MySQL ชื่อ MariaDB เหตุผลหลักในการสร้างทางแยกคือเพื่อให้โครงการเป็นอิสระภายใต้ใบอนุญาตสาธารณะทั่วไป
ปัจจุบัน MySQL และ MariaDB เป็นหนึ่งใน RDBMS ที่ใช้บ่อยที่สุด (หากไม่ใช่มากที่สุด) ซึ่งใช้สำหรับเว็บแอปพลิเคชัน เช่น WordPress, Joomla, Magento และอื่นๆ
บทความนี้จะแสดงเคล็ดลับพื้นฐานที่เป็นประโยชน์บางประการในการปรับแต่งประสิทธิภาพของ MySQL/MariaDB อย่างละเอียด โปรดทราบว่าบทความนี้ถือว่าคุณได้ติดตั้ง MySQL หรือ MariaDB ไว้แล้ว หากคุณยังคงสงสัยว่าจะติดตั้งอย่างไรบนระบบของคุณ คุณสามารถทำตามคำแนะนำโดยละเอียดของเราได้ที่นี่:
- การติดตั้ง LAMP บน RHEL/CentOS 7
- การติดตั้ง LAMP บน Fedora 22
- การตั้งค่า LAMP บน Ubuntu 15.04
- การติดตั้ง MariaDB บน Debian 8
- ติดตั้ง MariaDB บน Gentoo Linux
- ติดตั้ง MariaDB บน Arch Linux
สำคัญ: ก่อนที่เราจะเริ่ม – อย่ายอมรับคำแนะนำนี้อย่างสุ่มสี่สุ่มห้า การตั้งค่า MySQL แต่ละรายการมีเอกลักษณ์เฉพาะตัวและต้องใช้ความคิดเพิ่มเติมก่อนทำการเปลี่ยนแปลงใดๆ
สิ่งที่คุณต้องรู้:
- ไฟล์การกำหนดค่า MySQL/MariaDB อยู่ใน
/etc/my.cnf
ทุกครั้งที่คุณแก้ไขไฟล์นี้ คุณจะต้องรีสตาร์ทบริการ MySQL เพื่อให้การเปลี่ยนแปลงใหม่มีผล - สำหรับการเขียนบทความนี้มีการใช้ MySQL เวอร์ชัน 5.6 เป็นเทมเพลต
1. เปิดใช้งาน InnoDB ไฟล์ต่อตาราง
อันดับแรก สิ่งสำคัญคือต้องอธิบายว่า InnoDB เป็นเครื่องมือจัดเก็บข้อมูล MySQL และ MariaDB ใช้ InnoDB เป็นเครื่องมือจัดเก็บข้อมูลเริ่มต้น ในอดีต MySQL ใช้เพื่อเก็บตารางฐานข้อมูลและดัชนีในพื้นที่ตารางของระบบ วิธีการนี้มีไว้สำหรับเซิร์ฟเวอร์ที่มีวัตถุประสงค์เพียงอย่างเดียวคือการประมวลผลฐานข้อมูล และดิสก์จัดเก็บข้อมูลไม่ได้ถูกใช้เพื่อวัตถุประสงค์อื่นใด
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
บางครั้งการอ่าน/เขียน OS อาจทำให้ประสิทธิภาพของเซิร์ฟเวอร์ MySQL ของคุณช้าลง โดยเฉพาะอย่างยิ่งหากอยู่บนฮาร์ดไดรฟ์เดียวกัน ฉันขอแนะนำให้ใช้ฮาร์ดไดรฟ์แยกต่างหาก (ควรเป็น SSD) สำหรับบริการ MySQL แทน
เพื่อให้เสร็จสมบูรณ์ คุณจะต้องแนบไดรฟ์ใหม่เข้ากับคอมพิวเตอร์/เซิร์ฟเวอร์ของคุณ สำหรับวัตถุประสงค์ของบทความนี้ ฉันจะถือว่าไดรฟ์อยู่ภายใต้ /dev/sdb
ขั้นตอนต่อไปคือการเตรียมไดรฟ์ใหม่:
fdisk /dev/sdb
ตอนนี้กด “n ” เพื่อสร้างพาร์ติชันใหม่ จากนั้นกด “p ” เพื่อทำให้พาร์ติชั่นใหม่เป็นพาร์ติชั่นหลัก หลังจากนั้น ให้ตั้งค่าหมายเลขพาร์ติชันจาก 1-4 หลังจากนั้นคุณจะเลือกขนาดพาร์ติชัน กด Enter ที่นี่ ในขั้นตอนถัดไป คุณจะต้องกำหนดขนาดของพาร์ติชัน
หากคุณต้องการใช้ทั้งดิสก์ ให้กด Enter อีกครั้ง มิฉะนั้นคุณสามารถกำหนดขนาดของพาร์ติชันใหม่ได้ด้วยตนเอง เมื่อพร้อมแล้ว ให้กด “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
ฉันขอแนะนำให้คุณหยุด Apache/nginx ด้วยเพื่อป้องกันความพยายามในการเขียนลงในฐานข้อมูล:
service httpd stop
service nginx stop
ตอนนี้คัดลอกไดเร็กทอรี MySQL ทั้งหมดในไดรฟ์ใหม่:
cp /var/lib/mysql /ssd/ -Rp
การดำเนินการนี้อาจใช้เวลาสักครู่ขึ้นอยู่กับไซต์ฐานข้อมูล MySQL ของคุณ เมื่อกระบวนการนี้เสร็จสิ้นให้เปลี่ยนชื่อไดเร็กทอรี MySQL:
mv /var/lib/mysql /var/lib/mysql-backup
ต่อไปเราจะสร้าง symlink
ln -s /ssd/mysql /var/lib/mysql
ตอนนี้คุณพร้อมที่จะเริ่ม MySQL และบริการเว็บแล้ว:
service mysqld start
service httpd start
service nginx start
ณ จุดนี้ฐานข้อมูล MySQL ของคุณจะถูกเข้าถึงจากไดรฟ์ใหม่