Innodb Architecture Performance Optimization

1 . Innodb Architecture and Performance Optimization MySQL 5.7 Edition Peter Zaitsev PgDay Russia 2017, Saint Petersburg | July 5th, 2017 

2 .Percona Live Europe Call for Papers & Registration are Open! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland Submit Your Proposal by July 17th! www.percona.com/live/e17 2 © 2017 Percona 

3 .Why Together ? Advanced Performance Optimization Needs Architecture Knowledge 3 

4 . Right Level Focus on Details What Matter 4 4 

5 .Wonder where Graphs are from ? Graphs from Percona Monitorign and Management Explore Online Demo at http://pmmdemo.percona.com 5 

6 .What about MySQL 8.0 ? This is what was previously called MySQL 5.8 Only 2nd Milestone release is available Innodb Features are still evolving 6 

7 . What Architecture ? Data Details for Background Structures • Transactions Activities • On Disk • MVCC • Purging • In Memory • Locking • Checkpointing • Latching • Flushing 7 7 

8 . Innodb Versions Covered Improvements MySQL 5.7 as Changes in in XtraDB / Baseline MariaDB Percona Server 8 8 

9 . Innodb Basics •B+Tree Based “Traditional” •ACID Transactions Storage Engine •MVCC •OLTP Optimized 9 9 

10 . Data Structures 10 

11 .Main “Objects” Tablespace Table File Segment Extent Page Index Redo Log Log Undo Undo Slot Record Space 11 

12 .Innodb Main Files Tablespace Files Log Files 12 

13 . Tablespace All Data Stored In Tablespaces System Tablespace Per-Table Tablespace Undo Tablespace(s) MySQL 5.6+ Temporary Tablespace MySQL 5.7+ General Tablespaces MySQL 5.7+ 13 

14 . Performance Considerations Innodb_file_per_table is default in MySQL 5.6+ Can cause problems with many tables Can cause problems with many CREATE/DROP/TRUNCATE Improved in MySQL 5.7 14 14 

15 . Tablespace: Physical Structure 15 15 

16 . Table Stored In Tablespace Consists of Indexes May have many partitions 16 16 

17 . Table Consists of Indexes ? Data is stored in CLUSTERED Index PRIMARY KEY or hidden Secondary keys point to PRIMARY KEY 17 17 

18 . Index is B+Tree 18 18 

19 .File IO during INSERT 19 

20 . Row Operations During Insert 20 20 

21 . Physical and Logical Structure Segments are Similar to Files Each Index has 2 segments Leaf Page Segment Non-Leaf Page Segment 21 21 

22 . Page Details 22 22 

23 . Performance Considerations Have PRIMARY KEY Short PRIMARY KEYs are better Sequential inserts are much faster for PRIMARY KEY Great to keep Secondary Keys in Memory 23 23 

24 . Redo Logs 2 (or more) files concatenated as redo log of fixed size Log consists of records (512b aligned) “Physio-logical” Redo record format Every change to Tablespace must be recorded in redo log before it is done (except temporary tablespace in MySQL 5.7) 24 24 

25 . Redo log buffering Buffered in Log Buffer Optionally Flushed at Transaction Commit Flushed Periodically 25 25 

26 . Performance Considerations Innodb_log_file_size • to control size of redo log innodb_log_write_ahead_size • Avoid ‘read-around-write’ for large log files not in cache • better write performance (less flushing) Larger log size • longer recovery time 26 26 

27 . Innodb Checkpointing How Much redo log space is used 27 27 

28 .Mind innodb_io_capacity • Recommended Setting can decrease performance 28 

29 . How Durable Transactions do you Need ? 29 29