Deep Dive on MySQL Databases on Amazon RDS

下载 0

寒冰

发布于

1791

人观看

#信息技术

近年来，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.

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.

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

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.

0点赞

0收藏

0下载