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 อยู่ ซึ่งในปัจจุบันอาจมีการเปลี่ยนแปลงไปมากแล้ว)

Compilation parameters:

For MyISAM / InnoDB

./configure --prefix=/usr/local/mysqltest/mysql- --with-innodb

For Falcon

./configure --prefix=/usr/local/mysqltest/mysql- --with-falcon

MySQL Startup Parameter

Falcon:

libexec/mysqld --no-defaults --user=root --falcon_min_record_memory=1G --falcon_max_record_memory=2GB --falcon_page_cache_size=1500M --max-connections=1500 --table-cache=512 --net_read_timeout=30 --net_write_timeout=30 --backlog=128

MyISAM / InnoDB:

libexec/mysqld --no-defaults --user=root --key-buffer-size=1500M --innodb-buffer-pool-size=1500M --innodb-log-file-size=100M --innodb-thread-concurrency=8 --max-connections=1500 --table-cache=512 --net_read_timeout=30 --net_write_timeout=30 --back_log=128

ขั้นตอนการทำ Benchmark
1. เตรียม table ขนาด 1,000,000 record ขนาดประมาณ 350Mb
2. Run แต่ละ Query ด้วย Thread 1, 4, 16, 64, 128, 256
3. แต่ละ Thread จะทำการ warm-up run ใช้เวลาประมาณ 180 วินาที และรัน effective run 3 ครั้ง ดังนั้นผลลัพธ์สุดท้ายที่ได้จะได้ผลลัพธ์สูงสุดของการรันทั้ง 3ครั้ง

ข้อมูลของการรันเป็นไปตาม link นี้
http://www.mysqlperformanceblog.com/files/benchmarks/innodb-myisam-falcon.html


ผลลัพธ์ที่ได้เป็นไปตามภาพพร้อมการสรุปผล

READ_PK_POINT


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

READ_KEY_POINT


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

READ_KEY_POINT_LIMIT


Query: SELECT name FROM $tableName WHERE country_id = %d LIMIT 5
Comment: ใช้ Query เดียวกับอันก่อนหน้านี้ แต่เพิ่ม Limit ลงไป
เนื่องมาจากวิธีการ access key ของ falcon ในขณะที่บทความนี้ออกยังไม่รองรับการใช้ Limit ดังนั้นกราฟของ Falcon จึงเป็นอย่างที่เห็น แต่ในปัจจุบันอาจจะสามารถใช้ได้แล้วก็ได้
MyISAM แสดงถึงผลลัพธ์ที่เสถียร
InnoDB แสดงดีกว่า MyISAM 58% ในกรณีที่ใช้ 4 thread แต่ความสามารถในการ scale ยังไม่ดีพอ บางทีนี่อาจจะยังคงเป็นปัญหากับการทำ InnoDB mutexes

READ_KEY_POINT_NO_DATA


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%

READ_KEY_POINT_NO_DATA_LIMIT


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

READ_PK_POINT_INDEX



Query: SELECT id FROM $tableName WHERE id = %d
Comment: ง่ายแต่เร็วนการดึงค่ามาจาก PK
ผลของ InnoDB และ MyISAM พอจะเทียบกันได้ และคิดว่านี่เป็นการแสดงถึงว่าทั้ง 2 engine นั้นถูกทำให้ optimize อย่างถึงที่สุด ผลลัพธ์ที่ได้คือผลลัพธ์ที่ดีที่สุดที่สามารถเป็นไปได้
Falcon scale ได้ไม่ค่อยดี แต่มันยัง optimize ได้อีกเยอะ

READ_PK_RANGE


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 เท่า

READ_PK_RANGE_INDEX


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

READ_KEY_RANGE


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

READ_KEY_RANGE_LIMIT


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

READ_KEY_RANGE_NO_DATA


Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d

READ_KEY_RANGE_NO_DATA_LIMIT


Query: SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50

READ_FTS


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 เป็นการวิเคราะห์คุณสมบัติและความเหมาะสมของทั้งสองตัวนี้ อีกทั้งยังมีการอ้างอิงมาถึงบทความนี้อีกด้วย แล้วคอยติดตามกันคร้บบบ หุหุ