All Questions
7,398 questions
0
votes
1
answer
119
views
How to reduce running time? Execution time exceeded
Excuse my grammar Im dyslexic:
The main issue is that after calculating all the lags and flows at the ISIN level, when I try to do the final grouping by bucket, asset class, and date, I'm forced to re-...
- 11
0
votes
0
answers
8
views
In which sections of AWR and Statspack can we identify how much CPU the database usually consumes?
We need to migrate a database, but we need to evaluate the possibility of reducing the number of resources (CPU) to decrease the number of licenses. In which sections of AWR and Statspack can we ...
- 801
0
votes
0
answers
30
views
Why am I getting an INDEX SKIP SCAN?
Scenario is as follows:
Table T is created as of
CREATE TABLE T
(
f_a ...
,f_b ...
,f_c ...
,ctv NUMBER(*,0) NOT NULL
,ffc DATE NOT NULL
,v NUMBER(1,0)
,uc NUMBER(1,...
- 123
0
votes
1
answer
31
views
Should I break a large user table into smaller tables for specific roles and information?
I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:
Personal information (e.g., name, email, phone_number, address, etc.)
Work-...
- 101
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
1
answer
39
views
Postgres slow query on large tables when doing a search radius search and joining on large tables with indices
I've been experiencing extremely slow queries despite having indices (hopefully the correct indices) on my tables. What I'm doing is searching for providers within a search radius as well as their ...
- 3
0
votes
1
answer
40
views
Does setting a default value for Alter Table in Postgres make the query significantly slower?
We had an incident very recently that caused our service to be unavailable for some minutes.
Here is the liquibase script that led to the incident:
<addColumn tableName="registration">
...
- 119
0
votes
3
answers
128
views
OS memory usage is high, but MS Sql server has plenty of free memory
On sql server 2019, I occasionally get "Physical Memory pressure" alert.
When I check the memory usage, I get two different cases.
There is a large memory in free pages.
Can anyone explain ...
- 351
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
1
answer
58
views
Long running waits- how to check/prove if the root case is at the SQL Side
Some of my users complain about the poor performance of the application developed/supported by an external company. The app uses SQL Server 2019 Enterprise Edition for a database engine.
I can see in ...
- 192
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
1
vote
2
answers
153
views
Heap with Non Clustered Index vs. Non-Sequential Clustered Index
I work with an enterprise application (code not available) that currently uses heaps. It uses a terrible pseudo-sequential varchar key that ends up not being sequential due to string sorting not ...
- 163
0
votes
0
answers
30
views
Postgres: How to select rows, grouping by rows with same column field into one row, with pagination
I've a table, with fields like
id
batchId
senderId
recipientId
uuid1
uuid
uuid5
uuid7
uuid2
uuid
uuid5
uuid8
uuid3
uuid2
uuid6
uuid9
I need to select rows with pagination, but handling rows with ...
0
votes
1
answer
30
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
2
answers
51
views
Mysql process consumes High CPU & RAM even after reboot
My VPS on DO (1 shared CPU & 1 GB RAM) sometimes got high CPU & RAM, and sometimes I stopped MySQL services then restart then it becomes normally, but sometimes this way did not work
Today, I ...
4
votes
1
answer
335
views
Do handling of JSON-format or anti-pattern (comma-delimited values etc.) in relational database introduces performance and maintainability issues
If...
INSERT INTO TABLE_NAME
SELECT
STRING_AGG(COLUMN_NAME, ',')
FROM
TABLE_NAME
introduces an anti-pattern (i.e., can cause poor performance, incorrect results (kindly verify ...
- 143
-1
votes
1
answer
66
views
SQL Server slows every night [closed]
The last couple of nights i woke up to websites with very slow loads due to SQL Server issues. The issue resolves on server restart
I tried sp_BlitzFirst and got "High Percentage Of Runnable ...
0
votes
1
answer
41
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
20
views
Prefixed indexes in Oracle
I know it is not logical to have 2 indexes when one is prefix of another one, I mean if I have following indexes:
create index ix_vch_plc on tb_voucher(fk_plc);
create index ix_vch_plc2 on tb_voucher(...
- 189
1
vote
1
answer
144
views
How to optimize performance for PostgreSQL logical replication with a large number of databases?
We are using PostgreSQL 16.4 on Debian 12.2 (16vcpu/64gb). We have a multi-tenant setup where each tenant has its own database.
We are facing issues with logical replication performance when handling ...
0
votes
2
answers
65
views
Add more CPU to VM running SQL but for OS only
Is it possible to add more CPU to a VM running SQL but for OS use only?
We have a SQL 2019 std VM that was already provisioned with 4 cores. We noticed antivirus is using CPU a lot. We want to add 4 ...
0
votes
0
answers
50
views
using WHILE EXISTS (SELECT 1 FROM dbo.TargetQueue) caused service broker keep calling activation function even there is no msg on the target queue
I have the following SQL service broker set up:
USE master
-- Create database
ALTER DATABASE ServiceBrokerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ServiceBrokerDemo
CREATE DATABASE ...
- 331
0
votes
1
answer
64
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
1
vote
2
answers
103
views
Do temp tables cause Recompilations in SQL Server?
we have a database on SQL Server 2019 with compatibility set to 110 (SQL Server 2012). We have a stored procedure that is being recompiled frequently, and I noticed we are using many temp tables. We ...
- 601
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
1
vote
1
answer
37
views
Same query, same data, one server query planner costs 50000, other costs 100
I'm running two Postgres 15 (PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit) RDS instances in AWS, one for my staging environment and one for ...
- 13
0
votes
0
answers
41
views
Seeking Fastest Hash-Based NoSQL Database: GDBM vs Kyoto Cabinet & Other Alternatives
Background
I'm currently researching fast, hash-based key-value NoSQL databases for my use case, which requires high performance. I was surprised by the limited availability of benchmarks, ...
- 101
0
votes
1
answer
23
views
sstableloader taking a long time to complete
We are looking to restore Cassandra data from snapshots and incremental backups.
This is a setup with 15 nodes in a single DC.
Snapshots and incremental backups are copied to local disk on each node ...
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
1
answer
39
views
Reduce vacuum overhead & partitioning strategy for high-volume messages in PostgreSQL 13
I have a use case where I need to process a file containing messages generated for multiple customers. These messages need to be stored in a database and to be forwarded to respective customer in ...
- 101
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
64
views
slow postgres 10 query - have I done something stupid with my indexes?
I have a database with a large (50M) table of time series observations and locations (900 rows). The following query takes quite some time:
SELECT *
FROM observations
WHERE location IN (SELECT ...
- 101
1
vote
0
answers
38
views
Help understand why RLS significantly affects query performance
I have an RLS policy defined as:
create policy read_contacts on contacts
as permissive
for select
using (company_id = (current_setting('nimble.company_id'::text))::bytea);
And such query:
...
- 13
0
votes
0
answers
101
views
Updates to most tables take 0.2ms, updates to a few take 13+ms. Why?
We run a pretty busy 20TB MySQL 8.0.34 running on AWS RDS on an r6i.8xlarge, and about 3 tables are showing slower-than-normal updates that are puzzling me.
Normal single-row UPDATE .. WHERE .. ...
- 129
0
votes
1
answer
43
views
How to count provided companies with specified solutions fast in postgresql?
I have a table structured as follows:
legal_entity_id (text)
solution_string (text)
total_score (bigint)
asjY4474dgd
Server
9
I need to determine, for each solution and company provided by users, ...
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
34
views
SSRS on a Secondary Replica
We have an availability group database with 4 servers which includes a asynchronous replica that is used for reporting. Sometimes due to large query sets the secondary replica stops synchronization ...
- 1,063
0
votes
1
answer
145
views
Faster query time but slower response time in 11.4 vs 10.3
I am trying to do a migration for a sever upgrade. The current live system is a Win 2016 server with MariaDB 10.3 on it. The new system is Win 2022 with MariaDB 11.4. Everything is virtual running on ...
- 1
0
votes
1
answer
57
views
PostgreSQL query scanning all partitions instead of specific one when filtering on dimention table
I have two tables in PostgreSQL, one large (transactional) with millions of data which is partitioned on column TenantId and one smaller (dimension) which has TenantId and TenantName columns, I have ...
- 170
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
1
answer
90
views
Why is PostgreSQL reading from heap and how to improve cache usage?
I have an SQL query that executes slowly on PostgreSQL 13.15 with 128GB of memory. The query mainly performs a Bitmap Heap Scan, and I’ve noticed that many reads are coming from the heap instead of ...
- 13
0
votes
0
answers
31
views
Best Practices for BulkWrite Performance with Large Documents and Array Fields in a 2-Node
I'm using MongoDB's bulkWrite to update documents in batches of up to 500 updateOne operations. I'd like to ensure I'm following best practices for optimal performance. Here's some context about my ...
- 1
0
votes
1
answer
164
views
How to fix checkpoint requests problems?
On PostgreSQL v15 every morning at 5:15 AM starts heavy inserts in our database and finishes around 7:00 AM. I try to see if there are some parameters that can be tuned like checkpoints.
I have ...
- 411