1 / 32

InnoDB Performance and Usability Patches - Enhancing MySQL

Learn about the performance and usability patches for InnoDB Storage Engine designed to optimize for modern hardware and scalability. Explore versions, IO management, adaptive checkpointing, and more.

arodney
Download Presentation

InnoDB Performance and Usability Patches - Enhancing 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. InnoDB Performance and Usability Patches MySQL CE 2009 Vadim Tkachenko, Ewen Fortune Percona Inc MySQLPerformanceBlog.com

  2. Who are we ? • Vadim Tkachenko • Co-Founder of Percona Inc • Lead of R&D department • Co-Author MySQLPerformanceBlog.com • Co-Author “High Performance MySQL” 2nd edition book • Ewen Fortune • Consultant, Percona Inc • Special Thanks Yasufumi Kinoshita • Not here, but author of most patches InnoDB Performance and Usability Patches

  3. What is this talk about? • Patches made by Percona for InnoDB Storage Engine • Two main focuses • Performance improvement patches • “Usability” patches • 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 InnoDB Performance and Usability Patches

  4. Why we do it • 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 team is “conservative” in making improvements in this area InnoDB Performance and Usability Patches

  5. Future • Why patches ? Why it can’t be included in InnoDB ? • We are often asked about, but actually question is to InnoDB team • (empty space due to uncertainty of MySQL future in Oracle) • Anyway we will continue our work InnoDB Performance and Usability Patches

  6. Versions • 5.0 • Set of patches • SHOW PATCHES to see full list • 5.1 • Storage engine XtraDB • Based on InnoDB + patches, not real competitor of InnoDB, but drop-in enhanced version InnoDB Performance and Usability Patches

  7. Performance Patches InnoDB Performance and Usability Patches

  8. Scalability • Enhanced read_write locks • Improves InnoDB scalability on systems with 8-16 cores • Similar on Google implementation, InnoDB-plugin-1.0.3 • Our implementation is alternative • Topic to research which one is better • InnoDB-plugin may be preferred, InnoDB team made hard job porting it to many platforms • And now in 5.4 • Split buffer_pool mutex even more • Additional split of buffer_pool mutex to 5.0.33 InnoDB Performance and Usability Patches

  9. IO patches • InnoDB IO patches • Part similar to Google’s InnoDB IO patches, but again alternative  • Several parts – some of them now in 5.4 InnoDB Performance and Usability Patches

  10. 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) InnoDB Performance and Usability Patches

  11. 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 InnoDB Performance and Usability Patches

  12. Adaptive checkpointing InnoDB default behavior, hiccups during buffer_pool flushing InnoDB Performance and Usability Patches

  13. Adaptive checkpoint • What we do: • Flush pages more intensive • the closer checkpoint the more intensive InnoDB Performance and Usability Patches

  14. Adaptive_checkpoint • Adaptive_checkpoint=1 InnoDB Performance and Usability Patches

  15. 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 InnoDB Performance and Usability Patches

  16. IO – multiple pages • Read_ahead = (both | linear | random) • Control to use or not internal InnoDB read-ahead logic • 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 InnoDB Performance and Usability Patches

  17. Extra rollback segments • By default InnoDB uses single segment protected by mutex • Sensitive in intensive parallel insert load InnoDB Performance and Usability Patches

  18. Fix group commit • “Broken” in 5.0 • Problem appears on slow disks with enabled binary-logs InnoDB Performance and Usability Patches

  19. Benchmark • Tpcc-like workload • 100 Warehouses (about 10GB of data) • Buffer_pool=5GB • System: Dell PowerEdge R900, RAID 10 on 8 disks, RAM 32GB • O_DIRECT for InnoDB, xfs filesystem, mounted with nobarrier • 5.0.77 vs 5.0.77-percona • Had no chance to test 5.4 yet InnoDB Performance and Usability Patches

  20. Benchmark InnoDB Performance and Usability Patches

  21. Usability patches InnoDB Performance and Usability Patches

  22. Microslow • InnoDB part InnoDB_IO_r_ops: 1 InnoDB_IO_r_bytes: 16384 InnoDB_IO_r_wait: 0.028487 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 5 InnoDB Performance and Usability Patches

  23. 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 datadictionary entries • Our solution: • LRU based datadictionary entries • Remove from memory oldest entries if limit reached InnoDB Performance and Usability Patches

  24. IO access pattern Show pattern of pages on disk accessed • mysql> select INDEX_ID,TABLE_NAME,INDEX_NAME,sum(N_READ),sum(N_WRITE) from INFORMATION_SCHEMA.INNODB_ALL_PAGE_IO group • by INDEX_ID; • +------------+------------------+-------------------+-------------+--------------+ • | INDEX_ID | TABLE_NAME | INDEX_NAME | sum(N_READ) | sum(N_WRITE) | • +------------+------------------+-------------------+-------------+--------------+ • | 30 | tpcc/item | PRIMARY | 547 | 0 | • | 32 | tpcc/district | PRIMARY | 1 | 1 | • | 36 | tpcc/history | GEN_CLUST_INDEX | 11 | 5 | • | 37 | tpcc/history | fkey_history_1 | 166 | 163 | • | 38 | tpcc/history | fkey_history_2 | 37 | 30 | • | 39 | tpcc/new_orders | PRIMARY | 76 | 76 | • | 43 | tpcc/order_line | PRIMARY | 218 | 189 | • | 44 | tpcc/order_line | fkey_order_line_2 | 1040 | 1040 | • | 46 | tpcc/stock | PRIMARY | 3137 | 1764 | • | 47 | tpcc/stock | fkey_stock_2 | 269 | 0 | • | 48 | tpcc/customer | PRIMARY | 960 | 580 | • | 49 | tpcc/customer | idx_customer | 171 | 0 | • | 50 | tpcc/orders | PRIMARY | 94 | 70 | • | 51 | tpcc/orders | idx_orders | 142 | 129 | InnoDB Performance and Usability Patches

  25. 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 | +-------+---------+---------+----------+------------+--------------+-------------+ InnoDB Performance and Usability Patches

  26. 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 InnoDB Performance and Usability Patches

  27. 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 InnoDB Performance and Usability Patches

  28. Extra undo slots • By default 1024 slots to store transaction undo information, that may limit count of concurrent transactions to 512 • We increase to 4072 • Only on 5.1 XtraDB • Use it only if you need, breaks compatibility with InnoDB InnoDB Performance and Usability Patches

  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 InnoDB Performance and Usability Patches

  30. Plans • Still hunt performance improvements • Operations tasks: • Fast recovery • There is reported bug http://bugs.mysql.com/bug.php?id=29847 • Preload table / index into buffer_pool. • Copy single .ibd table from one server to different • Open InnoDB tables in parallel • Currently serialized • Different improvements on statistics • Some patches already published (not by us) InnoDB Performance and Usability Patches

  31. To finalize • Most of patches is not rocket science • Could be developed or included in official tree long time ago • Even more, for some patches we just only uncommented few lines of code • Expect most of them in MariaDB 5.1 InnoDB Performance and Usability Patches

  32. Questions ? • Thank you for coming! InnoDB Performance and Usability Patches

More Related