This document discusses MySQL 5.6 and its improvements to operational and diagnostic capabilities. Key enhancements include online DDL operations that do not block reads or writes, buffer pool dump and restore for faster startup, import/export of partitioned tables, and transportable tablespaces. Diagnostic tools were improved with EXPLAIN showing more details, the ability to EXPLAIN updates and deletes, optimizer tracing, and the performance schema providing detailed query level instrumentation and monitoring by default.
This document discusses MySQL performance tuning and various MySQL products and features. It provides information on MySQL 5.6 including improved scalability, new InnoDB features for NoSQL access, and an improved optimizer. It also discusses MySQL Enterprise Monitor for performance monitoring, and the Performance Schema for instrumentation and monitoring internal operations.
MySQL 5.7 proposes several changes to improve performance and consistency including:
1. Making replication durable by default by setting sync_binlog and repository options.
2. Deprecating features like INNODB monitor tables and ALTER IGNORE TABLE in favor of newer standards.
3. Simplifying and restricting SQL modes to encourage stricter querying and remove ambiguous options. Explanations for errors and modes will also be improved.
The document discusses proposed changes to MySQL Server 8.0 and replication defaults. Some key areas discussed include changing the default character set to UTF8MB4, turning on the event scheduler by default, increasing some session buffer sizes, enabling security defaults, and enabling replication features like binary logging and GTIDs by default. The document seeks feedback from users on the proposed changes.
MySQL: From Single Instance to Big DataMorgan Tocker
The document discusses various MySQL database architectures for different usage needs, from single server setups to high availability configurations. It begins with traditional single server and web/database tier setups. It then covers high availability options using MySQL replication, shared storage, and MySQL Cluster. Popular topologies include master-slave replication for scaling reads, read-write splitting between master and slaves, and using slaves for reporting queries to improve performance. Considerations like network latency, failure handling, and limitations of read-write splitting are also discussed.
The document outlines changes and new features in MySQL versions 5.7 through upcoming releases. Key points include:
- MySQL 5.7 development follows a milestone release process to stabilize new features before general availability. Four development milestone releases have been completed so far.
- Notable 5.7 features include statement timeouts, change replication without stopping SQL threads, and performance improvements like optimized UNION ALL queries.
- Some existing functionality will change in 5.7, like making replication more durable by default and producing errors for queries with only partial GROUP BY clauses.
- Ongoing efforts include refactoring and improving InnoDB, the optimizer, and other components for better performance and scalability. New features in development
This document provides an overview of MySQL for Linux system administrators. It discusses MySQL architecture including storage engines, memory usage, the MySQL server process, and InnoDB transaction processing. It also covers topics like backups and replication, and the agenda includes performance and capacity planning. The goal is to help system administrators understand and manage MySQL databases.
This document provides an overview and agenda for a presentation on MySQL 5.6 performance tuning and best practices. The presentation covers analyzing MySQL workload and internals, performance improvements in MySQL 5.6 and 5.7, benchmark results, and pending issues. It emphasizes the importance of monitoring systems to understand performance bottlenecks and the need for an iterative process of monitoring, tuning, optimizing, and improving database performance over time.
Upgrade to MySQL 5.7 and latest news planned for MySQL 8Ted Wennmark
The document discusses upgrading to MySQL 5.7 from previous versions. It provides an agenda that covers MySQL 5.7, upgrading to MySQL 5.7, and MySQL 8. It then discusses reasons to upgrade including performance/scalability improvements in MySQL 5.7, new features in 5.7 like JSON support and optimizer improvements, staying on a fully supported release, and security improvements in 5.7. Benchmarks show MySQL 5.7 is up to 6x faster than previous versions on OLTP workloads.
This document discusses query optimization in MySQL. It provides an introduction to how the MySQL query optimizer works to determine the most efficient execution plan for a SQL query. Several examples are shown using the EXPLAIN statement to analyze queries against sample data in the World Schema. Indexes are added and analyzed to demonstrate how they can improve query performance in different scenarios. The document also discusses some general strategies and rules of thumb used by the query optimizer.
The document outlines 10 usability guidelines for MySQL:
1) All features should be possible through SQL for consistency and discoverability.
2) Features, configurations, and errors should be intuitively obvious and discoverable without reading manuals cover-to-cover.
3) Too many similar configuration options without clear use cases can be paralyzing; only add options if use cases are known.
4) New configuration options must allow the effect to be measured through observability.
5) Features should work consistently across contexts for orthogonality.
6) The system should be safe to script against and avoid duplicate processing.
7) Extend functionality to match common use cases.
8) Preserve the ability to
MySQL 5.7 introduced native support for JSON data with a new JSON data type and JSON functions. The JSON type allows efficient storage and access of JSON documents compared to traditional text storage. JSON functions allow querying and manipulating JSON data through operations like extraction, search, and generation of JSON values. Developers now have more flexibility to work with hierarchical and unstructured data directly in MySQL.
MySQL Performance Tuning. Part 1: MySQL Configuration (includes MySQL 5.7)Aurimas Mikalauskas
Is my MySQL server configured properly? Should I run Community MySQL, MariaDB, Percona or WebScaleSQL? How many innodb buffer pool instances should I run? Why should I NOT use the query cache? How do I size the innodb log file size and what IS that innodb log anyway? All answers are inside.
Aurimas Mikalauskas is a former Percona performance consultant and architect currently writing and teaching at speedemy.com. He's been involved with MySQL since 1999, scaling and optimizing MySQL backed systems since 2004 for companies such as BBC, EngineYard, famous social networks and small shops like EstanteVirtual, Pine Cove and hundreds of others.
Additional content mentioned in the presentation can be found here: http://speedemy.com/17
MySQL 5.7 - What's new and How to upgradeAbel Flórez
The document discusses new features in MySQL 5.7, including:
1) Performance improvements such as being 3x faster than MySQL 5.6 for certain workloads based on sysbench benchmarks.
2) Optimizer enhancements like a new cost-based optimizer model for better query performance and resource usage.
3) Additional features like native JSON support with functions to create, search, modify and return JSON values and improved security.
This document provides an overview of MySQL server performance tuning. It discusses laying the foundation for performance tuning by examining the server, OS, network and filesystem. It also covers examining current server settings and status variables, and tuning various aspects of MySQL like InnoDB, MyISAM, queries and session settings. The document aims to provide guidance on areas to optimize to improve MySQL server performance.
MySQL® 5.7 is a great release which has a lot to offer, especially in the development and replication areas. It provides a lot of new optimizer features for developers to take advantage of, a much more powerful GIS function and high performance JSON data type, allowing for a more powerful store for semi-structured data. It also features dramatically improved Performance Schema, Parallel and Multi-Source replication, allowing you to scale much further than ever before, just to give you a taste. In this webinar, we will provide an overview of the most important MySQL 5.7 features.
This webinar will be part of a 3-part series which will include MySQL 5.7 for Developers and MySQL 5.7 for DBAs.
InnoDB Architecture and Performance Optimization, Peter ZaitsevFuenteovejuna
This document provides an overview of the Innodb architecture and performance optimization. It discusses the general architecture including row-based storage, tablespaces, logs, and the buffer pool. It covers topics like indexing, transactions, locking, and multi-versioning concurrency control. Optimization techniques are presented such as tuning memory configuration, disk I/O, and garbage collection parameters. Understanding the internal workings is key to advanced performance tuning of the Innodb storage engine in MySQL.
MySQL client side caching allows caching of query results on the client side using the mysqlnd driver. It is transparent to applications using MySQL extensions like mysqli or PDO. Cached results are stored using pluggable storage handlers like APC, memcache, or local memory. Queries can be cached based on SQL hints or custom logic in a user-defined storage handler. Statistics are collected on cache usage and query performance to analyze effectiveness. This provides an alternative to server-side query caching with potential benefits like reducing network traffic and database load.
The document discusses Oracle's MySQL Cloud Service which provides MySQL as a database service on Oracle Public Cloud. Key features include automated backups, patching, monitoring, elastic scaling, high availability, security features from MySQL Enterprise Edition, and tools for data access, migration and restoration. The service runs MySQL 5.7 Enterprise Edition with an optimized configuration for the cloud environment.
The document discusses MySQL architecture and concepts. It describes the application layer where users interact with the MySQL database. It then explains the logical layer which includes subsystems like the query processor, transaction management, recovery management and storage management that work together to process requests. Key concepts like concurrency control, locks, transactions, storage engines and InnoDB/MyISAM are also overviewed.
This document provides an overview and instructions for installing and using the MySQL database system. It describes MySQL's client-server architecture, how to connect to the MySQL server using the command line client, and provides examples of common SQL commands for creating databases and tables, inserting, selecting, updating, and deleting rows of data. It also introduces some basic SQL functions and provides SQL scripts as examples to create tables and insert data.
This document provides information on using EXPLAIN to troubleshoot MySQL performance issues. EXPLAIN shows how MySQL executes SQL queries, including which indexes and joins it uses. The output includes information on the query type, access method, filtered rows, and extra details to help identify inefficient queries or indexes.
Recovery of lost or corrupted inno db tables(mysql uc 2010)Aleksandr Kuzminsky
The document discusses recovery of lost or corrupted InnoDB tables in MySQL. It provides an overview of how MySQL stores data in InnoDB, describes typical failure scenarios that can cause data loss or corruption, and introduces an InnoDB recovery tool that can be used to parse InnoDB tablespace pages and apply constraints to recover records from corrupted pages.
Inno Db Internals Inno Db File Formats And Source Code StructureMySQLConference
The document discusses InnoDB, the transactional storage engine for MySQL. It provides an overview of InnoDB's goals, architecture, on-disk file formats, source code structure and more. Specifically, it covers InnoDB's focus on transaction processing, performance and reliability. It describes InnoDB's key components like tablespaces, pages, rows, indexes and logging. The presentation aims to explain InnoDB's internals and design considerations to achieve durability, performance, efficiency and compatibility.
The document provides an overview of diagnosing performance and other issues with the InnoDB storage engine in MySQL. It discusses various sources of information for troubleshooting like SHOW ENGINE INNODB STATUS and OS tools. Common problems covered include the InnoDB data dictionary getting out of sync, crashes/segmentation faults, locking issues, and performance problems related to disk I/O, buffer pool hit rates, high CPU usage from row operations or thread thrashing. Interpreting diagnostic output and potential solutions are also outlined.
This document summarizes a presentation by Raymond Siebert about 1&1's use of MySQL. It discusses 1&1's history and growth as an internet service provider. It describes 1&1's shift to open source solutions like Linux, Apache, PHP, and MySQL due to cost efficiency and flexibility. 1&1 operates around 700 internal MySQL installations for customer products, ordering processes, billing, and more. These installations utilize high availability configurations and geo-redundancy. MySQL has helped 1&1 scale to support its fast growth while maintaining availability, though the presenter notes MySQL has limitations for big data and business intelligence workloads.
MySQL 5.6, news in 5.7 and our HA optionsTed Wennmark
Join us for this free MySQL Tech Tour to learn straight from the source how you can benefit from Oracle’s latest MySQL innovations. Our technical experts will help you understand how to take advantage of the wide range of new features and enhancements available in MySQL Fabric, MySQL 5.6, MySQL Cluster and other MySQL solutions. They will share tips & tricks to help you get the most of your database. You will also discover what’s coming next in MySQL 5.7.
The document discusses MySQL 5.6 replication features including:
- Multi-threaded replication which allows parallel application of transactions to different databases for increased slave throughput.
- Binary log group commit which increases master performance by committing multiple transactions as a group to the binary log.
- Optimized row-based replication which reduces binary log size and network bandwidth by only replicating changed row elements.
- Global transaction identifiers which simplify tracking replication across clusters and identifying the most up-to-date slave for failover.
- Crash-safe slaves which store replication metadata in tables, allowing automatic recovery of slaves and binary logs after failures.
The document summarizes a presentation on the internals of InnoDB file formats and source code structure. The presentation covers the goals of InnoDB being optimized for online transaction processing (OLTP) with performance, reliability, and scalability. It describes the InnoDB architecture, on-disk file formats including tablespaces, pages, rows, and indexes. It also discusses the source code structure.
Linux con europe_2014_full_system_rollback_btrfs_snapper_0sprdd
This document discusses using Btrfs and Snapper to enable full system rollbacks. It describes how snapshots are used to capture the state of the system at different points in time. These snapshots can then be used to rollback the entire system to a previous known good state, reducing downtime from system issues or configuration changes. The key capabilities of Snapshots include automatically capturing changes, displaying differences between snapshots, and rolling back to previous states. Integration with tools like YaST allow visualizing and undoing changes at the file level, while full system rollback supports reverting the entire operating system, including the kernel.
The care and feeding of a MySQL databaseDave Stokes
The document provides an overview of caring for and maintaining a MySQL database server for Linux administrators. It discusses that database servers have different needs than other servers and hardware is critical. It also summarizes setting up MySQL, monitoring operations, backups, replication, and tuning for performance.
MySQL Troubleshooting with the Performance SchemaSveta Smirnova
This document discusses using the Performance Schema in MySQL to troubleshoot performance issues. It provides an overview of the Performance Schema and what information it collects. It then discusses how to use specific Performance Schema tables like events_statements_history_long, events_stages_history_long, and others to identify statements that examine too many rows, issues with index usage, and which internal operations are taking a long time. The document provides examples of queries to run and what to look for in the Performance Schema output to help troubleshoot and optimize SQL statements.
MySQL Performance - SydPHP October 2011Graham Weldon
A talk on optimisations around MySQL on the server side, and through the use of PHP extensions to reduce disk writes to provide for more IO access for MySQL. This was presented at SydPHP in October 2011
Performance Schema in MySQL (Danil Zburivsky)Ontico
The document discusses the Performance Schema feature in MySQL 5.5, which instruments and collects data about internal operations to help identify performance bottlenecks. It is implemented as a storage engine that collects data about events like query execution steps, locks, I/O, and threads into tables that provide visibility into where the server spends its time. This helps address the lack of good instrumentation previously available in MySQL for performance tuning.
This document provides 10 tips for optimizing MySQL database performance at the operating system level. The tips include using SSDs instead of HDDs for faster I/O, allocating large amounts of memory, avoiding swap space, keeping the MySQL version up to date, using file systems without barriers, configuring RAID cards for write-back caching, and leveraging huge pages. Overall, the tips aim to improve I/O speeds and memory usage to enhance MySQL query processing performance.
Going thru the era of IoT that involves lots more and much bigger data, we need a faster database. MySQL 5.7 gives you 3x speed of its predecessor and able to reach 1.6m qps on our select benchmark.
MySQL 5.7 provides significant performance improvements and new features over previous versions. Benchmark tests showed it was 3x faster than MySQL 5.6 for SQL point selects and connection requests, and 1.5x faster for OLTP read/write workloads. New features include enhanced InnoDB storage engine capabilities, improved replication, JSON data type support, and increased security.
This document provides a summary of new features and enhancements in MySQL 5.6, including improved performance, scalability, availability, and usability. Key highlights include optimizations to the query optimizer, enhanced instrumentation via the performance schema, improvements to InnoDB and replication, and new utilities to help administer replication deployments. Oracle aims to release development milestone versions of MySQL frequently to get new features in users' hands early.
Technical Introduction to PostgreSQL and PPASAshnikbiz
Let's take a look at:
PostgreSQL and buzz it has created
Architecture
Oracle Compatibility
Performance Feature
Security Features
High Availability Features
DBA Tools
User Stories
What’s coming up in v9.3
How to start adopting
Sql server 2016 it just runs faster sql bits 2017 editionBob Ward
SQL Server 2016 includes several performance improvements that help it run faster than previous versions:
1. Automatic Soft NUMA partitions workloads across NUMA nodes when there are more than 8 CPUs per node to avoid bottlenecks.
2. Dynamic memory objects are now partitioned by CPU to avoid contention on global memory objects.
3. Redo operations can now be parallelized across multiple tasks to improve performance during database recovery.
Oracle Database 12c includes over 500 new features. Some key new features include:
- Oracle Database 12c Express (EM Express) which replaces Database Control and has less features than Database Control but does not require Java or an app server.
- New online capabilities like online DDL operations with no DDL locking, online move of partitions with no impact to queries, and online statistics gathering for bulk loads.
- Adaptive SQL Plan Management which allows the optimizer to select a more optimal plan at execution time based on current statistics.
- Multitenant architecture which allows consolidation of multiple databases into one container database with pluggable databases.
Based on the popular blog series, join me in taking a deep dive and a behind the scenes look at how SQL Server 2016 “It Just Runs Faster”, focused on scalability and performance enhancements. This talk will discuss the improvements, not only for awareness, but expose design and internal change details. The beauty behind ‘It Just Runs Faster’ is your ability to just upgrade, in place, and take advantage without lengthy and costly application or infrastructure changes. If you are looking at why SQL Server 2016 makes sense for your business you won’t want to miss this session.
The document provides information about new features and performance improvements in MySQL 5.7. Key points include: MySQL 5.7 is generally available (GA); it offers enhanced InnoDB storage engine performance, replication improvements, a new optimizer cost model for better query performance, and improved security features like AES 256 encryption being default. Benchmark results show MySQL 5.7 performing 3x faster than 5.6 and up to 6x faster than 5.5 for various workloads.
Ashnik EnterpriseDB PostgreSQL - A real alternative to Oracle Ashnikbiz
A Technical introduction to PostgreSQL and Postgres Plus -
Enterprise Class PostgreSQL Database from EDB - You have a ‘Real’ alternative to Oracle and other conventional proprietary Databases
Powering GIS Application with PostgreSQL and Postgres Plus Ashnikbiz
This document provides an overview of Postgres Plus Advanced Server and its features. It begins with introductions to PostgreSQL and PostGIS. It then discusses Postgres Plus Advanced Server's Oracle compatibility, performance enhancements, security features, high availability options, database administration tools, and migration toolkit. The document also provides information on scaling Postgres Plus Advanced Server through partitioning and infinite cache technologies. It concludes with summaries of the replication capabilities of Postgres Plus Advanced Server.
Mysql User Camp : 20th June - Mysql New FeaturesTarique Saleem
This document discusses new features in MySQL 5.7 and NoSQL support in MySQL. Some key points:
- MySQL 5.7 includes improvements to InnoDB for better transactional performance and scalability, as well as enhancements to replication, security, and other areas.
- NoSQL support allows direct access to MySQL data via Memcached APIs for simpler and faster key-value access while maintaining ACID guarantees.
- Benchmarks show NoSQL inserts into MySQL can be up to 9x faster than SQL inserts, and MySQL 5.7 can achieve over 1 million queries per second.
Mysql User Camp : 20-June-14 : Mysql New features and NoSQL SupportMysql User Camp
This slide was presented at Mysql User Camp Event on 20-June-14 at Oracle bangalore. This presentation gives a good insight about New Features in Mysql 5.7 DMR 4 and Nosql Support in Mysql.
Ceph Community Talk on High-Performance Solid Sate Ceph Ceph Community
The document summarizes a presentation given by representatives from various companies on optimizing Ceph for high-performance solid state drives. It discusses testing a real workload on a Ceph cluster with 50 SSD nodes that achieved over 280,000 read and write IOPS. Areas for further optimization were identified, such as reducing latency spikes and improving single-threaded performance. Various companies then described their contributions to Ceph performance, such as Intel providing hardware for testing and Samsung discussing SSD interface improvements.
Oracle plans to invest in MySQL to improve performance and scalability, simplify management, and provide better integration with Oracle products. Key focuses for MySQL include the web, embedded systems, telecom, and the LAMP stack. MySQL 5.5 offers major performance improvements such as the InnoDB storage engine becoming default and multiple buffer pools and rollback segments for increased scalability.
MySQL 5.7 provides significant performance and scalability improvements over previous versions. It offers a 3x speed increase on benchmarks compared to MySQL 5.6. New features include enhanced InnoDB functionality for faster operations, replication improvements, a new optimizer cost model, and native JSON support. Additional security enhancements in MySQL 5.7 aim to securely initialize and manage installations.
The document discusses Oracle E-Business Suite technology priorities and guidance. It focuses on leveraging the latest Oracle and industry technologies to offer ease of use, facilitate identity management, simplify integration, and deliver business intelligence while reducing cost of ownership. Specific technologies highlighted include the Oracle database, Fusion Middleware, and leveraging SOA.
The document discusses performance improvements and new features in MySQL 5.7. Key points include:
- MySQL 5.7 shows significant performance gains over previous versions, with benchmarks showing 3x faster query performance and 82% faster connection throughput.
- New features include enhanced replication, a new optimizer cost model, performance schema improvements, and native JSON support.
- The optimizer and parser were refactored for improved maintainability, readability and stability. A new cost-based optimizer model provides better performance and tunability.
Similar to MySQL 5.6 - Operations and Diagnostics Improvements (20)
This document discusses Spirit, an online schema change utility for MySQL 8.0. It begins by covering the state of DDL operations in MySQL and how Spirit works to perform schema changes without blocking reads or writes. It then discusses optimizations Spirit uses and features like checkpointing. Finally, it outlines some feature requests to make more operations instant or inplace in MySQL to reduce the need for Spirit in many cases.
This document summarizes the author's first 90 days of experience with Vitess, an open source database proxy. It provides an overview of Vitess, including that it sits between applications and MySQL to provide routing, query consolidation, and other features. It also discusses Vitess terminology, questions about MySQL compatibility, consistency models, and other quirks and features. The document concludes with a discussion of the best use cases for Vitess and areas where it could be improved.
TiDB is a distributed, horizontally scalable SQL database that is compatible with MySQL. It separates processing and storage into independent scalable components - the TiDB SQL layer and the TiKV storage foundation. TiDB uses a multi-version concurrency control approach based on Google's Spanner/F1 databases. It has been used in large-scale production deployments containing over 30 TB of data per day. Benchmarks show it can scale linearly with additional nodes. While aiming to be compatible with MySQL features, it does not support some like stored procedures and triggers.
Introducing TiDB - Percona Live FrankfurtMorgan Tocker
TiDB is an open-source distributed SQL database developed by PingCAP that is compatible with MySQL. It provides horizontal scalability, high availability, and consistent distributed transactions. Mobike, which has 200 million users and 9 million bikes, uses TiDB to handle over 30 TB of data per day. While TiDB aims to be compatible with MySQL, some features like stored procedures work differently or are still in development.
TiDB Introduction - Boston MySQL Meetup GroupMorgan Tocker
This document provides an overview and summary of TiDB, an open-source distributed SQL database inspired by Google's Spanner and F1. The summary includes:
1. TiDB is a distributed SQL database that is compatible with MySQL and provides horizontal scalability, high availability, and strong consistency with a hybrid OLTP/OLAP architecture.
2. It consists of TiDB, TiKV, and PD components where TiDB is the frontend MySQL compatible database layer, TiKV is the distributed key-value storage layer, and PD is the placement driver for metadata management.
3. TiDB is being used by over 300 companies including Mobike for applications such as real-time analytics, high concurrency
TiDB Introduction - San Francisco MySQL MeetupMorgan Tocker
This document provides an overview and agenda for introducing TiDB, an open source distributed SQL database inspired by Google's Spanner and F1 projects. The summary includes:
- TiDB is a distributed SQL database that is compatible with MySQL and provides horizontal scalability, high availability, and strong consistency with its key components TiDB, TiKV, and PD.
- The agenda covers an introduction to PingCAP, the company behind TiDB, a technical walkthrough of the TiDB architecture, and a use case example with Mobike, one of TiDB's customers with over 200 million users.
- A live demo of running TiDB on Google Kubernetes Engine is also included on the agenda along with discussions of
This document provides an overview and summary of TiDB, an open-source distributed SQL database compatible with MySQL. It discusses TiDB's architecture which includes TiDB for the SQL layer, TiKV for storage, and PD for placement driving. TiDB provides features like horizontal scalability, distributed transactions, and high availability. Example use cases are also presented, like Mobike's use of TiDB for locking/unlocking bikes and real-time analytics of bike usage data across 200 cities in China.
The document is an introduction to the MySQL 8.0 optimizer guide. It includes a safe harbor statement noting that the guide outlines Oracle's general product direction but not commitments. The agenda lists 25 topics to be covered related to query optimization, diagnostic commands, examples from the "World Schema" sample database, and a companion website with more details.
The document discusses locking and concurrency control in databases, demonstrating how table locks, row locks, and multi-version concurrency control work through examples of a database being backed up while concurrent changes are made. It shows how different locking strategies, like those used in MyISAM and InnoDB, allow for concurrent access to data while maintaining consistency and isolation. A live demo then highlights deadlocks and lock waits that can occur with concurrent access and how they are handled.
MySQL 5.6 - Operations and Diagnostics Improvements
1. <Insert Picture Here>
MySQL 5.6 - Online Operations and Improved Diagnostics
Morgan Tocker, MySQL Community Manager
http://www.tocker.ca/
2. 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.
3. 4 Years of MySQL Innovation
MySQL Cluster 7.3
MySQL Migration Wizard
MySQL Workbench 6.0
MySQL 5.6
MySQL 5.5
Windows installer & Tools MySQL 5.7 M y S Q L
Cluster
MySQL
MySQL Enterprise Monitor 2.3 & e r
M a n a g 3.0
Applier for
Hadoop
MySQL Enterprise Backup
Security
MySQL Utilities
MySQL Workbench 5.2 & 6.0
Scalability
MySQL Cluster 7.2
HA
MySQL Enterprise
MySQL Cluster 7.1
O r a c l e C e r t i f i c a t i o n s Audit
4. Agenda for Today
• Quick Intro to MySQL 5.6
• Operational Enhancements
• Diagnostic Enhancements
6. The Path to MySQL 5.6
DMR1-
DMR2
DMR3
DMR4
DMR5
RC
April 2011
Oct 2011
Dec 2011
Aug 2012
Aug 2012
Nov 2012
Optimizer:
Optimizer:
Optimizer:
Optimizer:
InnoDB:
MRR, ICP, File
Sort
BKA, New
EXPLAIN, Traces
Sub-Queries
JSON EXPLAIN,
Sub-Queries
TT, Online DDL,
Memcached API
New Server
Defaults
InnoDB:
InnoDB:
Replication:
Replication:
Split Kernel Mutex,
MT Purge
Dump/Restore
Buffer Pool
GTIDs
Binary Log Group
Commit
Replication:
More P_S
!
Crash-Safe, MultiThread Slave,
Checksums
Memcached API
New P_S
Partitioning
Improvements
!
!
InnoDB:
Full Text Index,
Read-Only
Optimizations
Condition
Handling
Fractional
Seconds
!
TIMESTAMP &
DATESTAMP
More P_S
!
Password Mgmt
More Partitioning
!
More Partitioning
More GTID, TT
7. By Lines of Code
• “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
8. By Bugs Fixed
• 1991 Bugs Fixed in 5.6
• 3763 Bugs Fixed in Total Since MySQL 5.5 GA
9. 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
10. By Actual Features / Enhancements
Scalable 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
12. InnoDB Online DDL
The beginning
All DDL requires the
table to be recreated,
along with all
indexes.
MySQL 5.1 Nov 2008
MySQL 5.5 Dec 2010
MySQL 5.6 Jan 2013
Fast ALTER
TABLE added.
Changes to
ENUM/SET
online.
Fast index
creation
added. Adding
indexes
changes to
SHARED lock,
dropping
indexes
immediate*
Online DDL.
Many changes
do not block
readers or
writers.
13. In-Place?
Copies Table?
Allows Concurrent
DML?
Allows Concurrent
Query?
CREATE INDEX,ADD INDEX
Yes*
No*
Yes
Yes
ADD FULLTEXT INDEX
MySQL 5.6 Online DDL
Operation
Yes
No*
No
Yes
DROP INDEX
Yes
No
Yes
Yes
Set default value for a column
Yes
No
Yes
Yes
Change auto-increment value for a column
Yes
No
Yes
Yes
Add a foreign key constraint
Yes*
No*
Yes
Yes
Drop a foreign key constraint
Yes
No
Yes
Yes
Rename a column
Yes*
No*
Yes*
Yes
Add a column
Yes
Yes
Yes*
Yes
Drop a column
Yes
Yes
Yes
Yes
Reorder columns
Yes
Yes
Yes
Yes
Change ROW_FORMAT property
Yes
Yes
Yes
Yes
Change KEY_BLOCK_SIZE property
Yes
Yes
Yes
Yes
Make column NULL
Yes
Yes
Yes
Yes
Make column NOT NULL
Yes*
Yes
Yes
Yes
Change data type of column
No
Yes
No
Yes
Add primary key
Yes*
Yes
Yes
Yes
Drop primary key and add another
Yes
Yes
Yes
Yes
Drop primary key
No
Yes
No
Yes
Convert character set
No
Yes
No
Yes
Specify character set
No
Yes
No
Yes
Rebuild with FORCE option
No
Yes
No
Yes
Via http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
14. InnoDB Online DDL (cont.)
• Also supports additional syntax LOCK=NONE and
ALGORITHM=INPLACE;
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.
15. Buffer Pool Dump and Restore
• 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
16. Import/Export Partitioned Tables
• Very helpful feature for time-series data.
• Swap partitions with regular tables:
ALTER TABLE pt
EXCHANGE PARTITION p
WITH TABLE t;
17. Transportable Tablespaces
• InnoDB data can be copied to a different server
without using mysqldump:
/* source */
FLUSH TABLES tableName FOR EXPORT;
/* destination */
ALTER TABLE tableName IMPORT TABLESPACE;
18. Important Bugs Fixed
• InnoDB not complaining when innodb_log_file_size
has changed between restarts:
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
19. Replication with GTIDs
• Binary log coordinates can now be global - rather
than per instance.
• Which means failover/topology changes are now
much easier.
20. Multi-threaded slaves!
• On a per schema basis, replication supports parallel
apply on slaves.
• In 5.7 it will be intra-schema parallel.
23. EXPLAIN UPDATE/DELETE statements
• Previously only SELECT statements could be
explained.
• Required one to rewrite UPDATE/DELETE
statement to a SELECT in order to EXPLAIN.
27. “The OLD way”
• 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.
28. Old way is not always instrumented to
a useable detail…
• 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.
29. Not Query-Oriented
• SHOW STATUS shows created temporary tables as
a counter, but:
• Can’t find which query created.
30. Difficult for Tooling
• InnoDB status contains a lot of magic numbers.
Tools like cacti-templates require light weight parsers
just to be able to read the information:
!
------------
!
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!
31. The New Way - Performance Schema
• Series of views which expose internal server
performance data with timing information.
• Similar to “Oracle Wait Interface”.
32. 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.
33. Performance Schema in MySQL 5.5
• 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.
34. Performance Schema in MySQL 5.6
•
•
•
•
Network IO, Table IO, Stages of execution time.
Brings number of instruments up to 545.
Performance_schema is now 52 tables.
Enabled by default.
36. Example
How Idle are your connections?
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)
37. 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)
38. Example
More detailed PROCESSLIST (version 2.0)
mysql> select * from processlist_full where conn_id is not nullG
...
*************************** 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
39. 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)!
WHERE
ess.EVENT_NAME = 'statement/com/Quit'
AND (a.TOTAL_CONNECTIONS - a.CURRENT_CONNECTIONS) > ess.COUNT_STAR;
+------+-----------+------------+----------------+
| USER | HOST
| not_closed | pct_not_closed |
+------+-----------+------------+----------------+
| root | localhost |
4 |
44.4444 |
| ODBC | localhost |
1 |
100.0000 |
+------+-----------+------------+----------------+!
2 rows in set (0.00 sec)
40. Example
How much time could I save in CI by using tmpfs?
mysql> SELECT
`a`.`EVENT_NAME`,
`a`.`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`.`events_waits_summary_global_by_event_name` `a`
INNER JOIN `performance_schema`.`file_summary_by_event_name` `b` USING (event_name)
WHERE
`a`.`EVENT_NAME` like 'wait/io/file/%'
AND `a`.`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)
41. 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
|
+-------------------+--------------+---------------+-------------+-------------+-------------+
42. Example
Index usage statistics
mysql> select * from schema_index_statistics limit 5G
*************************** 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
43. Example
Statement Analysis
mysql> select * from statement_analysis limit 5G
*************************** 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!
44. More context, examples:
• Mark Leith
http://www.markleith.co.uk/ps_helper/
• Todd Farmer
http://mysqlblog.fivefarmers.com
46. Concept
• 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.
48. 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!
49. 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.
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)
51. 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.
52. 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
56. By Actual Features / Enhancements
Scalable 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