1 / 34

XtraDB OpenSource Storage Engine for MySQL

Learn about XtraDB, an improved version of InnoDB focusing on performance and usability enhancements. Discover its history, scalability, and IO improvements for optimal MySQL performance.

lkilroy
Download Presentation

XtraDB OpenSource Storage Engine for MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. XtraDB OpenSource Storage Engine for MySQL OSCON 2009 Vadim Tkachenko, Ryan Lowe Percona Inc MySQLPerformanceBlog.com

  2. Who are we ? • http://MysqlPerformanceBlog.com • Vadim Tkachenko • Co-Founder of Percona Inc • Lead of R&D department • Co-Author “High Performance MySQL” 2nd edition book • Ryan Lowe • Senior Consultant, Percona Inc • Coordinator, USA Consulting Group • Special Thanks Yasufumi Kinoshita • Not here, but author of most work XTraDB OpenSource Storage Engine for MySQL

  3. What is this talk about? • XtraDB storage engine for MySQL • Enhanced version of InnoDB • Two main focuses • Performance improvements • “Usability” improvements • Make InnoDB a bit more friendly  • World changed since time of Pentium 100MHz and 8MB of RAM • But many such assumptions still in InnoDB code XTraDB OpenSource Storage Engine for MySQL

  4. MySQL Architecture in short XtraDB – drop in replacement for InnoDB XTraDB OpenSource Storage Engine for MySQL

  5. Why we do it • Gather all patches we have into single engine • Most requirements and changes come from practical work with customers • We need InnoDB fully utilizing modern hardware today • 16 cores • RAIDs • SSD / FusionIO / other storage technologies • InnoDB is not able to do that today, developed ~10 year ago XTraDB OpenSource Storage Engine for MySQL

  6. History • Started with patches for MySQL 5.0 • Still support • https://launchpad.net/percona-patches • Percona builds for MySQL 5.0 • http://www.percona.com/mysql/ XTraDB OpenSource Storage Engine for MySQL

  7. XtraDB, what is it • Enhanced InnoDB • Better hardware utilization • Better usability • Open for third-party patches • http://www.percona.com/docs/wiki/percona-xtradb:start • https://launchpad.net/percona-xtradb • Found in twitter: “We moved a database-set of 40 servers to mysql-5.1.34 + xtradb and are running our former workload with 6 servers. ramonvanalteren“ XTraDB OpenSource Storage Engine for MySQL

  8. XtraDB base • MySQL 5.1 • “easy” to remove and plug new engine • InnoDB plugin • New features • Focus of development of InnoDB team • Designed to plug into 5.1 XTraDB OpenSource Storage Engine for MySQL

  9. Performance Improvements XTraDB OpenSource Storage Engine for MySQL

  10. Scalability • Split buffer_pool mutex • Replace global lock on innodb_buffer_pool on several small parts • Decrease contention on single lock on multi-cores systems • Extra rollback segments • InnoDB uses just 1, again contention on multi-concurrency load • Extra undo slots • By default max 1024 concurrent transactions, we increase to 4076 XTraDB OpenSource Storage Engine for MySQL

  11. IO improvements • InnoDB IO • Set of patches to improve InnoDB IO performance • Several parts – some of them now in MySQL 5.4 XTraDB OpenSource Storage Engine for MySQL

  12. IO – multiple threads • Read_io_threads • Number of threads for reads requests (by default 1) • Not really useful as used only for read-ahead requests • Write_io_threads • Number of threads for write requests (by default 1) • This is one you may want to use on system with multiple disks • IO_capacity • Amount of IO operations per second InnoDB assumes server can do (by default 100, which is not right assumptions for modern systems) XTraDB OpenSource Storage Engine for MySQL

  13. IO – Adaptive checkpoint • InnoDB flushing of dirty buffer_pool pages may be intensive • Lack of free pages may be controlled by innodb_max_dirty_pages_pct • Flushing at the moment of checkpoint is not controllable, intensive and may hurt XTraDB OpenSource Storage Engine for MySQL

  14. Adaptive checkpointing InnoDB default behavior, hiccups during buffer_pool flushing XTraDB OpenSource Storage Engine for MySQL

  15. Adaptive checkpoint • What we do: • Flush pages more intensive • the closer checkpoint the more intensive XTraDB OpenSource Storage Engine for MySQL

  16. Adaptive_checkpoint • Adaptive_checkpoint=1 XTraDB OpenSource Storage Engine for MySQL

  17. IO Control of Insert buffer • Ibuf_max_size – maximal size of insert buffer (by default can be half of buffer_pool) • Ibuf_accel_rate – IO rate for background thread, works in pair with io_capacity XTraDB OpenSource Storage Engine for MySQL

  18. IO – multiple pages • Read_ahead = (both | linear | random) • Control to use or not internal InnoDB read-ahead logic XTraDB OpenSource Storage Engine for MySQL

  19. IO – multiple pages • Flush_neighbor_pages = (yes|no) • By default InnoDB also writes neighborhoods of flushing pages • All these operations were made for disks with expensive (in time sense) random reads – may be not needed for SSD / FusionIO / other devices with cheap random reads XTraDB OpenSource Storage Engine for MySQL

  20. Extra rollback segments • By default InnoDB uses single segment protected by mutex • Sensitive in intensive parallel insert load XTraDB OpenSource Storage Engine for MySQL

  21. Fix group commit • “Broken” in 5.0 • Problem appears on slow disks with enabled binary-logs XTraDB OpenSource Storage Engine for MySQL

  22. Benchmark • Tpcc-like workload • 100 Warehouses (about 10GB of data) • Buffer_pool=16GB (in memory) | 3GB (io-bound) • System: Dell PowerEdge R900, RAID 10 on 8 disks, RAM 32GB • O_DIRECT for InnoDB, ext filesystem • 5.1-XtraDB vs 5.1-standard XTraDB OpenSource Storage Engine for MySQL

  23. Benchmark XTraDB OpenSource Storage Engine for MySQL

  24. Usability patches XTraDB OpenSource Storage Engine for MySQL

  25. Limit data dictionary • Problem: • Data dictionary entry of once opened table kept in memory forever (or while DELETE table) • Is not problem for regular usage (100-1000 tables) • Is problem for instances with 10K+ tables • 10GB+ of memory just allocated for data dictionary entries • Our solution: • LRU based data dictionary entries • Remove from memory oldest entries if limit reached XTraDB OpenSource Storage Engine for MySQL

  26. Show buffer pool content • What is in buffer_pool select space,offset, RECORDS, DATASIZE, INDEX_NAME,TABLE_SCHEMA,TABLE_NAME from information_schema.INNODB_BUFFER_POOL_CONTENT limit 10; +-------+---------+---------+----------+------------+--------------+-------------+ | space | offset | RECORDS | DATASIZE | INDEX_NAME | TABLE_SCHEMA | TABLE_NAME | +-------+---------+---------+----------+------------+--------------+-------------+ | 1584 | 640643 | 9 | 10312 | PRIMARY | art104 | article104 | | 1648 | 2100 | 135 | 15226 | PRIMARY | art114 | author114 | | 1492 | 4507 | 158 | 15130 | PRIMARY | art87 | author87 | | 1406 | 17498 | 141 | 16056 | img_status | art52 | img_out52 | | 1466 | 47632 | 49 | 15140 | PRIMARY | art62 | img_out62 | | 1470 | 1395457 | 24 | 14769 | PRIMARY | art84 | article84 | | 1460 | 16025 | 62 | 15174 | PRIMARY | art61 | img_out61 | | 1458 | 560956 | 20 | 14977 | PRIMARY | art61 | article61 | | 1466 | 67953 | 56 | 15182 | PRIMARY | art62 | img_out62 | | 1621 | 162962 | 46 | 15134 | PRIMARY | art110 | link_out110 | +-------+---------+---------+----------+------------+--------------+-------------+ XTraDB OpenSource Storage Engine for MySQL

  27. Show memory usage • Extended information about memory consuming ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 328830416; in additional pool allocated 2117120 + Internal hash tables (constant factor + variable factor) + Adaptive hash index 4839388 (4425628 + 413760) + Page hash 138716 + Dictionary cache 3383508 (3320220 + 63288) + File system 41848 (41336 + 512) + Lock system 332788 (332468 + 320) + Recovery system 0 (0 + 0) + Threads 41900 (41348 + 552) Buffer pool size 16384 + Buffer pool size, bytes 268435456 Free buffers 12396 XTraDB OpenSource Storage Engine for MySQL

  28. Show locks held • ---TRANSACTION 0 163390, ACTIVE 0 sec, process no 15571, OS thread id 1159485776 inserting • mysql tables in use 1, locked 1 • 7 lock struct(s), heap size 1216, undo log entries 4 • MySQL thread id 15, query id 15455 127.0.0.1 root update • INSERT INTO history(h_c_d_id, h_c_w_id, h_c_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES(?, ?, ?, ?, ?, ?, • ?, ?) • Trx read view will not see trx with id >= 0 163391, sees < 0 163086 • TABLE LOCK table `test/warehouse` trx id 0 163390 lock mode IX • RECORD LOCKS space id 10 page no 3 n bits 168 index `PRIMARY` of table `test/warehouse` trx id 0 163390 lock_mode X • locks rec but not gap • TABLE LOCK table `test/district` trx id 0 163390 lock mode IX • RECORD LOCKS space id 18 page no 7 n bits 216 index `PRIMARY` of table `test/district` trx id 0 163390 lock_mode X locks rec but not gap • TABLE LOCK table `test/customer` trx id 0 163390 lock mode IX • RECORD LOCKS space id 19 page no 57918 n bits 96 index `PRIMARY` of table `test/customer` trx id 0 163390 lock_mode X locks rec but not gap • TABLE LOCK table `test/history` trx id 0 163390 lock mode IX XTraDB OpenSource Storage Engine for MySQL

  29. TransactionalReplication • Similar to Google’s patch • Information in relay-log.info is not consistent with InnoDB state. • When server crash MySQL will repeat several transaction • You are lucky if replication fails on “Duplicate key error” • In worst case you will have several transactions executed twice • Our solution: store information of binary log name and position and relay-log name and position in InnoDB transactional log file XTraDB OpenSource Storage Engine for MySQL

  30. Move tables between servers • Before was available only for MyISAM • For InnoDB only full copy of database • Very painful if you have 100GB+ of data • Useful for: • Restore only table (from backup or master) • Copy only single table to QA • Re-sharding data XTraDB OpenSource Storage Engine for MySQL

  31. Fast Recovery • Start InnoDB after crash takes ages • The same from snapshot backup • We changed internal InnoDB algorithm of handling list of pages XTraDB OpenSource Storage Engine for MySQL

  32. And last but not least • XtraBackup • OpenSource hot backup of XtraDB / InnoDB tables • Incremental / Differential backups • Stream backup (tar) directly to remote server / tape • https://launchpad.net/percona-xtrabackup • http://www.percona.com/docs/wiki/percona-xtrabackup:start XTraDB OpenSource Storage Engine for MySQL

  33. Plans • Still hunt performance improvements • Operations tasks: • Preload table / index into buffer_pool. • Several buffer_pools • Open InnoDB tables in parallel • Currently serialized • Different improvements on statistics • Full list of ideas • http://www.percona.com/docs/wiki/devplan:start:ideas XTraDB OpenSource Storage Engine for MySQL

  34. Questions ? • Thank you for coming! XTraDB OpenSource Storage Engine for MySQL

More Related