mysql 5.6 - operations and diagnostics improvements

MySQL 5.6 - Online Operations and Improved Diagnostics Morgan Tocker, MySQL Community Managerhttp://www.tocker.ca/

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

MySQL 5.5MySQL Cluster 7.3

MySQL Enterprise Monitor 2.3 & 3.0MySQL Enterprise Backup Security Scalability HA Audit

MySQL 5.6MySQL Workbench 6.0

M y S Q L U t i l i t i e s

M y S Q L A p p l i e r f o r

H a d o o p

MySQL Workbench 5.2 & 6.0M y S Q L E n t e r p r i s e O r a c l e C e r t i f i c a t i o n s

4 Years of MySQL Innovation

M y S Q L C l u s t e r M a n a g e r

Windows installer & Tools

MySQL Cluster 7.2MySQL Cluster 7.1

MySQL Migration Wizard

MySQL 5.7

Agenda for Today

• Quick Intro to MySQL 5.6 • Operational Enhancements • Diagnostic Enhancements

Quick Intro to 5.6

The Path to MySQL 5.6

April 2011

!Optimizer:

MRR, ICP, File Sort

InnoDB:

Split Kernel Mutex, MT Purge

Replication:

Crash-Safe, Multi-Thread Slave, Checksums

Memcached API

New P_S

Partitioning Improvements

Oct 2011

!Optimizer:

BKA, New EXPLAIN, Traces

InnoDB:

Dump/Restore Buffer Pool

More P_S

Dec 2011

!Optimizer:

Sub-Queries

InnoDB:

Full Text Index, Read-Only Optimizations

Condition Handling

Fractional Seconds

Aug 2012

!Optimizer:

JSON EXPLAIN, Sub-Queries

Replication:

TIMESTAMP & DATESTAMP

More P_S

Aug 2012

!InnoDB:

TT, Online DDL, Memcached API

Replication:

Binary Log Group Commit

Password Mgmt

More Partitioning

Nov 2012

!New Server Defaults

More Partitioning

More GTID, TT

• “MySQL 5.6 is the largest MySQL code size increase in a MySQL version ever. The last time we saw anything like this was with the merging of MySQL Cluster in 4.1. At the very least, Oracle is paying people to write lines of code to extent that nobody has before.” - Stewart Smith

By Lines of Code

By Bugs Fixed

• 1991 Bugs Fixed in 5.6 • 3763 Bugs Fixed in Total Since MySQL 5.5 GA

By Major Features

• Better Performance and Scalability • Better Transactional Throughput • Better Performance with Solid State Drives • Better Query Execution Times and Diagnostics • Better Application Availability • NoSQL Access to InnoDB • InnoDB Fulltext Search • Improved Replication and High Availability • Improved Performance Schema

By Actual Features / EnhancementsScalable Read Only Transactions Improvement to Buffer Pool Flushing Precise spatial operations in GIS

Concurrent Innodb data file extension Subquery Optimizations Password hashes instead of plain passwords in Query Logs

Non-Recursive Deadlock Detection More efficient Optimizer SHA256 hashing with Salt for Authentication

Faster Locking Primitives Optimized ROW Based Replication Use obfuscated password storage for command line tools

Improved Innodb Thread Concurrency Multi-Threaded Slave Policy Based password validation

Multiple background Purge Threads Global Transaction Identifiers Plugin authentication support for Replication

Improved Purge lag control (now works) Crash Safe Slave and Binlog INNODB_METRICS (I_S)

Split of “Kernel Mutex” Replication Event Checksums Meta Data Information Tables (I_S)

Data Dictionary Cache Time Delayed Replication Buffer Pool Information Tables (I_S)

Improved Adaptive Flushing Server UUID Reduced Overhead (PS)

Page Cleaner/Separate Flush Thread Improved Logging for Row based Replication Simplified Configuration (PS)

Group Commit for Binary Log Replication Utilities for Failover and Admin Table Access instrumentation (PS)

