Aurora RDS:
AWS Released encryption for Aurora Instances.
Encryption is not available as of now.
Amazon RDS Encryption Links
Encrypting Amazon RDS Resources
You can encrypt your Amazon RDS instances and snapshots at rest by enabling the encryption option for your Amazon RDS DB instance. Data that is encrypted at rest includes the underlying storage for a DB instance, its automated backups, Read Replicas, and snapshots.
Amazon RDS encrypted instances use the industry standard AES-256 encryption algorithm to encrypt your data on the server that hosts your Amazon RDS instance. Once your data is encrypted, Amazon RDS handles authentication of access and decryption of your data transparently with a minimal impact on performance. You don’t need to modify your database client applications to use encryption.
Amazon RDS encrypted instances provide an additional layer of data protection by securing your data from unauthorized access to the underlying storage. You can use Amazon RDS encryption to increase data protection of your applications deployed in the cloud, and to fulfill compliance requirements for data-at-rest encryption.
Amazon RDS encrypted instances are currently available for MySQL, PostgreSQL, Oracle, and SQL Server DB instances.
Amazon RDS also supports encrypting an Oracle or SQL Server DB instance with Transparent Data Encryption (TDE). TDE can be used in conjunction with encryption at rest, although using TDE and encryption at rest simultaneously might slightly affect the performance of your database.
All logs, backups, and snapshots are encrypted for an Amazon RDS encrypted instance. A Read Replica of an Amazon RDS encrypted instance is also encrypted using the same key as the master instance.
Aurora RDS:
Issues I have encountered so far :
- No backup and Restore Option. You got to manually migrate the data.
- You can’t rename the database. This is huge problem for us to process ETL and then rename the database.
- Unable to restore Individual databases.
- Very hard to troubleshoot timeout errors and deadlocks.
- No Encryption option available for AURORA RDS.
- Billing and IOPS Calculation is convoluted. We had about 2 Billion IOPS in few days. How its calculated is kinda of mystery.
- Read Replicas are setup instance level. Not sure options are available for individual database replication.
- http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html#USER_ReadRepl.MySQL
- Aurora doesn’t provide MyISAM MySQL Engine. Large dataset ETL Operations seems be lot faster with MyISAM Engine. ( about 500+ Million rows )
SQL Server RDS IOPS limitation:
Effective immediately, you can provision new RDS database instances with 1,000 to 10,000 IOPS, and with 100GB to 1 TB of storage for MySQL and Oracle databases. If you are using SQL Server, the maximum IOPS you can provision is 7,000 IOPS. All other RDS features including Multi-AZ, Read Replicas, and the Virtual Private Cloud, are also supported.AWS now supports 20000 IOPS for SQL Server.
https://aws.amazon.com/blogs/aws/new-high-performance-provisioned-iops-amazon-rds/
Logging on AURORA RDS:
Links:
RDS Logs to Table or Logfile Configuration
Managing Table-Based MySQL Logs
You can direct the general and slow query logs to tables on the DB instance by creating a DB parameter group and setting the log_output server parameter to TABLE. General queries are then logged to the mysql.general_log table, and slow queries are logged to the mysql.slow_log table. You can query the tables to access the log information. Enabling this logging increases the amount of data written to the database, which can degrade performance.
Both the general log and the slow query logs are disabled by default. In order to enable logging to tables, you must also set the general_log and slow_query_log server parameters to 1.
Log tables will keep growing until the respective logging activities are turned off by resetting the appropriate parameter to 0. A large amount of data often accumulates over time, which can use up a considerable percentage of your allocated storage space. Amazon RDS does not allow you to truncate the log tables, but you can move their contents. Rotating a table saves its contents to a backup table and then creates a new empty log table. You can manually rotate the log tables.
PROMPT> CALL mysql.rds_rotate_slow_log; PROMPT> CALL mysql.rds_rotate_general_log;
To completely remove the old data and reclaim the disk space, call the appropriate procedure twice in succession.
Accessing the MySQL Slow Query and General Logs
The MySQL slow query log and the general log can be written to a file or a database table by setting parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see Working with DB Parameter Groups. You must set these parameters before you can view the slow query log or general log in the Amazon RDS console or by using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs.
You can control MySQL logging by using the parameters in this list:
slow_query_log: To create the slow query log, set to 1. The default is 0.
general_log: To create the general log, set to 1. The default is 0.
long_query_time: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query execution time to be logged, in seconds. The default is 10 seconds, the minimum is 0. If log_output = FILE, you can specify a floating point value that goes to microsecond resolution. If log_output = TABLE, you must specify an integer value with second resolution. Only queries whose execution time exceeds the long_query_time value are logged. For example, setting long_query_time to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
log_queries_not_using_indexes: To log all queries that do not use an index to the slow query log, set to 1. The default is 0. Queries that do not use an index are logged even if their execution time is less than the value of the long_query_time parameter.
log_output option: You can specify one of the following options for the log_output parameter.
TABLE (default)– Write general queries to the mysql.general_log table, and slow queries to the mysql.slow_log table.
FILE– Write both general and slow query logs to the file system. Log files are rotated hourly.
NONE– Disable logging.
When logging is enabled, Amazon RDS rotates table logs or deletes log files at regular intervals. This measure is a precaution to reduce the possibility of a large log file either blocking database use or affecting performance. FILE and TABLE logging approach rotation and deletion as follows:
When FILE logging is enabled, log files are examined every hour and log files older than 24 hours are deleted. If the remaining combined log file size after the deletion exceeds a threshold of 2% of a DB instance’s allocated space, then the largest log files are deleted until the log file size no longer exceeds the threshold.
When TABLE logging is enabled, log tables are rotated every 24 hours if the space used by the table logs is more than 20 percent of the allocated storage space or the size of all logs combined is greater than 10 GB. If the amount of space used for a DB instance is greater than 90 percent of the DB instance’s allocated storage space, then the thresholds for log rotation are reduced. Log tables are then rotated if the space used by the table logs is more than 10 percent of the allocated storage space or the size of all logs combined is greater than 5 GB.
When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If the backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the mysql.general_log table is named mysql.general_log_backup. The backup log table for the mysql.slow_log table is named mysql.slow_log_backup.
You can rotate the mysql.general_log table by calling the mysql.rds_rotate_general_log procedure. You can rotate the mysql.slow_log table by calling the mysql.rds_rotate_slow_log procedure.
Table logs are rotated during a database version upgrade.
Amazon RDS records both TABLE and FILE log rotation in an Amazon RDS event and sends you a notification.
To work with the logs from the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs, set the log_output parameter to FILE. Like the MySQL error log, these log files are rotated hourly. The log files that were generated during the previous 24 hours are retained.
ALL Single Availability Zone
Pricing:
Amazon RDS Pricing Info
Storage for Amazon RDS
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html#USER_PIOPS.Realized
Amazon RDS uses Amazon Elastic Block Store (Amazon EBS) volumes for database and log storage. Depending on the amount of storage requested, Amazon RDS automatically stripes across multiple Amazon EBS volumes to enhance IOPS performance. Amazon RDS provides three types of storage with a range of storage and performance options.
AWS Documentation on Storage
Amazon RDS Storage Types
Amazon RDS provides three storage types: magnetic, General Purpose (SSD), and Provisioned IOPS (input/output operations per second). They differ in performance characteristics and price, allowing you to tailor your storage performance and cost to the needs of your database. You can create MySQL, PostgreSQL, and Oracle RDS DB instances with up to 6TB of storage and SQL Server RDS DB instances with up to 4TB of storage when using the Provisioned IOPS and General Purpose (SSD) storage types. Existing MySQL, PostgreSQL, and Oracle RDS database instances can be scaled to these new database storage limits without any downtime. For a complete discussion of the different volume types, see the topic Amazon EBS Volume Types.
- Magnetic (Standard) – Magnetic storage, also called standard storage, offers cost-effective storage that is ideal for applications with light or burst I/O requirements. These volumes deliver approximately 100 IOPS on average, with burst capability of up to hundreds of IOPS, and they can range in size from 5 GB to 3 TB, depending on the DB instance engine that you chose. Magnetic storage is not reserved for a single DB instance, so performance can vary greatly depending on the demands placed on shared resources by other customers.
- General Purpose (SSD) – General purpose, SSD-backed storage, also called gp2, can provide faster access than disk-based storage. This storage type can deliver single-digit millisecond latencies, with a base performance of 3 IOPS/GB and the ability to burst to 3,000 IOPS for extended periods of time. In certain cases, based on your instance and storage configuration, you may get more than 3000 IOPS. General purpose (SSD) volumes can range in size from 5 GB to 6 TB for MySQL, PostgreSQL, and Oracle DB instances, and from 20 GB to 4 TB for SQL Server DB instances. This storage type is excellent for small to medium-sized databases.
- Provisioned IOPS – Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads, particularly database workloads, that are sensitive to storage performance and consistency in random access I/O throughput. Provisioned IOPS volumes can range in size from 100 GB to 6 TB for MySQL, PostgreSQL, and Oracle DB engines. SQL Server Express and Web editions can range in size from 100 GB to 4 TB, while SQL Server Standard and Enterprise editions can range in size from 200 GB to 4 TB. You specify the amount of storage you want allocated, and then specify the amount of dedicated IOPS you want. These two values form a ratio, and this value maintains the ratio specified for the DB engine you chose. Amazon RDS delivers within 10 percent of the provisioned IOPS performance 99.9 percent of the time over a given year.
Several factors can affect the performance of Amazon EBS volumes, such as instance configuration, I/O characteristics, and workload demand. For more information about getting the most out of your Provisioned IOPS volumes, see Amazon EBS Volume Performance.
For existing MySQL, PostgreSQL, and Oracle DB instances, you might observe some I/O capacity improvement if you scale up your storage. Note that you cannot change the storage capacity of a SQL Server DB instance due to extensibility limitations of striped storage attached to a Windows Server environment.
Performance Metrics
Amazon RDS provides several metrics that you can use to determine how your DB instance is performing. You can view the metrics in the RDS console by selecting your DB instance and clicking . You can also use Amazon CloudWatch to monitor these metrics. For more information, go to the Viewing DB Instance Metrics.
- IOPS – the number of I/O operations completed per second. This metric is reported as the average IOPS for a given time interval. Amazon RDS reports read and write IOPS separately on one minute intervals. Total IOPS is the sum of the read and write IOPS. Typical values for IOPS range from zero to tens of thousands per second.
- Latency – the elapsed time between the submission of an I/O request and its completion. This metric is reported as the average latency for a given time interval. Amazon RDS reports read and write latency separately on one minute intervals in units of seconds. Typical values for latency are in the millisecond (ms); for example, Amazon RDS reports 2 ms as 0.002 seconds.
- Throughput – the number of bytes per second transferred to or from disk. This metric is reported as the average throughput for a given time interval. Amazon RDS reports read and write throughput separately on one minute intervals using units of megabytes per second (MB/s). Typical values for throughput range from zero to the I/O channel’s maximum bandwidth.
- Queue Depth – the number of I/O requests in the queue waiting to be serviced. These are I/O requests that have been submitted by the application but have not been sent to the device because the device is busy servicing other I/O requests. Time spent waiting in the queue is a component of Latency and Service Time (not available as a metric). This metric is reported as the average queue depth for a given time interval. Amazon RDS reports queue depth in one minute intervals. Typical values for queue depth range from zero to several hundred.
Facts About Amazon RDS Storage
The following points are important facts you should know about Amazon RDS storage:
- The current maximum channel bandwidth available is 2000 megabits per second (Mbps) full duplex. In terms of the read and write throughput metrics, this equates to about 105 megabytes per second (MB/s) in each direction. A perfectly balanced workload of 50% reads and 50% writes may attain a maximum combined throughput of 210 MB/s. Note that this is channel throughput, which includes protocol overhead, so the actual data throughput may be less.
- Provisioned IOPS works with an I/O request size of 32 KB. An I/O request smaller than 32 KB is handled as one I/O; for example, 1000 16 KB I/O requests are treated the same as 1000 32 KB requests. I/O requests larger than 32 KB consume more than one I/O request; Provisioned IOPS consumption is a linear function of I/O request size above 32 KB. For example, a 48 KB I/O request consumes 1.5 I/O requests of storage capacity; a 64 KB I/O request consumes 2 I/O requests, etc. For more information about Provisioned IOPS, see Amazon RDS Provisioned IOPS Storage to Improve Performance.Note that I/O size does not affect the IOPS values reported by the metrics, which are based solely on the number of I/Os over time. This means that it is possible to consume all of the IOPS provisioned with fewer I/Os than specified if the I/O sizes are larger than 32 KB. For example, a system provisioned for 5,000 IOPS can attain a maximum of 2,500 IOPS with 64 KB I/O or 1,250 IOPS with 128 KB IO.Note that magnetic storage does not provision I/O capacity, so all I/O sizes are counted as a single I/O. General purpose storage provisions I/O capacity based on the size of the volume. For more information on general purpose storage throughput, go to General Purpose (SSD) Volumes.
- The first time a DB instance is started and accesses an area of disk for the first time, the process can take longer than all subsequent accesses to the same disk area. This is known as the “first touch penalty.” Once an area of disk has incurred the first touch penalty, that area of disk does not incur the penalty again for the life of the instance, even if the DB instance is rebooted, restarted, or the DB instance class changes. Note that a DB instance created from a snapshot, a point-in-time restore, or a read replica is a new instance and does incur this first touch penalty.
- Because Amazon RDS manages your DB instance, we reserve overhead space on the instance. While the amount of reserved storage varies by DB instance class and other factors, this reserved space can be as much as one or two percent of the total storage.
- Provisioned IOPS provides a way to reserve I/O capacity by specifying IOPS. Like any other system capacity attribute, maximum throughput under load will be constrained by the resource that is consumed first. That resource could be IOPS, channel bandwidth, CPU, memory, or database internal resources.
Other Factors That Impact Storage Performance
All of the following system related activities consume I/O capacity and may reduce database instance performance while in progress:
- DB snapshot creation
- Nightly backups
- Multi-AZ peer creation
- Read replica creation
- Scaling storage
System resources can constrain the throughput of a DB instance, but there can be other reasons for a bottleneck. If you find the following situation, your database could be the issue:
- The channel throughput limit is not reached
- Queue depths are consistently low
- CPU utilization is under 80%
- There is free memory available
- There is no swap activity
- There is plenty of free disk space
- Your application has dozens of threads all submitting transactions as fast as the database will take them, but there is clearly unused I/O capacity
If there isn’t at least one system resource that is at or near a limit, and adding threads doesn’t increase the database transaction rate, the bottleneck is most likely contention in the database. The most common forms are row lock and index page lock contention, but there are many other possibilities. If this is your situation, you should seek the advice of a database performance tuning expert.
Adding Storage and Changing Storage Type
You can modify a DB instance to use additional storage and you can convert to a different storage type. Adding storage or converting to a different storage type can take time and reduces the performance of your DB instance, so you should plan when to make these changes.
Although your DB instance is available for reads and writes when adding storage, you may experience degraded performance until the process is complete. Adding storage may take several hours; the duration of the process depends on several factors such as database load, storage size, storage type, amount of IOPS provisioned (if any), and number of prior scale storage operations. Typical scale storage times will be under 24 hours, but can take up to several days in some cases. During the scaling process, the DB instance will be available for use, but may experience performance degradation.
Storage conversions between magnetic storage and general purpose (SSD) storage can potentially deplete the initial 5.4 million I/O credits (3,000 IOPS X 30 Minutes) allocated for general purpose (SSD) storage. When performing these storage conversions, the first 82 GB of data will be converted at approximately 3,000 IOPS, while the remaining data will be converted at the base performance rate of 3 IOPS per GB of allocated general purpose (SSD) storage. This can result in longer conversion times. You can provision more general purpose (SSD) storage to increase your base I/O performance rate, thus improving the conversion time, but note that you cannot reduce storage size once it has been allocated.
General Purpose (SSD) Storage
General purpose (SSD) storage offers cost-effective storage that is ideal for small or medium-sized database workloads. This storage type can deliver single-digit millisecond latencies, with a base performance of 3 IOPS/GB and the ability to burst to 3,000 IOPS for extended periods of time. In certain cases, based on your instance and storage configuration, you may get more than 3000 IOPS. General purpose (SSD) storage volumes can range in size from 5 GB to 6 TB for MySQL, PostgreSQL, and Oracle DB instances and from 20 GB to 4 TB for SQL Server DB instances. Note that provisioning less than 100 GB of general purpose (SSD) storage for high-throughput workloads can result in higher latencies if the initial general purpose (SSD) I/O credit balance is depleted.
Amazon RDS Provisioned IOPS Storage to Improve Performance
For any production application that requires fast and consistent I/O performance, we recommend Provisioned IOPS (input/output operations per second) storage. Provisioned IOPS storage is a storage type that delivers fast, predictable, and consistent throughput performance. When you create a DB instance, you specify an IOPS rate and storage space allocation. Amazon RDS provisions that IOPS rate and storage for the lifetime of the DB instance or until you change it. Provisioned IOPS storage is optimized for I/O intensive, online transaction processing (OLTP) workloads that have consistent performance requirements. Provisioned IOPS helps performance tuning.
AWS Documentation
Factors That Affect Realized IOPS Rates
Your actual realized IOPS rate may vary from the amount that you provision depending on page size and network bandwidth, which are determined in part by your DB engine. It is also affected by DB instance size and database workload.
Page Size and Channel Bandwidth
The theoretical maximum IOPS rate is also a function of database I/O page size and available channel bandwidth. MySQL uses a page size of 16 KB, while Oracle, PostgreSQL (default), and SQL Server use 8 KB. On a DB instance with a full duplex I/O channel bandwidth of 1000 megabits per second (Mbps), the maximum IOPS for page I/O is about 8,000 IOPS total for both directions (input/output channel) for 16 KB I/O and 16,000 IOPS total for both directions for 8 KB I/O.