MySQL server hardware, OS and configuration tuning tips

Hardware and OS

1. ควรใช้ 64-bit OS เพราะรองรับการเพิ่ม memory ที่มากขึ้น
2. ไม่ใช้ software raid
3. เลี่ยงการใช้ RAID 5 และใช้ RAID 10 แทน
4. ถ้าหากมี Battery-Backed Cache RAID controllers จะดีมาก
5. ใช้ disk ไม่ต้องใหญ่มากจะทำให้มีประสิทธิภาพดีกว่า
6. ใช้ disk ที่มีความเร็วในการอ่านและเขียน
7. แยก data กับ OS ให้อยู่คนละ partition
8. mount filesystem เป็นแบบ noatime และ nodirtime
9. ใช้ DEADLINE IO scheduler แทนการใช้ CFQ หรือ ANTICIPATORY IO scheduler โดยให้เพิ่ม elevator=deadline เข้าไปที่ ไฟล์ grub.conf ตรงส่วนของ kernel ที่ต้องการโหลด
   [shell]title CentOS (2.6.32-220.7.1.el6.x86_64)
   root (hd0,0)
   kernel /vmlinuz-2.6.32-220.7.1.el6.x86_64 ro root=LABEL=/ quiet elevator=deadline
   initrd /initramfs-2.6.32-220.7.1.el6.x86_64.img[[/shell]
10. Install package เท่าที่ใช้งานและลบ package ที่ไม่จำเป็นออก
11. ตรวจสอบจำนวนไฟล์ที่ mysql user สามารถเปิดได้ ด้วยคำสั่ง ulimit ถ้าหาก database มีจำนวน table มากควรเพิ่มให้พอเหมาะ
12. mysql temp space และ replication logs ไม่ควรอยู่ใน partition เดียวกับ data
13. ป้องกันการใช้งาน swap โดยใส่ option “vm.swappiness=0” ใน /etc/sysctl.conf
14. ใช้ XFS filesystem เพื่อเพิ่มประสิทธิภาพและป้องกัน double buffering (เกิดใน ext3) เมื่อใช้ร่วมกับ MySQL
15. เพิ่มชื่อเครื่องและ IP ที่ใช้งานประจำในไฟล์ /etc/hosts จะได้ไม่ต้องไป lookup ที่ dns
16. ไม่ควร force kill MySQL process เพราะจะทำให้ Database เกิด corrupt

Configuration

1. ใช้ innodb_flush_method=O_DIRECT เพื่อป้องกันการเกิด double buffer เมื่อมีการเขียนข้อมูล
2. หลีกเลี่ยงการใช้ O_DIRECT และ EXT3 filesystem เพราะจะทำให้เขียนเป็นแบบ serialize
3. ใช้ skip-name-resolve
4. innodb_buffer_pool_size ให้มีขนาดไม่เกิน 70-80% ของจำนวน memory กำหนดไว้เพื่อให้ระบบ load InnoDB file ไปยัง memory
5. ไม่ควรให้ innodb_log_file_size มีขนาดใหญ่เกินไป ประมาณ 256MB กำลังดี ถ้าหากขนาดใหญ่มากจะเร็วกว่าแต่จะทำให้ช้าถ้าหาก recovery เมื่อระบบเกิด crash
6. ไม่ควรให้ max_connections มีค่ามาก เพราะถ้ามากเกินและถ้าหากมี connection เข้ามามากจะทำให้ใช้งาน memory มากเกินกว่า memory ที่มีอยู่อาจจะทำให้ระบบล่มได้
7. innodb_log_buffer_size ไม่ควรเกิน 2-8MB เว้นแต่จะใช้ BLOBs ขนาดใหญ่
8. ต้องมี innodb_file_per_table เพื่อป้องกันไฟล์ main tablespace โตเกินไป และจะจัดเก็บแต่ละ table เป็นไฟล์ของตัวเอง
9. ใช้ query cache เมื่อข้อมูลไม่มีการเปลี่ยนแปลงบ่อย
10. เพิ่ม temp_table_size และ max_heap_table_size เพื่อป้องกันการเขียนบน disk
11. ไม่ควรให้ sort_buffer_size มีค่ามาก เพราะ sort_buffer_size มีการใช้ memory ตามจำนวน connection ที่เข้ามา

Backup

1. ควร backup จากเครื่อง slave
2. ไม่ควรไว้ใจ LVM snapshot เพื่อ backup เนื่องจากอาจเกิด data inconsistencies ได้
3. หากต้องการ online backup แนะนำให้ใช้ xtrabackup