All Questions
3,206 questions
2
votes
1
answer
33
views
Innodb - Can you safely ignore the warning "Memory size allocated for the temporary table is more than 20% of innodb_buffer_pool_size"
We get this warning in our online shop
report.WARNING: Memory size allocated for the temporary table is more
than 20% of innodb_buffer_pool_size. Please update
innodb_buffer_pool_size or decrease ...
- 125
0
votes
2
answers
40
views
innodb fetch time, not query execution time
A very basic query but from a huge table
SELECT profile_id FROM clients WHERE is_vip = 0
execution has a duration of 0.716s and data fetch time of 15.072s
Obviously my question is if I can speed this ...
- 101
0
votes
1
answer
32
views
Where can I find more detailed MySQL deadlock logs?
We have a MySQL database in AWS RDS that was migrated recently from 5.7 to 8. One of our microservices running in ECS executes bootstrapping code at launch to iterate through a large list of table ...
GS-Scooter
0
votes
0
answers
11
views
Can you downgrade provisioned iops on gp3 for postgresql RDS?
Doing an occasional analysis on a postgreSQL RDS instance with gp3 storage.
Running out of provisioned IOPS.
Is it possible to temporarily raise the provisioned IOPS limit? e.g. just for a day or ...
- 347
0
votes
0
answers
24
views
mysql innodb - copying db manually and register it
I'm creating a test setup such that at the start of each test, I will create an isolated db based on the original DB (40 tables, 14mb size total).
When using mysqldump+mysql or mydumper+myloader, the ...
- 101
0
votes
0
answers
15
views
Postrgresql Logical replication causing very high buildup of log files even though replication slots are active [ AWS RDS postgresql 16.1]
I have two rds instances and have set up logical replication between them [ publisher subscriber]. for the 3-4 days everything was working fine wal files were getting cleared but suddenly when the ...
- 1
0
votes
1
answer
62
views
Loading an SQL dump is slow
note: I initially asked this question on stackoverflow, but was told superuser would be a better place for it. I was then made aware of the existence of dba.stackexchange, which seems like the right ...
- 101
0
votes
1
answer
31
views
Amazon Aurora RDS - invalid undo record
Context
Aurora RDS, MySQL 8.0.mysql_aurora.3.05.2
Problem
The writer instance is constantly cycling. In the AWS RDS console, under Logs & events for the writer, I see
Attempting to restart the ...
- 121
0
votes
0
answers
46
views
mysql 100% disk i/o with no query
I have a mysql instance running on my windows dev box that is using 100% disk i/o with no active query.
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB MUTEX;
If I refresh the mutex list the count in the ...
- 101
0
votes
0
answers
20
views
How to display all locks from SHOW ENGINE INNODB STATUS
How to get around the message
10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
from SHOW ENGINE INNODB STATUS in MySQL 8?
I tried searching but haven't found anything.
My main goal is to be ...
- 1
0
votes
0
answers
36
views
MySQL Memory Usage Exceeding Expectations
We're running MySQL on a machine with 8GB of memory. The innodb_buffer_pool_size is set to 1GB. Recently, MySQL crashed due to a full memory condition. Our monitoring tool showed 100% memory ...
- 187
0
votes
1
answer
42
views
Setting Synchronous_commit to off in Postgres in AWS RDS
0
We are experiencing significant delays in our PostgreSQL database due to LWLock:WALWrite and LWLock:WALSync waits, which are causing commits to pile up.
To address this, we have undertaken several ...
- 1
0
votes
0
answers
82
views
ERROR] [MY-013882] [InnoDB] Missing redo log file ./#innodb_redo/#ib_redo1292 (with start_lsn = 4230987776)
sudo cat /opt/bitnami/mysql/logs/mysqld.log
2024-10-27T07:54:59.478600Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.35).
...
- 1
0
votes
1
answer
37
views
What happens to running queries when I issue a rename statement in MySQL?
I'm running Mysql 8 with InnoDB tables. I have a table with 100k small rows that is under heavy-ish use (10-50 queries per second.
I want to swap the table with its newer version, using RENAME table ...
1
vote
0
answers
22
views
MySQL endless increasing InnoDB .idb file size when doing INSERT ... ON DUPLICATE KEY UPDATE field = CONCAT(field, 'new data') on a LONGTEXT field
I can't find a solution/explanation for a situation I'm facing without changing the code logic (I don't want to do that). The table:
CREATE TABLE IF NOT EXISTS `table` (
`id` int(10) unsigned NOT ...
- 111
0
votes
1
answer
26
views
Analyzing sql query performance issues in mysql
I am new to mysql, I have few questions in regards to query performance tuning. (Note- its version 8.0.32)
I understand we use "explain analyze" to see the execution plan and it shows the ...
- 11
0
votes
0
answers
22
views
Granting Permissions To as User in Mysql
I created user-a that has the following grant privileges
SHOW GRANTS FOR 'user-a'@'%';
'GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, ...
0
votes
0
answers
15
views
MySQL 8.0 connecting to AWS RDS MySQL 5.7 with SSL/TLS CA authentication fails weirdly (Error code MY-002026)
When trying to establish TLS encrypted replication between a replication source living in AWS RDS on version 5.7.44, and a replica living in a VM on-premise on version 8.0.39, I keep getting an error:
...
- 1
0
votes
0
answers
94
views
How does RDS Postgres OldestReplicationSlotLag metric correlate to replication slot Log Sequence Number lag?
We run a Postgres 14.9 database via RDS Aurora Serverless. We have a logical replication slot consumed by a service (Fivetran) every 15 minutes.
We noticed this behavior where the reported ...
- 51
0
votes
0
answers
99
views
mysql ibd import error: cannot reset LSNs in table: Data structure corruption
I am using mysql (Ver 8.0.26 for Linux on x86_64) on centos 8.
I have copied *.ibd with *.cfg file from one server to another server as follows:
source server (Server1)
mysql> show create Table \...
- 1
0
votes
0
answers
41
views
Understanding MySQL Memory Usage
I’m trying to understand MySQL’s memory usage on a Linux server. Here's the memory information from my server:
Total Memory: 123GB
Memory Used: 57GB
Output from free -h :
total ...
- 21
1
vote
1
answer
48
views
Collation difference in PostgreSQL on AWS RDS and local machine
I have an issue with collation in PostgreSQL when sorting a result set. We use AWS RDS PostgreSQL, and we want to sort query results in a specific way. Locally, everything works perfectly on my laptop ...
- 119
2
votes
1
answer
24
views
Primary Key data type suggestion for MySQL for an application that depends in a external system. unsigned bigint vs varchar data type in primary keys
I'll give you some context and then ask my question.
I'm building a database model for an ecommerce application. This application needs to communicate to another system (ERP system) to get product, ...
0
votes
0
answers
41
views
How can I tell if SQL Server is RDS, RDS Custom, or installed manually on a VM (EC2)?
I only have direct access to the SQL Server. How can I check if it's RDS, RDS Custom (which allow to connect to the VM), or installed manually on a VM (EC2)?
- 801
0
votes
1
answer
70
views
"Stuck" Transaction Locking a Table MariaDB InnoDB
I am getting a lot of wait lock time outs on one of my tables when running some updates. I checked INNODB_LOCKS, INNODB_LOCK_WAITS, and SHOW PROCESSIST and I could not see anything. I ran SELECT * ...
- 46
0
votes
1
answer
79
views
MySQL innodb RAM usage increasing day by day
We have MySQL (version 8.x) with InnoDB engine. We were doing weekly MySQL restart (for rotating log files, like slow-query, error log etc) earlier and our RAM usage was in under control. Sometime ...
- 1
0
votes
1
answer
85
views
How can high severity alerts be configured in Amazon RDS SQL Server?
Per the RDS documentation,
SQL Server Agent alerts and operators aren't supported.
But on any sensible SQL Server, you really want these for the high severity alerts.
So, I'm here to ask: How can ...
- 801
1
vote
0
answers
45
views
How can I improve the performance of a "cold" (first) run of this seemingly simple query?
I'm working on a specific query which has been slowing down an application for some time. The table in question has about 55 million rows.
A "cold" (first) run of this query usually takes ...
- 193
0
votes
1
answer
153
views
RDS MySQL not using NVMe instance storage - no benefit of "Optimised Reads"
I am load testing various RDS instances trying to optimise db price/performance
I had seen this doc https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-optimized-reads.html and it sounded like ...
- 528
0
votes
0
answers
22
views
RDS Mysql replica cdc performance
I have a RDS Mysql which has high read/write volume. ~10k IOPS on an average and peaks up to 16k IOPS.
I have another RDS mysql replica of the main. We are using this replica to capture cdc logs and ...
0
votes
0
answers
46
views
How can I find what's eating so much space in Postgres in AWS RDS Aurora?
Essentially when I run
SELECT
pg_size_pretty(SUM(pg_total_relation_size(relid))) as total_size,
pg_size_pretty(SUM(pg_indexes_size(relid))) as index_size,
pg_size_pretty(SUM(pg_relation_size(...
- 101
0
votes
0
answers
192
views
How to grant SYSTEM_VARIABLES_ADMIN permission to master MySQL user or rds_superuser_role from MySQL Workbench hosted on RDS?
Using MySQL 8.0.39 on an AWS RDS insteance, for troubleshooting purposes we want to be able to Enable/Disable the global general_log setting , being able to turn it on and off easily and quickly from ...
- 9
1
vote
2
answers
62
views
Does regular truncate & insert hurt performance?
We have a large (5 TB) PostgreSQL 15 RDS that acts as a data warehouse, data comes in through regular pipelines and then ~20 users run analytic queries on it.
Our infrastructure people have access to ...
- 559
0
votes
1
answer
35
views
Advice on migrating mysql columns from hexed char(24) to binary(12)
I have many mysql tables in production which uses char(24) as primary key. We use BSON ObjectId to create GUIDs (It's similar to UUIDv1, but it's smaller (12 bytes)).
currently, we store these 12-byte ...
- 3
0
votes
0
answers
14
views
Understanding MySQL's Deadlock for un-related tables
I see the following Deadlock in my show engine innodb status's output:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-08-22 09:35:46 0x7f70f2d2d700
*** (1) TRANSACTION:...
- 21
0
votes
0
answers
11
views
How to see number of storage volumes on AWS RDS MySQL?
According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html, for "400–65,536 GiB" storage MySQL RDS will use "Number of volumes provisioned = 4".
I have ...
1
vote
2
answers
102
views
How to work around "row is too big" for role definitions without recreating the database?
I am running into the row is too big (ERROR: 54000: row is too big: size 9976, maximum size 8160) error in Postgres related to role permissions on a couple of my instances. I understand that after ...
- 11
1
vote
2
answers
49
views
AUTO_INCREMENT value is incremented by one after a restart of MariaDB
I need your help to understand some behaviour of MariaDB/InnoDB.
I have a dump file I use to initialize my database.
Below is the dump file:
-- MariaDB dump 10.19 Distrib 10.11.5-MariaDB, for Win64 (...
- 11
1
vote
1
answer
32
views
Why free storage space dip on MySQL replica?
I have AWS RDS MySQL 8.0.36 primary-replica setup. I see this storage free space dip on the replica only. Any ideas why it happened?
-1
votes
2
answers
56
views
Single Primary Key vs. Composite Primary Key to store user isolated data
Let's say I want to store user provided data in a table called post.
users data are isolated by user_id column
select sum(likes_count) where user_id = $ should be fast
select * from post where id = $ ...
- 3
2
votes
0
answers
76
views
AWS RDS PostgreSQL -
We have an AWS RDS PostgreSQL (v14.10) instance running on a db.t3.micro instance. Randomly throughout the day (some days more than others), our .NET Core application starts failing with the following ...
- 121
0
votes
0
answers
31
views
MSSQL on AWS: remote user unable to establish connection via SSMS
Newly created MSSQL 2017 SE instance, using SQL authentication. I am able to connect to it with no issues via my corporate VPN. Overseas user on VPN is unable to connect, getting this error from SSMS:
...
- 143
0
votes
0
answers
41
views
How can I avoid downtime for AWS RDS SSL/TLS certificate rotation?
I have some production databases in RDS for which I have active connections and writes every minute. The current server certificates are expiring on 22nd August. I tried to update one, it takes 5 ...
- 101
1
vote
1
answer
336
views
Mysql Crashing on: [FATAL] Semaphore wait has lasted > 600 seconds
Once a day or two we get the bellow error and the mysql crashingt:
2024-07-14T05:31:59.327696Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash ...
- 131
0
votes
0
answers
23
views
Failed to prune sessions: no pg_hba.conf entry for host, although connection works fine
I am using an AWS RDS Postgres database and Typeorm to connect to the database. I have also configured the RDS database to require a SSL connection. I pass the CA certificate to the database like this:...
- 101
1
vote
1
answer
37
views
Decrease high response time when reading large table in MySQL
I have a table with around 80~ million rows called parts.
I have a simple query like this
SELECT line_code, part_number, compressed_part_number, ...[Others columns]
FROM parts
WHERE (@partNumber = ...
- 111
0
votes
1
answer
63
views
mysql reuse of deleted rows
Using MySql and innodb with innodb_file_per_table flag ON.
We have a table with a size of ~100GB.
We delete the data and insert fresh data (approx same size) every day (the delete is in chunks by a ...
0
votes
1
answer
42
views
InnoDB Cluster ALTER large tables without causing lag
We need to run an ALTER TABLE command to add a new modified column to all servers in a InnoDB ClusterSet (Two clusters)
Example:
ALTER TABLE DB.tableA add column modified varchar(30) GENERATED ALWAYS ...
- 516
0
votes
1
answer
71
views
How to run a query against AWS RDS "locally"?
Our company has an RDS instance of a Postgres database in AWS. I don't have much experience with AWS.
I'd like to run a very lengthy query against the database (VACUUM FULL) that may take many hours ...
- 4,690
0
votes
2
answers
239
views
How to down size the storage of AWS RDS MySQL instance?
I have an RDS MySQL instance db.m5.4xlarge with 8.3TB of data, and I want to shrink it to 4TB to save costs because it actually has 4TB of data. What are the viable options?
I've tried several methods,...
- 11