Questions tagged [performance-tuning]

Improving the performance characteristics of a database application or system.

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

MySQL 5.7 on Amazon Aurora - how to read the IO Cache vs. Disk Read metric?

I'm trying to determine how often InnoDB is hitting RAM vs. disk. Does db.Cache.innoDB_buffer_pool_hits.avg mean InnoDB went to the buffer pool? Does db.Cache.Innodb_buffer_pool_reads.avg mean that ...
mstrom's user avatar
  • 133
2 votes
1 answer
63 views

MySql Aurora - how often to run ANALYZE TABLE?

I have a database that has existed for about 10 years, with hundreds of tables, many of which have several million rows. We don't have a DBA and we have an increasing number of slow queries. We're ...
mstrom's user avatar
  • 133
1 vote
1 answer
38 views

MySQL Amazon Aurora - a few slow queries causing extreme performance issues

I'm a dev, not a DBA, so pardon my ignorance. I'm running on MySQL Amazon Aurora, single-instance (with a replica for failover). A few days ago, my application had a handful of extremely slow queries (...
mstrom's user avatar
  • 133
-1 votes
1 answer
93 views

Optimizing Negative Int/BigInt Ranges in SQL Server: Strategies and Benefits

I am currently delving into the potential applications and advantages of utilizing the negative range of the int or bigint data types within SQL Server, with a particular focus on the range extending ...
Ali varzeshi's user avatar
2 votes
1 answer
80 views

MySQL abnormal connection peak and page cleaner warnings

I'm running MySql 5.7.42 on Ubuntu 18.04 on VM with 12 Core, 64GB Ram and SSD storage. This server is dedicated as DB server, some scripts and maintenance (backups) runs nightly but no other services ...
Sbraaa's user avatar
  • 21
0 votes
1 answer
79 views

Execution plan - cost of sort

I've got a procedure with a Sort operator of 92%. Here is the execution plan: here is the script: CREATE PROCEDURE [Specification].[GetSpecificationsByPage] ( @Next int, @Limit int ...
dexon's user avatar
  • 47
2 votes
2 answers
317 views

Execution plan - Not using Index seek due to ISNULL

i've got a procedure that i want to improve that uses this where clause: WHERE p.PlatformId = ISNULL(@PlatformId, p.PlatformId) and p.PayeeStsId = 1 and p.PlatformPayeeId = ISNULL(@...
dexon's user avatar
  • 47
1 vote
2 answers
183 views

Optimize query with many joins

The developers came asking to improve the query performance. I tried using CTEs instead of lateral joins and EXISTS, testing covering indexes, additional filtering. No significant performance benefits....
George K's user avatar
  • 2,271
0 votes
1 answer
116 views

Windows Power Plans for SQL Server

Is the High Performance power plan in Windows Server still relevant in SQL Server setups on modern hardware/virtualization? Searching for articles from my usual trusted sources brought up nothing more ...
Wayne Cochran's user avatar
0 votes
1 answer
483 views

PostgreSQL : is max_worker_processes cpu bound?

I have seen a lot of PostgreSQL performance tuning blogs and companies like EDB suggesting putting max_worker_processes setting for PostgreSQL to the number of CPUs in the machine. We are using a ...
mediocre's user avatar
0 votes
1 answer
115 views

Is this Parameter Sniffing?

I have a particular stored procedure that takes 1 parameter. This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc. Day 1 - ...
Sicilian-Najdorf's user avatar
0 votes
3 answers
205 views

Partitioning an existing table yearly in SQL Server with huge data

I have a logs table for my application which is almost 3TB data. I want to partition this table yearly. Below is the scripts for Table, View, and indexes script: GO CREATE TABLE [dbo].[AuditLogs]( ...
Mohd Waseem's user avatar
1 vote
2 answers
286 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 ...
SQL_NoExpert's user avatar
  • 1,059
-3 votes
1 answer
59 views

Please I received this error on a scrub process “scrub failed, no running scrub jobs”

Please help it is urgent . The database is also in replication enter code here
aee's user avatar
  • 11
1 vote
1 answer
67 views

Almost identical MySQL server totally different performance

I have two servers which differ in mysql version Server 1 runs with mysql 5.7 2 cores Intel(R) Xeon(R) Gold 6248R CPU @ 3.00GHz and 6GB RAM Server 2 runs with mysql 8.0 4cores CPU Intel(R) Xeon(R) ...
kekw's user avatar
  • 111
1 vote
3 answers
127 views

SQL Server Query Performance: Index Seek in SSMS but Index Scan in Java Application

I'm facing an issue with SQL Server query performance and I'm hoping to get some insights from you all. Problem Statement: I have a SQL query that performs well (~6 ms) when executed directly in SQL ...
Vipin's user avatar
  • 113
0 votes
2 answers
209 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 ...
Farhan's user avatar
  • 121
0 votes
2 answers
189 views

Performance tuning of SQL Server query, with LEFT OUTER JOIN vs INNER JOIN

In my scenario i need to optimize a stored procedure, that i used to import data into DB from an exchange DB. I am relly puzzled because, the solution with INNER JOIN is lower than the LEFT OUTER JOIN ...
Skary's user avatar
  • 368
0 votes
1 answer
119 views

PostgreSQL Processor Queue Length

Is there a similar metric to MSSQL's Processor Queue Length in PostgreSQL? I had a situation recently where a system ran several concurrent long-running queries that essentially blocked all other ...
Nick Molyneux's user avatar
0 votes
0 answers
25 views

MongoDB: Complicate aggregation operation speed optimization

We want to calculate the average weight reduced and total weight loss users according to scales collection. The following steps describe our aggregation: Firstly, we calculate the average weight for ...
Hhry's user avatar
  • 111
0 votes
2 answers
47 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 ...
user570286's user avatar
1 vote
1 answer
186 views

Slower than expected query performance on Postgres

I just moved a FrontEnd web from querying a small Redshift Cluster to Postgres, looking for, primarily getting rid of Redshift but mostly to get better performance and user experience from a reporting ...
Alejandro's user avatar
  • 113
0 votes
1 answer
1k views

Tuning MySQL 8.0.33 - increasing innodb_buffer_pool_size not working?

I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running). I'm not sure if this is the right thing to ...
Cintya 's user avatar
0 votes
1 answer
79 views

Tune SQL Server 2019 for receive many small queries

I am using SQL Server SE 2019, on a Windows Server 2019 (with SSD drive) that run IIS 10. On top of IIS 10 run an ASP.Net application, that has to be considered AS IS (I can't modify it). Lately tourn ...
Skary's user avatar
  • 368
2 votes
1 answer
946 views

Postgres-15 response time jumps up from 0.5 ms to 100-200 ms once in a while (~ 0.5% of requests are affected)

I have a simple database with one table of ~5 millions rows, another table with ~10 millions rows, couple of other tables have 10th of thousands of rows. There is a constant load without any spikes at ...
Viktor Vsk's user avatar
0 votes
0 answers
70 views

Utilizing GIN Indexes Efficiently for Queries Without Type Casting in Postgres

We have an use case where we need to perform cascading deletions across multiple tables in our database. For this purpose, I have found that using GIN indexes as suggested in this answer is quite ...
Sri S's user avatar
  • 11
3 votes
2 answers
356 views

Merge join asks for seemingly unnecessary sort before joining

I use one table to update another table. Both the tables have a clustered index where the first 4 columns are the same type of data. CREATE UNIQUE CLUSTERED INDEX [...
KHP's user avatar
  • 65
0 votes
0 answers
681 views

Sometimes simple queries are slow, high buffer IO wait times the cause?

I have a Azure SQL database, Premium P2: 250 DTUs, using 250GB of storage. Sometimes even simple queries runs slow, for example by getting a single row from a simple 300mb table with with 500k rows, ...
viblo's user avatar
  • 479
0 votes
1 answer
94 views

Help with SQL Schedulers Offline

We are in the middle of a migration and moving from SQL 2012 Enterprise to SQL 2019 Standard (due to licensing costs) and I have recently encountered this issue using sp_blitz. With the results ...
James Scott's user avatar
1 vote
0 answers
52 views

Getting a SORT operator when number of rows being updated is above a threshold [duplicate]

I'm working a table that has VARCHAR(18) as the PK. I'm putting this here in case it's significant. We have a SP that runs as part of an ETL that updates X rows each time. I've noticed that when the ...
Geezer's user avatar
  • 431
3 votes
1 answer
218 views

SQL server chooses hash match over merge join but the input fields to join should be sorted

I have a simple query that joins two tables on the field PRODID. For some reason, SQL Server opts to use a hash match to join this, but I believe it should choose a merge join, as that field is part ...
KHP's user avatar
  • 65
1 vote
1 answer
121 views

Rewriting a DELETE statement to reduce pagelatch_ex waits

I have the following TSQL statement, which is often executed in multiple sessions in parallel and leads to serious pagelatch_ex waits: DELETE dbo.huge_table1 WHERE c1 = 0 AND c3 IN (SELECT v ...
Fajela Tajkiya's user avatar
1 vote
2 answers
85 views

How to interpret the stats of index usage? [closed]

Question: How do we interpret the stats shown in the diagram below? The stats seem to indicate bad index designing, how can it be improved. I realize this a sort of broad question since it depends on ...
nam's user avatar
  • 291
3 votes
2 answers
427 views

How to gather Index usage information

Following query correctly outputs number of seeks, scans, etc. I have question on understanding some rows in the output. In some output rows of the query, the index name is shown as NULL and the ...
nam's user avatar
  • 291
1 vote
1 answer
252 views

Database performance, high IOPs and latency (SQL Sevrer)

I know that if we separate databases on separate drive configured of separate disk group (SAN LUN), it helps to improve disk IOPs. Does separation of databases to different drives help to improve IO ...
Rajesh Ranjan's user avatar
1 vote
1 answer
35 views

Could cloning / imaging SSD cause MySQL data to be more fragmented?

On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times ...
adrianTNT's user avatar
  • 195
0 votes
1 answer
352 views

Upgrade Aurora from 5.6 to 5.7.mysql_aurora.2.11.1 getting tons of deadlocks and lock wait timeouts, maybe 20-30x what they were before the upgrade

Our application logs are showing an excessive amount of lock wait times, and deadlocks for queries that have not had issues previously after upgrading from 5.6 to 5.7 MySQL in AWS Aurora. Are there ...
user2966697's user avatar
3 votes
1 answer
223 views

Stored Procedure Tuning Help

Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as ...
AW_DBA's user avatar
  • 31
-1 votes
2 answers
38 views

Is a performance improvement for this query with subselect possible?

I have the following table (simplified non-sense example): table fruits: +-----+---------+-------------+ | id | fruit | customer_id | +-----+---------+-------------+ | 1 | apple | 1 | ...
Phantom's user avatar
  • 111
0 votes
1 answer
1k views

Does a select within a where clause affect performance?

I know that we should avoid using functions in the where clause of a query wherever possible since the function will run for every row to return the proper results. However, does the same principle ...
Paradox's user avatar
  • 155
0 votes
1 answer
72 views

Help me in optimising the query which takes almost 50 seconds

Please help me optimise the following query running on MySQL 8.0.28 Community Edition. explain select id, created_at, data, deleted_at, entity_id, ...
Avinash Pawar's user avatar
1 vote
1 answer
671 views

Why is MariaDB innodb_flush_method = O_DIRECT so much slower than fsync for SELECT count(*) as count? [closed]

I have a MariaDB 10.5.18 server with 64 GB of RAM running on an enterprise grade SSD. I'm using a fairly generic my.cnf to try to figure out why innodb_flush_method O_DIRECT is so much slower than ...
Deefo22's user avatar
  • 11
0 votes
2 answers
115 views

About "wal_buffers" of Postgresql-11 with "synchronous_commit=off"

Last days I tuned my PG11 for a lots of huge writing transactions, and got a amazing result. The only warry in my mind is that whether there is more risk of lossing data when I turned off ...
Leon's user avatar
  • 347
0 votes
1 answer
292 views

Does it make sense to enlarge "shared_buffers" for writing Postgresql?

I'm using Postgresql-11, with a traditional HDD as the physical storing device. I need to optimize the performance for writing. I have changed the wal_buffers from -1 to 512MB, that means that ...
Leon's user avatar
  • 347
0 votes
0 answers
48 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....
KodaCode's user avatar
1 vote
1 answer
732 views

Calculating MySQL server requirements

I almost didn't post on here as I know this is a somewhat open ended question and I'm in danger of being berated but I've been doing a lot of reading about various things and still don't really have a ...
webnoob's user avatar
  • 605
2 votes
0 answers
102 views

Mysql high Created_tmp_tables

I am seeing some high Created_tmp_tables and I just can't seem to figure out where they are coming from. Those rates apparently cause high Opened_tables aswell. PhpMyAdmin is complaining that ...
Jenny's user avatar
  • 21
-2 votes
2 answers
122 views

index scan instead of index seek on columns with OR operator [closed]

I am trying to tune following query select - - - FROM Table1.Column1 = 'I' and ( Table1.column2 in (1234) OR Table2.column2 in (1234) ) I am trying to improve query execution time but it takes ...
SqlDBA's user avatar
  • 117
2 votes
1 answer
467 views

Why would INSERT, REPLACE or UPDATE statements sometimes take over 1 second to execute?

I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet. The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, ...
Nuno's user avatar
  • 809
0 votes
1 answer
674 views

How should I configure my postgresql to create an Index for a big table (655GB) as fast as possible?

I'm importing a big dataset of medication usage (14 years of public health system data from Brazil) for a data science project. When I first started, I created one of the tables with all the indexes (...
Jaejatae's user avatar
  • 103

1
2 3 4 5
14