Querying Archived RDS Data Directly From an S3 Bucket

querying archived rds data from s3 bucket.png

querying archived rds data from s3 bucket.pngA recommendation we often give to our customers is along the lines of “archive old data” to reduce your database size. There is a tradeoff between keeping all our data online and archiving part of it to cold storage.

There could also be legal requirements to keep certain data online, or you might want to query old data occasionally without having to go through the hassle of restoring an old backup.

In this post, we will explore a very useful feature of AWS RDS/Aurora that allows us to export data to an S3 bucket and run SQL queries directly against it.

Archiving Data to S3

Let’s start by describing the steps we need to take to put our data into an S3 bucket in the required format, which is called Apache Parquet.

Amazon states the Parquet format is up to 2x faster to export and consumes up to 6x less storage in S3, compared to other text formats.

1. Create a snapshot of the database (or select an existing one)

2. Create a customer-managed KMS key to encrypt the exported data

Archiving Data to S3


3. Create an IAM role (e.g. exportrdssnapshottos3role)

4. Create an IAM policy for the export task and assign it to the role

    "Version": "2012-10-17",
    "Id": "Policy1636727509941",
    "Statement": [
            "Sid": "Stmt1636727502144",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::123456789:role/service-role/exportrdssnapshottos3role"
            "Action": [
            "Resource": [

5. Optional: Create an S3 bucket (or use an existing one)

6. Set a bucket policy to allow the IAM role to perform the export e.g.:

     "Version": "2012-10-17",
     "Statement": [
         "Effect": "Allow",
         "Principal": {
            "Service": ""
         "Action": "sts:AssumeRole"

7. Export the snapshot to Amazon S3 as an Apache Parquet file. You can choose to export specific sets of databases, schemas, or tables


Export the snapshot to Amazon S3

IAM role

Querying the Archived Data

When you need to access the data, you can use Amazon Athena to query the data directly from the S3 bucket.

1. Set a query result location

Amazon Athena

2. Create an external table in Athena Query editor. We need to map the MySQL column types to equivalent types in Parquet

  id DECIMAL(20,0),
  name STRING, 
  is_customer TINYINT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
LOCATION 's3://aurora-training-s3/exports/2021/log/'
tblproperties ("parquet.compression"="SNAPPY");

3. Now we can query the external table from the Athena Query editor

FROM log_requests
WHERE created_at >= CAST('2021-10-01' AS TIMESTAMP)
  AND created_at < CAST('2021-11-01' AS TIMESTAMP)
GROUP BY name;

Removing the Archived Data from the Database

After testing that we can query the desired data from the S3 bucket, it is time to delete archived data from the database for good. We can use the pt-archiver tool for this task.

Having a smaller database has several benefits. To name a few: your backup/restore will be faster, you will be able to keep more data in memory so response times improve, you may even be able to scale down your server specs and save some money.

Complete the 2021 Percona Open Source Data Management Software Survey

Have Your Say!


The 1-2-3 for PostgreSQL Logical Replication Using an RDS Snapshot

PostgreSQL Logical Replication RDS

We have several PostgreSQL versions that support logical decoding to replicate data changes from a source database to a target database, which is a cool and very powerful tool that gives the option to replicate all the tables in a database, only one schema, a specific set of tables or even only some columns/rows, also is a helpful method for version upgrades since the target database can run on a different (minor or major) PostgreSQL version.

PostgreSQL Logical Replication

Image from:

There are some cases when the databases have been hosted in the AWS Relational Database Service (RDS) which is the fully auto-managed solution offered by Amazon Web Services, there is no secret that choosing this option for our database backend comes with a level of vendor lock-in, and even when RDS offers some build-in replica solutions such as Multi-AZ or read-replicas sometimes we can take advantage of the benefits from logical replication.

In this post I will describe the simplest and basic steps I used to implement this replica solution avoiding the initial copy data from the source database to the target, creating the target instance from an RDS snapshot. Certainly, you can take advantage of this when you work with a big/huge data set and the initial copy could lead to high timeframes or network saturation.   

NOTE: The next steps were tested and used for a specific scenario and they are not intended to be an any-size solution, rather give some insight into how this can be made and most importantly, to stimulate your own creative thinking.  

The Scenario

Service Considerations

In this exercise, I wanted to perform a version upgrade from PostgreSQL v11.9 to PostgreSQL v12.5, we can perform a direct upgrade using the build-in option RDS offers, but that requires a downtime window that can vary depending on some of the next:

  • Is Multi-AZ enabled?
  • Are the auto backups enabled?
  • How transactional is the source database?

During the direct upgrade process, RDS takes a couple of new snapshots of the source instance, firstly at the beginning of the upgrade and finally when all the modifications are done, depending on how old is the previous backup and how many changes have been made on the datafiles the pre backup could take some time. Also, if the instance is Multi-AZ the process should upgrade both instances, which adds more time for the upgrade, during most of these actions the database remains inaccessible.

The next is a basic diagram of how an RDS Multi-AZ instance looks, all the client requests are sent to the master instance, while the replica is not accessible and some tasks like the backups are executed on it.

PostgreSQL Logical Replication on RDS

Therefore, I choose logical replication as the mechanism to achieve the objective, we can aim for a quicker switch-over if we create the new instance in the desired version and just replicate all the data changes, then we need a small downtime window just to move the traffic from the original instance to the upgraded new one.


To be able to perform these actions we would need:

  • An AWS user/access that can operate the DB instances, take DB snapshots and upgrade and restore them.
  • The AWS user also should be able to describe and create DB PARAMETER GROUPS.
  • A DB user with enough privileges to create the PUBLICATION on source and SUBSCRIPTION on target also is advisable to create a dedicated replication user with the minimum permissions. 

The 1-2-3 Steps

Per the title of this post, the next is the list of steps to set up a PostgreSQL logical replication between a PostgreSQL v11.9 and a v12.5 using an RDS snapshot to initialize the target database. 

  1. Verify the PostgreSQL parameters for logical replication
  2. Create the replication user and grant all the required privileges
  3. Create the PUBLICATION
  5. Create a new RDS snapshot 
  6. Upgrade the RDS snapshot to the target version
  7. Restore the upgraded RDS snapshot
  8. Get the LSN position 
  9. Create the SUBSCRIPTION
  10. Advance the SUBSCRIPTION 
  11. Enable the SUBSCRIPTION

Source Database Side

1. Verify the PostgreSQL parameters for logical replication

We require the next PostgreSQL parameters for this exercise

demodb=> select name,setting from pg_settings where name in (
        'max_wal_senders') ;
          name          | setting
 max_replication_slots  | 10
 max_wal_senders        | 10
 max_worker_processes   | 10
 track_commit_timestamp | on
 wal_level              | logical
(5 rows)

NOTE: The parameter track_commit_timestamp can be optional since in some environments is not advisable for the related overhead, but it would help to track and resolve any conflict that may occur when the subscriptions are started.

2. Create the replication user and grant all the required privileges

demodb=> CREATE USER pgrepuser WITH password 'SECRET';
demodb=> GRANT rds_replication TO pgrepuser;
demodb=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrepuser;

3. Create the PUBLICATION

demodb=> CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;


demodb=> SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput');
(1 row)


5. Create a new RDS snapshot 

aws rds create-db-snapshot \
    --db-instance-identifier demodb-postgres\
    --db-snapshot-identifier demodb-postgres-to-125

6. Upgrade the RDS snapshot to the target version

aws rds modify-db-snapshot \
    --db-snapshot-identifier demodb-postgres-to-125 \
    --engine-version 12.5

7. Restore the upgraded RDS snapshot 

Since we are moving from version 11.9 to 12.5 we may need to create a new DB parameter group if we are using some custom parameters. 
From the instance describe we can verify the current parameter group

aws rds describe-db-instances \
        --db-instance-identifier demodb-postgres \| 
jq '.DBInstances | map({DBInstanceIdentifier: .DBInstanceIdentifier, DBParameterGroupName: .DBParameterGroups[0].DBParameterGroupName})'
    "DBInstanceIdentifier": "demodb-postgres",
    "DBParameterGroupName": "postgres11-logicalrep"

Then we can validate the custom parameters 

aws rds describe-db-parameters \
	--db-parameter-group-name postgres11-logicalrep \
	--query "Parameters[*].[ParameterName,ParameterValue]" \
	--source user --output text 
track_commit_timestamp	1

We need to create a new parameter group in the target version

aws rds create-db-parameter-group \
	--db-parameter-group-name postgres12-logicalrep \
	--db-parameter-group-family postgres12

Finally, we need to modify the parameters we got before in the new parameter group

aws rds modify-db-parameter-group \
	--db-parameter-group-name postgres12-logicalrep \
	--parameters "ParameterName='track_commit_timestamp',ParameterValue=1,ApplyMethod=immediate"

Now we can use the new parameter group to restore the upgraded snapshot

aws rds restore-db-instance-from-db-snapshot \
	--db-instance-identifier demodb-postgres-125 \
	--db-snapshot-identifier demodb-postgres-to-125 \
	--db-parameter-group-name postgres12-logicalrep

8. Get the LSN position from the target instance log

To list all the database logs for the new DB instance

aws rds describe-db-log-files \
	--db-instance-identifier demodb-postgres-125

We should pick the latest database log

aws rds download-db-log-file-portion \
	--db-instance-identifier demodb-postgres-125 \
	--log-file-name "error/postgresql.log.2021-03-23-18"

From the retrieved log portion we need to find the value after for the log entry redo done at:

2021-03-23 18:19:58 UTC::@:[5212]:LOG:  redo done at 3E/50000D08

Target Database Side


demodb=> CREATE SUBSCRIPTION pglogical_sub01 CONNECTION ' port=5432 dbname=demodb user=pgrepuser password=SECRET' PUBLICATION pglogical_rep01
  copy_data = false,
  create_slot = false,
  enabled = false,
  connect = true,
  slot_name = 'pglogical_rep01'

10. Advance the SUBSCRIPTION 

We need to get the subscription id

demodb=> SELECT 'pg_'||oid::text AS "external_id"
FROM pg_subscription 
WHERE subname = 'pglogical_sub01';
(2 rows)

Now advance the subscription to the LSN we got in step 8

demodb=> SELECT pg_replication_origin_advance('pg_73750', '3E/50000D08') ;
(1 row)

11. Enable the SUBSCRIPTION

demodb=> ALTER SUBSCRIPTION pglogical_sub01 ENABLE;

Once we are done with all the steps the data changes should flow from the source database to the target, we can check the status at the pg_stat_replication view. 


Choosing DBaaS from cloud vendors bring some advantages and can speed up some implementations, but they come with some costs, and not all the available tools or solutions fits all the requirements, that is why always is advisable to try some different approaches and think out of the box, technology can go so far as our imagination. 


Amazon RDS for MySQL 5.5 EOL Date is Approaching – Act Now!

Amazon RDS for MySQL 5.5 EOL

Amazon RDS for MySQL 5.5 EOLAs mentioned in the AWS discussion forum back in October, Amazon has started the end of life (EOL) process for RDS MySQL version 5.5. What this means is:

  • AWS will upgrade RDS instances to MySQL 5.7 starting February 9 2021 00:00 UTC during your next defined maintenance window, provided you have one.
  • If you don’t have a maintenance window defined, RDS will automatically upgrade you on March 9 00:00 UTC and there is no opt-out.

As any seasoned administrator knows, upgrades can be painful and things might go wrong.


I think we can safely assume that the upgrade will be performed in-place, as it would be too complex and time-consuming otherwise. Since a direct upgrade from 5.5 to 5.7 is not supported, we need to go through 5.6 first. This means instances need to be restarted twice; first to go from 5.5 to 5.6, and then from 5.6 to 5.7.

As per the RDS upgrade guide, any read replicas are upgraded first, while the primary instance is upgraded last.

If there are issues with any of the restarts, you will need to depend on AWS support to help you. Remember you don’t have access to the underlying instance.

Here is a list of other important issues you need to be aware of:

Query Regressions

By far the most common issue, some queries will perform differently in 5.7 due to changes in the optimizer code. Some will be faster, some will be slower. There is even the possibility of some queries starting to do a full table scan, in spite of them using an index before.

It is of utmost importance to do regression testing in advance of the upgrade to avoid any unpleasant surprises.

Tables Using the Old Datetime format

Starting with MySQL 5.6, the datetime columns have microsecond precision. This means old 5.5 tables using such columns have to be rebuilt, which can be a lengthy process. This happens at upgrade time by default – more precisely while running mysql_upgrade script.

We can avoid this by converting tables on a read replica, and then promoting it to primary. For more information check out the article about Upgrading to MySQL 5.7 focusing on temporal types.

Reserved Words

It is a good practice to check for new reserved words usage prior to doing a database upgrade. Any reserved keywords will cause syntax errors on the new version, potentially breaking your application.

Luckily, the MySQL manual provides a list of reserved words for each version.


It is not clear what will happen with snapshots from the old version. To be on the safe side, a new full backup should be taken right after the upgrade.

Rollback Options

Since there is no mention of any rollback mechanism in the communication, you need to think about the strategy if things go wrong past the point of no return.

One possibility is to create an external replica. Even though it is not supported officially, replication from a higher to lower MySQL version is possible most of the time.

The replica could be useful as a temporary data source, while any unexpected issues with the new version are addressed.

Final Words

MySQL 5.5 is ancient by now, and it definitely makes sense to upgrade. Even MySQL 5.6 is going out of support soon – in fact, Percona is offering post-MySQL 5.6 EOL support if you are not ready to upgrade yet.


In this case, the upgrade process is not straight-forward and is definitely not risk-free. Rather than wait and pray nothing breaks, a better strategy is to do some testing, prepare a proper upgrade plan and execute it well in advance of the EOL date.

This will help you minimize downtime, and give you time to deal with any problems beforehand, reducing the risk to your business.

Not ready to give up MySQL 5.6? Talk to our sales team about MySQL 5.6 Post EOL Support.

Contact Us Today!


Data Consistency for RDS for MySQL: The 8.0 Version

data consistency rds mysql 8

data consistency rds mysql 8In a previous blog post on Data Consistency for RDS for MySQL, we presented a workaround to manage run pt-table-checksum on RDS instances. However, if your instance is running a MySQL 8.0.X version, there’s a simpler way to check data consistency.

Starting with 8.0.1, MySQL introduced something called “Dynamic Privileges” which is a solution to grant more granulated privileges to the users, instead of the almighty SUPER privilege.

So what was the issue with pt-table-checksum and RDS again? Since there’s no SUPER privileges for any user, there was no way for the tool to change the binlog_format to STATEMENT… but not anymore.

The solution when using 8.0 is to grant a privilege called SYSTEM_VARIABLES_ADMIN, and with that privilege, the user granted with it can now execute “set global binlog_format = STATEMENT” without being rejected.


Before going to the steps, my setup is a primary RDS 8.0.20 with a read replica, the same version. A table called “inconsistency” with, well, an inconsistency introduced.


mysql> select * from dani.inconsistency;
| id | string_field | numeric_field |
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | pepe         |             1 |
9 rows in set (0.00 sec)


mysql> select * from dani.inconsistency;
| id | string_field | numeric_field |
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | papa         |             1 |
9 rows in set (0.00 sec)

Now to the actual check. The first step is to grant the privilege. Here I have my “percona” user:

mysql> grant system_variables_admin on *.* to percona;
Query OK, 0 rows affected (0.01 sec)

The second and final step is to execute pt-table-checksum. That’s it!

[root@ip-192-168-1-200~]# pt-table-checksum --user=percona --password=xxxxxxx --databases=dani --recursion-method,D=percona,t=dsns --no-check-binlog-format --no-check-replication-filters --chunk-size=3
Checking if all tables can be checksummed ...
Starting checksum ...
09-10T20:20:17      0      1        9          0       5       0   0.175 dani.inconsistency
[root@ip-192-168-1-200 ~]# echo $?

So we can see here that there is 1 DIFF reported. Also, the exit status of “16” is confirmed (16 means “At least one diff was found”).

The difference between both instances is in the 3rd chunk, that from id =7 to id=9:

mysql> SELECT * FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) GROUP BY db, tbl\G
*************************** 1. row ***************************
            db: dani
           tbl: inconsistency
         chunk: 3
    chunk_time: 0.00951
   chunk_index: PRIMARY
lower_boundary: 7
upper_boundary: 9
      this_crc: 32ab17eb
      this_cnt: 3
    master_crc: 2d705b07
    master_cnt: 3
            ts: 2020-09-10 20:20:17
1 row in set (0.00 sec)

Which is the 9th row, wherein the primary it says “pepe” and in the replica says “papa”.

So if you are running RDS for MySQL with the 8 series, pt-table-checksum is back to being something you can use thanks to the dynamic privileges. Yet another reason to upgrade to MySQL 8.0!


Checking Data Consistency for RDS for MySQL

data consistency RDS MySQL

data consistency RDS MySQLMySQL for RDS and DBaaS, in general, are very controlled environments by the vendors, meaning that there are missing things like a SUPER grant for the root user (and any user in general). This has some implications on operations, one of them being the impossibility of running pt-table-checksum to verify data consistency between a primary and its replicas.

However, there’s a workaround that might overcome this situation and involves three things:

  • The pt-table-checksum itself
  • A way to collect executed queries
  • And the last one, which can be controversial, is to remove the read-only from the replica and use a maintenance window to stop traffic to the database while pt-table-checksum runs.

The problem with RDS is that you cannot change binlog_format to STATEMENT, which is one of the requirements for pt-table-checksum to run.

The workaround consists of capturing the executed queries and replay it them in the replica. There are several ways to collect the queries: one can be using the Performance Schema in a similar way as explained in this blog post ( Another one is just using the slow log with long_query_time = 0. By default on RDS the log output is set to TABLE so with a simple query against mysql.slow_log you can get the queries. Another option that we prefer to avoid is to use pt-query-digest processlist feature since it might lose capturing some queries.

Queries look like this:

# Time: 2020-09-01T15:20:34
# User@Host: percona[percona] @ []
# Query_time: 0.007615  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use dani;
REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'dani', 'inconsistency', '5', 'PRIMARY', '9', NULL, COUNT(*), '0' FROM `dani`.`inconsistency` FORCE INDEX(`PRIMARY`) WHERE ((`id` > '9')) ORDER BY `id` /*past upper chunk*/;
# Time: 2020-09-01T15:20:34
# User@Host: percona[percona] @ []
# Query_time: 0.009266  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use dani;
UPDATE `percona`.`checksums` SET chunk_time = '0.008633', master_crc = '0', master_cnt = '0' WHERE db = 'dani' AND tbl = 'inconsistency' AND chunk = '5';

The next step is to send those queries to the replicas as soon as possible so we can somehow guarantee that the point in time for comparison is the same for tables on both primary and secondary. And that’s the reason why one needs to change the read-only value in the replicas to 0. A change that can be rollbacked immediately after the pt-table-checksum process ends.

The Proof of Concept

I have created an RDS primary/secondary environment and have added a table with inconsistency on purpose.

Primary values:

mysql> select * from inconsistency;
| id | string_field | numeric_field |
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | pepe         |             1 |
9 rows in set (0.09 sec)

And replica values:

mysql> select * from inconsistency;
| id | string_field | numeric_field |
|  1 | casa         |             1 |
|  2 | caza         |             2 |
|  3 | auto         |             3 |
|  4 | auto         |             3 |
|  5 | auto         |             4 |
|  6 | auto         |             5 |
|  7 | autos        |             5 |
|  8 | autos        |             6 |
|  9 | papa         |             1 |
9 rows in set (0.08 sec)

Can you spot the difference :)? It’s the last row. While on the Primary the string_field says “pepe” in the replica it says “papa”.

So are we ready to run pt-table-checksum? Not quite. The tool will complain about not being able to change the binlog_format and it will end the execution. Unfortunately, currently, there’s no way to avoid that other than modifying the code. The change is to add a return to the following conditional:

      if ( VersionParser->new($dbh) >= '5.1.5' ) {
         $sql = 'SELECT @@binlog_format';

With the return:

      if ( VersionParser->new($dbh) >= '5.1.5' ) {
         $sql = 'SELECT @@binlog_format';

In pt-table-checksum version 3.2.1, that is in the line 10181:

Now we are ready! Let’s see if we can find out that difference using the tools. To send the queries to the replicas, execute the queries previously captured.

And finally, the actual pt-table-checksum command:

pt-table-checksum --host=dgb-primary --user=percona --password=xxxxx --no-check-binlog-format --no-check-slave-tables --databases=dani --recursion-method=none --chunk-size=3

The output won’t report any difference and is expected to happen like that, so don’t panic. So, how do we check the reality? By querying the checksums table in the replica:

mysql> select * from percona.checksums;
| db   | tbl           | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
| dani | inconsistency |     1 |    0.00877 | PRIMARY     | 1              | 3              | ae8eafc4 |        3 | ae8eafc4   |          3 | 2020-09-01 16:48:04 |
| dani | inconsistency |     2 |   0.008754 | PRIMARY     | 4              | 6              | 374d887b |        3 | 374d887b   |          3 | 2020-09-01 16:48:04 |
| dani | inconsistency |     3 |   0.008737 | PRIMARY     | 7              | 9              | 25680fb9 |        3 | d7e101a5   |          3 | 2020-09-01 16:48:04 |
| dani | inconsistency |     4 |   0.008944 | PRIMARY     | NULL           | 1              | 0        |        0 | 0          |          0 | 2020-09-01 16:48:04 |
| dani | inconsistency |     5 |   0.008905 | PRIMARY     | 9              | NULL           | 0        |        0 | 0          |          0 | 2020-09-01 16:48:04 |

See the difference? It is the chunk number 3, the “this_crc” and “master_crc” are different. It’s hard to spot, right? Let’s try with some filters to the query:

mysql> SELECT * FROM percona.checksums WHERE (  master_cnt <> this_cnt  OR master_crc <> this_crc  OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;
| db   | tbl           | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts                  |
| dani | inconsistency |     3 |   0.008602 | PRIMARY     | 7              | 9              | 25680fb9 |        3 | d7e101a5   |          3 | 2020-09-01 16:56:16 |
1 row in set (0.08 sec)

There you go, data inconsistency detected.

Working with MySQL 8.0? There’s an even easier way to check data consistency!

Fine print

Some things to consider:

  • Replicas should be up to date – If there’s a lag between primary and secondary you would get false negatives.
  • The read-only itself: it’s kind of ironic that to check data consistency you have to disable the one thing that guarantees data consistency. However, it is temporary, and it is highly important to revert to read-only=on once the process is done.
  • Traffic to the database must be stopped in order to guarantee 100% that the data that we are checking is in the same point-in-time, meaning: no changes happened in between.

When Should I Use Amazon Aurora and When Should I use RDS MySQL?

Now that Database-as-a-service (DBaaS) is in high demand, there is one question regarding AWS services that cannot always be answered easily : When should I use Aurora and when RDS MySQL?

DBaaS cloud services allow users to use databases without configuring physical hardware and infrastructure, and without installing software. I’m not sure if there is a straightforward answer, but when trying to find out which solution best fits an organization there are multiple factors that should be taken into consideration. These may be performance, high availability, operational cost, management, capacity planning, scalability, security, monitoring, etc.

There are also cases where although the workload and operational needs seem to best fit to one solution, there are other limiting factors which may be blockers (or at least need special handling).

In this blog post, I will try to provide some general rules of thumb but let’s first try to give a short description of these products.

What we should really compare is the MySQL and Aurora database engines provided by Amazon RDS.

An introduction to Amazon RDS

Amazon Relational Database Service (Amazon RDS) is a hosted database service which provides multiple database products to choose from, including Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server. We will focus on MySQL and Aurora.

With regards to systems administration, both solutions are time-saving. You get an environment ready to deploy your application and if there are no dedicated DBAs, RDS gives you great flexibility for operations like upgrades or backups. For both products, Amazon applies required updates and the latest patches without any downtime. You can define maintenance windows and automated patching (if enabled) will occur within them. Data is continuously backed up to S3 in real time, with no performance impact. This eliminates the need for backup windows and other, complex or not, scripted procedures. Although this sounds great, the risk of vendor lock-in and the challenges of enforced updates and client-side optimizations are still there.

So, Aurora or RDS MySQL?

Amazon Aurora is a relational, proprietary, closed-source database engine, with all that that implies.

RDS MySQL is 5.5, 5.6 and 5.7 compatible and offers the option to select among minor releases. While RDS MySQL supports multiple storage engines with varying capabilities, not all of them are optimized for crash recovery and data durability. Until recently, it was a limitation that Aurora was only compatible with MySQL 5.6 but it’s now compatible with both 5.6 and 5.7 too.

So, in most cases, no significant application changes are required for either product. Keep in mind that certain MySQL features like the MyISAM storage engine are not available with Amazon Aurora. Migration to RDS can be performed using Percona XtraBackup.

For RDS products shell access to the underlying operating system is disabled and access to MySQL user accounts with the “SUPER” privilege isn’t allowed. To configure MySQL variables or manage users, Amazon RDS provides specific parameter groups, APIs and other special system procedures which be used. If you need to enable remote access this article will help you do so

Performance considerations

Although Amazon RDS uses SSDs to achieve better IO throughput for all its database services, Amazon claims that the Aurora is able to achieve a 5x performance boost than standard MySQL and provides reliability out of the box. In general, Aurora seems to be faster, but not always.

For example, due to the need to disable the InnoDB change buffer for Aurora (this is one of the keys for the distributed storage engine), and that updates to secondary indexes must be write through, there is a big performance penalty in workloads where heavy writes that update secondary indexes are performed. This is because of the way MySQL relies on the change buffer to defer and merge secondary index updates. If your application performs a high rate of updates against tables with secondary indexes, Aurora performance may be poor. In any case, you should always keep in mind that performance depends on schema design. Before taking the decision to migrate, performance should be evaluated against an application specific workload. Doing extensive benchmarks will be the subject of a future blog post.

Capacity Planning

Talking about underlying storage, another important thing to take into consideration is that with Aurora there is no need for capacity planning. Aurora storage will automatically grow, from the minimum of 10 GB up to 64 TiB, in 10 GB increments, with no impact on database performance. The table size limit is only constrained by the size of the Aurora cluster volume, which has a maximum of 64 tebibytes (TiB). As a result, the maximum table size for a table in an Aurora database is 64 TiB. For RDS MySQL, the maximum provisioned storage limit constrains the size of a table to a maximum size of 16 TB when using InnoDB file-per-table tablespaces.


Replication is a really powerful feature of MySQL (like) products. With Aurora, you can provision up to fifteen replicas compared to just five in RDS MySQL. All Aurora replicas share the same underlying volume with the primary instance and this means that replication can be performed in milliseconds as updates made by the primary instance are instantly available to all Aurora replicas. Failover is automatic with no data loss on Amazon Aurora whereas the replicas failover priority can be set.

An explanatory description of Amazon Aurora’s architecture can be found in Vadim’s post written a couple of years ago

The architecture used and the way that replication works on both products shows a really significant difference between them. Aurora is a High Availablity (HA) solution where you only need to attach a reader and this automatically becomes Multi-AZ available. Aurora replicates data to six storage nodes in Multi-AZs to withstand the loss of an entire AZ (Availability Zone) or two storage nodes without any availability impact to the client’s applications.

On the other hand, RDS MySQL allows only up to five replicas and the replication process is slower than Aurora. Failover is a manual process and may result in last-minute data loss. RDS for MySQL is not an HA solution, so you have to mark the master as Multi-AZ and attach the endpoints.


Both products can be monitored with a variety of monitoring tools. You can enable automated monitoring and you can define the log types to publish to Amazon CloudWatch. Percona Monitoring and Management (PMM) can also be used to gather metrics.

Be aware that for Aurora there is a limitation for the T2 instances such that Performance Schema can cause the host to run out of memory if enabled.


Aurora instances will cost you ~20% more than RDS MySQL. If you create Aurora read replicas then the cost of your Aurora cluster will double. Aurora is only available on certain RDS instance sizes. Instances pricing details can be found here and here.

Storage pricing may be a bit tricky. Keep in mind that pricing for Aurora differs to that for RDS MySQL. For RDS MySQL you have to select the type and size for the EBS volume, and you have to be sure that provisioned EBS IOPs can be supported by your instance type as EBS IOPs are restricted by the instance type capabilities. Unless you watch for this, you may end up having EBS IOPs that cannot be really used by your instance.

For Aurora, IOPs are only limited by the instance type. This means that if you want to increase IOPs performance on Aurora you should proceed with an instance type upgrade. In any case, Amazon will charge you based on the dataset size and the requests per second.

That said, although for Aurora you pay only for the data you really use in 10GB increments if you want high performance you have to select the correct instance. For Aurora, regardless of the instance type, you get billed $0.10 per GB-month and $0.20 per 1 million requests so if you need high performance the cost maybe even more than RDS MySQL. For RDS MySQL storage costs are based on the EBS type and size.

Percona provides support for RDS services and you might be interested in these cases studies:

When a more fully customized solution is required, most of our customers usually prefer the use of AWS EC2 instances supported by our managed services offering.

  • If you are looking for a native HA solution then you should use Aurora
  • For a read-intensive workload within an HA environment, Aurora is a perfect match. Combined with ProxySQL for RDS you can get a high flexibility
  • Aurora performance is great but is not as much as expected for write-intensive workloads when secondary indexes exist. In any case, you should benchmark both RDS MySQL and Aurora before taking the decision to migrate.  Performance depends much on workload and schema design
  • By choosing Amazon Aurora you are fully dependent on Amazon for bug fixes or upgrades
  • If you need to use MySQL plugins you should use RDS MySQL
  • Aurora only supports InnoDB. If you need other engines i.e. MyISAM, RDS MySQL is the only option
  • With RDS MySQL you can use specific MySQL releases
  • Aurora is not included in the AWS free-tier and costs a bit more than RDS MySQL. If you only need a managed solution to deploy services in a less expensive way and out of the box availability is not your main concern, RDS MySQL is what you need
  • If for any reason Performance Schema must be ON, you should not enable this on Amazon Aurora MySQL T2 instances. With the Performance Schema enabled, the T2 instance may run out of memory
  • For both products, you should carefully examine the known issues and limitations listed here and here

The post When Should I Use Amazon Aurora and When Should I use RDS MySQL? appeared first on Percona Database Performance Blog.


Percona Support with Amazon RDS

Amazon RDS

This blog post will give a brief overview of Amazon RDS capabilities and limitations, and how Percona Support can help you succeed in your Amazon RDS deployments.

One of the common questions that we get from customers and prospective customers is about Percona Support with Amazon RDS. As many companies have shifted to the cloud, or are considering how to do so, it’s natural to try to understand the limitations inherent in different deployment strategies.

Why Use Amazon RDS?

As more companies move to using the cloud, we’ve seen a shift towards work models in technical teams that require software developers to take on more operational duties than they have traditionally. This makes it essential to abstract infrastructure so it can be interacted with as code, whether through automation or APIs. Amazon RDS presents a compelling DBaaS product with significant flexibility while maintaining ease of deployment.

Use Cases Where RDS Isn’t a Fit

There are a number of use cases where the inherent limitations of RDS make it not a good fit. With RDS, you are trading off the flexibility to deploy complex environment topologies for the ease of deploying with the push of a button, or a simple API call. RDS eliminates most of the operational overhead of running a database in your environment by abstracting away the physical or virtual hardware and the operating system, networking and replication configuration. This, however, means that you can’t get too fancy with replication, networking or the underlying operating system or hardware.

When Using RDS, Which Engine is Right For Me?

Amazon’s RDS has numerous database engines available, each suited to a specific use case. The three RDS database engines we’ll be discussing briefly here are MySQL, MariaDB and Aurora.

Use MySQL when you have an application tuned for MySQL, you need to use MySQL plug-ins or you wish to maintain compatibility to support external replicas in EC2. MySQL with RDS has support for Memcached, including plug-in support and 5.7 compatible query optimizer improvements. Unfortunately, thread pooling and similar features that are available in Percona Server for MySQL are not currently available in the MySQL engine on RDS.

Use MariaDB when you have an application that requires features available for this engine but not in others. Currently, MariaDB engines in RDS support thread pooling, table elimination, user roles and virtual columns. MySQL or Aurora don’t support these. MariaDB engines in RDS support global transaction IDs (GTIDs), but they are based on the MariaDB implementation. They are not compatible with MySQL GTIDs. This can affect replication or migrations in the future.

Use Aurora when you want a simple-to-setup solution with strong availability guarantees and minimal configuration. This RDS database engine is cloud-native, built with elasticity and the vagaries of running in a distributed infrastructure in mind. While it does limit your configuration and optimization capabilities more than other RDS database engines, it handles a lot of things for you – including ensuring availability. Aurora automatically detects database crashes and restarts without the need for crash recovery or to rebuild the database cache. If the entire instance fails, Aurora automatically fails over to one of up to 15 read replicas.

So If RDS Handles Operations, Why Do I Need Support?

Generally speaking, properly using a database implies four quadrants of tasks. RDS only covers one of these four quadrants: the operational piece. Your existing staff (or another provider such as Percona) must cover each of the remaining quadrants.

Amazon RDS
Amazon RDS

The areas where people run into trouble are slow queries, database performance not meeting expectations or other such issues. In these cases they often can contact Amazon’s support line. The AWS Support Engineers are trained and focused on addressing issues specific to the AWS environment, however. They’re not DBAs and do not have the database expertise necessary to fully troubleshoot your database issues in depth. Often, when an RDS user encounters a performance issue, the first instinct is to increase the size of their AWS deployment because it’s a simple solution. A better path would be investigating performance tuning. More hardware is not necessarily the best solution. You often end up spending far more on your monthly cloud hosting bill than necessary by ignoring unoptimized configurations and queries.

As noted above, when using MariaDB or MySQL RDS database engines you can make use of plug-ins and inject additional configuration options that aren’t available in Aurora. This includes the ability to replicate to external instances, such as in an EC2 environment. This provides more configuration flexibility for performance optimization – but does require expertise to make use of it.

Outside support vendors (like Percona) can still help you even when you eliminate the operational elements by lending the expertise to your technical teams and educating them on tuning and optimization strategies.


Percona Live Europe Featured Talks: Migrating To and Living on RDS/Aurora with Balazs Pocze

Colin Charles

Percona Live Europe Featured Talk Balazs GizmodoWelcome to another post our series of interview blogs for the upcoming Percona Live Europe 2017 in Dublin. This series highlights a number of talks that will be at the conference and gives a short preview of what attendees can expect to learn from the presenter.

This blog post is with Balazs Pocze, Senior Datastore Engineer at Gizmodo. His talk is titled Migrating To and Living on RDS/Aurora. Gizmodo migrated their platform (Kinja) from a datacenter-based approach to AWS, including the migration of standalone MySQL hosts to RDS/Aurora. In our conversation, we discussed how they achieved this migration:

Percona: How did you get into database technology? What do you love about it?

Balazs: I worked as an Operations/DevOps guy for years before I started working with databases. I guess it happened because I was the person at the company that I worked at the time who dared to deal with the database when something strange happened. Somebody had to hold the hot potato. ?

I love that being a DBA is like being a bass player in a rock band. When you do your job perfectly, no one ever notices you are there – but the entire show depends on your work.

Percona: You’re presenting a session called “Migrating To and Living on RDS/Aurora”. What reasons were crucial in the decision to migrate to a cloud platform? Performance? Less management? Database demands?

Balazs: Actually, we migrated the entire Kinja (our platform) to the cloud, so migrating the database wasn’t a question for a second. We moved to the cloud because we didn’t want to deal with hardware anyway, we need flexibility. In the data center days, we had to size the DC’s to handle all of our traffic at any given moment. This means we had to burn a lot of money on underutilized machines. In the cloud, we can spin up machines when we need more computing power. In conjunction, our hardware just got old enough so that it made sense to consider what was a better idea: buying lots of expensive hardware, keep it running, dealing with the hardware (the majority of the ops team lives on a different continent than our servers!) or simply migrating everything to the cloud. That was simpler and safer.

But we didn’t just migrate to the cloud, we also migrated to RDS – managed database service instead of servers with a database on them. The reason to start using RDS was that I didn’t want to re-implement all of the automation stacks we had on the data centers. That seemed like too much work with too many points of failure. When I checked how to fix those failure points, the entire project started to look like the Deathstar. The original database stack was growing organically in the given data center scenario, and reimplementing it for the cloud seemed unsafe.

Percona: How smoothly was the transition, and did you hit unexpected complications? How did you overcome them?

Balazs: The transition was smooth and, from our reader’s view, unnoticeable. Since the majority of my talk will be about those complications and the ways we solved them, I think it would be best if I answer this question during my session. ?

But there’s a non-exhaustive list: we had to switch back from GTID to old-fashioned replication, we had to set up SSL proxies to connect securely the data center and the cloud environment, and after we had to debug a lot of packet loss and TCP overload on the VPN channel. It was fun, actually.

Percona: What do you want attendees to take away from your session? Why should they attend?

Balazs: This session will be about how we had to change our view of the database, and what differences we met in the cloud compared to the hardware world. If somebody plans to migrate to the cloud (especially AWS/RDS), I recommend they check out my talk, because some of the paths we walked down were dead ends. I’ll share what we found, so you don’t have to make the same mistakes we did. It will spare you some time.

Percona: What are you most looking forward to at Percona Live Europe 2017?

Balazs: Three things: hearing about new technologies, learning best practices, and most importantly meeting up with the people I always meet at Percona conferences. There is a really good community with lots of great people. I am always looking forward to seeing them again.

Want to find out more about Balazs and RDS migration? Register for Percona Live Europe 2017, and see his talk Migrating To and Living on RDS/Aurora. Register now to get the best price! Use discount code SeeMeSpeakPLE17 to get 10% off your registration.

Percona Live Open Source Database Conference Europe 2017 in Dublin is the premier European open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, MariaDB, MongoDB, time series database, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Open Source Database Conference Europe will be September 25-27, 2017 at the Radisson Blu Royal Hotel, Dublin.


How to Configure Aurora RDS Parameters

Aurora RDS Parameters

Aurora RDS ParametersIn this blog post, we’ll look at some tips on how to configure Aurora RDS parameters.

I was recently deploying a few Aurora RDS instances, a process very similar to configuring a regular RDS instance. I noticed a few minor differences in the way you configure Aurora RDS parameters, and very few articles on how the commands should be structured (for RDS as well as Aurora). The only real literature available is the official Amazon RDS documentation.

This blog provides a concise “how-to” guide to quickly change Aurora RDS parameters using the AWS CLI. Aurora retains the parameter group model introduced with RDS, with new instances having the default read only parameter groups. For a new instance, you need to create and allocate a new parameter group (this requires a DB reboot). After that, you can apply changes to dynamic variables immediately. In other words, the first time you add the DB parameter group you’ll need to reboot even if the variable you are configuring is dynamic. It’s best to create a new DB parameter group when initializing your clusters. Nothing stops you from adding more than one host to the same DB Parameter Group rather than creating one per instance.

In addition to the DB Parameter Group, each instance is also allocated a DB Cluster Parameter Group. The DB Parameter Group is used for instance-level parameters, while the DB Cluster Parameter Group is used for cluster-level parameters (and applies to all instances in a cluster). You’ll find some of the MySQL engine variables can only be found in the DB Cluster Parameter Group. Here you will find a handy reference of all the DB cluster and DB instance parameters that are viewable or configurable for Aurora instances.

To run these commands, you’ll need to have the “aws” cli tool installed and configured. Note that the force-failover option used for RDS instances doesn’t apply to Aurora. You should perform either a controlled failover or let Aurora handle this. Also, the group family to use for Aurora is “oscar5.6”. The commands to set this up are as follows:

aws rds create-db-parameter-group
    --db-parameter-group-name percona-opt
    --db-parameter-group-family oscar5.6
    --description "Percona Optimizations"
aws rds modify-db-parameter-group
    --db-parameter-group-name percona-opt
    --parameters "ParameterName=max_connections,ParameterValue=5000,ApplyMethod=immediate"
# For each instance-name:
aws rds modify-db-instance --db-instance-identifier <instance-name>
aws rds reboot-db-instance
    --db-instance-identifier <instance-name>

Once you create the initial DB parameter group, configure the variables as follows:

aws rds modify-db-parameter-group
    --db-parameter-group-name <instance-name>
    --parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate"
aws rds modify-db-parameter-group
    --db-parameter-group-name <instance-name>
    --parameters "ParameterName=max_connect_errors,ParameterValue=999999,ApplyMethod=immediate"
## Verifying change:
aws rds describe-db-parameters
      --db-parameter-group-name aurora-instance-1
      | grep -B7 -A2 'max_connect_errors'

Please keep in mind, it can take a few seconds to propagate changes to nodes. Give it a moment before checking the values with “show global variables”. You can configure the DB Cluster Parameter group similarly, for example:

# Create a new db cluster parameter group
aws rds create-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --db-parameter-group-family oscar5.6 --description "new cluster group"
# Tune a variable on the db cluster parameter group
aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name percona-cluster --parameters "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=2,ApplyMethod=immediate"
# Allocate the new db cluster parameter to your cluster
aws rds modify-db-cluster --db-cluster-identifier <cluster_identifier> --db-cluster-parameter-group-name=percona-cluster
# And of course, for viewing the cluster parameters
aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name=percona-cluster

I hope you find this article useful, please make sure to share with the community!


Percona Live 2017: A Deep-Dive Into What’s New in Amazon Aurora

Percona Live 2017

PostgresPercona Live 2017 is rolling along, and now that everybody got through lunch we’re all recharged and ready for the afternoon. Let’s start it out with Amazon Aurora.

Amazon AuroraOnce of the best-attended sessions was Sailesh Krishnamurthy’s (Senior Engineering Manager at Amazon Web Services) talk on a deep dive into what is new in Amazon Aurora. Amazon Aurora is a fully managed relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. It is purpose-built for the cloud using a new architectural model and distributed systems techniques to provide far higher performance, availability and durability than previously possible using conventional monolithic database architectures.

Amazon Aurora packs a lot of innovations in the engine and storage layers. In this session, Sailesh looked at some of the key innovations behind Amazon Aurora, new improvements to Aurora’s performance, availability and cost-effectiveness and discussed best practices and optimal configurations.

Don’t miss any of tomorrow’s talks! You can find Thursday’s (4/27) session schedule here.

Powered by WordPress | Theme: Aeros 2.0 by