new innodb features in mysql 5€¦ · in mysql 5.6 read only transactions are optimized to reduce...
TRANSCRIPT
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 12 1
New InnoDB Features in MySQL 5.6 Jimmy Yang
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 2
Program Agenda
Overview of InnoDB Features in MySQL 5.6
Performance and Scalability Features
Better Availability Features
More InnoDB Features in MySQL 5.6
Q&A
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 3
Overview of InnoDB Features in MySQL 5.6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 12 4
• Removal of legacy bottlenecks • Improved threading/concurrency • Optimized for read-only workloads
BETTER PERFORMANCE AND SCALABILITY
• Online DDL operations • Transportable tablespaces • Dump, preload buffer pool
BETTER AVAILABILITY
• Full-text search • NoSQL, key-value access to InnoDB
DEVELOPER AGILITY
InnoDB Features in MySQL 5.6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 12 5
• MRR / BKA • ICP • Persistent table stats
BETTER QUERY PERFORMANCE
• Enriched performance schema • InnoDB metrics tables • I_S for InnoDB system tables, buffer pool
BETTER MONITORING
• New default variables • Auto-handling of redo log file size mismatch • Mark table/index as corrupted • Read-only mode • Configurable data dictionary cache
BETTER USABILITY
InnoDB Features in MySQL 5.6 (2)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 6
Performance and Scalability Features
in MySQL 5.6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 7
MySQL 5.6: Scalability
Users can fully utilize latest generations of hardware and OS Scales as data volumes and users grow
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 8
MySQL 5.6: Scalability
Users can fully utilize latest generations of hardware and OS Scales as data volumes and users grow
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 9
Performance and Scalability (1)
Improved threading and concurrency • Remove legacy mutexes (kernel mutex split) • Multi threaded purge • Separate thread to flush dirty pages • Remove false CPU cacheline sharing • Improve thread scheduling • Reduce contention during tablespace extension
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 10
Kernel Mutex Split
Misc. • Heuristics • Thread states • History length • Task queue • Drop list
Core • Locking
• Transaction life cycle • Read views • Query stats
Kernel mutex is a global mutex that protects the access of core sub-systems as well some miscellaneous resources.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 11
Kernel Mutex Split
kernel mutex splits into different mutexes for each of their functionalities • Server mutex
• Lock system mutex
• Lock wait mutex
• Transaction system mutex • Transaction mutex
• Task queue mutex
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 12
Multi Threaded Purge Purge is a type of garbage collection. Purge includes:
• Remove obsolete values from indexes • Remove delete marked records that will not be seen by any active transaction
• Remove the relevant undo entries from history list Configuration Parameters:
• innodb-purge-threads: 1-32
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 13
Multi Threaded Purge
innodb_purge_threads=4
Task queue
Worker
Worker
Worker Coordinator
The coordinator will also purge records The coordinator waits for the worker threads to complete before
truncating the UNDO logs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 14
Performance and Scalability (2)
Improved IO • Improve adaptive flushing • Improve LRU flushing • Avoid neighbor flushing for SSD • Increase max size of redo log files
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 15
Flushing
Flushing is the activity of writing dirty pages & logs to the disk.
Two types of flushing activities: • LRU flushing, based on LRU_list (roughly ordered on time
since last access) • Adaptive flushing, based on flush_list (strictly ordered on
oldest_modification LSN)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 16
Improve LRU Flushing Major changes in LRU flushing algorithm
• Attempts to maintain a pool of free pages in the free list • Configurable how deep to scan LRU for a victim • Flushing happens in background instead of user threads
Configuration parameters: • innodb_lru_scan_depth (consider changing the default for
larger buffer pools)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 17
Improve Adaptive Flushing
New flushing algorithm in MySQL 5.6 • Rate at which redo log is being generated • Total redo log capacity • Rate at which dirty pages are increasing in the buffer pool • IO capacity of the system
Configuration Parameters: • innodb_adaptive_flushing • innodb_io_capacity • innodb_io_capacity_max
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 18
Improve Adaptive Flushing
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 19
Option to Disable Neighbour Flushing
Neighbours are pages on the same extent • Extents are contiguous region of 64 pages on disk • InnoDB typically attempts to flush all dirty pages within an
extent when it chooses one of them for flushing. • This behaviour is to avoid seek penalty by making IO pattern
as sequential as possible
In case of SSD there is no extra cost for random IO • Disable neighbour flushing
Configuration Parameters: • innodb_flush_neighbors
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 20
Performance and Scalability (3)
Improved InnoDB data compression Optimization for read only transactions Improved data management
• Separate tablespace(s) for InnoDB undo log • Support 4k, 8k page sizes • Allow selection of single tablespace location
Use hardware checksums (CRC32) Faster DROP table in very large buffer pools
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 21
Improve InnoDB Data Compression
Configurable compression level • innodb_compression_level - the default value is 6 (the zlib
default) and allowed values are 1 to 9.
Optional to skip logging of compressed page images • innodb_log_compressed_pages. The default value is true.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 22
Improve InnoDB Data Compression
Dynamic padding to reduce compression failures • innodb_compression_failure_threshold_pct: default 5, range 0 -
100,dynamic, implies the percentage of compress ops to fail before we start using to padding. Value 0 has a special meaning of disabling the padding.
• innodb_compression_pad_pct_max: default 50, range 0 - 75, dynamic, the maximum percentage of uncompressed data page that can be reserved as pad.
Index level compression stats • INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX • Helps to reduce compression failures
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 23
Optimization for Read-Only Transactions
In MySQL 5.6 READ ONLY transactions are optimized to reduce overhead of transaction management • No read view creation (required for MVCC) • No allocation of undo log • Separate lists for READ ONLY and READ WRITE transactions
Read-only transactions: • For auto-commit non-locking SELECTs the optimizations are
transparent • User can specify a transaction as READ ONLY • User can start the whole InnoDB instance as read only
(innodb_read_only)
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 24
Optimization for Read-Only Transactions Sysbench Point Select
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 25
Performance Tuning
One size does not fit all • Right number of buffer pool instances • Bigger metadata_locks_hash_instances • Larger redo log files • Increase innodb_lru_scan_depth for larger buffer pool • Better memory allocator, such as jemalloc library • Take advantage of optimization for read-only
transactions • Disable performance schema • Turn on innodb_fast_checksums
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 26
Better Availability Features in MySQL 5.6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 27
Online DDL operations Dump, preload buffer pool Transportable tablespaces
Better Availability Features
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 28
Online Operations in MySQL 5.6
ADD INDEX DROP INDEX
RENAME TABLE
ADD COLUMN
ALTER ROW FORMAT
ALTER COLUMN NULLABLE
ALTER COLUMN NOT NULL
DROP FOREIGN KEY
ADD FOREIGN KEY
ADD PRIMARY KEY
DROP COLUMN
RENAME COLUMN
ALTER KEY_BLOCK_SIZE
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 29
ibdata files
Sys
tem
tabl
espa
ce
internal data
dictionary
MySQL Data Directory
InnoDB tables
OR innodb_file_per_table
.ibd files
.frm files
undo logs
change buffer
InnoDB Database Files
doublewrite buffer
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 30
Type of Online Operations
Metadata only • MySQL Server metadata, such as alter column default • MySQL Server metadata & InnoDB metadata, such as add/
drop foreign key
Metadata plus w/o rebuilding the table, such as add/drop index
Metadata plus rebuilding the table, such as add primary index, add column.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 31
Pre-prepare Prepare Build Final
How Does It Work?
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 32
Pre-preparation Phase
Server • Determine the algorithm and concurrency level supported by
the storage engine. • Hold MDL_SHARED_UPGRADABLE: allow concurrent DML
InnoDB ha_innobase::check_if_supported_inplace_alter() • Check if InnoDB supports a particular alter table in-place.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 33
Prepare Phase
Server • Upgrade to MDL_EXCLUSIVE: no concurrent DML allowed • Build internal objects describing requested changes
InnoDB ha_innobase::prepare_inplace_alter_table() • Check whether the alter is legitimate • Update internal structures • Create temporary file(s) for change log(s) due to DMLs • Start logging
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 34
Build Phase
Server • Hold MDL_SHARED_UPGRADABLE: allow concurrent
DML • Let storage engine to carry out the changes requested by
ALTER.
InnoDB ha_innobase::inplace_alter_table() • Alter the table in-place with operations specified. • Apply the change logs
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 35
Final Phase
Server • Update .frm and remove old table definitions • Upgrade to MDL_EXCLUSIVE: no concurrent DML allowed • Notify storage engine • Cleanup internal structures
InnoDB ha_innobase:: commit_inplace_alter_table()
• Commit or rollback the changes a) Sync and delete the logs b) Commit metadata changes c) Cleanup internal structures
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 36
How Does It Work - Online Add Index CREATE INDEX index_name ON table name (c1)
Pre- Prepare Phase Prepare Phase
Build Phase
Final Phase
Concurrent User Source (table)
Scan clustered index; Extract index entries; Sort / merge index build Drop old table (if primary)
No concurrent DML allowed
Upgradable Shared Metadata Lock
DML Logging; Apply logs at the end of create index
Create temp table for new index (if primary)
Upgradable Shared Metadata Lock
Metadata Lock
Concurrent Select, Delete, Insert, Update
(cluster) Index
Create log files; Logging starts
Update system tables (metadata)
Exclusive Metadata Lock
Exclusive Metadata Lock
Concurrent Select, Delete, Insert, Update
No concurrent DML allowed
Check whether the online DDL is supported
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 37
Considerations for Online Operations (1)
• • • •
a) innodb_sort_buffer_size b) innodb_online_alter_log_max_size
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 38
Considerations for Online Operations (2)
• •
•
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 39
Example 1: Add / Drop Index
mysql: set old_alter_table=0; Query OK, 0 rows affected (0.00 sec)
mysql: create index i_dtyp_big on big_table (data_type); Query OK, 0 rows affected (37.93 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql: drop index i_dtyp_big on big_table; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql: set old_alter_table=1; Query OK, 0 rows affected (0.00 sec)
mysql: create index i_dtyp_big on big_table (data_type); Query OK, 1731584 rows affected (4 min 59.33 sec) Records: 1731584 Duplicates: 0 Warnings: 0
mysql: drop index i_dtyp_big on big_table; Query OK, 1731584 rows affected (3 min 55.90 sec) Records: 1731584 Duplicates: 0 Warnings: 0
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 40
Example 2: Rename Column
mysql: set old_alter_table=0; Query OK, 0 rows affected (0.00 sec)
mysql: alter table big_table change `flags` `new_flags` -> varchar(3) character set utf8 not null; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql: set old_alter_table=1; Query OK, 0 rows affected (0.00 sec)
mysql: alter table big_table change `new_flags` `flags` -> varchar(3) character set utf8 not null; Query OK, 1731584 rows affected (3 min 31.78 sec) Records: 1731584 Duplicates: 0 Warnings: 0
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 41
Preload Buffer Pool
Resolves “cold cache” problem
Buffer pool contents can be dumped at any point in time • Can be configured to dump automatically at shutdown
The dumped contents can be loaded at any point in time • Can be configured to load automatically at startup
Configuration Parameters: • innodb_buffer_pool_dump_* • innodb_buffer_pool_load_*
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 42
Transportable Tablespaces
A frequently requested feature since MySQL 4.1 Challenge: Resolving dependencies on…
• Change buffer • Undo logs • Crash recovery • Data dictionary
Solutions: • Make tablespaces ‘clean’ on export • Adjustments on import
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 43
Transportable Tablespaces (2) Export:
• FLUSH TABLE t FOR EXPORT; • Copy tablespace file(s) • UNLOCK TABLES;
Import: • CREATE TABLE t; • ALTER TABLE t DISCARD TABLESPACE; • Move the copied file(s) in place • ALTER TABLE t IMPORT TABLESPACE;
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 44
More InnoDB Features in MySQL 5.6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 45
More New Features
InnoDB full-text search
Direct access to InnoDB via memcached Monitoring and diagnostics
Improved usability • New default variables • Auto-handling of redo log file size mismatch • Mark table / index as corrupted • Read-only mode • Configurable data dictionary cache
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 46
InnoDB Full-Text Search
InnoDB full-text index as an inverted index
Support all query types supported by MyISAM: • Natural language search • Query expansion • Boolean search
Plus • Proximity search: a special type of Boolean search • Create full-text index with parallel tokenization and sorting
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 47
Fast, simple access to InnoDB • Accessed via Memcached API • Use existing Memcached clients • Bypasses SQL transformations
Implementation • Memcached daemon plug-in to mysqld • Memcached protocol mapped to the
native InnoDB API • Shared process space for ultra-low
latency
SQL (MySQL Client)
InnoDB Storage Engine
MySQL Server Memcached plugin
Application NoSQL (Memcached Protocol) mysqld
NoSQL to InnoDB via Memcached API
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 48
NoSQL to InnoDB via Memcached API (2)
SQL/NoSQL access• NoSQL for key-value operations• SQL for rich queries, JOINs, FKs, etc.
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 49
Monitoring & Diagnostics
Enriched performance schema • Covers 46 mutexes, 12 rwlocks, 7 types of threads, 3 types
of I/O
Information schema metrics table • 17 modules, 207 counters
Information schema for InnoDB system tables Information schema for InnoDB buffer pool
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 50
More Information
https://blogs.oracle.com/mysqlinnodb/
http://dimitrik.free.fr/blog/index.html
http://mysqlha.blogspot.com/
http://dev.mysql.com/tech-resources/articles/mysql-5.6.html
http://dev.mysql.com/doc/ • http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html • http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html • http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. 51
Questions?