MySQL with Innodb Performance Optimization (2 and never ending in optimization)

มาต่อตอนที่ 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 ไปแล้วก็ได้ จึงเป็นเรื่องที่ต้องทำอยู่สม่ำเสมอครับ