MySQL update for Los Angeles MySQL Users Group

50 %
50 %
Information about MySQL update for Los Angeles MySQL Users Group
Technology

Published on February 20, 2014

Author: davidmstokes

Source: slideshare.net

Description

MySQL 5.7 presentation for LA MySQL Users Group 20 Feb 2014

MySQL 5.7 Preview for LA MySQL Users Group ➔ ➔ ➔ Dave Stokes MySQL Community Manager David.Stokes@oracle.com 1Copyright © 2013, Oracle and/or its affiliates. All rights reserved. @Stoker

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 decision. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 2Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

MySQL Manual 5.7 • • • Performance Improvements MDL, Transactions & Memory Instrumentation with Performance Schema Cleans up and improves architecture 3Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

But 5.7 will BREAK some things • • Yes, we KNOW it is painful! We are trying to be • As carefule as possible • Provide as much notice as possible • Let you know why we made the change 4Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Things to do to help the change • • • Change the default to use desired behavior Issue warnings about feature deprecations Remove features We are doing all three 5Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proposal: enable sql mode to ONLY_FULL_GROUP • • Default behavior for other databases In 5.6 and lower, MySQL was guessing what to use for non-grouped fields. – New ANY_VALUE() SQL Function to allow 'non-deterministic' statements so things can work 'old way' 6Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Replication • • 5.6 provided more durable replication with group commit 5.7 proposal – make durable the default – sync_binlog =1 – Master-info-repository = TABLE – Relay-log-infor-repository = TABLE 7Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proposal • Deprecate SHOW ENGINE INNODB MUTEX • • Overlaps with Performance Schema Deprecate InnoDB Monitor tables – Developed long before Performance Schema 8Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proposal – make STRICT the default SQL Mode • • Lot of criticism that MySQL is too permissive, truncating out-of-range, zero dates, etc. Roll ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE into STRICT Mode 9Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Proposal • • Deprecate EXPLAIN PARTITIONS Deprecate EXPLAIN EXTENDED 10Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Alter Ignore Table • Useful for adding PRIMAY/UNIQUE keys on tables with duplicate keys • Will silently drop rows for you • Deprecated 5.7.17 • Removed in DMR4 • Was a MySQL-only extension 11Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Query Cache ● Defaults to DISABLED in 5.6 ● Plans for other options in later releases 12Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Deprecate NULL synonym N • • Anyone using? We DO want feedback!! For removal in 5.7 or 5.8 13Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

FEDERATE & MERGE Storage Engines • • • We recommend multi-source replication over Federated We want your feedback if you are still using Federated!! Similar to Partitioning but MyISAM ONLY • Major limitations with Partition Exchange • Anyone using? 14Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Slide to check if audience is still awake 15Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Driving Innovation AND Quality 191 Worklogs for 5.6 • 191 Bugs fixed in 5.6 • 3763 Bugs in total since 5.5 GA • 911 New MTR tests in MySQL 5.6 • Tripled QA team – 400 man/years experience • 16Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

New Yum Repositories --Now with 5.7 DRMs ● Simple and convenient way to install and update MySQL to the latest software packages using Yum – Server – Workbench – Connectors 17Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Benchmarks ● Sysbench Point Select – – 95% faster than 5.6, ~32 concurrent user sessions – ● 500,000 Queries Per Second, ~ 64 concurrent user sessions 172% faster than 5.5 InnoDB Memached – 1,150,000 QPS, ~128 concurrent user sessions – 550% faster than 5.6 18Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

MySQL Database Development Priorities • Optimized for Web, Cloud-based, Embedded use cases • Simplified, Pluggable architecture • • • Maintainability, more extensible More NoSQL options (HTTP, JSON, JavaScript, etc.) Refactoring • • • Data Dictionary in InnoDB Optimizer/Parser/Protocol InnoDB • • • Optimized for SSD GIS Easy HA, Replication and Sharding → Fabric 19Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Connections Per Second ● 5.5.23 ~ 19k without P_S and ~18k with ● 5.6.15 ~ 24K and 19.5k ● 5.7.2 ~44k and ~18k ● 5.7.3 ~45k and ~44k ● Thanks to help from Facebook!! 20Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Optimizer ● Improved JSON based explain – VISUAL EXPLAIN – Better traces ● Improved “IN” performance ● Non sorted fields in sort buffer compacted, reduces disk sorts 21Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Performance_schema ● New summary tables – Easier place to start – Metadata Lock Instrumentation 22Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

