Skip to main content

Questions tagged [amazon-rds-aurora]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
25 views

Decrease high response time when reading large table in MySQL

I have a table with around 80~ million rows called parts. I have a simple query like this SELECT line_code, part_number, compressed_part_number, ...[Others columns] FROM parts WHERE (@partNumber = ...
fluid undefined's user avatar
0 votes
1 answer
37 views

MySQL Amazon Aurora - why is CPU low while max vCPU is high?

In MySQL Amazon Aurora, is it normal for CPU to be be low while other metrics are above the max vCPU line?
mstrom's user avatar
  • 133
1 vote
1 answer
141 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
0 votes
0 answers
53 views

Why reading data in Postgres requires lock?

Using AWS Postgres, I am seeing some issues with LWLock:lock_manager. While investigating the issues, I've found this article. I can't understand why READING data requires any kind of lock? And why ...
Maciej Pszczolinski's user avatar
0 votes
0 answers
116 views

Aurora RDS - difference between ConnectionAttempts and DatabaseConnections

So I have read this a few times about these two metrics: ConnectionAttempts and DatabaseConnections. Based on the above, is RDS rejecting lots of connections? Here are the graphs of these two metrics. ...
Abs's user avatar
  • 1
0 votes
0 answers
6 views

Aurora RDS MySQL historical database recovery from historical snapshot and master snapshot?

I was able to simulate a transactional cluster replicating to a historical cluster and it does all the changes, inserts, updates, deletes. I was able to do an archiving delete so those deletes did ...
Dean Hiller's user avatar
1 vote
1 answer
17 views

Aurora RDS async replication to historical MySQL cluster

I was trying to do https://dev.mysql.com/doc/refman/8.0/en/replication-howto-masterstatus.html until the command to pause all writes gets access denied on my test db FLUSH TABLES WITH READ LOCK; Error ...
Dean Hiller's user avatar
2 votes
0 answers
24 views

Aurora mysql throttle during writes

I came across a situation where I need expert opinion. Our application ingests data into table A in aurora mysql 3.02 every 5 seconds. This is a write intensive process. During this time, I am using ...
Falcon's user avatar
  • 101
1 vote
1 answer
717 views

Amazon Aurora RDS (serveless) - How to match an ACU in terms of CPU number and memory

I need to migrate an Amazon RDS back to on-premises envoriment. How to How to match an ACU in terms of CPU number and memory? My instance has: Minimum capacity 1 ACUs (2 GiB) Maximum capacity 10 ACUs (...
Astora's user avatar
  • 791
1 vote
1 answer
521 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
0 votes
0 answers
156 views

SKIP LOCKED ... FOR UPDATE degrading performance with other unrelated activity in the write replica

We have a workload running in RDS Aurora Postgresql (v11) modelled around the transactional outbox pattern. Roughly, X workers will open a transaction, query a table for the most recent non-locked row ...
ChuckE's user avatar
  • 101
1 vote
0 answers
37 views

Is there any tool that can be used to backup and restore individual schemas and not the entire database?

Can someone help me with any tool that can be used to incrementally back up the individual schemas and restore them instead of backing up the entire DB? We need this for the Aurora MySQL DB RDS ...
Omkar's user avatar
  • 11
0 votes
0 answers
347 views

AWS RDS Postgres: How to see which query/process is using memory

Running a RDS Aurora Postgres cluster with two r6g.2xlarge instances that started fluctuating in memory usage around 6:50pm EST yesterday. Typically we are running with ~16gb headroom but it dips to ~...
BillPull's user avatar
  • 101
1 vote
0 answers
458 views

AWS RDS Postgres configuration for database disk size >1TB and almost 1B records?

For our applications, we opted for Amazon Aurora Serverless v2 (with PostgreSQL 13.8) because of its ability to automatically scale up and down. The ACU configurations are 16 - 128 (the highest it can ...
Sotosoul's user avatar
4 votes
1 answer
1k views

AWS Aurora Mysql seemingly not picking PRIMARY or secondary index

Relevant System Info: Aurora Mysql 8.0.mysql_aurora.3.03 rg6.xl instances (1 writer, 2 read replicas) Total size:5.5TB (all databases combined, or just looking at the most recent Snapshot) I have been ...
Randoneering's user avatar
0 votes
1 answer
386 views

Upgrade Aurora from 5.6 to 5.7.mysql_aurora.2.11.1 getting tons of deadlocks and lock wait timeouts, maybe 20-30x what they were before the upgrade

Our application logs are showing an excessive amount of lock wait times, and deadlocks for queries that have not had issues previously after upgrading from 5.6 to 5.7 MySQL in AWS Aurora. Are there ...
user2966697's user avatar
0 votes
1 answer
790 views

Aurora MySQL: Delete SQL Takes Forever to Finish

I have an Aurora MySQL 5.7 running on a non-production test instance (r5.2xlarge). I have a "simple" delete where I am trying to execute and it has been running for 72 hours already! I like ...
Brian Estrada's user avatar
0 votes
1 answer
2k 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
3 votes
1 answer
3k 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
0 votes
0 answers
71 views

RDS Size Larger than DB Size after Upgrade

I have an Amazon cluster that's currently at 14TB, when my DB size is less than 1GB. This only started after I upgraded the Aurora/MySQL version to 2.08.3/5.7. It started slowly ramping up the size ...
Goblin Girl's user avatar
3 votes
1 answer
2k views

Postgres grant issue on select from view, but not from base table

I have a problem I can't work out. I am using the postgres account on my instance, and I have some failures in selecting from a view, where can select against the base table fine. Let me run through ...
polyglot's user avatar
  • 133
0 votes
1 answer
349 views

Fast Select from a table with ~300 columns with about 200 mil static rows

I have a table with around 300+ columns which contain Demographics data with various attributes like Name, Address, State, Zipcode, Sex, education etc. The table has around 200mil rows. The table ...
Rohit Agre's user avatar
3 votes
2 answers
4k 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
  • 437
1 vote
1 answer
311 views

Force Index does not work on aws mysql

I have a table sales with a compound index on (user_id, eastern_date, state) CREATE TABLE sales ( id int not null auto_increment, user_id int not null, eastern_date date not null, ...
RSHAP's user avatar
  • 113
1 vote
0 answers
148 views

Aurora Serverless 2 MySQL 8.0 doing a mysqlpump to dump users

Recently i ask about using roles in MySQL 8.0 using aurora 3.0 in a Serverless 2 RDS cluster. They answered me that the correct way to do that is using mysqlpump with the --users parameters to dump ...
Mariano Dabove's user avatar
1 vote
1 answer
2k views

Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0

CreateTableConstraintStep ddlList = (CreateTableConstraintStep) dslContext .createTableIfNotExists(getDSLTableName(List)) .columns(fields) ...
will mannix's user avatar
0 votes
1 answer
3k views

Analyzing queries with high disk IO

RDS Aurora PostgreSQL 10.14 instance db.r5.4xlarge. I'm trying to figure out some high RDS IO costs in my machine. I'm looking at the pg_stat_statements and asking whether the following query make ...
Cowabunga's user avatar
  • 135
0 votes
1 answer
2k views

Slow query Mysql with Order By and JOIN

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. ...
Santiago Correa's user avatar
0 votes
0 answers
1k views

AWS DMS Task failed with error: Error executing source loop; Stream component failed at subtask 0

I want to migrate my PostgresDB hosted in Citus cloud service to AWS RDS Aurora Postgres. I am using AWS DMS service. Have created task but getting following errors: Last failure message Last Error ...
Ashish Karpe's user avatar
0 votes
1 answer
624 views

Parameter innodb_flush_log_at_trx_commit not available in AWS Aurora 5.7

I'm trying to modify the parameter innodb_flush_log_at_trx_commit on a RDS Aurora 5.7 instance from the AWS RDS Web Console and when I look through the parameters in the database I can not find it. ...
JohnnyAce's user avatar
  • 101
1 vote
1 answer
600 views

Aurora MySQL: how do I keep binlogs around after reboot?

We have a request to enable binlogs on an Aurora MySQL instance and to keep those logs around for a certain amount of time. To test, we enabled binlogging with binlog_format=ROW and set our retention ...
Nate H's user avatar
  • 31
0 votes
0 answers
1k views

AWS DMS migration not migrated Foreign keys, sequences and indexes got renamed

I have used DMS for AURORA PostgreSQL to PostgreSQL SCHEMA Migration. Migrate Data only type is used for DMS activity. After a Full load of Data, all tables were created on the target endpoint with ...
aditya Mehta's user avatar
1 vote
0 answers
3k views

How can I downgrade an AWS Aurora MySQL 5.7 2.09.2 to an earlier 5.7 version?

During a test, I applied a branch of our Terraform description that upgraded a couple Auroras from 5.7 2.04.x to 5.7 2.09.2 without losing the data (as expected). When attempting to apply the main ...
rbanffy's user avatar
  • 111
3 votes
2 answers
3k views

Performance issues from RDS Aurora 5.6 to Aurora 5.7

Recently upgraded one of my RDS instances to aurora mysql 5.7 from 5.6 and I've had to triple the instance class while I try to resolve this (cpu eventually pegs and never declines). Most reads are ...
Kisaragi's user avatar
  • 131
0 votes
1 answer
1k views

AWS Aurora Mysql different instance types for writer/reader

I have a db.r5.large for the master/writer. I want to add some readers. Do they need to be the same instance type as the master? Or can i add for example 3x db.t3.medium ?
AFRC's user avatar
  • 101
1 vote
2 answers
2k views

Which AWS RDS; current t3.medium over CPU baseline, but not sure what's the next best option

I currently have one RDS instance, db.t3.medium running Aurora MySQL. Due to increase workload (expected), the CPU is, on average running just over its baseline and therefore in about 10 days will run ...
rjbathgate's user avatar
2 votes
1 answer
4k views

Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)? [duplicate]

We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally ...
Andrey Mormysh's user avatar
2 votes
1 answer
9k views

Is it possible to add Postgres extensions to AWS Aurora?

I am trying to futureproof a Postgres database so it can be transferred to AWS Aurora. I am using the guide extension uuid-ossp. Is it possible to add PostGres extensions to AWS Aurora? In ...
mikelus's user avatar
  • 323
0 votes
1 answer
548 views

Aurora vacuum not deleting frozen empty pages?

I recently ended up deleting most of the data in one of our tables, on purpose. Normally the table is append-mostly, with a short window during which updates might happen after the original insert. ...
Tuure Laurinolli's user avatar
0 votes
0 answers
309 views

Why isn't autovaccum running/completing?

My PostgreSQL instance (AWS Aurora) isn't autovacuuming. Or it is, but it is somehow being interrupted or stopped. I'm not canceling any autovacuum queries (though I suppose AWS itself could be). The ...
Paul Draper's user avatar
2 votes
1 answer
239 views

Large MySQL database dump/restore [closed]

Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds ...
Gheorghe  Isac's user avatar
0 votes
1 answer
2k views

Create row level trigger for Partitioned table

I have created a partitioned table (base), and an audit table for it(base_audit) and then created child tables (c1,c2,c3,..cn). To populate entries in base_audit, I have created a function and a row ...
RBB's user avatar
  • 785
1 vote
1 answer
576 views

Limiting user access by subnet to Amazon Aurora PostgreSQL

How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but ...
Miles Elam's user avatar
-1 votes
2 answers
210 views

MySQL Select Query not using Required Index

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query. When I saw the Explain Plan , Optimizer is not ...
user3865748's user avatar
0 votes
0 answers
1k views

SSHtunnelForwarder to Linux Box, then connect to Postgres RDS (AWS)

I'm trying to write a python script to update DDL in a Aurora RDS (postgres) on AWS. In Order to do this, I need to SSHTunnel to a linux box that sits at the edge of the VPC, then do another jump to ...
Eshara Mondal's user avatar
2 votes
0 answers
360 views

Migrate cluster Aurora RDS postgres from 9.X to 10 without downtime

Ok, this question is to find some strategy or tool that helps me to migrate from cluster Aurora RDS postgres from 9.6 to 10 without downtime. What I find until now Aurora RDS is allowed to do major ...
Krismorte's user avatar
  • 554
1 vote
0 answers
1k views

Constraint creation on a large table is too slow

There's a table with 723M rows, large amount of data and not partitioned. We're adding a constraint to the table as follows: ALTER TABLE ONLY my_table ADD CONSTRAINT partial_constraint EXCLUDE USING ...
Pavanraotk's user avatar
1 vote
1 answer
4k views

Why and When the pg_stat_activity's most of the columns are null?

Recently, I have noticed that when I query the pg_stat_activity for few users most of the columns are [null] (pgAdmin 4) in the result. Only thing I can see is datid, datname, pid, usesysid, usename,...
RBB's user avatar
  • 785
1 vote
1 answer
1k views

mysql/aurora not immediately seeing results of committed transactions

In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The ...
Inukshuk's user avatar
  • 111
0 votes
0 answers
902 views

AWS DMS: Table Mapping?

We are moving from SQL Server to AuroraDB (mySQL). Both our source database (SQL Server) and our target (AuroraDB) are on AWS RDS so we are naturally using DMS for the migration. We make heavy use ...
Nick's user avatar
  • 173