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
-1 votes
1 answer
13 views

Should we create new tables instead of filtering 1 large table?

In an application with groups and posts where each post can be in exactly 1 group, we can have the table POSTS(post_id, group_id, post_content). However, there's gonna be a lot of posts in a lot of ...
user avatar
1 vote
1 answer
21 views

Could MySQL perform better on a 10GB network storage with raid vs local HDD?

Compared to using a HDD on local computer and do many MySQL read / writes on it, could it have more queries per second if I install 10GBe network cards and I store the MySQL data on a raid network ...
user avatar
  • 131
0 votes
0 answers
18 views

Indexing a pivot table. Is really necessary?

I'm creating a small app for a contractor where an Event is assisted by multiple Clients. ┌─────────┐ ┌───────────┐ ┌────────┐ │ Event ◄──────► Assitance ◄──────► Client │ └─────────┘ ...
user avatar
0 votes
2 answers
53 views

The implications of lack on indexes on supporting a large production database

The situation is as follows: I started at a new company several months ago in a support capacity. Part of my role will involve running queries in production to help diagnose application issues. I was ...
user avatar
0 votes
1 answer
17 views

Can database confirm the sequential read is really sequential in SSD

In SSD, overwriting a data file means SSD has to first erase it and then write data. This can change the data layout in the SSD for the data file. MySQL has some policies to optimize random IO, e.g., ...
user avatar
  • 83
0 votes
0 answers
9 views

Enterprise manager - ASH graph

I'd like to know the numbers in the left the graph also have some relationship between the amount of active session waiting an event, For example: Usually seem be only the load of the machine, but ...
user avatar
  • 506
2 votes
2 answers
31 views

Find Which Queries will be affected by Proposed Change to CTFP

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
user avatar
  • 1,450
0 votes
0 answers
55 views

Why is full backup so slow after running big table update?

I'm using SQL Server 2016 SP3 Enterprise Edition on AWS EC2 instance I have the following msdb job: takes a copy-only full backup, database belongs to a read-only replica from AlwaysOn restores the ...
user avatar
1 vote
3 answers
51 views

Optimizing a PostgreSQL query with many small lookups

