Questions tagged [aws-aurora]

Managed cloud database service‎ from Amazon Web Services, available for Postgres or MySQL.

Filter by
Sorted by
Tagged with
0 votes
0 answers
12 views

on-prem sybase to aws aurora

I want to migrate from on-Prem Sybase to AWS Aurora Postgres database. I came across tools like AWS SCT and AWS DMS, but I am not sure , if the source Sybase database should be in AWS cloud as a pre-...
Pavankumar's user avatar
1 vote
1 answer
54 views

Extremely slow COUNT query on Aurora Postgress Serverless v2 with two tables

I'm designing a web application where sellers can offer their cars, banks provide various financing offers (e.g. 36 months, 25% downpayment, 25% final payment). Buyers come to this web app and they ...
bdadam's user avatar
  • 111
0 votes
1 answer
29 views

Using logical replication to migrate a large Aurora database from AWS to on-premises PostgreSQL

After 5 years and many $$$ spent on keeping our data in an AWS RDS Aurora PostgreSQL cluster, we now want to bring the 12 TB of data back to an on-premises PostgreSQL instance, specifically a Crunchy ...
Robert's user avatar
  • 13
0 votes
1 answer
34 views

What is the difference between CREATE INDEX and CREATE INDEX CONCURRENTLY in PostgreSQL?

I would like to know what the difference is between CREATE INDEX and CREATE INDEX CONCURRENTLY (if any) after the creation of the index has built. I have been having a discussion with a colleague ...
Aaron Gonzalez's user avatar
0 votes
0 answers
15 views

Does AWS Aurora MySQL Load Balance Reads Across Availability Zones?

I am running an AWS Aurora MySQL multi-AZ cluster with four nodes in two availability zone reader A and writer C are in one AZ, and reader B and D are in another AZ. It seems like reader A is ...
db_guy's user avatar
  • 1
0 votes
0 answers
63 views

AWS Blue/Green stuck at "Replica creation is canceled due to external replication"

I'm trying to perform a Blue/Green upgrade of PostgreSQL 11.21 in AWS RDS. The DB cluster has no replication slots. However every attempt failed in Incompatible-create state and the log message is &...
Aleksandar's user avatar
0 votes
0 answers
28 views

Slow execution even with proper indexes defined

