Questions tagged [aws-aurora]
Managed cloud database service from Amazon Web Services, available for Postgres or MySQL.
150
questions
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-...
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 ...
- 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 ...
- 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 ...
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 ...
- 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 &...
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 ...
- 11
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 ...
- 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 ...
- 11
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 ...
- 1
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?
...
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 ...
- 11
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' ...
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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
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 ...
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 ...
- 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 ...
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. ...
- 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 ...
- 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-...
- 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 ...
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 ...
- 21
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/...
- 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 ...
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 ...
- 21
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 ...
- 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 ...
- 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:
+-------------...
- 193
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.
...
- 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 ...
- 171
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 ...
- 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 (...
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/...
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 ...
- 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.
...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 209
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....
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 140