Questions tagged [aws-aurora]
Managed cloud database service from Amazon Web Services, available for Postgres or MySQL.
118
questions
0
votes
1
answer
42
views
Does Aurora Serverless support Babelfish?
If running Amazon Aurora Serverless on PostgreSQL, is it possible to use the Babelfish for PostgreSQL feature?
- 103
0
votes
0
answers
22
views
AWS Aurora Postgres Cluster Extremely High Unbillable I/O on Writer Node
Exactly at 10pm MDT I upgraded the Aurora Postgres Cluster from 11.14 to 11.15
I may have overlooked a database backup still running during that process. Here are the results:
I then decided at 5pm ...
- 1
0
votes
0
answers
13
views
Query running for more time after Aurora postgres minor upgrade from 12.4 to 12.8
I am new to postgres and never dealt with any performance issues.
We have a SQL which was performing better in Aurora postgres 12.4. The SQL was running for less than 1ms. Now after a minor upgrade to ...
1
vote
0
answers
99
views
AWS Lambda to RDS PostgreSQL connection
Hello fellow AWS contributors, I’m currently working on a project to set up an example of connecting a Lambda function to our PostgreSQL database hosted on RDS. I tested my Python + SQL code locally (...
- 11
0
votes
0
answers
18
views
When is the mysql performance schema event "wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done" fired?
I am using Aurora Mysql 5.7.mysql_aurora.2.07.2 and am facing bottlenecks on load testing with a write-heavy workload. On enabling performance insights I noticed that a large number of sessions were ...
- 101
2
votes
1
answer
118
views
Aurora upgrade 2 to 3 / MySql 5.7 to 8.0: schemaInconsistencyCheck
In our attempts to upgrade our Aurora 2 MySql 5.7 DB to Aurora 3 MySql 8.0, we are running into a pre-check that we are unable to resolve. The error is:
{
"id": "...
- 21
0
votes
0
answers
13
views
Does AWS RDS MariabDb supports automatic failover?
We want to use AWS RDS to store our app's data. We are leaning towards MariaDb because of its temporal tables feature.
And hence not chosing AWS Aurora with MySQL. Another reason being even though ...
- 121
0
votes
1
answer
60
views
Fetching large number of rows from large MySQL table
I'm working with an IOT application where we have a bunch of devices sending readings every minute or so, to an AWS Aurora MySQL 5.6 (InnoDB) instance. That instance is a db.t2.medium (2 CPU, 4GB RAM) ...
0
votes
0
answers
36
views
Will a query which uses IS NULL always perform bad on large tables?
I've got the following table :
CREATE TABLE `file` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`track_id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`...
- 113
0
votes
0
answers
11
views
How can we run Federated queries on RDS/Aurora PostgreSQL databases
I am trying to run few select queries to take snapshots on few tables and PG views(like select * from pg_Stat_activity) of AWS Aurora/RDS PostgreSQL databases on each of our instances(around 20) that ...
4
votes
1
answer
151
views
Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS
We had an AWS MySQL RDS t3.xlarge instance with 2000 IOPS. Also, I have a cron job written in java which inserts data in a table: it can insert up to 12M rows daily. Since AWS RDS cost is quite high ...
- 141
0
votes
0
answers
24
views
Where is mysqlpump log file in Linux/Ubuntu located
I am neither a DB administrator, nor a DevOps but I need to migrate a very large table (~500M rows) from one MySQL schema to another. For doing so, I use mysqlpumpbackup tool. Also, since the DB is an ...
- 141
0
votes
0
answers
69
views
Why is read committed Isolation slower than repeatable read?
I have a stored procedure to do a daily archive job:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
insert into _temp_merge_table(.......)
select /*omit...*/
from Order
where ...
- 1
0
votes
0
answers
19
views
Does aurora PostgreSQL use wal_buffers?
Can someone shed some light as to whether aurora postgres uses wal buffers or not? Given the way the writes are handled differently, I would like to understand the same while it is being said in the ...
1
vote
1
answer
90
views
RDS slow queries after few weeks
Since I updated from MySQL 5.6 to 5.7 I started to have very slow queries in my Aurora RDS instance. In my local XAMPP the same queries took a few seconds but in RDS some of them were about 6 minutes. ...
- 113
0
votes
0
answers
73
views
how to get last MySQL binlog file position on instance restore
I'm writing a dr creation script. I do not want to use aurora built-in feature for dr.
what we have been doing until now are the following manual steps:
restore aurora snapshot snapshot
search in the ...
- 373
0
votes
0
answers
23
views
How to compare two MySQL databases?
I will be migrating MySQL databases from Aurora MySQL cluster in one account to another account using snapshot approach of AWS RDS.
I need to perform a validation or comparison test to prove that ...
0
votes
0
answers
157
views
Migrating MS SQL Server Temporal Tables to AWS Aurora PostgreSQL
I'm hoping you can lend some pointers on this problem.
We are soon going to be undertaking a migration from AWS RDS MS SQL Server to AWS Aurora PostgreSQL mainly for cost savings and the ability to ...
0
votes
0
answers
9
views
How to get names of connection pools from DB tables like INFORMATION SCHEMA?
We have multiple services connect to a database using Hikari. Is there a way to find which connection pools are connected to the DB? Would be ideal if we can see the name and count of connections.
- 101
0
votes
0
answers
79
views
Aurora MySQL 5.7 waits
Have an Aurora MySQL 5.7 DB that is currently basically just the destination for a data replication stream (home-grown). The new application that will provide a UI into the data isn't yet live.
We're ...
- 414
1
vote
0
answers
52
views
Does AWS Aurora MySQL not support encrypted binary log replication with RDS MySQL master?
Trying to setup replication between an RDS MySQL (5.6) master with an Aurora MySQL (also 5.6) replica cluster in the same region via binary log replication for a work project. However, this doesn't ...
0
votes
2
answers
96
views
OOM errors when running queries that have a boolean fulltext search?
I am experiencing intermittent oom errors when running queries that have a boolean fulltext search in them.
I am on AWS Aurora (t2.medium).
Here is an example query, considering the table "sets&...
- 117
0
votes
1
answer
27
views
Can set command cause more CPU usage?
Im running AWS aurora 5.7. The application stack is PHP. After moving to Aurora, I noticed that the CPU consumption is really high. When I checked the performance insights, its showing the SET command ...
- 1,688
0
votes
0
answers
26
views
Does a multirow batch insert in Aurora MySQL count as 1 insert?
We modified our application from single row INSERT ... ON DUPLICATE KEY UPDATE to batch 500 records at a time.
I notice in the AWS console, the Insert Throughput shown for our Aurora MySQL 5.6 DB has ...
- 414
1
vote
2
answers
204
views
AWS Aurora Postgres 12 Setting Collation
I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS.
I am trying to set ...
0
votes
0
answers
15
views
After insert trigger is not working on Aurora Writer instance
I have an After insert trigger on aurora writer instance and it is not inserting any records into the audit log table after the record is inserted in the table.
This is my AFTER INSERT trigger ...
0
votes
1
answer
138
views
How to set PostgreSQL's permission when migrate other database to it on AWS?
We want to migrate an Oracle DB to a PostgreSQL DB in RDS with DMS.
There is an official guide for preparision in the PostgreSQL's DB:
https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle2postgresql....
- 147
1
vote
0
answers
166
views
MySQL DB stuck with constantly rolling back transaction but no associated query
Ran into a problem on a website with a very slow site and tracked it down to the database responding very slowly, even for simple select queries. There seems to be spikes of activity every 9-12 ...
- 111
1
vote
1
answer
45
views
Does AWS Aurora include support for the MySQL Plugin API?
Amazon Aurora is marketed as a "MySQL and PostgreSQL-compatible relational database built for the cloud".
Does AWS Aurora include support for the MySQL Plugin API?
- 171
0
votes
0
answers
87
views
RDS Postgres Aurora concurrent materialized view performance tuning advice
I am trying to modify instance settings to speed up a slow running concurrent materialized view refresh. I am increasing maintenance_work_mem and work_mem in session, but these seem to have no effect.
...
- 273
0
votes
0
answers
154
views
Aurora I/O IOPS vs Billed Volume IOPS
Here is a AWS Aurora PostgreSQL 12 database.
The database is an r5.4xlarge and average load is ~16.
Over 3 hours, the average "Write IOPS" was 40,000 and the average "Read IOPS" ...
- 640
0
votes
0
answers
43
views
insert does not work in aurora serverless?
This is in continuation of Aurora Serverless - tables are empty after successful insert, which I asked yesterday. I create a separate question to highlight what seems to be the real problem: I have ...
- 221
0
votes
0
answers
20
views
Aurora Serverless - tables are empty after successful insert
I have MySQL 5.7.12 running on Aurora Serverless, and I've created a database with a table:
mysql> show create database tpcc\G
*************************** 1. row ***************************
...
- 221
0
votes
1
answer
125
views
Index maintenance on pg_catalog tables in AWS RDS
I have had some bad historical usage on my Aurora PostgreSQL instance. Namely, connections that create a lot of temporary objects and some very long running transactions (which limits autovacuum).
Now ...
- 640
3
votes
2
answers
876
views
AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?
I'm using AWS Aurora PostgreSQL Serverless with autoscaling.
It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with ...
- 133
0
votes
0
answers
46
views
Is it possible to use check_user_proxy on AWS RDS?
My top-level goal is to enable two-user (zero-downtime) secret rotation for RDS users. To recap/summarize the approach, you always have two accounts in your DB that have equivalent access. You switch ...
- 101
0
votes
0
answers
61
views
Running long query for Aurora Postgresql in psql
Aurora PostgreSQL 12.4.
I am working on a problem of archiving older table partitions data into s3. I am basically running this statement in a .sh script from a detached screen in a EC2 instance:
...
- 61
0
votes
1
answer
424
views
How can I delete latest 2 binlog files from RDS MYSQL Aurora
I have created a snapshot of Aurora MYSQL database and then restore it. Let's call it clone
Here is the output of the command SHOW BINARY LOGS on the master database
Here is the output of the command ...
- 133
0
votes
2
answers
81
views
Does changing the character set from utf8 to ascii improve consumed space of a CHAR field on mysql?
I have the following table:
CREATE TABLE `tokens` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`s_id` int(10) unsigned NOT NULL,
`a_token` char(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci ...
- 115
0
votes
1
answer
89
views
What is the proper configuration for max open connection, max idle connection and max connection life postgreSQL?
We have an app that serves small to moderate kinds of traffic.
our server spec:
6 node of micro/small aws ec2 for client
1 db.r5.2xlarge for writer
1 db.r5.large for reader
our app/client setting:
...
- 1
2
votes
4
answers
1k
views
Migrating MySQL 8.0.20 database to AWS Aurora
I originally posted this question on stack overflow as I did not know this service existed and someone suggested I may get a better answer here.
I was trying to use the AWS RDS Proxy service and ...
- 121
0
votes
0
answers
617
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 ...
- 101
0
votes
1
answer
1k
views
an error in your SQL syntax; check the manual that corresponds to your MySQL server version [closed]
I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6
while creating a table it is showing me this error
ERROR 1064 (42000) at line 25 in file: 'name.sql': You have an error in your SQL ...
- 69
0
votes
0
answers
32
views
Setup amazon aurora as replica to mariadb master [duplicate]
I am trying to setup replication between MariaDB 10.4.17 and Aurora 5.7.12 MySQL, with Mariadb as master and aurora as replica.
I am following this aws article
I noted the file name and position
+-----...
- 69
0
votes
1
answer
301
views
Optimal data types for UUID and ENUM AWS Aurora for MySQL table DDL
Hi i have a table in MySQL 5.7 Aurora and current data types for the table is
CREATE TABLE `table` (
`ID` varchar(255) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`PATTERN_TYPE` varchar(255) NOT NULL,
`...
- 109
0
votes
2
answers
342
views
Intermittent only_full_group_by errors in AWS Aurora MySQL cluster db
We recently upgraded from MySQL 5.6 to 5.7 and are running into errors related to only_full_group_by sql mode. We are only seeing the problem intermittently. Our database lives in an AWS Aurora MySQL ...
10
votes
1
answer
795
views
UPDATE on big table in PostgreSQL randomly takes too long
I'm trying to figure out why an UPDATE statement takes too long (>30 sec).
This is random, i.e. in most cases it finishes in under 100msec. However, sometimes (randomly) it takes > 30sec to ...
- 125
0
votes
1
answer
49
views
Why is there a writable instance inside a secondary region for MySQL AWS Aurora Replica?
I have created two Aurora MySQL clusters with two nodes in two regions using terraform. The secondary region is set up to replicate from primary region using binlog. This seems to be set up correctly, ...
- 101
0
votes
0
answers
71
views
Use mysqldump for replication?
I have a large production database in Amazon RDS MySQL (billions of rows) that I want to set up as a master, replicating to an Aurora Serverless instance. Unfortunately, it looks like the only way to ...
- 221
2
votes
2
answers
2k
views
MySQL - Can I have quick queries in a table with 200 million rows?
THE PROBLEM
I have following trivial SELECT statement:
select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500;
The query is pretty simple (no join statements, no ...
