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
53
views
Large table with too many indexes stalls my app
PostgreSQL version: 13.10
My app has a notification feature, where users have a list of events that happened in relation to them (much like the Facebook notifications). Each notification is a row in ...
- 101
0
votes
2
answers
39
views
Performance for query filtered by user preference
I have an app, that shows data based on user preference. I will simplify it in this theoretical example:
The app shows articles filtered based on user preference for their favorite authors or their ...
- 103
1
vote
0
answers
39
views
Mysterious Increase in Server Load Every Hour with MySQL 5.7.4
I have a CentOS 7 server with MySQL 5.7.4 installed. The machine has 8 cores and 16 GB of RAM, and only the MySQL service is running. The database is not very large, and there are not many queries. ...
- 21
1
vote
0
answers
16
views
Mysterious Increase in Server Load Every Hour with MySQL 5.7.4
I have a CentOS 7 server with MySQL 5.7.4 installed. The machine has 8 cores and 16 GB of RAM, and only the MySQL service is running. The database is not very large, and there are not many queries. ...
- 21
0
votes
1
answer
101
views
Insert Statement on large MySQL table does not terminate
I have a MySQL database with several tables.
One table (InnoDB) is quite large (about 490 million rows).
The table consists of 4 columns:
id: int(11), auto_increment
term: varchar(255)
docId: int(11)
...
- 3
0
votes
0
answers
45
views
What is difference between "Statement Cumulative Statistics" and "Statement Self Statistics" in reports generated by OraSrp tool
I have generated trace for single session in oracle database(Version 19c). Then used TKPROF and ORaSrp tool to read raw trace files.
What I observed for same sql statement OraSrp tool gives two stats
*...
- 21
0
votes
1
answer
35
views
What is a common type of storage hardware for dedicated MySQL servers? [closed]
I am curious what kind of storage hardware is common to see in servers that are dedicated for MySQL ? For example online services like Amazon AWS, Azure, Google, etc that offer(ed) dedicated mysql ...
- 195
0
votes
1
answer
405
views
MySQL | Measure overall query cost (performance, cpu usage, memory usage)
The Problem
You have a MySQL query, let's say a SELECT and you need to identify its overall cost in all aspect (Host Resource Usage, Query speed / performance ...) so that your result can be ...
- 145
1
vote
0
answers
112
views
Critically high website CPU usage very sudden, persistent upon reboots [closed]
Quick summary. Wordpress community website, large userbase. Has caching for guests (WP Super Cache). Always operates around load of 1-2, or 5-6 at peak hours CPU.
As of 4pm EST on Feb 15, the CPU load ...
- 111
-1
votes
1
answer
40
views
Different speed on VLFs in SQL Server?
As the topic says, can VLFs in the same logfile have different speeds?
Yesterday when I updated a table with 12M records, the update speed was about 5x when VLFa was active vs VLFb, is this normal and ...
- 11
0
votes
1
answer
383
views
Why is there no performance_schema.threads?
I am trying to diagnose an issue where a phpBB message board hangs (becomes unresponsive) for about five minutes about once every hour. (It is not exactly on the hour every hour, but it is a little ...
- 13
3
votes
1
answer
546
views
Aurora / PostgreSQL Mass Update on 100 Million Rows Coming to a Halt
During the migration of a 300 million rows table I have encountered a strange behaviour on our AWS Aurora database:
Around 25 minutes into the migration, the total IOPS of the database dropped to ...
1
vote
0
answers
52
views
Query reads way more data on Postgres 13 than 11
I have two servers, one running Postgres 11, the other Postgres 13. Both servers have the same amount of RAM and CPU cores/speeds, and both are configured to these parameters. I have a query which on ...
1
vote
2
answers
454
views
MySQL is not using index on query plan with Order By and Limit
I'm trying to understand the behaviour of the MySQL query planner where an index is not used when a query includes Order By and Limit combined.
The table in question is big with around 8 millons of ...
- 11
1
vote
1
answer
68
views
Postgresql query takes unnecessary (?) long time
I know the web is full of questions, complaints, etc. like my question. But each of them still does not answer the questions of people finding those posts via Google, ...
I try to post my question ...
- 111
0
votes
1
answer
174
views
80,000 batch calls per second as seen in activity monitor
CEO wants "solutions" to CPU on the SQL Server always being near max use. I figured out that they have all the customers calling an API to hit this one stored procedure to the tune of 80,000 ...
- 1
0
votes
0
answers
43
views
Designing a scaleable social photo gallery in Postgres
I’m trying to design a scaleable approach to modeling a social photo gallery and user profiles.
Every profile will have 1 gallery.
My current idea is the following:
Photo table (id, photourl, fKey ...
- 101
3
votes
0
answers
216
views
Possible missing step in sampled statistics generation?
I've searched far and wide for information on why sampled statistics are less accurate, and broadly speaking every question has always been explained with the following generalizations:
sampled are ...
- 39
1
vote
1
answer
77
views
keeping order rows saved to calculate rank and percentile
we have a large table, around 12M records, everyday at midnight,
we run a query to sort all rows by a certain criteria and save the order in a column.
the reason we do that is because we have a ...
1
vote
2
answers
108
views
How to improve performance on query with nested INs?
I am trying to improve the performance of this query using joins instead of INs.
Also I am using the GUI tablePlus with Postgres. What are some good tools to assist with performance?
UPDATE app_public....
- 25
0
votes
1
answer
850
views
Improving OPENQUERY performance from Oracle to SQL Server
I'm currently working on a database migration, from Oracle 11g to SQL Server 2019. We're talking here of about 10-12 GB, in something like 300 tables (so creating them manually is out of reach, as is ...
- 3
0
votes
1
answer
461
views
Do prepared statements give a performance advantage when running multiple times with similar parameters?
I've used prepared statements (MySQL & PHP) for many years, but only with an eye to being safe against SQL injection. I've never re-used a prepared statement, I've always built them up every ...
- 245
0
votes
1
answer
267
views
Adding a non-clustered, covering index, on a primary key, when clustered index exists
I went through the related articles, suggested by SO, but didn't really find all the answers, so I'll try to be specific here.
Presume we have a (MSSQL) table, with a simple ID (int) primary key, with ...
- 149
1
vote
0
answers
101
views
How this performance graph may be interpreted?
My configuration is:
Compute
AWS ECS cluster with a Django based API (Using ORM). 1vCPU and 4GB of memory per container.
Database
RDS MariaDB db.t3.medium instance (2 vCPU, 4GB of memory)
gp3 type ...
- 111
0
votes
1
answer
783
views
Bad inner join performance on one-to-many relationship
I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
1
vote
2
answers
215
views
DataGrip showing prints during a transaction
I have a long running update query, for this reason I used the old well known trick of dividing the query in smaller chunks of 1000 to be print the status of the query and calm the anxiety.
...
- 303
0
votes
2
answers
534
views
MariaDB - Mysterious Delay at Each Query
I have 2 tables on MariaDB (10.5.16-MariaDB-log) where I query table A, and then loop over the roughly 4000 results and for each one do a query on table B. (This is inherited code and this will be ...
- 11
0
votes
0
answers
64
views
MySQL occasional performance issues
I am currently working on system with MySQL DB and SpringBoot (Hibernate). Both are located on the same server. All tables use InnoDB. Although there are no slow queries (5+ seconds), there is ...
- 1
1
vote
1
answer
719
views
Optimize querying 100 million records- Postgres - indexes
Currently our queries are not performant.
Table: (Item) 100 million rows.
id: uuid
user_id: bigInt
item_type_id: uuid
status: varchar
start: date
end: date
Index on user_id, item_type_id
Query:
...
- 13
2
votes
1
answer
143
views
SQL Server JOIN vs sparse table
I am in the need to query a relation between an entity called article (it's a product sold in a shop) and it's characterstics (a set of attributes). The result will always presented in a paged manner (...
- 358
0
votes
0
answers
177
views
Making backups with mariabackup from slave MariaDB. Best Way possible
I am trying to determine the best way to create backups from a MariaDB 1.4 slave, and I have the following questions:
Will creating backups affect the MariaDB slave replication and performance?
Is it ...
- 1
2
votes
3
answers
457
views
How to query most recent pair of each unique data-pair?
There's a table wherein there currency exchange rates are.
fx_rates
---
id
buy_curr_code
sell_curr_code
rate
inserted_at
A table gets updated with unknown frequency: sometimes daily, sometimes ...
- 35
0
votes
1
answer
45
views
How to find which queries are being run at a particular time
I have successfully implemented the tip described here to automate the collection of SQL Server database connections. I am getting the messages when this occurs and I have narrowed down the issues to ...
- 111
0
votes
0
answers
30
views
Is calling a stable function performance comparable to reading values from a table?
Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name).
_test_has_group_feature(group_name, ...
- 1,145
0
votes
1
answer
35
views
Drive with SQL binaries out of space
What happens if the drive (not C-drive) where the SQL binaries are located runs out of space?
Can this take down a production server?
- 785
0
votes
1
answer
67
views
How to tell if a MySQL query speed is bottlenecked by storage or CPU speed?
Using MySQL 8.0.30 on Rocky Linux 9
For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram ...
- 195
0
votes
0
answers
47
views
Optimize SQL Query
Im having trouble with the performance of this query, especificallly with the case statements. How would yo optimize it?
SELECT a.t,
a.c,
a.id,
a.id_r,
a.s,
a.n,
a.n_s,
a.c,
a.co,
a....
- 1
1
vote
1
answer
246
views
Postgres - most efficient way of storing a small number [duplicate]
I need to add new field to a table. The field will only store a number that ranges from 0 to maybe 30, so (in theory) a single byte field should be fine.
The smallest integer data-type listed in the ...
- 1,125
2
votes
1
answer
330
views
How can I force a system function to use an index in SQL Server?
Good morning team,
I’m really struggling to improve the performance of this function: sys.fn_cdc_map_time_to_lsn. It is a system function which is used by CDC, you can read a full description of what ...
- 41
0
votes
0
answers
25
views
How to make this location-based complex query more efficient?
I'm building a dating app, and it needs to filter based on several conditions:
Distance
Gender and Orientation
Liked or unliked status
I'm using Postgis extension. In this case "my own" ...
- 101
1
vote
0
answers
269
views
PostgreSQL - (declarative) List vs. Range partitioning for DATE's; avoid recheck
PostgreSQL 12.8 - AWS Aurora
Given a large data-set (~10B rows / 5TB size) residing in a partitioned table
CREATE TABLE large_table (
partition_date DATE,
prime_entity INT,
attribute_1 ...
- 21
0
votes
1
answer
292
views
Performance benefit of dividing a frequently used stored procedure into multiple stored procedures in SQL Server
I have a stored procedure usp_appdata that is used by our application loading screen as well as a lot of other places in the application. This SP is very resource intensive and returns a lot of data. ...
- 437
6
votes
1
answer
486
views
Why does SQL Server sometimes estimate that joining onto an empty table will increase the row count?
I recently came across an issue where tSQLt tests were taking a long time to run.
The procedure under test was doing a 38 table (!) join (with 37 faked tables and a table valued parameter).
Only two ...
- 83.3k
1
vote
0
answers
44
views
Best performance for cleaning table
I have two tables with millions of rows in which an average of 300 or more rows are added every day between the two tables.
Many of these rows are rows that do not provide any information and I need ...
- 111
0
votes
1
answer
471
views
Logging in error states in Postgres transactions
I'm currently working on rebuilding some SQL Server stored procedures in Postgres, but I have the following hangup
CREATE OR REPLACE PROCEDURE public.math_proc ()
LANGUAGE plpgsql
AS $$
BEGIN
...
- 251
0
votes
1
answer
57
views
MySQL | Related counter on one to many relation
Problem
Is there a way to automatically count related tables (one to Many), where the count will automatically increment / decrement depending if we add or remove a related item.
So, I could obviously ...
- 145
1
vote
2
answers
691
views
Very slow DROP INDEX, ADD PRIMARY KEY index
This is probably related to another question I had, but not sure.
So my table url_meta is 25 GB including data and indexes, 70 million records.
Data 18.3 GiB
Index 7.3 GiB
Overhead 5.0 ...
- 195
0
votes
1
answer
230
views
Optimize MariaDB - avoid "Copying to tmp table" - find joins without index
I have a website which is backed by a MariaDB database. The DB seems to be the bottleneck to the sites performance. I spend quite some time on optimizing various DB parameters, but now I am stuck with ...
- 101
0
votes
1
answer
293
views
Deadlock on Non Clustered Index
<deadlock>
<victim-list>
<victimProcess id="process1e4222068c8" />
</victim-list>
<process-list>
<process id=&...
- 167
0
votes
1
answer
250
views
Optimizing 2x 1.8GB tables takes 50 minutes
I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 ...
- 195