All Questions
1,006 questions
0
votes
0
answers
26
views
AWS RDS MySQL read replica's failover and standby instance's failover
This question is for AWS RDS MySQL
I have recently started studying RDS, trying to study from the documentation as well as other sources. For following scenario I am unable to understand certain ...
- 226
0
votes
1
answer
128
views
Unable to start RDS replication to MariaDB RDS instance from AuroraDB for MySQL instance
I am following this guide in order to migrate MariaDB RDS instance into AuroraDB for MySQL.
https://aws.amazon.com/blogs/database/migrate-self-managed-mariadb-to-amazon-aurora-mysql/
Reduce downtime ...
- 142
1
vote
0
answers
191
views
AWS RDS MySQL "manual" read replica in "Waiting for dependent transaction to commit" state but lag is zero
I have an SOP I use to upgrade AWS RDS instances running MySQL with no downtime that I have been following since 2021. I'll outline my method below, but it involves manually hooking up a new master ...
- 111
2
votes
0
answers
168
views
'Deadlock found when trying to get lock; try restarting transaction' in AWS RDS MySQL 8.0.36 after upgrading from MySQL 5.7.42. Django v4.1.6
Following an upgrade of our AWS RDS instance from MySQL version 5.7.42 to version 8.0.36, we are encountering persistent deadlock issues across concurrent transactions, affecting even basic read ...
- 21
0
votes
0
answers
73
views
AWS RDS SQL Server 2019 Standard edition, everyday 3 am getting errors "insufficient system memory in resource pool 'internal' to run this query"
I have AWS RDS SQL Server 2019 Standard edition with 128GB RAM, everyday 3 am getting errors
insufficient system memory in resource pool 'internal' to run this query
There are few jobs that run at a ...
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
827
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
545
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
2
votes
1
answer
174
views
Problems getting SQL Server linked server to work in web-based admin utility on Windows Server 2022
I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records ...
- 21
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
522
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
0
votes
1
answer
16
views
Ram Usage of Db rapildy increasing how free up it
In my db it is 32 gb ram but it fill now 24 gb rapidly. After hiiting the 32 mark the db restarted automatically . I have set the buufer size 27gb. then also it increasing.How do i free up the ram aor ...
Part Gurav
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
1
answer
331
views
aurora mysql index creation challenges on large tables
I have a table of yearly data [size 500GB = ~1 billion rows] in aurora mysql 3.02.2. requirement is to migrate this table to another aurora mysql [PROD] using DMS. we initiated the DMS task with data ...
- 11
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
1
answer
137
views
Best way to "stop" Aurora RDS PostqreSQL read instance during off-peak hours?
We have an r6g.large write instance in our cluster which is perfectly enough for us during off-peak hours which is about 16 hours a day on workdays.
But during the 8 peak hours we would need a larger ...
ak93
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
290
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
584
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
0
answers
127
views
How to install Docker and Postgres on AL2023
What I want to do:
Install Docker and use a Postgres image to create a database to use with a Nest.js tut
What i have tried:
I have installed docker using:
yum install docker
Which yields when I do ...
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