All Questions
Tagged with amazonrds or amazon-rds
985 questions
0
votes
0
answers
53
views
How does AWS Data Migration Service actually work with MSSQL?
I'm very confused as to how AWS Data Migration Service is actually achieving it's replication from an on prem MS SQL 2019 instance to RDS 2019.
I understand from the documentation that tables with a ...
- 31
0
votes
1
answer
826
views
drop database hangs with "Waiting for table metadata lock" in MySQL8 RDS on AWS
We have a large table in a database that a user tried to run DELETE * FROM table on. It was still running the next day so we killed the process.
I tried renaming, dropping and truncating the affected ...
- 126
0
votes
1
answer
158
views
What could cause replication to break on MySQL when replicating from 5.7.40 to 8.0 replica
We have a MySQL primary running on 5.7.40. We are in process of testing the upgrade to 8.0.36 (this is on AWS RDS). We had 2 MySQL-8 replicas and 2 MySQL-5.7 replicas replicating from the primary.
At ...
- 119
0
votes
1
answer
1k
views
Postgres: Investigate RAM usage of a connection
Via pg_proctab(), I can see that the connections from our application can use up to 800MB of RAM per connection in postgres. Usually up to 400MB. (Update to clarify after a comment: These connections ...
- 178
0
votes
1
answer
544
views
How to cancel vacuum process in AWS RDS PostgreSQL
Is there a way to cancel an automatic vacuum in rds postgresql? When running
select pg_cancel_backend(...);
select pg_terminate_backend(...);
with a user that has the rds_superuser role an error ...
1
vote
1
answer
107
views
DROP and TRUNCATE table on MySQL 8.0.35 freezes the DB
We recently upgraded from MySQL 5.7 to MySQL 8.0.35 in AWS RDS.
There is a log table that services write to and we rotate it daily, using RENAME TABLE command.
Later when we perform a DROP or TRUNCATE ...
- 125
0
votes
1
answer
129
views
Select query with 90+ parameters intermittently 20x slower on RDS PostgreSQL instance when using connection pool
I'm running a select statement on a postgresql table (on an RDS instance) in order to sum up values across 2 mil rows. The select statement has about 100 parameters (mostly different countries we want ...
- 101
0
votes
1
answer
474
views
Aurora PostgreSQL TEMP TABLE creation taking more CPU than expected?
I'm running on an Aurora PostgreSQL on a db.x2g.2xlarge instance. Simply creating temporary table showed up as the third highest wait, which surprises me, as I expected it to be one of the lighest ...
- 1,655
1
vote
0
answers
40
views
AWS SQL - High CPU Usage from select * from #spproccol
SQL 13.x will intermittently spike a CPU usage (60-90%). It will stay high so we added AWS Performance Insights. These queries are not something we have defined or used but something is calling them ...
- 11
1
vote
0
answers
237
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.
...
- 11
0
votes
0
answers
20
views
Aurora3 RDS MySQL Stored function/trigger performance when using information_schema
Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers ...
0
votes
0
answers
39
views
What is the correct configuration for an RDS instance to allow for upgrades when the databases are very large?
I've been using Amazon RDS instances for Postgres databses for a while, but I haven't had to do a lot of upgrading on large databases until recently. I hit an issue where one database was hundreds of ...
- 101
0
votes
0
answers
132
views
MySQL - Can FLUSH TABLES Be Triggered Automatically by MySQL Itself?
As it says in the title, I am looking to determine whether MySQL itself will, on its own, issue a FLUSH TABLES command at any point. I have yet to find anything that points to a specific answer to ...
- 23
0
votes
0
answers
9
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 ...
- 115
0
votes
0
answers
144
views
Unable to connect to AWS mysql RDS instance
I created an AWS mySQL RDS instance (free tier), but i am unable to connect to it from workbench or cli. I have enabled public access to the DB while creation and allowed "All traffic" while ...
- 101
0
votes
3
answers
345
views
select count(*) takes too much time in RDS/MySQL during high traffic
Setup:
db.t3.xlarge RDS instance.
MySQL 8.0.33 engine.
Default parameter and options group.
~900k records table.
RDS has no logging enabled at all, and we cannot restart the instance for now to ...
- 3
0
votes
0
answers
137
views
How to fix high px deq slave session stats in Oracle RDS
We are seeing some high percentage of "px deq slave session stats" waits in our Production database.(this maintained on AWS RDS). Due to which outage is happening many times
from searching ...
- 13
1
vote
2
answers
150
views
SQL Server Combine Partioned Backups Files to One?
Scenario:
Every day you receive a SQL Server 2016 database backup set that is composed of multiple partitioned .bak files of size 1GB each:
backup1.bak (1GB)
backup2.bak (1GB)
backup3.bak (1GB)
...
...
- 213
2
votes
1
answer
521
views
Column used in the publication WHERE expression is not part of the replica identity
I'm trying to create a logical replication setup where I have a single large database with smaller read replicas branching of filtered on a sub id.
For example, if I wanted a subset of items pushed to ...
- 123
0
votes
1
answer
160
views
How to atomically update different databases?
I have a lambda function which receives data of newly signed up users from a message queue and insert that same data in two different databases.
Those two databases belong to two different ...
- 103
1
vote
1
answer
127
views
Upgrade Postgres RDS in AWS fails major version upgrade
I have been attempting to upgrade our AWS RDS Postgres from 11.22 to 12.17 and/or 15.5
In both instances I get the following error in the error log:
executing: SELECT datname, datallowconn FROM ...
- 188
1
vote
0
answers
3k
views
AWS RDS Postgres: How to diagnose CheckpointLag and potential slowups using AWS' Monitoring suite?
We are currently hosting a postgres RDS database and our team is noticing slowup in our querying service. I'm noticing a spike in the metric, CheckpointLag and I've been tasked in trying to find where ...
10
votes
1
answer
2k
views
How to add index to a big table with 60M records without downtime?
we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
- 53
1
vote
1
answer
1k
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 (...
- 801
0
votes
2
answers
103
views
Oracle Batch Deletion
I have an Oracle table that has over 1.8 Billion rows, I need to delete up to 260 million of these rows.
I am a Microsoft SQL DBA, so not fully comfortable with the Oracle syntax and best practices ...
- 431
1
vote
0
answers
320
views
RDS MySQL Cluster: Cannot Grant SET_USER_ID
On an RDS MySQL 8.0.33 cluster, I'm trying to grant the SET_USER_ID privilege to a migrations@% User so that that account can change the DEFINER on some views. I'm using the Master account, root, ...
- 111
0
votes
0
answers
30
views
Managed database or docker image with data-volume?
I've mostly used managed database (AWS RDS) for production. I was fiddling with docker and was wondering if it's a good idea to have containerised postgres database with data-volume?
I feel it may not ...
- 101
6
votes
1
answer
3k
views
Adding a Primary Key to a Large PostgreSQL Table with High Traffic
I need to add a primary key to a large PostgreSQL table (approximately 2TB) with high traffic. This is a critical operation, and I'm looking for guidance on how to do it efficiently.
I have already ...
- 247
0
votes
0
answers
124
views
Data validation for RDS PostgreSQL migration
I'm planning to replace a bunch of RDS PostgreSQL databases using this method defined by AWS.
The DMS task will run will be CDC-only. I came up with data validation requirements for the migration and ...
- 129
0
votes
1
answer
289
views
MySQL - AWS RDS - Replication node is stopped
I have a replication node on RDS that when shown on AWS Console it states that there's an error on RDS replication node (ERROR) and it has stopped replicating. What can I do to restart the replica?
...
- 101
1
vote
3
answers
979
views
Sudden spike in BinLogDiskUsage
Two days ago our MySQL RDS instance saw a sudden spike in BinLogDiskUsage, CPUUtilization, NetworkReceiveThroughput and NetworkTransmitThroughput.
The result was a database that was extremely slow to ...
- 13
0
votes
1
answer
583
views
Connecting to AWS RDS succeeds via terminal but fails in pgAdmin 4. Why?
I can establish an SSH tunnel using ssh -L 2222:<RDS-Endpoint>:5432 ec2-user@<EC2-IP> -i ~/.ssh/key.pem and then connect to my RDS using psql "host=localhost port=2222 user=postgres ...
- 103
0
votes
1
answer
206
views
Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?
Is it possible to query Oracle system tables (DBA_USERS,dba_role_privs,dba_tab_privs) Using Db_link?
I am getting ORA-00942: table or view does not exist when tried to query the same.
- 13
0
votes
1
answer
53
views
Amazon AWS credits system for SQL Server Connections
I remember there was some credit system in 2019 in AWS Sql Server, which regulated how many times you could connect per second (minute?), based on how much you paid. Over night, during idle, you would ...
- 216
0
votes
1
answer
243
views
IF EXISTS doesn't stop errors on RDS Postgres 11.19 instance
Recently one of our RDS Postgres databases was upgraded to version 11.19. Since then, we're receiving errors when running a database migration.
I can't replicate this issue locally, it appears to be ...
0
votes
1
answer
579
views
How can I tell which query uses an index in MySQL?
I have investigated my indexes in MySQL using performance_schema.table_io_waits_summary_by_index_usage which has told me that one of my indexes is used in one database environment (Production EU) but ...
- 125
0
votes
1
answer
106
views
Improving bandwidth from RDS postgres
When I do $ time psql -c "select repeat('a', 123456789)" -o /dev/null -U postgres -h hostname I reliably get about 10 seconds, making the transfer speed ~12 MB/sec. I'd like to do better.
I ...
- 103
0
votes
0
answers
102
views
TDS-Protocol with pre-login failure message when connecting to Amazon RDS (SQL Server)
We are trying to connect from an application installed on a virtual machine to a database as part of an AWS RDS in the cloud.
There are several routers and firewalls between the source and the target ...
0
votes
1
answer
613
views
MariaDB large number of sessions waiting on index_tree_rw_lock
I have a MariaDB RDS instance that is mostly saving batched insert statements to 4 tables. These inserts have batches of an average of 200/batch and each statement saves to a single table. However, ...
- 1
1
vote
1
answer
932
views
Understanding RAM Usage, ReadThroughput, and Freeable Memory Metrics in PostgreSQL RDS
I'm facing an interesting issue related to RAM usage, ReadThroughput, and Freeable Memory metrics with my PostgreSQL database on an RDS instance, and I'm hoping to get some insights here.
Two months ...
- 21
1
vote
0
answers
203
views
MySQL -> MariaDB Replication throws Collation Error
We are moving from MySQL on AWS RDS to MariaDB on-premise. To do so without downtime, we planned to create a replication first between the MySQL master and the MariaDB slave and then promote the ...
1
vote
1
answer
1k
views
AWS RDS MySQL upgrade from 5.7 to 8 failing due to ROW_FORMAT
We need to upgrade our DB to version 8, but we're getting an error in the migration log that's preventing us from upgrading.
Creating indexes larger than 767 bytes on tables with redundant row format ...
user3411972
0
votes
1
answer
246
views
How to reflect the change of `max_sync_workers_per_subscription` during replication
I've changed the parameter max_sync_workers_per_subscription in Amazon RDS Postgresql and verified that it changed in the master database:
db=> show max_sync_workers_per_subscription;
...
- 176
0
votes
0
answers
189
views
Amazon RDS MySQL - write throughput limited to 15 MB / s
We are duplicating the data inside a rather Amazon RDS MySQL big table, about 2 GB of data.
We are doing that by having batches of 30 entries moved at a time (INSERT INTO etc SELECT FROM ... LIMIT x ...
- 101
1
vote
1
answer
144
views
Migrate of Encrypt Columns from SQL Server 2005 To 2019 RDS AWS
good morning,
Perform a migration of an On Premise database:
Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10, 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-...
- 125
0
votes
0
answers
414
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
0
votes
0
answers
2k
views
Why we're getting so many idle connections in Postgres
We're running a Flask application with RDS Aurora Postgres and we've started to experience the following issue:
Traceback (most recent call last):
File "/usr/local/lib/python3.7/site-packages/...
- 101
0
votes
2
answers
306
views
Interpreting MySQL (AWS RDS) CPU load and IOWAIT wrt threading model
This is a general question, not to solve a problem: Since MySQL uses one thread per connection. That means if you do IO, and it has to wait for it, the CPU that the thread runs on will be in IOWAIT. ...
- 191
0
votes
0
answers
446
views
How to debug this tuple await events in RDS PostgreSQL
I have been noticing that sometimes we get a lot of open connections for a long time. Enabling enhanced metrics in RDS PostgreSQL I saw the following:
It seems this tuple wait event is making a lot ...
1
vote
1
answer
2k
views
Reduce 'wait/sql/table/io/handler' load of high frequent updates on the same rows
We have a time-series application that also writes all the records to kind of a 'head' table in MySQL. This is somewhat of a legacy, but very interwoven and hard to get rid of. The problem is, that it'...
- 191