Questions tagged [performance]
An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
6,376
questions
0
votes
1
answer
39
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 ...
0
votes
1
answer
28
views
Migrated to dedicated MariaDB makes app slower
I'm new to dba stackexchange, hope you can help me :-)
I do have a Hetzner root server (EX52-NVMe Intel Core i7-8700 Hexa Core 64 GB DDR4 2× 1024 GB NVMe SSD) and Docker on it. At first, it was ...
0
votes
1
answer
25
views
Postgresql - optimizing multicolumn search with "prefix" search pattern as one of the filters
I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle ...
- 1
1
vote
2
answers
72
views
View memory usage by database on an SQL server
My team (no DBAs in the team) is responsible for a database hosted on an SQL server with multiple other databases used by other areas of the business. Memory usage has recently been spiking to 100% on ...
David G
0
votes
1
answer
16
views
Network bottlenecks is concentrated on a specific host during query
I have deployed a CoC version of PieCloudDB in a private cloud environment. My S3 service consists of a cluster of three machines. During the deployment of PieCloudDB database, I specified one of the ...
- 1
1
vote
2
answers
65
views
Why is Postgres so slow to order those 200 rows that are already ordered?
I have two relatively complex SQL queries which I join using a UNION ALL. Each individual query is fast and returns instantly. The problem is that once joined together they perform terribly bad, and ...
- 177
0
votes
0
answers
49
views
What is the fastest way to transfer data from SQL Server 2014
I have a Database that is corrupted, backup and restore does not work, the table contains 180 million records with a total size of 1TB.
I need to copy the data from the table to another, but it is ...
- 21
1
vote
0
answers
38
views
Querying master.dbo.syslockinfo much slower on new Sql Server machine
After changing the machine running SQL Server 2019 Standard to a new one (Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz, 128GB RAM, SAS SSD RAID10), I noticed a very significant drop in the performance of ...
- 11
0
votes
1
answer
43
views
Batch update in a large dataset
We have 3 tables:
Product (product)
Stock (product_Stock)
Price (product_price)
In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
- 191
0
votes
0
answers
31
views
How to monitor Postgres workers' utilization?
I have a client who has a legacy application using a Postgres DB. They have SEVERE performance issues, so they figured it might be a good idea to scale their DB server to the GCP's max. Performance ...
- 101
0
votes
0
answers
17
views
Verify a postgresql index can be dropped securely
Indexes use resources because they have to be updated when data changes.
How to check if an index can be securely dropped (ie. not used at all)?
This request seems to return the unused indexes, ...
- 235
1
vote
0
answers
14
views
Performance degradation after upgrading to MySQL 8
We have upgraded MySQL in our application from version 5.7.43 to version 8.0.35.
We are getting below exception while trying the fetch the records from the database.
231104 04:04:03 : com.mysql.jdbc....
- 11
1
vote
1
answer
67
views
Does SQL implementations (or at least SQL Azure) skip obvious duplicate data with joins when returning data?
Say I have:
very wide table A where I need all columns
that I need to join with a small table B that has a LOT of rows.
Of course when I join these two tables I get returned all the content of A for ...
- 355
3
votes
2
answers
86
views
MySQL chooses a slow execution plan for selecting rows by range condition with ORDER BY, LIMIT and OFFSET clauses
I have a table "client_data" with 2 indexes:
CLIENT_USER_CREATED (CLIENT, USER, CREATED)
CLIENT_CREATED (CLIENT, CREATED)
The primary key is (ID, CLIENT, CREATED)
The table is partitioned ...
0
votes
0
answers
50
views
Postgres slow replication debug strategy
I'm using logical replication to move data from server X to Y. We had a hiccup on server X that caused the replication to fall severely behind. We don't know what it was, and right now I'm trying to ...
- 307
2
votes
0
answers
79
views
Query not using foreign key indexes
I have the following schema for my database:
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);
CREATE TABLE employments (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
...
0
votes
2
answers
117
views
Postgres indexes for 75 columns
I have a table with about 5 million records in my Postgres database for real estate listings in my area. The table has 95 columns, ~75 which are used for filtering and searching for listings the user ...
- 3
4
votes
0
answers
509
views
Non parallel plan with row level security predicate to check server role
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="...
- 41
0
votes
0
answers
77
views
Scalar udf abysmal performance
I have a udf. It is essentially a case statement, using ifs instead and multiple "broken" assignments of the same variable, see code below.
It does not access any table. Still, on one of my ...
- 437
0
votes
3
answers
32
views
Which would be best for a scalable environment?
Which is more advantageous, speaking of performance, and best practices?
A table of customers and a table of suppliers and a table of seller
Or
A table that covers the three entities having a field ...
4
votes
2
answers
912
views
Balancing Indexing and Database Performance: How Many Indexes Are Too Many?
I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default ...
- 1,157
0
votes
0
answers
33
views
mariabackup 10.11.3-MariaDB incremental backup taking long time due to "Waiting for log copy thread to read lsn"
The incremental backups on my MariaDB instance are taking long time due to the following:
Waiting for log copy thread to read lsn 11214585412"
...which takes 3 minutes.
Everything else is ...
- 101
0
votes
1
answer
41
views
Selecting child items from another table as a JSON array column (without ruining performance)
Let's say I have these two tables: author(id, name, country) and publication(id, name, year, author_id).
What I want to get in the end is:
author_id | author_name | author_country | ...
- 199
1
vote
1
answer
35
views
PostgreSQL recursive query performance improvement
I'm building a database which is used to derive hierarchical relations for which I chose a PostgreSQL recursive CTE .
my table structure is
table_name |column_name |data_type ...
- 13
0
votes
3
answers
58
views
MS SQL Server shared hosting resources
I have a general question regarding use of MS SQL Server in a shared hosting environment.
I a shared hosting environment, where the MS SQL Instance hosts multiple databases from multiple customers (in ...
0
votes
0
answers
51
views
Performance issue on same identical database with same execution plan
Need some help advise on one of the issues that we have bee working on.
For one of our SQL databases (SQL2014) we use database mirroring ( i know its old but we are working on migration) and there is ...
- 772
1
vote
2
answers
131
views
SQL Server database with 100 terabyte of data
We have been assigned a new application where the vendor is expecting the database to grow to around 100 TB. They have mentioned SQL Server Standard Edition is compatible.
My question is whether SQL ...
- 1,039
0
votes
0
answers
49
views
SQL Server unexplained network traffic gaps in traced events
This appears to have started happening a week, maybe two weeks ago.
Essentially, we cannot figure out whether it is the server or the client that is periodically pausing between SQL statements. In the ...
- 4,310
0
votes
2
answers
44
views
Azure Disk performance optimizaton in SQL VM
We are using Azure SQL VM and we have multiple data files of multiple databases stored on same drive (let's say Drive E: of size 3 TB) which is created from 4 * 512 GB disks + 4 * 256 GB disks.
I was ...
0
votes
0
answers
31
views
Sql-Server Performance Degradation on a specific DB
I have a production Sql Server 2019.150.4316.3 instance (2 Core licence) which holds two mid active databaes (~200 concurrent users) along with low active DBs, installed on a VM (64 GB Ram).
...
1
vote
2
answers
67
views
When can we downsize Azure Virtual machine for SQL Server?
For now our production SQL Server is Standard_D96ds_v5 Azure VM machine.
We got request to check if we can downsize server to Standard_D48ds_v5.
What measures will take into consideration to have ...
- 295
0
votes
1
answer
14
views
psql meta-command \d is really slow
My basic question is: what affects the performance of \d?
I have a separate (small) schema that I manage on a database server that I have otherwise no higher privileges on. The server holds a huge (...
- 345
0
votes
2
answers
59
views
How to make a remote SQL Server to pull local SQL Server data
In my scenario, I have a SQL Server Express that is my data source. It periodically produce batch of data, that should be uploaded to a central repository (a SQL Server SE in the cloud). The data ...
- 358
1
vote
1
answer
105
views
VSSSQLwriter error
I've a SQl server 2019 on a Windows Server 2019 standard core, I manage two typer of backup, one with mantainance plans and one with Veeam Backup. The mantainance plans it's ok, the backup with Veeam ...
0
votes
2
answers
69
views
any way to keep track of all queries and their number per hour or per day in SQL server
in SQL server, i would like to keep track of all queries and their count , based on specific time frame and keep thsir historiacl record.
like every hour , which query ran for how many times.
We have ...
- 121
0
votes
1
answer
65
views
How to speed up query for stock balance?
I have 2 tables StockIn and StockOut and calculated stock balance (in-out).
Both tables contain millions of records.
When I calculate the stock balance, due to the millions of records, my query ...
- 3
0
votes
0
answers
56
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
- 1,417
0
votes
1
answer
45
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
- 3
2
votes
0
answers
87
views
Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume
I'm working with a PostgreSQL table containing terabytes of data, and it grows by millions of rows weekly. Each row is identified by a KSUID, and my primary read patterns are:
Retrieve a row by its ...
- 21
3
votes
1
answer
162
views
PostgreSQL Query Planner overestimating Row Count leads to slow Hash Join
I have a table with two columns and about 10M rows. About half the records are NULL in the first field, and about half the records are NULL in the second field, although there are a few hundred ...
- 31
0
votes
1
answer
24
views
SSRS with remote ReportServer db - where to focus cpus/memory for processing
On an SSRS instance with a remote ReportServer db, where is the processing done? Should the SSRS instance vm get additional cpus and memory or the sql instance where the ReportServer db is? The ...
- 83
0
votes
0
answers
36
views
PostgreSQL Optimizer does not find best solution when specifying subset of keys in query
PostgreSQL (Version 15) does not find a (in my opinion) obvious optimization for the query plan, is there a way to help the optimizer with this problem?
I have a specific kind of query where I have to ...
- 9
0
votes
1
answer
81
views
Slow deletes in Postgresql with foreign key constraints [duplicate]
I have a number of tables relating to an entity and delete them in a transaction to make sure all are removed simultaneously:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
delete from table1 where entity_id = ...
0
votes
2
answers
35
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 101
1
vote
1
answer
176
views
Postgres GENERATED ALWAYS performance when adding new column
I have a fairly big PostgreSQL 13 table that I need to add a new TEXT column and with this new column I would like to add a GENERATED ALWAYS column with the following definition:
ALTER TABLE my_table
...
0
votes
0
answers
27
views
Optimizing Performance of Highly Nested Postgres SQL Query
I have been working with a PostgreSQL database and I've encountered a SQL query that seems to have a high degree of nesting, which I suspect might be impacting its performance. I'm seeking advice on ...
- 101
0
votes
1
answer
112
views
MariaDB is in "Sending data" state for too long for COUNT
Recently I have encountered an issue of slow data loads.
Upon looking into the Process list I have found out that the issue is on the database side, it gets stuck in the "Sending data" phase ...
- 101
0
votes
0
answers
22
views
5k ms Planning time for 2k rows table
I have a table that has 44 columns and slightly less than 2000 rows. Also, the data are all text or integers. Texts don't go longer than a few characters. Table size is about 1900kb. All my other ...
- 1
2
votes
0
answers
74
views
Why does the same query require more "Heap Fetches" when run on my postgresql replica?
I have a Postgres 13.10 database running on AWS RDS, with a primary instance and a replica. The replication is managed by AWS - it's not logical replication I manage myself.
The replica is configured ...
- 311
0
votes
1
answer
118
views
What are the use cases for MySQL Generated Invisible Primary Keys (GIPKs)?
I have read in the MySQL documentation that it is possible to enable the generation of invisible primary keys (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html).
I'm quite a newbie in ...
- 3