XtraDB / InnoDB internals in drawing (Vadim Tkachenko 2010.04)


2019/08/18 发布于 技术 分类

innodb_int.pdf 来源:https://www.percona.com/blog/2010/04/26/xtradb-innodb-internals-in-drawing/


1. Memory query buffer pool Page level cache for tables and indexes Table1.ibd pages Table2.ibd pages Disk innodb_buffer_pool_size is critical parameter Thumb rule: 60-80% of RAM Table3.ibd pages XtraDB: I_S.INNODB_BUFFER_POOL_PAGES shows content of buffer_pool query Reads are done in foreground. innodb_read_io_threads are for read-ahead background thread “merges” buffer with indexes Table1.ibd Primary key == data Pages are written in background innodb_write_io_threads. innodb_flush_method = O_DIRECT To avoid caching in OS cache Insert buffer part of buffer_pool InnoDB-std: may take ½ of buffer pool XtraDB: innodb_ibuf_max_size Disable: innodb_change_buffering=0 Adaptive hash search – speeds up search by secondary indexes lookups and range scans XTraDB: check sizes in SHOW INNODB STATUS er tb Secondary indexes Files are divided by 16K pages XtraDB: 4K, 8K, 16K Table2.ibd XtraDB: you can view content in I_S.INNODB_SYS_TABLES, I_S.INNODB_SYS_INDEXES Table3.ibd Writes are done via “double write buffer” to prevent corruptions In s .ibd are placed separetely if innodb_file_per_table=1 Otherwise in ibdata1 Ibdata1 ( system table space ) uf fe r is wr itt en Data dictionaty fro m BP to di Double write buffer sk Opened tables info InnoDB-std: can grow unlimitedly XtraDB: innodb_dict_size_limit Changes are fixed in log file via log buffer Misc internal memory: Page hash XTraDB: check sizes in File system SHOW INNODB STATUS Lock system Recovery system Threads Insert buffer. Changes to secondary nonunique indexes are buffered there. InnoDB-std: rollback segment is 1 XtraDB: innodb_extra_rsegments Rollback segments Undo slot Undo slot Undo slot UNDO space may grow unlimitedly XtraDB: innodb_use_purge_thread=1 to use separate threads for cleaning UNDO space innodb_log_buffer_size 4M-16M is good value Log buffer Usually changes are fixed In background “log thread” on disk with fsync() command. innodb_flush_log_at_trx_commit controls how to fsync 1023 slots per segment Pointers to undo space log file log file REDO LOGS innodb_log_file_size InnoDB-std: max size < 4GB XtraDB: max size < 2TB innodb_log_files_in_group (usually 2-3)