InnoDB ● Online rename index, online change to varchars ● Innochecksums (in 5.6) for calculated/reported → report ● 5.7 – Selectable algorithm (innodb/crc32/none) – Force rewrite of checksum (even if invalid) – Table spaces > 3GB supported – Specify max page count mismatch before terminating – Supports multiple table spaced, files per tablespace 23Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

InnoDB temp tables ● New separate table space for temp tables ● Better create/drop – 11x faster ● 2-4x faster INSERT/DELETE/UPDATE ● Optimized DML operations ● Undo logs in temp table spces 24Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Replication ● Higher slave throughput, parallel threads on same schema ● Enhanced semi-sync, wait for N slaves ● Performance_Schema ● Added SSL for mysqlbinlog ● Dynamically change replication filers – CHANGE REPLICATION FILTER – No need to start/sop server to make changes 25Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Triggers ● Multiple Triggers per table ● Set execution order ● Check column constraints at end of trigger execution 26Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Error logging ● Three levels – Errors Only – Errors + Warnings – Errors + Warnings + Notes (default) ● --log-error-verbosity = 1 ● SET GLOBAL log_error_verbosity=1; ● --log_timestamps = SYSTEM UTC (default) 27Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Learn More ✗ mysql.com ✗ MySQL Products, Editions, Training, Consulting Customer use cases and success stories dev.mysql.com ✗ Downloads, Documentation ✗ Forums PlanetMySQL eDelivery.oracle.com ✗ ✗ ✗ ✗ ✗ Download and evaluate all MySQL products 28Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

New MySQL 5.6 Training Learn about the world’s most popular open-source database Learn MySQL From Oracle • • • • • • • Expert-led training to help you install, configure, and administer MySQL 5.6. Extensive hands-on practices guide you through each concept Explore real-world problems and discover best practices as you work with the tools and techniques used by professional MySQL database administrators Content developed in collaboration with product engineering. Available in traditional or virtual classroom as well as self-study formats. Custom training solutions to match your organization’s specific business needs Backed by Oracle University’s 100% Satisfaction Program oracle.com/education/mysql 29Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

MySQL Connect ➔ September, 4-days at same time as OOW ➔ San Francisco Union Square Hotel? ➔ Learn from the best ➔ MySQL Engineers ➔ Customers ➔ Tutorials on Advanced Subjects ➔ Passes for User Groups? ➔ How do we discount for UG??? 30Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

MySQL 5.7 David.Stokes@Oracle.com @stoker slideshare.net/davestokes 31Copyright © 2013, Oracle and/or its affiliates. All rights reserved.

Add a comment

Related presentations

Related pages

MySQL

MySQL's official homepage by TCX DataKonsult AB.
Read more

The Los Angeles MySQL Meetup Group (Los Angeles, CA) - Meetup

The Los Angeles MySQL Meetup Group. Home; Members; Sponsors Photos; Pages Discussions Message Board; ... Welcome, Los Angeles MySQL Users! Upcoming 7; Past ...
Read more

MySQL :: MySQL 5.7 Reference Manual :: 14.2.11 UPDATE Syntax

SHOW CREATE USER Syntax. ... UPDATE Groups LEFT JOIN (SELECT GroupId, MIN ... MySQL uses Watcom (Oracle) syntax for UPDATE, ...
Read more

MySQL

Adobe vertraut auf MySQL um gestalterische Arbeiten produktiver ausführen zu können; Weitere ...
Read more

mysql | Drupal Groups

2011-10-31 09:30 - 17:30 America/Los_Angeles. Organizers: christefano. ... Apache, MySQL, ... User group meeting
Read more

Los Angeles MySQL Users Group and Southernalifornia Linux ...

One Response to Los Angeles MySQL Users Group and Southernalifornia Linux Expo SCaLE 12x. Veronika Zaitseva says: June 6, 2014 at 2:52 pm.
Read more

MySQL :: MySQL Community Downloads

MySQL SUSE Repository The MySQL repository for SUSE Linux provides a simple and convenient way to install and update MySQL products with ... MySQL Cluster ...
Read more

LA Drupal [Los Angeles Drupal] | Drupal Groups

LA Drupal is the hub for Drupal users in the Los Angeles and surrounding areas to network and ... View all Los Angeles Drupal videos. ... User group meeting
Read more

www.oracle.com

Los Angeles Java User Group: Los Angeles JUG: http://www.lajug.org/ Los Angeles: CA: United Stat: Java: NA: ... MySQL: NA: PackerLand User Group :
Read more

MySQL :: MySQL Downloads

MySQL Enterprise Edition (commercial) ... Customer Download » (Select Patches & Updates Tab, Product Search) Trial Download ...
Read more