Questions tagged [amazon-rds-aurora]
The amazon-rds-aurora tag has no usage guidance.
53
questions
1
vote
1
answer
150
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 ...
- 113
0
votes
0
answers
18
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 ...
- 101
1
vote
0
answers
30
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 ...
- 11
0
votes
0
answers
104
views
Assigning new role to already existing user in RDS
"We have an RDS instance hosted for Aurora PostgreSQL, specifically using the 'db.r5.xlarge' instance type. When we initially set up the RDS instance, a default user named 'rdsadmin' was ...
0
votes
0
answers
157
views
Mutliple sessions on commit status/ aurora_redo_log_flush event in AWS Aurora 2.11.1 - MySQL 5.7
Our database runs on Aurora 2.11.1 - MySQL 5.7 and we have enabled performance insights.
It's usually runs in between 15 to 50 sessions at a given time. According to the performance insights around 20 ...
- 1
0
votes
0
answers
201
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 ~...
- 101
1
vote
0
answers
251
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 ...
- 11
4
votes
1
answer
603
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 ...
- 135
0
votes
1
answer
312
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 ...
0
votes
1
answer
465
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 ...
0
votes
1
answer
808
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 ...
- 3
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/...
0
votes
0
answers
58
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 ...
3
votes
1
answer
1k
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 ...
- 133
0
votes
1
answer
275
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 ...
- 101
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.
...
- 427
1
vote
1
answer
258
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,
...
- 113
1
vote
0
answers
131
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 ...
1
vote
1
answer
1k
views
Row size too large (> 8126) - 5.7.mysql_aurora.2.10.0
CreateTableConstraintStep ddlList =
(CreateTableConstraintStep) dslContext
.createTableIfNotExists(getDSLTableName(List))
.columns(fields)
...
- 11
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 ...
- 135
0
votes
1
answer
1k
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.
...
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 ...
- 101
0
votes
1
answer
521
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.
...
- 101
1
vote
1
answer
506
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 ...
- 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 ...
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 ...
- 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 ...
- 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 ?
- 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 ...
- 131
2
votes
1
answer
3k
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 ...
- 123
2
votes
1
answer
7k
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 ...
- 323
0
votes
1
answer
483
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. ...
- 103
0
votes
0
answers
279
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 ...
- 760
2
votes
1
answer
223
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 ...
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 ...
- 765
1
vote
1
answer
498
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 ...
- 121
-1
votes
2
answers
192
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 ...
- 11
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 ...
2
votes
0
answers
359
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 ...
- 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 ...
- 169
1
vote
1
answer
3k
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,...
- 765
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 ...
- 111
0
votes
0
answers
831
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 ...
- 173
0
votes
0
answers
451
views
AWS aurora mysql is restarted unexpectedly
In our system
one of aurora mysql instances was't available for short period.
because this instance suddenly restarted at 08/14/2019 3:56JST.
we already checked error log for aurora.
but i found ...
0
votes
2
answers
1k
views
Hints on bulk loading 3.6 billion rows to InnoDB on Aurora MySQL
everyone!
I'm struggling for a week to bulk load 3.6 billion rows to an InnoDB table on Aurora MySQL 5.6.10a. This table has one FK to a "Main" table and has 12 columns. The first 1.4 billion were ...
2
votes
1
answer
1k
views
AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason
We have been using Aurora serverless databases in staging and production environments for a few months now, and find the CPU remains in the range 15-20% almost all the time. However in the past week ...
- 21
0
votes
0
answers
695
views
What database should I use locally if I'm going to use Aurora Serverless?
I'm planning to use Aurora Serverless in QA/PROD and then a docker container for running a compatible engine locally. Should I use MariaDB 10.0 or MySQL 5.6?
Database need is low, we're not doing ...
- 101
1
vote
0
answers
729
views
Predicting AWS RDS MySQL Instance Performance
I have a small MySQL db running on AWS RDS t2.small instance.
So far, the CPU usage has been very predictable based on the traffic from my app.
Example:
1000 concurrent users = 15% CPU
2000 ...
- 111
0
votes
1
answer
787
views
Aws RDS cpu Spike to 100% because of running set isolation command and set
My application running with the AWS RDS as a backend.
currently i am doing a performance test.
While doing that i am seeing RDS cpu usage goes 100% after 20min.
While seeing the performance insights ...
- 113
1
vote
1
answer
4k
views
Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12)
Here's some contextual information: I have a multi-tenant, WordPress MU installation running on Elastic Beanstalk, scaled up to 2 instances minimum. These are connected to the RDS database and are ...
- 13