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.

Filter by
Sorted by
Tagged with
0
votes
0answers
16 views

PostgreSQL: Query runs fine in Prod but really slow in QA

Postgres 10.4 dbs hosted in AWS environment. I have a query which joins two tables with many-to-many relationship. I learned while troubleshooting that Analyzer won't run until number of rows inserted ...
0
votes
1answer
42 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
1
vote
0answers
27 views

Postgres database insert become slow after 10 days, and then it needs a full vacuum

We have a postgres database which is almost 15GB's in size after vacuum. With a dedicated hardware i,e 32GB of ram with 12 Cores. The data automatically gets inserted around 300k inserts per day, and ...
0
votes
1answer
33 views

How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs

We are currently running into this issue for one of our availability group design:- On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours On Secondary which is configured ...
0
votes
1answer
34 views

Slow SQL taking almost 1s to execute with only 24000 records! (EXPLAIN included)

Any ideas how to improve and what the actual culprit is? records in product are only 24000 takes 0.85s to run Mysql 5.7 select `product`.* from `product` inner join `manufacturer` on `product`.`...
0
votes
0answers
39 views

Readers blocking reader on readable secondaries

We recently migrated to AG setup for one of our VLDB's around 20+TB. Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
0
votes
1answer
36 views

How to get the size of a SELECT statement in SQL?

Consider I have to do a SELECT, without knowing how big the rows are, and I would like to run a query to check the length of the response, to calculate whether I will be able to store the result in ...
0
votes
1answer
23 views

In SQLITE, how do you create a new table into its own file?

I am trying to do key value pairing, where performance is the top priority. I understand that the smaller a sqlite file is, the better the read and write speeds are, and so I am going to create about ...
0
votes
0answers
25 views

Postgresql, joining most recent row from one table to another based on dates

We have several tables we have to bring together to match the most recently effective price with the transaction date. The simplified query is shown below: SELECT t.transaction_id, price.* ...
3
votes
1answer
107 views

SQL Server High CPU usage and RESOURCE_SEMAPHORE waits

Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %) This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has ...
0
votes
2answers
38 views

Optimizing nested SQLite query

I have a SQLite table named hashbands that looks like this: hashband | file_id | window_id ------------------------------ potato | 0 | 0 potato | 1 | 0 The table has 100M+ rows (lol). ...
9
votes
2answers
205 views

Why is SQL doing an index scan and seek with the same XML query structure?

I've set up a test to benchmark xml performance in SQL server. Test Setup One Million rows of data XML defined Column with primary key A primary XML index A secondary XML index on the path XML data ...
0
votes
0answers
15 views

Which design could be better for scale a MongoDB query for this usecase

Context Imagine that you have two MongoDB collections, one called nodes and the other called mirrors. Each mirror is replica of a node. So, in case the node is removed (for whatever reason), you ...
3
votes
1answer
326 views

Can shrink database improve performance

Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application. I don't know yet what the issues are but the solution they propose is to ...
0
votes
2answers
44 views

Is it a good approach to design tables according to the queries I'll perform on it?

Watching this video, and pretty new to dbms. The speaker explains that in a row-oriented DB, rows are read in blocks. So, my understanding is that if I have rows with fewer fields, more rows can fit ...
1
vote
2answers
81 views

SP runs slower on new server

We migrated our databases from SQLServer 2012 to SQLServer 2019. Our ETL's are build in Visual Studio and are set up from a master package. The masterpackage calls on different packages, which are not ...
-1
votes
1answer
19 views

Query optimization with joins and unions and fulltext boolean search

I have the following base tables with subtables for searchbait. (This is on aws aurora with mysql 5.7 compatibility) tblA (id, userId, title, ...) tblB (id, userId, title, ...) tblA_searchBait (id-&...
2
votes
1answer
16 views

QuasarDB cluster stuck in IOWAIT on AWS EC2 instances

I am experiencing a system freeze in IOWAIT on my QuasarDB cluster. I am continuously ingesting data at 10 GBit/s using the QuasarDB batch API. After a couple of hours, I have all the qdb threads in ...
0
votes
2answers
38 views

Really poor performance when using subquery to get one value

Hi I have a table defined like below (Its actually lots of partition tables) CREATE TABLE sensor_values ( ts TIMESTAMPTZ(35, 6) NOT NULL, value FLOAT8(17, ...
-2
votes
1answer
28 views

Are unstructured databases faster using arrays or many binary variables? [closed]

Is it faster (or otherwise better) to structure unstructured data like this: { label:"many_variables_example", "a":0, "b":1, "c":1, "d":1, &...
1
vote
2answers
58 views

Tempdb log growth 3x as slow on new server, SP does not perform

We are running into the issue that our stored procedure on our new SQL Server performs a lot slower than on the old server. We looked into the problem and noticed the following on our temp db. The ...
0
votes
0answers
49 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...
0
votes
3answers
44 views

How to re-write this query or restructure my table for SPEED

I have an InnoDB MySQL table of this structure: column type service varchar(16) url varchar(255) datetime datetime It contains logs for requests made to one of multiple services. It contains ~3 ...
1
vote
1answer
34 views

Can large data deletion affect Innodb buffer pool performance? [duplicate]

At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
1
vote
1answer
48 views

MySQL ON DUPLICATE KEY UPDATE monitoring

How do I monitor the performance of the following SQL in-depth? INSERT INTO rating (account_id, product_id, rating) VALUES (133236, 107, 0), (133236, 513, 1), (133236, 575, 2), (133236, 677, 3) ...
1
vote
1answer
32 views

MySQL performance issues with occasional complete freezes

we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes ...
0
votes
1answer
44 views

Why is Oracle so much slower for small atomic inserts compared to Postgres? And what defaults can I change to remedy this?

If I create a brand new database from a Docker image and run a fresh database with the default settings. CREATE TABLE "TEST_TABLE" ( "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE, ...
0
votes
2answers
29 views

How to design tables - dabbler question

I can understand that this is a basic question, but it's not about knowledge (I think) it's about practical experience. I came from another field so, be kind, please. I have my ORDER Every ORDER has ...
1
vote
1answer
16 views

How to optimise query fetching most recent report for each entry in another table?

I am attempting to optimise a query that displays the last reported movement for a set railroad locomotives. The query currently takes 8-12s to run for a common query to generate a report on a webpage,...
0
votes
0answers
30 views

Will database replication increase the size of the replicated database?

First thank you for your time. We have a SQL Server database that was assigned to be replicated. the original size of the database was 5 GB for the datafile, not the log file. Now after the ...
0
votes
0answers
24 views

In what sort of way “v$transaction” view will help DBA's to improve the performance of the query?

I've just started learning some DBA tips and techniques in Oracle and I came across v$transaction view. I've read some articles regarding this view but some things are still ambiguous ! I want to know:...
1
vote
1answer
64 views

Advice on Database Design

context : Teachers create problems to their Students A Problem have Numerals(Questions) A Numeral(Question) have Steps to guide the student to the answer Choice represent the type of mathematical ...
3
votes
1answer
32 views

Does a huge key length value for a mulibyte column affect the index performance?

When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) ...
0
votes
1answer
31 views

Does daily pg_dump mess up postgres cache?

I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory. Performances are way worse than on my previous provider, and ...
0
votes
0answers
15 views

PostgreSQL: Simple IN vs IN values performace

We have queries with IN filters with long list of INT values Sometimes, they running extremely slow, and I have found suggestion to use syntax Field IN (VALUES(1465), (1478), ... Instead of Field IN (...
0
votes
1answer
32 views

How can I measure the impact of queries with errors being rejected by SQL Server?

After configuring XE on November 23, 2020 to capture some errors I could count 2,163,665 occurrences of the same error coming from requests of a legacy app (that probably won’t be corrected so soon). ...
1
vote
1answer
56 views

MariaDB Uuid as Primary Key Performance Problem / Tweaks

im upgrading our mariadb database from PRIMARY KEY INT AUTO_INCREMENT to UUID PRIMARY KEY. We have around 80 tables with multiple relations between them. I've build a migration script to change the id ...
0
votes
2answers
62 views

MySQL partitioning relatively large DB Table

I have table structure similar to the following - CREATE TABLE `ProductCatalog` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `SerialNumber` varchar(20) DEFAULT NULL, `BasePrice` decimal(10,2)...
0
votes
0answers
21 views

Performance Insights MySQL RDS cannot change Counter Metrics or download full SQL

This is probably some permissions issue but I am trying to use Performance Insights on the AWS console for several MySQL RDS instances that are supposedly configured for it. I am following the ...
0
votes
1answer
34 views

Tuning query - reduce temporary and/or filesort use with join

My query runs in 60-90 seconds, I would like to see how can I improve it. One table contain partitions and have tens of million rows each partition. The other table contain 5-8 million rows between ...
1
vote
0answers
48 views

How to improve Oracle Standard Edition's performance for testing? [closed]

There's a great post on StackOverflow about improving Postgres performance for testing. https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940 However, ...
1
vote
1answer
34 views

Local JSON file vs MySql database [closed]

I've built a website using node js and it has two parts. one for users and one for website administrators for editing content on website that is locked by a login page. The user side, loads data from ...
0
votes
0answers
37 views

How to restrict CPU consumption of CPU intensive applicaiton with Db2 Workload Manager?

in Db2 v11.1 on Linux I would like to have control over applications that consumes too much CPU resources. For every application that consumes too much CPU for more then 3 seconds I want to lower CPU ...
2
votes
1answer
34 views

Should partitioning increase performance of indexed accesses and updates? [duplicate]

I often hear that partitioning huge tables should significantly increase query speed and insert/update speed, because indexes are proportionally smaller. I'm struggling to understand why that should ...
0
votes
1answer
18 views

Heavy performance drop on Progress query, looks like based on string character inside table

I'm launching following query on a Rel 11.6 Progress database: FIND FIRST Table1 WHERE Table1.Field1 = 1 AND Table1.Field2 >= 2 AND TRUE ...
0
votes
1answer
30 views

Does a single replica gives any performance improvement?

Consider a case where you have a single PostgreSQL database. Then you add a replica (hot standby) and you send all the reads to that replica. This is what does Rails for example. Does this gives any ...
0
votes
0answers
30 views

MySQL faster after restore

I have a table with ~300k records, and query with join other table. A query executed about 2 minutes. But! Yesterday I was backup and restore this database and saw, my query faster 10 times! From >...
2
votes
2answers
189 views

MySQL - Can I have quick queries in a table with 200 million rows?

THE PROBLEM I have following trivial SELECT statement: select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500; The query is pretty simple (no join statements, no ...
1
vote
2answers
48 views

Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column. Unfortunately, I cannot ...
2
votes
2answers
148 views

Highest cost section in Actual Execution Plan doesn't show any logical reads?

In the following query plan - https://www.brentozar.com/pastetheplan/?id=H1dwOCHed (it has 3 sections, the last one is the problem), there's a node at the very bottom that consumes 37% of the ...

1
2 3 4 5
122