I am using AWS Aurora Serverless V2 with MySql. I have a table with 10M rows as shown below. CREATE TABLE 'VCont' ( 'id' varchar(64) NOT NULL, 'version' int NOT NULL, 'colId' bigint unsigned ...
Ravindra's user avatar
1 vote
1 answer
148 views

Why would reads block other reads in Aurora PostgreSQL?

We're running an AWS Aurora PostgreSQL version 13 database. The other day, I was trying to set up a backup job to run from the read replica, and I ended up encountering the error "User was ...
Brad's user avatar
  • 113
1 vote
0 answers
46 views

MySQL slow running query - Need help with tuning

I am using the Aurora MySQL Serverless V2 database and facing an issue with the below query taking longer. In the end, I have listed different approaches that I tried to fix this. The table has around ...
Ravindra's user avatar
0 votes
0 answers
21 views

Is it possible to backup AWS RDS Aurora MySQL per database by any AWS services?

I have several AWS RDS Aurora clusters, and user data is stored in different database. Currently I have my own script to backup per database, but I am considering to backup through AWS provided ...
Yuk Chan's user avatar
0 votes
0 answers
18 views

How do I temporarily enable mysql.general_log in AWS Aurora Serverless V2?

I am trying to debug an issue and I just need to see the general log of a mysql instance. Is there any way of temporarily enabling or routing the logs to a file/cloudwatch for a minute or so? ...
Archimedes Trajano's user avatar
1 vote
1 answer
46 views

Can't use performance_schema in my mysql server

I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking ...
Paras Sahu's user avatar
0 votes
0 answers
209 views

TempTable Memory Storage Engine Issue

I have a db.r5.2xlarge (64GB) AWS Aurora-MySQL instance (8.0.26 equivalent) and I have a long-running SQL which gives this error: ERROR 1114 (HY000) at line 1: The table '/rdsdbdata/tmp/#sqlfe_130_0' ...
Brian Estrada's user avatar
0 votes
0 answers
97 views

mysqldump-like process for Amazon Aurora MySQL?

I believe that mysqldump does not work with Amazon Aurora MySQL. This is a bit of a problem for us as we used use that tool - before moving to Amazon hosting - to extract information across tables to ...
phil's user avatar
  • 153
0 votes
1 answer
145 views

High CPU Usage and MySQL Server Inaccessibility during Magento flat reindexing

We use the following configuration: Engine: Aurora MySQL v8.0 Instance Type: db.r6g.4xlarge vCPU: 16 Memory: 128.0 GiB Role: Writer Instance I'm encountering a critical issue during the reindexing ...
Winfle's user avatar
  • 1
0 votes
1 answer
97 views

Charset Questions on Aurora-MySQL 5.7 as a Replica to a Aurora-MySQL 8.0 Primary

We are planning to upgrade from Aurora MySQL 2.10.2 (MySQL 5.7 equivalent) to Aurora MySQL 3.03.0 (MySQL 8 equivalent). One of the rollback plans that we are considering is having another Aurora MySQL ...
Brian Estrada's user avatar
1 vote
0 answers
97 views

Ways to reduce latency in importing data from csv files into MySQL Workbench when connected to an RDS MySQL db Instance

This question is a follow-up to this question I asked on Stack Overflow yesterday after answering my own question by creating a Aurora MySQL db Instance and establishing a connection between it and ...
Marlen's user avatar
  • 227
0 votes
0 answers
167 views

Create index on big table increase IO:XactSync wait event for other sessions

AWS Aurora (postgres 11.9) after downgrading instance from db.r6g.8xlarge to db.r6g.4xlarge instance and enabling logical_replication I'm trying to rebuild indexes (cause of bloat) for table size 30GB ...
Nevermind's user avatar
3 votes
1 answer
546 views

Aurora / PostgreSQL Mass Update on 100 Million Rows Coming to a Halt

During the migration of a 300 million rows table I have encountered a strange behaviour on our AWS Aurora database: Around 25 minutes into the migration, the total IOPS of the database dropped to ...
Philipp Bzdok's user avatar
0 votes
1 answer
362 views

Postgresql Sequence Hopping

I have recently migrated (dump and restore) my database from AWS RDS PostgreSQL v12 to Aurora Serverless v2 PostgreSQL v14.6. The issue is we have setup sequencing in a table on old RDS, now in the ...
sam's user avatar
  • 11
1 vote
1 answer
245 views

Time zone conversion in DMS from DB2 to Aurora Postgres

Challenge We are using AWS DMS to convert data from IBM DB2 into AWS Auroa Postgres IBM DB2 server is set to Eastern time (ET) zone. Timestamp related data inside DB2 is therefore in ET. Findings AWS ...
Padraic Renaghan's user avatar
1 vote
0 answers
72 views

How to encrypt a production unencrypted RDS Aurora MySQL 8 cluster

I have a running RDS Aurora MySQL 8.0.23 cluster running in production. The database is unencrypted and I need to enable encryption for it. As far as I understand, this is not possible to do directly. ...
revy's user avatar
  • 111
2 votes
1 answer
612 views

Creating indexes in an amazon aws aurora mysql does have a cost?

I am looking for informations without finding them. If I create indexes in a aws aurora mysql instance, do I have to pay? The only things I found is that you pay for all I/O operations (and for ...
Giacomo M's user avatar
  • 129
1 vote
0 answers
87 views

Assign privileges to a postgres user

I am working on creating a postgres user and assign certain privileges to the user. These are privileges to execute certain functions with the end goal of being able to replicate data or change-data-...
Abhi's user avatar
  • 111
1 vote
1 answer
527 views

Aurora MySQL 5.7 to 8.0 Upgrade Issue with general_log_backup

I am upgrading our Amazon Aurora from 5.7 to 8.0 and I got this error in the precheck logs. 20) Tables recognized by InnoDB that belong to a different engine Error: Following tables are ...
bichonfrise74's user avatar
0 votes
0 answers
76 views

Can we use AWS SCT (Schema Conversion Tool) with AWS DMS for ETL and migrating data from MySQL to Amazon Aurora for MySQL?

I want to migrate data from on-premises MySQL/MySQL RDS to amazon Aurora for MySQL. While migrating, I need to convert schema as well as do some ETL to get required schema and data. In ETL I need to ...
Sagardevd's user avatar
0 votes
0 answers
169 views

Postgres/Aurora query response time unexplained

I have AWS Postgres Aurora RDS version 11 running with db.r6g.12xlarge instances in a 3-node cluster with 1 writer, 2 readers. It should be noted though that this also occurs on our on-premises Ubuntu/...
Diane's user avatar
  • 141
2 votes
1 answer
454 views

How to reap benefits of parallelism for PostgreSQL DELETE?

I have a fairly niche use case of a (AWS Aurora) PostgreSQL database and we are running into performance issues that I have discovered a "clever hack" (not actually clever) to work around ...
Alistair Hutton's user avatar
2 votes
3 answers
439 views

Is it safe to enable Fast DDL in Production

We have a Database running Aurora MySql V5.7. We would like to enable Fast DDL to improve the speed of Adding a Column to a large database. According to the documentation, this feature is Enabled in ...
Captain Cool's user avatar
0 votes
1 answer
788 views

pg_stat_statements slows down the database

I have an aws aurora postgresql cluster running posgresql 13.7 engine. The cluster is using the default parameter group. These are the default settings used for pg_stat_statements in the parameter ...
mario's user avatar
  • 3
0 votes
1 answer
268 views

Creating a multi-region active-passive DR plan for Aurora MySQL?

I'm trying to create a disaster recovery plan for a cost efficient, maintainable and with little down for Aurora MySQL. I want two read/write databases in two different regions, they can be separate ...
CKT's user avatar
  • 101
0 votes
1 answer
146 views

Why would a group by return multiple groups with the same value?

In the query select pricing_date,count(*) from prices where pricing_date>='2022-12-01' group by pricing_date; I receive results with multiple pricing_date groups of the same date: +-------------...
enharmonic's user avatar
0 votes
1 answer
146 views

Can PostgreSQL bulk update cause issue in Logical replication?

I use AWS Aurora PostgreSQL. PS version:13(updated the question) We use Logical slots(wal2json) to replicate to the data warehouse. Recently one of the Bulk updates queries updated 40 million rows. ...
TheDataGuy's user avatar
  • 1,910
2 votes
2 answers
251 views

Creating a table from a GROUP BY uses a lot of temporary disk space - can it be avoided?

I've got a table with ~2 billions rows of data, and I would like to make another table with some aggregates. It looks like PostgreSQL uses temporary disk space for these queries. I can create the ...
Michal Charemza's user avatar
0 votes
0 answers
637 views

Aurora PostgreSQL doing high amount of IO:SLRURead irrespective of the shared_buffers size

I have a table that is about 5-6 GB in size. Whenever my application is doing SELECT queries on this table, the performance insight shows a high amount of IO:SLRURead wait event. I've tried this with ...
mrsud's user avatar
  • 95
1 vote
0 answers
539 views

Does the "max vCPU" limit only apply to the "CPU" wait state?

In the Average Active Sessions chart of Performance Insights for Aurora MySQL 8, there is a horizontal Max vCPU (at 16, in my case). Now I wonder if this limit applies only to the CPU wait state (...
Evgeniy Berezovsky's user avatar
3 votes
1 answer
2k views

How to performance tune high /wait/io/redo_log_flush values in "commit"

According to the "Top SQL" view in AWS Performance Insights, commit tops the list of SQL statements in terms of average active session (AAS). And most of the time seems to be spent in wait/...
Evgeniy Berezovsky's user avatar
1 vote
2 answers
333 views

Automating MySQL replication using snapshots on AWS RDS (or Linux)

I'm currently working on a BASH script to build a new Aurora cluster based on the latest snapshot of an existing cluster, then set up MySQL replication from the old one to the new. In the past I've ...
Swechsler's user avatar
  • 133
3 votes
2 answers
3k views

What's the advantage of sharding in AWS Aurora?

AWS' RDS docs on multi-master Aurora state the following: In an Aurora multi-master cluster, each shard is managed by a specific DB instance, and a DB instance can be responsible for multiple shards. ...
rinogo's user avatar
  • 427
0 votes
1 answer
950 views

WAL file retention in Aurora Postgres

I’m planning to create a bunch of Aurora Postgres clusters with logical_replication enabled, so I would not have to reboot my clusters once will need to enable logical_replication for future ...
Omri's user avatar
  • 343
-1 votes
1 answer
83 views

AWS Aurora Performance Insight Metric

I notice there are Blk reads and Local blk reads in performance insights, but there doesn't seem to be any documentation from both AWS and PosgrestSQL explains the difference between them. Does anyone ...
jy l's user avatar
  • 1
1 vote
2 answers
697 views

How to speed up SHOW BINARY LOGS

I am working on migration from mysql 5.7 to mysql 8 (AWS Aurora). As part of the migration I set up AWS DMS tasks and I came to notice each task takes more and more CPU from the writer. The underline ...
Nir's user avatar
  • 513
1 vote
0 answers
308 views

Explain Analyze output has much higher execution time than the actual time

Execution time on Aurora postgres is much higher than the actual query execution time from the line 1 of the explain analyze output. actual time = 0.408..0.415 Planning time = 2.293 ms Execution ...
Mahesh's user avatar
  • 111
0 votes
1 answer
28 views

Monitring Aurora

We are currently migrating from SQL server to RDS Aurora. I am using Redgate to monitor the SQL Server database. My manager recommending New Relic for RDS Aurora, however, I don't feel Newrelic deep ...
sebeid's user avatar
  • 1,381
0 votes
0 answers
247 views

Best way to duplicate an huge Amazon Aurora 2 (MySQL 5.7) DB into an Amazon Aurora 3 (MySQL 8.0)?

I would like to upgrade to Amazon Aurora 3 (MySQL 8.0) an Amazon Aurora 2 (MySQL 5.7). In-place upgrade isn't available, Snapshot upgrade isn't possible neither because the production machine has the ...
DarkCenobyte's user avatar
3 votes
0 answers
72 views

Is there any way to optimize this query (generated by Drupal)?

I have an older system built on drupal, and there is a particular (generated) query that takes a long time - never completing in some cases. The query: SELECT node.nid, node.created, node....
chris's user avatar
  • 1,202
1 vote
0 answers
504 views

Is there a way to get AWS Aurora Mysql 5.7 compatible to cause timestamps to be updated to current timestamp when null is explicitly passed in?

All examples are technically from AWS RDS Aurora Mysql compatible, however I believe the issue is the same for non-aws mysql. In Mysql 5.6 and 5.7 with the exact same values for sql_mode (the 5.6 ...
Robert's user avatar
  • 111
2 votes
1 answer
286 views

MySQL Query slows down on concurrent requests to different subpartitions of a partitioned table

We have partitioned a large table (with over 100 million records) into multiple partitions and subpartitions in our Serverless Aurora to query different subpartitions in parallel. But we find that ...
PrathapB's user avatar
  • 121
0 votes
1 answer
35 views

Slow query optimize help

Db is on Aurora cluster. Added indexes. removed functions from where clause. rewrote some logic. Got number of records to measurable limit, but still the query is performing slow. Attaching query plan ...
Sddesh's user avatar
  • 1
0 votes
1 answer
474 views

Why does my JDBC keep setting session variables?

I'm looking at the Performance Insights for MySQL Aurora on AWS and among the top queries I see: SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ SET SESSION TRANSACTION ISOLATION LEVEL READ ...
Johnny's user avatar
  • 140