เป็น Slide PDF จากงาน OSDBCON 2006 เรื่อง Innodb Architecture and Performance (PDF)
เรื่องพวกนี้รวบรวมและอ้างอิงจาก http://www.mysqlperformanceblog.com
ผมตัดมาเฉพาะส่วนของการทำ Optimization เท่านั้นครับ เอาไว้ให้อ่านกันง่าย ๆ ผมจะพยายามอธิบายในแต่ละส่วนให้เข้าใจง่าย ๆ อีกทีนึง แต่ถ้า Slide อันไหน มันมีความอยู่ในตัวเองพออยู่แล้วจะไม่อธิบายเพิ่มเติมครับ
Do not use defaults
- Default settings are for toy databases
- 8MB buffer pool, 10MB logs size
- Not enough for any serious load
- Innodb is affected by buffer sizes much more than MyISAM
- Advanced caching
- Synchronous IO
Sizing Buffers
- innodb_buffer_pool_size
- Typical value 60-80% of memor (If Innodb is only your storage engine)
- key_buffer_size
- May be still needed for temporary tables
- Some 32MB is enough
- log_buffer_size
- 4-8MB is enough for most cases
ในส่วนของ Slide ทั้ง 2 หน้านี้จะพูดถึงเรื่องของ defaults config ของ MySQL ที่เมื่อลงแล้วจะได้มา โดย buffer pool เนี่ย มันไม่พอต่อความต้องการแน่ ๆ โดยตัว buffer pool เหมือนถังน้ำบนดาดฟ้าตึกที่ให้การไหลน้ำของอาคารนั้นไหลอย่างสม่ำเสมอ และเพียงพอ แต่ถ้า buffer pool เล็กเกินไป จะทำให้ข้อมูลส่งและรับไม่สมดุลกัน เวลามีการ query ข้อมูลใหญ่ ๆ ออกมาตัว client จะรับไม่ทัน (หลาย 10MB หรือ หลาย GB) รวมไปถึงในกรณีที่ส่งข้อมูลออกไปไม่ทัน เมื่อมีการโหลดข้อมูลหลาย ๆ connection และหลาย ๆ transaction ในกรณีนี้ต้องปรับให้สมดุลกัน
Sizing Log Files
- Larger log files – better performance
- Reduces amount of flushes needed
- Increases recovery time
- Use log files which give you recovery time you need
- Combined size about 1GB is reasonable value
- Resizing is a bit complicated
- Removing old log files so new ones are created
ขนาดของ log file ไม่ควรใหญ่มาก ลบมันออกไปซะบ้าง เพราะมันจะมีผลเวลาเราต้อง recovery ตัวฐานข้อมูล log เก่า ๆ ที่ไม่จำเป็น (จริง ๆ) ก็ลบมันซะครับ
Optimizing IO
- Avoid Double Buffering
- Same data cached in OS cache and buffer pool
- Waste of memory
- Innodb cache is much more efficient
- Use unbuffered IO
- Linux
- innodb_flush_method=O_DIRECT
- May slow things down write performance
Optimizing Commits
- ACID Commits require flush to the disk
- Expensive, limited to 100-250/sec uncached
- RAID with battery backup cache can improve dramatically (1000/sec+)
- Group commit broken in MySQL 5.0
- Unless you disable binary log and sacrifice recovery from backup
- innodb_flush_log_at_trx_commit=2
Other Variables
- innodb_additional_mem_pool_size
- Used for data dictionary and other data
- Automatically increased as needed
- Do not set too high, avoid memory waste
- innodb_thread_concurrency
- Limit number of Threads running in kernel
- 2*(NumCPUs+NumDisks) – in theory
- Optimal may be much smaller in practice
กำหนด innodb_additional_mem_pool_size ให้พอดี ไม่ต้องเยอะมาก เพราะมันเป็นแค่ที่เก็บ data dictionary เท่านั้น ส่วนของ innodb_thread_concurrency ไว้กำหนดจำนวน Thread สำหรับทำงานกับ Innodb ให้ไม่เกินกว่าที่ควรจะเป็น มีสูตรคำนวณคือ 2 * (NumCPUs+NumDisks)
innodb_file_per_table
- Use its own tablespace for each table
- System tablespace is still used for undo segments and metadata
- Easier to backup, reclaim space
- Performance effect varies
- Problems with very large number of tables
- Less tested than default configuration
กำหนด innodb_file_per_table ซะ เพราะมันจะช่วยแบ่ง tablespace ที่เป็นไฟล์ ibdata1 ใหญ่ ๆ เป็นก้อนเดียว ยิ่งฐานข้อมูลใหญ่เท่าไรไฟล์ tablespace ก็ใหญ่เท่านั้น คราวนี้มันจะตัดแบ่งไฟล์ออกมาเป็นแบบเดียวกับ MyISAM ที่จะมีไฟล์ *.MYD เป็นไฟล์ข้อมูล ถ้าเป็น innodb แบบ innodb_file_per_table จะเป็นไฟล์ *.ibd แทน โดยเหมาะกับ File-System บางแบบ ที่ไม่สามารถรองรับไฟล์ใหญ่ ๆ ได้ (เช่น FAT32 ที่รองรับไฟล์ 1 ไฟล์ได้ไม่เกิน 4GB) และช่วยให้การ Backup ง่ายขึ้นด้วย แต่ก็แลกกับจำนวนไฟล์ที่เยอะแบบบ้าเลือดแบบเดียวกับ MyISAM แต่สำหรับผมแล้วคุ้มมากกว่า เพราะถ้าไฟล์ table space ไฟล์หลัก ที่มีไฟล์ไฟล์เดียวเสีย ข้อมูลทั้งหมดหายเกลี้ยง ๆ แต่การทำ per_table กลับช่วยแก้ปัญหาตรงนี้ลงไปได้เยอะ แถมลดเวลาการ recovery และ repair ไฟล์เวลาเกิดปัญหาด้วยครับ
ตอนต่อไปเราจะมาต่อกัน ยังมีอีกเยอะเลย ครับผม ;)