All Questions
3,206 questions
0
votes
1
answer
1k
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 ...
- 114
1
vote
1
answer
699
views
Cascading foreign key DELETE with tables that have RESTRICT constraints
I have a database that stores recipes for multiple organizations. So every table has an org_id pointing to the orgs table to allow each organization to have their own set of recipes and ingredients.
...
- 113
0
votes
1
answer
670
views
MySQL doesn't start
Recently I had an issue with MySQL for an unknown reason. It didn't start and produced an error message, so I decided to update it from 8.0.31 to 8.0.32.
The Windows version is 10 Pro for Workstations ...
-1
votes
1
answer
4k
views
How to avoid deadlock inside transaction? [closed]
I have a transaction that wraps two code paths that update/insert related tables in the opposite order. Outside of a transaction I understand why that would cause a deadlock, but why does it happen ...
- 195
3
votes
1
answer
383
views
Postgres RDS session with backend type "startup", empty activity and no query holds locks on tables
I'm running Postgres 12 on AWS RDS and monitoring it with pgAdmin. This is a read-only replica of the production database.
I have a weird session with not much info, only: backend_type = startup, PID, ...
- 133
2
votes
2
answers
430
views
Operational Error on MySQL AWS RDS Instance
The heading pretty much describes it all, here are some more information on the Amazon RDS I am using.
Engine: MySQL Community 8.0.28
Class db.r6g.large
Multi-AZ no. The data volume is very small, ...
- 73
1
vote
1
answer
4k
views
mysqldump: Error: Index for table is corrupt; try to repair it when dumping table - does this mean repair was done?
I ran into the same database trouble that another user described here and I managed to get mysqld started by setting innodb_force_recovery = 6, which allowed me to perform a backup using mysqldump -u&...
- 13
1
vote
0
answers
67
views
DR - Missing binlogs in Maxwell
We have a standard db.r6g.2xlarge RDS MySQL instance running in production with 2 replicas. Maxwell CDC is setup to publish binlogs to a Kafka topic.
From a RPO point of view: in the worst case (say ...
- 111
0
votes
1
answer
177
views
RDS Postgres v14.4 CPU utilization not going below 5% when idle
I have an RDS Postgres instance on a t3.small and am trying to drive down the CPU usage of my clients connecting to it (I have a few API services connected).
I've stopped all my services connecting to ...
- 101
1
vote
1
answer
171
views
InnoDB - how MySQL InnoDB pages works with underlying OS file system
I've studied and summarized how MySQL InnoDB pages works with underlying OS file system when accessing secondary storage.
Would you mind to confirm that my understanding is correct?
InnoDB store ...
- 113
0
votes
1
answer
803
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
0
votes
1
answer
696
views
RDS Postgres, tup_returned > tup_fetched, the data might not be properly indexed
I'm trying to diagnose a high traffic and problematic table(it has an abysmal cache hit rate 0.035)
Given the following sentence from AWS RDS docs:
The tup_returned metric shows the number of rows ...
1
vote
0
answers
126
views
How this performance graph may be interpreted?
My configuration is:
Compute
AWS ECS cluster with a Django based API (Using ORM). 1vCPU and 4GB of memory per container.
Database
RDS MariaDB db.t3.medium instance (2 vCPU, 4GB of memory)
gp3 type ...
- 111
1
vote
2
answers
153
views
MySQL vs PostgreSQL for hash-based table partitioning
I'm planning an open-source a backend service whose primary job is to deliver sync messages between users' devices. I want the self-hosted and hosted versions of the service to share the same ...
- 113
0
votes
2
answers
2k
views
Postgresql: Trigger is not working at times
I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled".
My trigger code is as follows:
CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
- 13
0
votes
1
answer
2k
views
AWS RDS Can't complete Blue / Green changeover
I'm trying to do a Blue / Green changeover but it's failing with the error:
Switchover from DB cluster asamazon2-cluster to
asamazon2-cluster-green-xxxx was canceled due to external replication
on ...
1
vote
1
answer
496
views
high CPU with high user connection, mysql database
I use a VPS with 56 CPU cores and 64 GB of memory, a with MySQL database, but the CPU usage is always high for my apps - user 10.000.
This is in my mysqld.cnf. What's wrong with my settings?
...
3
votes
1
answer
975
views
Faster alter table column type in RDS Postgres?
has anyone figured out faster way of doing postgres 14 column type change in RDS? We currently have a table that is running out of int4 space and multiple columns needs to be converted to int8. Table ...
- 31
1
vote
0
answers
97
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
1
vote
1
answer
99
views
MySQL: Materializing table function take so long
Thanks for checking my question
I encountered a strange issue. the same query in same db return different actual time
here is explain analyze statement
explain analyze
SELECT COUNT(1) AS `cnt`
FROM `...
- 11
0
votes
1
answer
180
views
How to change user-agent/application name when connect to PostgreSQL?
I use AWS RDS, and there is top application tab in perfomance insight like below sceenshot
TablePlus is a SQL client that I used. If I run query using that app, it shows TablePlus in that tab. But if ...
0
votes
1
answer
208
views
Mariabackup, Xrtrabackup, how those handling InnodDB page compression, meaning: sparse files?
InnoDB engine, when using page compression, heavily depend on file system sparse file support. InnoDB Page Compression.
It raises the question, how will mariabackup/xtrabackup handle those InooDB ...
- 187
1
vote
0
answers
146
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
0
votes
0
answers
254
views
Rapid drops in free memory on RDS MySQL Instance
Recently, we've been having problems with our db.t3.xlarge RDS Instance running MySQL 8.0.28. We have a large number of cronned python scripts that run a high volume of read and write queries against ...
2
votes
0
answers
190
views
force rds proxy to renew credentials on grant change?
I'm using Mysql flavored Aurora with an RDS proxy in front. Most of the time life is good. Sometimes, however, I update a grant giving new new permissions to an account. At these times if I were ...
- 121
2
votes
1
answer
703
views
Why is there a need for backup read replica in AWS RDS even though backup on primary is enabled and read replica created using primary server?
In Working with read replicas - Amazon Relational Database Service, it states:
When creating a read replica, there are a few things to consider. First, you must enable automatic backups on the source ...
- 21
1
vote
0
answers
398
views
Crashing after upgrade to MySQL 8.0.31
We recently upgraded to MySQL 8.0.31 on RDS. The upgrade went fine, but a couple days later the instance crashed because of data corruption and wouldn't restart. We ended up having to go boot in ...
- 111
1
vote
1
answer
202
views
/var/lib/mysql/mysql.ibd big after disabling performance_schema
What I think is the reason for this issue
Recently, I was trying to reduce the memory usage by disabling the performance_schema (I read a tutorial that disabling it will reduce the memory usage.)
So, ...
- 21
1
vote
1
answer
314
views
Migrate MyISAM tables to InnoDB using replication to avoid a long down time
We have to migrate MyISAM tables to InnoDB but we are afread of a long down time.
In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes.
My question is:
...
- 500
2
votes
0
answers
509
views
Why AWS RDS Mysql is not keeping binlog files for "binlog retention hours" value?
I have an AWS RDS Mysql master with 1 AWS RDS slave and 1 ec2 mysql slave replicating this master instance. I already set the binlog retention hours to 7 days with the command:
CALL mysql....
- 21
0
votes
1
answer
569
views
MariaDB/InnoDB performance: composite primary key vs. unused surrogate integer primary key to a table?
Years ago with MySQL/InnoDB, I've researched and found that adding a surrogate integer primary key to a table which doesn't have it always makes sense for performance reason. Even if that key is never ...
- 135
0
votes
2
answers
289
views
Pt-archiver csv double quote missing for some fields
I am trying to use pt-archive to archive my database. Below is my script for the archiving process.
pt-archiver --source h=localhost,D=***,t=** --user=*** --password=**** --where "dateTime<'...
- 227
3
votes
1
answer
1k
views
MySQL consumes much more memory than configured in innodb_buffer_pool_size
Long story short: I have the buffer pool size = 40G. I have 64GB RAM on my server. But MySQL actually uses 73G of memory (53G physical and the rest swapped).
MySQL version: 8.0.22-0ubuntu0.20.04.2
...
- 191
1
vote
1
answer
2k
views
down time while adding read-replica in mysql rds
When i add read replica in MySQL RDS database will other read replicas have downtime?
What happens in case of RDS aurora?
- 19
2
votes
0
answers
123
views
Set custom postgresql.conf for one database on Mac using Postgres.app
I have ~20 PostgreSQL 14 databases on my local development machine running on Postgres.app.
I am trying to pg_restore a single 100GB database to Amazon RDS, but I'm getting some inconsistencies with ...
- 121
0
votes
1
answer
60
views
How can I sort thousands of Replies on a Post, following an algorithm that's not just based on columns of the "Replies" table?
On my website, Replies to Post are sorted based on an algorithm that depends on multiple factors: Reactions, Reply Quality, User Reputation, Reports, Replies to Reply, etc... (these are just a few ...
- 839
3
votes
2
answers
2k
views
Need to keep each binlog files to 24 hrs
We have a requirement that we need to keep each binlog files(like mysql-bin-changelog.025249, mysql-bin-changelog.025250, mysql-bin-changelog.025251....) of MYSQL RDS to minimum 24 hrs.
However, i see ...
- 83
9
votes
3
answers
4k
views
In MySQL/MariaDB, do Indexes' performance degrade as they become larger and larger?
I'm currently exploring the use of PARTITION, for a specific use case I have.
I use InnoDB, file per table. MariaDB 10.8.
I was reading Rick's PARTITION Maintenance in MySQL webpage.
I'd like to ...
- 839
2
votes
2
answers
2k
views
Database keeps crashing every few hours
The MariaDB server will crash every few hours(it's been a week so yeah, dozens of crashes), and restart automatically. In 2 previous crashes it went as far as the server not starting up again, and I ...
- 197
1
vote
1
answer
118
views
InnoDB Database Using All Disk Space
I have a database for an app, that is currently using MariaDB with about 220 InnoDB tables. The entire database is about 30GB of data. However when attempting to move it to a new server that has the ...
- 111
0
votes
2
answers
877
views
MySQL Error: Can't find record in table_name
I use MySQL version 5.7.39, ENGINE=InnoDB, Isolation = Read-Uncommitted:
Table product with structure:
CREATE TABLE `product` (
`category_id` varchar(50) NOT NULL,
`product_seq` bigint(20) ...
0
votes
1
answer
565
views
Merging 2 mysql rds into one
Is it possible to merge 2 MySQL rds into one with no downtime ? They both don’t have any overlapping databases
I am thinking of loading the databases from one of the databases into the other via mysql ...
- 701
1
vote
0
answers
144
views
Backup data from RDS to S3 and delete from RDS once copied to S3
We have a requirement werein we have to backup data from RDS(MYSQL and Postgres) to S3 incrementally and to delete the already copied data from RDS. I thought of using aws data-pipeline for this, ...
- 83
0
votes
1
answer
163
views
In the MySQL secondary index, are the primary keys on the leaf nodes ordered?
mysql index
In the MySQL secondary index, are the primary keys on the leaf nodes ordered?
Physical structure in MySQL secondary index
- 31
1
vote
1
answer
975
views
Calculating MySQL server requirements
I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
- 605
1
vote
1
answer
45
views
Delay Async Replication Time ClusterSet
I have following setup:
Primary InnoDB Cluster with 3 instances(following Group Replication Protocol), R/W setting.
Replica InnoDB Cluster with 3 instances(following Group Replication Protocol), R/O ...
- 13
0
votes
1
answer
436
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
2k
views
MariaDB not starting in windows 10 after crash
MariaDB 10.7 service suddenly stopped in Windows 10 after a crash and the service is not starting. The pop up shows 'windows cannot start mariadb service). The windows event viewer gives the following ...
- 121
2
votes
2
answers
740
views
How to identify if my innodb buffer pool is choking ? (and should i increase it ?)
We have a mysql box where i do rigorous scans/aggregations every few minutes (~ 35GB of data). Innodb buffer pool is ~ 1.5GB.
Off late i have started noticing a few latent queries, where the 'read ...
- 123
0
votes
0
answers
283
views
VACUUM is significantly slower in Postgresql 13 compared to Postgresql 12
After switching to Postgresql 13 (13.6) from Postgresql 12 (12.10), we noticed that the manual VACUUM operation takes significantly more time, often more than double compared to Postgresql 12. Which ...
- 1