I have a database about books and book sales. The tables look like this: CREATE TABLE purchase ( person_id integer, book_id integer, CONSTRAINT purchase_pk PRIMARY KEY (person_id, book_id) ...
user avatar
0 votes
0 answers
32 views

How to understand and decrease Innodb_rows_read.avg (RDS INNODB)?

I am looking to understand what's behind Innodb_rows_read value and how to decrease it. For some unknown reasons my RDS server has been over 90% of CPU for more than 5 days and after investigation, ...
user avatar
  • 109
0 votes
1 answer
20 views

Aggregate data with multi-schema tenant architecture

We have an application that uses the tenant schema architecture, one database, has multiple schema per "tenant", each tenant is a replica with same tables etc. Sometimes we don't know the ...
user avatar
  • 101
0 votes
2 answers
113 views

help with improve query

we have this query and it takes more than 12 minutes with some of our users is there any hint we can improve it. SELECT b.id, ARRAY_AGG(DISTINCT CONCAT(pci2.id, '~', ...
user avatar
0 votes
1 answer
28 views

SQLCONNECTIONPOOL growing very huge

One of my db server SQLCONNECTIONPOOL growing very huge,it consumed 55GB , out of 65Gb (sql MAX) memory) Environment: Microsoft SQL Server 2017 (RTM-CU25) Standard Edition How can i reduce the ...
user avatar
1 vote
0 answers
37 views

What is select[dbo].[SqlManagement](1) in MS Sql Server?

I often run this script to see whats taking up time and slowing down my server SELECT top 10 last_execution_time ,execution_count , total_worker_time , total_elapsed_time , Text FROM sys....
user avatar
-3 votes
1 answer
50 views

speedup postgres pg_restore

Hi I have to update monthly my database using this big dump (100gb) I don't have the control over how it's done I only receive a .zip with a lot of .gz i want to speed up the restoring since I only ...
user avatar
  • 3
0 votes
0 answers
28 views

How can I improve disk performance on PostgreSQL on disks with slow write performance?

For a little personal project I have a docker-swarm running and I am using glusterfs as data storage. With docker-swarm, a container can be stopped at any point in time by the service and be re-...
user avatar
  • 631
1 vote
1 answer
74 views

Simple query with a single join very slow

I have this very slow, simple query that joins a large table (~180M rows) with a smaller table (~60k rows) with a foreign key, filtering an indexed column on the smaller table, ordering by the primary ...
user avatar
-2 votes
0 answers
22 views

Tool to generate random select queries?

I'm trying to test a few things performance-wise on postgresql. I specifically need lots of queries selecting random tables with random expressions doing multiple joins, similar to the join order ...
user avatar
0 votes
2 answers
59 views

Why use both universal identifiers and separately incremented primary keys for strong entities?

There is a good public example for this: The relational model of ChEMBL (A bioactive molecule database). Here all strong entities (such as molecules) have a database-wide unique identifier which also ...
user avatar
  • 325
0 votes
1 answer
17 views

What is the best way to insert data that is not yet in the table?

I have a data set that has grown bigger. Before I could afford to re-create the table anytime, but the writing operation is now taking too long. I cannot do something like timestamp-based delta ...
user avatar
  • 1
0 votes
1 answer
31 views

Compatibility level check [duplicate]

To prevent memory grant (excessive) problems I want to change my DB compatibility level to 150 How can I be sure I won’t have a problem ? Is there any script/tool to check my DB for 150?
user avatar
0 votes
1 answer
41 views

Converting Query from DB2 to PostgreSQL, Now takes hours to run

I have a project for converting some web pages from using DB2 to the same data/tables/etc within PostgreSQL. I'm running into an issue where a query that would normally run in less than a second for ...
user avatar
0 votes
0 answers
23 views

MSreplication_subscriptions updated tens of thousands of times per sync

I have a server that is a subscriber to several transactional replications. When looking into Activity Monitor in SSMS on the subscriber, I frequently see entries like this in Recent Expensive Queries:...
user avatar
  • 125
0 votes
0 answers
23 views

PostgreSQL cache function return value

Suppose I have defined a function that is expensive to compute. Is there a way to define an index in PostgreSQL to cache the return value for a given set of parameters? For example, let's say that I ...
user avatar
2 votes
0 answers
39 views

How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

What would be the best way to aggregate across hierarchies? I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) - folder Column Type Description folder_id (pk)...
user avatar
0 votes
1 answer
48 views

Is my database ERD well designed

I'm creating a PostgreSQL database for datasets, which should store historical and future data of weather forecasts and train timetables. I'm wondering if my ERD is good enough for a database which ...
user avatar
  • 1
0 votes
0 answers
34 views

Are there performance issues with FKs on different schemas?

In order to perform functional decomposition of a "big blob" of a database the first step we wanted to take is to separate different modules of the database into separate schemas on the same ...
user avatar
2 votes
1 answer
22 views

Using innodb_flush_log_at_trx_commit = 2 with RDS MultiAZ - Safe?

I understand the downsides of using the parameter setting innodb_flush_log_at_trx_commit = 2 If you want to know more, look at another answer, for example Is it safe to use ...
user avatar
0 votes
0 answers
14 views

Database as IPC on ARM proccesor performance

I have multiple processes on my ARM processor which want to communicate with each other. I know there are other mechanisms for IPC except database, but currently, I chose database. My solution uses ...
user avatar
  • 131
0 votes
0 answers
40 views

Very imbalanced memory usage across NUMA nodes

Our SQL Server 2014 Standard edition uses 82 / 94 GB RAM on our server and we have 2 NUMA nodes configured. When I check the LPE for both nodes, instance 1 has an very high value, 90000+ and steadily ...
user avatar
  • 1
0 votes
1 answer
48 views

Is making 2 sequential request to server for every webpage better than implementing server to make subqueries with every request?

Design A: Make server do more work Given a client url in such format www.mywebsite.com/profile/[username], its page generates a number of components that further requests data from the server to fill ...
user avatar
  • 5
-6 votes
1 answer
29 views

cursor syntax to fetch and drop tables in the db

I have a list of tables from a select query which gives me at least 50 tables daily . Need to drop them as they are temporary. I need a cursor to fetch and drop each one of them in a loop. I am using ...
user avatar
0 votes
3 answers
40 views

Mysql query that is very quick (but sometimes very slow) versus an alternative version of the same query that is consistently a *bit* slow

I have a query that is part of a larger query. I have tried two versions of the query to try to improve the page run time. One version runs almost instantaneously.. but sometimes (approx one in 50) ...
user avatar
  • 619
1 vote
0 answers
26 views

Will a time series database be a better fit for our data?

I work on a website where users can play and download audio. Each time a visitor plays or downloads a piece of audio, that usage is recorded for statistics. We have two MySQL tables where we keep this ...
user avatar
  • 113
1 vote
1 answer
59 views

Optimizing expensive GROUP BY / ORDER BY query on 690,000 row table

I am writing a query to calculate hiscore rankings for a game. It performs a GROUP BY to aggregate hiscore statistics for individual player stats that are stored across 23 different rows (separated by ...
user avatar
6 votes
2 answers
443 views

Non parallel plan only in Azure when SESSION_CONTEXT is used

I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
user avatar
0 votes
0 answers
26 views

Performance slowdown- mysql

My application is deployed on Tomcat with Mysql and Java 8 on a private cloud based server. Today, suddenly the threads connected on the mysql has reached its max limit and is deprecating database ...
user avatar
4 votes
1 answer
148 views

Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS

We had an AWS MySQL RDS t3.xlarge instance with 2000 IOPS. Also, I have a cron job written in java which inserts data in a table: it can insert up to 12M rows daily. Since AWS RDS cost is quite high ...
user avatar
  • 141
4 votes
2 answers
122 views

Index converted from Seek to Scan in CTE

SQL Server version: Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-...
user avatar
  • 279
0 votes
0 answers
48 views

SQL Server Query Performance Deterioration

I currently utilize a financial database which my company pays access for (CapitalIQ - Xpressfeed) and it allows us to get a replicated version of their DB once a night which I then host on a AWS ...
user avatar
2 votes
1 answer
94 views

MySQL 5.7.37 is consuming CPU and High memory ( 90-95% )

I have a problem with my MySQL database. RAM usage is too high, CPU too. Does it come from my configuration ? Here's my configuration : auto_generate_certs ON auto_increment_increment 1 ...
user avatar
  • 21
0 votes
0 answers
34 views

SQL Server 2014 consuming more memory than max server memory setting

For the past month, I have noticed our SQL Production server has been consuming more memory than we allocated for it. This is the current structure: Total OS Ram: 31Gb SQL Server currently using ...
user avatar
1 vote
2 answers
106 views

What is most efficient design pattern for a sql database containing billions of rows per user in a single table?

I work on a relatively large system where have started to run into performance problems scaling for multiple users. The system is a .NET application, so query's are written using an ORM (entity ...
user avatar
  • 13
0 votes
0 answers
13 views

MongoDB datasize in replicaset dropped suddenly

We are using MongoDB 4.2 which has lot of collections and indexes. Yesterday we faced issue in which our mongodb started degraded performance where query which were taking few ms started taking ...
user avatar
  • 101
1 vote
1 answer
143 views

Postgres' conditional trigger: performance consideration

In my DB i need to make conditional trigger, and i stumbled upon this article. Discussed in the article, there are 2 options to implement the conditions: Option A: condition resides in the trigger ...
user avatar
  • 157
2 votes
0 answers
75 views

Huge Difference in IO and CPU for same query

I saw that same query got more than two execution plans. Query is included inside procedure which runs once a day every day. All tables in query got from 2000 to 50000 additional records every day. I ...
user avatar
  • 307
0 votes
1 answer
77 views

Postgresql - Split one big and often updated table into two for performance

Im about to make a decision on the shape of solution database. Thing im considering, and which is problematic to find clear answer with google is - Lets assume i have 1 big relation for my web ...
user avatar
7 votes
2 answers
288 views

Is the Query Store's Overall Resource Consumption report telling me my database is doing terribly or are the numbers just whacked?

Recently we upgraded one of our production servers from SQL Server 2016 to SQL Server 2019 (CU 15). This was a great opportunity for me to enable Query Store on our main application database. It's ...
user avatar
  • 24.2k
0 votes
1 answer
39 views

Is there any way to let Postgres know it can filter results and then calculate aggregations?

Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A. None of the ...
user avatar
  • 1
2 votes
1 answer
31 views

How to performantly query using a function applied to an MySQL auto-increment column?

I have a users table in MySQL 8 with an auto-increment id as primary key: CREATE TABLE users (id int NOT NULL AUTO_INCREMENT, primary key (id) ). I have a use case for sorting the users into 1000 ...
user avatar

1
2 3 4 5
126