MySQL

มาต่อตอนที่ 2 กันครับผม จากตอนที่แล้ว MySQL with Innodb Performance Optimization (1) ตอนนี้ส่วนใหญ่ตัวภาษาอังกฤษค่อนข้างตรงตัวอยู่แล้ว เลยเสริม ๆ ส่วนที่ผมมีประสบการณ์ลงไปบ้างนิดหน่อยครับผม

Speed up Shutdown

• Innodb may take very long to shutdown
  • Flushing dirty buffers from buffer pool
• Increase downtime for upgrades etc
• innodb_max_dirty_pages_pct
  • Maximum percent of dirty pages
• SET GLOBAL innodb_max_dirty_pct=0
  • Wait as dirty pages get close to 0, and shut it down

ตัว innodb นั้นใช้เวลาในการ shutdown ตัวเองนานเพราะต้องมีการเคลียร์พวก memory/connection/buffer_pool ต่าง ๆ ก่อนเสมอ เพื่อป้องกันข้อมูลสูญหายจากการทำ transaction บางอย่างที่ยังไม่เสร็จ หรือยกเลิกทั้ง transaction group ไปเลยก่อน วิธีที่ทำให้ innodb นั้น shutdown ได้เร็วขึ้นก็ใช้การ innodb_max_dirty_pages_pct แล้วตั้งให้เป็น 0 เพื่อไม่ให้มีการเขียน page ใหม่  ๆ ลงใน buffer_pool อีก

SHOW INNODB STATUS

• The instrument for understanding what is going on inside InnoDB
• Partially exported as SHOW STATUS variables in MySQL 5.0
• Shows statistics about latches, locks, IO, logging activity, row level activity, thread queue activity etc.

ใช้ SHOW INNODB STATUS เพื่อดูสถิติของระบบ innodb ว่านำมาปรับแต่งอยู่เสมอ ๆ

InnoDB and Hardware

• RAID With battery backed up cache may be important.
• NAS known to cause the problems
• May have problems scaling with many CPUs
  • Fix on a way
  • Faster CPUs, multiple low end boxes
  • Disabling HyperThreading may be good

ใช้ RAID ที่มี battery backed up cache ถ้าเป็นไปได้ (เพื่อป้องกันข้อมูลสูญหายในกรณีที่ใช้ RAID) และไม่ควรใช้ NAS ด้วยประการทั้งปวง เพราะถ้า swtich ระหว่าง server กับ storage เดี้ยงทุกอย่างจบ พังยับ ล้มกระจายครับงานนี้ และปิด HyperThreading ซะ แต่จริง ๆ แล้วการทดสอบล่าสุด (Results of Performance Measurements on MySQL 5.0 Using DBT-1: Intel Xeon Dual-Core Version Consideration) พบว่าจะปิดหรือเปิด performance แตกต่างกันแค่ 1% เท่านั้น เพราะปัญหาเรื่อง performance-drop นั้นถูกแก้ไขแล้ว (แนะนำให้อ่านลิส์ดังกล่าวร่วมกับคำแนะนำนี้ครับ) เพื่อใช้ในการปรับแต่งสำหรับเครื่อง Multi-Core/Multi CPU ครับ

Innodb Aware Schema

• Use short PRIMARY KEY
  • Long PK make all secondary index larger
• Have Primary key
  • InnoDB will use internal key anyway
  • And it will be 6 bytes in length
• Have sequential PRIMARY KEY
  • Non sequential inserts cause fragmentation

Handling Long PRIMARY KEY

• If you have long primary key you can promote it to UNIQUE KEY
  • Add auto_increment pseudo_id column and make it primary key
  • Change real primary key to UNIQUE KEY
• Note: Lookups are slower by secondary key

Power of PRIMARY KEY

• PRIMARY KEY is special key in InnoDB
• PRIMARY KEY lookups are much more efficient
  • Both in memory and IO bound
• PRIMARY KEY range scans have same speed as full table scans.
• Joins on PRIMARY KEYs are more efficient
  • Account in schema design

การออกแบบ Schema (Database Structure) นั้นใช้ข้อมูลที่เป็น PRIMARY KEY ให้สั้นและกระชับ เช่นพวก int ไม่แนะนำให้ใช้ข้อมูลจำพวก String ใด ๆ เพราะทำให้ตัว PRIMARY KEY นั้นใหญ่และการค้นหานานกว่าเดิม และอย่างน้อย ๆ ทุก table ต้องมี PRIMARY KEY อยู่ 1 field เพื่อใช้ในการค้นหา แบบ WHERE/ORDER Cause (เป็นเรื่องปกติในวิชาที่ว่าด้วยเรื่อง Database ของ Computer Science อยู่แล้ว) ใครไม่ทราบว่าทำไมต้อง INDEX ให้ลองคิดถึงการค้นหาหนังสือในห้องสมุดที่ถ้าไม่มี PRIMARY KEY พวก OPAC หรืออย่างเก่า ๆ หน่อยก็ตู้สารบัญหนังสือ (ที่เป็นบัตรเล็ก ๆ เรียงตามอักษรชื่อเรียง) การค้นหาคุณต้องไปนั่งไล่หาตามตู้หนังสือที่ละชั้น หรืออย่างดีที่สุดคือไล่ตามหมวด ซึ่งช้ามาก ๆ ยิ่งห้องสมุดใหญ่เท่าใด ยิ่งหายากเท่านั้น จริง ๆ แล้วตามหลักแล้ว PRIMARY KEY มันก็คือ INDEX รูปแบบหนึ่งนั้นแหละครับ โดยเวลาทำการ Join Tables กันควรใช้พวก INDEX มาทำการ Join กันเสมอ ๆ

