- 快召唤伙伴们来围观吧
- 微博 QQ QQ空间 贴吧
- 文档嵌入链接
- 复制
- 微信扫一扫分享
- 已成功复制到剪贴板
Innodb Architecture Performance Optimization
InnoDB架构与性能优化MySQL5.7版
展开查看详情
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