Overview of Amazon Aurora MySQL
The following sections provide an overview of Amazon Aurora MySQL.
Topics
Amazon Aurora MySQL performance enhancements
Amazon Aurora includes performance enhancements to support the diverse needs of high-end commercial databases.
Fast insert
Fast insert accelerates parallel inserts sorted by primary key and applies
specifically to LOAD DATA
and INSERT INTO ... SELECT
...
statements. Fast insert caches the position of a cursor in an
index traversal while executing the statement. This avoids unnecessarily
traversing the index again.
You can monitor the following metrics to determine the effectiveness of fast insert for your DB cluster:
-
aurora_fast_insert_cache_hits
: A counter that is incremented when the cached cursor is successfully retrieved and verified. -
aurora_fast_insert_cache_misses
: A counter that is incremented when the cached cursor is no longer valid and Aurora performs a normal index traversal.
You can retrieve the current value of the fast insert metrics using the following command:
mysql>
show global status like 'Aurora_fast_insert%';
You will get output similar to the following:
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| Aurora_fast_insert_cache_hits | 3598300 |
| Aurora_fast_insert_cache_misses | 436401336 |
+---------------------------------+-----------+
Amazon Aurora MySQL and spatial data
The following list summarizes the main Aurora MySQL spatial features and explains how they correspond to spatial features in MySQL.
-
Aurora MySQL 1.x supports the same spatial data types
and spatial relation functions as MySQL 5.6. -
Aurora MySQL 2.x supports the same spatial data types
and spatial relation functions as MySQL 5.7. -
Aurora MySQL 1.x and 2.x both support spatial indexing on InnoDB tables. Spatial indexing improves query performance on large datasets for queries on spatial data. In MySQL, spatial indexing for InnoDB tables isn't available in MySQL 5.6, but is available in MySQL 5.7. Both Aurora MySQL 1.x and 2.x use a different spatial indexing strategy than MySQL for high performance with spatial queries. The Aurora spatial index implementation uses a space-filling curve on a B-tree, which is intended to provide higher performance for spatial range scans than an R-tree.
The following data definition language (DDL) statements are supported for creating indexes on columns that use spatial data types.
CREATE TABLE
You can use the SPATIAL INDEX
keywords in a CREATE TABLE
statement to add a
spatial index to a column in a new table. Following is an example.
CREATE TABLE test (shape POLYGON NOT NULL, SPATIAL INDEX(shape));
ALTER TABLE
You can use the SPATIAL INDEX
keywords in an ALTER TABLE
statement to add a
spatial index to a column in an existing table. Following is an example.
ALTER TABLE test ADD SPATIAL INDEX(shape);
CREATE INDEX
You can use the SPATIAL
keyword in a CREATE INDEX
statement to add a spatial index
to a column in an existing table. Following is an example.
CREATE SPATIAL INDEX shape_index ON test (shape);
Comparison of Aurora MySQL 5.6 and Aurora MySQL 5.7
The following Amazon Aurora MySQL features are supported in Aurora MySQL 5.6, but these features are currently not supported in Aurora MySQL 5.7.
-
Asynchronous key prefetch (AKP). For more information, see Working with asynchronous key prefetch in Amazon Aurora.
-
Hash joins. For more information, see Working with hash joins in Aurora MySQL.
-
Native functions for synchronously invoking AWS Lambda functions. You can asynchronously invoke AWS Lambda functions from Aurora MySQL 5.7. For more information, see Invoking a Lambda function with an Aurora MySQL native function.
-
Scan batching. For more information, see Aurora MySQL database engine updates 2017-12-11.
Currently, Aurora MySQL 5.7 does not support features added in Aurora MySQL version 1.16 and later. For information about Aurora MySQL version 1.16, see Aurora MySQL database engine updates 2017-12-11.
The performance schema isn't available for early release of Aurora MySQL 5.7. Upgrade to Aurora 2.03 or higher for performance schema support.
Comparison of Aurora MySQL 5.7 and MySQL 5.7
The following features are supported in MySQL 5.7.12 but are currently not supported in Aurora MySQL 5.7:
-
Group replication plugin
-
Increased page size
-
InnoDB buffer pool loading at startup
-
InnoDB full-text parser plugin
-
Multisource replication
-
Online buffer pool resizing
-
Password validation plugin
-
Query rewrite plugins
-
Replication filtering
-
The
CREATE TABLESPACE
SQL statement -
X Protocol
For more information about these features, see the
MySQL 5.7 documentation