Deep Dive on MySQL Databases on Amazon RDS

近年来,MySQL已经成为新应用程序开发和迁移的首选数据库,从价格过高、限制性的商业数据库迁移。我们将概述AWS上提供的MySQL和Mariadb选项,并深入研究Amazon Relational Database Service(RDS),一个完全管理的MySQL服务,以及Amazon Aurora,一个性能高达5倍的MySQL兼容数据库和许多其他创新。

展开查看详情

1.Deep Dive on MySQL Databases on Amazon RDS Chayan Biswas Sr. Product Manager Amazon RDS

2.Amazon RDS is . . . Cloud native engine Open source engines Commercial engines RDS platform • Automatic fail-over • Isolation & security • Advanced monitoring • Backup & recovery • Industry compliance • Routine maintenance • X-region replication • Automated patching • Push-button scaling © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

3.Amazon RDS for open source MySQL engines are . . . Cloud native engine Open source engines Commercial engines RDS platform • Automatic fail-over • Isolation & security • Advanced monitoring • Backup & recovery • Industry compliance • Routine maintenance • X-region replication • Automated patching • Push-button scaling © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

4.Agenda Why run MySQL Why run managed MySQL on Amazon RDS Migrating to RDS Tips and tricks from one of the world’s largest MySQL operators © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

5.© 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

6. 1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

7. 1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

8.MySQL is the world’s most popular database All respondents © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Source: Stack Overflow Developer Survey Results 2018 ( https://insights.stackoverflow.com/survey/2018/#technology )

9.MySQL is the world’s most popular database Professional Developers © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Source: Stack Overflow Developer Survey Results 2018 ( https://insights.stackoverflow.com/survey/2018/#technology )

10.“Most popular” buys you . . . Highly exercised, stable code Large ecosystem of ISVs, Tools, Implementation and Support Partners Large community of users and community-driven resources and a larger DBA talent pool © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

11. 1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

12.MySQL 8.0 highlights - FUNCTIONALITY • Common Table Expressions • Window functions • JSON improvements • 5108 Spatial Reference Systems • utf8mb4 © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

13.Common Table Expressions (CTEs; WITH statement) Common Table Expressions • Is more readable WITH derived AS (subquery) SELECT ... • Can be referenced more than once FROM derived, t1 ... • Can be chained vs. • Has better performance Derived Tables SELECT ... • Can be recursive FROM (subquery) AS derived, t1 ... © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

14. JSON improvements • JSON_TABLE(): accepts JSON, returns relational table • ->> (inline path) operator: Equivalent to JSON_UNQUOTE() + JSON_EXTRACT() • JSON aggregation functions: JSON_ARRAYAGG() and JSON_OBJECTAGG() • JSON_MERGE_PATCH(): accepts two JSON objects, merges them into one • Range support in XPath expressions: for example $[1 to 5], $[last] • Utility functions: JSON_PRETTY(), JSON_STORAGE_SIZE(), JSON_STORAGE_FREE() © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

15.MySQL 8.0 highlights - AVAILABILITY • Instant ADD COLUMN • Unified, transactional data dictionary • Crash-safe, atomic DDL © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

16.Instant ADD COLUMN ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT; • Only metadata changes are made in the data dictionary • No need to acquire metadata lock during SE changes and we don’t touch the data of the table • Currently supports the following operations (with some limitations*): • Change index option • Rename table (in ALTER way) • SET/DROP DEFAULT • MODIFY COLUMN • Add/drop virtual columns • Add columns (non-generated) – also called instant ADD COLUMN • Fails fast for operations that do not support INSTANT algorithm © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

17.Unified, transactional metadata dictionary Benefits • Simplicity • Centralized, uniform data dictionary • Removal of file-based metadata storage • Uniform and centralized caching for dictionary objects • Transactional, crash-safe storage of dictionary data • Simpler, improved implementation for INFORMATION_SCHEMA tables • Atomic DDL © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

18.MySQL 8.0 highlights - PERFORMANCE • 2x-5x higher performance versus MySQL 5.7 • Hot-spot management • Descending indexes • Invisible indexes • Improved optimizer cost model • Resource Groups • Improved replication © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

19.2x-5x higher performance versus MySQL 5.7 Multi-AZ configuration, r4.16xlarge, 30K IOPS, 1TB database Transactions per second, with varying number of threads 6000 5000 4000 3000 2000 1000 0 2 2 2 2 2 2 2 2 2 2 2 2 4 4 4 4 4 4 4 4 4 4 4 4 8 8 8 8 8 8 8 8 8 8 8 8 161616161616161616161616323232323232323232323232646464646464646464646464 2 MySQL 5.6 MySQL 5.7 MySQL 8.0 © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

20. Resource groups CREATE RESOURCE GROUP Reporting TYPE = USER VCPU = 5-6 THREAD_PRIORITY = 6; CREATE RESOURCE GROUP Batch TYPE = USER Dynamically control resource VCPU = 7-8 THREAD_PRIORITY = 4; consumption by threads in a group SET RESOURCE GROUP Reporting FOR 1866; Currently supports vCPUs as a manageable resource SELECT /*+ RESOURCE_GROUP(Reporting) */ <col> FROM … ALTER RESOURCE GROUP Reporting VCPU = 5 THREAD_PRIORITY = 19; © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

21.MySQL 8.0 highlights – SECURITY, MANAGEABILITY • Roles • Password strength • Open SSL as default TLS/SSL library • Enhanced observability © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

22.MariaDB 10.2 highlights • Common Table Expressions • Window Functions • JSON and GeoJSON functions • Replication improvements • Delayed replication; throttling; compressed binlog • Oracle compatibility improvements • Multi-trigger; CHECK constraint expressions; EXECUTE IMMEDIATE, 38-digit DECIMAL etc. © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

23.MariaDB 10.3 highlights • Oracle compatibility • PL/SQL compatibility parser • Sequences • INTERSECT and EXCEPT to complement UNION • New ROW type and TYPE OF stored functions • Invisible Columns • Cursor with parameters • Temporal data processing • User-defined aggregates • ADD INSTANT COLUMN for InnoDB © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

24.Oracle compatibility - PL/SQL compatibility parser Easier migration from Oracle to MariaDB No need to migrate Oracle PL/SQL to SQLP/PM Simply SET sql_mode=’oracle’ to create a stored procedure using Oracle PL/SQL syntax Parser addresses syntax differences between a large subset of PL/SQL and SQL/PSM • Stored Procedures and Stored Functions • ELSIF, UNIQUE, TRUNCATE • Cursors • Functions • LOOP • Prepared Statements • Variables • Synonyms for Basic SQL Types • Exceptions • Packages • BEGIN Blocks • NULL Handling Oracle and MariaDB syntax based stored procedures can be used in the same database © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

25. 1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved. Image credit: By Mackphillips - Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=55946550

26.© 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

27.Amazon RDS for open source MySQL engines are . . . Cloud native engine Open source engines Commercial engines RDS platform • Automatic fail-over • Isolation & security • Advanced monitoring • Backup & recovery • Industry compliance • Routine maintenance • X-region replication • Automated patching • Push-button scaling © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

28.1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.

29.1. Popular 2. Innovative 3. Flexible © 2018, Amazon Web Services, Inc. or its affiliates. All rights reserved.