MyISAM เร็วกว่า InnoDB จริงหรือ? (Part 1)

[มนตรี บอยรักยุ้ยคนเดียว]

และแล้วในที่สุด Blog ของเราก็มีคนมาอ่านแร้ววว 1 คนแหนะ (-*-) นั่นก็คือ จิต เพื่อนที่มหาลัย ที่ตอนนี้ทำงานอยู่บริษัท Database ชั้นนำแห่งหนึ่ง และเนื่องจากบทความที่จะเขียนนี้เกี่ยวกับเรื่องของ Storage Engine ของ Database ทำให้ผมชักเกร็งๆแล้วว่าจะเขียนดีมั้ย ก็เลยกั๊กไว้อยู่นานไม่โพสซะที แต่ในที่สุดก็ทำใจได้แล้ว ถ้าผิดพลาดประการใดก็ช่วยแนะนำด้วยนะคร้าบบบ

ก่อนอื่นก็ต้องขอบอกก่อนเลยว่าบทความนี้ผมไม่ได้ทดสอบเอง แต่เป็นการแปลมาจากBlog ของต่างประเทศอีกทีหนึ่งคือ http://www.mysqlperformanceblog.com ซึ่งผู้เขียนเห็นว่าน่าสนใจทีเดียวจึงนำมาแปลและเรียบเรียงตามความเข้าใจของผู้เขียน หากผิดพลาดประการใดก็ขออภัยด้วยนะคับ

จริงๆแล้วจุดประสงค์หลักของการทดสอบนี้จะเป็นเรื่องของ Storage Engine 3 ตัวคือ Falcon, MyISAM, InnoDB ซึ่งตอนที่บทความต้นฉบับนี้เขียนขึ้นนั้น(ปี2007) Falcon พึ่งจะออกมาเป็น Version Alpha ดังนั้นจุดที่น่าสนใจของบทความผู้เขียนว่าน่าจะเป็นที่จุดประสงค์รองมากกว่า

โดยจุดประสงค์รองของการทดสอบครั้งนี้อยู่ที่ เรื่องราวเกี่ยวกับ MyISAM นั้นเร็วกว่า InnoDB ในเรื่องการอ่านจริงหรือ อย่างที่รู้กันว่า InnoDB ทำงานแบบ Transaction และ Support Foreign Key จึงมี overhead มากกว่า ทำให้มีการพูดกันว่า InnoDB ช้ากว่า MyISAM ความจริงทั้งหมดคุณจะได้เห็นจากการทดลองนี้

การทดสอบใช้ PHPTestSuite
สคริปและชุดคำสั่งที่ใช้ทดสอบอยู่ที่นี่

Table ที่ใช้ทดสอบเป็น Table แบบทั่วไปที่ปกติที่จะใช้กันบน OLTP หรือ Web App
Medium Size Rows, Auto Increment
Primary Key and Couple of Extra Indexes

ในการทดสอบครั้งนี้จะใชการ read(select) ด้วย pattern ในการอ่านหลายรูปแบบ
Primary Key Single Row Lookup, Primary Key Range Lookup, Same Access Types
for Primary Key and Full Table Scans.

เพื่อที่จะเน้นให้เห็นถึงคุณสมบัติที่แตกต่่างกันของ Storage Engine เราจะทดสอบด้วย Range ด้วยการใช้และไม่ใช้ LIMIT

การเปรียบเทียบประสิทธิภาพครั้งนี้จะเรียกว่าการเปรียบเทียบระสิทธิภาพแบบ Micro ที่คำนึงถึงฟังก์ชันที่สำคัญๆของ Storage Engine เป็นหลัก และจะใช้มันเพื่อดูประสิทธิภาพและ Scalability ในกรณีง่าย และการทดสอบนี้ได้มีการใช้ CPU bound workload ด้วย(การทำแบบไม่ใช้ I/O disk เลย) เพื่อที่จะดูประสิทธิภาพของ Storage Engine ในเรื่องของการใช้ CPU

ดูคำอธิบาย schema และ queries ที่นี่
CentOS release 4.4 (Final)
2 х Dual Core Intel XEON 5130 model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6 cpu
MHz : 1995.004
cache size : 4096 KB
16GB of RAM

MySQL version ใช้ MySQL 5.1.14-beta sources for MyISAM / InnoDB และ MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql-5.1-falcon สำหรับ Falcon (ในบทคามนี้จะใช้ falcon ที่เป็น version alpha อยู่ ซึ่งในปัจจุบันอาจมีการเปลี่ยนแปลงไปมากแล้ว)

ข้อมูลของการรันเป็นไปตาม link นี้
http://taevblog.blogspot.com/2009/05/myisam-innodb-part-1.html

[มนตรี บอยรักยุ้ยคนเดียว]

Query: SELECT name FROM $tableName WHERE id = %d
Comment: เป็นการรัน query ด้วยการ access ผ่าน primary key
InnoDB เร็วกว่า MyISAM 6-9%
Falcon แสดงถึง Scalability ที่แย่มาก

Query: SELECT name FROM $tableName WHERE country_id = %d
Comment: ในกรณีนี้ Falcon ทำได้ดีที่สุด เพราะ Falcon ใช้เทคนิคพิเศษในการดีง rows ออกมา
ในขณะที่ MyISAM แสดงถึง Scalability ที่แย่เมื่อจำนวน Thread เพื่มขึ้น คิดว่าน่าจะเนื่องมาจาก pread system call ที่ MyISAM ใช้ในการ Access Data และการดึงมาจาก OS ไม่สามารถถูก Scale ได้

