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

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

ติดตั้ง SVN Server บน Windows ภายใน 10 นาที

เป็น entry ที่อยู่ใน Draft นานเป็นอันดับต้น ๆ (เกิน 1 ปีแน่  ๆ) แต่จนแล้วจนรอด มันรกหูรกตาเลยเอามาปัดฝุ่นเขียนเพิ่มเติม และเอาออกมาสักทีนึง

ใครไม่รู้ว่า SVN คืออะไรก็ตามไปอ่าน entry เก่าได้ที่นี่

สิ่งที่ต้องเตรียมก่อนในการติดตั้ง (เวลาดาวน์โหลดไม่นับรวมเวลาในการติดตั้ง) คือ SVN Server ซึ่งเป็นตัวจัดการไฟล์ที่เรานำมาใส่ใน Store ของมัน และ SVN Service เป็นโปรแกรมเล็ก ๆ ที่นำ SVN Server เข้าไปอยู่ใน Services list ของ Windows เพื่อให้ตัว SVN Server ทำงานในระดับ Background Application และสามารถสั่งให้ SVN Server ทำการเริ่มการทำงานทั้งแต่เปิดเครื่องผ่าน Services ของ Windows เองได้อย่างง่าย ๆ

SVN Service

Summary : Runs svnserve as a Windows service (requires .NET Framework 1.1)

Category : libraries

License : GNU General Public License

Owner(s) : ricos, sclerose

การติดตั้งทั้งหมดอ้างอิงบน Windows XP Professional

  1. ดาวน์โหลด SVN Server ได้จาก http://subversion.tigris.org/ และดาวน์โหลดตัว SVN Service จาก http://svnservice.tigris.org/ 
  2. ติดตั้ง SVN ลงใน c:\svn\ หรือ directory ใด ๆ ที่ต้องการก็ได้ (ใน entry นี้อ้างอิง c:\svn\ ครับ)
  3. ทดสอบว่ามันจะทำงานได้หรือเปล่าก็เปิด Commnand Prompt(พิมพ์ cmd ที่ Start > Run)
  4. ไปที่ c:\svn\bin (พิมพ์ cd c:\svn\bin)
  5. พิมพ์ svnadmin เพื่อทดสอบดู
    ถ้าขึ้นว่า “svnadmin.exe – Unable To Locate DLL”
    ให้ติดตั้ง Visual C++ 6.0 VCREDIST.exe โดยดาวน์โหลดได้ที่นี่ ถ้าสั่งให้ทำงานแล้วไม่มีปัญหา ก็ทำการทดสอบส่วนของ svnserve.exe เพื่อทดสอบว่าสามารถสั่งให้ SVN Server ทำงานได้หรือไม่
  6. พิมพ์ svnserve -r d:\svndatarootdir
    โดยที่ svndatarootdir คือ directory ที่ใช้สำหรับเก็บ repository ทั้งหมดของ svn ของเรา
  7. ถ้าใช้คำสั่งในข้อ 6 แล้วไม่มีปัญหา ให้ปิด Commnand Prompt แล้วติดตั้ง SVN Service
  8. ทำการเรียก SVNService Administration

    2007-12-08_052919

  9. แล้วทำการตั้งค่าดังนี้

    2007-12-08_051949 
    – ช่อง SVN Binary Path ให้ชี้ไปที่ c:\svn\bin ที่เก็บตัว binary file ของ svnserve.exe
    – ช่อง Repository Path ให้ชี้ไปที่ svndatarootdir หรือที่เก็บ directory ของ repository ทั้งหมด (ในตัวอย่างเป็น d:\svn)
    – Listen Host อันนี้กำหนด IP หรือ Hostname สำหรับใช้ติดต่อเข้ามา ซึ่งถ้าใช้คนเดียวก็กำหนดเป็น localhost ไปครับ
    – Listen Port กำหนด Port ที่เข้าใช้ SVN Server แนะนำให้ใช้ Port 3690 ไปเลยครับผม เพราะเป็น Port มาตรฐานของ SVN Server อยู่แล้ว
    – กด Save และ Close ปิดไปครับ

  10. เข้าไปทั้งค่า Service เพิ่มเติมที่ Service ของ Windows ได้ที่ Administrative Tools (ถ้าหาใน Start ไม่เจอให้ไปที่ Control Panel และไปที่ Administrative Tools ก็ได้)

    2007-12-08_053608

  11. ไปหา Name ที่ชื่อว่า SVNService แล้วดับเบิ้ลคลิ้กเรียก SVNService Properties ขึ้นมา

    2007-12-08_053739

  12. แล้วตั้งค่าที่ Startup type เป็น Automatic เพื่อให้เมื่อเปิดเครื่องมาให้ SVNService ทำงาน แล้วตัว SVNService จะไปเรียก svnserve พร้อมตั้งค่าตามที่เราตั้งไว้ใน SVNService Administration มาให้ แล้วกดปุ่ม Start เพื่อให้ SVNService ทำงาน

    2007-12-08_053928

  13. ถ้าทุกอย่างราบรื่น ตอนนี้เราก็จะได้ SVN Server ขึ้นมาทำงานแล้ว

