2016 12 Innodb Internals PDF
2016 12 Innodb Internals PDF
2016 12 Innodb Internals PDF
● Re-read the MySQL 5.6 reference guide on InnoDB and found it to be very vague.
● Reached out to other SMEs and performed my own lab work to help fill in the gaps.
● Turned my notes into a 5-part blog post series covering InnoDB Mechanics (MySQL
5.6) related to…
● Memory
● I/O (file structure and logs)
● I/O (table data)
● Concurrency
● Consistency / Statistics Handlings
● 55 Variables covered
● What is InnoDB?
● Importance of InnoDB
● InnoDB: A brief history
● InnoDB High Level Mechanics
● Memory
● IO
● InnoDB is the ACID compliant solution for MySQL, making it the most attractive and
most commonly used storage engine for modern installations of MySQL today.
● MySQL 8.0 is starting to phase out MYISAM engine
● No transportable MYISAM tablespaces
● mysql schema is now INNODB
● AWS RDS platforms either discourage use of MYISAM (MySQL / RDS backup
consistency issues) or just don’t allow it at all (Aurora)
● InnoDB = MySQL (OLTP) : MySQL (OLTP) = InnoDB
Having a high level understanding of InnoDB mechanics allows for ease of configuration
and ease of troubleshooting
● Memory
● I/O
● Other considerations
innodb_buffer_pool_size
● Default: 128M
● Should be approximately 75% of system memory
● Note that global memory is also shared with MYISAM key cache
Metric:
innodb_old_blocks_pct
● Default: 37%
● Consider increasing under high eviction (not made young) scenarios where a page
is being evicted before it is considered ‘hot’
● Consider decreasing if you have largely stagnant data
Metric
innodb_old_blocks_time
● Default: 1 second
● Prevent hot pages from being removed from the buffer pool by full table scans,
commonly occurring with logical backups like mysqldump
Metric
Experimentation with old blocks pct and old blocks time may be required to find the best
configuration for your use case.
innodb_buffer_pool_instances
● Default: 8, unless you have a buffer pool less than 1G, then default will be 1. In
earlier versions of 5.6 the default was 1 regardless.
Metric
● Performance Schema
● Instrument: wait/synch/mutex/innodb/buf_pool_mutex
● Adds dynamic hash indexing to innodb data pages referencing a prefix of frequently
accessed b-tree index pages / leaf nodes
● Is enabled by default
● No control of the algorithm
Metric:
● Single resource
● Partitioning supported in Percona 5.6 and added in Oracle MySQL 5.7 and
MariaDB 10.2
● If you see semaphore issues that relate to source file btr0sea.c, you need to add
partitions or disable adaptive hash indexing entirely. You may also see
improvement by adjusting innodb_thread_concurrency.
● Mutex latches are directly tied to individual indexes, so contention issues may not
be resolved by adding partitioning if you have one hot index
● Ensure that your memory configuration doesn’t put you in a position where OOM is
a possibility
● Remember that there are other global memory caches like the MYISAM key buffer
as well as session memory caches like the sort, join, read, and read_rnd buffer
● Mysqlcalculator.com is a good baseline
Q&A
innodb_log_file_size / innodb_log_files_in_group
Metric
innodb_log_buffer_size
● Default = 8M
Metric
innodb_flush_log_at_trx_commit
● Default = 1
● Setting to anything other than 1 forces sacrifices ACID compliance for performance
gain
Metric
● Performance schema
● Instrument: wait/io/file/innodb/innodb_log_file
sync_binlog
● Unlike redo logs, binary logs are meant to drive PITR and replication and is not a
part of InnoDB data consistency
● Synchronizes the flushing of commit groups to binary log at transaction commit
● Default = 0
● Set to 1 when you want the binary log to stay in sync with InnoDB. This will have a
performance impact. Setting to 0 is still ACID compliant, but the binary logs are not
considered to be consistent to the rest of the RDBMS.
Metric
● Performance schema
● Instrument: wait/io/file/sql/binlog
● innodb_adaptive_flushing
● innodb_adaptive_flushing_lwm
● innodb_max_dirty_pages_pct
● innodb_max_dirty_pages_pct_lwm
● innodb_flushing_avg_loops
● innodb_lru_scan_depth
● innodb_flush_neighbors
● innodb_flush_method
● innodb_write_io_threads
● innodb_io_capacity
● innodb_io_capacity_max
innodb_checksum_algorithm
innodb_doublewrite
Metric
● Performance schema
● Instrument: wait/synch/mutex/InnoDB/buf_dblwr_mutex
innodb_file_per_table
Metric
● Performance schema
● Instrument: wait/io/file/innodb/innodb_data_file
● Table: file_summary_by_event_name
● Table: file_summary_by_instance
innodb_file_format
Q&A
● Innodb_buffer_pool_size
● Approx 75% of system memory
● Innodb_file_per_table
● On
● Innodb_file_format
● Barracuda
● Innodb_checksum_algorithm
● crc32 (strict if this is brand new with no data)
● Innodb_log_file_size / files in group
● Start with at least 512MB, adjust to make sure you have 1 hour of DML covered
● sync_binlog
● 1, keep it that way unless you need to move past an issue
● Innodb_flush_method
● Direct_io is best for most modern use cases. You should still consider fsync if you are not
using innodb file per table
● Innodb_io_capacity
● Set to 60% of IOPS capacity, especially if you’re using EC2 or RDS
● Default is 200, most disk systems are considerably faster
● Innodb_io_capacity_max
● Set to 99% of IOPS capacity
● Performance_schema
● Key tables
■ Events_waits_summary_global_by_event_name
■ Events_statements_summary_by_digest
● 5.5: Consider enabling when you’re having issues
● 5.6: Consider enabling unless you have a highly concurrent workload
● 5.7: Enable it
● Historical graphing
● Percona Monitoring and Management Platform
● Vivid Cortex