Fight Cache Coherence and False Sharing issues Separate Tablespaces for Innodb Undo Logs Statements instrumentation (PS)

Reduced Innodb Memory Fragmentation Fast Restart – Preloading Innodb Buffer Pool Stages Instrumentation (PS)

Reduced Locking for Partitioned tables Online DDL Aggregations by User, Host etc (PS)

Reduced Contention for LOCK_open Import/Export for Partitioned Tables Network IO Instrumentation (PS)

Support for multiple table_open_cache instances Remote Binlog Backup Show Host Cache Contents (PS)

Large (over 4GB) redo logs support Innodb Transportable Tablespaces Improved File I/O Instrumentation (PS)

Index Condition pushdown (ICP) New configuration files defaults Explain for UPDATE/DELETE queries

Multi-Range-Read (MRR) User Defined DATA DIRECTORY for Innodb Tables JSON output with more information

Faster ORDER BY nidxcol LIMIT N Connection Attributes Optimizer Tracing

Persistent Statistics for Innodb MemcacheD API in Innodb Deadlock Logging

Improvements to Innodb Compression Explicit Partition Selection in queries GET DIAGNOSTICS

Fast Page Checksums (CRC32) Full Text Search index for Innodb

4K and 8K Page sizes for Innodb Microsecond TIME precision

Operational Enhancements

InnoDB Online DDL

The beginning All DDL requires the table to be recreated, along with all indexes.

MySQL 5.1 - Nov 2008 Fast ALTER TABLE added. Changes to ENUM/SET online.

MySQL 5.5 - Dec 2010 Fast index creation added. Adding indexes changes to SHARED lock, dropping indexes immediate*

MySQL 5.6 - Jan 2013 Online DDL. Many changes do not block readers or writers.

Via http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

LOperation In-Place? Copies Table? Allows Concurrent

DML?Allows Concurrent Query?

CREATE INDEX,ADD INDEX Yes* No* Yes YesADD FULLTEXT INDEX Yes No* No YesDROP INDEX Yes No Yes YesSet default value for a column Yes No Yes YesChange auto-increment value for a column Yes No Yes YesAdd a foreign key constraint Yes* No* Yes YesDrop a foreign key constraint Yes No Yes YesRename a column Yes* No* Yes* YesAdd a column Yes Yes Yes* YesDrop a column Yes Yes Yes YesReorder columns Yes Yes Yes YesChange ROW_FORMAT property Yes Yes Yes YesChange KEY_BLOCK_SIZE property Yes Yes Yes YesMake column NULL Yes Yes Yes YesMake column NOT NULL Yes* Yes Yes YesChange data type of column No Yes No YesAdd primary key Yes* Yes Yes YesDrop primary key and add another Yes Yes Yes YesDrop primary key No Yes No YesConvert character set No Yes No YesSpecify character set No Yes No YesRebuild with FORCE option No Yes No Yes

• Also supports additional syntax LOCK=NONE and ALGORITHM=INPLACE;

InnoDB Online DDL (cont.)

mysql> ALTER TABLE a DROP PRIMARY KEY, LOCK=NONE;!

ERROR 1846 (0A000): LOCK=NONE is not supported. !

Reason: Dropping a primary key is not allowed without !

also adding a new primary key. Try LOCK=SHARED.

• Automatic cache priming feature. • Saves LRU contents on shutdown (or on demand)

and reloads pages into memory on startup. • Not enabled by default:

• innodb_buffer_pool_load_at_startup = 1 • innodb_buffer_pool_dump_at_shutdown = 1

Buffer Pool Dump and Restore

• Very helpful feature for time-series data. • Swap partitions with regular tables:

Import/Export Partitioned Tables

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE t;

• InnoDB data can be copied to a different server without using mysqldump:

Transportable Tablespaces

/* source */ FLUSH TABLES tableName FOR EXPORT; /* destination */ ALTER TABLE tableName IMPORT TABLESPACE;

