All Questions
3,206 questions
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
0
votes
3
answers
319
views
Mariadb threads blocking randomly
We have a mariadb instance hosted on Ubuntu 22.04. We are regularly seeing some threads getting stalled on relatively high activity tables(although the tables are not huge ones). Once stuck, it never ...
- 121
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
102
views
ibdata1 file size still increases, after adding innodb_file_per_table=1 and deleting ibdata1 file
MariaDB
ibdata1 file is increasing in size significantly.
Even after deleting the ibdata1 file and adding the
innodb_file_per_table=1
in my.cnf file.
ibdata1 is again consuming the space and ...
0
votes
0
answers
70
views
InnoDB I/O error on ALTER TABLE MODIFY
I'm trying to update some of our database's columns from utf8mb3 to utf8mb4, but while trying to execute the query I lose connection, because the server crashes. Are my buffers too small or is there ...
- 121
0
votes
1
answer
22
views
MariaDB in the cloud....need some guidance
my plan is to move a mariadb into the cloud in germany. the clients will connect to it from 2 different office locations with 1gig/50mb up/dowload cable internet. Approx 10 clients will use the ...
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
1
vote
0
answers
190
views
MySQL using more memory than allocated in InnoDB
I have a Ubuntu 20.04 machine with 64 GB of RAM that is used both as a web server and for MySQL version 8.0.35. I am using InnoDB, but even if the innodb_buffer_pool_size variable is set to 128MB, the ...
- 11
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
1
answer
305
views
Fatal error 1408 when installing mariadb-server / mariadb-upgrade
I recently upgraded my Debian 10 server running MySQL 5.7 to Debian 11 and MariaDB 10.3. That upgrade went smoothly. I then decided to upgrade to Debian 12 and MariaDB 10.11. While performing that ...
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
0
answers
58
views
mysql clusterset vs replication
We run a medium sized app which may need the potential to grow but for a few years a single Mysql server would be sufficient. However we host on Azure which twice in the last 6 months has gone down ...
- 101
0
votes
1
answer
204
views
How do I prevent ibdata1 from continually growing by over 1GB per hour?
Like most DBAs I have two copies of my entire system, one running on a commercial website provider and one on my development system. Both are identical: the same options, the same software level, ...
- 101
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
3
votes
0
answers
178
views
Why can't I combine CHECK constraint with ON UPDATE CASCADE?
Given table A:
CREATE TABLE `A` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
);
when I try to create this second table B:
CREATE TABLE `B` (
`id` int NOT NULL,
`val1` int,
PRIMARY KEY (`id`),
...
- 31
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 ...
-2
votes
1
answer
203
views
MariaDB InnoDb & Columnstore
I have a DB mariadb with innodb tables with one, two million records.
I checked online and saw that Columnstore is faster.
I created a Columnstore table in my DB and put all the contents of the Innodb ...
- 7
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
2
answers
213
views
Innodb_buffer_pool_pages_free - Can it be 1 or 0?
I have been researching this topic on whether the Innodb_buffer_pool_pages_free can be 0 or not and still has no impact on the site. Some DB admins are of the opinion that ...
- 13
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
0
answers
63
views
MaySQL InnoDB cluster log fill on one server
in my mysqld.cnf file, I enabled logging via the following
general_log = 1
general_log_file = /var/log/mysql/mysql.log
It is a three-node cluster. I can see all the queries in the log ...
- 1
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
1
answer
131
views
innodb_buffer_pool_size complications
I had some complications regarding innodb_buffer_pool_size. I read the official documentation, and I found out the formula innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
so if my ...
1
vote
3
answers
57
views
MySQL query optimisation
This is the MySQL table DDL:
CREATE TABLE `users` (
`username` varchar(250) NOT NULL,
`jid` varchar(250) NOT NULL,
`nick` text NOT NULL,
`subscription` char(1) NOT NULL,
`ask` char(1) NOT ...
-1
votes
1
answer
774
views
How do i fix "InnoDB Plugin initialization aborted with error Generic Error"
i am running a Nextcloud Server on a Ubuntu 20.04 which was running fine. I wanted to upgrade the Nextcloud Version and make a backup before. This is what i did:
plugged in a external harddrive (/dev/...
Ruben247
0
votes
2
answers
64
views
INSERTing millions of files into a table
I archive millions of XML files (1-100MB each) in a table with the structure of
CREATE TABLE Data
(
ID int(11) unsigned NOT NULL,
XML longtext COMPRESSED,
PRIMARY KEY(ID)
) ENGINE=Aria ...
- 4,531
0
votes
1
answer
28
views
MySQL Data not flushing to disk
Lets start with what I did...
I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries
I copy ...
0
votes
1
answer
225
views
Mysql deadlock on non unique index
I'm getting the deadlock on MySQL v8.0.32 which is very strange for me because of the following:
I'm trying to insert the data: parent-child record (users, user_roles) - each transactions inserts new ...
- 101
0
votes
1
answer
52
views
If I UPDATE one column in one row in the MySQL InnoDB engine, do I have to wait for the lock to be released when I SELECT another column?
For example, suppose you run the queries below.
UPDATE `user`
SET point=30
WHERE id=1;
SELECT nickname
FROM `user`
WHERE id=1;
In this case, should the second SELECT query wait for the lock from the ...
0
votes
2
answers
49
views
What is the correct index on a large dataset with group by?
I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency.
I have several instances I need to manage where I need to sort ...
- 195
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
1
answer
129
views
Getting deadlocks very frequently in Mysql insert query
=2023-09-15 15:16:35 0x1530b7bc17002023-09-15 15:16:35 0x1530b7bc1700
*** (1) TRANSACTION:
TRANSACTION 55060584750, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), ...
- 11
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
1
vote
0
answers
55
views
Wrong query optimization plan with InnoDB
After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage.
The problem are some queries that are wrong optimized with the new engine.
...
- 11
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
2
votes
1
answer
2k
views
Restoring old raw backup in MariaDB 10.8.1+ without ib_logfile0
I tried to restore an old raw backup of databases (whole datadir) in MariaDB 10.11.5 but it didn't booted up. After that I found following lines in log:
[ERROR] InnoDB: File ./ib_logfile0 was not ...
- 123
0
votes
0
answers
328
views
How to alter table with ALGORITHM=INSTANT after dropping fulltext index
I have a table lets say my_table with the following fields:
id (int)
title (varchar)
content (text)
And a FULLTEXT index on title and on title + content Let's call those indexes index_text_1 and ...
- 113
0
votes
1
answer
719
views
which way to increase InnoDB Buffer after more resources given
Sorry for re-treading the constant discussions of Buffer size and efficiently, we have a standalone server for a vendor system that has a database of 10GB on MySQL 8.0.28
the server itself is a bit ...
- 1