Amazon Aurora
User Guide (API Version 2014-10-31)

Overview of Amazon Aurora MySQL

The following sections provide an overview of Amazon Aurora MySQL.

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

Amazon Aurora MySQL supports the same spatial data types and spatial relation functions as the equivalent MySQL release. For example, Amazon Aurora MySQL 5.7 supports the same spatial data types and spatial relation functions as MySQL 5.7.

Aurora MySQL also supports spatial indexing on InnoDB tables, similar to that offered by MySQL 5.7. Spatial indexing improves query performance on large datasets for queries that use spatial data. Aurora MySQL uses a different indexing strategy than MySQL, using a space-filling curve on a B-tree instead of 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.

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 Amazon Aurora MySQL Database Engine Updates 2017-12-11.

The performance schema is disabled for Aurora MySQL 5.7.

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:

  • Global transaction identifiers (GTIDs)

  • 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.