• InnoDB not complaining when innodb_log_file_size has changed between restarts:

Important Bugs Fixed

2013-08-18 20:49:29 17883 [Warning] InnoDB: Resizing redo log from 2*3072 to 2*8192 pages, LSN=5980091!

2013-08-18 20:49:29 17883 [Warning] InnoDB: Starting to delete and rewrite log files.!

2013-08-18 20:49:29 17883 [Note] InnoDB: Setting log file ./ib_logfile101 size to 128 MB!

InnoDB: Progress in MB: 100!

2013-08-18 20:49:30 17883 [Note] InnoDB: Setting log file ./ib_logfile1 size to 128 MB!

InnoDB: Progress in MB: 100!

2013-08-18 20:49:30 17883 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

• Binary log coordinates can now be global - rather than per instance.

• Which means failover/topology changes are now much easier.

Replication with GTIDs

• On a per schema basis, replication supports parallel apply on slaves. • In 5.7 it will be intra-schema parallel.

Multi-threaded slaves!

Diagnostics Enhancements

EXPLAIN FORMAT=JSONEXPLAIN: {!

"query_block": {!

"select_id": 1,!

"nested_loop": [!

"table": {!

"table_name": "country",!

"access_type": "ref",!

"possible_keys": [!

"PRIMARY",!

"Population"!

"key": "Population",!

"used_key_parts": [!

"Population"!

• Previously only SELECT statements could be explained.

• Required one to rewrite UPDATE/DELETE statement to a SELECT in order to EXPLAIN.

EXPLAIN UPDATE/DELETE statements

Optimizer Trace

"join_optimization": {!

"select#": 1,!

"steps": [!

"condition_processing": {!

"condition": "WHERE",!

"original_condition": "(`test`.`alias1`.`pk` and \!

(`test`.`alias2`.`pk` = `test`.`alias1`.`col_int_key`))",!

"steps": [!

"transformation": "equality_propagation",!

"resulting_condition": "(`test`.`alias1`.`pk` and \!

multiple equal(`test`.`alias2`.`pk`, \!

`test`.`alias1`.`col_int_key`))"!

• Incredibly Verbose output. • Shows why query plans were not chosen. • Explains transformations that applied.

Optimizer Trace (cont.)

Performance Schema

• Largely SHOW GLOBAL STATUS + SHOW ENGINE INNODB STATUS tuning.

• Some perf tools external to MySQL provided by the operating system. • Barrier to entry sometimes higher.

“The OLD way”

• With SHOW GLOBAL STATUS, you can see a ratio of Temp tables in memory versus Temp tables on disk.

• OK, How many rows per temp table on disk? • If it’s a couple, it might just be logical IO.

Old way is not always instrumented to a useable detail…

• SHOW STATUS shows created temporary tables as a counter, but: • Can’t find which query created.

Not Query-Oriented

• InnoDB status contains a lot of magic numbers. Tools like cacti-templates require light weight parsers just to be able to read the information: !!!

Difficult for Tooling

------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 History list length 20 Total number of lock structs in row lock hash table 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 3491 MySQL thread id 32, query id 4668737 localhost heikki show innodb status!

• Series of views which expose internal server performance data with timing information.

• Similar to “Oracle Wait Interface”.

The New Way - Performance Schema

• Allows you to monitor server events/stages of execution.

• Place “probes” at instrumentation points. • Focus is on low overhead/fast collection. • Uses fixed memory, designed to be able to use in

production.

Performance Schema

• Instrumented features which are mainly useful to developers: • File I/O • Mutexes • RW Locks

• Did not yet include query level instrumentation :( • Was not enabled by default.

Performance Schema in MySQL 5.5

• Network IO, Table IO, Stages of execution time. • Brings number of instruments up to 545. • Performance_schema is now 52 tables. • Enabled by default.

Performance Schema in MySQL 5.6

PS in 5.6

accounts events_statements_summary_global_by_event_name rwlock_instances

cond_instances events_waits_current session_account_connect_attrs

events_stages_current events_waits_history session_connect_attrs

events_stages_history events_waits_history_long setup_actors

events_stages_history_long events_waits_summary_by_account_by_event_name setup_consumers

events_stages_summary_by_account_by_event_name

events_waits_summary_by_host_by_event_name setup_instruments

events_stages_summary_by_host_by_event_name events_waits_summary_by_instance setup_objects

events_stages_summary_by_thread_by_event_name events_waits_summary_by_thread_by_event_name setup_timers

events_stages_summary_by_user_by_event_name events_waits_summary_by_user_by_event_name socket_instances

events_stages_summary_global_by_event_name events_waits_summary_global_by_event_name socket_summary_by_event_name

events_statements_current file_instances socket_summary_by_instance

events_statements_history file_summary_by_event_name table_io_waits_summary_by_index_usage

events_statements_history_long file_summary_by_instance table_io_waits_summary_by_table

events_statements_summary_by_account_by_event_name

host_cache table_lock_waits_summary_by_table

events_statements_summary_by_digest hosts threads

events_statements_summary_by_host_by_event_name

mutex_instances users

events_statements_summary_by_thread_by_event_name

objects_summary_global_by_type

events_statements_summary_by_user_by_event_name

performance_timers

SELECT user, host, 100 * (SUM(IF(event_name = 'idle', sum_timer_wait, 0)) / SUM(sum_timer_wait)) pct_idle, ps_helper.format_time( SUM(IF(event_name = 'idle', sum_timer_wait, 0)) ) total_idle FROM events_waits_summary_by_account_by_event_name WHERE host IS NOT NULL GROUP BY user, host; +---------+-----------------------------+----------+---------------+ | user | host | pct_idle | total_idle | +---------+-----------------------------+----------+---------------+ | root | localhost | 99.9919 | 00:18:55.3054 | | test_hc | TFARMER-MYSQL.wh.oracle.com | 100.0000 | 20.61 s | +---------+-----------------------------+----------+---------------+ 2 rows in set (0.02 sec)

Example How Idle are your connections?

Example Re-implement SHOW PROCESSLIST, but add interface

SELECT p.*, CASE WHEN PORT = 0 AND IP = '' THEN 'Unix Socket' WHEN IP REGEXP '^(::ffff:)?[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN 'IPv4' WHEN PORT > 0 THEN 'IPv6' ELSE 'Undetermined' END AS interface FROM performance_schema.socket_instances si RIGHT JOIN performance_schema.threads t ON (t.thread_id = si.thread_id) JOIN information_schema.processlist p ON (t.processlist_id = p.id)\G *************************** 1. row *************************** ID: 6 USER: root HOST: localhost:2873 DB: performance_schema COMMAND: Query TIME: 0 STATE: executing INFO: SELECT /* snip */ ON (t.processlist_id = p.id) interface: IPv6 1 row in set (0.13 sec)

Example More detailed PROCESSLIST (version 2.0)

mysql> select * from processlist_full where conn_id is not null\G ... *************************** 8. row *************************** thd_id: 12400 conn_id: 12379 user: root@localhost db: ps_helper command: Query state: Copying to tmp table time: 0 current_statement: selectfrom processlist_full where conn_id is not null last_statement: NULL last_statement_latency: NULL lock_latency: 1.00 ms rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 1 tmp_disk_tables: 0 full_scan: YES last_wait: wait/synch/mutex/sql/THD::LOCK_thd_data last_wait_latency: 62.53 ns source: sql_class.h:3843

Example Which account fails to close connections?

SELECT ess.USER, ess.HOST, (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR not_closed, ((a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) - ess.COUNT_STAR) * 100 / (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) pct_not_closed FROM!

performance_schema.events_statements_summary_by_account_by_event_name ess!

JOIN performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST)!

2 rows in set (0.00 sec)

Example How much time could I save in CI by using tmpfs?

mysql> SELECT à`.ÈVENT_NAME`, à`.`SUM_TIMER_WAIT`/1024/1024/1024 AS `total_latency_ms`, `b`.`SUM_NUMBER_OF_BYTES_READ` AS `total_bytes_read`, `b`.`SUM_NUMBER_OF_BYTES_WRITE` AS `total_bytes_written` FROM `performance_schema`.èvents_waits_summary_global_by_event_name` à` INNER JOIN `performance_schema`.`file_summary_by_event_name` `b` USING (event_name) WHERE à`.ÈVENT_NAME` like 'wait/io/file/%' AND à`.`COUNT_STAR` > 0; +--------------------------------------+------------------+------------------+---------------------+ | EVENT_NAME | total_latency_ms | total_bytes_read | total_bytes_written | +--------------------------------------+------------------+------------------+---------------------+ | wait/io/file/sql/casetest | 0.208953609690 | 0 | 0 | | wait/io/file/sql/dbopt | 1.635324865580 | 0 | 65 | | wait/io/file/sql/ERRMSG | 0.103571338579 | 58982 | 0 | | wait/io/file/sql/file_parser | 265.539358388633 | 1450 | 92321 | | wait/io/file/sql/FRM | 9.076319346204 | 233702 | 0 | | wait/io/file/sql/global_ddl_log | 0.042241489515 | 0 | 0 | | wait/io/file/sql/misc | 0.130650339648 | 0 | 0 | | wait/io/file/sql/pid | 0.297200348228 | 0 | 5 | | wait/io/file/mysys/charset | 0.054673913866 | 18316 | 0 | | wait/io/file/mysys/cnf | 0.047869719565 | 56 | 0 | | wait/io/file/myisam/dfile | 8.178050385788 | 53640 | 33796 | | wait/io/file/myisam/kfile | 15.392133934423 | 9778 | 5138 | | wait/io/file/innodb/innodb_data_file | 15.472816837952 | 8929280 | 49152 | | wait/io/file/innodb/innodb_log_file | 9.008877178654 | 69632 | 2560 | +--------------------------------------+------------------+------------------+---------------------+ 14 rows in set (0.03 sec)

Example How much time is spend waiting where?

mysql> select * from ps_helper.wait_classes_global_by_latency; +-------------------+--------------+---------------+-------------+-------------+-------------+ | event_class | total_events | total_latency | min_latency | avg_latency | max_latency | +-------------------+--------------+---------------+-------------+-------------+-------------+ | wait/io/file | 550470 | 46.01 s | 19.44 ns | 83.58 µs | 4.21 s | | wait/io/socket | 228833 | 2.71 s | 0 ps | 11.86 µs | 29.93 ms | | wait/io/table | 64063 | 1.89 s | 99.79 ns | 29.43 µs | 68.07 ms | | wait/lock/table | 76029 | 47.19 ms | 65.45 ns | 620.74 ns | 969.88 µs | | wait/synch/mutex | 635925 | 34.93 ms | 19.44 ns | 54.93 ns | 107.70 µs | | wait/synch/rwlock | 61287 | 7.62 ms | 21.38 ns | 124.37 ns | 34.65 µs | +-------------------+--------------+---------------+-------------+-------------+-------------+

Example Index usage statistics

mysql> select * from schema_index_statistics limit 5\G *************************** 1. row *************************** table_schema: world table_name: city index_name: Name rows_selected: 31 select_latency: 37.45 s rows_inserted: 0 insert_latency: 0 ps rows_updated: 0 update_latency: 0 ps rows_deleted: 0 delete_latency: 0 ps *************************** 2. row *************************** table_schema: world table_name: city index_name: PRIMARY rows_selected: 1 select_latency: 21.05 us rows_inserted: 0 insert_latency: 0 ps rows_updated: 4 update_latency: 262.35 us rows_deleted: 0 delete_latency: 0 ps

Example Statement Analysis

mysql> select * from statement_analysis limit 5\G *************************** 1. row *************************** query: UPDATE `City` SET NAME = ? WHERE NAME = ? full_scan: exec_count: 3 err_count: 0 warn_count: 0 total_latency: 37.44 s max_latency: 37.44 s avg_latency: 12.48 s rows_sent: 0 rows_sent_avg: 0 rows_scanned: 6 digest: 0ab3971f852eb3541dc88efc2c278be0 *************************** 2. row *************************** query: INSERT INTO `City` VALUES (...) full_scan: exec_count: 4079 err_count: 0 warn_count: 0 total_latency: 994.07 ms max_latency: 866.28 us avg_latency: 243.70 us rows_sent: 0!

• Mark Leith http://www.markleith.co.uk/ps_helper/

• Todd Farmerhttp://mysqlblog.fivefarmers.com

More context, examples:

Information Schema

• Similar to Performance Schema, but designed for static, slower changing data.

• Introduced in MySQL 5.0, but dramatically more meta data in MySQL 5.6.

Concept

New I_S Tables (5.6)

INNODB_LOCKS INNODB_TRX INNODB_SYS_DATAFILES

INNODB_LOCK_WAITS INNODB_SYS_TABLESTATS INNODB_CMP

INNODB_METRICS INNODB_CMP_RESET INNODB_CMP_PER_INDEX

INNODB_CMPMEM_RESET INNODB_FT_DELETED INNODB_BUFFER_PAGE_LRU

INNODB_SYS_FOREIGN INNODB_SYS_COLUMNS INNODB_SYS_INDEXES

INNODB_FT_DEFAULT_STOPWORD INNODB_SYS_FIELDS INNODB_CMP_PER_INDEX_RESET

INNODB_BUFFER_PAGE INNODB_CMPMEM INNODB_FT_INDEX_TABLE

INNODB_FT_BEING_DELETED INNODB_SYS_TABLESPACES INNODB_FT_INDEX_CACHE

INNODB_SYS_FOREIGN_COLS INNODB_SYS_TABLES INNODB_BUFFER_POOL_STATS

INNODB_FT_CONFIG

5.6 Example - Estimate Working Set

• The five minute rule: If you load a page into memory, then require it again within 5 minutes it should stay in memory.

• Pages in memory is in information_schema!

mysql> call test.estimate_working_set(10, 30);!

.. lines omitted for brevity .. +----------------------+ | pages_in_working_set | +----------------------+ | 100679 | +----------------------+ 1 row in set (5 min 55.61 sec)

Estimate Working Set (cont.)

• I have only a 128MB innodb_buffer_pool_size. • Example shows I need 100679 pages (16K each)

= 1573MB of memory to be efficient.

The Future

MySQL 5.7

• More Performance Schema • Instrumentation for transactions and memory

allocation. • More Online DDL

• Extend VARCHAR • More EXPLAIN enhancements

• EXPLAIN a running query. • Show cost information.

MySQL 5.7 (cont.)

• More online usability • Online replication filter reconfiguration.

• Improved error log configuration and verbosity. • More annoying bugs fixed.

• Warning when creating duplicate indexes. • Control-C support in the client

PS Example Memory usage per user

mysql> select * from memory_by_user_by_current_bytes; +------+--------------------+-------------------+------------------+-------------------+-----------------+ | user | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated | +------+--------------------+-------------------+-------------------+-------------------+-----------------+ | NULL | 2085 | 10.64 MiB | 5.23 KiB | 8.00 MiB | 30.16 MiB | | root | 1401 | 1.09 MiB | 815 bytes | 334.97 KiB | 42.73 MiB | | mark | 201 | 496.08 KiB | 2.47 KiB | 334.97 KiB | 5.50 MiB | +------+--------------------+-------------------+-------------------+-------------------+-----------------+

Cost Information