No Key Compression

• InnoDB does not have key compression
  • As MyISAM does
• InnoDB Indexes can be 10 times larger than MyISAM indexes
  • One of the reasons InnoDB tables generally take 2-3 times more space
• Be easy on indexes
• May be fixed by gzip page compression

ตัว innodb ไม่มี key compression เหมือนกับ MyISAM (ซึ่งเป็นต้นเหตุให้บางครั้งมันช้า) ทำให้มันเสียเวลา scan ตัว index นานกว่า MyISAM ถึง 10 เท่า (แต่ปกติจะอยู่ที่ 2-3 เท่า)

Power of clustering

• Get benefit of clustering by primary key
• Messages Table
  • Primary key(user_id,message_id)
  • Very fast to get all messages for given user
  • Would be even better with multi column auto_increment key support
• General rule: data which you need together to have close PK values

Table Fragmentation

• InnoDB tables fragment over time
• Rows are not fragmented but pages can be scattered
  • Less of the problem because of large pages
• OPTIMIZE TABLE to rebuild the table
  • Slow (no index rebuilt by sort)
  • Blocks whole table during operation
  • Master-Master replication may help

High Performance Backup

• Use physical level backup
  • Logical level backup is very slow to recover
  • But do NOT copy files with database running
• Innodb Hot Level Backup
  • Commercial solution
• LVM/Snapshot based backup
  • About same performance but free
  • Requires specific OS Setup

Blob handling

• InnoDB can skip reading blobs if they are not in select column list
  • Makes sense to keep blobs in the same table
• Blobs stored each at separate page(s) if it does not fit to the page
  • Consuming at least 1 page
• Blobs are allocated in new space on update.

Avoid count(*) without where

• SELECT COUNT(*) FROM TBL
  • Instant for MyISAM, Memory etc
  • Slow for InnoDB
    • Performs table/index scan
• Try to avoid
  • SHOW TABLE STATUS LIKE ‘table’
    • Approximate number of rows
  • Counter table for exact number of rows

Innodb Row count

• Count of rows is inaccurate
  • And guessed for each query execution
    • Using random BTREE dives
  • Can cause fluctuating plans
  • May be problem hard to catch.
• OPTIMIZE TABLE may help row count estimation accuracy

Innodb Statistics

• Cardinality values computed using BTREE dives as well
  • Can also be inaccutrate
• Computed first time table is opened after start
  • Make first table open rather slow
• ANALYZE TABLE forces refresh
  • Using same estimation method

ถ้าจะนับข้อมูลให้ใช้การ SHOW TABLE STATUS LIKE ‘tablename’ แทนการใช้ SELECT COUNT(*) FROM ‘tablename’ ถึงแม้บน MyISAM จะเร็ว แต่บน innodb กลับช้า จริง ๆ ส่วนนี้สามารถแก้ไขด้วยการใช้ query-cache แต่ถ้ามีการ update index ใหม่เข้าไปก็ต้องนับใหม่อีกทีซึ่งก็ยังช้าอยู่ดีครับ โดยการใช้ SHOW TABLE STATUS LIKE ‘tablename’ นั้นเป็นการดึงข้อมูลตัว counter มาจาก information_schema นั้นเอง และจริง ๆ แล้วถ้าต้องการรายละเอียดข้อมูลของ schema เพื่อเอาไปใช้งานด้าน ORM (Object-Relational mapping) ให้ใช้ข้อมูลใน information_schema เข้ามาช่วยได้เช่นกัน (จริง ๆ การมีตาราง information_schema นั้นเป็นไปตามข้อกำหนดของ ANSI/ISO SQL:2003 standard ใน Part 11 Schemata) ใน information_schema นั้นจะบอกทั้งโครงสร้างของแต่ละตารางและความสัมพันธ์ต่าง ๆ ทั้งหมด ช่วยให้ทำพวก tools หรือ program พวก automatic-generate SQL command ได้ดีมาก ๆ

สำหรับเรื่อง Optimization นี่ไม่มีที่สิ้นสุด และไม่มีอะไรที่ถูกที่สุดครับ การทำต้องทดสอบเสมอ และควรทดสอบและตรวจสอบว่าสิ่งที่เรา optimize ลงไปนั้นยังใช้งานได้ดีอยู่หรือไม่ และควรปรับแต่งอยู่เสมอ และในทางกลับกัน เมื่อระบบออก major/minor-release ใหม่ ๆ ออกมาควรทำการศึกษา release-note ทุกครั้งก่อนการ upgrade เสมอ เพราะอาจจะกระทบต่อ performance และสิ่งที่เรา optimize ลงไปอาจจะใช้ไม่ได้ผลในตอนที่เรา upgrade ไปแล้วก็ได้ จึงเป็นเรื่องที่ต้องทำอยู่สม่ำเสมอครับ

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

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