MySQL with Innodb Performance Optimization (1)

เป็น 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 ไฟล์เวลาเกิดปัญหาด้วยครับ

ตอนต่อไปเราจะมาต่อกัน ยังมีอีกเยอะเลย ครับผม ;)

2 thoughts on “MySQL with Innodb Performance Optimization (1)

  1. ก็ยังไม่รู้ อยู่ดีว่า จะ เซตค่ายังไง ให้มันเร็วที่สุด
    มึนๆๆๆ

Leave a Reply