ต่อไปเป็นส่วนของการทดสอบ และตั้งค่า Repository และ SVN Server ที่เราติดตั้งว่าทำงานปกติหรือไม่

  1. ให้ดาวน์โหลด TortoiseSVN (A Subversion client, implemented as a windows shell extension.) มาติดตั้ง
  2. สร้าง directory ใหม่ใน d:\svn หรือที่เก็บ repository ทั้งหมด

    2007-12-08_054836 

  3. ทำการสร้าง Repository ใหม่

    2007-12-08_054927

  4. แล้วเลือก Native filesystem

    2007-12-08_055011

    2007-12-08_055035

  5. เราจะได้โครงสร้าง Directory ทั้งหมดภายใน d:\svn\test ดังภาพนี้

    2007-12-08_055133

  6. ทดสอบว่า test repository ของเราทำงานได้หรือไม่ก่อน
  7. คลิ้กขวาที่ Desktop แล้วเลือก TortoiseSVN แล้วเลือก Repo-browser

    2007-12-08_054536 

  8. แล้วพิมพ์ URL ดังรูปนี้

    2007-12-08_055623
     

  9. แล้วเราจะได้หน้าต่างของข้อมูลภายใน test repository แบบด้านล่าง (ว่าง ๆ เพราะไม่มีอะไรอยู่ภายใน) ถ้าได้ดังรูปด้านล่างนี้และ expanded ตัว list ออกมาได้โดยไม่มีการแจ้ง error ใด ๆ ถือว่า SVN Server ทำงานได้สมบูรณ์แล้ว

    2007-12-08_055641

สำหรับตอนต่อไป (คราวนี้สัญญาว่าจะลงให้เร็วที่สุดแน่ ๆ) จะเป็นการกำหนดค่า ต่าง ๆ ในการเข้าใช้งานเช่น username/password สำหรับเข้าถึงข้อมูล รวมไปถึงระบบสิทธิ์การเข้าถึงแต่ละ user ที่เข้ามาใช้ด้วยครับ

วันนี้ขอไปนอนก่อนหล่ะครับ ;)

[Update 11/12/2007]

อ้างอิงจาก

ใช้ Gmail ผ่าน IMAP นรกของเน็ต Inter. ช้า

หลังจากที่ Gmail เปิดให้ใช้งาน IMAP ผมก็ทำการ switch มาใช้ IMAP แทน POP3 ทันที ด้วยข้อดีที่มีอยู่เยอะ ของมัน (หาอ่านเอาเอง หรือจะค้นเอาจากพี่กู(เกิ้ล) ก็ได้ครับ)

แต่จนแล้วจนรอดก็ต้องกลับมาตายรังใช้ POP3 เหมือนเดิม เพราะว่า IMAP จะใช้งานลำบากมาก ถ้า Internet connection นั้นช้า โดยเฉพาะถ้าเป็นการต่อกับ internet ที่เป็นสายเชื่อมต่อผ่าน inter-connection แล้วนี่ยิ่งแล้วใหญ่ ทำให้ถ้าใช้ IMAP ที่มีการ sync-realtime นี่จบข่าวกันไป กว่าจะคลิ้กแต่และ folder ใน outlook แล้วโชว์อีเมลทีนั่งรอไปสักพัก แถมบางครั้งยังไม่โชว์ว่ามีอีเมลใหม่เข้ามา กลายเป็นว่าทำงานช้าไปในทันที เลยต้องกลับมาใช้ POP3 เหมือนเดิม เพราะมันโหลดแบบ copy-leave มา ไม่ใช่ sync-realtime ถ้าความเร็วไม่ถึงขั้น นี่ช้ามาก ๆ ถึงจะสะดวกกว่าก็ตามที

ซัดอีกแล้ว "สายลับจับบ้านเล็ก Limited Edition" NO#1564

รอบที่แล้ว Seasons Change NO#4682 มารอบก็นี้สายลับจับบ้านเล็ก Limited Edition NO#1564 ครับผม

P1040875

ต้องเอาออกจากกล่องก่อนครับ จะเป็นกล่องอีกชั้นคล้าย ๆ กับกล่องข้าว

P1040860

ด้านในก็จะมีนามบัตรที่แนบมากับกล่องใส่แผ่นครับ

Read more

เราใช้คุกกี้เพื่อพัฒนาประสิทธิภาพ และประสบการณ์ที่ดีในการใช้เว็บไซต์ของคุณ คุณสามารถศึกษารายละเอียดได้ที่ นโยบายความเป็นส่วนตัว

Privacy Preferences

คุณสามารถเลือกการตั้งค่าคุกกี้โดยเปิด/ปิด คุกกี้ในแต่ละประเภทได้ตามความต้องการ ยกเว้น คุกกี้ที่จำเป็น

Allow All
Manage Consent Preferences
  • Always Active

Save