ค้นหาเว็บไซต์

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 ไว้แล้ว หากคุณยังคงสงสัยว่าจะติดตั้งอย่างไรบนระบบของคุณ คุณสามารถทำตามคำแนะนำโดยละเอียดของเราได้ที่นี่:

  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 ใช้เพื่อเก็บตารางฐานข้อมูลและดัชนีในพื้นที่ตารางของระบบ วิธีการนี้มีไว้สำหรับเซิร์ฟเวอร์ที่มีวัตถุประสงค์เพียงอย่างเดียวคือการประมวลผลฐานข้อมูล และดิสก์จัดเก็บข้อมูลไม่ได้ถูกใช้เพื่อวัตถุประสงค์อื่นใด

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 ของคุณจะถูกเข้าถึงจากไดรฟ์ใหม่