Query: SELECT name FROM $tableName WHERE country_id = %d
Comment: ในกรณีนี้ Falcon ทำได้ดีที่สุด เพราะ Falcon ใช้เทคนิคพิเศษในการดีง rows ออกมา
ในขณะที่ MyISAM แสดงถึง Scalability ที่แย่เมื่อจำนวน Thread เพื่มขึ้น คิดว่าน่าจะเนื่องมาจาก pread system call ที่ MyISAM ใช้ในการ Access Data และการดึงมาจาก OS ไม่สามารถถูก Scale ได้

Query: SELECT state_id FROM $tableName WHERE country_id = %d
Comment: Query นี้คล้ายกับ Query READ_KEY_POINT แต่ต่างกันอยู่ที่ค่าของ column ที่ถูก access นั้นถูกจัดเก็บใน key
MyISAM และ InnoDB จัดการกับกรณีนี้โดยการ retrive มาเฉพาะ key
InnoDB ดีกว่า 25-30%

Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5
Comment: Query เหมือนอันก่อนหน้านี้แต่เพิ่ม LIMIT ลงไป
Limit กับ Falcon ผลลัพธ์ยังคงเหมือนเดิม
InnoDB ดีกว่า MyISAM ประมาณ 87% ในกรณีใช้ 4 thread แต่ตกลงมาอย่างมากเมื่อเพิ่มจำนวน thread

Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT 5
Comment: Query เหมือนอันก่อนหน้านี้แต่เพิ่ม LIMIT ลงไป
Limit กับ Falcon ผลลัพธ์ยังคงเหมือนเดิม
InnoDB ดีกว่า MyISAM ประมาณ 87% ในกรณีใช้ 4 thread แต่ตกลงมาอย่างมากเมื่อเพิ่มจำนวน thread

Query: SELECT min(dob) FROM $tableName WHERE id between %d and %d
Comment: เข้าถึงด้วย range ด้วยค่าของ primary key
MyISAM scale ได้แย่มาก และเหตุผลก็เป็นเหตุผลเดียวกับ READ_KEY_POINT
InnoDB ดีกว่า MyISAM 2-26 เท่าและมากกว่า Falcon 1.64 - 3.85 เท่า

Query: SELECT count(id) FROM $tableName WHERE id between %d and %d
Comment: MyISAM scale ได้ดีเนื่องจาก access แค่ column ที่เป็น key และ pread system call ไม่ได้ถูกใช้

Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d
Comment: ในทำ READ_KEY_RANGE Falcon ทำได้ดีที่สุด
Falcon ทำได้ดีกว่า InnoDB 10-30%
MyISAM ตกลงเมื่อ thread มีจำนวน 128-256

Query: SELECT name FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50
Comment: Falcon ตกไปอีกครั้ง เพราะไม่ support LIMIT

Query: SELECT min(dob) FROM $tableName
Comment: เป็น query ที่ถึกที่สุดก็ว่าได้ เพราะต้อง scan ทั้ง 1 ล้าน row
InnoDB ดีกว่า MyISAM ประมาณ 30% ถ้าใช้ 4-16 Thread แต่ MyISAM scale ได้ดีกว่าเล็กน้อยในกรณีนี้
InnoDB ดีกว่า Falcon 2-3 เท่า

และแล้วก็จบสำหรับบทความนี้ ผู้อ่านบางท่านอาจจะงงๆอยู่บ้าง(เพราะผู้เขียนก็ยังงงๆเหมือนกัน - -') ยังไงหาสงสัยสามารถเข้าไปดูต้นฉบับได้ที่นะคับ

http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

บทความนี้ก็ขอลาไปเท่านี้ก่อน แต่เรื่องราวของ MyISAM vs InnoDB ยังไม่จบ ไว้ต่อ Part 2 ในบทความหน้า ซึ่งเป็นบทความที่แปลมาอีกเช่นกัน เขียนขึ้นในปี 2008 เป็นการวิเคราะห์คุณสมบัติและความเหมาะสมของทั้งสองตัวนี้ อีกทั้งยังมีการอ้างอิงมาถึงบทความนี้อีกด้วย แล้วคอยติดตามกันคร้บบบ หุหุ

[yord]

สุดยอดจริงๆ เลยครับ
ขอบคุณมากครับ สำหรับความรู้ที่ดีมากครับ 

[panus_t]

สรุปคือ InnoDB  ดีที่สุดใช่ หรือไม่ครับ   ดูจากกราฟ เห็นสูงกว่าเพื่อน   

[howtodo]

ไม่น่าจะใช่ดีที่สุด
ต้องเลือกตามความเหมาะสม
innodb ดีสำหรับ transaction 

[มนตรี บอยรักยุ้ยคนเดียว]

สรุปคือ InnoDB  ดีที่สุดใช่ หรือไม่ครับ   ดูจากกราฟ เห็นสูงกว่าเพื่อน  

นะตอนนี้ถ้าใช้ ใน รพ.ใช้เป็น InnoDB ดีกว่าใช่ MyISAM ครับ
ซ่อมฐานข้อมูลได้
นี้คือสิ่งที่ดีกว่า MyISAM