ภาคที่1
MySQL นับว่าเป็นหัวใจของ Web Server อีกตัวหนึ่งเลยก็ว่าได้เพราะว่า MySQL นั้นเป็นแหล่งข้อมูลที่สามารถเรียกใช้งานได้อย่างรวดเร็ว การ Config MySQL อิงตามรุ่น 4.1 โดยใช้กับ Server ที่ใช้ tis620 เป็น Default
เริ่มต้นที่การ Compile PHP ให้สนับสนุน MySQLปกติแล้วสามารถ Compile PHP ให้สนับสนุน MySQL ด้วยการใช้ –with-mysql วิธีการนี้จะเป็นการใช้ MySQL Lib Client ที่ Bundle มากับ PHP ซึ่งเป็น Version เก่า นอกจากนี้ยังมี Extension ใหม่ชื่อ MySQLi ซึ่งถ้าจะใช้ MySQLi จะไม่สามารถใช้ MySQL Lib Client ที่ Bungle มาด้วยได้ ดังนั้นเริ่มต้นให้ Compile PHP ด้วย –with-mysql=/usr/local/mysql (หรือถ้า mysql อยู่ที่อื่นก็ใช้ path อื่น)
เพื่อความสะดวกในการใช้งานภาษาไทย มักจะ setup ใน my.cnf ว่า default-character-set = tis620 วิธีการนี้จะทำให้ MySQL ทำงานช้าลงไปประมาณ 20 - 30%
หลังจากใส่คำสั่งว่า default-character-set = tis620 ลงไปใน my.cnf แล้ว ผลที่ได้คือ MySQL Client มันเพี้ยน เพราะว่า Charset ของ Server เป็น tis620 แต่ของ Client เป็น Latin
ดังนั้นต้อง setup เพิ่มอีกตัวหนึ่งคือ skip-character-set-client-handshake ใส่ส่วน my.cnf ครับ วิธีการนี้จะทำให้ Client ทำงานที่ Charset เดียวกับ Server เลยครับ
skip-locking - ปัจจุบันเปลี่ยนเป็น skip-external-locking เกี่ยวกับการทำ Repicate MySQL Server
skip-thread-priority - เป็นการกำหนดว่าไม่ต้องให้ thread แซงคิวกันได้ MySQL จะให้ QUERY แต่ละแบบมีความสำคัญไม่เท่ากัน แต่การเอาหัวข้อนี้ออกทำให้ระบบ queue ของ MySQL ไม่ต้องมายุ่งยากกับการจัดคิวและทำงานเป็น FIFO
skip-bdb - ถ้าใช้ bdb ก็ Comment บรรทัดนี้
skip-networking - อันนี้เป็นการบอก MySQL Server ว่าไม่ต้อง Listen ที่ INET SOCKET ให้ Listen ที่ UNIX SOCKET อย่างเดียวพอ อันนี้ไม่ได้เพิ่มความเร็วมากนัก แต่ลดโอกาสการโดนโจมตีได้log-slow-queries - อันนี้ใช้เฉพาะเวลาที่ต้องการดูว่า Query อันไหนทำงานช้า จะได้มาปรับแต่งได้
ภาคที่ 2 การบริหาร Thread
- ตัวแปรเกี่ยวกับ Thread ที่สำคัญของ MySQL คือ
thread_cache ตัวแปรนี้จะเป็นการไม่ทำลาย thread ของ MySQL ให้ต่ำกว่าเลขนี้ครับ ปกติก็เดาไปเรื่อยๆ ครับ โดยดูจาก Status ของ MySQL ให้ดูจาก phpMyAdmin จะสะดวกดี จะมีค่าเกี่ยวกับ thread คือ
Threads cached 143
Threads connected 7
Threads created 532
Threads running 1
Threads cached - คือจำนวน threads ที่อยู่ในโปรแกรม MySQL ตอนนี้ จะเห็นได้ว่ามี 143 threadsThreads connected - คือจำนวน threads ที่ใช้งานจริงๆ
Threads running - คือ threads ที่กำลังหาผลการ Query อยู่
Threads created - คือจำนวน threads ที่สร้างใหม่ตั้งแต่เริ่ม Server มา ถ้าค่านี้เพิ่มเร็วเกินไป ให้เพิ่มจำนวน Thread_cache ผลที่ได้คือ MySQL จะทำงานเร็วขึ้นนิดหน่อยเพราะว่าจะไม่ต้องเสียเวลา สร้างและทำลาย Threads บ่อยๆ
ภาคที่ 3 MYISAM กับหน่วยความจำ
ตัวแปรที่เราสนใจคือ
key_buffer=32M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=4M
- Key Buffer คือพื้นที่สำหรับ Cache ค่า Key ของแต่ละ Table ครับ โดยที่ Key ของ MySQL มี 3 ตัวคือ
PRIMARY, INDEX และ UNIQUE ปกติถ้ามีการใช้ Table MyISAM มากๆ ค่านี้ควรจะมากๆ แนะนำ 16MB สำหรับแรม 256 และเพิ่มมากขึ้นเมื่อแรมมากขึ้น
- Sort Buffer คือหน่วยความจำที่ MySQSL แต่ละ Connection จะจองเพิ่ม เพื่อทำ Table Scan ปกติถ้าคุณจะ Sort Field ที่ไม่ใช่ Key จะต้องใช้หน่วยความจำส่วนนี้เสมอ ให้ Setup เริ่มต้นตั้งแต่ 512K ขึ้นไป เนื่องจากเป็นหน่วยความจำที่จะมีการจองเพิ่มต่อ Connection ดังนั้นจะไม่ควรจะ Setup ให้สูงเกินเพราะว่าจะทำให้ MySQL ทำงานจนหน่วยความจำหมด
- Read Buffer คือหน่วยความจำที่ MySQL จะใช้ในการเก็บค่าที่อ่านจากตารางแบบต่อเนื่อง (คือไม่ได้ Sort) ไม่จำเป็นต้องมากนักก็ได้ เพราะว่าปกติเราจะมีการทำ LIMIT ในการอ่านค่ามาแสดงบนเว็บอยู่แล้ว
- Read-Random Buffer Size คือหน่วยความจำที่ MySQL จะใช้เก็บค่าจากตารางแบบไม่ต่อเนื่อง (เช่นผลการ Sort) ควรจะใหญ่กว่า Read Buffer (MyISAM ใช้)
ภาคที่ 4 tmp_table
ปกติแล้วในการ Complex Query นั้น MySQL จะทำการสร้างตารางผลลัทธ์ขึ้นมาในหน่วยความจำเป็น เป็น TABLE แบบ HEAP แต่ถ้าตารางมีขนาดใหญ่กว่าค่าค่าหนึ่ง MySQL จะคัดลอกตารางนั้นลง Disk เป็น MyISAM TABLE
Complex Query โดยมากเราจะพูดถึง Query ที่มีการใช้ GROUP BY, UNIQUE, LIKE และ SUB SELECT
วิธีการดูว่ามีการ Swap ลงหน่วยความจำมากน้อยแค่ไหน สามารถดูได้จาก
Created tmp disk tables 14652
Created tmp tables 222220
โดยเมื่อมีการสร้าง tmp_table MySQL จะเพิ่มค่า Created tmp tables และถ้ามีการ Swap ลง Disk จะเพิ่มค่า Created tmp disk tables ปกติ ถ้านำสองค่านี้มาหารกัน คูณ ร้อย ไม่ควรจะเกิน 5-10% ขึ้นอยู่กับว่าตารางที่คุณใช้ใหญ่เล็กอย่างไร มีความซับซ้อนมากแค่ไหน
ตัวแปรที่จะควบคุมการ Swap จะมี 2 ตัวคือ
tmp_table_size=32M
max_tmp_tables=32
โดยถ้า tmp_table ใหญ่กว่า tmp_table_size จะ Swap ลง Disk หรือถ้ามีจำนวน tmp_table มากกว่า max_tmp_tables ก็จะ Swap ลง Disk เช่นกัน ค่า tmp_table_size ปกติเป็น 32M และ max_tmp_tables จะเป็น 32M
คุณไม่ควร Setup ให้สูงกว่า 2 เท่าของค่าปกติ แต่แนะนำให้ลองไปตรวจสอบว่าโปรแกรมของคุณมีทางที่จะ Optimize Query ได้มากแค่ไหน หรือ จะใช้วิธีการ Cache ผมลัพธ์ของหน้าเว็บเข้ามาช่วยก็ได้
ภาคที่ 5 - Key Buffer แบบเชิงลึก
Key Buffer คือหน่วยความจำที่ MySQL จองไว้หนเดียว แล้วใช้งานแชร์กันทุกๆ Process
แต่เราจะมาพูดถึงประสิทธิภาพของ Key กัน ค่าที่น่าสนใจคือ
Key blocks unused 27683
Key blocks used 1312
Key read requests 1318393
Key reads 1344
คู่แรกจะบอกว่า Key Buffer ของคุณใช้งานไปมากน้อยแค่ใด ปกติแล้ว Key Blocks Unused จะไม่มากหรือเป็น 0 เลยก็ได้ อย่างตัวอย่างแสดงว่าเรากำหนดค่า Key_Buffer มากเกินไป
คู่ที่สองถ้าเอา (Key read requests - Key reads) * 100 / Key read requests เราจะเรียกว่า Key Hits Rate ตัวอย่างคือ 99.9 ครับ แสดงว่า Key Hits Rate ดีมาก ปกติแล้วจะอยู่ที่ประมาณ 95 - 99% ครับ ถ้าน้อยกว่านี้แนะนำให้เพิ่ม Key_Buffer สำหรับ Key Hits Rate นั้นจะต้องคิดเมื่อทำงาน MySQL ไปแล้วสักพักนะครับ อาจจะ 2-3 วัน
ภาคที่ 6 - Table Cache
สำหรับ Table Cache นั้นเป็นการเปิด Handle ของ Table ทิ้งเอาไว้ เพื่อการเข้าถึงข้อมูลใน Table ได้อย่างรวดเร็ว แต่ถ้าคุณเพิ่มค่านี้มากๆ คุณอาจจะเกิดปัญหาว่า File Descriptor ไม่พอ แนะนำให้เพิ่ม File Descriptor ได้จากการแก้ไขตัวแปร Kernel ที่ /proc/sys/fs/file-max โดยการใช้คำสั่ง
echo 392604 > /proc/sys/fs/file-max
table_cache ปกติแล้วถ้าในระบบที่มีตารางมากๆ table_cache ควรจะครอบคลุมตารางพื้นฐานทั้งหมด และอีกประมาณ 50% ของตารางที่เหลือ แต่ถ้าเป็นไปได้จะครอบคลุมทั้งหมดเลยก็ไม่ผิดแต่อย่างใด
table_cache=1024
วิธีการจะดูว่า set ไว้น้อยเกินไปหรือเปล่า ให้ดูที่
Open tables 1024
Opened tables 1120
โดย Open tables คือจำนวน Table ที่เปิดอยู่ขณะนี้ และ Opened tables คือจำนวน Table ที่เปิดมาทั้งหมด นับตั้งแต่เริ่ม MySQL Server มา โดยถ้าค่าของ Opened tables เพิ่มเร็วเกินไป แนะนำให้เพิ่มค่า table_cache
วิธีการปรับค่า table cache ทำได้โดย เพิ่มบรรทัดนี้ใน my.cnf
table_cache=1024
ภาคที่ 7 Query Cache
Query Cache นั้นเป็นคุณสมบัติใหม่ที่มีใน MySQL รุ่นที่ 4.x ขึ้นมา
Query Cache ทำงานง่ายๆ คือ ถ้ามี Query เหมือนเดิม MySQL จะเรียกจาก Cache แทนที่จะไป Query ใหม่ แต่ Query Cache ไม่ได้มีประโยชน์กับทุก Database Structure Query Cache เหมาะกับ Table ที่ไม่ค่อยได้ Update แต่มีจำนวน Records เป็นจำนวนมาก เช่น 50,000 records ขึ้นไป Query Cache จะใช้กับ Select เท่านั้น ถ้าระบบของคุณแตกต่างจากนี้การใช้ Query Cache อาจจะทำให้ได้ผลตรงกันข้ามก็ได้
วิธีการเปิดใช้งาน Query Cache ให้ใส่บรรทัดนี้ลงใน my.cnf ครับ
query_cache_type=1
query_cache_size=32M
query_cache_type จะมีได้ 3 ค่าคือ
0 - ปิด Query Cache
1 - เปิด Query Cache คุณสามารถสั่งให้ไม่ต้อง Cache ได้โดยการใช้ “SELECT SQL_NO_CACHE”
2 - แบบ On Demand คุณสามารถสั่งให้ MySQL Cache โดยการใช้ “SELECT SQL_CACHE”
ปกติแล้วถ้า Table มีการ Update แล้ว MySQL จะลบ Cache ของ Table นั้นๆ ทั้งหมดทันที และ Query Cache นั้นเป็น Case Sensitive ดังนั้น
SELECT * FROM a WHERE b=1
กับ
select * from a where b=1
จะไม่เหมือนกันนะ ถ้าเราเรียกตัวแรกแล้วเรียกตัวที่ 2 ตัวที่ 2 จะไม่ได้เรียกจาก Cache
ดังนั้นถ้า
1. ในระบบของคุณมีการเขียน SQL แบบไม่ได้วางแผนเรืองตัวใหญ่ตัวเล็ก คุณจะได้รับประโยชน์จาก Query Cache น้อยลง
2. ถ้า Table หลักๆ ของคุณมีการ Update ตลอดเวลา คุณจะได้รับประโยชน์จาก Query Cache น้อยลง3. ถ้า Table หลักๆ ของคุณไม่ได้มีจำนวน Records มากคุณก็แทบจะไม่ได้รับประโยชน์จาก Query Cache เลย